     10 

        SQL 

         

         (  TQuery)    
 SQL-    ,    ,   
,      . 
     SQL- ,     ,   
,  ,   ,    
.         SQL    
  Delphi,          
. ,  ,        
    SQL     .  
      SQL- "XYZ",   
 SQL- "ZYX"? 

        SQL 

      ,       ,  
  Delphi  SQL- .     , 
   12, "  '',    
   VCL,       
      ,    . 
            ,      
,    SQL-  (SQL database).  SQL  
 Structured Query Language (  ), .. 
 ,   .      
.    ^ 

         SQL 

           SQL-    
   . 

      SQL-    

     ,    BDE   SQL- ,  
  . 

      ,   SQL 

            
  TQuery,   SQL,    ,  
    SQL ,   TStoredProc  TUpdateSQL. 

       SQL 

           BDE   
 SQL- .                       1 

    271 

    I Data Definition Language (  ).   
  ,       
  SQL,     "  
   .    , 
    ,   , ..   
    . 
     Data Manipulation Language (  ).   
 SQL,         
.  DML   ,     
    . 
        SQL,  ,      
        . 

     

       SQL,   ,  SQL-   
  ,        SQL.  
    .       
 ,    , SQL-  
    ,     
SQL-,  ,    .  
    . 
          .      
      .       
   . 
       .      
           
  SQL ,      ,   
.        (Wide Area Network 
WAN),       : "" 
 .               ""' 
     ,     !  ,    
SQL- ,    ,   -. 
         .  
,           
.       , -  
        ,  
          
 .          
,     ,      
  .   SQL- ,     
 SQL   100 ,      
 . 

      SQL 

    ,  ,  , ,     
  SQL.     ,   
  InterBase,    Delphi 3.    
   Delphi      Database Desktop, 
   ,   ,   
   ,    . 

      SQL- 

      ,  ,    
Database Desktop       InterBase,   
 . 
    1.  ,     Database Desktop,  
   .    Database Desktop, 
  . 10.1. 
    2.   File^Open^Table,     Open 
Table,   . 10.2. 
      ,    Open Table, 
   [ ] 
    3.  IDBLOCAL    Alias    
 Database Information,   . 10.3. 

    272                                      III.     


    . 10.1.   Database Desktop    
 Delphi 

    . 10.2.   Open Table     

    4.     .       
SYSDBA,    masterkey. 
    5.   employee ( )     Open. 

    . 10.3.   Database Information     
 SQL 

     

      SQL   Database Desktop     , 
    , ..   ,   
.    ,   ,  ,  
.      ,   BDE 
   . 

     SQL,    QBE 

    ,       employee,   
SQL. ,    Database Desktop   QBE (Query By 
Example    ),   SQL-   
SQL-.    ,   QBE-  
 ,       , 
    . 
    1.   File^New^QBD Query,      
,       Open Table,   . 10.2. 

     I 
             Open 
Query       New. 

    2.     Alias  IDBLOCAL. 
    3.    (employee)       
Open.    QBE,    . 10.4. 

    . 10.4.   QBE         


     10.     SQL 273 

    4.     FIRST_NAME  LAST_NAME.    
, ,        . 
    5.    JOB_GRADE    3. 
    6.   Query^Run Query    QBE.  
  ,  14 ,  , 
   . 10.5. 
       ,  ,   
  QBE. 

      QBE  SQL 

      QBE     SQL   
  .  ,   QBE-,   
  Query=>Show SQL.   SQL- SQL Editor, 
  . 10.6. 

    1  SQL Ediloi :WORK:<Unli4ed> 

    . 10.5.         

    . 10.6.  SQL      QBE 

     ,  SQL  ,     .  
,  SQL  ,   SQL-. ,  
       SQL,    
      SQL.     SQL,  
 SQL-         
  .  , Delphi     
(Localsql.hip),  Local SQL Guide,     
 SQL. 

     , TQuery  SQL-  

              
  SQL-   ,     , 
        
,    SQL. 

       TTable  TQuery 

       TQuery   SQL- -,  
       . 

         . 

          ( )  
 , , ,     . 
           SQL,      
,        . 

    274  III.     


      TQuery   . 

         , ,  
  ,      . 
       DDL-    . 

       SQL 

       TQuery    SQL  
