


                      ࠢ筨  SQL - ࠬ         5-123


E                            F

E         ROLLBACK                                      F


         祭 ⬥ ࠡ, 믮  ⥪饩 ࠭樨.

         ।뫪 .

         ⠪  ROLLBACK [WORK]
                      [ TO [SAVEPOINT] savepoint ]

         祢   WORK             易⥫ ࠬ, ᯥ-
         ᫮                      稢騩 ANSI - ᮢ⨬.
         ࠬ  SAVEPOINT        易⥫ ࠬ, ᯮ-
                                     騩   筮  ⪠
                                     ࠭樨    ᯥ஢
                                     窥 ࠭.
                    savepoint         窨  ࠭, ᤥ
                                      ⥪饩 ࠭樨.

         砭  ࠭ (  ᪠  ࠡ) - 
          ᯮ- ᫥⥫쭮 SQL  -   ஢,   
             ORACLE ࠪ   楫.  ࠭ -
                    稭  ࢮ 믮  SQL  -  
                    ᫥ COMMIT,  ROLLBACK   ᮥ  
                    . ࠭ 蠥 ஬ COMMIT,
                    ROLLBACK     ᮥ      
                    (।७   砩).  ,   
                    ORACLE   뤠    COMMIT   ᫥
                     DDL - .   ᠭ ࠭-
                    権   ࠡ⪨ ࠭権   
                    11 - "ࠢ ࠭ﬨ"  㬥 "ORACLE
                    RDBMS 㪮⢮   ".

                    ᯮ짮     ROLLBACK      ࠧ
                    SAVEPOINT:

                         * 蠥 ࠭
                         * ⬥   ⥪饩 ࠭樨
                         * 頥  窨 ࠭   ࠭-
                           樨
                         * ᢮ ஢  ࠭樨

                    ᯮ짮    ROLLBACK        ࠧ
                    SAVEPOINT:

                         * 믮   ⪠ ࠭樨 (-
                              窥 ࠭)
                         * ࠭  ଠ 窨 ࠭,  
                           ன  믮 ⪠,    
                           ᫥騥 窨 ࠭. ⥫-
                           ,   ⭮  믮 ⪠
                               ⮩  窥 ࠭.  ।-
                           騥 窨 ࠭   ࠣ-

                                   -- 226 --



                           .
                         * ᢮  ஢ ப  ⠡,
                            뫨  믮 ᫥  窨
                           ࠭.
                           
                  ࠢ筮 㪮⢮   SQL        5-124


                     ࠪ⨪ ४  ணࠬ  짮-
                    ⥫  ࠭樨    -
                    ஢ COMMIT WORK  ROLLBACK WORK.  ᫨   
                       ࠭    ਪ ணࠬ
                     ਩,  ᫥      ᠭ
                    ࠭ 㤥 ⪠祭.

         ਬ      ⪠ ᥩ ࠭樨 :

                    ROLLBACK

                     ⪠  窥 ࠭ SP5 :

                    ROLLBACK TO SAVEPOINT SP5

         易   COMMIT,   SET  TRANSACTION,  SAVEPOINT.
         ⥬        11 -  "ࠢ  ࠭ﬨ"  㬥
                    "ORACLE  RDBMS  㪮⢮    
                    ".
             
                      ࠢ筨  SQL - ࠬ         5-125






























                                   -- 227 --




E                            F

E         ROLLBACK (஥ SQL)                     F


         祭  ⥪ ࠭,   -
                      ᢮  ஢.   RELEASE
                    ᯮ  ६ ᢮  -
                    ᮢ  ᮥ   .

         ।뫪 .

         ⠪  EXEC SQL [AT dbname] ROLLBACK [WORK]
                         [TO  [SAVEPOINT] savepoint]
                         [RELEASE]


         祢   AT dbname        易⥫쭠 ࠧ, ।-
         ᫮                          ,   㤥
         ࠬ                   뤠  ROLLBACK.

                    WORK             易⥫쭠        
                                     ROLLBACK

                    SAVEPOINT        易⥫ ࠬ, ᯮ-
                                     騩   筮  ⪠
                                     ࠭樨    ᯥ஢
                                     窥 ࠭.
                    savepoint         窨  ࠭, ᤥ
                                      ⥪饩 ࠭樨.

                    RELEASE          易⥫ ࠬ,   ᢮-
                                     騩    ᮥ-
                                     騩    .

         砭  ᫥ ࠭  ணࠬ  室 
          ᯮ-  ⢥ত  ⬥ , 
             ᮥ    믮  
                    樨 RELEASE. ᫨  RELEASE 뫠 饭, 
                    ணࠬ 訫,  ஢, 믮 
                    ᫥ ࠭樨    ᢮,  
                    ORACLE  ᯮ,     -
                    .     ਢ     ஢
                    㣨 짮⥫ﬨ  ⪠   祭.

                    ORACLE ⮬᪨ ⪠뢠  ,  ᫨
                    ணࠬ 訫 ਩.

                     ROLLBACK  WORK    뢠 ﭨ 
                    ᮤঠ host - ६  ᫥⥫쭮
                    믮  ணࠬ.

         易   COMMIT, SAVEPOINT  SET TRANSACTIONS.
         ⥬
                    
                  ࠢ筮 㪮⢮   SQL        5-126


                                   -- 228 --




E                            F
                                                         
