    15  

    Microsoft SQL Server 

            \ 
Microsoft SQL Server.     -1  
   Delphi,      SQL,  
  Microsoft SQL Server. 

     SQL Server 

          SQL Server   
SQL Services Manager.         SQL 
Server.     Windows Sen/ices,    
Control Panel Windows NT.   SQL Service    
  Windows NT,        
 Services  Service Manager  Windows NT. 
       SQL Server   Windows NT,   
       SHUTDOWN  
Transact-SQL.        SHUTDOWN 
   NOWAIT.      
     . 

       

     ,     Microsoft SQL Server   
  ,        Sybase SQL Server.  
   Microsoft    Sybase   
Sybase SQL Server.    Microsoft SQL Server 6.0,  
        ,  
   . 
     ,  Microsoft SQL Server      
 Windows NT,        
/,           
  , ..  Microsoft SQL Server   . 
  SQL Server      ,   
   .       , 
       ,   
 .   -   "" Windows NT 
(  - ),         
 ,    ,     
.         SQL Server. 
               
,    . 
       ,   Delphi     
SQL Server  ,       . 
,        
TCP/IP-        
 (loop-back)  127.0.0.1. 
    
    306  III.   

       

         SQL Server    
 SQL Client Configuration.      Microsoft SQL 
Server.       . 
    1.      SQL Client  Configuration. 
    2.     Advanced. 
    3.        Server. ,  ,   
,      .     
 . 
    4.     Net Library Configuration  ,   
  .       Named Pipes- 
TCP/IP,     . 
    5.        Named Pipes    
     (  Server),   . 
   Add/Modify     ,  
    Done. 
    6.      ,   Named Pipes,    
   ,     
  . ,   TCP/IP   IP- 
     .     . 
  ,      Add/Modify  Done. 
           ,  
      SQL   Delphi. 

     

       ,      
  SQL client Configuration.  16-   
(, Windows 3.x)  16-   , 
  32-  32- .    
    (, DB Artisan)  
16-,      Windows NT.      
32-    16-  
 . 

          Microsoft SQL Server 

            Microsoft SQL Server, 
  . 
    1.          Microsoft iSQL/w.  
      ,    Delphi 
, , ,      BDE.   
 BDE Administrator     ( ,  ). 
    2.               
ISQL/w,     .      
  TCP/IP,      
(pinging)    -    PING, 
    Windows 95  Windows NT.   
  ,    HOSTS,   IP-. 
           ,  
  net view \\_,   _ 
    Windows NT,     SQL.    
 ,    netuse \\_\15,  
 __   .    
 ,    makepipe/readpipe.  
 makepipe  ,     -  
readpipe   /Sservername /Dteststring.  makepipe/readpipe 
      .  
   makepipe  <Ctrl+C>  <Ctrl+Break>.  
-      ,  
    Windows NT. ,  
      . 

     15. Microsoft SQL Server 307 

    3.          PING  IP- 
 ,     ,  , ,   
 HOSTS.         
 ,     IP-.    
HOSTS     ,     
        . 
    4.           IPX,  
,   -  -.   
     isql   /L.  j  
 /L      dbservernum,   
|      Bindery.      , 
    .      
 . 
    5.       TCP/IP,    PING   , 
   IP-  , ,   ,    
.    ,   IP- . 
     TCP/IP, 127.0.0.1,   
   TCP/IP.      
      .     
,       .   
     . 
    6.       ISQL/W  ,    PING 
 ,  ,   ,    
,     .    
       . 
    7.            ,   
   (  ,   ).  
         ,  
     ,     
 . 
      Windows NT       
 Windows NT Diagnostics,    Administrative Tools.  
      ,  ,   
 .      Windows  
DOS- MSD (Microsoft Diagnostics),     
    DOS  Windows.      
