    17  
    InterBase 

             
   InterBase.     SQL  
InterBase,      InterBase  Delphi.  
          
InterBase. ,    .    . 

      

       InterBase      
  .        
Windows 95  Windows NT.    InterBase    
       . 
     InterBase  ,     
  Windows.     ,      
  InterBase Server 4.2   InterBase 4.2 (  
 InterBase Server 4.2   InterBase Server 4.2  Windows 
NT 3.51).   ,       
Windows 95  Windows NT ( Windows NT 3.51    
  ). 
             
      .   , 
,       Windows,   
   Startup Configuration.   Properties,  
         
. ,      Shutdown,   
 . 

       

       ,    ,  InterBase  
        . 
,      InterBase   
(WISQL, BDE  ..)       .  
    . 
         ,    
 ,      . , 
   TCP/IP,   HOSTS   TCP/IP- : 

    100.10.15.12     marketing 

           TCP/IP SERVICES 
,     InterBase: 

    gds_db  3050/tcp 

      InterBase    (   
   ).    TCP/IP  
      

     17. InterBase 343 

    WISQL       InterBase.  
,           
TCP/IP    NetBEUI  IPX/SPX   . 

       

             
   BDE       Delphi. 
     9,         
           
  BDE. 
     BDE      BDE Administrator,   
  Delphi Database Explorer.    
   BDE Administrator. 
    1.     BDE Administrator,     
 Databases      New.    
 New Database Alias     Database Driver Name 
 INTRBASE     . 
    2.          . 
    3.          Definition. 
    4.       SERVER NAME      
      .      
 GDB.       : 

    MIS:/data/interbase/accounting.gdb 

     MIS   , a /data/interbase/accounting.gdb     
,   . 
         InterBase     SERVER NAME 
  .          
: : \DATA\INTERBASE\ACCOUNT . GDB. 
    5.            USER NAME  
 ,      . 
          
   Delphi. 
           .     
 InterBase   .   ENABLE BCD, 
         
.    ,      
     (Binary-Coded Decimal  BCD)    
        DECIMAL  NUMERIC.  
 ENABLE BCD   FALSE ( ),    DECIMAL 
 NUMERIC    Delphi  ,    
       .      
,      ENABLE BCD  TRUE. 
           BDE Administrator  
  Apply. 

          InterBase 

          InterBase   Delphi 
 ,   . 
    1.          InterBase 
Communication Diagnostics,    InterBase 4.2.  
        Test,   
 DB Connection.   , ,    
  BDE.    BDE Administrator  
  .     DB Connection 
     ,   NetBEUI  Winsock  
  Test   .  -    
,     .       
  ,     . 

    344	 III.   

    2.         InterBase WISQL.   
,     Delphi -  , , , 
     BDE.    BDE 
Administrator     ( ,  ). 
    3.        InterBase WISQL   , 
    .      
TCP/IP,        PING, 
    Windows 95  Windows NT.   
   ,    HOSTS,    IP-. 
    4.         Windows NT   
,    net view \\_,   
_     Windows NT,     SQL.  
   ,    netuse 
\\_\1$,   _   
.     ,       . 
    Windows NT. ,  
      . 
    5.          PING  IP- 
 ,     ,  , ,   
 HOSTS.          
 HOSTS,     InterBase  
    .     HOSTS   
  : 

    100.10.15.12     marketing 

          PING  IP-  
,     ,          
HOSTS,    IP-.       
       HOSTS. 
    6.            PING   
,   IP-,     .   
  ,   IP- .  
     TCP/IP, 127.0.0.1,  
    TCP/IP.     
    .     ,   
    .     
   . 
    7.        WISQL  ,   PING  
,         TELNET, 
   Windows 95  Windows NT.    
 : 

    TELNET 100.100.100.100 

     

    TELNET  

     100.100.100.100 TCP/IP-  ,    . 
   TELNET  ,       
inet  . 
    8.     TELNET , a WISQL - ,    
       
 InterBase   . -,    
     TCP/IP SERVICES,   
  : 

    gds_db  3050/tcp 

    9.            ,   
      . 

     SQL 

     ,        
     SQL.   :      
  SQL  InterBase    ,  
    SQL.   ,   
