


                  ࠢ筮 㪮⢮   SQL        5-78


E                            F

E         DECLARE CURSOR (஥ SQL)               F

         祭 ।    ⥬      
                    뢠   । SQL  -  .
                        ।,    㤥 
                    (OPEN).

         ।뫪 .

         ⠪  EXEC SQL [AT dbname] DECLARE cursor_name CURSOR
                        FOR {SELECT subststment | ststment}

         祢   AT dbname        㪠뢠  易⥫  ࠧ
         ᫮                      AT,   ᯮ  
         ࠬ                   ,  ࠭ । -
                                     ஬ DECLARE DATABASE    -
                                     㥬   ஥ -
                                      CONNECT.

                    cursor_name      , 砥 .

                    substatment       SELECT  ࠧ INTO.

                    statement         ,  ࠭  ।-
                                       ஥ SQL - -
                                     ஬ DECLARE STATEMENT.

         砭   DECLARE CURSOR ⭮  ⨢
          ᯮ- SQL - ࠬ ,  ᫥⥫쭮,   -
               室 ⥪ ४  -
                    ஢, 뫠   . ORACLE ᬮ-
                     ஢ ,  ᫨     ࠭
                    । ⨢   ஬.  ⥪⮬
                    ( )  । -
                         室 䠩 ⥪饣 ;
                     ⫨砥   ࠢ    ORACLE  
                    host - 몠. ਬ,     -
                    ஢ (।)  楤, 饩-
                     ⤥쭮  ⥫.

                       DECLARE  CURSOR   㭨-
                    ;    ।      
                        㥬 .

                    ᫨   SELECT ᯥ஢ ࠧ FOR
                    UPDATE OF,        뫠  
                    UPDATE, ᯮ WHERE CURRENT OF CURSOR.  -
                        ᯮ짮  ᫥-
                    饬 WHERE CURRENT OF CURSOR,  ᫨  
                     樮஢  ப   FETCH.




                                   -- 171 --



         ਬ     EXEC SQL DECLARE C1 CURSOR
                      FOR SELECT ename, empno, job, sal FROM emp
                                WHERE deptno = :deptno
                                FOR UPDATE OF SAL

         易   SELECT,  ஥ SELECT,PREPARE,OPEN
         ⥬       cursor, FETCH, CLOSE cursor.
         
                      ࠢ筨  SQL - ࠬ         5-79


















































                                   -- 172 --




E                            F

E         DECLARE DATABASE (஥ SQL)             F


         祭 ।  㤠  ,  -
                     뫠    ࠧ AT ᫥騥 SQL
                    - .

         ।뫪 室    饬    㤠-
                       짮⥫.

         ⠪  EXEC SQL DECLARE dbname DATABASE


         祢   dbname           䨪, 砥  㤠-
         ᫮                        .
         ࠬ

         砭     ।,      -
          ᯮ-    ஥  SQL -   
             ࠧ AT.  ⠪ ࠬ ⭮ ᫥騥:

                         * COMMIT
                         * DECLARE CURSOR
                         * DELETE
                         * INSERT
                         * ROLLBACK
                         * SELECT
                         * UPDATE

                     ⥫쭮 ଠ樥     "-
                     ணࠬ"   ᮮ⢥饬 -
                    .

         ਬ     user      :string(1..10);
                    pass      :string(1..10);
                    my_database :string(1..10);

                    user :="scott";
                    pass :="tiger";
                    my_database :="D:CHICAGO";

                    EXEC SQL DECLARE oracle1 DATABASE;

                    EXEXC SQL CONNECT :user
                        IDENTIFIED BY :pass
                        AT oracle1
                        USING :my_database;

                    EXEC SQL AT oracle1 SELET ...;

         易 ஥   SQL  -    CONNECT    COMMIT
         ⥬      RELEASE.
                    
                  ࠢ筮 㪮⢮   SQL        5-80


                                   -- 173 --




E                            F

E         DECLARE STATEMENT (஥ SQL)            F


         祭  SQL -   SQL - ६.

         ।뫪 .

         ⠪  EXEC SQL DECLARE ststement_name STATEMENT

         祢   ststement_name   䨪 .
         ᫮ 
         ࠬ

         砭  믮  DECLARE  STATEMENT  室
          ᯮ- ⮫쪮  砥,  DECLARE CURSOR, 뫠騩-
               । ,  믮  -
                     PRERARE. ᫨  PREPARE ।-
                      OPEN cursor,  DECLARE  STATEMENT  
                    ॡ.

                    ᫨ DECLARE  STATEMENT  ᯮ,   
                    ।⢮ ࠬ  DECLARE  CURSOR,  OPEN
                    cursor, DESCRIBE    PREPARE,  믮騬 
                    .

                    ⥪ ।  -   -
                    樨  ⥪饣    (筮  樨
                    ),    ⫨砥    ࠢ  
                    ORACLE  host - 몠.  ਬ,   -
                      ஢  (।)    楤,
                    饩 ⤥쭮  ⥫.

         ਬ      ਬ    ⨯  ᫥-
                    ⥫쭮 ᯮ짮          DECLARE
                    STATEMENT:

                    EXEC SQL DECLARE s STATEMENT;
                    EXEC SQL PREPARE s USING :my_string;
                    EXEC SQL DECLARE c CURSOR FOR s;

         易   PREPARE,   OPEN  cursor,  FETCH,  CLOSE
         ⥬       cursor.
         
                      ࠢ筨  SQL - ࠬ         5-81












                                   -- 174 --




E                            F