UpdateMode,  ,   BDE   
   .     ,  
    ,   ,    
  .  UpdateMode      . 
     upWhereAll.        
 .     BDE    
   ,     .   , 
,       ,  
"1       .   
  BDE       
  . 
     upWhereKeyOnly.        
.     BDE     
.    ,  . 
     upWhereChanged.      upWhereAll  
UpWhereKeyOnly.   upWhereChanged  BDE   
  ,          
.     ,    
,    . 

        Ttable  TQuery 

      ,    SQL,  
 ,      .  
        
 ,    .    
 ,       ,   
    TQuery. 

       

            
 ,     . 

      ,    

           .  
  (Field Editor)      .  
         (Form 
Designer).           
    Add Fields   .    
    ,  ,  , 
    . 

      ,   TQuery 

      ,   TQuery,  
     , ,     
SQL,        ,   .  
      SELECT *,   
  ,    . 

       

           , 
   . 

     10.     SQL                                 275 


      ,    

           .  
  ,   Fikter,"Fiitered  
FilterOptions,  0''   SetRange.    
     ,     
SetRange    9, "    ". 

      ,   TQuery 

       ,   TQuery, 
      .    
   ,      
             
 . 

        

     TDatabase 
     TDatabase      
  SQL-    ,   . 

      TDatabase 

     TDataBase   ,  
  . 
     AliasName  DatabaseName 

        ,     
TDataBase.     BDE-   
 AliasName,    ,   , 
   DatabaseName.     
     IBLOCAL     
,   employee.     
 dbTest  TDataBase. 

    with dbTest do begin 
    {   . } 
    Close; 
    (       . } 
    AliasName := 'IBLOCAL'; 
    DatabaseName := 'EMPLOYEE'; 
    {   . } 
    Open; 
    end; 


     KeepConnection 

        , ,   
  ,    .     True, 
            
  . 

     LoginPrompt  Params 

     LoginPrompt ,       
       .     
True,           
,   . 10.3.     False,   
       Params  TDatabase. 
     . 

    with dbTest do 
    begin 
    {        Params. ) 
    Params.Add('username=sysdba'); 
    Params.Add('password=masterkey'); 


    276                                      III.     


    {    . } 
    LoginPrompt := False; 
    {   . } 
    Open; 
    end; 


     Translsolation 

     Translsolation      
   .        . 
     tiDirtyRead.      ,   BDE 
   ,       
 .          
     ,   ,   
,  ,     Rollback    
.                      ""'""""" 
     tiReadConnnited.       , 
  . 
     tiRepeatableRead.    Translsolation   
,          
  . 
            ,  
    Translsolation   ,   BDE 
   . 

                          
                         
                      tiDirtyRead 
                          tiReadCommited 
                         tiRepeatableRead 


      TDatabase 

     TDataBase   ,  
   . 
     Open  Close.  Open     ,   
Close, ,   .  Connected   True, 
  ,  False,   . 
    B CloseDatasets.  CloseDatasets       
,     .    
  ,      . 
    1 StartTransaction, Commit  Rollback.      
       9, "   
 ". 

     TDatabase 

     TDataBase   ^1^1.    
         Params. 
        ,   
" LoginPrompt  Params". 

       

       (Database Editor),   . 10.7, 
           
    TDataBase. 

     TStoredProc 

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


     10.     SQL                                 277 


    . 10.7.        
 TDataBase 

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

     

     TStoredProc   ,    
        . 

     DatabaseName  StoredProcName 

        ,   . 
      spDaily  
TStoredProc     DailyProcessing    Finance. 

    with spDaily do 
    begin 
    f    . } 
    DatabaseName := 'Finance'; 
    {    . } 
    StoredProcName := 'DailyProcessing'; 
    end; 


     Params, ParamCount, ParamByName  ParamBindMode 

             
Delphi   .  Params, ParamCount  ParamByName 
   TQuery     9, "   
 ". 
     ParamBindMode ,      
,   .     
  pbByName,  BDE    . 
   TStoredProc,     ,  
   .       , 
  ,   .    
  ParamBindMode  pbByNumber,  BDE  
      ,  
 TStoredProc,  ,    .  
 ,       ,  
         (,  
  CopyParams).                  '~" """ 

      TStoredProc 

     TStoredProc   ,   
     . 

    278  III.     


     Prepare, ExecProc, Open  Close 

          .  Prepare 
        ExecProc  Open. 
 ExecProc ,      ,  
 Open     ,   