,       InterBase WISQL  . 

     17. InterBase 345 

       

      , ,   ,    
      .  ,     
 .  SQL     CREATE DATABASE.  
     .    InterBase: 

    CREATE   DATABASE   'C:\DATA\IB\SALES'   USER   SYSDBA PASSWORD masterkey; 

     InterBase WISQL      , 
     Create Database  File.   
     WISQL  . 
    1.     Interactive SQL (WISQL)    
InterBase    Task  InterBase Service Manager. 
    2.      Create Database   File. 
    3.           (, 
C:\DATA\IB\SALES.GDB)   Database   Create Database.  
       ,    , 
    . 
    4.        .     
SYSDBA   masterkey. 
    5.       .        
   .        
  Connect to Database  File. 
       Database Options   Create Database  
   , ,    
  .     1024  (1 ),   
   2048, 4096  8192 (.. , , 
PAGE_SIZE=2048).     ,    
 ,       ,    . 
      WISQL     CREATE DATABASE,  
       ISQL.   
 ISQL     ,   WISQL   
.  ISQL     : \Program Files\Borland\ IntrBaseXBin. 

     

     InterBase       (shadows),  
         . 
        
   ,    . 
         CREATE SHADOW    
 .    : 

    CREATE  SHADOW  I   "sales.shd"   LENGTH  5000; 

           .  
      ,    . 
             
,         
. ,         , 
     . 
      CREATE SHADOW      
,        
- .  AUTO ,     
 ,     .  MANUAL 
,            
,             
   DROP SHADOW.  CONDITIONAL ,  
   -,  

    346  III.   

   .   ,   
  ,     ,     
   .      
CONDITIONAL      : 

    CREATE   SHADOW   I   CONDITIONAL   "d:\shadows\sales.shd" LENGTH  5000; 


     CONNECT 

     InterBase CONNECT       
.    : 

    CONNECT  _____ USER   "__" PASSWORD   "_" 

      _____       
 .      ,    
  .       : 

    CONNECT "C:\DATA\IB\SALES.GDB" USER "SYSDA" PASSWORD "masterkey"; 

         ,   
CONNECT,   DISCONNECT. 

    DISCONNECT ALL; 

     ALL        DEFAULT.   
WISQL,    CONNECT  DISCONNECT   (   
 ISQL),          
 Connect to Database  Disconnect from Database  File. 

      

             . 
        
      .     
      ,  .   
 SQL CREATE TABLE.    CUSTOMER    
  SQL  WISQL  . 

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

           SALE. 

    CREATE TABLE SALE 
    ( 
    SaleNumber      int       NOT NULL, 
    SaleDate        date, 
    CustomerNumber   int    NOT NULL, 
    ItemNumber      int    NOT NULL, 
    Amount	float 
    ) 

     , ITEM,   . 

    CREATE TABLE ITEM 
    ( 
    ItemNumber	int                 NOT  NULL, 

     17. InterBase	347 

    Description  char (30), Price        float ) 

      

      InterBase   CREATE TABLE   
 .  ,      , 
,  . 

    CREATE TABLE SALE_HISTORY EXTERNAL FILE 
    ':\DATA\IB\SALEHIST.DAT' 
    ( 
    SaleNumber     int    NOT NULL, 
    SaleDate      date, 
    CustomerNumber int   NOT NULL, 
    ItemNumber     int    NOT NULL, 
    Amount        float 
    ) 

         ,  InterBase  
 .   ,    .   
       ,    
InterBase, ,      .    
,     ,      
     InterBase    INSERT. . . SELECT. 

        

        CREATE TABLE  InterBase  
      (computed column). 
    ITEM,     
  . 

    CREATE   TABLE   ITEM ( 
    ItemNumber	int                 NOT  NULL, Description               char 
(30), Price                              float, 
    GovernmentPrice   COMPUTED  BY   (Price   -   Price   *   .15) ) 

        GovernmentPrice     15% 
  .        
,    ,    . 
,   ,       
CREATE TABLE ,     . 
           
        SELECT,   
        .  
    -,  - 
   SQL        
      . 

      

         ,   SQL  InterBase 
  ,     .   
    ,     
.    CUSTOMER,   : 

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

    348	 III.   

        [3]     