E         DELETE                                        F


         祭  ப  ⠡.

         ।뫪    楬 ⠡,   ਢ-
                     DBA     ⠡ ਢ
                    DELETE.

         ⠪  DELETE [FROM] [user.]table [alias] [WHERE condition]

         祢   user              ⠡.
         ᫮ 
         ࠬ  table             ⠡  ,  -
                                      㤠 ப.

                    alias            , 祭 ⠡,  -
                                      ᯮ  饬  -
                                     砥 -         ५஢
                                        DELETE.

                    condition        ᫮, ᫥饥   ᮣ襭
                                      ᠭ  ᫮  (⠫쭮
                                     ᠭ   3 " -
                                      SQL").  Condition  -
                                        १  TRUE   
                                     FALSE.

         砭  ᫨  ࠧ WHERE,  ⠡ 㤠-
          ᯮ-   ப.  ᫨    ࠧ  ᯮ,
             㤠  ப,    ᫮ condition
                     TRUE.  ᫮  뫠  㥬
                    ⠡     ᥡ ५஢ -
                    .

                     㤠塞 ப    ᮮ⢥騥  
                    ᢮   .

         ਬ    ⮡ 㤠   ப  ⠡ TEMP_ASSIGN,
                    :

                    DELETE FROM TEMP_ASSIGN

                    ⮡ 㤠  ⠡ EMP  த殢,  -
                    稢   ᫥     100 ஢
                    ᨮ,  ᫥ :

                    DELETE FROM EMP
                         WHERE JOB = 'SALESMAN'
                           AND COMM < 100

         易   DROP TABLE; ࠧ FROM  WHERE 
                    SELECT.
                    
                  ࠢ筮 㪮⢮   SQL        5-82

                                   -- 175 --




E                            F

E         DELETE (஥ SQL)                       F


         祭   ப  ⠡  .  ⠪
                    ஥ DELETE ⫨砥  ᨭ⠪ ⠭-
                    ⭮ SQL  -    DELETE   ࠧ AT,
                    FOR, CURRENT OF  host - ६묨.

         ।뫪    楬 ⠡      
                     ਢ DELETE,   ਢ DBA.

         ⠪  EXEC SQL [AT dbname] [FOR :host_integer]
                       DELETE [FROM] table [alias]
                           [WHERE {condition | CURRENT OF cursor}]

         祢   AT dbname        易⥫쭠  ࠧ,  
         ᫮                       㬠稢    
         ࠬ                   裡, ࠭  祭   -
                                       DECLARE DATABASE.

                    FOR :host_integer ᯥ 쭮 -
                                     ⢮ 権        
                                     DELETE,   ࠧ WHERE -
                                      ᨢ.

                    FROM table       㪠뢠   ⠡  -
                                     ,     ॢ
                                     㤠 ப.

                    alias            , 祭 ⠡,  -
                                      ᯮ  饬  -
                                     砥 -         ५஢
                                        DELETE.

                    WHERE            ᫮ ᪠ ப, 
                                     㤠. ᫨ , 㤠-
                                         ப ⠡
                                      .

                    condition        ᪮ ࠦ,    ஥
                                      ᮤঠ 뫪  host -
                                     ६   host  - ᨢ.
                                     . ⠪ । ᨭ⠪
                                     condition    4 - "樨
                                      㭪樨".

                    cursor           ᯮ   -
                                     ,  ।  
                                     DECLARE  CURSOR.    믮
                                         DELETE      ࠧ
                                     CURRENT OF      
                                       樮஢  -
                                     .



                                   -- 176 --



                      ࠢ筨  SQL - ࠬ         5-81


         砭  ᫨   ⢥  host  -  ६  
          ᯮ- WHERE 㪠 ᨢ,  DELETE 믮-
                 ᥣ  ⮢ -
                    ᨢ.  믮    DELETE  
                    㤠 ,    ப.

                    ᫨ -  host - ६,  ᯮ㥬
                     ࠧ WHERE,    ᨢ,    
                    host -  ६   ᨢ. ᨢ
                      ࠧ筮 ࠧ,  ᫮ ࠡ-
                     ⮢  ।  ࠧ஬  -
                    쭮.

                    ᫨   ப  㤮⢮  ᫮, 
                     ப  㤠    㤥  SQLCODE ୥
                    ᫮ NOT_FOUND.

                    饥 ⢮ 㤠 ப  頥  
                    SQLROWS.   ࠧ WHERE ᯮ -
                    ,  稪 ࠦ 饥 ⢮ 㤠-
                       ⮢ ᨢ, ࠡ⠭ 
                     DELETE.

                    ᫨ ࠧ WHERE 饭,   ⬥砥 ⠢-
                     䫠    5-  SQLWARN (SQLCA).
                    ᠭ SQLCODE,  ORACLE.SQLROWS  SQLCA  ਢ-
                       "㪮⢥ ணࠬ  ४-
                    ࠬ ORACLE"   "    㪮  
                    ४ࠬ ORACLE"     ⭮
                    몠 ணࠬ஢.

         ਬ    騥 ਬ      ᯮ짮
                     DELETE:

                    EXEC SQL DELETE FROM EMP
                       WHERE DEPTNO = :deptno
                       AND SAL :sal;

                    EXEC SQL DECLARE C1 CURSOR
                       FOR SELECT ...;

                    EXEC SQL FETCH C1
                       INTO ...;

                    EXEC SQL DELETE FROM EMP
                       WHERE CURRENT OF C1;

         易   DECLARE DATABASE  DECLARE STATEMENT.
         ⥬
                    
                  ࠢ筮 㪮⢮   SQL        5-84





                                   -- 177 --




E                            F

E         DESCRIBE (᪨ ஥ SQL: ⮤ 4)F


         祭 ।    樠  ਯ  
                    ࠭ ᠭ host - ६  SQL - -
                    , ।⥫쭮 ⮢ ஬
                    PREPARE.

         ।뫪 室    PREPARE  -
                     SQL  - ,  ᮤঠ騩 host - ६-
                    ,    (DESCRIBE).

         ⠪   易 ६:

                    EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name
                        INTO descriptor_name

                     ⮢ ᯨ᪠ SELECT:

                    EXEC SQL SESCRIBE [SELECT LIST FOR] statement_name
                        INTO descriptor_name


         祢   BIND             ॡ    ᯥ஢
         ᫮     VARIABLES        ⮣,       㤥
         ࠬ                   뢠 室 ६.

                    SELECT LIST      ᯥ,   㤥 -
                                      ᯨ᮪ 室  ६-
                                     .    ᯮ짮  ⮩
                                     ࠧ 易⥫쭮.

                    statement_name   㪠뢠   ࠭ ।
                                     SQL - .   ᮤঠ
                                     ࠢ 뫪     ᨢ
                                     host - ६.

                    descriptor_name..㪠뢠 ,   ஬ -
                                       ਯ.

         砭  ਯ   ।⥫쭮 । 
          ᯮ- ணࠬ. 室  室 ६  -
                 ਯ.

                     ,  DESCRIBE 믮 ᫥ -
                     PREPARE.

         ਬ     ⮡ ᬮ  ਬ ਬ⥫쭮  -
                      ணࠬ஢,     ᮮ-
                    饬 "  㪮  ४-
                    ࠬ ORACLE".





                                   -- 178 --



         易   PREPARE;      㪮  
                    ४ࠬ ORACLE.
                             
                      ࠢ筨  SQL - ࠬ         5-85


E                             F

E         DROP CLUSTER                                  F


         祭      ᯥ஢ -
                    .

         ।뫪     楬  ⮣  ,
                      ਢ DBA.

         ⠪  DROP CLUSTER [user.]cluster
                        [INCLUDING TABLES]

         祢   user               .
         ᫮ 
         ࠬ  cluster           饣 .

                    INCLUDING TABLES 㤠  ⠡, ਭ-
                                     騥 .  ᫨  ࠧ 
                                     ᯥ஢,      㤠
                                      室  㤠 
                                     室騥   ⠡.


         砭  , 楬  ண   , 
          ᯮ-   㤠, ᫨    ਢ DBA.
         
                      㤠 ⠪   
                    頥     (  ) 
                    ᮮ⢥騥 ⠡ ࠭⢠.

                     ਧ ⠡   
                    䥪⮬ 㤠   .  ⤥ ⠡-
                     ᪫ਧ . ⮡  -
                     䥪, 室 믮 ᫥饥:

                       1.   ⠡  ⠪  ன
                           ᮤঠ,   樨 CLUSTER:

                          CREATE TABLE new
                              AS SELECT * FROM old

                       2.   ⠡:

                          DROP TABLE old

                       3.  , ᯮ ஬ RENAME
                           २  ⠡  :

                          RENAME new TO old


                                   -- 179 --



                    ਢ, ।⠢  ன ⠡  -
                    ७        室  
                    ।⠢ .

         ਬ      ⬥  GEORGAPHY   祭-
                       ⠡栬 室 :

                    DROP CLUSTER GEOGRAPHY INCLUDING TABLES

         易   DROP TABLE.
         ⥬
                    
                  ࠢ筮 㪮⢮   SQL        5-86














































                                   -- 180 --




E                            F

E         DROP DATABASE LINK                            F


         祭  ᯥ஢  裡.

         ।뫪 室  楬  裡. 
                    ⬥ 饩 (PUBLIC) 裡 室  -
                     DBA.

         ⠪  DROP [PUBLIC] DATABASE LINK link

         祢   PUBLIC              ᯥ஢  
         ᫮                      ⬥ PUBLIC - 裡.
         ࠬ
                    link              ⬥塞 裡.


         砭  ᫨   ,      ᯥ஢
          ᯮ- PUBLIC;  ࠧ  ਬ ⮫쪮 DBA. -
                筠,  ࠧ PUBLIC  
                     饭.

                    DBA  ⬥ ,  楬 ன -
                     㣮 DBA.

         ਬ      ⬥  裡   BOSTON -
                     믮 ᫥ :

                    DROP DATABASE LINK BOSTON

         易   CREATE DATABASE LINK; "㪮⢮ -
                    ⥫  SQL*Net"  襣 ⮪ SQL*Net.
                    
                      ࠢ筨  SQL - ࠬ         5-87






















                                   -- 181 --




E                            F

E         DROP INDEX                                    F


         祭     ᯥ஢ .

         ।뫪 室   楬      
                    ਢ DBA.

         ⠪  DROP INDEX [user.]index

         祢   user               .
         ᫮ 
         ࠬ  index             ⬥塞 .


         砭    ᬮ 㤠  ,  ᮧ  㣨
          ᯮ- 짮⥫, ᫨   ਢ DBA. -
               ⬥,   易    
                         ᮮ⢥饥 ⠡筮
                    ࠭⢮.

                     ⢥ user । 짮⥫,  -
                    訩  DROP.

         ਬ      㤠  MONOLITH :

                    DROP INDEX MONOLITH

         易   ALTER INDEX, CREATE INDEX, CREATE TABLE.
                    
                  ࠢ筮 㪮⢮   SQL        5-88

























                                   -- 182 --




E                            F

E         DROP ROLLBACK SEGMENT                         F


         祭  㪠 ᥣ ⪠.

         ।뫪 ॡ ਢ DBA.

         ⠪  DROP [PUBLIC] ROLLBACK SEGMENT segment

         祢   PUBLIC            ᯥ஢  -
         ᫮                       (PUBLIC) ᥣ⮢.
         ࠬ
                    segment          ᯥ  -
                                      ᥣ ⪠.

         砭  ⬥   ⮫쪮  ᯮ㥬 ᥣ
          ᯮ- ⪠.   ।,  ᯮ    
               ᥣ ⪠,   ⮫ STATUS
                     ᫮  DBA_ROLLBACK_SEGS.  ᫨ 
                    ᯮ,     ,   -
                    믮 ࠭樨  ,    -
                      SHUTDOWN  ࠬ஬ IMMEDIATE 
                    ⥬ ⮢ (STARTUP)      ०
                    EXCLUSIVE.

                     । ᥣ ⪠  -
                    頥  ᮮ⢥饥 ⠡筮 ࠭⢮.

         ਬ      ⬥ 饣 ᥣ  ACCOUNTING  믮
                    ᫥ :

                    DROP PUBLIC ROLLBACK SEGMENT ACCOUNTING

         易   ALTER ROLLBACK SEGMENT, CREATE ROLLBACK
         ⥬       SEGMENT, CREATE TABLESPACE.
         
                      ࠢ筨  SQL - ࠬ         5-89



















                                   -- 183 --




E                            F

E         DROP SEQUENCE                                 F


         祭  㪠 ᫥⥫쭮.

         ।뫪 室  楬 ᫥⥫쭮 -
                       ਢ DBA.

         ⠪  DROP SEQUENCE [user.]sequence

         祢   user              ᫥⥫쭮.
         ᫮ 
         ࠬ  sequence          ⬥塞 ᫥⥫쭮-
                                     .

         砭   ⢥ user ࠧ㬥 짮⥫, -
          ᯮ- 訩  DROP.
         
                       ⮤ ୮  ᫥⥫-
                     -  ⬥  ୮ ᮧ. ਬ,
                        ᫥⥫쭮,   祭
                    150,      27.  ⮣ 室-
                    :

                           1. ⬥ ᫥⥫쭮.
                           2.   ᫥⥫쭮    ⥬  
                              , 㪠  START WITH 祭 27.

         ਬ      ⬥  ᫥⥫쭮  ESEQ 짮⥫
                    ELLY :

                    DROP SEQUENCE ELLY.ESEQ

         易   ALTER SEQUENCE  CREATE SEQUENCE.
         ⥬
                    
                  ࠢ筮 㪮⢮   SQL        5-90



















                                   -- 184 --




E                            F

E         DROP SYNONYM                                  F


         祭     㪠 ᨭ.

         ।뫪  㤠 筮 ᨭ    
                    楬.  㤠 饣 ᨭ   -
                    , 楬 ண   ,  -
                    室  ਢ DBA.

         ⠪  DROP [PUBLIC] SYNONYM [user.]synonym

         祢   PUBLIC            ᯥ஢  -
         ᫮                       饣 ᨭ.
         ࠬ
                    synonym           㤠塞 ᨭ.

         砭     ᬮ 㤠  ᨭ, 楬
          ᯮ- ண  ,  ᫨   ਢ
             DBA.

                     ᨭ  ⬥   稢 -
                    .

         ਬ     ⮡ ⬥ ᨭ  MARKET, :

                    DROP SYNONYM MARKET

         易   CREATE SYNONYM.
         ⥬
                          
                      ࠢ筨  SQL - ࠬ         5-91
























                                   -- 185 --




E                            F

E         DROP TABLE                                    F


         祭    ⠡    ᮤন.

         ।뫪 室     楬 ⠡,  
                     ਢ DBA.

         ⠪  DROP TABLE [user.]table

         祢   user              㤠塞 ⠡.
         ᫮ 
         ࠬ  table             饩 㤠 ⠡.

         砭    ᬮ 㤠 ⠡,   ਭ-
          ᯮ- , ᫨   ਢ DBA.
         
                      㤠 ⠡, ⮬᪨ 㤠-
                      ,   ᫨  뫨 ᮧ 㣨
                    짮⥫,   ⠪ ਢ, ।⠢-
                      ⮩ ⠡.   ᨭ, 뫠騥-
                        ⠡,   㤠,   ⠭
                    訡묨.   㤠   ᮧ 
                      ᮧ  ⠡ ⠪ ࠧ,  ⮡
                      ᨭ    ⠫  ⢨⥫묨.
                    ⢥,     ⠡楩 㤠  
                    ப.

                    ᫨ ⠡  室  ,    ਭ-
                      ,  , 頥 
                    ⠡筮 ࠭⢮.

                    ⮡  㤠 ⠡   ⤥쭮,
                     㤠  , 믮  DROP
                    CLUSTER  ࠬ஬ INCLUDING TABLES.

                    । 㤠 ⠡ 室   -
                     ᫮  USER_CROSS_REFS  । -
                    । ᨬ   㣨 ⠡.

         ਬ      ⬥ ⠡ TEST_DATA :

                    DROP TABLE TEST_DATA

         易   DROP CLUSTER, ALTER TABLE CREATE INDEX,
         ⥬       CREATE TABLE.

                        
                  ࠢ筮 㪮⢮   SQL        5-92







                                   -- 186 --




E                            F

E         DROP TABLESPACE                               F


         祭  ⠡筮 ࠭⢠    
                    㤠  ꥪ⮢  ,  ਭ-
                    .

         ।뫪 ॡ ਢ DBA. 筮 ࠭⢮
                        online, ⠪   offline.

         ⠪  DROP TABLESPACE tablespace
                        [INCLUDING CONTENTS]

         祢   tablespace       ᯥ  -
         ᫮                       ⠡筮 ࠭⢠. -
         ࠬ                   筮 ࠭⢮  SYSTEM   
                                       ⬥.

                    INCLUDING        ᯥ,      ⠡筮
                    CONTENTS         ࠭⢮   㤠
                                      ᫨  ᮤন . ᫨
                                      㬥  饭    -
                                     ࠭⢮   ᮤন , 
                                     ⬥. ᫨    饭-
                                      㬥  ⠡筮  -
                                     ࠭⢮ ᮤন ,   
                                     ⬥.

         砭  । 㤠 ⠡筮 ࠭⢮ 室
          ᯮ- ॢ  offline,  ⠪   ᫨  짮⥫
                 ᥣ⠬ , , -
                      ६,  ⠡筮 ࠭⢮ ⬥-
                      㤥.

         ਬ     㤠  ⠡筮 ࠭⢠ MFRG 
                     ᥬ ᮤন, 室 :

                    DROP TABLESPACE MFRG
                        INCLUDING CONTENTS

         易.. ALTER   TABLESPACE,   CREATE  DATABASE,
         ⥬       CREATE TABLESPACE.
                    
                      ࠢ筨  SQL - ࠬ         5-93












                                   -- 187 --




E                            F

E         DROP VIEW                                     F


         祭  ᯥ஢    .

         ।뫪 室  楬    -
                     DBA.

         ⠪  DROP VIEW [user.]view

         祢   user              .
         ᫮ 
         ࠬ  view              饣 .

         砭  ᫨      ਢ DBA,   ⬥
          ᯮ-  㣮 짮⥫.
         
                      ⬥ ,  ᨭ  ,  
                     뫠騥,    ⬥,   ⠭
                    묨.   ⬥   ८।,
                     ⠪  ८। 㣨   ⠡-
                    ,   易,  ⮡     ᨭ
                    ⠫   ⢨⥫.   ਢ 
                    㤠塞  ⠪ ⬥.

                      "" ⥬  ⬥    -
                    ᮧ.

                    । ⬥    ᯮ짮 -
                    ஬ ᫮  USER_CROSS_REFS   ।-
                    , 易         ஬  -
                    㣨 ⠡  .

         ਬ      㤠  VIEW_DATA :

                    DROP VIEW VIEW_DATA

         易   CREATE  TABLE,  CREATE   VIEW,   CREATE
         ⥬       SYNONYM.
             
                  ࠢ筮 㪮⢮   SQL        5-94















                                   -- 188 --




E                            F

E         EXECUTE (᪨ ஥ SQL: ⮤ 2)F


         祭 믮  INSERT, DELETE  UPDATE, -
                     ⮢    PREPARE. 
                     뫠  host -  ६,  ᯮ
                    ࠧ USING.

         ।뫪 ।  믮 室 믮 
                    PREPARE.

         ⠪  EXEC SQL [AT dbname] [FOR :host_integer]
                       EXECUTE statement_name
                           [USING values_list]

         祢   AT dbname        ।  裡  㬠-
         ᫮                      稢  .
         ࠬ
                    FOR :host_integer ᯥ ᨬ쭮 -
                                      権    EXECUTE,
                                       ࠧ WHERE ᯮ-
                                      ᨢ.

                    statement_name    ⮢   ,
                                        믮. 
                                         
                                     SELECT.

                    USING            ᯥ稢 ᯨ᮪ host - -
                                         ࠦ,  
                                     ⠢  室 host -
                                     ६   ࠭ ⮢-
                                      .  ᥡ, 
                                     ᫨    ६ -
                                      ᨢ, ⠫ -
                                      ࠧ  USING ⠪ 
                                      ᨢ.

                    values_list      values_element [,values_element...]

                    values_element   host_variable [:indicator] |
                                         literal | expression

         ਬ     騩 ਬ  ᯮ짮 -
                     EXECUTE:

                    my_own_statement : string(1..100);
                    my_var : float;
                    get(my_own_statement);

                    EXEC SQL PREPARE s FROM :my_own_statement;
                    EXEC SQL EXECUTE s USING :my_var;




                                   -- 189 --



         易   PREPARE;   ਬ⥫쭮    ⭮
         ⥬        ணࠬ஢  㬥:   "㪮⢮
                    ணࠬ  ४ࠬ ORACLE"  "-
                      㪮  ४ࠬ ORACLE".
         
                      ࠢ筨  SQL - ࠬ         5-95


E                            F

E         EXECUTE IMMEDIATE (᪨ ஥ SQL: ⮤ 1)F


         祭 ⮢  믮 SQL - ,  ᮤ-
                    騩 뫮  host - ६.

         ।뫪 .

         ⠪  EXEC SQL [AT dbname]
                     EXECUTE IMMEDIATE [:host_string | string_literal]

         祢   AT dbname        易⥫쭠 ࠧ, ᯥ-
         ᫮                       㬠稢       
         ࠬ                   .

                    :host_string     host -  ६,   ᮤঠ
                                     騩 믮 SQL - -
                                     .

                    string_literal    ࠫ,     ᮤঠ騩
                                     ⥪ SQL  - ,  
                                       믮.


         砭   EXECUTE   IMMEDIATE   ਭ  
          ᯮ-  ᨬ ࠫ,  host - ६-
             , ᮤঠ SQL -   ᮧ 믮-
                      ⮣  .      믮
                       㭨⮦ 믮 .

                     믮  EXECUTE IMMEDIATE, -
                     SQL -    ஢
                     ।  㦥 訡.  ᫨  
                    訡, 祣    믮      SQLCODE
                    頥  訡.  ᫨  ᨭ⠪-
                    ᪨ ࠢ,   訡    ⠤
                    믮,   ⠪ 頥  SQLCODE.

                     EXECUTE  IMMEDIATE    
                    ஢,   믮   .
                     ⭮ 믮 ஢  -
                    짮  PREPARE  -    ⮢
                    SQL -   ⥬ - EXECUTE.  -
                     믮    २⢠,  ⠪  
                     ॡ    믮  ᨭ⠪-
                    ᪮ ࠧ.

         ਬ     get(my_string)

                                   -- 190 --



                    EXEC SQL EXECUTE IMMEDIATE :my_string


                    EXEC SQL EXECUTE IMMEDIATE
                        "DELETE FROM EMP WHERE EMPNO = 9460"

         易   PREPARE  EXECUTE.
         ⥬
                                                
                  ࠢ筮 㪮⢮   SQL        5-96

















































                                   -- 191 --




E                            F
                 
E         EXPLAIN PLAN                                  F


         祭 ।  믮,  ஬ 㤥 ᫥-
                     ORACLE  믮 㪠 SQL -  -
                    .   ᯥ஢ ⠡ -
                    , 뢠騩  蠣  믮.

         ।뫪    楬     ਢ
                    INSERT  ⠡,     뢠
                    १  믮.

         ⠪  EXPLAIN PLAN
                        [SET STATEMENT ID = char]
                        [INTO [user.]table]
                        FOR sql_statement


         祢   SET              ᯥ       ᮤন
         ᫮     STATEMENT_ID     ⮫  STATEMENT_ID    
         ࠬ  = char           ப  믮   室-
                                      ⠡.  ᫨ 饭, -
                                     祭 ⮣ ⮫  㤥 NULL.

                    INTO [user.]table  뢠  室   ⠡.
                                     user -  ⮩ ⠡. 
                                     㬮砭 user  -  짮⥫,
                                     믮訩  EXPLAIN PLAN
                                      㬮砭 table - PLAN_TABLE.

                    FOR sql_statement ⥪  SELECT, INSERT,
                                     UPDATE     DELETE.   
                                     EXPLAIN PLAN   -
                                       sql_statement.


         砭  室 ⠡   ᮧ   믮-
          ᯮ-    EXPLAIN  PLAN.    ᮧ 
             ⠡, ᯮ짮  ਬ஬,      
                    ਡ⨢ ⥫  䠩 XPLAINPL.SQL. -
                       ᯮ짮  㣮  ⠡楩,
                       ᮤঠ ⮫  ⥬   
                    ⨯ ,    ਢ   ਬ.  
                    ଠ樨   ᯮ  䠩  XPLAINPL.SQL 
                    襩 樮 ⥬      "㪮-
                     짮⥫  ⠫樨".

                    ᫥ 믮  EXPLAIN PLAN ॡ㥬
                    ப   ।    ᮤঠ  ⮫
                    STATEMENT_ID.  ப ⭮  
                    祭 ࠢ 㪠      祭
                    char    SET  STATEMENT_ID.     㬮砭
                    祭 ⮣ ⮫ (STATEMENT_ID) ।-
                     "NULL",  室  㤮⮢,   
                    㪠 ᮮ⢥饥 SET -  祭, ᮡ

                                   -- 192 --



                    ᫨ 室 ⠡ ᮤন ଠ  
                    믮 ᪮쪨 ஢.
                    
                      ࠢ筨  SQL - ࠬ         5-97


                     EXPLAIN  PLAN   ⠪ 믮 
                    ⠢  ।⢠ ஢ SQL.

                     ଠ樥  樨  १⮢  -
                      EXPLAIN PLAN   -
                     7 - "।⢠  ⨪ ந⥫쭮"
                    - 㬥 "ORACLE RDBMS 㪮⢮  ன-
                     ந⥫쭮".

         ਬ     ᬮਬ ᫥騩  EXPLAIN PLAN:

                    EXPLAIN PLAN
                       SET STATEMENT_ID = 'RAISE_IN_CHICAGO'
                       INTO OUTPUT
                       FOR UPDATE EMP
                              SET SAL = SAL * 1.10
                              WHERE DEPTNO =
                                     (SELECT DEPTNO
                                          FROM DEPT
                                          WHERE LOC = 'CHICAGO')

                      믮 ᫥騥 㭪樨:

                         * ।   믮  ᯥ-
                             SELECT
                         * ⠢ ப,  뢠騥  믮-
                             㪠 ⠡ OUTPUT
                         * 砥     ᥬ     ப     ⮫
                           STATEMENT_ID  祭 'RAISE_IN_CHICAGO'.

                    騩    SELECT  訢  ⠡
                    OUTPUT  頥  믮:

                    SELECT OPERATION, OPTIONS, OBJECT_NAME
                       FROM OUTPUT
                       WHERE STATEMENT_ID = 'RAISE_IN_CHICAGO'

                      㤥 룫拉 ᫥騬 -
                    :

                    OPERATION          OPTIONS      OBJECT_NAME
                    

                    FILTER
                    TABLE ACCESS       FULL         EMP
                    TABLE ACCESS       FULL         DEPT

         易   7 - "।⢠ ⨪ ந⥫쭮-
         ⥬       "  -  㬥  "ORACLE  RDBMS  㪮⢮ 
                    ன ந⥫쭮".
             
                  ࠢ筮 㪮⢮   SQL        5-98

                                   -- 193 --




E                            F

E         FETCH (஥ SQL)                        F


         祭 롨ࠥ      ப.  ஥
                     SQL FETCH 樮   ᫥-
                     롨ࠥ ப  砥  ⥪-
                    饩 ப ᮮ⢥騬 host - ६.

         ।뫪 ।⥫쭮    , 
                    ᮤন SQL -  SELECT.  ᪮
                    SQL     ⮢ (PREPARE)  ,
                    ,  ⠭ ਯ    
                    । 믮 OPEN.

         ⠪  EXEC SQL [FOR :host_integer]
                       FETCH cursor_name
                         INTO host_variable_list

                     ᪮ SQL, ⮤ 4:

                    EXEC SQL [FOR :host_integer]
                       FETCH cursor_name
                          USING descriptor

         祢   FOR :host_integer ᨬ쭮 ᫮ ப, 롨-
         ᫮                      ࠥ  室 ६.
         ࠬ
                    cursor_name      㪠뢠   ,  ।-
                                      ஬       DECLARE
                                     CURSOR,   ।-
                                         ணࠬ   
                                     FRTCH.

                    INTO              ࠧ । host -  -
                  host_variable_list ६,     
                                     ।  ணࠬ. ᫨ -
                                     -     ६  -
                                     ᨢ,   host - ६
                                       ᨢ.

                  host_variable_list ⢮,  饥    
                                        host  -   ६,
                                     ࠧ 묨 ( -
                                      樨஢  -
                                      ६).

                    descriptor        ਯ,   ।
                                     ࠭ ஬ DESCRIBE.


         砭       । (DECLARE)  
          ᯮ- (OPEN).   믮  FETCH -
                .  .  ᠭ -
                     OPEN.


                                   -- 194 --



                     SELECT, 易  ᮬ,   
                    ᮤঠ ࠧ INTO;  ⮣  ࠧ -
                      ᯨ᪮ host - ६    -
                    祭   FETCH.
                    
                      ࠢ筨  SQL - ࠬ         5-99





















































                                   -- 195 --




                     FETCH 뢠 ப  ⨢ host -
                    ६,     ᮤঠ  १.
                    ୠ ६ ⠭    "-1",
                    ᫨ ᮮ⢥ host - ६  -
                    祭 null.

                    ⢮ 롨ࠥ ப ।  ࠧ-
                     室  ६   祭 易-
                    ⥫쭮 ६  ࠧ FOR.  ਬ,  ᫨
                    室 ६  ᠭ    ᨢ ࠧ-
                     100,  ORACLE ⠥  100 ப.
                    ࠧ FOR   ᯮ짮  ࠭祭
                    롮ન  75 ப.

                    ᫨  ⠡  ⠫ ப,   -
                    樮  ᫥ ப,  ࠭ -
                    祭 ᢠ host - ६, ᯥ-
                    ஢ ࠧ INTO  FETCH / ࠧ
                    USING  OPEN CURSOR.

                    ᫨   騩   樮஢ 
                    ᫥ ப    ⠡   ,  SQLCODE
                    ⠭  +100 ( +1403   ०  ᮢ-
                    ⨬  ᨥ 5).  ⮬ 砥 ᫨ 롨-
                     祭  null,  祭  ᮮ⢥
                    host - ६  .

                     ,    ஬ FETCH  -
                    樨 ࠧ "AT". ⢥  -
                     ᯥ   DECLARE CURSOR.

                    FETCH    롨ࠥ ப ⮫-
                       ࠢ. ᫨    -
                      । ப, 室  
                      롨 ப  ப.

        ਬ     騩 ਬ  ᯮ짮 -
                     FETCH:

                    EXEC SQL FETCH my_cursor INTO :empno, :ename

         易   PREPARE,  DECLARE CURSOR,  OPEN cursor,
         ⥬       CLOSE cursor.
                    
                  ࠢ筮 㪮⢮   SQL        5-100













                                   -- 196 --




E                            F

E         ࠧ FOR (஥ SQL)                    F


         祭 ࠭稢 ⢮ ७   ࠡ  
                    ᨢ    SELECT,  DELETE,  INSERT,
                    FETCH  EXECUTE.

         ।뫪 .

         ⠪  FOR :host_integer


         祢   FOR :host_integer    ᯮ짮  ࠧ  FOR
         ᫮                      ⢮  믮 
         ࠬ                   ࠢ       ࠧ-
                                      ᨢ host -  ६-
                                      (  ᨢ -
                                      ६)  host_integer.

         砭  ࠧ FOR     易⥫쭠.   ᨢ   host   -
          ᯮ- ६   ࠧ ࠧ୮. -
              ࠧ FOR 饭,   ᫥  ⢠
                    ࠡ⠭ ⮢   롨ࠥ   쭠
                    ࠧ୮.

         ਬ     騩 ਬ  ᯮ짮 -
                     FOR:

                    limit : constant integer := 200
                    ename_1 : name_array (1..100) -- 砭: ᨢ
                    ename_2 : name_array (1..50)  --   ࠧ
                                                  -- ࠧ୮
                    EXEC SQL FOR :limit
                       DELETE FROM EMP            -- delete 㤥
                           WHERE ENAME = :ename_1 -- 믮
                             AND ENAME = :ename_2 -- 50 ࠧ

         易   EXECUTE  FETCH.
         ⥬
                    
                      ࠢ筨  SQL - ࠬ         5-101















                                   -- 197 --




E                            F

E    GRANT (ଠ I)                                    F


         祭 ।⠢    

         ।뫪  ᪫祭 ,  짮⥫ 
                    ᢮  ᮡ⢥  ஫,  ॡ  ਢ
                    DBA.

         ⠪  GRANT database_priv [,database_priv] ...
                      TO user [,user] ...
                      [ IDENTIFIED BY password [,password] ...]

         祢 ᫮
          ࠬ
                 database_priv      ᯥ   ᪮-
                                       ਢ       :
                                    CONNECT, DBA, RESOURCE.
                 user                    饥
                                     짮⥫  . -
                                       㦥  , ⮣
                                     ᯮ  ।-
                                    ⠢ ਢ.  ᫨  -
                                     짮⥫ ,   -
                                                       
                                    ᯥ஢묨 ਢﬨ.
                 password           ᯥ ஫  -
                                     짮⥫. ᮪ 짮-
                                    ⥫  ஫   ᮤঠ
                                      ⢮ ⮢.
                                     㬥  ᯮ  
                                     祭 ஫  -
                                     짮⥫,   -
                                        .  ஫
                                    易⥫쭮    -
                                        GRANT  ᯮ
                                      ਢ -
                                    騬 짮⥫.

         砭  ਢ CONNECT   (⠭)  
          ᯮ-   짮⥫.    ਢ,
                     ࠡ 
                    ꥪ⠬,      ਢ  -
                    . ਬ -  짮⥫  믮
                       ⠡  ᫮    DICTIONARY.
                     ਢ CONNECT,  짮⥫  ᮧ-
                     , ᨭ   裡.
                    ਢ RESOURCE ࠧ蠥 짮⥫ ᮧ-
                     ꥪ  ,  :  ⠡, -
                    ,    ᫥⥫쭮.   ଠ
                     GRANT RESOURCE  ࠭稢 ਢ-
                        ᠬ   ⠡ ࠭.
                     ࠡ   ।묨  ࠭⢠  -



                                   -- 198 --



                     ମ II  GRANT.
         
                  ࠢ筮 㪮⢮   SQL        5-102


                    ਢ DBA     짮⥫   
                    ⠭  ਢ,  ॡ㥬   ࠡ 
                    ꥪ⠬  . ஬ ⮣, ਢ DBA
                    ࠧ蠥   짮⥫  믮  ।
                    ⢨     ,  ਬ:
                    CREATE TABLESPACE  CREATE ROLLBACK SEGMENT.

                     ਢ DBA, 짮⥫ :

                    * 믮    SELECT    ⠡ 
                      
                    * ᮧ ꥪ   㣨 짮⥫
                    * ⬥ ꥪ 㣨  짮⥫, 
                      ⠡, , ᨭ   裡
                    * ।⠢ ࠧ ਢ
                    * ᮧ 騥 (PUBLIC) ᨭ  裡
                    * 믮  ᯮ/  

         ਬ     ।⠢ 짮⥫ SCOTT    ஫
                    TIGER       ᮧ ⠡-
                    ,  ᫥ :

                    GRANT CONNECT, RESOURCE TO SCOTT IDENTIFIED BY TIGER

                     SCOTT  ᮧ ꥪ   ⠡筮
                    ࠭⢥ SYSTEM.

                    ⮡ ।⠢  ਢ DBA 㦥 -
                    饬 짮⥫ SCOTT,   ᫥  -
                    :

                    GRANT DBA TO SCOTT

                     ஫      易⥫쭮,  ⠪  
                    SCOTT - 㦥 騩 짮⥫    -
                    .

                    짮⥫ SCOTT    ᢮ ஫ 
                    COWSLIP,  ᫥ :

                    GRANT CONNECT TO SCOTT IDENTIFIED BY COWSLIP

         易   ALTER USER  REVOKE.
         ⥬
    
                      ࠢ筨  SQL - ࠬ         5-103








                                   -- 199 --




E                            F

E         GRANT (ଠ II)
                                                         F

         祭 ।⠢        (⠡-
                    筮 ࠭)      
                    ࠭祭  ꥬ , 砥 짮-
                    ⥫  ࠡ.

         ।뫪 ॡ ਢ DBA.

         ⠪  GRANT RESOURCE [ (quota [K|M]) ]
                      ON tablespace
                      TO { PUBLIC | user [,user] ...}

         祢 ᫮
          ࠬ
                 RESOURCE            㪠뢠   ,      ᯨ᮪
                                     짮⥫     ᮧ
                                     ꥪ         ⠡筮
                                     ࠭⢥.
                 quota               ᯥ  楫 ᫮,
                                     ।⠢饥  ᮡ ⢮
                                     ⮢ , ஥  
                                     ᥡ  । 짮⥫.
                                     ⢨ ࠬ quota -
                                     砥  ࠭祭 ᯮ짮-
                                         ⠡筮  -
                                     ࠭⢠. 祭 0 ⬥ 
                                     짮⥫ ਢ RESOURCE
                                         ⠡筮  -
                                     ࠭⢠.  ᫨  ᫮ ᫥
                                     "",  㬭  1024, -
                                      - "", 㬭  1048576.
                 tablespace           饣     ⠡筮
                                     ࠭⢠
                 user                ᯥ 饣  -
                                     ⥫.  祢  ᫮  PUBLIC
                                     砥 ।⠢ ਢ
                                             ⠡筮
                                     ࠭⢠   ᥬ  짮⥫
                                      .


         砭  ଠ II  GRANT ।⠢  ࠢ
          ᯮ-  ମ I ⥫  .  ᯮ짮
             ன    ।⥫쭥,  ⠪  DBA 
                    ⥫  ࠢ ।-
                        .


         ਬ     ᫨  - DBA,    ।⠢ 짮⥫
                    SCOTT  ᯮ짮 ᨬ쭮 - 10 -
                    ⮢   ⠡筮  ࠭⢥  FINANCE,
                     ᫥ :


                                   -- 200 --



                    GRANT RESOURCE (10M) ON FINANCE TO SCOTT

         易   ALTER USER, REVOKE, CREATE SEQUENCE.
         ⥬
    
                  ࠢ筮 㪮⢮   SQL        5-104





















































                                   -- 201 --




E                            F

E         GRANT (ଠ III)                             F


         祭 ।⠢ ࠧ ⨯ 㯠  ꥪ⠬ -
                     ,  ⠪  ⠡,    ᫥-
                    ⥫쭮.

         ।뫪     楬  ꥪ,    GRANT
                    OPTION   ⮣ ꥪ   ਢ DBA.

         ⠪  GRANT {object_priv [,object_priv] ...} | {ALL
                          [PRIVILEGES] } ON [user.]object
                       TO { user | PUBLIC } [,user] ...
                       [ WITH GRANT OPTION ]

         祢 ᫮
          ࠬ
                 object_priv          ⠡  -    :  ALTER,
                                     DELETE, INDEX,INSERT,REFERENCES,
                                     SELECT, UPDATE.
                                      ஢  -   : DELETE,
                                     INSERT, SELECT,UPDATE.
                                      ᫥⥫쭮⥩    ALTER
                                      SELECT.
                                     ਢ UPDATE    REFERENCES
                                       ࠭稢  
                                         뫮  ।-
                                      ⮫殢.  ⠪ -
                                     ⠢    ਢ    
                                     ⮫殢 ᫥騩:

                                  GRANT column_priv [,column_priv]
                                   (column [,column]...)
                                      ON [user.]table
                                      TO {user | PUBLIC} [,user] ...
                                      [WITH GRANT OPTION]
                 column              ᯥ ⮫ ⠡
                 ALL PRIVILEGES        祢  ᫮  筮
                                                ਢ
                                     object_priv.
                 ON [user.]object    ᯥ ⠡,  
                                     ᨭ,     -
                                     .
                 TO user             㪠뢠 짮⥫(), -
                                       ।⠢  ਢ-
                                     .
                 PUBLIC              ।⠢    짮⥫
                                      ,   ⠪  -  
                                     짮⥫,   ।-
                                     ⠢ 㪠 ਢ.
                 WITH GRANT          㪠뢠,   짮⥫, -
                 OPTION              ஬  ।⠢  ਢ-
                                     ,   ᢮ । ।-



                                   -- 202 --



                                     ⠢   㣨 짮⥫.
                 
                      ࠢ筨  SQL - ࠬ         5-105


         砭   ᥣ    ਢ   
          ᯮ- ⠡.
             ALTER -  ਢ   믮  ALTER
                    TABLE.
                    DELETE - ਢ  㤠 ப  ⠡.
                    INDEX - ਢ  ᮧ   ⠡.
                    INSERT - ਢ  ⠢ ப  ⠡.
                    REFERENCES -  ਢ    뫪    ⠡
                     ࠭祭  ⠡  ⮫.
                    SELECT - ਢ     ⠡. ⮡
                    ࠭        ।
                    ⮫殢 ⠡, ᮧ   ᭮ 
                    ⮫殢      ।⠢ ਢ  
                     ⮣ .
                    UPDATE -  ਢ  䨪 ப  ⠡-
                    .    ⮫쪮  ⮫, 
                    㪠.
                    ਢ, ।⠢ । 짮-
                    ⥫          ⮬ ਢ
                    PUBLIC.  ਬ,  ᫨ Scott  ।⠢  Blake
                    ਢ SELECT  ⠡ EMP,  ⠪ ।-
                    ⠢   ਢ ᥬ  (PUBLIC),    ⬥
                    ਢ  PUBLIC      ⬥  ਢ  
                    Blake,  ᫥   童 PUBLIC.
                    ࠭稢     ⠡  ० ᥣ
                    ᮧ    ஢  ।⠢ -
                      ஢.

                     GRANT   ᨭ ८ࠧ   -
                      ᮮ⢥騬 ꥪ⠬.

         ᫥⥫쭮

                     ᯮ짮   ᫥⥫쭮   -,
                    ஬  室 ।⠢  -
                     SELECT.  ⢥⢥ - ਢ ALTER
                    ।祭    믮   ᫥⥫-
                      ALTER SEQUENCE.  ਢ   -
                    짮 ᫥⥫쭮  । -
                     짮⥫, 㪠 ࠧ WITH GRANT OPTION.

                    ORACLE  ।,   ।⠢ ࠢ
                    㯠  ᫥⥫쭮,     ⠡, ⠪
                      ꥪ (᫥⥫쭮,  ⠡, -
                    ,      )  짮⥫  
                     ࠧ .
    
                  ࠢ筮 㪮⢮   SQL        5-106






                                   -- 203 --




         ਬ     ⮡ ।⠢   ࠢ    ⠡
                    BONUS 짮⥫ JONES    -
                    ⠢ ਢ 㣨 짮⥫,  -
                    :

                    GRANT ALL ON BONUS TO JONES WITH GRANT OPTION

                     ࠧ襭 ᥬ 짮⥫  -
                        ⠡ GOLF_HANDICAP, :

                    GRANT SELECT, UPDATE ON GOLF_HANDICAP TO PUBLIC

         ਬ ᫥⥫쭮

                    ⮡ ।⠢       ᫥⥫쭮
                    ESSEQ 짮⥫ ELLY  짮⥫  BLAKE,  
                     믮 ᫥騩 SQL - :

                    GRANT SELECT ON ESSEQ TO BLAKE

                     BLAKE   ஢   ᫥-
                    ⥫쭮 짮⥫ ELLY ⠪ :

                    SELECT ELLY.ESSEQ.NEXTVAL FROM DUAL

         易   REVOKE.
         ⥬
                                 
                      ࠢ筨  SQL - ࠬ         5-107





























                                   -- 204 --




E                            F

E         INSERT                                        F


         祭     ப  ⠡   ⠡,
                    饩  ᭮ .

         ।뫪    楬 ⠡    
                     ਢ INSERT   ਢ DBA.

         ⠪  INSERT INTO[user.]table [column [,column] ...)]
                        {VALUES (value [,value]...) | query}

         祢   user              짮⥫   -   
         ᫮                      ⠡.
         ࠬ
                    table             饩 ⠡, 
                                      ᨭ.    㬮砭   -
                                     짮⥫, 뤠訩   
                                     INSERT.

                    column           ⮫ ⠡  .

                    value            祭, ᮮ⢥饥  -
                                     樨 ⮫    ᯨ᪠ ⮫-
                                     殢. value    ࠦ-
                                     . ᫨   value    NULL,  
                                      ᮮ⢥⢮  ⮫-
                                       ⨯ .

                    query             SELECT,    騩
                                     ⮫쪮 祭, ᪮쪮 㪠-
                                        ᯨ᪥ ⮫殢.   
                                     ᮤঠ ࠧ ORDER BY  UPDATE.

         砭   INSERT,  ਬ塞    ࠧ  VALUES,
          ᯮ- ᥣ       ப.  ,
             ।塞 columns,  ᯥ஢-
                     祭.   ⮫,   饭      ᯨ᪥
                    columns,  ᮤঠ 祭 NULL ,  ᫥-
                    ⥫쭮,    ᠭ NOT NULL.

                     INSERT,    ஬  ᯮ 
                    (query)  ࠧ VALUES,    -
                       ப  (⮫쪮,  ᪮쪮  饭
                    ᮬ). ᫥ ⮣,   믮, 
                    ⮫ ᮯ⠢    樨  ⮫栬,
                    ᯥ஢묨 ᫥ 祢  ᫮  INSERT
                    ( ᥬ,  ᫨ 祣  ᯥ஢). -
                      뫠    ⠡,    
                    ப ⠢.

                    祭 易⥫쭮       ᮯ⠢
                    ᥬ ⮫栬, ।  NOT NULL.



                                   -- 205 --



                    ᫨  ᪮ 㪠뢠  ᯨ᮪  ⮫殢,  
                       ⠢   ᮮ⢥⢨ 祭;
                    ᫨ ,  祭   ᯥ祭 ᥬ
                    ⮫栬. ᫨  ᯨ᮪ ⮫殢 饭,  祭
                     㪠뢠  浪 ७ ᯮ-
                     ⮫殢 ⠡.  樨  ।-
                    , ᨢ           ᫮      
                    USER_TAB_COLUMNS.

                    ᫨    INSERT  -
                    頥 ப,  ⠡  㤥   
                    ப.
                    
                  ࠢ筮 㪮⢮   SQL        5-108













































                                   -- 206 --




         ਬ     ⠢  ப  ⠡ DEPT :

                    INSERT INTO DEPT VALUES(50, 'PRODUCTION',
                                                'SAN FRANCISCO')

                     ⠢  ப    ⮫栬  ⠡
                    EMP,  祭 NULL  ᫮    筮
                    樨 :

                    INSERT INTO ENP (EMPNO,ENAME,JOB,SAL,COMM,DEPTNO)
                       VALUES(7890,'JINKS','CLERK',1.2E3,NULL,40)

                     ஢ ஢  १⮢:  ⠪
                    㤭,   ᨮ  ॢ 25%,  
                    ⠡ BONUS, -
                    :

                    INSERT INTO BONUS
                       SELECT ENAME, JOB, SAL, COMM
                          FROM EMP
                          WHERE COMM < 25 * SAL
                            OR JOB IN ('PRESIDENT','MANAGER')

         易   DELETE  UPDATE.
         ⥬
                    
                      ࠢ筨  SQL - ࠬ         5-109































                                   -- 207 --