E         SAVEPOINT                                     F

         祭 । 窨  ࠭樨,   ன  -
                     ᫥⢨ ⪠    
                    ROLLBACK TO SAVEPOINT.

         ।뫪 .

         ⠪  SAVEPOINT savepoint

                      ஥ SQL:

                    EXEC SQL SAVEPOINT savepoint

         祢   savepoint         窨 ࠭,  
         ᫮                       ᮧ.  窨 -
         ࠬ                     -  䨪 SQL 
                                       稭  ⠭
                                     ᮣ襭   ꥪ-
                                     ⮢ (஬ ⢨  室-
                                       ࠧ    㣨
                                     ꥪ⮢  .

         砭  窨 ࠭ ᯮ ᮢ⭮   -
          ᯮ- ஬ ROLLBACK   ⪠  ⥪饩 ࠭-
             樨.
                     㬮砭  ᨬ쭮 ᫮ 祪 ࠭
                     ࠭ ࠢ 5.  ⨢ 窠 ࠭
                    - ⠪,    뫠  ᤥ  ᫥  樨
                    COMMIT  ROLLBACK.   ࠭祭   
                     ⥬      祭   ࠬ
                    SAVEPOINTS 䠩 INIT.ORA.
                    窨 ࠭    ࠪ⨢ ணࠬ-
                    , ⠪    ᮧ   -
                     ⠤ ன 楤.   
                     訥    ࠢ  訬,
                    묨 楤ࠬ. ਬ,   -
                    짮 窨 ࠭  ᫥
                    ,    砥 訡  -
                       ।騥 蠣.
                    窨 ࠭    ⠪     ਪ
                    ணࠬ ᫥饣 ⨯:  ᫨ 楤 -
                    砥   ᥡ ᪮쪮 㭪権,  窨 ࠭
                      ⠭ । 砫 . -
                    , ᫨  㭪  ਩, ଠ
                      㤥   ﭨ, ।-
                    饬 㭪樨  믮    -
                    묨 ࠬࠬ  ।ਭ  ⢨  
                    ⠭.
                     祪 ࠭     ࠭樨
                     ⫨   㣠. ᫨  ᮧ
                      ࠭  䨪஬,  -
                     㦥 ᯮ짮  ।饩 窥 ࠭-
                    ,  ४뢠  .  ᫥  ᮧ
                    ஫쭮 窨    த -

                                   -- 229 --



                    ,   ࠡ  (commit),  ⪠  
                    ࠭  ⪠  窥 ࠭.
                    
                      ࠢ筨  SQL - ࠬ         5-127


         ࠭

                    ࠭ (  ᪠  ࠡ) - 
                    ᫥⥫쭮 SQL  -   ஢,   
                    ORACLE ࠪ   楫.  ࠭ -
                    稭  ࢮ 믮  SQL  -  
                    ᫥ COMMIT,  ROLLBACK   ᮥ  
                    . ࠭ 蠥 ஬ COMMIT,
                    ROLLBACK     ᮥ      
                    (।७   砩).  ,   
                    ORACLE   뤠    COMMIT   ᫥
                     DDL - .   ᠭ ࠭-
                    権   ࠡ⪨ ࠭権   
                    11 - "ࠢ ࠭ﬨ"  㬥 "ORACLE
                    RDBMS 㪮⢮   ".

         ਬ       㦠騬 BLAKE    CLARK,
                    ஢ન,   㬬  㤭 -
                      ॢ蠥 20000 ஢  ୮ -
                      㦠饬 CLARK  :

                    UPDATE EMP
                      SET SAL = 2000
                      WHERE ENAME = 'BLAKE'

                    SAVEPOINT BLAKE_SAL

                    UPDATE EMP
                      SET SAL = 1500
                      WHERE ENAME = 'CLARK'

                    SAVEPOINT CLARK_SAL

                    SELECT SUM(SAL) FROM EMP;

                    ROLLBACK TO SAVEPOINT BLAKE_SAL

                    UPDATE EMP
                      SET SAL = 1300
                      WHERE ENAME = 'CLARK'

                    COMMIT

         易   COMMIT, ROLLBACK  SET TRANSACTIONS.
         ⥬
                    
                  ࠢ筮 㪮⢮   SQL        5-128






                                   -- 230 --




E                            F

E         SELECT                                        F


         祭 뢠 ப  ⮫    ᪮-
                     ⠡.    ᯮ짮   ᠬ-
                    ⥫   ( ।묨 ࠭祭-
                    ﬨ   ࠧ)      㣮
                    .

         ।뫪 室  楬 ⠡     
                     ਢ SELECT   ਢ DBA.

         ⠪  SELECT [ALL|DISTINCT] {* | table.* | expr [c_alias]}
                                       [, {table.* | expr [c_alias]}]...
                    FROM [user.]table [t_alias][,[user.]table [t_alias]]...
                    [WHERE condition]
                    [CONNECT BY condition [START WITH condition]]
                    [GROUP BY expr [, expr]...[HAVING condition]]
                    [{UNION|INTERSECT|MINUS} SELECT ...]
                    [ORDER BY {expr|position} [ASC|DESC]
                                               
                            [,{expr|position} [ASC|DESC]] ]...
                                               
                    [FOR UPDATE OF column [,column]...[NOWAIT] ]


         祢   ALL                㬮砭  砥,
         ᫮                         ப, 㤮⢮騥
         ࠬ                   ᫮ ,  -
                                     .
                    DISTINCT         ᯥ,    㡫騥
                                     ப 㤠       .
                                     ப ᬠਢ 㡫-
                                     饩, ᫨   ᮮ⢥騥
                                     祭  ᯨ᪠ SELECT -
                                        ப.
                    *                ᯥ,   뢠
                                      ⮫ ⠡  ࠧ FROM.
                    table.*          ᯥ,   뢠
                                      ⮫ 㪠 ⠡.
                    expr             (. ᠭ  ᨭ⠪ "expr"
                                       4 -  "樨    㭪-
                                     樨" -  㪮⢠).
                    c_alias          ᯥ稢     ࠦ
                                      ⮫殢, ஥ 㤥 -
                                          뤠. 
                                        ⢨⥫  
                                     ⮫殢.       ⮫殢
                                      뫠 -   
                                     .
                    [user.]table     ᯥ    ⠡  
                                     ,  뤠. 
                                     ⠡  ।⢮ 
                                     ; ᫨       㪠,
                                     । 짮⥫, -

                                   -- 231 --



                                     訩 .
                                     
                      ࠢ筨  SQL - ࠬ         5-129


                    t_alias          ᯥ稢 ⠡  㣮 
                                      믮    -
                                        ᯮ ५-
                                     ஢ . 㣨 뫪
                                      ⠡    
                                      믮 १ .
                    condition        (. ᠭ         ᨭ⠪
                                     "condition"   4 -  "-
                                     樨    㭪樨" -  㪮-
                                     ⢠).
                    position          ⭮⥫ -
                                      ⮫  ᯨ᪥ SELECT.
                    ASC|DESC         㪠뢠 冷     ஢
                                     (ᮮ⢥⢥ - 騩 
                                     뢠騩), 騩  -
                                     ⥫쭮 ᫨ﭨ -
                                      ⥬.
                    column           ⮫,  ਭ騩 ⠡,
                                     㪠  ᯨ᪥ ࠧ FROM.
                    NOWAIT           㪠뢠,  ORACLE  -
                                            ࠢ
                                     짮⥫, ᫨  ⠥
                                     ஢ ப, 㦥 -
                                     ஢ 㣨 짮⥫.

                     ࠧ  SELECT 뢠 -
                    㠫쭮   6 - "ࠧ  SELECT".

         砭  ࠧ   ᯮ짮    ਢ 
          ᯮ- 浪  ᪫祭:
               * CONNECT BY, START WITH, GROUP BY  HAVING -
                         㪠뢠   浪.
                      * ࠧ ORDER BY  FOR UPDATE OF  
                        ⠬.

                     筮  易⥫쭮 ।  ⮫-
                       ⠡   ⠡ -  짮-
                    ⥫,   ⠪ 㪠 뢠 室.
                    筮 ORACLE 믮  ࠡ, ᫨ -
                     ⠡  ⮫殢  ஢  .
                      ஢ ⮫殢 室 ⠪-
                    ,  ᫨    ⠡   ⮫  
                    묨 .

                     ࠦ  expr  ਢ   뤠 
                    ⮫,  table.* -  뤠 ᪮쪨 ⮫殢
                    -     ⮫ ⠡ ( 浪  -
                    ।   ᮧ ⠡).   
                     ࠧ 뤠  ࠦ ।  -
                     ⮢ ࠦ.




                                   -- 232 --



                    窠 (*) 砥 "" ⮫ ⠡. -
                      ࠧ FROM 㪠 ᪮쪮 ⠡,  -
                    窠 砥   "   ⮫      ⠡".
                    窠     ।७   ⠡
                    (table.*),  ࠭稢 뤠 ⮫쪮  ⮫-
                    栬  ⠡.
                    
                  ࠢ筮 㪮⢮   SQL        5-130


                     ,  ᫨ 㪠,  ᯮ  -
                    ⪨ ।饣  ࠦ     
                    ⢥  ⮫  室 . -
                           ⢨⥫   ⮫殢
                    ⠡    ᯮ짮    뫪  
                    ⠢襩  .

                    FROM table  ᯥ  ⠡  ,  
                    ன  롨 .  ᫨  㪠-
                       ⠡,      ᯮ짮 
                    뫪   ⠡  ᥩ ⠢襩 
                     SELECT.

         CONNECT BY

                    ࠧ CONNECT  BY ᯮ   -
                    ᪨ ⭮襭.    㪠뢠,    ப
                     뤠  ᪮ 浪  -
                     ⭮襭,  ᯮ㥬    ।
                    娨 裡 ப  ⠡. ࠧ PRIOR -
                     㪠뢠 । ࢮ  ᫮. PRIOR
                    砥 த⥫   ⭮襭 "த⥫" -
                    "⮬".  㣠    ।⠢  
                    設. ਬ, CONNECT BY PRIOR EMPNO=MGR -
                    砥,    EMPNO    த⥫᪮  設
                    ࠢ    MGR    ୥ (஦)
                    設.  ᫮ ஢ CONNECT  BY  
                    㯭 짮⥫ .

                    CONNECT BY  ᯮ짮 ᮢ⭮ :

                            * ᠬ
                            * ꥤﬨ

         START WITH

                    ࠧ START WITH  ப ( -
                    ),   ᯮ짮  ⢥ -
                     ॢ,   ᫮, ஬  
                    㤮⢮. ⢨   ⮩  ࠧ  砥
                    砫   ⮫殢,  㤮⢮ ᫮
                    ࠧ WHERE.
                     ࠧ START WITH ⨬ .
                    ᫨       SELECT   ᯮ  ࠧ
                    CONNECT BY,      ᯮ짮 ᥢ-
                    ⮫殬 LEVEL,  頥 1  ,
                    2 -  設, ஦ ୥, 3 -  -
                      ..

                                   -- 233 --



                     ᥢ⮫ LEVEL . ⠪   3 - "-
                     몠 SQL",    ࠧ START WITH -  
                    6 -  "ࠧ  SELECT" -  㪮-
                    ⢠.
                    
                      ࠢ筨  SQL - ࠬ         5-131





















































                                   -- 234 --




         GROUP BY, HAVING

                    ࠧ GROUP  BY   HAVING ᯮ  
                    饩 ଠ樨  㯯  ப,  
                     祭      . .
                    ⠪ ࠧ GROUP BY   6   㪮-
                    ⢠.

         ⢥ 樨

                    樨 UNION,  INTERSECT   MINUS  
                     १  ஢ SELECT. UNION -
                    뢠 १     ,  ᪫
                    㡫騥; INTERSECT 뢠 ⮫쪮  -
                    , 饭  ᠬ;  MINUS -
                     ப,  饭  ᮬ  ᫥
                    㤠 ப,   饭    ᮬ.
                    ᫮  ⨯ ⮫殢, 頥  -
                    ⠬,      ,  筮
                    ࠧ୮ 易⥫쭠.

                    ᫨ ⠪ ᯮᮡ    -
                    ᮢ, ᬥ     ⮢  ᬠਢ
                    ᢥ .   浪  ᯮ-
                     ᪮.   樨 ࠧ㬥
                      SELECT ࠧ DISTINCT.

         ORDER BY

                    ࠧ ORDER BY 㪠뢠 冷,  ஬ 
                     ப. . ஡ ࠧ ORDER BY
                      6  㪮⢠.

         FOR UPDATE OF

                    ࠧ FOR  UPDATE    ࠭   ப.
                    筮  ஬ SELECT ... FOR UPDATE ᫥-
                       ᪮쪮 ஢ UPDATE ...WHERE.
                      ஢ ப, 㣨 짮-
                      ᬮ ᤥ  ,     믮
                     COMMIT  ROLLBACK. (㣨 짮⥫
                    㢨 믮     ⮫쪮  ᫥
                    믮   COMMIT).

                    ࠧ FOR  UPDATE 砥,    ॢ
                     ப,  ࠭,   ॡ 易-
                    ⥫쭮 . (ନ "" 砥 
                    ᥡ 樨 UPDATE, DELETE  INSERT).

                    SELECT ...  FOR UPDATE   ᯮ짮  ᮢ-
                    ⭮ :

                        * DISTINCT  GROUP BY
                        * UNION, INTERSECT  MINUS
                        * 㯯묨 㭪ﬨ த COUNT  MAX.



                                   -- 235 --



                    ࠧ NOWAIT ⠢ SELECT ,   
                    , ᫨ ப, 롨ࠥ   SELECT
                    FOR UPDATE, - 㦥 ᯮ.
                    
                  ࠢ筮 㪮⢮   SQL        5-132


         ᫥⥫쭮

                     SELECT  뫠 ⠪  ᥢ-
                    ⮫ ᫥⥫쭮⥩  NEXTVAL  CURRVAL. 
                     ᠭ ᫥⥫쭮⥩   ᯮ-
                            ᠭ  
                    CREATE SEQUENCE.

         

                      ᠭ ᨭ⠪ query    6
                     㪮⢠.


         ਬ    ⮡  ENAME,  SAL,  JOB    DEPTNO  
                    ப ⠡  EMP   ⤥  30  ,
                    :

                    SELECT ENAME, SAL, JOB, DEPTNO
                        FROM EMP
                        WHERE DEPTNO <= 30

         易  ࠧ FROM,  WHERE,  GROUP BY,  HAVING;  ᠭ
         ⥬       ᨭ⠪ simple   join  (⮥  ꥤ),
                    outer join (譥 ꥤ)  query ()
                      6  㪮⢠.


                
                      ࠢ筨  SQL - ࠬ         5-133






















                                   -- 236 --




E                            F

E         SELECT (஥ SQL)                       F


         祭 롨ࠥ ப  㪠 ⠡.  頥-
                     祭  室 host - ६.

         ।뫪 室    楬 ⠡   
                     ਢ SELECT,   ਢ DBA.

         ⠪   EXEC SQL [AT dbname] [FOR :host_integer]
                    SELECT select_list
                      INTO :host_variable [,:host_variable]...
                      FROM table_list
                      [WHERE condition ]
                      [CONNECT BY condition [START WITH condition]]
                      [GROUP BY expr[,expr]...[HAVING condition]]
                      [ {UNION|INTERSECT|MINUS} SELECT ...]
                      [ORDER BY {expr|position} [ASC|DESC]
                              [,{expr|position} [ASC|DESC] ] ]...
                      [FOR UPDATE OF column [,column]...[NOWAIT] ]

         祢   AT dbname        易⥫쭠 ࠧ, 뫠-
         ᫮                          ।騩  
         ࠬ                   CONNECT.        
                                       ।⥫쭮 -
                                      ஬ DECLARE DATABASE.
                    FOR :host_integer ࠭稢 ⢮ ࠥ-
                                      ப.   ஡ . 
                                     ᠭ ࠧ  FOR      6
                                      㪮⢠.
                    select_list      筮 筮     
                                     SELECT  ᪫祭 -
                                      ᯮ짮 host - ६-
                                      ,     ᯮ짮-
                                      ⠭.
                    INTO             ।⠢ ᯨ᮪     室
                                     host - ६.   ६-
                                      ।   -
                                     ६  楤୮  몠  
                                       ࠭ ।-
                                     ,        ।묨
                                     짮⥫   ᯮ짮
                                       SQL  -  .  ᫨  -
                                     -   host - ६ 
                                     ᯨ᪥ INTO    ᨢ,
                                     ᨢ      -
                                     ⠫,  易⥫쭮 -
                                      ࠧ୮.
                    FROM ।  筮 ஥ -
                                      SELECT.  砭:    -
                                     ⢥    ⠡   -
                                     짮 host - ६.
                    WHERE             ᮤঠ 뫪  host -
                                     ६,    ᨢ.
                    condition        ᪮ ࠦ, ᮤঠ饥

                                   -- 237 --



                                     뫪  host -  ६ 
                                     ᨢ. ஡ .  ᨭ⠪-
                                     ᪮ ᠭ  condition   
                                      4 - "樨  㭪樨".
                                     
                  ࠢ筮 㪮⢮   SQL        5-134


                     祢 ᫮  ࠬ  -
                      SELECT.

         砭    ஥  SELECT ᯮ-
          ᯮ-    롮ન  ப,    -
             짮 ᨢ host - ६  ⢥
                    襭    롮ઠ ᪮쪨 ப  
                     믮  FETCH.

                    ᫨  頥     ப,  
                    SELECT 뢠  祭  +100 (+1403  ०
                    ᮢ⨬  ᨥ 5)  sqlcode    
                    祭 host - ६.  । ࠢ-
                      ᫮ NOT FOUND (+100)   ᯮ짮-
                     ஬ WHENEVER.

                     SELECT    ᯮ짮 
                    DECLARE CURSOR  PREPARE     ᫥騬
                    ஬ FETCH.        -
                     ࠧ FOR UPDATE OF:

                    EXEC SQL DECLARE c1 CURSOR
                       FOR SELECT column [,colunmn,...]
                           FROM table|vie [,table|view ] [,...]
                           WHERE search_condition
                           FOR UPDATE OF column [,column] [,...]

                      ⮫殢 ⥬  ᫠   ࠧ
                    CURRENT  OF  UPDATE,   ᫥ 
                    ஬ FETCH.   ⠫ﬨ    
                    ᠭ  DECLARE CURSOR.

         ਬ    騩 ਬ        ᯮ짮
                    ஥  SELECT:

                    EXEC SQL SELECT ENAME, SAL+100, JOB
                              INTO :ENAME, :SAL, :JOB
                              FROM EMP
                              WHERE EMPNO = :EMPNO;

                    騩 ਬ  뢠 ᯮ짮 -
                     SELECT:

                    EXEC SQL INSERT INTO EMP (ENAME,DEPTNO)
                        SELECT * FROM NEWHIRES

         易   PREPARE, DECLARE CURSOR, FETCH  EXECUTE
         ⥬
                    
                      ࠢ筨  SQL - ࠬ         5-135

                                   -- 238 --




E                            F

E         SET TRANSACTION                               F


         祭 ⠭ ⥪ ࠭  READ ONLY.

         ।뫪 .

         ⠪  SET TRANSACTION {READ ONLY}

         祢   READ ONLY         室 ࠬ஬.
         ᫮ 
         ࠬ

         砭   㬮砭  ࠭樨  ⠭  
          ᯮ- ঠ ᮣᮢ  ⥭  ஢ -
             ஢.  SET TRANSACTION READ ONLY 
                     ⠭   ᮣᮢ   ⥭  
                    ஢ ࠭樨.   ⮫쪮 READ ONLY ⠭-
                    ,  ᫥騥   ࠭樨
                      , ᠭ  砫 -
                     ࠭樨.  㭪樮쭮   祭 
                     ⮢, 뤠  ᮢ   
                    ᪮쪨 ⠡栬      ६    ⠫
                    짮⥫   ⠡ ஢.

                     SET TRANSACTION     -
                    ஬  ࠭樨;  ᫨   ⠪, 㤥 -
                     訡.      뤠  
                    ࠭樨 ⮫쪮   .  ᯮ짮  READ
                    ONLY   뢠    ﭨ    㣨
                    짮⥫  ࠭樨.   ᠭ
                    ࠭権   ࠡ⪨    11 -
                    "ࠢ ࠭ﬨ"   -  㬥  "ORACLE
                    RDBMS 㪮⢮   ".

                     ࠭樨 READ ONLY ࠧ襭  ⮫쪮 ,
                    樨 UPDATE, DELETE  INSERT - ⨬.

                    ﭨ ᮣᮢ  ⥭  㤥  த-
                      砭 ࠭樨, 㣨 ᫮ -
                     뤠  ஢  COMMIT   ROLLBACK.  
                    㪠뢠 ,    DDL    뤠
                    COMMIT, ᫥⥫쭮    ⠪  
                    ࠭ READ ONLY.    DDL -  ஢
                    짮⥫  頥      
                    COMMIT,  ⥬   - 稭   ࠭-
                    .     ᢮ ࠭樨,
                           襭  DDL  -
                    ࠬ.

                    ᮢ ⥭    ࠭権 READ ONLY
                    ॠ 筮 ⠪ ,      ᮣᮢ-
                       ஢  ஢.    
                    㬮砭  ᮣᮢ      
                     砫. ࠭樨 ⨯ READ ONLY ᯥ稢-

                                   -- 239 --



                     ᮣᮢ      뤠-
                      SET TRANSACTION.
                    
                  ࠢ筮 㪮⢮   SQL        5-136


                      묨 ⠡ ந ,
                         ந  ᥣ-
                     ⪠  .    -
                      ,      -
                    ,  ORACLE ४ , ⢮訥
                     믮 . । 砫 믮
                     ࠭権 ⨯ READ ONLY,  ࠡ  
                     騬 ⠡栬,   㢥
                    ᫮ ᥣ⮢  ⪠    ঠ   ⠪
                    ࠭権;  ⢨  믮 DBA.

         ਬ     騩     믮  -
                              ᫠
                    ࠡ   ⥩஢,  ਭ .
                          㣨 짮-
                    ,       ६ ଠ  㤠 
                    ⥩.

                    COMMIT
                    SET TRANSACTION READ ONLY
                    SELECT COUNT(*) FROM SHIP
                    SELECT COUNT(*) FROM CONTAINER
                    COMMIT

                    ,  ᫥ COMMIT ॠ쭮  -
                     -    ,  -
                     蠥 READ ONLY - ࠭.

         易   COMMIT,  ROLLBACK  SAVEPOINT;  11
                    -  "ࠢ  ࠭ﬨ" - 㬥 "ORACLE
                    RDBMS 㪮⢮   ".
                    
                      ࠢ筨  SQL - ࠬ         5-137




















                                   -- 240 --




E                            F

E         ࠧ STORAGE                                 F

         祭 ஢ ࠪ⨪   ࠧ-
                     ꥪ⮢  ,  ⠡, -
                      .  SQL -  ᮤঠ
                    ࠧ  । ,  筮 ।塞
                    祢묨  ᫮ STORAGE  DEFAULT STORAGE. 
                    ⨬ ࠬ ⭮ ᫥騥:

                    CREATE TABLESPACE
                    CREATE TABLE
                    CREATE CLUSTER
                    CREATE INDEX
                    CREATE ROLLBACK SEGMENT   ⠪  ᮮ⢥騥
                     ALTER

         ।뫪 .

         ⠪   STORAGE (
                       [ INITIAL integer ]  [ NEXT integer ]
                       [ MINEXTENTS integer ]  [ MAXEXTENTS integer ]
                       [ PCTINCREASE integer ] )

         祢   integer        . ᠭ ᨭ⠪ <integer>.
         ᫮     INITIAL            ࢮ ⥭,
         ࠬ                ।塞  ᮧ ꥪ-
                                    -  室   ꥪ.
                                   㬮砭 - 砫 ࠧ -
                                  ⥭  10240  ⮢.  쭮
                                  祭 4096 ⮢,   ᨬ-
                                    4095 ⮢.   祭
                                  㣫  1024  ⠬  (
                                  ).
                 NEXT             ࠧ    ᫥-
                                   ।塞 ⥭.  -
                                  砭 10240 ⮢,   - 2048
                                  ⮢, ᨬ - 4095 ⮢.
                                   祭 㣫  1 -
                                  .

                                    祭, ஥ 
                                  ⠢ ,    -
                                      ᫥饣 -
                                  ⥭  ᨬ  祭 -
                                  ࠬ PCTINCREASE. ᫨ ᫥
                                  ࠢ 0,   ᫥騩  ⥭
                                  㤥   ࠧ, ࠢ-
                                   NEXT.  ᫨ PCTINCREASE -
                                  ⥫,    ᫥騩 ᥣ
                                  㤥  ⠪ ⢮ 業⮢
                                   ।饣.
         
                  ࠢ筮 㪮⢮   SQL        5-138



                                   -- 241 --





                 MAXEXTENTS       饥 ⢮ ⥭⮢, 
                                  ,     -
                                  .   㬮砭 99 ⥭⮢.
                 MINEXTENTS       饥 ⢮ ⥭⮢, ஥
                                    ।  ᮧ-
                                   ᥣ.   -
                                    ࢮ砫쭮 襥 -
                                  ⢮         ᫨   
                                  뢭  ⪠ ᮮ⢥-
                                  饣 ࠧ.  砭 1,  -
                                  砥 ࢮ砫쭮 ।
                                  ⮫쪮  ⥭.
                                  ᫨ MINEXTENTS  1,   
                                  믮  CREATE -
                                    㪠 ⢮ -
                                  ⥭⮢,  ᭮뢠    祭
                                  ࠬ஢ INITIAL,NEXT,PCTINCREASE.
                 PCTINCREASE      業,    㤥  -
                                     ।塞 ⥭
                                   ࠢ  ।騬. ᫨ -
                                  ࠬ  PCTINCREASE ࠢ , 
                                  ⥭   ࠧ.
                                    㬮砭 PCTINCREASE ࠢ 50.
                                   祭  㢥稢-
                                    ᫥饬 ⥭, 
                                   筮  ਢ   㬥-
                                  襭  饣  ᫠  ⥭⮢  ,
                                  ᫥⥫쭮, ᭨ /뢮-
                                  .   ࠧ,   
                                  NEXT,        㤥        
                                  PCTINCREASE.   㬭 ࠧ
                                  ।饣      PCTINCREASE
                                  १ 㣫  襣
                                  ᫠,  ⭮   ࠧ   .
                                  PCTINCREASE      -
                                  ⥫.

         砭  ᬮ७ ࠬ       ᪮
          ᯮ- 㯠     ,  ⠪   䥪⨢
             ᯮ짮  .  뢠饥  㦤
                    ࠧ  ਠ⮢  ࠬ஢ ਢ 
                     4 - "ன  /뢮"  -  㬥
                    "ORACLE RDBMS 㪮⢮  ன ந-
                    ⥫쭮".
                     ࠬ STORAGE ᯮ  ।
                    ALTER,    祭    -
                    騥 ⥭.

         ਬ     STORAGE ( INITIAL 100K NEXT 50K
                           MINEXTENTS 1 MAXEXTENTS 50
                           PCTINCREASE 5 )

         易   CREATE   TABLE,  CREATE  INDEX,  CREATE
                    CLUSTER, CREATE ROLLBACK SEGMENT.
         
                      ࠢ筨  SQL - ࠬ         5-139

                                   -- 242 --




E                            F

E         ࠭祭  ⠡                       F

                    .   ⮩  "ࠧ CONSTRAINT"


E                            F

E         UPDATE                                        F

         祭    㪠 ⠡.

         ।뫪    楬 ⠡    
                     ਢ UPDATE,   ਢ DBA.

         ⠪  UPDATE [user.]table [alias]
                       SET column = expr [,column = expr]...
                       [WHERE condition]

                    

                    UPDATE [user.]table [alias]
                       SET (column [,column]...) = (query)
                         [,(column [,column]...) = (query) ] ...
                       [WHERE condition]


         祢   user               ⠡.
         ᫮     table             饩 ⠡,   -
         ࠬ                   ன    .
                    alias            ᯮ   २
                                     ⠡  뫮  ᫥
                                     ࠧ .
                    column           ⮫,  ਭ騩 ⠡.
                                     ᫨   ⢥,   㣫
                                     ᪮  易⥫.
                    expr             (. ᠭ  ᨭ⠪ "expr"
                                         4  - "樨  㭪-
                                     樨" -  㪮⢠).
                    query             SELECT,    ᮤঠ騩
                                     ࠧ ORDER  BY    FOR   UPDATE,
                                     筮 ५஢   -
                                     饩 䨪樨 ⠡楩.
                    condition        ࠢ쭮 ᫮, 饥 -
                                     祭 TRUE  FALSE.  (. -
                                     ᠭ  ᨭ⠪  "condition" 
                                      4 - "樨  㭪樨" -
                                      㪮⢠).
                                     
                  ࠢ筮 㪮⢮   SQL        5-140







                                   -- 243 --




         砭  ࠧ SET । ⮫,  騥 -
          ᯮ-   ᨬ   祭.
             ࠧ WHERE । ப,  祭 -
                      ;  ᫨  ,  
                      ப ⠡.     ப,
                    㤮⢮饩  ᫮  WHERE  ⮫ ᫥ 
                     ࠢ⢠  祭 ࠦ -
                      ࠢ⢠. ࠦ  ⮫쪮 
                    ப  䨪樨.

          (query)

                    ᫨ ࠧ SET ᮤন ,  ᫥ 
                       筮  ப   -
                    塞 ப.   祭    १
                     砥   ᮮ⢥⢥  ⮫  
                    祭   㣫  ᪮.  ᫨    
                    頥 ப,     ᢠ     (..    SET
                    value=query) 祭 㤥 NULL.   -
                     롨 ப  ⠡,  .

                     ࠧ SET  ᬥ訢 祭, 頥-
                     ᠬ  砥 ࠦﬨ.

                     UPDATE ⮬᪨ 뤠 ᪫⥫-
                      ஢  ப  ⠡,  
                    .  ஢ ᢮ ᫥ -
                      COMMIT  ROLLBACK.

         ५஢ 

                    ᫨  롨ࠥ ப  ⠡ 饩
                    䨪樨,  ORACLE 㤥 믮    
                      ப  (      ࠧ   
                    UPDATE).  ⨯ 䨪樨 뢠 "-
                    ஢묨 ﬨ". 뫪  ⮫ -
                    塞 ⠡ 筮 믮   -
                     ⠡.
                    ⥭樠쭮  ப,    -
                     UPDATE,     㣮 -
                      ᮮ⢥⢨  ५஢ ᮬ.
                    ଠ쭮   UPDATE   ப 
                      ᠬ 祭.

         ਬ     ⠭ 祭 ᨮ null  -
                    㤭  ᨥ TRAINEE (⠦), :

                    UPDATE EMP
                       SET COMM = NULL
                       WHERE JOB = 'TRAINEE'

                     ॢ  㦠饣  JONES ஬  ⤥
                    20  襭     1000  ஢  (
                    ।,   㢥७,   ⠪ 䠬
                    - ⢥), :



                                   -- 244 --



                    UPDATE EMP
                       SET JOB = 'MANAGER', SAL=SAL+1000, DEPTNO=20
                       WHERE ENAME = 'JONES'
                    
                      ࠢ筨  SQL - ࠬ         5-141


                    ਢ   UPDATE 뢠:

                     *     ᬥ訢  ⢥
                       
                     * ᯮ짮 ५஢ 
                     * ᯮ짮  ࠧ  WHERE     ࠭祭
                       ப,  䨪樨.

                    UPDATE EMP A
                      SET DEPTNO =
                          (SELECT DEPTNO
                             FROM DEPT
                             WHERE LOC = 'BOSTON'),
                          (SAL,COMM) =
                          (SELECT 1.1*AVG(SAL), 1.5*AVG(COMM)
                             FROM EMP B
                             WHERE A.DEPTNO = B.DEPTNO)
                      WHERE DEPTNO IN
                          (SELECT DEPTNO
                             FROM DEPT
                             WHERE LOC = 'DALLAS'
                                OR LOC = 'DETROIT')

                    ।騩  UPDATE:

                      *  ⮫쪮 㦠, ࠡ  -
                          ன (ࠧ WHERE)
                      * ⠭    ⤥      
                        ⮭ ()
                      * ⠭   ᨮ ࠢ
                        ᮮ⢥⢥ 1.1  1.5  । 祭
                          ⥪饬 ⤥ (५஢  -
                        ).

         易   DELETE  INSERT; ࠧ WHERE.
         ⥬
                        
                  ࠢ筮 㪮⢮   SQL        5-142














                                   -- 245 --




E                            F

E         UPDATE (஥ SQL)                       F

         祭    㪠 ⠡.

         ।뫪    楬 ⠡    
                     ਢ UPDATE,   ਢ DBA.

         ⠪  EXEC SQL [AT dbname] [FOR :host_integer
                       UPDATE [user.]table
                    SET column [,column]...=({value[,value]...|query})
                       [WHERE {condition|CURRENT OF cursor_name}]

                    

                    EXEC SQL [AT dbname] [FOR :host_integer
                       UPDATE [user.]table
                          SET column1 ={(value)|query}
                        [,SET column1 ={(value)|query} ]...
                      WHERE {search_condition|CURRENT OF cursor_name}]


         祢   AT dbname        ᯥ  㬠稢
         ᫮                       .
         ࠬ  FOR :host_integer ࠭稢 ᫮ ६
                                     ࠡ뢠 ⮢ ᨢ
                                        UPDATE.
                    table             塞 ⠡.
                    column                
                                      ⮫殢,       
                                       ᯥ祭 祭.
                                      ⮫   ਭ-
                                      ⠡, 㪠  ࠧ
                                     INTO.
                    value            ।  ࠫ,  -
                                       :host_variable9:indicator]
                    query            ଠ  SELECT  ࠧ
                                     INTO.
                    WHERE clause     ᫮  ᪠    
                                     䨪樨 ப. ᫨  -
                                      饭,   -
                                      ⠡  .
                    condition        ᪮ ࠦ, ᮤঠ饥
                                     뫪   host - ६ 
                                     ᨢ.  ᫮  ᪠  
                                     ᮤঠ  ᥡ . ஡-
                                      .   ᨭ⠪᪮ -
                                       condition   4 - "-
                                     樨  㭪樨".
                                     
                      ࠢ筨  SQL - ࠬ         5-143






                                   -- 246 --




                    CURRENT OF       뫠    ᫥ ப,
                                     ࠡ⠭     FETCH,
                                     易   㪠 ஬.
                                      ᫥騥 ࠭祭:
                                     * ᫨  ࠧ CURRENT OF ᠭ
                                       ᮢ⭮    UPDATE,   
                                          ᯮ짮-
                                        SELECT ...  FOR UPDATE.
                                       . ⠪  ࠧ "DECLARE
                                       CURSOR".
                                     *        
                                       樮஢  ப.
                                     * ᫨   믮﫮 FETCH 
                                            ,  ࠧ
                                       CURRENT OF  頥 ப.

         砭  ᫨ host - ६  ᨢ, -
          ᯮ-  UPDATE 믮  ࠧ   -
              .   믮  UPDATE
                      ,    ப.
                    ᫨ -  host- ६, 
                     ࠧ WHERE - ᨢ,  ᨢ    
                     host- ६   ࠧ WHERE, ⠪  
                    ࠧ SET.   ᨢ   ࠧ筮 ࠧ-
                    ୮,   ᫮ ࠡ뢠 ࠧ ப -
                    । 쭮 ࠧ୮  祭
                     ࠧ FOR (᫨   쭮 ࠧ-
                    ).

                    ᫨ ࠧ  WHERE  饭,  ⠢  䫠
                    ।०  5  SQLCA (sqlwarn).

                    ᫨    ப  㤮⢮ ᫮ -
                    ᪠ (search_condition),  SQLCODE 㤥 ᠭ
                    ᫮ NOT FOUND.

                    饥 ᫮  ஢ ப ⠪ -
                    頥  SQLCA.    ⢥ 室 host  -
                    ६ ᯮ ᨢ, 稪 ࠦ
                    饥 ⢮        
                    ᨢ, ࠡ⠭ ஬ UPDATE.
                    
                  ࠢ筮 㪮⢮   SQL        5-144















                                   -- 247 --




         ਬ    騥 ਬ       ᯮ짮
                    ஥ SQL -  UPDATE:

                    EXEC SQL UPDATE EMP
                       SET SAL = :sal, COMM = :comm
                       WHERE ENAME = :ename;

                    EXEC SQL UPDATE EMP
                       SET (SAL,COMM) =(:sal, :comm)
                       WHERE ENAME = :ename;

                    EXEC SQL UPDATE EMP
                       SET (SAL,COMM) =
                           (SELECT SAL*1.1, COMM*1.1
                              FROM EMP)
                              WHERE ENAME = 'JONES';

                    EXEC SQL UPDATE DEPT
                       SET EMPCNT =
                           (SELECT COUNT(*)
                              FROM EMP
                              WHERE EMP.DEPTNO = DEPT.DEPTNO);

         易   PREPARE  EXECUTE IMMEDIATE; ࠧ FOR.
         ⥬
                    
                      ࠢ筨  SQL - ࠬ         5-145































                                   -- 248 --




E                            F

E         VALIDATE INDEX                                F


         祭 䨪 楫⭮ 㪠 .

         ।뫪 室  楬 ,    
                    ਢ DBA.

         ⠪  VALIDATE INDEX [user.]index

         祢   user              .
         ᫮ 
         ࠬ  index             ஢塞 .


         砭    ६  ⮬  ஢,   -
          ᯮ-  祭  㪠뢠  ࠢ 
             ,   ஢ ⮣,     ப
                    ⠡   ᮮ⢥騩    .
                    ORACLE ⢥न⥫쭮 ᮮ,  ᫨    -
                    ; ᫨   ⠪, 室 ⬥ -
                      ᮧ  .

         ਬ      ஢ન    ROSEBUD :

                    VALIDATE INDEX ROSEBUD

         易   CREATE INDEX.
         ⥬
                    
                  ࠢ筮 㪮⢮   SQL        5-146

























                                   -- 249 --




E                            F

E         WHENEVER (஥ SQL)                     F


         祭 ,  ࠧ ࠡ뢠 訡-
                      ।०.    饬  砥,  WHENEVER
                         ணࠬ 믮 -
                    ஬.  ࠭,    訥 訡
                     ࠡ⠭.

         ।뫪 .

         ⠪  EXEC SQL WHENEVER {NOT FOUND|SQLERROR|SQLWARNING}
                       {CONTINUE | GOTO label_name | STOP}


         祢   NOT FOUND           ᮡ -
         ᫮                      ,      +100
         ࠬ                   ( +1403  ० ᮢ⨬
                                      ᨥ 5).
                    SQLERROR          , -
                                      ⥫  -
                                     .
                    SQLWARNING          ।०,
                                     饥  ⠫ 訡.
                    CONTINUE         㪠뢠,   ணࠬ 
                                     믮 ᫥騩 .
                    GOTO label_name  㪠뢠,   ணࠬ 
                                     ३  㪠  label
                                     _name.
                    STOP              믮 ⥫ -
                                     ⢨.


         砭   WHENEVER 㤥 ࠨ   IF
          ᯮ-  ஢ન 㪠 ᫮ ᫥  -
               EXEC SQL.
                        WHENEVER  
                    ᪫⥫쭮     樨  ணࠬ 
                      ᠥ ࠢ    ᮮ⢥饣
                    몠 ணࠬ஢.   ⪠, ᯥ-
                    ஢   WHENEVER,   
                     ணࠬ.

                     WHENEVER ⠥  ᨫ,    㤥
                    ४ 㣨 ஬ WHENEVER.

                    ࠡ⪠ 訡   ᠬ    WHENEVER  
                    楫   横 믮 ᫥-
                    騬 ࠧ: WHENEVER SQLERROR CONTINUE.

                    室 ࠧ  ஥  SQL  -  
                    WHENEVER   WHENEVER 몠 SQL*Plus.

                     WHENEVER  ᭮ ᯮ  ᯥ-
                    ஢ ⢨, 믮塞 :

                                   -- 250 --



                       * 롮થ ᫥ ப  
                       * ᫨ INSERT INTO...SELECT...   ⠢ 
                          ப

         ਬ    騩 ਬ  ᯮ짮  -
                     WHENEVER:

                    EXEC SQL WHENEVER SQLERROR GOTO some_problems;
                    ....
                    some_problems:
                        EXEC SQL WHENEVER SQLERROR CONTINUE;
                        EXEC SQL ROLLBACK RELEASE;

         易   FETCH  EXECUTE.
         ⥬
                    











































                                   -- 251 --