WINMSD.;     \wiNNT\SYSTEM32. 

       

              
,     BDE  ,   
Delphi    .       , 
        ,     
         BDE. 
     BDE      BDE Administrator,   
  Delphi Database Explorer.    
   BDE Administrator. 
    1.     BDE Administrator,      
 Databases      New.    
 New Database Alias    Database Driver Name  MSSQL  
   . 
    2.          . 
    3.          Definition. 
    4.       SERVER NAME      
   SQL.       ,  
   SQL Client Configuration. 
    5.          USER NAME   
,      .   
       , 
 Delphi      . 
           BDE Administrator  
  Apply. 

    308  III.   

        

         BDE,   
      Microsoft SQL Server.   
     ,   , 
 ,         
    (     ).  
,         . 
        Configuration 
 BDE Administrator. 

     DRIVER FLAGS       

            (BLOB  Binary Large 
OBjects)      Microsoft SQL Server. 
  ,     DRIVER FLAGS  
    .     ,  , 
          BLOB. 

    APPLICATION NAME 

        APPLICATION NAME    , 
        SQL Server.  
      . 

    CONNECT TIMEOUT 

          ,   
         SQL. 
      60 .    
       - 
( ,       ). 

    HOST NAME 

           ,    
   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  32767 . 
 ,         
   -   ,  
     (,   BLOB).   
    4096 ;    
    4096  8192. 
         ,  
,     SQL Server sp_conf igure.  
        
sp_configure 'network packet size'.     
,        BDE 
Administrator.    TDS PACKET SIZE   BDE 
Administrator   network packet size  SQL Server  ,  
   . 

    Q   Error:   unknown  user  name   or  password 
    Q   Server  error   4002   Login  failed 
    Q   Server  error    20014   Login   incorrect 

     15. Microsoft SQL Server 309 

      

       ,   ,  
 TDS  5   . 

    DATABASE NAME 

            SQL,   
 .    SQL Server   
        .  
     ,   DATABASE 
NAME.            
 ,         
  ,         
          . 

    BLOB EDIT LOGGING 

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

    MAX QUERY TIME 

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

     SQL 

         SQL Server, ""  
SQL      . SQL Server     
   ,    SQL,       
 SQL.        Transact-SQL, 
    SQL Server,     
  ,      SQL  
. ,       , 
  ,        ISQL. 

       

      , ,   ,    
      .  ,     
 .  SQL     CREATE DATABASE.  
      .    
SQL Server. 

    CREATE   DATABASE    ON  _= LOG  ON  _= 

        ,  ,    
   .     SQL Server 
         . 
       SQL Server   DISK INIT.  
  . 

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

    310  III.   

        name    . 
 physname        
 .        Windows 
NT,      .  vdevno  
   .  , 
  ,       , 
  .      1  255.  
0    .  size   
 ,  . ,   
  4  (4096 ),     2048. 
      .   
    SQL Server. 

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

          DISK INIT  
     NTFS,   FAT.   FAT  
      ,   
    .     
   NTFS  ,     
 .        , 
    .    CREATE  DATABASE, 
    : 

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

                 , 
       . 

     USE 

     USE      ,  ,  
   .    : 

    USE   

     ,          
 DB  ISQL.     ISQL   , 
        <Refresh>,   
   .       
   . 

      

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

    CREATE   TABLE   CUSTOMER 
    ( 
    CustomerNumber 
    LastName 
    FirstName 
    StreetAddress 
    City 
    State 
    Zip 
    int	NOT NULL, 
    char (30)	NULL, 
    char (30)	NULL, 
    char (30)	NULL, 
    char (20)	NULL, 
    char (2)	NULL, 
    char (10)	NULL 

     15. Microsoft SQL Server 311 

           SALE. 

    CREATE   TABLE   SALE ( 
    SaleNumber	int                 NOT  NULL, 
    SaleDate                     datetime     NULL, 
    CustomerNumber        int                 NOT  NULL, 
    ItemNumber                 int                 NOT  NULL, 
    Amount                         money ) 

    ,  SALE .      ITEM,   
  . 

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

      

            SQL 
ALTER TABLE. Microsoft SQL Server      , 
       .  
      . 

    ALTER TABLE CONTACT 
    ADD PhoneNumber char (10) NULL 

     :      NOT NULL  
,    ,        
     NULL. 

     

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

    ALTER   TABLE   CUSTOMER 
    ADD   PRIMARY  KEY    (CustomerNumber) 

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

    ALTER  TABLE   SALE 
    ADD  FOREIGN  KEY   (CustomerNumber)   REFERENCES   CUSTOMER 

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

    312	 III.   

             
  .     . 

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

         ,   . 
   ,        
          
 .  ,       
   ,  Delphi  , 
  . 

      

       ,   .  
 ,      ,   
 . ,     
 INVALID_STATE     ISQL  . 

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

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

      

       SQL   CREATE  INDEX.    . 

    CREATE   INDEX   SALE02   ON   SALE    (SaleDate) 

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

    CREATE UNIQUE INDEX SALE01 ON SALE (SaleNumber) 

      

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

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

           ITEM. 

    INSERT	INTO ITEM	(ItemNumber,	Description,	Price)	VALUES(1001,'Zoso LP',13.45) 
    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) 

     15. Microsoft SQL Server 313 

    ,      SALE.	1 

    INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber, ItemNumber, 