E                            F

E         INSERT (஥ SQL)                       F


         祭  ப  ⠡.

         ।뫪    楬 ⠡    
                     ਢ INSERT   ਢ DBA.

         ⠪  EXEC SQL [AT dbname] [FOR :host_integer]
                     INSERT INTO [user.]table [(column [,column]...)]
                     {VALUES(values_element [,values_element]...) | query}

         祢   AT dbname        易⥫쭠 ࠧ, ।-
         ᫮                       㬠稢  .
         ࠬ
                    FOR :host_integer  ࠭稢  ᫮  ⮢
                                     ᨢ, 易  믮
                                     樨 INSERT.

                    [user.]table      饩 ⠡, 
                                      ᨭ.   ᯥ-
                                     㥬 ⮫   ਭ-
                                      ⠡, 㪠  ࠧ
                                     INTO.

                    VALUES           ।⠢  ப  -
                                     ,  . -
                                     祭ﬨ ப     祭
                                     祢 ᫮,  ⠭,  SQL -
                                     ࠦ  host - ६,
                                     ᯥ஢  ࠧ. -
                                      host - ६,  ᯥ-
                                     ஢   祭, 
                                      ᠭ  ணࠬ  -
                                     ६. ⢮  祭 
                                     ⮬ ᯨ᪥      ࠢ
                                        ⮫殢.

                    values_element    :
                                        * host - ६  易-
                                          ⥫쭮 ୮  -
                                          , 
                                        *  ࠦ

                    query             SELECT,    騩
                                     ⮫쪮 祭, ᪮쪮 㪠-
                                        ᯨ᪥ ⮫殢.   
                                     ᮤঠ ࠧ ORDER  BY    FOR
                                     UPDATE.

         砭  ᫨ host - ६  ᯨ᪥ VALUES - ᨢ,
          ᯮ-  㤥 믮 ⢮ ࠧ, ࠢ
              ।  ࠧ୮⥩ ᨢ  祭
                     ࠬ FOR.


                                   -- 208 --



                    ᫨ ࠧ WHERE  頥  ப,    
                    ப  ⠡ ⠢  㤥  SQLCODE -
                    ⠭  +100 ( +1403 -   ०  ᮢ-
                    ⨬  ᨥ 5).
                    
                  ࠢ筮 㪮⢮   SQL        5-110





















































                                   -- 209 --




          ᮢ⨬  ᨥ 5

                    ANSI ।   ⠭       
                    "+100"  樨 " 롮ન".   6 
                    㬮砭 ⠪ ࠧ㬥 +100.   ᠭ
                    樨 ᮢ⨬    ᨥ  5,  ⠢饩
                    ORACLE   ⮩ 樨 +1403,  -
                      "㪮 짮⥫  ४-
                    ࠬ ORACLE".

         ਬ    騥 ਬ  ᯮ짮  -
                     INSERT:

                    EXEC SQL FOR :array_size INSERT INTO EMP
                           (ENAME, EMPNO, SAL)
                           VALUES(:ename, :empno, :sal);

                    EXEC SQL INSERT INTO new_emp (ENAME,EMPNO,SAL)
                         SELECT ENAME, EMPNO, SAL FROM EMP
                         WHERE DEPTNO = :deptno;

         易   EXECUTE; ࠧ FOR.
         ⥬
                    
                      ࠢ筨  SQL - ࠬ         5-111

































                                   -- 210 --




