    24  

       SQL 

           5, "SQL  
 ".    SQL    
 ,     ,   
       SQL.     ,  
     SQL,   5  . 
    ,    ,       
  SQL    -   SQL. 
      InterBase    WISQL.  
     . 
            ,    
5.   ,       .  
       5. 
     ,    ,     
" "  .        
   ,        
     8, "   /". 
            SQL: ANSI  
Sybase.   InterBase   ANSI SQL.  , 
    InterBase,      
  ANSI ,   Oracle.    , 
 Sybase SQL Server,     "" Microsoft SQL 
Server,       ANSI. 

      
            SQL,  
       15-18.    - 
Oracle, InterBase, Sybase  Microsoft SQL Server -   , 
   ,    SQL   . 
         ,   
     .       
  SQL.       
,      .   
   .   SQL    , 
     .      
 ,     -  . 
     ,       SQL.  , 
   SQL   ,   SQL   SQL.   
      ,    
     -.    
  SQL,    ! 

    448 IV.    

    DDL DML 

          SQL:    
 (DDL  Data Definition Language)     (DML  Data 
Manipulation Language).  DDL      
   . , CREATE TABLE    DDL.  DML 
      .   DML 
  SQL UPDATE.     DDL,   DML,  
     .    SQL   DDL, 
  DML,  ,         SQL. 
         . 

      DDL 

              
.        ,    
            
 . 
          5,  CREATE DATABASE  
    .      , 
    ,  .    
        
   (   )     
 .     ,   
ALTER DATABASE.   InterBase. 

    ALTER DATABASE 
    ADD 'C:\DATA\IB\ORDENT2' 

              
.  Sybase  Microsoft SQL Server   , : 

    ALTER DATABASE ORDENT ON ORDENT2=100 

      Sybase  Microsoft SQL Server  ALTER DATABASE  
      ,     
     . 

        

            
    ,     
   .   Microsoft  Sybase SQL Server    
  (segment)  .     ,  
    ,    , 
      . 
    |	    Sybase SQL Server (System 10    
)    . 
    1.         (  SQL Server 
    (device))    DISK INIT. 

    DISK INIT 
    name ="INDDEV", 
    physname="SYS:DATA\INDDEV.DAT", 
    vdevno=25, 
    size=51200 
    2.            ALTER DATABASE. 
    I 
    ALTER DATABASE ORDENT ON INDDEV=100 

     24.    SQL	449 
    15 Delphi 3   / 

    3.            
 sp_addsegment. sp_addsegment   'indexeseg',    'ORDENT',    'INDDEV 
      'indexeseg'     ,  'ORDENT'       ' 
INDDEV       . 
    4.           . 
,          . 
    CREATE   INDEX   INVOICES02   ON   INVOICES   (CustomerNumber)   ON   indexeseg 
      INVOICES   INVOICES02    
 , ,   ,       
          INVOICES02. 
     Oracle           
(tablespace)   .     CREATE 
TABLESPACE,            
CREATE TABLE  CREATE INDEX. ,     
 . 

    CREATE TABLESPACE indexspace 
    DATAFILE 'c:\oracle\indexspace.dat' SIZE 500K REUSE 
    AUTOEXTEND ON NEXT 50OK MAXSIZX' 10M; 

           . 

    CREATE   INDEX  emp02   ON  emp   (ename,    job)   TABLESPACE   indexspace; 

           ,  
    .        
 ,        
    /,     
    . 
            
    ,      
.     ,  
  . 
    Q         . 
           
