    18  

    Sybase SQL Server 

             
Microsoft SQL Server.      SQL 
Server  Delphi,      Transact-SQL  
Sybase SQL Server. 

      

          Sybase SQL Server   
  .   UNIX    
  _-,    Windows NT   
Sybase Services Manager.         
Sybase SQL Server.     Services  Control Panel 
Windows NT.  SQL Service     Windows NT, 
        Services  
Windows NT Service Manager. 
       SQL Server   Windows NT,   
       SHUTDOWN  
Transact-SQL.        SHUTDOWN 
   NOWAIT.      
    . 

       

        Delphi,    
Windows 95  Windows NT,   Sybase    
 Sybase Net Library.        
  Windows 95  Windows NT,     
  .   Windows NT (    Sybase 
Open Client for Windows NT)        
.        Windows 95   
      422. 
           Windows 95,    Windows NT. 
        .   
,   ,        
    . 

     422  Net Library/Open Client   Windows NT 

      Net Library  Windows NT    
:       .   
 1996 .        
 422,    Windows NT. ,     

     18. Sybase SQL Server 371 

    .   ,  ,   , 
      TEMP  Windows   
, ,      .   
       422,    TEMP 
   .     ,  
  ,       . 

      

           
 SQLEDIT     Net Library.   
      . 
    1.  SQLEDIT         
Input Server Name. 
    2.   Add       Server Entry. 
    3.  Service Type      Query. 
    4.  Platform  Windows 95  Windows NT   
   . 
    5.  Net-Library Driver   .  
   NLWNSCK (  TCP/IP Winsock)  
NLNWLINK (  IPX).     
    6.      ,   ,  
 Connection Information/Network Address.    TCP/IP- 
  TCP/IP   IPX-   IPX.   
,  : 
    100.10.15.12,3000 
       100.10.15.12    TCP/IP,    3000   
.       ,    
   . 

      
       TCP/IP      
    HOSTS. ,    HOSTS 
  : 
    100.10.15.12    marketing 
           marketing, 3000. 
    7.             
Add Service      Connection Service Entry. 
    8.         
.      Ping     
..           
   .     , 
    . 
    9.     <Ctrl+S>        
  SQLEDIT. 

      
               
,       Net Library.	l? 

    SYBPING 

            Sybase SYBPING, 
    SYBASE.       
         Ping.    
        . 

    372  III.   

     Windows 3.x 

          16-   
(Windows 3.x),     ,    
 . 
    Q    Sybase SQL Server,  ,  System 10, 
         
Sybase DB-Library. System 10       
CT-Library.     System 10   Windows 3.x 
      DB-Library,    
     System 10 .   
  Delphi 1.0   DB-Library, Delphi 2.0    
   DB-Library,   CT-Library.     
,    . 
    Q      DB-Library  :    
  .      ,  
3-1-94 (1  1994 .),       . 
  ,    ,    
 . ,      Delphi 
1.0    DB-Library,   1994 ., 
      -  ,  
       .    
  Windows 3.x     Sybase  
    ,     
 DB-Library. 
    Q    SQL Server,  ,  System 10,   
       ,  , 
  .      
Out-Of-Band-Data (OOBD).      IPX,  
  TCP/IP      
 .      Workplace 
TCP/IP    Novell     OOBD,  
     . 
    Q       Windows 3.x   OOBD 
   URGENT.      
  . 
    100.10.15.1,3000,URGENT 
      ,     dbcancel ()  
DB-Library,      ,   . 
    Q         16- 
    WSYBPING,   SYBPING,  
    32- . 
    Q    DB-Library  Windows 3.x   
(enhanced)    386,       
  .      Windows 
3.x   Sybase SQL    DB-Library    
,    ,     DB-Library  
      . 

      D 

         *  ,   
  BDE  ,   Delphi    
.         ,   
            
       BDE. 
     BDE      BDE Administrator,   
  Delphi Database Explorer.    
   BDE Administrator. 
    1.     BDE Administrator,     
 Databases      New.    
 New Database Alias    Database Driver Name  Sybase  
   . 
    2.           . 
    3.          Definition. 

     18. Sybase SQL Server	373 

    4.       SERVER NAME      
   SQL       ,  
   SQLEDIT. 
    5.          USER NAME   
,      .   
         
  Delphi. 
           BDE Administrator  
  Apply. 

       BDE 

         BDE,   
      Sybase SQL.    
    ,   ,  
,    (     ). 
 ,        
.         
Configuration  BDE Administrator. 

    APPLICATION NAME 

        APPLICATION NAME    , 
       SQL.   
     . 

    CONNECT TIMEOUT 

          ,   
        SQL.  
     60 .     
      - 