E                            F

E         LOCK TABLE                                    F


         祭 筮  ४⨥  ⮬᪨  ஢  
                    楫 ஢      ᪮쪨 ⠡ 
                    ᯥ筮 ०, 饥  ࠧ 
                    㠫஢   ६ 襩 ࠡ.

         ।뫪 室    楬  ⠡   
                    ਢ DBA,       ਢ   ALTER,
                    DELETE, INSERT, SELECT  UPDATE.

         ⠪  LOCK TABLE [user.]table [, [user.]table]...
                      IN lockmode MODE [NOWAIT]

         祢   user              ⠡.
         ᫮ 
         ࠬ  table             ⠡,   ன
                                       .

                    lockmode          :ROW SHARE,ROW EXCLUSIVE,
                                     SHARE UPDATE, SHARE, SHARE  ROW
                                     EXCLUSIVE  EXCLUSIVE.

                    NOWAIT           ᯥ,    ORACLE   
                                       ⠡, ᫨ 
                                     ஢ 㣨 짮-
                                     .        ORACLE
                                     頥  ࠢ ணࠬ
                                     짮⥫.

         砭  ᪫⥫ ஢  (exclusive)  ࠧ
          ᯮ- 믮 ᮢ  ⨬ ⠡栬,  -
                ࠡ.

                    塞 ஢ (share)  ࠧ  ࠫ-
                    쭮 믮 ᮢ,   -
                    樨 ⠡.

                    ஢ ⨯     "Row     share"     ᪠
                    ࠫ   ⠡.   -
                    짮⥫ ஢ ᥩ  ⠡    ᪫-
                    ⥫쭮 ०. ROW SHARE  SHARE UPDATE 
                    ᨭ.

                    ஢ ⨯ "Row  Exclusive"    ROW
                    SHARE,  ஬ ⮣ -  ஢  -
                     SHARE.  ஢ 믮 ⮬-
                    ᪨  , ⠢  㤠.

                    ஢ ⨯ "Share Row Exclusive" ᯮ-
                      ᬮ ᥩ ⠡    㣨
                    짮⥫ ᬠਢ ப,   
                    ஢ ⠡  ० SHARE    䨪
                    ப.

                                   -- 211 --



                    ஢ ⨯ "Share Update"  ᨭ
                    ROW SHARE    ᮢ⨬  ࠭-
                     ﬨ ORACLE RDBMS.
                    
                  ࠢ筮 㪮⢮   SQL        5-112






















































                                   -- 212 --




                     ⨯ ஢  ਬ  
                     ⮩    ⠡ ६,  㣨 - ⮫쪮
                    㠫쭮. ਬ,  ᪮쪮 짮⥫
                     ⠭ SHARE - ஢    ⮩
                     ⠡  ६,     ஢   ⨯
                    EXCUSIVE -  ⮫쪮   짮⥫.   
                    ᠭ  ⢨  ० ஢
                       12 - "⨢稢  -
                    ࠫ쭠 ࠡ⪠" 㬥 "ORACLE RDBMS -
                    ⢮   ".

                     ஢   ⠡       業,
                    ᪮쪮  짮⥫    .
                     ஢, ⠡ ⠥ ⠪
                     砭 ࠭樨 ( 믮 commit 
                    rollback).

                    ᫨   LOCK  TABLE     믮,
                    ਬ - ⠪  㣮 짮⥫ 㦥 -
                    ஢ ⠡,  LOCK TABLE  ,  
                     ᬮ  㦥,   頥 -
                    ࠢ 짮⥫    (᫨    ᯥ஢
                    NOWAIT). ᫨ NOWAIT ᯥ஢,   -
                    १ࢨ஢ 㣨 짮⥫, ORACLE -
                    頥 ࠢ 짮⥫    訡-
                    .

                    ஢   蠥 㣨 짮⥫
                       ⠡.     ⠪  
                     ⠡, ᫥⥫쭮 - 騥  -
                    뢠騥 짮⥫    .

                    ஢  ८ࠧ뢠   ஢
                    ᮮ⢥饩 ⠡  ⠡.

         ਬ      ஢ ⠡ EMP  ᪫⥫쭮 ०-
                     (  ᫮ -  , ᫨ ⠡ -
                    ஢ 㣨    짮⥫),   室
                    :

                    LOCK TABLE EMP IN EXCLUSIVE MODE NOWAIT

         易   DELETE,   INSERT,    UPDATE,    COMMIT,
         ⥬       ROLLBACK,  SAVEPOINT.   12 - "⨢-
                      ࠫ쭠 ࠡ⪠" 㬥 "ORACLE
                    RDBMS 㪮⢮   ".
               
                      ࠢ筨  SQL - ࠬ         5-113










                                   -- 213 --