StreetAddress.    ,     
char (30).   , ,     
  . 
             
 .  : 

    CREATE TABLE CUSTOMERARRAY 
    ( 
    CustomerNumber        int       NOT NULL, 
    LastName	char(30), 
    FirstName	char(30), 
    StreetAddress         char(30)[1:3], 
    City	char(20), 
    State	char(2), 
    Zip	char(10) 
    ) 

        StreetAddress       
,   1 ( 0  )  3 ( 2). 
          ( 16 ).  
  : 

    CREATE TABLE HOURLY_SAMPLES 
    ( 
    SampleNo	int      NOT NULL, 
    SampleDate         date     NOT NULL, 
    WeeklySampleValues  int[7,24] 
    ) 

        WeeklySampleValues    , 
          . 

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

          

     ,  InterBase       
       : USER, TODAY 
 NOW.    USER   ,   
 ,    TODAY     
(  Delphi DATE),   NOW      ( 
 Delphi NOW).       . 

    CREATE  TABLE  REPORTLOG (ReportUser  varchar(20)   DEFAULT  USER, 
    ReportDate  date   DEFAULT   "TODAY", 
    ReportDateTime  date   DEFAULT   "NOW") 

     ,   "TODAY"  "NOW"     
,    USER  .     ,  
         
      "USER". 

     17. InterBase	349 

         

           
  SQL ALTER TABLE.      
     (,   
   Microsoft SQL Server),      
 InterBase.       . 

    ALTER TABLE CUSTOMER 
    ADD (PhoneNumber char (10)) 

        . 

    ALTER TABLE CUSTOMER DROP PhoneNumber 

     ,     NOT NULL    
,     ,      
    NULL. 
    
     

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

    ALTER  TABLE  CUSTOMER 
    ADD   PRIMARY  KEY   (CustomerNumber) 

         CUSTOMER   
,      CustomerNumber.   
    ,      
CustomerNumber.  ,       
 ,    NULL. 
          ,  
     ,   .  
   ,       
. ,      () 
   .       
        . 
   : 

    ALTER   TABLE   SALE 
    ADD  FOREIGN  KEY   (CustomerNumber)   REFERENCES   CUSTOMER 

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

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

         ,   . 
   ,        
          
 .  ,       
   ,  Delphi  , 
  . 

    350	 III.   

      

       ,   .  
 ,      ,   
 . ,     
 INVALID_STATE     WISQL  . 

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

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

      

       SQL  InterBase   CREATE INDEX.   
 : 

    CREATE   INDEX  SALE02   ON   SALE   (SaleDate) 

     SALE02    , SALE   ,    
, a SaleDate   .    ,    
InterBase       ,    . 
         CREATE UNIQUE INDEX  ,  
 : 

    CREATE  UNIQUE   INDEX  SALE01   ON   SALE   (SaleNumber) 

           ,   
InterBase     .    
  DESCENDING: 

    CREATE DESCENDING INDEX SALE03 ON SALE (Amount) 

         , , , 
 : 

    SELECT * FROM SALE 
    ORDER BY Amount DESCENDING 


        

     InterBase      
        .  
         
  .        
         
  .     . 

    ALTER INDEX SALE02 INACTIVE 

          . 

    ALTER INDEX SALE02 ACTIVE 

      ,      .  
,       ,   
         
   .   ,  
,     . 

      

     SQL INSERT       
InterBase.     VALUES  INSERT   
 ;        ,   
    .        
     ,  .  
     CUSTOMER (     
  WISQL  ). 

     17. InterBase	351 


    INSERT INTO CUSTOMER (CustomerNumber, LastName, FirstName, StreetAddress, 

City, State, Zip) VALUES(1,'Doe','John','123 SunnyLane','Anywhere', 'MO','73115') 

    INSERT INTO CUSTOMER (CustomerNumber, 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' )                                                                      
    ; 
    i        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) 

    ,      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)	I 

    INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber,	j 
    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) 


      

        ,      
     USER, TODAY  NOW. 

    INSERT INTO REPORTLOG VALUES(USER, "TODAY", "NOW")	I 


     UPDATE 

     SQL UPDATE    ,   
  .  UPDATE     WHERE, 
 ,     .   
 : 

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

       WHERE   UPDATE    , 
    , ,   ;     
        WHERE. 

    UPDATE CUSTOMER SET State='MO' 

    352	 III.   

            
 ,     . ,  
       ITEM   . 
   UPDATE   . 

    UPDATE ITEM 
    SET Price=Price+(Price*.07) 

        INSERT,   UPDATE    
     USER, TODAY  NOW. 

    UPDATE   SALE 
    SET Amount=Amount-(Amount*.25) 
    WHERE SaleDate >= "TODAY"-30 

              
 TODAY.     ,   , 
 30  . 

     DELETE 

           DELETE.   
 ,   . 

    DELETE FROM CUSTOMER 

     DELETE     WHERE    . 

    DELETE FROM CUSTOMER WHERE LastNameO'Doe' 


      

     (transaction)      . 
    SET TRANSACTION.  