Amount)  VALUES(101,'10/18/90',1,1001,13.45) I 
    INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber, ItemNumber, 
Amount) I , VALUES(102, '02/27/92', 2,1002, 67. 90) I 
    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, : 

    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*.07) 


     DELETE 

           DELETE.   
 ,   . 

    DELETE FROM CUSTOMER 

     DELETE     WHERE    : 

    DELETE FROM CUSTOMER WHERE LastNameO'Doe' 


      

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

      

    1~       (DML  Data 
Manipulation Language),  INSERT, UPDATE  DELETE,   
      -^/|^DL: 
-^ipata^etio.itip^Larjguaae).   ,     

    314  III.   

    ,   .      
   " ;       
    - . ,      
 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,Amount+15  AmountPlusShipPING FROM   SALE 


      

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

    SELECT COUNT(*) FROM CUSTOMER 

          . 

SELECT  MAX(Amount)    FROM   SALE 

    ,       . 

    SELECT SUM(Amount) FROM SALE 


      WHERE 

      WHERE       
,   SELECT.   .  
   ,   .  (   ). 

    SELECT * FROM CUSTOMER WHERE State='MO' 

         ,     
    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 '10/18/90' AND '05/20/95' 
    
     15. Microsoft SQL Server 315 

     

      WHERE      (join) 
    |    .  
      WHERE -|      
  SELECT.       
SELECT   FROM,      
^       (=)   , 
     WHERE  ,   
  .  : 

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

       ,  ,      
FROM,   s;   ,     CUSTOMER  
SALE       CustomerNumber. , 
    ,   (outer)  | 
 (inner).      (left)   
(right) ,    1     (left 
join)        SQL. 

          

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

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

    ANSI-  

      ,       SQL  
Server ^ ANSI-.      
  . 

    SELECT   CUSTOMER.CustomerNumber,    SALE.Amount FROM   CUSTOMER   LEFT   
JOIN   SALE ON  CUSTOMER.CustomerNumber=SALE.CustomerNumber 

          . 

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

           LEFT  
RIGHT.  ,         
    ,     :   . 

      

      (multi-tier join)  , 
          
   ,         
.   . 

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

        CUSTOMER  SALE      
;-CustomerNumber.     ,  ItemNumber, 
  SALE  ITE:        
   . 

    316	 III.   316	 III.   

     

        ,     
   .      