E                            F

E         NOAUDIT (ଠ I)                             F


         祭 筮    ⬥ 䥪 ।-
                    饣 ⥬  AUDIT.

         ।뫪  ଠ  NOAUDIT 㦤  ਢ-
                     DBA.

         ⠪  NOAUDIT {system_option [,system_option]...| ALL}
                       WHENEVER [NOT] SUCCSESSFUL]

         祢   system_option      ᫥ ⥬  -
         ᫮                      権: CONNECT,DBA,NOT  EXISTS 
         ࠬ                   RESOURCE.

                    ALL              ।⠢  樨 ࠧ.

                    WHENEVER         ᯥ,    ॣ
                    [NOT]              몫祭  ᯥ-
                    SUCCSESSFUL         㤠筮  饭.
                                     ᫨   ࠧ 饭,  -
                                      ⪫砥     
                                     ᯥ譮, ⠪    㤠筮
                                     饭.

         砭   ᠭ ⥬ 権  .    ᠭ
          ᯮ-  AUDIT.
         
         ਬ     ४饭 ॣ樨 ᯥ譮  -
                    ᮢ 室 :

                    NOAUDIT RESOURCE WHENEVER SUCCSESSFUL

         易   AUDIT  I  GRANT.
         ⥬
                                                    
                  ࠢ筮 㪮⢮   SQL        5-114


















                                   -- 214 --