.  Close    TStoredProc   
  State   dslnactive.     
   115$^   Finance. 

    with spDaily do 
    begin 
    {       . } 
    DatabaseName := 'Finance'; 
    StoredProcName := 'DailyProcessing'; 
    {     . } 
    Prepare; 
    {   . } 
    ExecProc; 
    end; 


      TStoredProc 

    |       TStoredProc     ,   
 TTable  TQuery ( 9, "    "). 

     TUpdateSQL 

     TUpdateSQL       
 SQL.       
       
   .  TUpdateSQL   
    ,   
  ,    TTable. 
     UpdateObject  TTable  TQuery    
 TUpdateSQL,       . 
 TUpdateSQL       
  . 

     

        DeleteSQL, InsertSQL  ModifySQL, 
   SQL  ,     . 
     SQL       
  SQL.     ukDelete, 
uklnsert  ukModify,     . , 
         TStrings   
 ibSQL  TListBox. 

    {   SQL     TListBox. ) 
    ibSQL.Clear; 
    ibSQL.Items.Assign(usCust.DeleteSQL) ; 

     

    {         SQL. } 
    ibSQL.Clear; 
    ibSQL.Items.Assign(usCust.SQL[ukDelete]) ; 

         usCust  TUpdateSQL. 

      TUpdateSQL 

      TUpdateSQL    . 
     SetParams  ,    SQL, ,  
  . 
     ExecSQL   SQL,    . 
     Apply    SetParams      
     ExecSQL. 

     10.     SQL                                 279 


      SQL- 

              
  SQL. 

        SQL 

      9, "    ",   
  ,       .  
       
StartTransaction, Commit  Rollback,   TDatabase. 
     SQL-        SQL, 
  ,  ,    
  .        ,  
     . 

       SQL 

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

    procedure SearchForOrders(dtSearch : TDateTime); 
    var sDate : String; 
    begin 
    { SQL     mm/dd/. } 
    sDate := FormatDateTime('imn/dd/yyyy',dtSearch); 
    with qrySearch do 
    begin 
    {    SQL. } 
    SQL.Clear; 
    {    SQL. } 
    SQL.Add('select OrderNum from STOCK'); 
    SQL.Add(Format('where OrderDate'' > %s''',[sDate]) ; 
    (  . } 
    Open; 
    end; 
    end; 

     ,     ,     
      .  
,    SQL   ,   
       .   
       ,  
    SQL: 

    select OrderNum from STOCK where OrderDate > :P_OrderDate 

             
   

     
    
    _.   ,         
,      . 
       SQL   SQL  TQuery Delphi  
  Params   ,     
ParamCount     . ,    
 ,     .     
,  

    280                                      III.     

      Params   TQuery   Object Inspector.  
      Parameters,   
. 10.8. 
    ,         ,  
      .    
      ,   
 . 

    procedure SearchForOrders(dtSearch : TDateTime); 
    begin with qrySearch do 
    begin 
    {   . } 
    ParamByName('P_OrderDate').AsDateTime := dtSearch; 
    {  . } 
    Open; 
    end; 
    end; 

    . 10.8.    Parameters    
    

       ,   SQL-   
P_OrderDate     DateTime. ,  ,    
  ParamByName.    FieldByName   
     ,       Params. 

        

          . 

         BDE 

     BDE Administrator    ,  
    SQL-. 
       .         
.     BDE    
 ,   ,       .  
   ,    Enable Schema 
Cache   True,    Schema Cache Dir   
  . 
       .   Batch Count 
  ,       
  .      
        . 
          
            
    ,      
.       SQLPassThru 
Mode   BDE Administrator  Shared. 

     

          SQL- .     SQL 
        (DDL)     
(DML).    SQL,     SQL-. 
       Database Desktop    QBE-  
      SQL. 

     10.     SQL 281 

          ,    
    TQuery,    SQL 
 ,     ,  
     4)  . 
     ,   ,    
  SQL- : 
    TDatabase, TStoredProc  TUpdateSQL.  TDatabase   
     ;  TStoredProc  
,   ,   TUpdateSQL   
        . 
    , ,     :  
 ,     SQL,   
      SQL. 

     TStoredProc 

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

    282                                      III.     