,    ,       
 COMMIT,    ROLLBACK.     
  ,        COMMIT; 
      . 
     InterBase WISQL    ( 
  SET TRANSACTION)    .    
  ,        
.           
 Commit Work  Rollback Work  File. 

      
       ,   InterBase   
   Data Definition Language (DDL).     
     , ,   .  
      ,    , 
   .     
       ,     
  (Transaction Isolation Level  TIL). 

     SELECT 

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

    SELECT  CustomerNumber,   LastName,   State   FROM  CUSTOMER 

     17. InterBase 353     12 Delphi 3   / 

       

          SELECT    
   ,  ,   , 
  .    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 

      SQL 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' 


     

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

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

    354	 III.   

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

          

             
   (inner join).   
      ,    
.   ,    (outer join) 
     ,     
,       NULL. 

    ANSI-  

        ANSI-   . 

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

      INNER   .    , 
      InterBase  
  . 

    SELECT   CUSTOMER.CustomerNumber,    SALE.Amount 
    FROM   CUSTOMER,    SALE 
    WHERE   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  .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)   (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 

     17. InterBase	355 

             
 ,         
  .        
 SELECT   .    
  ,    SALES     
      .    
          . 

    - 

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

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

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

      

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

    SELECT   DISTINCT   .CustomerNumber,    S.SaleNumber FROM   CUSTOMER     
 FULL   JOIN   SALE   S ON   1=1 


      
         1=1,   
 InterBase        ON 
(  ,     ). 

      

      (Cartesian product)      
    .     
  ,      
   .  : 

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

            
 .  ,       
  .        
 ,     ,   
   .    
   ,    . 

      

      (subquery)   SELECT,   
  WHERE   SELECT.    
    ,      
  . 

    356	 III.   

    ;       SELECT   *   FROM  CUSTOMER 
    1      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?   InterBase (  SQL  ANSI) 
       GROUP BY   
 ,      SELECT. 
   GROUP BY      
 SELECT        
 InterBase        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      
  .    : 
    SELECT * FROM CUSTOMER ORDER BY STATE 

     17. InterBase	357 

      

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

    SELECT  CUSTOMER.LastName,   COUNT(*)   NumberWithName 
    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     .   
 ,          
 . 

     

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

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

               
,   . 

    SELECT * FROM MOCUSTOMERS 


    358                           III.   


         ,     
  WHERE    SELECT,   
,   ,     , 
        . 
     SELECT,   ,    
   ,     SELECT,   :   
    ORDER BY.      
  InterBase,    Sybase SQL Server, Microsoft SQL Server  Oracle. 
            
.   InterBase   
 . 
    Q   SELECT        
     . 
    Q    ,    ,  
  NULL,      INSERT. 
    Q   SELECT      , 
 DISTINCT,   HAVING,  , 
 , ,  ,   . 
         ,   
        
  ,   .  ,  
   ,        
       .     
  WITH CHECK OPTION   CREATE VIEW.   
 : 

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

             
  ,     State  ''. 

      

      (stored procedure)   
     SQL,      
   .   InterBase    
 :   (select procedure)   
 (executable procedure).      
SELECT    ,  ,   
,        . 
        CREATE PROCEDURE,    
 InterBase  . 

    CREATE PROCEDURE listcustomers 
    RETURNS (LastName CHAR(30), FirstName CHAR(30)) AS BEGIN 
    FOR SELECT LastName, FirstName FROM CUSTOMER 
    INTO : LastName., : FirstName 
    DO SUSPEND; END	* 

       - ,     
.    InterBase: 

    CREATE PROCEDURE listcustomer^sbystate (State char (2), 
LastNameMask"~cb.ar (30) ) RETURNS (LastName CHAR(30), FirstName CHAR(30)) AS BEGIN 
    FOR SELECT LastName, FirstName FROM CUSTOMER WHERE State = :State and 
LastName like :LastNameMask INTO :LastName, :FirstName DO SUSPEND; END 

     17. InterBase	359 

      

           RETURNS  , 
     . 

    CREATE   PROCEDURE   listcustomers    (State   CHAR(2)) 
    RETURNS(LastName   CHAR(30)) 
    AS 
    BEGIN 
    FOR SELECT LastName 
    FROM CUSTOMER 
    WHERE State=:State 
    INTO :LastName 
    DO 
    SUSPEND; END 

         FOR SELECT... DO  
     .  SUSPEND 
       .  
   ,   . 

      

            ,  
 ,       RETURNS.   
    InterBase SQL: 

    CREATE PROCEDURE insertitem 
    (ItemNumber Integer, Description Char(30), Price Float) AS BEGIN 
    INSERT INTO ITEM (ItemNumber, Description, Price) VALUES (:ItemNumber, 
:Description, :Price); END 

     

            (Data 
Definition Language  DDL),     ,    
(script) SQL.          
   SQL    
  . ,       
  USE  SET TERM.     SQL   
InterBase    Run an ISQL Script  File  
WISQL.       17.1. 

     17.1.     SQL- 

    CONNECT "C:\DATA\IB\SALES.GDB" USER "SYSDBA" PASSWORD "masterkey"; 
    SET TERM ; 
    CREATE PROCEDURE GET_CUSTOMER_BY_STATE (State CHAR(2)) 
    RETURNS (LastName CHAR(30)) 
    AS 
    BEGIN 
    FOR SELECT LastName 
    FROM CUSTOMER
    WHERE State = :State 
    INTO : LastName  
    DO	*"*".. 
    SUSPEND; END  
    SET TERM ; A EXIT; 

         CONNECT     
   .    SET TERM,   
   SQL (;)   .   , 
   ,     ,   

    360	 111.   

       CREATE   PROCEDURE. ,  SET   
TERM    . 

       

      InterBase    EXECUTE  PROCEDURE, 
   . 

    EXECUTE  _   

     _    ,  
    , : 

    EXECUTE PROCEDURE GET_CUSTOMER_BY_STATE "" 

          GET CUSTOMER BY_STATE  
  "". 

      

      (exception)      , 
 ,       
.       InterBase 
  CREATE EXCEPTION,   EXCEPTION   
    .   17.2 
    InterBase   ,  
  . 

     17.2.   InterBase      

    CONNECT "C:\DATA\IB\SALES.GDB" USER "SYSDBA" PASSWORD "masterkey"; 
    CREATE EXCEPTION SALE_TOO_LOW 
    "        $5."; 
    SET TERM ; 
    CREATE PROCEDURE insertsale (SaleNumber int, SaleDate date, CustomerNumber 
int, Item-Number int, Amount float) AS BEGIN 
    IF (:Amount < 5) THEN 
    EXCEPTION SALE_TOO_LOW; ELSE 
    INSERT INTO SALE 
    VALUES (:SaleNumber, :SaleDate, :CustomerNumber, 
    :ItemNumber, :Amount); END  
    SET TERM ;  EXIT; 

             ,  
  $5.      IF. . 
.THEN   ,     Amount  
  $5.     ,    EXCEPTION 
   SALE TOO_LOW. 

     

     (trigger)      ,  
      .     SQL,  
      : , 
%^   , ..    .  
    InterBase SQL: 

    CREATE  TRIGGER  SALEDelete   FOR  CUSTOMER 
    BEFORE   DELETE	x 
    AS 
    BEGIN 
    DELETE FROM SALE 
    WHERE CustomerNumber=OLD.CustomerNumber; END 

     17. InterBase	361 

              
SALE,          CUSTOMER. 
    (cascading delete)    
           
,     . 
          OLD.  
     ,      
  UPDATE  DELETE.   NEW   
   ,        
INSERT  UPDATE. 
           BEFORE   
   ,      INSERT, UPDATE 
 DELETE. 
      InterBase        
   (,  32768).    
,       ,   
   POSITION, : 

    CREATE   TRIGGER   SALEDate   FOR  CUSTOMER 
    BEFORE   DELETE 
    POSITION   0 
    AS 
    BEGIN 
    DELETE   FROM   SALE 
    WHERE   CustomerNumber=OLD.CustomerNurnber; END 

       POSITION    0  32767.  
    .      
    ,      . 

     

     InterBase     SQL   
 .       
 (  Windows     DLL),  
     InterBase    DECLARE 
EXTERNAL FUNCTION.           
   ,    .     
 DLL    . 

    library IBUDFLIB; 
    uses 
    SysUtils, Classes; 
    function Maxlnt(var Intl, Int2 : Integer) : Integer; 
    far cdecl export; 
    begin 
    If (Intl>Int2) then Result:=Intl 
    else Result:=Int2; end; 
    function Minlnt(var Intl, Int2 : Integer) : Integer; 
    far cdecl export; 
    begin	**
    If (Intl<Int2) then Result:=Intl 
    else Result:=Int2; 
    *** end;	"TV 
    exports Maxlnt, Minlnt; 
    begin end. 

    362	 III.   

        SQL,       
  InterBase. 

    CONNECT "C:\DATA\IB\SALES.GDB" USER SYSDA PASSWORD masterkey 
    DECLARE EXTERNAL FUNCTION MAXININT 
    INTEGER, INTEGER RETURNS INTEGER BY VALUE ENTRY_POINT "Maxlnt" MODULE_NAME "IBUDFLIB.DLL"; 
    DECLARE EXTERNAL FUNCTION MININT 
    INTEGER, INTEGER RETURNS INTEGER BY VALUE ENTRY__POINT "Minlnt" 
MODULE_NAME "IBUDFLIB.DLL"; 
    EXIT; 

           InterBase  . 
    Q  InterBase    DLL  ,   
    .    DECLARE EXTERNAL 
FUNCTION    ,     . 
          ,     ( 
    var     Maxlnt  Minlnt), 
..        ,   
 . 
    Q  - InterBase      
 cdecl.  
    far pascal, stdcall  safecall   . 
              ,    
.     ,     
  ,    ,  InterBase  . 
     Minlnt  Maxlnt     WISQL  . 

    SELECT  Maxlnt(3,5)    FROM  CUSTOMER; 

      InterBase   SELECT     
   ,    CUSTOMER.  Maxlnt 
         CUSTOMER,  
     . 
       SELECT,       , 
  ,    .   
     (     
WISQL ): 

    CREATE   TABLE   DUAL    (A   CHAR(l)); INSERT   INTO   DUAL    (A)   
VALUES    ('A'); SELECT   22   /   7  A3  Approximate?!   FROM   DUAL; 

             
 DROP   EXTERNAL FUNCTION. 

     

     (cursor)        
 SQL     .     
    .       
SQL,     BDE,     
    . 
         : , , 
   .        
  . 

     17. InterBase 363 

      

            WISQL.  
    . 
        SELECT  (  
)   .    : 

    DECLARE CUSTOMER_SELECT CURSOR FOR SELECT * FROM CUSTOMER 

       ,  ,   .  
 ,    ,  
 OPEN. 

    OPEN CUSTOMER__SELECT 

      OPEN      .   
   FETCH. 

    FETCH CUSTOMER_SELECT 

              
.     FETCH     
.   SQL Server     
  :    FETCH     
 .       CLOSE   
   OPEN. 

      
      InterBase        
      Delphi.    
    -   , 
    .      , 
 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 CUSTOMERJJPDATE 

           CLOSE.  
       .  
  : 

    CLOSE CUSTOMER UPDATE 

    364  III.   

       InterBase 

            
         . 
            , 
      ,  
   . ,       
,        
.         
   . 

       

      ,      
      . ,  
 ,   ,     , - 
.       
    .     
    ,   ,  
            
  .      ""   
    ,     . 
    InterBase    
 Server Manager.    . 
    1.	  Server Manager. 
    2.	  File^Server Login    . 
    3.	  TasksOBackup     Database Backup. 
    4.               
Database Path.    Remote       
   ,   NetWare. 
    5.            Backup File 
or Device.      Windows NT,     
  \\.\tapeO. 
    6.          .  . 17.1 
  . 
            
      .     
 Restore   Tasks,       Restore 
Source       Restore Destination. 

       

         InterBase,    
   .      
   Task^User Security  Server Manager.  
    InterBase  . 
    1.	  Server Manager. 
    2.	  File^Sefver Login    . 
    3.	  Tasks^User Security     

InterBase Security (. 17.2). 


    4.	   Add User      . 
    5.	     . 
    6.	       . 


     17. InterBase	365 



    . 17.1.       Server Manager 

    . 17.2.     InterBase   User 
Configuration  Server Manager 

            
     InterBase.       
     (  PUBLIC) 
 . 

       

      InterBase Server Configuration    
:   Task<=>Server Config    Server 
Manager         InterBase server  
,  Windows       Properties. 
      Server Configuration    :   
InterBase    . 
      InterBase (IB Settings^   : Database Cache  
Client map size.  Database Cache   , 
    .     
 ,   ,      
  .      .   
  -  ,     
   InterBase  ,   
 .  Client size map      
 .          
    -     
(, BLOB)  -. 

    366  III.   

        (OS Settings)   : Min 
Process Working Set, Max Process Working Set  Process Priority Class. 
 Min Process Working Set  Max Process Working Set  
    ( ).   
 ,      -    
(Database Cache),         
.      Windows NT,     
Performance Monitor  ,     
.    . 
    1.    InterBase   Windows NT 
Performance Monitor (    ,   ). 
    2.      <Ctrl+I>    
  . 
    3. Object: Process, Intance: IBSERVER, Counter: Page File Bytes  
   Add. 
    4.  Page Faults/sec  % Processor Time  IBSERVER 
(. 17.3). 

    . 17.3.   Performance Monitor Windows NT  
      InterBase 

        ( Page Faults/sec),  
 % Processor Time    50%,    
 Process Working Set    . 
         . 
      Process Priority Class     
 InterBase   ,     . 
     High      
,   ,    
InterBase,   ,       
-.          
   Apply. 

       

    -   ,     
"-",        
.  InterBase,      , 
     ,   
,  ,  .    
     (     
"" (deadlock)),       
 .  InterBase     
Tasks^Lock^Janager Statistics    Server Manager (. 17.4). 
      InterBase     
 ,         
 .  LOCK_HEADER BLOCK      
    ,      
 ,  ,     ..  
      :  
 ,      ..  
      ,   
 . 

     17. InterBase 367 

        

      ,     
         
   .     
    .     
Main-tenance^Database Validation    InterBase Server Manager. 
 . 17.5    Database Validation. 
           :  
   ,    
           
  .   ,   
  ,  ,    
 ,   Read-only validation. 

    . 17.4.  Lock Manager Statistics       
 InterBase 

    . 17.5.   Database Validation   
   

     ,     , 
  .        
 Repair.        ,  
     ,     
     .    
  InterBase  . 
    1.         
Server Manager (,    ^ Explorer 
() Windows).    ,  - 'J     
   . 
    2.      Maintenance^Database 
Validation    InterBase Server Manager. 
    3.       Repair  
    . 

    368  III.   

    4.     ,     
Database Validation,      Ignore checksum errors. 
    5.      Repair   
  . 
    6.    ,   Read-only 
validation. ,          , 
     .     
 ,    Server Manager. 
    7.         
 Server Manager.     , 
     . 
    8.        .       ,    . 
    9.       ,   
 ,     .     
Read-only validation. 

       

       Database Statistics     
 Server Manager,    ,  
   .        
File Database^Connect    Server Manager. 
       Tasks1* Database Statistics    Server 
Manager      :   
 ,     ,     
     .     
     Maintenance^Database Properties.  . 17.6 
   Database Statistics. 

    . 17.6.  Database Statistics     
   

      Database Statistics   ,   
 ,    ,   ,   
 View^Database Analysis.    ,  
          . 


       

          Maintenance^Database Sweep 
 ,      , 
    .      
   .       
  ,     ,  ,   
    ,    
 . ,       
   . 

     17. InterBase 369 

      

      ,    , 
  -      
,     \-    . 
      -      
      .   
Maintenance^Transaction .Recovery       
,    ""    
    .    
-          . 

     

               
InterBase.     ,    InterBase,  
  ,       
.        SQL  
InterBase          .  
 InterBase        ,   
  ,       
   Delphi,     InterBase. 

     ... 

      18, "Sybase SQL Server",   , 
   Delphi       Sybase. 

    370	 III.   