E                            F

E         NOAUDIT (ଠ II)                            F


         祭 筮      ⨢
                    䥪 ।饣  AUDIT   㬠-
                    稢 権 ॣ樨 㯠  ⠡栬.

         ।뫪    楬 ꥪ,   -
                    ⠥ NOAUDIT.    権 ॣ樨,
                    ⠭  㬮砭 (DEFAULT), 室-
                      ਢ DBA.

         ⠪  NOAUDIT {option [,option]... | ALL}
                       ON {object | DEFAULT }
                       [WHENEVER [NOT] SUCCSESSFUL}

         祢   option            ⠡ ਥ ᫥騥 樨
         ᫮                      AUDIT,COMMENT,DELETE,GRANT,INDEX,
         ࠬ                   INSERT,LOCK,RENAME,SELECT,UPDATE.
                                      ஢ ਥ ᫥騥 樨
                                     AUDIT,COMMENT,INSERT,DELETE,GRANT,
                                     LOCK,RENAME,SELECT,UPDATE.
                                      ᫥⥫쭮⥩ ਥ
                                     樨 ALTER,AUDIT,GRANT,SELECT.
                                      ᨭ ਥ     -
                                     樨,    ⠡ (⠪  
                                     ⢨ ॠ쭮 ந  ⠡-
                                     栬).

                    ALL              砥  ਥ 樨.

                    object            ⠡,   
                                     ᨭ,  楬 ண 
                                     .

                    DEFAULT          뫠   㬠稢 樨
                                     ॣ樨 㯠,  ⠭-
                                      ஬  -
                                           ᮧ
                                     ⠡. DEFAULT    ਬ  
                                     ࠬ  ᫥⥫쭮.

                    WHENEVER         ᯥ,    ॣ
                    [NOT]              몫祭  ᯥ-
                    SUCCSESSFUL         㤠筮  饭.
                                     ᫨   ࠧ 饭,  -
                                      ⪫砥     
                                     ᯥ譮, ⠪    㤠筮
                                     饭.

         砭   NOAUDIT 㪠뢠 ORACLE  ४  -
          ᯮ-  ᯮ짮 ⠡  ஢. -
              ஬ DDL, NOAUDIT ⠪ 뢠 ⥪-
                     ࠭.


                                   -- 215 --



         ਬ     ४饭   ॣ樨  ⮪  
                    ⠡ EMP :

                    NOAUDIT UPDATE ON EMP

                     ४饭 类 ॣ樨 㤠  -
                    ⮪ ࠡ    ⠡楩 EMP 믮 ᫥騩
                    :

                    NOAUDIT ALL ON EMP WHENEVER NOT SUCCSESSFUL

         易   AUDIT ଠ II  GRANT.
         ⥬
                                                     
                      ࠢ筨  SQL - ࠬ         5-115












































                                   -- 216 --