( ,     ). 

    HOST NAME 

          ,    
   Sybase SQL Server.     
   , ,  ,   
 sp__who. 

    NATIONAL LANG NAME 

          ,   
    .    ,   
,   SQL Server  . 

    TDS PACKET SIZE 

           Tabular Data Stream 
(TDS). TDS      ,    SQL 
Server     .    BDE Administrator 
      0  65535 ,  SQL Server  
    512 .  ,   
         
   ,     
  (,  BLOB).      
 512 ;      
  512  8192. 

      
            
   additional network memory.    
     SQL Server,   
   .     
    additional network memory    network 
packet size,        512 . 

    374	 III.   

        ,  , 
    SQL Server sp_configure.   
      sp_configure 'network packet 
size'.     ,    
    BDE Administrator.   
 IDS PACKET SIZE   BDE Administrator   network 
packet size  SQL Server   (    
additional network memory  ),     . 
    Q	Error:   unknown  user  name   or  password 
    Q	Server error  4002  Login  failed 
    Q	Server  error  20014   Login  incorrect 

    DATABASE NAME 

            SQL,  
   .    SQL Server  
         . 

    BLOB EDIT LOGGING 

         false,    
   BLOB.     
      -,    
 .  BLOB     
 bulk copy,       select into/bulk 
copy    , ,     sp_dboption. 

    MAX QUERY TIME 

        ,     SQL 
Links    ,    .  
 Sybase SQL Links       
   .    
     DRIVER FLAG  2048.   
       TIMEOUT.  
  SQL Links       
 (300 ). 

       Sybase SQL Server 

      ,    Delphi  
    Sybase       
.      . 

      Numeric  Decimal 

      Sybase System 10    : Numeric  Decimal. 
    BDE       
 , f idFloat.   ,     
       (xltNone),     
    . 

            

      Sybase        
. ,    CUSTOMER,   DEMO, 
   ACCOUNTING,   

    ACCOUNTING.DEMO.CUSTOMER. %  , 


          Sybase 

            Sybase SQL Server, 
  . 
    1.          Sybase SYBPING.   
      , , ,  
    BDE.    BDE Administrator 
    ( ,  ). 

     18. Sybase SQL Server	375 

    2.        SYBPING   ,  
   .       
 TCP/IP,        PING, 
    Windows 95  Windows NT.   
   ,    HOSTS,    IP-.	, 

      
       SYBPING   WSYBPING.  
   16-       
32- . 

    3.          PING  IP- 
 ,     ,  , ,   
 HOSTS.         
 (   SYBEDIT) ,    
 IP-.    HOSTS     
,          
   . 
    4.           IPX,  
   IPX-  Novell  ,   
  SYBEDIT.   ,     ,   
  ,     . 
    5.       TCP/IP    PING   , 
   IP-  , ,   ,    
.    ,   IP- . 
      TCP/IP, 127.0.0.1, 
     TCP/IP.     
    .     ,   
    .     
   . 
    6.       SYBPING  ,    PING 
 ,  ,   ,    
 ,     .   
        . 
     System 10    Sybase SYBINIT. 
    7.            ,   
   (  ,   ).  
, , ,   System 10  Novell NetWare  
    Novell IPX,     TCP/IP. 
          
,       ,   
   . 

     SQL 

           Transact-SQL  Sybase SQL 
Server      ,   
   SQL  . ,    
   ,   ,       
 SQL Server I SQL. 

       

      , ,   ,    
      .  ,     
 .  SQL     CREATE DATABASE. $ 
      .    
Transact-SQL: *%

    CREATE   DATABASE    ON  _=.</ LOG  ON  _=? 

        ,  ,    
 (logical device)   .    
 SQL Server         
  . 
       SQL Server   DISK INIT.  
  : 

    376	 III.   

    DISK INIT 
    name='saldatOO', 
    physname='c:\sybasesql\data\saldatOO.dat', 
    vdevno=5, 
    size=2048 

      name    . 

     physname       
  .         . 
     vdevno     . 
 ,         
  ,   .   
   1  255.  0    . 
    10,    
       sp_conf igure. 
     size    ,  
. ,     4  (4096 
),     2048. 
          .  
     SQL Server: 

    DISK INIT 
    name='sallogOO', 
    physname=':\sybasesql\data\sallogOO.dat', 
    vdevno=6, 
    size=512 

       ,     ,  
   .    CREATE DATABASE,  
   : 

    CREATE   DATABASE   sales   ON   saldatOO=4   LOG  ON   sallogOO=l 

                  