(reflexive)   (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   32 
    WHERE  C.CustomerNumber=S.CustomerNumber 
    AND  .CustomerNumber<>S2.CustomerNumber 
    GROUP  BY  C.CustomerNumber,   S.Amount 

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

      

       (full join)      
 ,      WHERE.  
         . 
         ,   ,  
         .  
  : 

    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 

    	        
 .  ,       
  .       
,    . 

     15. Microsoft SQL Server     317 

      

      (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,        
 ,     SELECT.  
  GROUP BY       SELECT 
    ; SQL Server      
 GROUP BY,     . 

    HAVING 

      HAVING  SELECT     
 ,      GROUP BY.  
    GROUP BY     SELECT 
   WHERE,   ,  HAVING    
   ,     . 
           ,    
  .  ,  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 

    318	 III.   318	 III.   

      

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

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

            
 ,       .   
,    ,    . 

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

             LastName 
  LName.  ,    
    , ,   WHERE  GROUP BY  
      . 

      

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

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

     ,        
 SELECT     .   
 ,          
 . 


     

     (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,   
,   ,  WHERE ,   
      . 

     15. Microsoft SQL Server 319  15. Microsoft SQL Server 319 

     SELECT,   ,    
   , ,   SELECT,   :    
   ORDER       SQL 
Server,     Sybase, InterBase  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 Server: 

    CREATE  PROCEDURE  listcustomersbystate(@State   varchar(2),   
@LastNameMask  varchar(SO)) 
    AS 
    BEGIN 
    SELECT LastName FROM CUSTOMER 
    WHERE State=@State AND LastName LIKE @LastNameMask END 


     

            (Data 
Definition Language --DDL),     ,   
  (script) SQL.    ,    
 ;     SQL  
    . ,     
     USE   .   SQL 
Server       Load ISQL Script   
 ISQL. ,   ,    15.1, 
  ,   ,     DIR. 

     15.1.  SQL-,    sp__dir 

    use master 
     
    /* 
    * DROP PROC dbo.sp_dir 
    */ 
    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 Smask 
varchar(30) = '%', @obtype varchar(2) = 'U', Sorderby 

    320	 III.   

    varchar(3)='/N' as /* 
     ,     , 
  DIR DOS. 
      : 
    Omask =     (    
). @obtype =   (    ). 
    @orderby = ,      . 
   . 
    /N  =	  . 
    /R  =	   . 
    /S =	  . 
    /D =	   . 
    /DS =	    . 
    /IS =	    . 
    /US =	    . 
    /RL =	    . 
    /  =	  . 
         . 
            
 , : 
    sp_dir 'TRA%', 'U', '/S' 
         , : 
    sp_dir @mask='TRA%',@obtype='U',@orderby='/S' 
       .     
 : 
    sp_dir '%','U','/N' 
    Written by Ken Henderson 3/13/97 3:39PM 
    V 
    CREATE TABLE isp_dir( 
    id	int	NOT NULL, 
    name	varchar(30)	NOT NULL, 
    type	char(2)	NOT NULL, 
    date_created	datetime	NOT NULL, 
    row_count	int	NOT NULL, 
    row_len_in__bytes	int	NOT NULL, 
    total_size__in_KB	int	NOT NULL, 
    data_space_in_KB	int	NOT NULL, 
    index_space_in_KB	int	NOT NULL, 
    unused_space_ in_KB	int	NOT NULL, 
    owner ~    ~	varchar(30)	NOT NULL) 
    insert into lsp_dir select o.id, 
    .name, 
    o.type, 
    date_created=	o.crdate, 
    row_count =	isnull(rows,0), 
    row_len__in_bytes=       isnull((select sum(length) from syscolumns where 
id=o.id and o.type" in ( ' U ' , ' S ' ) ) , 0) , 
    total_size_in_KB =       isnull((select sum(reserved) from sysindexes 
where in-did in (0, 1, 255) and id = o.id),0)*2, 
    data_space_in_KB =      isnull(((select sum(dpages) from sysindexes where indid 

     15. Microsoft SQL Server     321 11  Delphi 3   / 

< 2 and id = .id)+
(select isnull(sum(used), 0) from sysindexes where indid = 255 and id = o.id)},0)*2,
index_space_in_KB =     isnull(((select sum(used) from sysindexes where indid in (0, 1, 255") and~id~= o.id) -
((select sum(dpages) from sysindexes where indid < 2 and id = o.id)+
(select isnull(sum(used), 0) from sysindexes where indid = 255 and id = .id))),0)*2,
unused_space_in_KB =    isnull (( (select sum(reserved) from sysindexes where ir.-did in (0, 1, 255) and id = o.id) -
(select sum(used) from sysindexes where indid in (0, I, 255) and id = o.id)),0)*2,
owner=	user_name(o.uid) from sysobjects o, sysindexes i
where o.name like @mask and o.type like Sobtype and o.id*=i.id and i.indid<=l
select @orderby=upper(@orderby)
if @orderby = '/N'
alter table #sp_dir add constraint PK_dir primary key clustered (name,id) else if @orderby = '/R'
alter table #sp_dir add constraint PK_dir primary key clustered (row_count,name) else if @orderby = '/S'
alter table #sp_dir add constraint PK_dir primary key clustered (total_size_in_KB,name) else if @orderby = '/D'
alter table #sp_dir add constraint PK_dir primary key clustered (date_created,name) else if @orderby = '/DS'
alter table #sp_dir add constraint PK_dir primary key clustered (data_space_in_KB,name) else if @orderby = '/IS'
alter table #sp_dir add constraint PK_dir primary key clustered (index_space_in_KB,name) else if Sorderby = '/US'
alter table #sp_dir add constraint PK_dir primary key clustered (unused_space_in_KB, name) if @orde~rby =~'/RL'
alter table #sp_dir add constraint PK_dir primary key clustered (row_len_in_bytes,name) else if @orderby = '/0'
alter table #sp_dir add constraint PK_dir primary key clustered (owner,name)
alter table	#sp_dir	add sequencer int identity
alter table	#sp_dir	drop constraint PK_dir
alter table	#sp_dir	add primary key clustered (sequencer)
insert into #sp_dir
(id,name,type, date_created,row_count,row_len_in_bytes,total_size_in_KB,data_ space_in_Kb
,index_space_in_KB,unused_space_in_KB,owner)
select power(2.0,31)-
1,'TOTAL:','NA',getdate(),row_count=sum(row_count),row_len_in_bytes=max(row_len_in_byte
s),total_size_in_KB=sum(total_size_in_KB),date_space=sum(data_space_in_KB) ,
index_space_in_KB=sum(index_space_in_KB),unused_space_in_KB=sum(unused_space_in_KB) , 'NA

322	 III.  

    from #sp_dir 
    select name,type,date_created, row_count, row_len_in_bytes, total 
size_in_KB, data_space_in_KB, index_space_in_KB, unused_space_in_KB, owner 
from #sp_dir 
    drop table #sp_dir 
    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 

          ,    
   ,        
  . 

      
          -,    . 

       

      SQL Server     EXECUTE, 
'   . 

    EXECUTE  _  

     EXECUTE    ,    ,   
     . ,  listcustomers 
       ISQL  . 

    listcustomers 

            ,    : 

    exec listcustomersbystate 'MO','%' 

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


     

     (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. 

     15. Microsoft SQL Server 323 

        INSERT  UPDATE    
    inserted.     
      ,       
   ,      . 

     

     (cursor)        
 SQL     .     
    .       
SQL,     BDE,     
     . 
         : , , 
   .        
  . 
        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    (bidirectional 
cursor)        .  BDE  
         
,     .      
,  TQuery  ,    ,   . 

    ,    ,    
    UPDATE  DELETE.   
    FOR UPDATE OF.    . 

    DECLARE CUSTOMER_UPDATE 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 CUSTOMER UPDATE 

    324  III.   

     

    DELETE FROM CUSTOMER 
    WHERE CURRENT OF CUSTOMERJJPDATE 

           CLOSE.   : 

    CLOSE CUSTOMER_UPDATE 

           ,   
.     DEALLOCATE: 

    DEALLOCATE CUSTOMER UPDATE 

     

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

     ... 

      ,   16, "Oracle"     
  Delphi      Oracle. 

     15. Microsoft SQL Server 325