E                            F

E         OPEN cursor (஥ SQL)                  F


         祭 ।  ࠭     .
                    ⥬ 믮      
                    ⠢  host - ६, 祭
                    ࠧ USING  ࠧ  WHERE.

         ।뫪 । ⨥    .

         ⠪ EXEC SQL OPEN cursor_name [USING host_variable_list]

                     ᪮ SQL, ⮤ 4:

                   EXEC SQL OPEN cursor_name [USING DESCRIPTOR_name]


         祢   cursor_name      㪠뢠  ࠭  
         ᫮                        DECLARE CURSOR
         ࠬ                   . .  ᠭ ⮣ -
                                        ⮩ .

                    USING              ᯨ᮪ host - ६,  -
                    host_variable-list   ⠢  -
                                       DECLARE  CURSOR.  -
                                        믮  樮:
                                      ६   ࠧ USING
                                     筮 ⫨  , -
                                        PREPARE.

                    descriptor_name  뫪   १ ।饣
                                      DESCRIBE.


         砭   OPEN cursor । ⨢ -
          ᯮ-  ப    樠   砫 ⮣
             ⢠. 祭 host -  ६    
                     ⠢  .   OPEN
                    cursor ⢨⥫ ⮫쪮  祭   ਪ-
                     ணࠬ.

                         ணࠬ .  ஬
                    ⮣,    , 뤠  CLOSE.

                     OPEN cursor ।    -
                    樮   ।⢥ । ࢮ -
                     ⨢ ⢠.      ப
                     롨,     FETCH.

                    ᫥    室 host - ६
                     ஢  ୮ .  ⮡ -
                     -  室  host  - ६ ,
                    ᫥⥫쭮, ⨢   ⢮,   室



                                   -- 217 --



                      .
                    
                  ࠢ筮 㪮⢮   SQL        5-116


                      뢠   ।⥫쭮-
                      .  ⨬      
                    ,  ᫨   .    -
                    ஡ ଠ樨    ४-
                     HOLD_CURSOR  RELEASE_CURSOR   "㪮⢥
                    ணࠬ  ४ࠬ ORACLE".

                    ᫨ ᯮ ᪨ SQL, ⮤ 4 (-
                     DESCRIBE),  host - ६,    
                    뫠 १ ਯ, ⠢  SQL -
                    , ।   .

         ਬ     ਢ  ਬ 뢠 ⨯ ᫥-
                    ⥫쭮 ᯮ짮  OPEN cursor.
                       ,   ,  
                    ଠ , ,  :

                    EXEC SQL DECLARE c CURSOR
                       FOR SELECT ename, empno, job, sal
                              FROM emp
                              WHERE deptno=30;

                    EXEC SQL OPEN c;

                    example: loop
                      EXEC SQL FETCH c INTO :ename, :empno, :job,
                                                    :sal:sal_ind;
                      if sqlca.sqlcode=oracle.error.not_found then
                          exit loop example;
                      end if;

                    EXEC SQL CLOSE c;

         易   PREPARE,  DECLARE CURSOR, FETCH  CLOSE
         ⥬       cursor.
             
                      ࠢ筨  SQL - ࠬ         5-117

















                                   -- 218 --