,        . 

     USE 

     USE     , ,  ,  
   .    : 

    USE   


      

           USE    
      .    
          
 .           , 
 .    SQL CREATE TABLE.   
 CUSTOMER   ^  ISQL  . 

    CREATE TABLE CUSTOMER 
    CustomerNumber	int4	NOT NULL, 
    LastName	char (30)	NULL, 
    FirstName	char^SO)	NULL, 
    StreetAddress	char (30)	NULL, 
    City	char(20)	NULL, 
    State	(2)	NULL, 
    Zip	chSir(lO)	NULL ) 

           SALE. 

    CREATE TABLE SALE ( 
    SaleNumber      int      NOT NULL, SaleDate        datetime  NULL, 

     18. Sybase SQL Server	377 

    CustomerNumber       int	NOT  NULL, ItemNumber                 int         
        NOT  NULL, Amount                           money ) 

    ,  SALE .      ITEM,   
  . 

    CREATE TABLE ITEM 
    ( 
    ItemNumber    int      NOT NULL, 
    Description   char(30)  NULL, 
    Price        money    NULL 


        

            SQL 
ALTER TABLE.      : 

    ALTER   TABLE   CONTACT 
    ADD  PhoneNumber  char(10)   NULL 

         : 

    ALTER TABLE CONTACT DROP PhoneNumber 

     ,       NOT NULL  
,     ,       
      NULL. 

     

       Sybase SQL Server      
 ALTER TABLE .... DROP .     
,        . 

     

     (constraint)      
  .        
  .       
    CREATE TABLE  ,    
ALTER TABLE.     : 

    ALTER  TABLE   CUSTOMER 
    ADD  PRIMARY KEY   (CustomerNumber) 

         CUSTOMER   
,     ^^.    
   ,      
CustomerNumber.  ,       
 ,    NULL. 
          ,  
     ,  .   
  ,       . 
,      ()   
 .        SQL 
Server     ^ .   
 : 

    ALTER  TABLE   SALE	*^ 
    ADD  FOREIGN  KEY   (CustomerNumber)   REFERENCES   CUSTOMER 

        CustomerNumber  SALE  , 
      CUSTOMER.  ,  
   SALE       
CUSTOMER,   ,  ,    SALE,  
   CUSTOMER.       
        
  (declarative referential 

    378	 III.   

    integrity).   ,     
            
  ,     . 
             
  .     : 

    ALTER TABLE CUSTOMER 
    ADD CONSTRAINT INVALID_STATE CHECK (State in ', 'AR', 'MO')) 

         ,   . 
   ,        
          
 .  ,       
   ,  Delphi  , 
  . 

      

       ,   .  
 ,      ,   
 . ,     
 INVALID_STATE     ISQL  . 

    INSERT   INTO  CUSTOMER   (CustomerNumber,   State) VALUES    (123,'CA') 

        ,    
       ' ', ' AR '  ' 
',         . 
          ,  , 
          . 

      

      SQL Server   CREATE  INDEX.    : 

    CREATE   INDEX   SALE02   ON  SALE    (SaleDate) 

     SALE02    , SALE   ,    
, a SaleDate   .  ,    SQL 
Server       ,    . 
         CREATE UNIQUE INDEX  ,  
 . 

    CREATE UNIQUE INDEX SALE01 ON SALE (SaleNumber) 


      

     SQL INSERT        
 SQL.     VALUES  INSERT   
 ;        ,   
    .        
  ,  .     
  CUSTOMER. 

    INSERT   INTO  CUSTOMER   (CustomerNumber,   LastName, FirstName,   
StreetAddress,   City,   State,   Zip) VALUES(1,'Doe','John',423   
SunnyLane','Anywhere', 'MO','73115') 
    INSERT   INTO  CUSTOMER   (CustomerNiimber,   LastName, FirstName,   
StreetAddress,   City,   State,   Zip) VALUES(2,'Doe','Jane','123   
SunnyLane','Anywhere', 'MO' , '73115' )	>%,,,. 
    INSERT   INTO  CUSTOMER   (CustomerNumber,    LastName, FirstName,   
StreetAddress,   City,   State,   Zip) VALUES(3,'Philgates','Buck','57   
Riverside','Reo', 'AR','65803') 

           ITEM. 

    INSERT   INTO   ITEM   (ItemNumber,   Description,   Price) VALUES(1001, 
'Zoso  LP',13.45) 

     18. Sybase SQL Server	379 

    INSERT INTO ITEM (ItemNumber, Description, Price) VALUES(1002,'White LP',67.90) 
    INSERT INTO ITEM (ItemNumber, Description, Price) VALUES(1003, 'Bad Co. LP',11.45) 
    ,      SALE. 
    INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber, 
    ItemNumber, Amount) 
    VALUES(101, 40/18/90',1,1001,13.45) 
    INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber, 
    ItemNumber, Amount) 
    VALUES(102, '02/27/92',2,1002,67.90) 
    INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber, 
    ItemNumber, Amount) 
    VALUES(103,'05/20/95',3,1003,11.45) ' 
    INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber, 
    ItemNumber, Amount) 
    VALUES(104, '11/27/97',1,1002,67.90) 

     ,        
        ,   
       .  : 

    INSERT INTO ITEM (Price, ItemNumber) VALUES(13.45,1001) 


     UPDATE 

     SQL UPDATE    ,   
  .  UPDATE     WHERE, 
 ,     .   
 : 

    UPDATE CUSTOMER SET Zip='65803' WHERE City='SpringField' 

       WHERE   UPDATE    , 
    , ,   ;     
        WHERE. 

    UPDATE CUSTOMER SET State='MO' 

       ,     
     . ,    
     ITEM   .   
 UPDATE   . 

    UPDATE ITEM 
    SET Price=Price+ (Price* .""67) 


     DELETE' 

           DELETE.   
 ,   : 

    DELETE FROM CUSTOMER  %.. 
    11'"u,: 

     DELETE     WHERE    . 

    DELETE FROM CUSTOMER WHERE LastNameO'Doe' 

    380	 III.   

      

     (transaction)      . 
      BEGIN TRANSACTION. 
            
 COMMIT,    ROLLBACK.    
   ,        
COMMIT;       . 

      
           DML (Data 
Manipulation Language), , INSERT, UPDATE  DELETE,   
        DDL (Data 
Definition Language).   ,     
,   .       
    ,       
 - . ,       CREATE 
TABLE  INSERT,      INSERT  
 . 

     SELECT 

      SQL SELECT   .    
   ,    ,    
.      SELECT * FROM _,  
_    (..  CUSTOMER, SALE  ITEM).   
             . 
     SELECT. 
      SELECT *      .  
  ,     ,    
 . 
    SELECT   CustomerNumber,   LastName,   State   FROM  CUSTOMER 


       

          SELECT     
  ,   (expression column),  
 ,   .    SELECT, 
     SALE    $15: 

    SELECT   SaleNumber,SaleDate,   AmountPlusShipping=Amount+15 FROM  SALE 


      

      (aggregate column)  ,   
 -     .    
   COUNT, SUM, AVG, MIN  MAX.     
.     ,  
 . 

    SELECT   COUNT(*)    FROM  CUSTOMER 

          . SELECT  
MAX(Amount)    FROM  SALE 
        %   . 

    SELECT SUM(Amount) FROM SALE 


      WHERE ^ 

      SQL WHERE      
 ,   SELECT.   .   
  ,   .  (   ): 

    SELECT * FROM CUSTOMER WHERE State='MO' 

     18. Sybase SQL Server 381 

          ,    
     Sunny. 

    SELECT * FROM CUSTOMER 
    WHERE StreetAddress LIKE '%Sunny%' 

     ,         
 .     ,    UPPER 
          . 
            $500. 

    SELECT * FROM SALE WHERE Amount > 500 

           ,    
18  1990 .  20  1995 . . 

    SELECT   *   FROM  SALE 
    WHERE   SaleDate   BETWEEN   40/18/90'   AND   '05/20/95' 


     

      WHERE     (join)   
   .       
   WHERE       
 SELECT.        SELECT 
  FROM,      ,  
      (=)   ,   
   WHERE  ,    
 .  : 

    SELECT  CUSTOMER. Customer_Number,    SALE. Amount 
    FROM  CUSTOMER,    SALE 
    WHERE  CUSTOMER.CustomerNumber=SALE.CustomerNumber 

       ,   ,     
 FROM,   SALE,    ,     
CUSTOMER  SALE       CustomerNumber. 
,     ,   (outer),  
   (inner).      (left)  
 (right) ,        
  (left join)     
   SQL. 

          

             
    (inner join).  
       ,   
 .   ,    (outer 
join)      ,   
  ,         
     NULL.    
   Transact-SQL: 

    SELECT  CUSTOMER.CustomerNumber,   SALE.Amount 
    FROM  CUSTOMER,    SALE 
    WHERE   CUSTOMER.CustomerNumber*=SALE.CustomerNumber 


    ANSI-  

      ,     Sybase SQL 
Server  ANSI-.      
  . 

    SELECT  CUSTOMER.CustomerNumber,   SALE.Amount 
    FROM  CUSTOMER  LEFT   JOIN   S^tbit 
    ON  CUSTOMER.CustomerNumber=SALE.CustomerNumber 
          . 
    SELECT CUSTOMER.CustomerNumber, SALE.Amount 
    FROM CUSTOMER LEFT OUTER JOIN SALE 
    ON CUSTOMER.CustomerNumber=SALE.CustomerNumber 

    382	 III.   

           LEFT  RIGHT. 
 ,          
   ,       . 

      

      (multi-tier join)  , 
   .       
   ,        . 
  . 

    SELECT  C.LastName,.FirstName,I.Description,S.Amount 
    FROM  CUSTOMER  C, 
    SALE   S, 
    ITEM  I 
    WHERE .CustomerNumber=S.CustomerNumber 
    AND S.ItemNumber=I.ItemNumber 

        CUSTOMER  SALE      
 CustomerNumber.     ,  ItemNumber, 
  SALE  ITEM.        
   . 

     

        ,     
   .      
(reflexive join)   (self-join).   . 

    SELECT   S.CustomerNumber,   S.Amount, 
    (S.Amount   /   SUM(S2.Amount))*100   Percentage FROM  SALE   S, SALE   S2 
    WHERE   S.CustomerNumber=S2.CustomerNumber GROUP BY  S.CustomerNumber,   S.Amount 

             
 ,         
  .        
 SELECT   .    
  ,    SALE     
      .    
          . 

    - 

     - (theta join)      
 ,     ..    
     (<>).   
 -;   ,    
 . 

    SELECT  .CustomerNumber,S.Amount,SUM(S2.Amount)   OTHERS 
    FROM  CUSTOMER  C, 
    SALE   S, 
    SALE   S2 
    WHERE . CustomerNumber=S . Custome*rNumber 
    AND C.CustomerNumber<>S2.CustomerNumber 
    GROUP BY C.CustomerNumber, S.Amount 

         . -,  
        CUSTOMER  
SALE. -,  -    , 
     .        
  SALE     ,     
 . 

      

       (full join)     
   ,      
WHERE.           
.           

     18. Sybase SQL Server	383 

    ,           
.    : 

    SELECT   ITEM.Description,   ITEM.Price,   SALE.Amount 
    FROM  SALE   FULL  OUTER   JOIN   ITEM 
    ON  ITEM.ItemNumber=SALE.ItemNumber 

      

      (Cartesian product)      
     .    
   ,      
   .  : 

    SELECT   SALE.SaleNumber,    ITEM.ItemNumber 
    FROM  SALE,    ITEM 
    ORDER BY  SaleNumber,   ItemNumber 

            
 .  ,       
  .       
,    . 

      

      (subquery)   SELECT,   
  WHERE   SELECT.    
    ,      
  . 

    SELECT   *   FROM  CUSTOMER 
    WHERE  CustomerNumber   IN(SELECT  CustomerNumber   FROM  SALE) 

     ,   ANY, ALL, SOME, EXISTS  SINGULAR,  
   .    ALL  
   SELECT,        
 . 

    GROUP   BY 

     SQL   ,   , 
      . , 
   ,        
    .     
        
       .  ,  
SQL   . -  SQL    ,   10,  
  50,   dBASE. ""      
GROUP BY  SELECT     SQL.   
   GROUP BY: 

    SELECT  CUSTOMER.CustomerNumber,   sum(SALE.Amount)   TotalSale 
    FROM  CUSTOMER,    SALE 
    WHERE  CUSTOMER.CustomerNumber=SALE.CustomerNumber 
    GROUP  BY  CUSTOMER.CustomerNumber 

              
   . 
     ,     ,    
  GROUP BY?   SQL Server,    
  GROUP BY      , 
    SELECT.    GROUP BY 
      SELECT    
 ; SQL Server        
GROUP BY,     . 

    %> 
    HAVING	^ 

      HAVING  SELECT     
 ,      GROUP BY.  
    GROUP BY     SELECT 
   WHERE,   ,  HAVING   WHERE 
   ,     . 

    384	 III.   

           ,    
 .  ,  HAVING  ,  
WHERE,            
,       .       
   HAVING. 

    SELECT  CUSTOMER.LastName,   COUNT(*)   NumberWithName 
    FROM  CUSTOMER 
    GROUP  BY  CUSTOMER.LastName 
    HAVING  COUNT(*)    >   1 

      HAVING      
      .    
  WHERE   ,  ,   
,        . 

    ORDER  BY 

      ORDER BY       
 .    : 

    SELECT * FROM CUSTOMER ORDER BY STATE 

      

    ,  , ,      
 COUNT ()   ()     . 
     (column alias)   
 ,         .  
 Sybase SQL Server  SQL       
       SELECT,    
,   . ,      
,   COUNT (),   NumberWithName. 

    SELECT   CUSTOMER.LastName,   NumberWithName=COUNT(*) 
    FROM   CUSTOMER 
    GROUP  BY  CUSTOMER.LastName 
    HAVING  COUNT(*)    >   1 

            
 ,       .   
,    ,    . 

    SELECT  CUSTOMER.LastName   LNAME,   COUNT(*)   NumberWithName 
    FROM   CUSTOMER 
    GROUP  BY  CUSTOMER.LastName 

             LastName 
  LName.  ,    
    , ,   WHERE  GROUP BY 
-^,       . 

      

     ,      SELECT    
,         .  
     (table alias)   , 
     FROM  SELECT.  
    ^,     . 

    SELECT   .LastName,    COUNT(*)   NumberWithName FROM  CUSTOMER   GROUP  
BY  C.LastName 

     ,        
 SELECT     .   
 ,          
 . 

     18. Sybase SQL Server	385 
    13 Delphi 3   / 

     

     (view)   SQL     SELECT, 
          , ,  
 ,     SELECT.     
     INSERT, DELETE  UPDATE.   
         . 
    SQL-,    
 .        
 SELECT,    ,    
,   ,    , 
    . 
     SQL     CREATE VIEW.  : 

    CREATE VIEW MOCUSTOMERS AS SELECT * FROM CUSTOMER WHERE State='MO' 

               
,   . 

    SELECT * FROM MOCUSTOMERS 

         ,     
  WHERE    SELECT,   
,   ,     , 
      . 
     SELECT,   ,    
   ,     SELECT,   :   
    ORDER BY      
 Sybase SQL Server,     InterBase, Microsoft  Oracle. 
         ,   
,         
,   .  ,     
,          
     .      
 WITH CHECK OPTION   CREATE VIEW.    . 

    CREATE VIEW MOCUSTOMERS AS SELECT * FROM CUSTOMER WHERE State='MO' WITH 
CHECK OPTION 

             
  ,     state  ''. 

      

      (stored procedure)   
   SQL,         
.     CREATE PROCEDURE,  
  . 

    CREATE PROCEDURE listcustomers AS 
    BEGIN	* SELECT LastName FROM CUSTOMER 
    END	^_ 

       - ,     
.      SQL Seiwer: 

    CREATE   PROCEDURE   listcustontersbystate(@State   char(2), 
    SLastNameMask  char(30)) AS BEGIN 
    SELECT  LastName   FROM  CUSTOMER 
    WHERE   State=@State  AND  LastName   LIKE   @LastNameMask END 

    386	 III.   

     

            
(Data Definition Language  DDL),     ,  
   (script) SQL.      
  ;     SQL  
    . ,     
    USE   GO.   SQL 
Server           
 ISQL. 

    ISQL  -Usa  -Imyscript.sql   -Omyscript.out 

     -i    ,  -   
  .   18.1    SQL. 
 sp_dir,   ,   ,  
 ,     DIR   DOS  Windows. 

     18.1.   SQL,    sp_dir /* 

    * DROP PROC dbo.sp_dir 
    */ 
    use sybsystemprocs go 
    IF OBJECT_ID('dbo.sp_dir') IS NOT NULL BEGIN 
    DROP PROC dbo.sp_dir 
    PRINT '< DROPPED PROC dbo.sp_dir >' END go 
    create procedure sp_dir @mask char(30) = '%', @obtype char(2) = 'U', 
@orderby char(3)='/N' as select o.id, 
    .name, 
    o.type, 
    date_created=o.crdate, 
    row_count = rowcnt(i.doampg), 
    row_len=l*null, 
    size = convert(numeric(13),(reserved_pgs(i.id, 
i.doampg)+reserved_pgs(i.id, i.ioampg))) , 
    data_space = convert(numeric(13),data_pgs(i.id, i.doampg)), 
    index_space = convert(numeric(13),data_pgs(i.id, i.ioampg)), 
    unused_space = convert(numeric(13),((reserved_pgs(i.id, 
    i.doampg)+reserved_pgs(i.id, i.ioampg))-(data_pgs(i.id, 
i.doampg)+data_pgs(i.id, i.ioampg)))) , 
    owner=user_name(o.uid) , 
    sequencer=0.0 into #sp_dir from sysobjects o, sysindexes i 
    where .name like @mask and oVtype like dobtype and o.id*=i.id 
    and i.indid<=l	.^ select @orderby=upper (@orderby)'' 
    if @orderby = '/N'	+. begin 
    create clustered index sp dirind on #sp_dir (name,id) end	" 'wj>-else 
    if @orderby = '/R' begin 
    create clustered index sp_dirind on #sp_dir (row_count,id) end else 
    if Sorderby = '/S' begin 

     18. Sybase SQL Server	387 

    create clustered index sp_dirind on ttsp_dir (size,id) end else 
    if @orderby = '/D' begin 
    create clustered index sp_dirind on #sp_dir (date_created,id) end else 
    if @orderby = '/DS ' begin 
    create clustered index sp_dirind on ttsp_dir (data_space,id) end else 
    if @orderby = '/IS' begin 
    create clustered index sp_dirind on #sp_dir (index_space,id) end else 
    if Sorderby = '/US' begin 
    create clustered index sp_dirind on #sp_dir (unused_space,id) end else 
    if @orderby = '/0' begin 
    create clustered index sp_dirind on #sp_dir (owner,id) end 
    alter table #sp_dir drop sequencer 
    alter table #sp_dir add sequencer numeric(10,0) identity 
    insert into #sp_dir (name,row_count,size,data_space,index_space,unused_space) 
    select 
    'TOTAL:',row_count=sum(row_count),size=sum(size),date_space=sum(data_space),index_space 
    =sum(index_space), unused_space=sum(unused_space) 
    from fsp_dir 
    select name,type,date_created, row_count, size, data_space, index_space, 
    unused_space, owner 
    from #sp_dir 
    order by sequencer 
    go 
    IF OBJECT_ID('dbo.sp_dir') IS NOT NULL 
    PRINT '< CREATED PROC dbo.sp_dir >' ELSE 
    PRINT '< FAILED CREATING PROC dbo.sp_dir >' go 

     sp_dir       
 ,       .   
   .  . 18.1     
 sp_dir. 
          ,    
   ,      GO  
 . 

      
          -,    . 


       

      SQL Server    EXECUTE,  
  . 

    EXECUTE _  

    388  III.   

     EXECUTE        ,   
     . ,  listcustomers 
       ISQL  : 

    listcustomers 

    iffi WISOL32 lUnlilled) on KHEN/oubs2							HtilE 
    ::!#  Connect     
    sp_dit-  'V.'S1 il-     V      -     '   '     '							jf 
    name	type date created					row^count    size                               
    _ 
    sysalternates	S	Jan	1   190(1	12:00:	88: 000AM	1	16 
    sysattributes	S	flug	17  1996	3:22:	15:916AM	16	16 
    syscolunns	S	Jan	1   1908	12:00:	:	239	21 
    syscomments	S	Jan	1   1900	12:00:	:	21	16 
    sysconstraints	S	Sep	1993	11:13:	11:OOOPM		16 
    sysdepends	S	Jan	1900	12:OB:	: 000AM	19	16 
    sysgams	S	Jan	2000	12:00:	:	0	0 
    sysindexes	S	Jan	1900	12:08:	00:000011	12	16 
    syskeys	S	Jan	1900	12:00:	00: 000ft 	51	16 
    syslogs	S	Jan	1900	12:88:	:800		62  
    sysobjects	S	Jan	1900	12:00:	:8	15	15 
    syspartitions	S	Rug	17   1996	3:22:	15:916RM	0	16 
    sysprocedures	S	Jan	1   1900	12:80:	:	261	18 
    sysprotects	S	Jan	1   1900	12:OB:	00:088011	87	31 
    sysreferences	S	Sep	1993	11:43:	11:		16 
    sysroles	S	Sep	1993	11:13:	11 : 	0	16 
    syssegments	S	Jan	1900	12:88:	:	1	8 
    systhresholds	S	Sep	1993	11:13:	11 : 		16 
    systypes	S	Jan	190B	12:80:	:	30	16 
    sysuser messages	S	Hay	22   1992	1:07:	22:	0	16 

    . 18.1.         
sp_dir 

           ,   
 : 
    exec   listcustomersbystate   '','%' 

     

    exec listcustomersbystate @LastNameMask='%',@State='MO' 


      

          ,   
   sp_.       
.     sp_spaceused.    
    pubs2    , 
   ,        master, 
,    ,   
 .       -master  sybsystemproc. 
     ,   USE       
   ,      
    . ,    
 sp dir    master,      pubs2, 
     : 

    master..sp_dir '%' 'S1 

      sp dir      sybsystemproc, 
        master. . 18.2 
 . 
              , 
      Sybase.    
owner (    )     dbo. 
    
     18. Sybase SQL Server 389 

    IS WISOL32- (Untitled) on KHEN/pubs2									^ 
    file  Edit ' ?oncect .  - .           .                       .. .. 
    master. .sp_dir   'V , 'S'                                                 
                                                                               
                             j~ 
    name	type date_created							rou_caunt    size                             
      __ 
    sysalternates	S	Jan	1	1900	12:00	:00	: 088AM	1	16 
    sysattributes	S		17	1996	3:22	:1S:916flM		83	16 
    syscharsets	S	Jul	27	1990	10:08	:32	: 76 8 11	5	32 
    syscolumns	S	Jan	1	1900	12:00	:00	: 800AM	253	2 
    syscomments	S	Jan	1	1900	12:08	:00	: 800AM	138	32 
    sysconf igures	S	Jan	1	1900	12:00	:80	:ODOAM	185	32 
    sysconstraints	S	Sep	1	1993	11:ii3:Ufl		: 088PM	0	16 
    syscurconf igs	S	Jan	1	1988	12:00	:00	: 000AM	8	0 
    sys databases	S	Jan	1	1900	12:88	:00	: 000AM	7	15 
    sysdepends	S	Jan	1	1900	12:00	:00	: 880AM	28	16 
    sysdeuices	S	Jan	1	1900	12:00	:80	: 088AM	18	16 
    sysengines	S	Jan	1	2000	12:00	:00	: 008AM	8	0 
    sysgans	S	Jan	1	2000	12:80	:00	: 000AM	8	8 
    sysindexes	S	Jan	1	1900	12:00	:00	: 000AM	39	16 
    syskeys	S	Jan	1	1900	12:00	:00:		l9	16 
    syslanguages	S	Jul	Z7	1990	18:08	:32	:760AM	0	16 
    syslisteners	S	Jan	1	1900	12:00	:00	: 088AM	8	 
    syslocks	S	Jan	1	1900	12:00	:BO	:OBBAM		8 
    sysloginroles	S	Sep	1	1993	11:U3	:16	: 800PM	U	16 
    syslogins	S	Jan	1	1900	12:00	:00	:BOOAM	1	16                              
                , 
    Puc. 18.2.         
  


     

     (trigger)      ,  
      .     SQL,  
        
: ,    , ..    
.      Transact-SQL: 

    CREATE TRIGGER SALEDelete ON CUSTOMER FOR DELETE 
    AS 
    BEGIN 
    DELETE FROM SALE 
    WHERE CustomerNumber=(Select CustomerNumber FROM 
    deleted) END 

              
SALE,          CUSTOMER. 
    (cascading delete),   
           
 ,     . 
          deleted.   
   DELETE       
 deleted,   .     
      CustomerNumber   
.   *       SALE. 
        INSERT  UPDATE    
    inserted.  %  
      ,       
  ,      . 

     

     (cursor)        
 SQL     .     
    .       
SQL,     BDE,     
     . 

    390  III.   

         : , , 
   .   ^^  ,  
   . 
        SELECT  (  
)   .    : 

    DECLARE CUSTOMER_SELECT CURSOR FOR SELECT * FROM CUSTOMER 

       ,  ,   .  
 ,    ,  
 OPEN. 

    OPEN CUSTOMER_SELECT 

      OPEN     .    
  

    FETCH. 
    FETCH CUSTOMER_SELECT 

              
.    FETCH     .  
 SQL Server       
:    FETCH      
.       CLOSE    
  OPEN. 

      
      SQL Server        
     Delphi.  BDE   
   -   ,   
  .      ,  TQuery  
,    ,   . 

    ,    ,    
    UPDATE  DELETE.   
     FOR UPDATE OF.    : 

    DECLARE CUSTOMERJJPDATE CURSOR FOR SELECT * FROM CUSTOMER FOR UPDATE OF LastName 


      
        FOR UPDATE OF     
,      .     
    . 

              
 DELETE  UPDATE    WHERE 
CURRENT OF _,    : 

    UPDATE CUSTOMER 
    SET LastName="Cane"        * 
    WHERE CURRENT OF CUSTOMERJJPDATE 

    	**. 

    DELETE FROM CUSTOMER 
    WHERE CURRENT OF %? 

           CLOSE.   : 

    CLOSE CUSTOMERJJPDATE 

           ,   
.     DEALLOCATE. 

    DEALLOCATE CUSTOMER UPDATE 

     18. Sybase SQL Server 391 

     

        ,    Sybase SQL Server,  
          
.        
Transact-SQL  Sybase SQL Server. 

     ... 

      19, "",    Delphi   
-        . 

    392  III.   