,     ,     
  ,       
    .     
    ,     
   . 
    Q        . 
           ,    
 . ,          
 .     ,   ""  
      ,   
 . (     "  
 "    .) 
    Q           
,    ,      
.           
 ,   ,     
     :       
 ,     ,     
     . 
    ,  InterBase   ,    
     .      
      . 

     

      SQL  (   )    
CREATE  INDEX.    : 
    CREATE INDEX INVOICES02 ON INVOICES (CustomerNumber) 
     INVOICES02    , INVOICES   ,   
 ,  
    CustomerNumber   . 

    450 IV.    

     InterBase         ,  
  .   Sybase  Microsoft SQL Server    
     . 

      

             
CREATE UNIQUE INDEX, : 
    CREATE  UNIQUE   INDEX  INVOICES01   ON   INVOICES   (InvoiceNo) 

      

          .  InterBase  
   DESCENDING    .  : 
    CREATE   DESCENDING   INDEX   INVOICES03   ON   INVOICES    (Amount) 
      ,  ,   . 
    SELECT * FROM INVOICES ORDER BY Amount DESCENDING 

        

     InterBase    ,      
 .   ,     
         
   .      
 : 

    ALTER INDEX INVOICES03 INACTIVE 

       ,     INACTIVE  ACTIVE. 

    ALTER INDEX INVOICES03 ACTIVE 

          . 
      ,    . 

       

          .   
      ,   
       . 
      Sybase  Microsoft SQL Server     
   identity.       
,   ,      
.      . 

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

          ,   
  . InterBase     
   .    InterBase   
,   . 

    CREATE  GENERATOR  CustomerNumbergen; 

              
   ,   . 

    SET TERM !! ; 'CREATE TRIGGER CUSTOMERInsert FOR CUSTOMER 

     24.    SQL	451 

    BEFORE INSERT POSITION 0 AS BEGIN 
    NEW.CustomerNumber=GEN_ID(CustomerNumberGEN, END SET TERM ; !! 

      ( )     
         Sybase, 
   Microsoft SQL Server.    ,     
    (page-based locking model). ( 
 Microsoft SQL Server    Insert Row Locking,   
     .)   
    ,       
  ,       ,  
  ,       .  
,  ,   ,    
customerNumber   1000,     
CustomerNumber=1001  ,          
    .        
 , ,  StreetAddress   ,   
   . ,  ,  
     (, Oracle),    . 
      Oracle        
,   (sequence).   
  Oracle   . 

    CREATE SEQUENCE CustomerSEQ; 

       InterBase,      
     Oracle PL/SQL. 

     

     (view)     SELECT,  
  ,    .  ,  
  -        
SQL-.     ,    
.        SELECT, 
   ,  ,    
,      ,     . 
         CREATE VIEW.  
      42.1. 

     24.1.    CREATE VIEW 

    CREATE   VIEW   NEWLEASES   AS 
    SELECT   * 
    FROM  LEASE 
    WHERE  MovedlnDate   >   "12/01/96" 

            ,   
 , : 

    SELECT * FROM NEWLEASES 

     SELECT,  ,      
,     SELECT,     
 ORDER BY.      /, 
 InterBase, Oracle, Sybace  Microsoft SQL Server. 
          
.     InterBase  
  . 
       SELECT      ,    
 . 

    452 IV.    

           INSERT,  
 ,   ,   
 NULL. 
    Q   SELECT    ,  DISTINCT, 
  HAVING,  ,  , 
     . 
       ,      
      , 
 .     CREATE VIEW  
  WITH CHECK OPTION   / 
    CREATE VIEW OKPROPERTY AS SELECT * FROM PROPERTY WHERE State="OK" WITH 
CHECK OPTION 
          ,   state 
   "",  . 
      Microsoft  Sybase SQL Server      
,         
.       .   
,      SQL Server. 
          SELECT,   ,    
    

    ORDER   BY, COMPUTE  INTO     UNION. 

    	      . 
    Q	      . 
    Q	         
 Readtext  Writetext. 
         . 1 
          DELETE. 
    Q       ,    
,    NULL,    
  INSERT. 
    Q  ,    ,   
 INSERT. 
          ,    
  DISTINCT  
    WITH CHECK OPTION    INSERT. 
    Q   ,      DISTINCT,  
  UPDATE. 
        ,    ,  
     . 
              . 
    Q  ,   ,    . 

      

               
 SQL,   ,   .  , 
 ,    ,  SELECT, 
 ,     . 
      (dynamic view)   , 
   SELECT       
.    24.1       
,    .  (    ).   
    ,    
 ,    .    ,  
  ,  ,   
        .  , 
   Transact-SQL Sybase/Microsoft: 

    CREATE VIEW CONTACTLISTV SELECT * FROM CONTACTLISTV WHERE EnteredBy=suser name() 

     24.    SQL	453 

    EnteredBy     CONTACTLIST,     
,   .       
       DEFAULT.  
    ,     
  suser_name ().  ,    
        
CONTACTLIST,    .     
  ,         
      . 

        

        ,      
      .      
,    , .     
 : ,  ,   
     . 
             
 ,             
 ,         
.  ,   Sybase    
         . 
       ,       
.      ,    
      .   
           
 ,  ,      
       GRANT  REVOKE  
 .  ,     
 ,  ,  ,    
  ,     .    
     ,      
 ,   . 
           
.    /    
     .   Oracle 
  EXPORT  IMPORT,   Sybase  Microsoft SQL Server   
 (Bulk Copy Program).    SQL Server,  
,   ,   ,     
         ASCII.   
 ,      , 
      ,     
    .        
   ASCII-  -  . 

      

            SQL.  
      SQL      
   .     :  
   .      
   SELECT.  ,     , 
   - .     
    . 
          CREATE PROCEDURE.  
     InterBase. 

    CREATE PROCEDURE CLEARCALLS 
    AS 
    BEGIN 
    DELETE   FROM  CALLS  WHERE   CallDateTime  <   '01/01/96'; END 

         Transact-SQL  SQL Server  
 . 

    CREATE PROCEDURE CLEARCALLS AS 
    DELETE FROM CALLS WHERE CallDateTime < '01/01/96' 

    454 IV.    

          ,    
 .   InterBase: 

    CREATE  PROCEDURE  CLEARCALLS   (BeginningDate  DATE) 
    AS 
    BEGIN 
    DELETE FROM CALLS WHERE CallDateTime < :BeginningDate; END 

         SQL Server   . 

    CREATE PROCEDURE CLEARCALLS (@BeginningDate datetime) AS 
    DELETE FROM CALLS WHERE CallDateTime < @BeginningDate 


      

      ,       
     RETURNS. : 

    CREATE PROCEDURE LISTPROP (State char(2)) 
    RETURNS (PROPERTYNO INTEGER, 
    ADDRESS VARCHAR(30)) 
    AS 
    BEGIN 
    FOR SELECT PropertyNo, Address 
    FROM PROPERTY 
    WHERE State = :State 
    INTO :PropertyNo, :Address 
    DO 
    SUSPEND; END 

     FOR SELECT... DO      
 .  SUSPEND     
 ,       .  
   ,    . 
             
SQL.          
 .      CONNECT  SET 
TERM.       Run an ISQL Script 
  WISQL.      24.2. 

     24.2.  ,     SQL 

    CONNECT "C:\DATA\RENTMAN\RENTMAN.GDB" USER SYSDBA PASSWORD masterkey; 
    SET TERM A ; 
    /*   */ 
    CREATE PROCEDURE LISTPROP 
    RETURNS (PROPERTYNO INTEGER, ADDRESS VARCHAR(30)) 
    AS 
    BEGIN 
    FOR SELECT PropertyNo, Address 
    FROM PROPERTY 
    INTO :PropertyNo, :Address 
    DO 
    SUSPEND; END 
    SET TERM ; 

     CONNECT         
.  SET TERM      
SQL:        (;)    
 ().     ,   
 ,      CREATE PROCEDURE.  
  CREATE PROCEDURE    SET TERM 
       SQL. 

     24.    SQL	455 

      

           ,    
    RETURNS.      InterBase: 

    CREATE   PROCEDURE   insertWORKTYPE   (WorkTypeCode   smallint, 
    Description char(30),   TaskDuration  float) 
    AS 
    BEGIN 
    INSERT   INTO  WORKTYPE  VALUES    (:WorkTypeCode,    .-Description,    
:TaskDuration); END 

        Transact-SQL  SQL Server   
 . 

    CREATE   PROCEDURE   insertWORKTYPE   (@WorkTypeCode   smallint, 
    @Description  char(30),   @TaskDuration   float) 
    AS 
    INSERT INTO WORKTYPE VALUES (@WorkTypeCode, SDescription, @TaskDuration) 


      

     SQL Server,   Oracle,   , 
       . 
  PL/SQL  Oracle     
.    . 

    CREATE FUNCTION get_bal(CustomerNo IN NUMBER) 
    RETURN NUMBER 
    IS 
    custbal   NUMBER(11,2); BEGIN 
    SELECT  balance 
    INTO  custbal 
    FROM  CUSTOMER 
    WHERE   CustomerNo  =  get_bal.CustomerNo; RETURN(custbal); END 

      SQL Server        
.   ,  -    
 .    Sybase: 

    CREATE   PROCEDURE   get_bal   @CustomerNo   int,   SBalance  money=NULL  
OUTPUT AS 
    SELECT  SBalance  =  Balance 
    FROM   CUSTOMER 
    WHERE   CustomerNo  =   @CustomerNo RETURN  100  *   SBalance  /*  
    .   */ 

             
  @1    RETURN. ,   
    ,    
Balance   100,         
   . 
       ,    .   
     . 

    declare @mybal money 
    select @mybal = null 
    exec @mybal = get_bal @CustomerNo=3 /*   ,   
 3. */ 
    select @mybal / 100 

     ,      100,  
      . 
       -   SQL Server   
       . 

    declare Smybal money select @mybal = null 

    456	IV.    

    exec  get_bal   @CustomerNo=3,   Smybal  OUTPUT select   Smybal 

         OUTPUT.     
,       .  
,     100 ,   SQL Server 
       . 

     

     Oracle       
,            
.   ,      
,    ,     
   .       Oracle: 

    CREATE   OR  REPLACE   PACKAGE   CustomerPKG  AS 
    FUNCTION  addcust(CustomerName  VARCHAR2,   Address  VARCHAR2, City 
VARCHAR2,   State   CHAR(2),   Zip  CHAR(10)) RETURN  NUMBER; 
    PROCEDURE  deletecust(CustomerNumber NUMBER); Invalid_State  EXCEPTION; 
Invalid_City  EXCEPTION; END  CustomerPKG 

       Oracle     
   .       Delphi  
interface  implementation.  CREATE PACKAGE   
 .     ,   
,         . 
  CREATE PACKAGE BODY     
 . 

    CREATE   OR  REPLACE   PACKAGE  BODY   CustomerPKG  AS tot_custs  NUMBER; 
    FUNCTION  addcust(CustomerName  VARCHAR2,   Address  VARCHAR2, City  
VARCHAR2,    State  CHAR(2),   Zip  CHAR(10)) RETURN  NUMBER   is   
NewCustomerNo  NUMBER(4); BEGIN 
    IF  State   IS  NULL  THEN  RAISE   Invalid_State; 
    IF  City   is   NULL  THEN   RAISE   Invalid_City; 
    SELECT   CustomerSEQ.NEXTVAL 
    INTO NewCustomerNo 
    FROM   DUAL; 
    INSERT   INTO  CUSTOMER   (CustomerNo,   CustomerName,   Address,   City,  
 State,   Zip) 
    VALUES   (NewCustomerNo,   CustomerName,   Address,   City,   State,   Zip); 
    tot_cust   :=  tot_custs   +   1; RETURN(NewCustomerNo) ; END; 
    PROCEDURE  deletecust(CustomerNumber  NUMBER)    IS BEGIN 
    DELETE   FROM   CUSTOMER 
    WHERE  CUSTOMER.CustomerNumber  =  deletecust.CustomerNumber; 
    tot_custs   :=  tot__custs   -   1; END; END  CustomerPKG 

            
,   ,  ,  ,   
,   ,      
,     .    
  ,        
,    ,      
  .    ,     
 ,      ,  
    ,     
 SQL-. 

     24.    SQL	457 

      

         InterBase, Oracle  
Sybase.  InterBase       
 CREATE EXCEPTION,       EXCEPTION.  Oracle  
      
 DECLARE. . .EXCEPTION   RAISE    .  Sybase 
      ,    
      ,  
 RAISERROR.       
,         
RAISERROR      .    
  InterBase   ,   . 

    /*  . */ 
    CREATE EXCEPTION CREDIT_TOO_HIGH 
    "   .       $5000."; 
    /*  SQL  ,   . */ 
    CONNECT "C:\DATA\IN\CASINO"; 
    SET TERM ; 
    CREATE PROCEDURE insertCREDIT (VoucherNumber smallint, VoucherDate date, 
    CustomerNumber int, Amount float) 
    AS 
    BEGIN 
    IF (:Amount > 5000) THEN 
    EXCEPTION CREDIT_TOO_HIGH; 
    ELSE 
    INSERT INTO CREDIT 
    VALUES (:VoucherNumber, :VoucherDate, :CustomerNumber, :Amount); END  
    SET TERM ;  EXIT; 

          $5000. ,  
 IF. . .THEN    ,   
  Amount     $5000.  EXCEPTION 
     CREDIT_TOO_HIGH,  
 Amount  $5000. 
       SQL Server      (   
,         ,   
 ). 

    sp_addmessage 20001, 
    "   .       
$5000." GO 
    use CASINO GO 
    CREATE   PROCEDURE   insertCREDIT   (@VoucherNumber   smallint,   
@VoucherDate   date, SCustomerNumber  int,   @Amount   float) AS 
    IF  @Amount   >   5000 
    RAISERROR   20001 ELSE 
    INSERT   INTO   CREDIT 
    VALUES   (@VoucherNumber,   @VoucherDate,   @CustomerNumber,   @Amount) GO 

       ,   SQL Server    
   ,   Delphi Format    printf 
().  ,        
 . 

    st_addmessage 50010, 
    "  %1  .       $5000." 
    GO 
    use ORDENT 
    GO 
    CREATE PROCEDURE insertCREDIT (@VoucherNumber smallint, @VoucherDate date, 
    @CustomerNumber int, @Amount float) 

    458            IV.    

    AS 
    IF  @Amount   >   5000 
    RAISERROE   50010,    @Amount ELSE 
    INSERT   INTO  CREDIT 
    VALUES   (@VoucherNumber,   VoucherDate,   gCustomerNumber,   @Amount) 

         %1.   Sybase  
  20   .   
    RAISERROR.  @Amount  
   .   ,     
 ()  RAISERROR,  ,   
        
  . 
     Sybase SQL Server     
     ,   
 . : 

    ALTER  TABLE   INVOICES ADD   CONSTRAINT   INVALID_CUSTOMER FOREIGN  KEY   
(CustomerNumber) REFERENCES   CUSTOMER(CustomerNumber) GO 
    exec   sp_addmessage   50010,    "           
 .         ." GO 
    exec   sp_bindmsg   INVALID_CUSTOMER,50010 GO 
            sp_bindmsg. 
    ,   ALTER 
TABLE,  ,   
    sp addmessage. 


     

       ,    SQL,  , 
     .     
 ,    InterBase   
  SQL,         
.    -   : 
,   .    InterBase: 

    CREATE   TRIGGER   DELETEJDETAIL   FOR  WORDERS ACTIVE  BEFORE   DELETE AS 
  BEGIN 
    DELETE   FROM  WODETAIL 
    WHERE  WODETAIL.Work_Order_Number=OLD.Work_Order_Number; END 

              
        .  
      (cascading delete)  , 
   ,    ,    
  . 
          OLD.   
         UPDATE  
DELETE.   NEW       
 INSERT  UPDATE. 
           BEFORE.  
   (before)   (after)  INSERT, 
UPDATE  DELETE. 
     InterBase         32768 
.   POSITION    .  
   POSITION    . : 
    CREATE TRIGGER GENERATE_WORKTYPECODE FOR WORKTYPE ACTIVE BEFORE INSERT 
-POSITION 0 '"AS BEGIN"'"" 
    NEW.Work_Type_Code  =  GEN_ID(Work_Type_CodeGEN,   1); END 

     24.    SQL	459 

     Sybase  Microsoft SQL Server    
  . 

    CREATE   TRIGGER   INVOICESDelete   ON   CUSTOMER FOR   DELETE   AS 
    DELETE   FROM  INVOICES  WHERE   INVOICES.CustomerNumber-(SELECT  
CustomerNumber   FROM  deleted) 

          deleted.  
        ,   
  ,     .  SQL Server  
  updated,     ,    
deleted,      . 
      SQL Server      ,  
    .     IF 
UPDATE (_),  _     .  
  ,    ,    
,   . 
        :     
.        DML, 
  ,     .    
  SQL Server.    ,  Oracle  
   ,    .   
   FOR EACH ROW  CREATE TRIGGER.   
      ,    
,        . 

     

               
SQL ALTER TABLE.   ,   Microsoft SQL Server,  
     . InterBase  Sybase,  
 ,   .       : 

    ALTER TABLE CUSTOMER ADD PhoneNumber char(10) 

         : 

    ALTER TABLE CUSTOMER DROP PhoneNumber 

     ,       NOT NULL  
,    ,      
    NULL. 

      
          Sybase 
SQL Server.         
,   Sybase       ,  
     Transact-SQL. 

      DML 

            (DML  
Data Manipulation Language),     SQL.  
  SQL,        
,     ,   DML.    
 DDL,       DML  
.    SQL    
,     ,  . 

    SELECT 

     SELECT      SQL.   
   ,  ,    
      SELECT.   
      SELECT. 

    460 IV.    

      

    ,      ,  
  (multi-tier join).   
    Table!   2,  
 2    .     : 

    SELECT  w.Work_Order_Number,   t.Name 
    FROM  WORDER  w,    LEASE   1,   TENANT   t 
    WHERE  w.Property_Number=l.Property_Number 
    and   1.Tenant__Number=t.Tenant_Number 

        WORDER  LEASE    
 Property_Number, a  LEASE  TENANT     
Tenant_Number.          . 

     

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

    SELECT   I.CustomerNumber,   I.Amount,    (I.Amount   /   
SUM(12.Amount))*100   Percentage FROM       INVOICES   I, 
    INVOICES   12, 
    WHERE   I.CustomerNumber=I2.CustomerNumber GROUP  BY  I.CustomerNumber,    I.Amount 

              
          . 
          
 .          , 
   INVOICES          
    .        
    . 

    - 

    - (theta join)      
   (  ).   -: 

    SELECT   .CustomerNumber,    0.Amount,    Sum(02.Amount)    OTHERS 
    FROM  CUSTOMER  C, 
    INVOICES   0, 
    INVOICES   02 
    WHERE  C.CustomerNumber=0.CustomerNumber 
    AND  C.CustomerNumber<>02.CustomerNumber 
    GROUP  BY  C.CustomerNumber,   0.Amount 

        .      
  CUSTOMER  INVOICES     . 
   -,    
  ,      .   
 INVOICES     ,    
   . 

      

      (Cartesian join)     
,       .  : 

    SELECT t.Name, w.Description FROM TENANT t, WORKTYPE w 

            
.         
   . ,     
   ,       
   . 

     24.    SQL	461 

    SELECT   INTO 

     Sybase  Microsoft SQL Server    
SELECT,        .  
          .   
 : 

    SELECT  w.Work_Order_Number,   t.Name 
    INTO   #MYWORKTABLE 
    FROM  WORDER  w,   LEASE   1,   TENANT   t 
    WHERE  w.Property_Number=l.Property_Number 
    and  1.Tenant_Number=t.Tenant_Number 

       ,    INTO     
   SELECT.     , 
  SELECT,   .      
  (    #   )  
      .     
INTO  SQL Server     Oracle  InterBase.   
INTO    Oracle  InterBase     
,     . 

      
        Oracle   . 

    CREATE TABLE MYWORKTABLE (Work_Order_Number, Name) 
    AS 
    SELECT  w.Work_Order_Number,   t.Name 
    FROM  WORDER  w,    LEASE   1,   TENANT   t 
    WHERE  w. Property_Number=l. Property_Number 
    and  l.Tenant_Number=t;Tenant_Nuraber 

        CREATE TABLE,    
   . ,   ,    
,       .   Oracle 
      . 

    INSERT 

         ,  INSERT  
       
. : 

    INSERT INTO WORDERBACKUP SELECT * FROM WORDER 

           , ,   
 ,   .     
   ,     INSERT. 

      
     Microsoft SQL Server     , 
      .   
  ,    INSERT,    
   . ,   InterBase    
,     INSERT    SELECT. 

    DELETE 

         DELETE,    
   DELETE       
  . ,      ,  
   .       

    462 IV.    

         ,  
    ,   
     ,    
,    ,    . 
         Sybase  Microsoft SQL Server    
    ,   TRUNCATE TABLE  
   DELETE,   .   
           
 . 

    UPDATE 

        UPDATE     
,      .    : 

    UPDATE CUSTOMER 
    FROM CUSTOMER , 
    ZIP Z 
    SET   C.City=Z.City,   .State=Z.State 
    WHERE   C.ZipCode  =   Z.ZipCode 

             
          . 

     

     (cusror)        . 
   ,   : DECLARE, OPEN, 
FETCH  CLOSE.  ,   ,   
  UPDATE  DELETE. 
         SELECT.   
 ,         
 .  : 

    DECLARE   c_PROPERTY  CURSOR FOR   SELECT   *   FROM   PROPERTY 

         ,    
.  OPEN  ,   . 
 : 

    OPEN c_PROPERTY 

    ,   OPEN        
 FETCH.   : 

    FETCH c__PROPERTY 

       FETCH     .  
     FETCH   .  
InterBase, Oracle  SQL Server      
          . 
  ,      . 

      
          ,   
,       Delphi. Borland Database Engine 
     Delphi,   , 
    . *:; 
    ,   ,    
    UPDATE  DELETE.  
       FOR UPDATE OF.  : 

    DECLARE c_PROPERTY CURSOR FOR SELECT * FROM PROPERTY FOR UPDATE OF Address 

     24.    SQL	463 

     ,      FOR UPDATE OF, 
   ,     . 
        . 
            
  WHERE CURRENT OF _. : 

    UPDATE PROPERTY 
    SET Address="357 Riverside Avenue" 
    WHERE CURRENT OF c_PROPERTY 

         . 

    DELETE FROM PROPERTY 
    WHERE CURRENT OF c_PROPERTY 

      CLOSE,   ,     
.  : 

    CLOSE c_PROPERTY 

      InterBase  CLOSE     , 
  .  Sybase     DEALLOCATE 
CURSOR.   : 

    DEALLOCATE   CURSOR      PROPERTY 


     SQL 

              
  SQL       
 SQL.     SQL     
  SQL,    ,   
     .    
     ,    , 
          
  SQL    . 
        . ,   
       ,    
   .       
 ,        
 SQL,           
   . ,       
 ,    .   "" 
 (   ),       
   .  :  
          
,   SQL. 
          :    
 SQL     . 

     SQL 

           ,    
   .     ,   
       
    .     . 

     ANSI 

     ,      SQL  
ANSI.      .   Sybase  
Microsoft SQL Server         
,    ANSI.   ,    
 ,     SQL   ANSI. 
          ,    
  ,       
.  ,         , 
  ,   ANSI SQL   
 ,      .  
,   ANSI,       
  . 

    464 IV.    

       

        SQL,      
,     .    ,  
       ,     
 ,    .     
  ,      ,   
      . 

      

            
 SQL.          
  .      , 
        . 

        INSERT 

        SQL  ,       
    INSERT    . 
       .   
,     ,  .     
       .  
,    ,  . 

    GROUP   BY 

     ,   ,     
  GROUP BY.    ,     
     . ,   
   ,      
     GROUP BY. 

    WHERE 

         SQL,,   
   WHERE.      
  SELECT,      WHERE  
    "" . 

       

      SELECT        
       ,    
        AND. 
        
 .     ,    
  .    ,    
 ,     . 

      

        WHERE,    
 AND  OR,  .     
        
 . 

      

             
 .         
/   ,      
       . 

     

        ,   
    .     
.  ,    , 
         SQL,  
  .        
 .  ,     SQL  , 
    SQL    .   
  IBM      
(Structured English Query Language),     SQL. 
    SQL    . 

     24.    SQL	465 

         ,  
        - , 
       .    
          
  SQL.       SQL. 
     ,        
       .   
         
    , , ,   . 
              
 SQL.       , 
   ,   .   , 
    SQL     .   
     ,    
 . 

    BEGIN...END 

      BEGIN . . . END    ,   
 . ,   Transact-SQL  Sybase  Microsoft 
SQL Server   IF   BEGIN. . .END,    
IF     SQL.  BEGIN.. .END   
 IF    ,      
.          
     ,    
,    . 

     

         .  , 
       . 

     

          .  
      ,      
 . 

        

     Sybase  Microsoft SQL Server    
 CREATE INDEX,     (clustered 
index).   (nonclustered index)      
             
 ,        . 
          
 .  ,      
      ,   SQL Server 
   .         
 . 
             
 ,       
   ,   ,   
 .       
  ,   .       
   :   ,    - . 
          SQL Server   . 

    CREATE  CLUSTERED  INDEX  INVOICES01  ON   INVOICES   (OrderNumber)  
,   InterBase   . 

      
         Sybase  Microsoft SQL Server  
  Oracle.    ,     . 
 Oracle 8        "Index 
Only Tables"  ,       .  
       SQL Server. 
          ,   
        . 

    466	IV.    

      

            ,   
,     SELECT,    
      ,    
 .   (covered index)    , 
      ,      
 .  ,       
     ,   
   .  ,  ""   
   .     : 

    CREATE   INDEX  ORDER04   ON  ORDERS   (OrderDate,   Amount)   
    SELECT  OrderDate,   SUM (.   TotalAmount 
    FROM  ORDERS 
    GROUP BY OrderDate 

     ,       ,  
    Amount   . 

      SELECT 

          SQL SELECT.  
      SELECT 
       . 

      EXISTS 

           SELECT  
EXISTS. ,        , 
    ,   ,   
   . 

    IF (SELECT COUNT(*) FROM TABLENAME WHERE condition) > 0 

          ,   WHERE, 
  ,     IF.     
    .      . 

    IF EXISTS    (SELECT   *   FROM  TABLENAME  WHERE   condition) 

        ,    
  ,   .   
 ,   ,   , 
     . 

    SELECT DISTINCT 

      SELECT DISTINCT    
.  ,   ,   , 
     DISTINCT.   DISTINCT 
       ,  
 SELECT.  : "     
?      ?".   , 
       .   SELECT 
DISTINCT   ,     
,       .  SELECT 
DISTINCT    ,    
    ,    . 

    WHERE 

      WHERE     SQL,  
   .  SELECT   
  ,          
     .      
 SELECT,    WHERE,  . 

       

        WHERE    ,   
  .   SELECT,   
 WHERE,  ,   

     24.    SQL	467 

        .     
,       .    
       WHERE,  
   , , ,    . 
    ,     FROM  WHERE   
      .  ,  
  (       ), 
         
 .     (,  
Microsoft SQL Server    SET FORCE PLAN ON),    
     . 

          

              
,      WHERE.    
   .    InterBase,   
    : 

    SELECT * FROM ORDERS 
    WHERE CAST (OrderDate AS CHAR(8) 
    >=   '07/01/95' 

        CAST ()     . 
-,     ,  InterBase  
      . -,    
  InterBase    OrderDate. 

       LIKE 

        LIKE    ,  
   . ,    
 : 

    SELECT * FROM CUSTOMER 
    WHERE LastName LIKE "%Joh%on" 

       ,    LIKE, 
   ,       
.  , ,   ,  
    ,  Joh    : 

    SELECT * FROM CUSTOMER WHERE LastName LIKE "Joh%on" 

     ,    Joh,     
    ,    on. 
   :    , 
     LIKE,   
     .     
   ,       
  ,     . 

      

          ,  ,  
  .      
         
 .        
 ,        .  
     .  ,  ,  
 ,    ,      
 ,     . 
    , ,    ,    
 ,        
 .         
 ,   ,  .    
,       (,  10 
),  ,  ,     
  .      ,   
    : 

    SELECT UseDate,   SUM(Amount)   AmountSpent 
    FROM  RECEIPTS 
    WHERE  CardType  in   (SELECT  CardType   FROM CARDTYPE  WHERE  Type='I') 
    GROUP  BY  UseDate 

    468 IV.    

        ,    
,   ,    .   
   ,       
 CARDTYPE,        .  
,       . 
        ,     Delphi . 
       . 
    1.        TQuery,   SQL  
      ,   . 
    2.    ,        
 ,     ,    
   .      : 
    4,2,4,12,13,20' 
    3.         SQL    
  IN.      : 

    SELECT  UseDate,   SUM(Amount)   AmountSpent FROM  RECEIPTS 
    WHERE  CardType   in   (:CardTypeList) GROUP  BY  UseDate 

    4.        . 
             
       CARDTYPE.      
     . 
        ,      
,   . 

    SELECT   r.UseDate,   SUM(r.Amount)   AmountSpent FROM RECEIPTS   r,   
CARDTYPE    WHERE   r.CardType  =  c.CardType and  c.Type='I' GROUP  BY  UseDate 

               
 . 

    ORDER  BY 

         ORDER BY.  
     ,     
    ,  .  
    ,        
   ,   . ,   
  Sybase  Microsoft SQL Server    
  .  ,     
  GROUP BY.   SQL    
  ,      
   .       
   . 

    HAVING 

          HAVING   
   .      HAVING 
 ,  WHERE,       
      ,     WHERE  
 .      HAVING: 

    SELECT  CUSTOMER.LastName,   COUNT(*)   NumberWithName 
    FROM  CUSTOMER 
    GROUP  BY  CUSTOMER.LastName 
    HAVING  CUSTOMER.LastNameO'Citizen' 

             WHERE. 

    SELECT CUSTOMER.LastName, COUNT(*) NumberWithName 
    FROM CUSTOMER 
    WHERE CUSTOMER. LastNameO ' Citizen' 
    GROUP BY CUSTOMER.LastName 

     24.    SQL	469 

      ,      . 
  HAVING     ,  
    ,      
HAVING.   ,     . 
     HAVING      , 
   .      
   ,       ,   
    SELECT    WHERE.   
     ,     
  HAVING.  : 

    SELECT   CUSTOMER.LastName,   COUNT(*)   NumberWithName 
    FROM  CUSTOMER 
    WHERE   CUSTOMER. LastNameo' Citizen' 
    GROUP BY  CUSTOMER.LastName 
    HAVING  COUNT(*)    >   2 

       NumberWithName     
  ,   HAVING. 

      
          ,   , 
    ,     .   
,     , ,   
SELECT,  HAVING,    WHERE,      
 .          
 SQL,              
     . 

    COMPUTE 

     Transact-SQL  Sybase  Microsoft SQL Server  
   ,   SELECT,  
      SELECT.    
   COMPUTE.      
 SELECT          
      , ,  (). 
    SELECT CustomerNumber, Amount FROM ORDERS COMPUTE SUM(Amount) 
           . -, 
    ,     
 COMPUTE     . -, COMPUTE  
  ,     ,  
   . -,   ,    
     COMPUTE, : 

    0.Amount,   SUM(02.Amount)   TotalOrders 
    SELECT   0.CustomerNumber, FROM	ORDERS   0, ORDERS   02 GROUP  BY   0.CustomerNumber, 
    0.Amount 

         ,      
 .        ,   
  :         , 
     .   ,   
        COMPUTE. 

    GROUP   BY 

          . ,  
 : 

    SELECT  OrderDate,   CustomerNumber,   SUM(Amount)   TotalOrders 
    FROM  ORDERS 
    GROUP BY OrderDate, CustomerNumber 

    470 IV.    

     

    SELECT OrderDate, CustomerNumber, SUM(Amount) TotalOrders 
    FROM ORDERS 
    GROUP BY CustomerNumber,   OrderDate 

             ORDERS, 
    . 

    CREATE   INDEX  ORDERS03   ON  ORDERS   (CustomerNumber,   OrderDate) 

      ,    OrderDate,   
 ,  ,      
    . 

      

           
.      SQL    
     .  , 
          . 

       

       ,   ,  
,   .     
,        . 
       ,    
      ,      
 .  ,       
   . 
             
,    .        ,  
    .      
,      ,    
        .  
       ,   
  . 

    SET NOCOUNT ON   SQL Server 

       Sybase  Microsoft SQL Server   , 
     , ,  
  .  -     . 
       SQL Server   
  SET NOCOUNT ON.     
 SET NOCOUNT   ,   READTEXT,    
 DB-Library dbreadtext (),   .   
   ,   SET NOCOUNT ON  
          . 

       

            
  SELECT, ,  ,    
.         
  SELECT   .   
       SELECT.    
SQL  InterBase: 

    SELECT City, State, Zip INTO :City, :State, :Zip FROM CUSTOMER 

         ,   Transact-SQL  SQL Server: 

    SELECT   @City=City,    @State=State,    @Zip=Zip FROM  CUSTOMER 

           SELECT   
,         
 . 

     24.    SQL	471 

       

            
.    ,     ,   
     . 
    ,        , 
   ,        
 .     ,    
           
.        ,   
           
    .     
    CUSTOMER,      
   . 
              
  ,  ,      
  .         
   . ,       
  ,       ,  
  .       
    . 

      

     ,        
 SELECT  .  ,  SQL  
    .   
     ,   .   
  ,   ,    
     . 
         . ,   , 
        
  : Ford, General Motors  Chrysler.  ,  
  (, , ,   ..) 
    ,        
     . ,   
 ,    SQL,  : 

    SELECT  CarType,   Maker,   Sales  TotalSales FROM  BIGTHREESALES 

    ,         
   ( , Delphi   
    ).       
   .  ,    
,     ,     
 .  : 

    CREATE   TABLE   CARCROSS 
    (	CarType                                                   CHAR(10)       
       NULL, FordSales                           FLOAT    NULL, GMSales        
                                        FLOAT    NULL, ChryslerSalesFLOAT    NULL) 

    ,      ,  
  ,         
  .  : 

    INSERT  INTO  CARCROSS   (CarType,   FordSales) Select  CarType,   Sales   
FordSales FROM  BIGTHREESALES WHERE Maker='Ford' 

            
     ,   
 UPDATE       
, , : 

    SELECT Sales INTO :GMSales FROM BIGTHREESALES WHERE Maker='GM' AND CarType='SC' 

    472 IV,    

    UPDATE CARCROSS 
    SET GMSales:=GMSales 
    WHERE CarType='SC' 

           SQL Server: 

    UPDATE CARCROSS 
    SET GMSales=Sales 
    FROM SALES S, CARCROSS  
    WHERE Maker='GM' 
    AND S.CarType=C.CarType 

    , ,   .     
    SQL      
 .        
     (folding),   
(flattening), .        . 
              Ford. 

    INSERT   INTO  CARCROSS    (CarType,   FordSales) Select  CarType,   Sales 
  FordSales FROM  BIGTHREESALES WHERE  Maker='Ford' 

             General Motors. 

    INSERT   INTO  CARCROSS   (CarType,   GMSales) Select  CarType,   Sales  
GMSales FROM  BIGTHREESALES WHERE  Maker='GM' 

    , ,  Chrysler. 

    INSERT   INTO  CARCROSS    (CarType,    ChryslerSales) Select   CarType,   
Sales   ChryslerSales FROM  BIGTHREESALES WHERE Maker='Chrysler' 

        CARCROSS    ,  
  . 24.1. 

    SELECT	 FROM CARCROSS			__J 
    
    CarType	FordSales	GMSales	ChryslerSales	! 
    SC	1i45i(it32.e	NULL	NULL	;' 
    CO	1145632.8	NULL	NULL 
    FS	3U3U662.0	HULL	NULL	" 
    TR	2356232. 	HULL	NULL 
    SC	MULL	233*li5. 	NULL	i't 
    CO	NULL	1225U3.B	NULL 
    FS	HULL	3llt56SU.O	NULL	',': 
    TR	HULL	22333*5. 	NULL 
    SC	HULL	NULL	3200322.8 
    CO	HULL	NULL	2388622.8 
    FS	HULL	NULL	U335622.8 
    TR	NULL	NULL	5543321. 	':!: 

    . 24.1.  CARCROSS   

     24.    SQL 473 

     CARCROSS      . 

    SELECT  CarType,   SUM(FordSales)    FordSales,   SUM(GMSales) GMSales,   
SUM(ChryslerSales)   ChryslerSales FROM  CARCROSS GROUP  BY  CarType 

          . 24.2. 

    S3 WISQL32- (Untilled) on KHtN/puLis2: 
    Ffe   ?<          
    SELECT CarType, SUM(FordSales) FordSales, SUM(GMSales) GMSales, 
SUM(ChrjislerSales) ChryslerSales j* FROM CARCROSS	_ GROUP BV CarType 
         _> 
    CarType         FordSales 
    GMSales 
    ChryslerSales 
    1454432.8 145632.0 3431(662.0 2356232.0 
    2331)1(5.8 122543.0 3445654.0 2233345.0 
    3200322.0 
    2300622.0 
    4335622.0 
    5543321.0 

    . 24.2.  ,       

      ,       - 
   . 

        SQL 

       ,      
  (data warehouse)      
    .      
    .  ,   
       ,     .  
      ,    
  .       
    .        , 
   ,        ? 
,        .   
    ?        
 ?       , 
   ,        
 ? 
          ,   
    ,    
  ,    .    
,      (in-line summarization).  
    ,      
           ,   
  .       
   ,         
   . 

    474 IV.    

         . ,     ,  
        . 
      ,     
  .   ,    
,     ,    .  
,    (John Doe)      Foley 1 
,       .     
   , ,   Foley,    
  . 
     ,       ,  
     ,       
. ,       . 

    CREATE   TABLE   CARDTRANS 
    (CardNumber	char(20) NOT NULL, TransactionDate date NOT NULL, Location int 
NOT NULL, Amount float NOT NULL) 

    ,   SELECT,     , 
      ,   . 

    SELECT  CardNumber,   TransactionDate,   Location, -ffiyjCLLII. 
NumberTrans,   SUM(Amount)   Amount 
    FROM'CARDTRANS 
    WHERE  TransactionDate  between   '01/01/95'   AND   '02/01/95' GROUP  BY  
CardNumber,   TransactionDate,   Location 

              , 
   .    ,  
  . 

    CREATE  TABLE   CARDTRANS 
    (CardNumber	char (20) NOT NULL, TransactionDate date NOT NULL, Location 
int NOT NULL, NumberTrans int NOT NULL, Amount float NOT NULL) 

         NumberTrans.     
    ?      ,    
         .   
   NumberTrans   1.    
    DEFAULT.     NumberTrans 
   ,     
.         3,      
  ,        int.  
  ,   ,    
   ,   . 

    SELECT   CardNumber,   TransactionDate,   Location,   SUM(NumberTrans)   
NumberTrans,   SUM(Amount) 
    Amount 
    FROM  CARDTRANS 
    WHERE  TransactionDate  between   '01/01/95'   AND   '02/01/95' 
    GROUP  BY  CardNumber,   TransactionDate,   Location 

     ,        
  COUNT (*)       
 SUM (NumberTrans).        ,  
     ,      NumberTrans. 
    ~"5"          
    ,  .  
      CARDTRANS.  ,  
     .      
  "  ,     . 
    ,      ,    
  ,   Amount. ,     
 Amount   Sybase smallmoney,      
  money. 

     24.    SQL	475 

      
             
      TransactionDate.   ( 
 Sybase  Microsoft SQL Server)         
 .  ,       
TransactionDate     ,  .   
    ,      . 
       SQL Server   . 

    SELECT  CardNumber,   CONVERT(char(8) ,   TransactionDate, 
    Location,   SUM(NumberTrans)   NumberTrans, FROM   CARDTRANS 
    112) , SUM(Amount)   Amount 
    WHERE  TransactionDate  between   49950101'   AND   '19950201' 
    GROUP  BY  CardNumber,   CONVERT(char(8),   TransactionDate,    112),   Location 

         CONVERT    
 TransactionDate   CHAR (8).   SQL Server  
   CHAR  DATETIME.    
CONVERT, 112,     CCCCMMDD.     
  CHAR (8)    . 
         ,    
        .   
       ,  
      ,    . 

     

          ,   SQL.  
    SQL,      
   SQL.       SQL  
 SQL.      ,     
,     ,    . 

     ... 

            
/  Delphi     .  
   ,      
/    . 

    476 IV.    