E                            F

E         PREPARE (᪨ ஥ SQL)         F


         祭 믮 ࠬ᪨ ࠧ SQL  - ,
                      host - ६.

         ।뫪 .

         ⠪  EXEC SQL PREPARE statement_name
                      FROM {:host_string | string_literal}

         祢   statement_name   砥  ࠧࠥ  -
         ᫮                      .  ᫨   㦥 -
         ࠬ                   祭  饬   ,
                                     ।饥  祭 㤥 -
                                     ४.   䨪,
                                     ᯮ㥬   ⥬  ORACLE,
                                        host -  ६-
                                     .

                    :host_string     host -   ६,  ᮤঠ
                                      SQL - , 騩
                                     ࠧ (⮢    믮-
                                     ).    ६    
                                      ⨯  varchar,  string  
                                     .

                    string_literal    ࠫ,     ᮤঠ騩
                                     ⥪ SQL - .


         砭   PREPARE 믮 ࠧ SQL - 
          ᯮ-     .  SQL -   ।
             ࠧ EXEC SQL,    ६   PREPARE
                    - ।.       ஬
                    SELECT  ࠧ FOR  UPDATE.  .    ᠭ
                     SELECT.

                    host -  ६,  ᫥  host_string,
                     蠡.  ⢨⥫  host  -
                    ६    ࠧ  USING  
                    OPEN cursor (室)  ࠧ  INTO  
                    FETCH (室). ⠭ 믮 -
                    .

                    SQL -  ⠢ ⮫쪮 , 
                    믮  ந쭮 ᫮ ࠧ.

         ਬ     騩 ਬ   ᯮ짮 -
                     PREPARE:

                    my_string : string(1..80);
                    get(my_string);

                    EXEC SQL PREPARE s FROM :my_string;

                                   -- 219 --



                    EXEC SQL EXECUTE s;

         易   DECLARE  CURSOR,  OPEN  cursor,  FETCH,
         ⥬       CLOSE cursor.
             
                  ࠢ筮 㪮⢮   SQL        5-118





















































                                   -- 220 --




E                            F

E         RENAME                                        F

         祭 २ ⠡,   ᨭ.

         ।뫪 室    楬  २뢠
                    ꥪ.

         ⠪  RENAME old TO new

         祢   old              ஥  ⠡,    
         ᫮                      ᨭ.
         ࠬ
                    new               砥    ⠡,
                                     , ᨭ.


         砭      ⠡,    -
          ᯮ-   "old"  "new".  old -  ꥪ, -
             饥 ,   new -    (।-
                    ,  new -  ࠢ쭮    ⥬  ORACLE
                    ,  ᯮ㥬 㣨 ꥪ⠬).

                    ਢ  ,  ਭ騥 ஬ -
                    , ७  .

                      ⮩    २
                    ⮫殢.    ᯮᮡ ⠪ २
                    -  믮  : CREATE TABLE ...
                    AA SELECT...  ਬ,  ⠡ STATIC,  
                    ᥣ  ⮫ - OLDNAME,     -
                    ᮧ     ⮫ NEWNAME.   
                    ᫥騬 ࠧ:

                    CREATE TABLE TEMPORARY (NEWNAME)
                       AS SELECT OLDNAME FROM STATIC

                    DROP TABLE STATIC

                    RENAME TEMPORARY TO STATIC

         ਬ          ⠡   DEPT  EMP_DEPT
                    室 믮 ᫥ :

                    RENAME DEPT TO EMP_DEPT

         易   CREATE SYNONYM,  CREATE  TABLE,  CREATE
         ⥬       VIEW.
             
                      ࠢ筨  SQL - ࠬ         5-119







                                   -- 221 --




E                            F

E         REVOKE (ଠ I)                              F


         祭 ⬥ ਢ      -
                   쪨 짮⥫.

         ।뫪 ॡ ਢ DBA.

         ⠪  REVOKE { [CONNECT] [,RESOURCE] [,DBA] }
                      FROM user [,user] ...

         祢   user             騩 짮⥫.
         ᫮     CONNECT,         ਢ,    
         ࠬ  RESOURCE,        ⬥. 祭  㬥-
                    DBA              ⮢  筮 ᮢ    -
                                      GRANT.


         砭   REVOKE 蠥 ᯥ譮   ᫨  
          ᯮ-  ਢ,  ⠫ ⬥.
             REVOKE    - ꥪ,  -
                    楬    짮⥫.  -
                    ,  ⬥ ਢ  CONNECT    짮⥫
                    Blake      ⠡ SALES. -
                    ⠫ 짮⥫, - 稢訥 ࠢ
                    㯠   ⮩ ⠡,   த  -
                    짮. ᫨ Blake     -
                     ,    ࠭ ࠢ ᮡ⢥
                      ꥪ.   ⮣ ᫥ ४
                    㤠 ꥪ 짮⥫, ० 祬 ⬥
                    ਢ CONNECT  ⮣ 짮⥫.

         ਬ     ⮡ ⬥       짮⥫    SCOTT    
                    JOLLY_ROGER  RESOURCe, :

                    REVOKE RESOURCE FROM SCOTT, JULLY_ROGER

         易   AUDIT  GRANT.
         ⥬

















                                   -- 222 --



                  ࠢ筮 㪮⢮   SQL        5-120


E                            F

E         REVOKE (ଠ II)                             F



         祭 ⬥ ਢ  ᯮ짮   -
                    ⠡筮 ࠭⢥.

         ।뫪 室 ਢ DBA.

         ⠪  REVOKE space_privilege ON tablespace
                       FROM user [,user] ...

         祢   space_privilege   ਢ   RESOURCE.  
         ᫮                       ⮫쪮 ਢ  ᯮ-
         ࠬ                    .
                    tablespace       饥 ⠡筮    -
                                     ࠭⢮,   ஬ 짮⥫
                                     뫠  ।⠢   ਢ
                                     RESOURCE.
                    user             騩 짮⥫, ࠭
                                     稢訩  ਢ  RESOURCE
                                         ⠡筮  -
                                     ࠭⢠.

         砭   ଠ   REVOKE ⡨ࠥ  짮⥫
          ᯮ-  ᮧ ꥪ  㪠 ⠡-
              ࠭⢥.  㣨 ꥪ  
                    ᮧ  짮⥫       ⠡筮
                    ࠭⢥,    騥   ।-
                     ⥫ .

         ਬ     ⮡ ⮡  짮⥫  SCOTT  
                    ᮧ   ꥪ      ⠡筮  ࠭⢥
                    SYSTEM, 믮 SQL - :

                    REVOKE RESOURCE ON SYSTEM FROM SCOTT

                    ।騩  ⥭ ᫥饬:

                    GRANT RESOURCE(0) ON SYSTEM TO SCOTT

         易   AUDIT ଠ II  GRANT ଠ II.
         ⥬
    
                      ࠢ筨  SQL - ࠬ         5-121









                                   -- 223 --




E                            F

E         REVOKE (ଠ III)                            F


         祭 ⬥ ਢ 짮⥫    -
                    ⠡栬,  ࠬ  ᫥⥫쭮.

         ।뫪    楬  ꥪ    
                       ।⠢  ਢ   -
                   짮 WITH GRANT OPTION.

         ⠪  REVOKE { object_priv [,object_priv]... | ALL }
                       ON [user.]object
                       FROM { user | PUBLIC } [,user]...

         祢   object_priv       ⠡  -    :  ALTER,
         ᫮                      DELETE, INDEX,INSERT,REFERENCES,
         ࠬ                   SELECT, UPDATE.
                                      ஢-      :DELETE,
                                     INSERT, SELECT,UPDATE.
                                      ᫥⥫쭮⥩    ALTER
                                      SELECT.
                    ALL              ⬥  ਢ
                    ON object        ᯥ  ⠡, -
                                       ᫥⥫쭮, 
                                       ⬥ ਢ.
                                           襩
                                     ᮡ⢥,        
                                       ।⠢ ਢ-
                                      WITH GRANT OPTION.
                    user              짮⥫,   ண 
                                     ⡨ࠥ    ਢ.    FROM
                                     PUBLIC ⬥ ਢ, -
                                     ⠢ ᮮ⢥騬 -
                                     ࠧ,     ⡨ࠥ ਢ-
                                     ,           ⭮
                                     짮⥫.
                    PUBLIC                
                                      짮⥫.

         砭   ଠ   ⬥ ਢ  ⠡
          ᯮ-      ᪮쪨 짮⥫.
             ᫨ 짮⥫    ।
                    ⠡,  ।⠢  祬  짮-
                    ⥫,    㤥  ࠭,    
                     짮⥫  ⬥  ਢ. ⬥
                    ਢ   짮⥫ ⮬᪨ ⬥
                       짮⥫,   । ਢ-
                    .

    
                  ࠢ筮 㪮⢮   SQL        5-122





                                   -- 224 --




         ਬ      ⬥  짮⥫ JONES ਢ  -
                     ⠡ DEPT10, :

                    REVOKE ALTER, DELETE, INSERT, UPDATE
                    ON     DEPT10
                    FROM JONES

                     ⬥  ਢ  DEPT10   -
                    ⥫,      뫨 ।⠢ ,
                    믮 ᫥ :

                    REVOKE ALL ON DEPT10 FROM PUBLIC

                     ⬥ ਢ 짮⥫ BLAKE   -
                    ⥫쭮 ESSEQ, Elly   -
                    :

                    REVOKE ALL ON ESSEQ FROM BLAKE

                    ᫨  Elly  ਢ DBA,    ⬥-
                      ਢ  SELECT   ᫥⥫쭮,
                    ன  Blake,   짮⥫ Scott ᫥-
                    騬 ஬:

                    REVOKE SELECT ON BLAKE.ESSEQ FROM SCOTT

         易   AUDIT  GRANT.
         ⥬
                                            





























                                   -- 225 --
