


                  ࠢ筮 㪮⢮   SQL        5-10


                        ARCHIVELOG      ᮮ⢥⢥ ࠧ蠥    
                        NOARCHIVELOG    頥 娢஢.

                        MOUNT           ।,     
                        DISMOUNT          :  -
                                           ࠧ஢. 
                                        設⢠     ⢨
                                             -
                                        ஢  . 
                                        樨  ᮯ஢ -
                                          ॡ,  ⮡   뫠
                                        ஢,     -
                                        ⮩.
                                        ⠫쭮 ࠧ MOUNT 뢠-
                                               "ORACLE     RDBMS
                                        㪮⢥    
                                         ".
                        OPEN            㪠뢠,      
                        CLOSE               -
                                           ଠ쭮   -
                                        饭     -
                                        㯭.
                                            ᠭ   ࠧ
                                        OPEN     㬥
                                        "ORACLE RDBMS 㪮⢮ -
                                          ".

         砭    ਥ 権   ⥪饣 -
         ᯮ짮  ﭨ  :

                ᫨  :              ᯮ짮:

                ஢                 ⮫쪮 MOUNT
               ஢,        , ஬ MOUNT  CLOSE
                                       ⮫쪮 CLOSE

         SIZE  REUSE   ᫨ ᯥ஢ SIZE,  REUSE - 饭,
                        㤥  ᮧ  䠩  㪠  ࠧ
                        (     ᪥).  ᫨  㪠
                         ࠬ,   砥 ⢨ 䠩 -
                         ᮧ,   稨 - ஢ 
                        ࠧ. ᫨ 㪠 ⮫쪮 REUSE   㪠-
                         祣, 䠩  ⢮  -
                         ⠪  ࠧ.

                         㬥   ॡ  ⥫
                        ⢨  ஭ DBA, ਬ - ।-
                          ⮢ 䠩  樨 REUSE 
                        믮 backup,  ᫨  ᯮ  ०
                        ARCHIVELOG.
                         饬 砥 ४ । 
                            ࠧ஢.  -
                        ⥫쭮   ଠ樥        ALTER
                        DATABASE   "ORACLE RDBMS 㪮-


                                    -- 91 --



                           ".
                        
                      ࠢ筨  SQL - ࠬ         5-11


         ਬ        ⮡ ஢   ᪫⥫쭮 ०:

                        ALTER DATABASE dbname MOUNT EXCLUSIVE

                        ⮡  㦥 ஢  :

                        ALTER DATABASE OPEN

                         ४祭  ० 娢஢ 䠩
                        ୠ:

                        ALTER DATABASE ARCHIVELOG

                   䠩 ୠ:

                        ALTER DATABASE
                            ADD LOGFILE dblog3.log SIZE 50000


         易       CREATE  DATABASE;    SQL*DBA:
         ⥬           STARTUP  SHUTDOWN ( ਫ    "ORACLE
                        RDBMS 㪮⢮   ")

                        
                  ࠢ筮 㪮⢮   SQL        5-12





























                                    -- 92 --




E                                   F

E         ALTER INDEX                                         F


         祭       ࠪ⨪ ।
                           .

         ।뫪         楬 ,  
                         ਢ DBA.

         ⠪      ALTER INDEX [user.]index
                            [INITRANS integer] [MAXTRANS integer]
                            [STORAGE storage]

         祢 ᫮ user             짮⥫  
          ࠬ
                        index            

                        INITRANS integer (    ࠬ
                                         INITRANS  ᠭ 
                                          CREATE TABLE).
                        MAXTRANS integer (    ࠬ
                                         MAXTRANS  ᠭ 
                                          CREATE TABLE).
                        STORAGE storage   (.    ᠭ  ࠧ
                                          STORAGE).

         砭    ࠬ INITRANS, MAXTRANS  STORAGE 
         ᯮ짮    祭,     CREATE TABLE

         ਬ          ⮣,  ⮡       CUSTOMER
                        짮⥫ SCOTT ⠪ ࠧ,  騥
                         ORACLE  ⮬  ᯮ짮  -
                        砫쭮 5  ⮢ ࠭権  ⥫-
                         ⥭  ࠧ஬ 100 ⮢, -
                        :

                        ALTER INDEX SCOTT.CUSTOMER
                            INITRANS 5
                            STORAGE (NEXT 100K)

         易       CREATE INDEX  CREATE TABLE; ࠧ
         ⥬           STORAGE.
               
                      ࠢ筨  SQL - ࠬ         5-13












                                    -- 93 --




E                                 F

E         ALTER ROLLBACK SEGMENT                             F


         祭      ࠬ  饣  ᥣ-
                         ⪠.

         ।뫪    ॡ ਢ DBA.

         ⠪      ALTER [PUBLIC] ROLLBACK SEGMENT rollback_segment
                            STORAGE storage

         祢 ᫮
          ࠬ
                        PUBLIC            騩 ᥣ-
                                         ⪠ - 騩.

                        rollback_segment   -
                                        饣 ᥣ ⪠.

                        STORAGE storage. . ᠭ ᨭ⠪ -
                                          STORAGE   ⮩ .

         砭        ALTER  ROLLBACK  SEGMENT ࠧ
         ᯮ짮  STORAGE     饥 ।
                          㪠 ᥣ ⪠.  -
                        ⥫쭮 㬥 INITIAL  MINEXTENTS  -
                          饣 ᥣ ⪠ ਬ-
                         .

                           ,     ALTER ROLLBACK
                        SEGMENT   ᬥ  ᥣ -
                          PUBLIC  private  .  -
                         ⠪  室 ⬥
                        ᥣ  ᮧ   㦭 ࠪ-
                        ⨪.

         ਬ        ⮡  ࠬ  ᥣ RSONE:

                        ALTER ROLLBACK SEGMENT RSONE
                        STORAGE (NEXT 1000 MAXEXTENTS 20
                                 PCTINCREASE 0)

         易       CREATE DATABASE, CREATE ROLLBACK
         ⥬           SEGMENT, CREATE TABLESPACE; ࠧ STORAGE.
                 
                  ࠢ筮 㪮⢮   SQL        5-14










                                    -- 94 --




E                              F

E         ALTER SEQUENCE                                F


         祭      樨 ᫥⥫쭮    
                        ᫥ ࠢ:

                        *     饥  蠣   ᥤ
                          ᫥⥫묨 ࠬ.
                        * ⠭ MINVALUE  MAXVALUE  몫-
                               ࠬ.
                        * /몫 ⢨ ࠬ CACHE
                        * /몫 ⢨ ࠬ ORDER

         ।뫪       楬 ᫥⥫쭮,
                         ।⠢      ਢ 
                         ਢ DBA.

         ⠪      ALTER SEQUENCE [user.]sequence
                            [INCREMRNT BY integer]
                            [MAXVALUE integer | NOMAXVALUE]
                            [MINVALUE integer | NOMINVALUE]
                            [CYCLE | NOCYCLE]
                            [CACHE integer | NOCACHE]
                            [ORDER | NOORDER]

         祢 ᫮ user             짮⥫  
          ࠬ                    ᫥⥫쭮.
                        sequence         饩 ᫥⥫-
                                        .
                        INCREMENT BY      ६  
                        integer         饩 ᫥⥫쭮.
                        MAXVALUE |           -
                        NOMAXVALUE       ᫥⥫  -
                                             ࠭-
                                        祭.
                        MINVALUE |            -
                        NOMINVALUE       ᫥⥫  -
                                             ࠭-
                                        祭.
                        CYCLE |         ।   ४祭  
                        NOCYCLE          MINVALUE,    -
                                        饩 ᫥⥫쭮
                                        ⨣ MAXVALUE;    -
                                        ४祭    MAXVALUE  
                                        뢠饩 ᫥⥫쭮.
                        CACHE integer | ।,   ࠭-
                        NOCHACE            騥  ᫥-
                                        ⥫  ( ᪮-
                                          㯠). ।
                                        20. CACHE    
                                        ࠧ    MAXVALUE    
                                        MINVALUE.
                        ORDER |         C, 㤥  ORACLE
                        NOORDER          -  ஢ ᫥-
                                        ⥫    浪

                                    -- 95 --



                                        . 筮,  ⠪ -
                                         ந   㪠-
                                         ORDER.
                   
                      ࠢ筨  SQL - ࠬ         5-15


         砭      ⮣,  ⮡  ᫥⥫쭮 
         ᯮ짮  㣮 ,    ⬥    ᮧ
                        .  ALTER SEQUENCE  ⮫쪮
                         騥 ᫥⥫ .

                        믮   ஢ન  ࠢ쭮
                         . ਬ -  -
                          MAXVALUE,  ᫨   ⥪-
                        饣 ᫥⥫쭮 .

         ਬ         ⠭  ᨬ쭮 祭 -
                        ᫥⥫쭮 ESEQ, :

                        ALTER SEQUENCE ESEQ MAXVALUE 1500

                         祭 CYCLE  CACHE :

                        ALTER SEQUENCE ESEQ CYCLE CACHE 5

         易       CREATE SEQUENCE  DROP SEQUENCE.
         ⥬
                        
                  ࠢ筮 㪮⢮   SQL        5-16





























                                    -- 96 --




E                              F

E         ALTER SESSION                                 F


         祭     砥    ⪫砥     ⥪饬  ᥠ
                        ।⢮ ஢ SQL.

         ।뫪    .

         ⠪      ALTER SESSION
                            SET SQL_TRACE {TRUE | FALSE}

         祢 ᫮ TRUE       砥 ।⢮ ஢ SQL
          ࠬ    FALSE      ⪫砥 ।⢮ ஢ SQL

         砭    ।⢮ ஢ SQL  -
         ᯮ짮   ந⥫쭮  ࠡ⪨ SQL - -
                        ஢.  祭 ।⢠ ஢ SQL
                        - ஢  襬 ᥠ 믮 
                        ALTER SESSION   祢  ᫮  TRUE.  
                        몫祭 ।⢠ ஢ 믮 -
                         ALTER SESSION  祢 ᫮ FALSE.

                         ⠪ ⨢஢  ।⢮  -
                        ஢ SQL   ᥠᮢ  
                        ORACLE, ⠭  TRUE ࠬ 䠩 INIT.
                        ORA: SQL_TRACE.   ⮬ 砥   -
                         ஢ ⤥쭮 ᥠ, 뤠 
                           ALTER  SESSION  祢 ᫮
                        FALSE.

                         뤠  ALTER SESSION   ਣ-
                         SQL*Forms.   ଠ樨  ⨢樨 
                        ⨢樨 ।⢠ ஢ SQL  -
                             23 - ""   26 -
                        "樨" 㬥 "ࠢ筨 ࠧࠡ稪 
                        SQL*Forms".

                         ஡ ଠ  ।⢥ -
                        ஢ SQL ( ଠ஢  -
                         १⮢)    7 - "।-
                        ⢠ ⨪ ந⥫쭮" 㬥
                        "ORACLE RDBMS 㪮⢮  ன ந-
                        ⥫쭮".

         ਬ         ⨢樨 ।⢠ ஢ SQL   -
                        襬 ᥠ 믮 :

                        ALTER SESSION SET SQL_TRACE TRUE

                         ⨢樨 ।⢠, 믮 ᫥
                        :

                        ALTER SESSION SET SQL_TRACE FALSE



                                    -- 97 --



         易       7 -  "।⢠  ⨪  ந-
         ⥬           ⥫쭮"  㬥 "ORACLE RDBMS 㪮-
                        ⢮  ன ந⥫쭮".

                      ࠢ筨  SQL - ࠬ         5-17


E                                F

E         ALTER TABLE                                     F


         祭      । ⠡:

                        *  ⮫  ࠭祭
                        *  ।  ⮫-
                          殢 (ਬ - ⨯   NOT NULL).
                        * ⬥ ࠭祭
                        *  饥 । 
                        * ⬥,     ⠡  믮-
                           backup

         ।뫪       楬  ⠡,  
                           ਢ ALTER   ਢ
                        DBA.

         ⠪      ALTER TABLE [user.]table
                         [ADD ({column_element|table_constraint}
                              [,{column_element|table_constraint}]...)]
                         [MODIFY (column_element [,column_element]...)]
                         [DROP CONSTRAINT constraint]...
                         [PCTFREE integer] [PCTUSED integer]
                         [INITRANS integer] [MAXTRANS integer]
                         [STORAGE storage]
                         [BACKUP]

         祢 ᫮ [user.]table     饣 짮-
          ࠬ                       ⠡,    
                                         .  㬮砭
                                         ⢥ user ।-
                                         짮⥫, 뤠訩 -
                                         ALTER TABLE.   -
                                         ࠪ⨪   ᢮
                                        ⠡,    
                                          ਢ ALTER, -
                                          ਢ DBA.
                        ADD/MODIFY           
                        column_element  । ⮫,  ࠭-
                                        祭 ⮫   㬮-
                                        . ⠪ column_element
                                        ਢ   ⮩ .
                        ADD              ࠭祭     
                       table_constraint ⠡. .   ⮩ -
                                         ᠭ "Constraint"
                        DROP constraint ⬥ 㪠  ࠭-
                                          ⮫  ⠡.
                        PCTFREE integer  ( ᠭ  -
                        PCTUSED integer  ࠬ஢   

                                    -- 98 --



                        INITRANS integer CREATE TABLE)
                        MAXTRANS integer
                        STORAGE storage (.  ࠧ Storage).
                        BACKUP            ᫮ ,
                                        ⬥  ⠡ 뫠 ᪮-
                                        ஢   뤠 -
                                         ALTER TABLE.
                       
                  ࠢ筮 㪮⢮   SQL        5-18


         砭    ᫨    ⠡  ⮫    -
         ᯮ짮   ࠧ  ADD,   砫쭮 祭 
                         ப - NULL.   ⮫  NOT
                        NULL   ⮫쪮      ( ᮤঠ
                        ப) ⠡.
                        ࠧ MODIFY  ᯮ   ᫥-
                         ਡ⮢ ⮫:

                            * ࠧ஢
                            * ⨯ 
                            * NOT NULL

                         ⨯   㬥 ࠧ ⮫-
                               ᮤঠ  NULL  
                        ப.  ⨯  NUT NULL  ⮫-
                         ᫨  ப ᮤঠ 祭  null.
                        ᫨   ࠧ ⮫,  襣 -
                        ਡ NOT  NULL     㪠뢠 "NOT NULL",
                        ⮫  ࠢ ࠭  ਡ.

                        砭. ,  ।  ᮬ  
                        ᥬ ⮫栬 ⠡ (SELECT * FROM ...), 
                        㤥  ࠢ쭮 ࠡ,  ᫨  -
                          ᮮ⢥饩 ⠡  ⮫-
                        .     㤠    
                        ᮧ .

         ਬ        ⮡   ⠡ EMP ᫮ ⮫
                        THRIFTPLAN   ᨬ  ࠧ஬    ᥬ
                            ﬨ ᫥ 窨,   ⠪
                        ⮢ ⮫ LOANCODE, :

                        ALTER TABLE EMP
                          ADD (THRIFTPLAN NUMBER(7,2), LOANCODE CHAR(1))

                         㢥祭 ࠧ THRIFTPLAN  9  
                        ⬥ ࠭祭 ENAME_CNSTR :

                        ALTER TABLE EMP
                          MODIFY (THRIFTPLAN NUMBER(9,2))
                          DROP CONSTRAINT ENAME_CNSTR

         易       CREATE TABLE; ࠧ Constraint.
         ⥬
                        
                      ࠢ筨  SQL - ࠬ         5-19


                                    -- 99 --



E                              F

         ALTER TABLESPACE
E                                                       F

    祭    饥 ⠡筮 ࠭⢮ ⥬:

                 *   २ 䠩()  
                 *  㬠稢 ࠬ஢   ⠡
                 * ॢ   online  offline
                 *   ⠭ backup.

    ।뫪  ॡ ਢ DBA

    ⠪    ALTER TABLESPACE tablespace
                 {ADD DATAFILE filespec [,filespec [REUSE] ] ...
                 |RENAME DATAFILE 'filename' [,'filename'] ...
                      TO 'filename' [,'filename']...
                 |DEFAULT STORAGE storage]
                 |ONLINE
                 | OFFLINE [ NORMAL | IMMEDIATE ]
                 | {BEGIN BACKUP | END BACKUP}}

         祢 ᫮
          ࠬ
                 tablespace        饣 ⠡筮 -
                                  ࠭⢠

                 ADD DATAFILE      㯭 ⠡筮  -
                 filespec         ࠭ ⥫ .  -
                                   䠩  ⠡筮 ࠭-
                                          室 
                                  ﭨ   online      offline.
                                  Filespec ᯥ䨪   
                                  ଥ:
                            'filename' [SIZE integer [K|M]][REUSE]
                                  integer 㪠뢠  SIZE    .
                                  ᫨ integer 饭, ।-
                                   ࠧ 䠩 ୠ  500. -
                                     "",  integer 㬭-
                                      1024,  ᫨  ""   -   
                                  1048576.  ᫨ SIZE 㪠,  REUSE
                                  - 饭,  㤥  ᮧ  䠩
                                  ᯥ஢ ࠧ ( -
                                      ⢮). ᫨
                                  㪠 ࠬ,  䠩 ᮧ,
                                  ᫨  ,   ᫨  - ஢-
                                    ࠧ.  ᫨ REUSE -
                                  饭   㪠   㬥,
                                  䠩    ⢮   -
                                   ⠪  ࠧ.

                 RENAME           २뢠    ᪮쪮
                DATAFILE filename 䠩,   易      ⠡
                TO filename       ࠭⢮.     樮
                                  ⥬    २뢠.  
                                  ६   ⮩   樨   ⠡筮
                                  ࠭⢮    室  

                                   -- 100 --



                                  offline.
                 DEFAULT         ᯥ  㬠稢 -
                 STORAGE storage ࠬ    ꥪ⮢,
                                 ᮧ  ⠡筮 ࠭⢥
                                 .   ᠭ   ᨭ⠪    ࠧ
                                 STORAGE
    
                  ࠢ筮 㪮⢮   SQL        5-20


                 ONLINE          㪠뢠,   ⠡筮 ࠭-
                                 ⢮   饭  -
                                  online.
                 OFFLINE         㪠뢠,   ⠡筮 ࠭-
                                 ⢮   뢥    offline
                                     (IMMEDIATE),  
                                 ᫥ ⮣,  ⥪騥 짮⥫
                                              
                                 (NORMAL).
                 BEGIN BACKUP    ᯥ,   䠩 , -
                                 ⠢騥    ࠭⢮,  
                                 ᪮஢   ⥬   楤ன
                                 backup.       
                                     ;  짮⥫
                                   த    .
                                    ࠡ ॡ
                                 ⠪ ࠢ騩 䠩  䠩 ୠ-
                                 .
                 END BACKUP      㪠뢠    襭  楤
                                 ⥬ ஢.

    砭   २  䠩()  室  믮
    ᯮ짮-  ᫥騥 蠣:
    
                 1. ॢ ⠡筮 ࠭⢮  offline.
                 2. २ 䠩  ஢ 樮 ⥬
                 3. २ 䠩    ALTER
                    TABLESPACE.
                 4.  ⠡筮 ࠭⢮  online.

    ਬ      ⮡ ᨣ஢    砫  樨
                 ஢, :

                 ALTER TABLESPACE ACCOUNTING BEGIN BACKUP

                 ⮡ ᨣ஢,  backup 訫, :

                 ALTER TABLESPACE ACCOUNTING END BACKUP

                 ,  믮 backup     ⨢
                 ⠡筮  ࠭⢥.    offline  backup 
                 ॡ   娢஢ 䠩 ୠ  -
                  믮.

    易     CREATE TABLESPACE, CREATE DATABASE, DROP
    ⥬         TABLESPACE; ࠧ STORAGE.
    
                      ࠢ筨  SQL - ࠬ         5-21

                                   -- 101 --




E                              F

E    ALTER USER                                           F

    祭    ᫥騥  ࠪ⨪  -
                 ⥫  :

                 * ஫
                 * 㬠稢 ⠡筮 ࠭⢮  ᮧ
                   ꥪ⮢
                 * 㬠稢 ६ ⠡筮 ࠭⢮ 
                   ६ ᥣ⮢, ᮧ   -
                   ⥫.

    ।뫪  ॡ ਢ DBA

    ⠪    ALTER USER username [IDENTIFIED BY password]
                 [DEFAULT TABLESPACE tablespace]
                 [TEMPORARY TABLESPACE tablespace]

    祢 ᫮
     ࠬ
                 username          饣  騩 
                                  짮⥫  .
                 password          ஫   짮⥫

                 DEFAULT          ᯥ 㬠稢 ⠡筮
                 TABLESPACE       ࠭⢮  ᮧ ꥪ⮢.

                 TEMPORARY        ᯥ 㬠稢 ⠡筮
                 TABLESPACE       ࠭⢮  ᮧ ६
                                  ᥣ⮢.

    砭  ஫ 짮⥫    ⠪   -
    ᯮ짮-     GRANT.  稢 ६ ⠡-
              筮 ࠭⢮  짮⥫    -
                 ⠭    ⠪      祭   짮⥫
                 ਢ RESOURCE  ⠡筮  ࠭⢠  (
                 ᫨    ࢮ  ⠡筮  ࠭⢮  ஬
                 ।⠢ ⠪ ਢ). 稢 -
                   ⠡筮 ࠭⢮ ⠭ ⠪
                  ।⠢ 짮⥫  ࢮ  ⠡筮
                 ࠭⢠.

    ਬ           짮⥫ SCOTT ஫  LION 
                 祭    ⢥   㬠稢 -  ⠡筮
                 ࠭⢮ TSTEST, :

                 ALTER USER SCOTT
                 IDENTIFIED BY LION
                 DEFAULT TABLESPACE TSTEST

    易     CREATE TABLESPACE  GRANT.
    ⥬
    
                  ࠢ筮 㪮⢮   SQL        5-22


                                   -- 102 --




E                            F
                                                           E
    AUDIT (ଠ I)                                                F


    祭   롮 権 ॣ樨 㯠   .

    ।뫪  ॡ ਢ DBA

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

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

                 ALL              ⥭ ᯨ  -
                                   権.  㬮砭 -
                                   ॣ ᨬ 
                                  ⮣, ᯥ譮    -
                                  .

                 WHENEVER         㪠뢠,  SQL -  -
                 SUCCSESSFUL       ॣ஢ ⮫쪮  
                                  ᯥ譮 襭.

                 WHENEVER NOT     㪠뢠,  SQL -  -
                 SUCCSESSFUL       ॣ஢ ⮫쪮  
                                  ਩ 襭.

    砭   ଠ ॣ樨 㯠     DBA
    ᯮ짮-  ᫥ ᫥騥 ⢨:
    
                 * 祭  ⪫祭 짮⥫   -
                    ( CONNECT)
                 * , ॡ騥 ਢ RESOURCE, ਬ
                   ⠪,  ᮧ ꥪ  
                 * , ॡ騥 ਢ DBA ( SBA)
                 * , 訢訥  訡 ORACLE, ⠪ 
                 㪠  ꥪ      (
                 NOT EXIST).

                  㯠    ஢ ⥬ 砥 
                  ࠬ AUDIT_TRAIL 䠩 INIT.ORA.  -
                 ᨬ  ⮣,  祭 ॣ  , SQL -
                  AUDIT 㤥 믮 ࠢ쭮. ᫨ -
                  ⢮,    ୠ  
                 ,  ⨢ 砥 - .
                   ॣ樨  १   ⠡
                 ᫮ .    १ ⮣   
                 ஢   ᭮   .  ᫨
                 ⠡ ॣ樨  ᫮  ࠧ, 
                  娢஢   ॣ樨 -
                    㤠   ⠡.   ⠡ ᫮
                    .
               
                      ࠢ筨  SQL - ࠬ         5-23


                                   -- 103 --




                 ,     ஢ DDL  DML ନ "-
                 襭" 砥 襭 "䠧  ࠧ"  (-
                 ,  ᬮ ᫮,  ஢ઠ ) ⭮-
                 ⥫쭮  믮 (execution).  ,
                     rollback  㤠 ப  ୠ
                 ॣ樨 㯠.

                 ᫨    ।    WHENEVER  SUCCSESSFUL,  
                 WHENEVER NOT SUCCSESSFUL,  SQL -  ॣ-
                  ᨬ   襭.


    ਬ       ॣ樨 ᯥ ᮢ ᮢ :

                 AUDIT RESOURCE WHENEVER SUCCSESSFUL

                  ॣ樨 ᯥ ⮪ 㯠  
                  :

                 AUDIT CONNECT  WHENEVER NOT SUCCSESSFUL

    易     AUDIT  II  NOAUDIT.
    ⥬
                                             
                  ࠢ筮 㪮⢮   SQL        5-24

































                                   -- 104 --




E                            F

E         AUDIT (ଠ II)                              F


    祭    ॣ 㯠  ⠪  ꥪ⠬  
                 ,  ⠡  .

    ।뫪   ॣ樨 ꥪ⮢      -
                 楬,   ਢ  DBA.    DEFAULT
                 ॡ ਢ DBA.

    ⠪    AUDIT {table_option [,table_option]... | ALL}
                 ON { [user.]table | DEFAULT}
                 [BY {SESSION | ACCESS}]
                      
                 [WHENEVER [NOT] SUCCSESSFUL]

    祢 ᫮
     ࠬ
                 table_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               ਬ  祭
                                   ਥ 権.
                 username           ⠡
                 table             ⠡,  ,  ᫥-
                                  ⥫쭮  ᨭ, 騩
                                    ⠡.
                 DEFAULT          ᯥ,  ᮧ -
                                  ᫥⢨ ⠡  ॣ-
                                  ,  㪠  table_option
                                  (⠭ ⥬ 樨
                                  ॣ樨 㯠).
                 BY SESSION       㪠뢠,   ᥠ 㤥 ᮧ-
                                   ⮫쪮    
                                   ⨯ ॣ㥬 㯠
                 BY ACCESS         㤥 ᮧ  
                                  ⪨ 㯠  ⨯.

    
                      ࠢ筨  SQL - ࠬ         5-25







                                   -- 105 --




    砭  ࠧ BY  । ⥯ ⠫樨 ॣ-
    ᯮ짮-   ஫ 㯠,  㪠뢠     
              뢠          AUDIT_TRAIL.  BY
                 SESSION   ⮬,   । ⨯ 㯠
                 ਬ  ꥪ  .  BY ACCESS ।-
                 ,  ॣ 㤥 믮  ⠪  ,
                   ⢫     㯠.

                  ᮡ  ࠧ  , 祬
                 㣨 (ਬ - 筮 DML -  믮-
                   ,   GRANT).    ⠫쭠
                 ॣ, ⥬ 襥 ⢮ ᥩ -
                 , ⠪      ஠஢,  
                   ॣ஢.

                  ० BY SESSION    ⠡ 
                      ᥣ ᥠ 짮⥫.
                 ࠭ SQL -  뢠  -
                 ,   ᥩ  ⠡ AUDIT_TRAIL.  -
                 ⢮ ⠪        ᫠
                 ᯥ  㤠 ⮪ 믮 ஢
                 । ⨯. ⢨ ࠧ BY ... 砥
                  㬮砭 BY SESSION.

                  ०  BY  ACCESS  ॣ㥬 SQL - 
                 ⬥   AUDIT_TRAIL      (⠢
                 ப).   権 DML ப   -
                  ᫥ 襭 "䠧 ࠧ" .

    ਬ        ॣ樨  ᥠᮢ,    뫨 -
                  ⪨ 㯠  ⠡ EMP, :

                 AUDIT ALL ON EMP WHENEVER NOT SUCCSESSFUL

                  ॣ樨    ⪨  ⠢ ப  
                 䨪樨  ⮩  ⠡, :

                 AUDIT INSERT, UPDATE ON EMP BY ACCESS

    易     AUDIT  I  NOAUDIT.
    ⥬

















                                   -- 106 --



                  ࠢ筮 㪮⢮   SQL        5-26


E                              F

E         CLOSE cursor (஥ SQL)                 F


         祭 ᢮  ,  ।  ஬
                    OPEN cursor.

        ।뫪   ࠭  .

        ⠪   EXEC SQL CLOSE cursor_name

        祢    cursor_name      ࠭  .
        ᫮ 
        ࠬ

        砭   ⪠        ਢ  
         ᯮ-  訡.  室 뢠 , ⮡
               .  ⮣  ப 
                      ࠭.

                    ⨥  ᢮ ࠭  -
                     ; ᢮, ,  믮,
                     ,  易  ஬, 㦭 㣮
                     .  ᢮ ⮬-
                     믮 ORACLE.

                    樨 ४ HOLD_CURSOR  RELEASE_CURSOR
                      ⢨   CLOSE  cursor.  
                    ଠ樨      "㪮-
                     ணࠬ  ४ࠬ ORACLE".

         ਬ     騩 ਬ 뢠  ⨯  ᫥-
                    ⥫쭮 ᯮ짮   ஢.     
                     ,   ,   ப  ,
                    ,  .

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

                    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 sample;
                       end if;
                    end loop example;

                    EXEC SQL CLOSE c;



                                   -- 107 --



         易   PREPARE, DECLARE CURSOR, OPEN cursor.
         ⥬
                    
                      ࠢ筨  SQL - ࠬ         5-27


E                            F

E         ࠭祭  ⮫                       F

         .  ࠧ "ࠧ CONSTRAINT".


E                            F

E          ᠭ  ⮫                  F


         祭 । ⮫  易   ࠭祭.

        ।뫪 .

        ⠪   column datatype [DEFAULT expr] [column constraint]

        祢    column             ⮫, 稭-
        ᫮                       饥 ᮣ襭   
        ࠬ                    ꥪ⮢  .

                    datatype         ࠢ ⨯  (।-
                                        3 - " 몠
                                     SQL"  㪮⢠.

                    DEFAULT expr     易⥫쭠 ࠧ DEFAULT -
                                     ।  ࠦ, 砥
                                     ⮫, ᫨    㪠-
                                          ப 
                                     ⠡. ⢨⥫쭮  -
                                     ࠦ, ᪫ 뫠騥 
                                     ⮫, LEVEL, ROWNUM.  -
                                      ࠦ    ᮮ⢥-
                                     ⢮ ⨯  ⮫. ⠪
                                     expr ।   4 -
                                     "樨  㭪樨"  -
                                     ⢠.

                    column constraint  (.      ࠧ   "ࠧ
                                     CONSTRAINT").

         砭  ORACLE Version 6.0 ন  ᨭ⠪  ࠧ
          ᯮ- DEFAULT; ᥬ᪠ প 㤥 ᯥ祭
               .

                     ᠭ ⮫ ᯮ  -
                     CREATE  TABLE    ALTER TABLE  ।
                    ⮫殢 ⠡.

         ਬ     । ⮫  EMPNO    ⨯  NUMBER,
                    ࠧ஬ 5   NOT NULL, :

                                   -- 108 --



                    CREATE TABLE EMP (EMPNO NUMBER(5) NOT NULL, ...)

                    ⮡ ᮧ ⮫ DEPTNO  ⨯ NUMBER, ࠧ-
                    ஬ 5     ࠧ㬥  祭  100,
                    :

                    CREATE TABLE EMP (..., DEPTNO NUMBER(5) DEFAULT 100)

         易  ࠧ CONSTRAINT;  CREATE TABLE.
         ⥬

















































                                   -- 109 --



                  ࠢ筮 㪮⢮   SQL        5-28


E                            F

E         COMMENT                                       F

         祭 뢠  ᫮  ਨ,  ⭮-
                    騥  ⠡.

        ।뫪     ⠡   ⮫  
                      楬 ⠡   ਢ-
                     DBA    ਢ  ALTER    
                    ⠡.

         ⠪  COMMENT ON { TABLE [user.]table
                               | COLUMN [user.]table.column }
                        IS 'text'

         祢   user              ⠡.
         ᫮ 
         ࠬ  TABLE table      ᯥ  㥬
                                     ⠡  .

                    COLUMN column    ᯥ  㥬-
                                      ⮫.

                    text             祭    窨
                                     ⥪     255  ᨬ.
                                     . ᠭ  text     3 -
                                     " 몠 SQL"  -
                                     ⢠.

         砭   ਩       
          ᯮ-   ⢥     ப  ''.
             ⮡ ᬮ ਩,    -
                      ஢ ᫮  : ALL_COL_COMMENTS,
                    ALL_TAB_COMMENTS, USER_COL_COMMENTS  USER_TAB_
                    COMMENTS.

         ਬ     ᥭ   ⮫ NOTES  ⠡-
                     SHIPPING,  :

                    COMMENT ON COLUMN SHIPPING.NOTES
                      IS 'Special packing or shipping instructions'

                     㤠 ⮣  室 :

                    COMMENT ON COLUMN SHIPPING.NOTES IS ''

         易  /**/(਩ SQL).
         ⥬
                                          
                      ࠢ筨  SQL - ࠬ         5-29





                                   -- 110 --




E                            F

E         COMMIT                                        F


         祭  COMMIT ਬ  ⮣, ⮡:

                      * ᤥ "ﭭ묨"  ,  ᤥ-
                        ⥪饩 ࠭樨 (ॠ쭮   
                        ᪮쪮 )

                      *   窨 ࠭  ࠭-
                      樨

                      *  ࠭

                      * ᢮  ஢  ࠭樨

        ।뫪 .

        ⠪   COMMIT [WORK]

         祢   WORK             易⥫  ᯥ稢 ANSI
         ᫮                      - ᮢ⨬.  COMMIT
         ࠬ                    COMMIT WORK - .

         砭     ࠭樨    ਪ-
          ᯮ-  ணࠬ (  ᫥ ࠭
             । ᮥ  ORACLE)  ᯮ 
                    COMMIT WORK ( ROLLBACK WORK).  ᫨   
                    ᠫ ࠭,   ਪ ணࠬ  -
                    訫 ਩,  ᫥  ᠭ ࠭-
                     㤥 ⪠祭.

                    ଠ 室  設⢠ ⨫   -
                    ⠫ ।    ORACLE   㤥   뢠
                    ⥪ ࠭. ଠ 室  ணࠬ
                    ४ ORACLE  ਢ   ⪠ ⥪饩
                    ࠭樨. .  ࠧ  樨 COMMIT 
                    ஥ SQL.

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




                                   -- 111 --



                     COMMIT    ROLLBACK  ⠪  
                    ࠭ ⨯ "READ ONLY", 稭 -
                    ஬ SET TRANSACTION.
                    
                  ࠢ筮 㪮⢮   SQL        5-30


         ਬ       ப  ⠡ DEPT   -
                      ᫥騥 :

                    INSERT INTO DEPT VALUES ('50','MARKETING','TAMPA')
                    COMMIT WORK

         易   ROLLBACK, SAVEPOINT, SET TRANSACTION.
         ⥬












































                                   -- 112 --



                      ࠢ筨  SQL - ࠬ         5-31


E                            F

E         COMMIT (஥ SQL)                       F


         祭  ﭭ묨  ,  믮 
                     ࠭樨.  RELEASE ᯮ 
                    ᢮  ᮢ  ᮥ  
                    .

        ।뫪 .

        ⠪   EXEC SQL [AT dbname] COMMIT [WORK] [RELEASE]

         祢   AT dbname        易⥫쭠 ࠧ,  㪠뢠-
         ᫮                         ,  ன
         ࠬ                   뤠 COMMIT.   ⥫쭮
                                     ଠ樥   ࠧ  AT -
                                          ᠭ     
                                     DECLARE DATABASE.

                    RELEASE          易⥫ ࠬ,  ᮥ-
                                     騩    .

         砭  ᥣ 蠩  ᫥  ࠡ 
          ᯮ-  ஢  COMMIT   ROLLBACK  樥
             RELEASE. ᫨   믮 COMMIT    RELEASE,
                    ஢, 襭     ࠡ 襩
                    ணࠬ,   ঠ  ,    ORACLE
                    RDBMS   ,      ⨢.
                      ਢ  ⮬,   ⠫ 짮-
                    ⥫, 㦤騥    ,  -
                       訡  ⪥ 饭.

                    ᫨  COMMIT 뤠  樨 RELEASE, -
                     ⥪    ࠭   㤥   ᠭ   
                    ᫥饣 ᮥ   .

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

                     COMMIT WORK    ᮤন host
                    - ६    쭥襩 ࠡ  ண-
                    ࠬ.

         ਬ     EXEC SQL COMMIT RELEASE

         易   ROLLBACK, SAVEPOINT, SET TRANSACTION.
         ⥬
         
                  ࠢ筮 㪮⢮   SQL        5-32





                                   -- 113 --




E                            F

E         CONNECT (஥ SQL)                      F


         祭 ᯥ稢 אַ 祭   ORACLE  RDBMS.
                    ணࠬ   믮  ⮣,  -
                    ⠥   .  CONNECT ᯮ
                     ᮥ 짮⥫    
                    ORACLE.

         ।뫪  ᯥ㥬     -
                      CONNECT.

         ⠪  EXEC SQL CONNECT :user_password
                        [AT dbname]
                        [USING :dbstring]

                    

                    EXEC SQL CONNECT :user IDENTIFIED BY :password
                        [AT dbname]
                        [USING :dbstring]

         祢   :user_password    host - ६, ᮤঠ-
         ᫮                        짮⥫    ஫,
         ࠬ                   ࠧ ᫥襬 "/".
                                      ⮬᪮   ॣ樨
                                     ⠭ 祭  ⮩  -
                                          "/".      
                                     ORACLE,   ॣ樨  -
                                     室 ᯮ짮   -
                                     ⥫  樮 ⥬.

                    :user IDENTIFIED  ।  짮-
                    By :password     ⥫  ஫    ⤥
                                     .

                    AT dbname        易⥫쭠 ࠧ, ।-
                                      㬠稢   .
                                            ।-
                                     ⥫쭮 ।     
                                     DECLARE DATABASE.

                    USING            । ப   ᯥ䨪樨
                    connect_string    㤠      
                                     ࠡ १ SQL*Net.


         砭  ᫨  ࠧ AT,   ⢥ ࠧ㬥-
          ᯮ-  ᯮ    ,  㪠  
             ࠧ USING.

                     ணࠬ   믮 ᪮쪮 -
                    ஢ CONNECT,    ⮫쪮   ᮥ 
                     믮   ᯮ짮  ࠧ  AT  
                    ᯥ஢ 㤠  .

                                   -- 114 --



                    ᫨   ⨫  ࠧ USING,   CONNECT
                       ࠧ㬥  .
                    
                      ࠢ筨  SQL - ࠬ         5-33























































                                   -- 115 --




         אַ ᮥ १ SQL*Net

                    אַ ᮥ ॡ   ⠭ -
                     痢   㧫  饭   -
                      㤮  ᯮᮡ १ , -
                      ⮩    .    ᯮ짮
                    ⮣  ⮤  ᮥ  㤠 
                     ।⢥,  㪠뢠   裡,  
                    ஥  뫠  㣨  EXEC SQL.
                    騩 ਬ  ⠪ ⮤:

                    :userid = "scott";
                    :password = "tiger";
                    :connect_string = "D:HQVMS";
                    EXEC SQL DECLARE dbname DATABASE;
                    EXEC SQL CONNECT :userid
                      IDENTIFIED BY :password
                      AT dbname
                      USING :connect_string;

                    ᫥騥 SQL  - ,  ᯮ騥 ࠧ
                    AT,    ᥡ  dbname,    믮-
                        ,  饩  -
                     dbname. (,  dbname -   host
                    - ६   祭    
                      ࠡ;    ࠢ SQL
                    - 䨪).  connect_string ᮤন ப
                    SQL*Net,  ᯥ   㤠 㧫,
                    饬  ਬ,    - MYNODE  ࠡ 
                    ⮪  DECnet.  (D:MYNODE).    ᠭ
                    ᨭ⠪ connect_string ᮤন  "㪮-
                    ⢥ 짮⥫ SQL*Net"    襣  ⮪
                    SQL*Net.

                    ᯮ  ⮤,   믮  ᫥
                    ᫥⥫쭮 :

                    EXEC SQL AT remote_db SELECT ...;
                    EXEC SQL AT remote_db INSERT ...;
                    EXEC SQL AR remote_db DECLARE cursor_name
                           CURSOR FOR SELECT...;
                    EXEC SQL OPEN cursor_name...;
                    EXEC SQL FETCH cursor_name...;

                    ,     ⨨   롮થ 
                       ᯮ ࠧ AT.  SQL -  
                    㤥 믮   ,    뫠-
                      ࠧ AT,  易  ஬ DECLARE
                    cursor.

                     ᯮ짮  ⠪ 孨   
                    ,   祬  ਢ   㤠  
                     ( 䨪 ).  ஬ ⮣,
                      ন ᪮쪮  ࠭権,  
                       ,  ன  ᮥ.
         
                  ࠢ筮 㪮⢮   SQL        5-34

                                   -- 116 --




         ਬ     ਢ   ᯮ  ⮪
                    DECnet  祭 짮⥫ १ SQL*Net
                     㤠   NYC, ࠭ । 
                      DECLARE DATABASE:

                    EXEC SQL CONNECT :username
                        IDENTIFIED BY :password
                        AT NYC
                        USING D:NYC_NODE

                    । ᪮ ⮣  ਫ -
                    稫   ORACLE  ࠢ  짮⥫
                     ஫.


         אַ ᮥ १ SQL*Net

                    㣮  饭  㤠   ண-
                    ࠬ ४஢ -   ᯥ稢-
                     ORACLE RDBMS ।⢮ 믮 ।-
                     ᮢ.   ⮬ 砥  ᮥ  
                       (쭮)     襬
                    ᮥ  㣮 (㤠)   . 
                    । SQL -   ࠡ  ᢮ -
                     ,    ࠧ     -
                    ࠥ   㣮 .

                    뢠饥 㦤 ᮧ  ᯮ짮
                     痢 ந  ᠭ  -
                     CREATE DATABASE LINK.

                    ,      易  -
                      ,  ᯮ㥬  ࠧ "AT" -
                    .    ਬ 
                    SQL -  ᫥  ⠡.  ⨢-
                     ⮬,      ४
                    ᯮ ⮫쪮  ࠧ "AT"  "EXEC
                    SQL"   ஢,      
                    믮.

                    ,   ⫨稥    אַ  ᮥ,
                     裡 ᪠ 믮 ⮫쪮 -
                     SELECT...

         易   COMMIT RELEASE  DECLARE DATABASE.
         ⥬












                                   -- 117 --



                      ࠢ筨  SQL - ࠬ         5-35


E                            F

E         ࠧ CONSTRAINT                              F

         祭 ࠭稢    室  祭  
                    ⮫  㯯 ⮫殢.   믮 -
                    ஢ INSERT,  UPDATE  DELETE  ᮮ⢥騥
                    ࠭祭 ஢. ⮡  믮-
                     ᯥ譮, ࠭祭  믮.

                      ORACLE  Version  6.0  ন
                    ᨭ⠪ ࠭祭    ।
                     ᫮ .   ᨨ ࠭祭 (
                    ᪫祭 NOT NULL)  ஢.

                    ࠭祭 ᯮ    ⮫
                     㯯 ⮫殢 ᫥ ᫮:

                    * ॡ,  ⮡ ⮫  㯯  ⮫殢
                      ᮤঠ 祭 NOT NULL
                    * ॡ,  ⮡ ⮫  㯯  ⮫殢
                      뫨 㭨   ⠡
                    * 䨪 ⮫  㯯   ᭮
                       (PRIMARY KEY)
                    * ॡ  祭 ⮫   㯯
                       㣮 ⠡ (譨  - FOREIGN KEY)
                    * ॡ 㤮⢮७  ⮫    㯯
                      ஬ ࠦ (CHECK)

         ।뫪  ᯮ짮  column_element  table
                    _constraint    CREATE  TABLE    ALTER
                    TABLE.   CREATE  TABLE ॡ ਢ
                    RESOURCE.

         ⠪  [{UNIQUE | PRIMARY KEY} (column [,column]...)
         ࠭-       [CONSTRAINT constraint] ]
              [FOREIGN KEY (column [,column]...)
         ⠡      REFERENCES [user.]table [(column [,column]...)]
                         [CONSTRAINT constraint] ]
                    [CHECK (condition) [CONSTRAINT constraint] ]


         ⠪  Column [NULL] | [NOT NULL [CONSTRAINT constraint] ]
         ࠭-  [{UNIQUE | PRIMARY KEY}   [CONSTRAINT constraint] ]
                REFERENCES [user.]table [(column)]
         ⮫         [CONSTRAINT constraint] ]
                    [CHECK (condition) [CONSTRAINT constraint] ]
                
                  ࠢ筮 㪮⢮   SQL        5-36







                                   -- 118 --




                    Column           ᯥ   ⮫, 
                                      뢠 ࠭祭

                    CONSTRAINT       ᯥ  ࠭祭.
                    constraint        易⥫쭠  ࠧ ਬ-
                                       ᥬ ࠭祭.

                    NULL |           㪠뢠,  ⮫  
                    NOT NULL           ᮤঠ 祭 NULL.

                    UNIQUE           ॡ, ⮡  ⮫  㭨-
                                     .

                    PRIMARY KEY      । ⮫   ᭮
                                     .

                    FOREIGN KEY      ।  ⮫   譨
                    (column..)          user.table(column(s)).
                    REFERENCES        㬮砭 columns(s) 
                    user.table(column)  ᭮ 箬 table.

                    CHECK condition  ।   ᫮,   ஬
                                        㤮⢮   ப,
                                     ⮡ 室  ⠡.  -
                                     ।  ᨭ⠪  condition
                                     室   4  - "樨
                                       㭪樨"  㪮⢠.

                       ⨯  ࠭祭:    ⠡ 
                    ⮫殢 (table constraints, column constraints).
                     ,  ᪫祭 ⮣,  ࠭-
                      ⮫ 뫠   ⢥  ⮫-
                    ,     ६    ࠭祭 ⠡ 
                    뫠  襥 ᫮ ⮫殢.

                    ࠭祭   ᯥ஢  ᯮ-
                    ᮡ:

                    * ।  ࠭祭   ⠡  
                    쭮 । ⠡:

                    CREATE TABLE Project_Empoloyee
                        (PROJECT NUMBER,
                         EMPLOYEE NUMBER,
                         PRIMARY KEY (PROJECT,EMPLOYEE) )

                    * ।  ࠭祭    ⮫ 쭮
                     । ⮫:

                    CREATE TABLE DEPT
                        (DEPTNO NUMBER PRIMARY KEY, ...)

                       㣨  ꥪ   ,  ࠭祭
                    ࠭  ᫮ .




                                   -- 119 --



                            ࠭祭.
                    ᫨    ᤥ ⮣,   ࠭祭 㤥
                    祭 ⥬   SYS_Cn,   n - 楫,
                    饥   㭨   .  -
                     祭 ⥬   ७ 
                     .
                    
                      ࠢ筨  SQL - ࠬ         5-37


                    ࠭祭  ᯮ짮   -
                     CREATE TABLE,   -  ALTER TABLE.  
                     㤠       ஬   ALTER
                    TABLE.  䨪樨 ࠭祭  室
                    㤠   .

         NULL       ࠭祭 NULL    ਬ  ⮫쪮  
                    ⮫殢.   ࠭祭     祭
                    NULL  NOT NULL. ࢮ 砥,   ⠡-
                       ᪮쪮 㣮 ப,  ᮤঠ 
                    ⮬ ⮫ 祭 NULL  ,  NOT  NULL
                    砥 ⢨  ⠪ ப.   ⢨
                    । ࠭祭  NULL,    ࠧ蠥
                    ⮫栬 ᮤঠ ⠪ 祭.

         ਬ      祭    ⠡  ⮫  EMPNO  ⨯
                     NUMBER, 饣 ࠭祭 NOT NULL (
                    ࠭祭 NN_CNSTRNT), :

                    CREATE TABLE EMP
                   (EMPNO NUMBER(5) NOT NULL CONSTRAINT NN_CNSTRNT...)

         UNIQUE     ࠭祭 QNIQUE:

                      * ॡ,  ⮡  ப      ⮬
                        ⮫ ࠧ 祭
                      * ॡ, ⮡ ⮫   NOT NULL
                      * ॡ, ⮡ ⮫   ᭮ 箬.

         ਬ      祭    ⠡  ⮫  ENAME  ⨯
                     CHAR, 饣 ࠭祭 QNIQUE ( -
                    ࠭祭 UNQ_NAME), :

                    CREATE TABLE EMP ( ...
                         ENAME CHAR(19) UNIQUE CONSTRAINT UNQ_NAME
                         ... )

                     祭   ⠡ EMP ⮫殢   -
                      ⥫䮭 , ।  ࠭-
                     UNIQUE (࠭祭  ⠡), :

                    ALTER TABLE EMP
                       ADD (PHONE_AREA_CODE CHAR(3) NOT NULL,
                            PHONE_NUMBER CHAR(8) NOT NULL,
                            UNIQUE (PHONE_AREA_CODE,PHONE_NUMBER)
                            CONSTRAINT PHONE_CNSTR)
                    
                  ࠢ筮 㪮⢮   SQL        5-38

                                   -- 120 --




         PRIMARY KEY  ᭮ :

                        * 㦨    㭨쭮  ஢
                           ப ⠡
                        *  ꥤ ॡ  UNIQUE  
                          ॡ NOT NULL
                        * ॡ,  ⮡ ⮫   ࠭祭
                          UNIQUE
                        *   㪠  ⠡  ⮫쪮  
                          ࠧ,         ᪮쪨
                          ⮫殢. ᫨ ᭮  ᮤন ⮫-
                            ⮫,   ᯮ짮 
                          ࠭祭  ⮫,  ⠪   ⠡-
                          . ᫨ -   ⮫, 室
                          ᯮ짮 ࠭祭  ⠡.

         ਬ      祭  ⠡ EMP  ⮫  EMPNO  ⨯
                    NUMBER   ࠭祭 PRIMARY KET ( ࠭-
                     PK_CNSTRNT), :

                    CREATE TABLE EMP
                        (EMPNO NUMBER(5) PRIMARY KEY CONSTRAINT
                         PK_CNSTRNT, ...)

                     ᮧ ⠡, 뢠饩   -
                    ⥩   ।  SHIP_NO   CONTAINER_NO 
                    ⢥ ᭮ , :

                    CREATE TABLE SHIP_CONTAINER
                       (SHIP_NO NUMBER NOT NULL,
                        CONTAINER_NO NUMBER NOT NULL,
                        PRIMARY KEY (SHIP_NO, CONTAINER_NO))

         FOREIGN KEY ,  ࠭祭 REFERENCES, 뢠-
                     ⠡楩 譥 .  ,  ன
                     १ ࠭祭 REFERENCES,  뢠-
                     ⠡楩 ᭮ .   쭥襬  -
                    㦤 EMP  - ⠡ 譥 ,   DEPT -
                    ⠡ ᭮ .

                    ࠭祭 FOREIGN KEY/REFERENCES:

                      * ॠ  楯  "譥  ",  
                        ᠭ   1 "樮 "
                      * 㤥  ⢥࣠  ⪨  믮  
                        INSERT  UPDATE  祭ﬨ,   -
                        騬  ⠡ ᭮ 
                      * 㤥  ⢥࣠  ⪨  믮 
                        DELETE, ᫨ ᫥  ᮣ   -
                        祭 REFERENCES.  ਬ - ⤥  -
                          㤠  ⠡ DEPT,  ᫨  ⠡-
                         EMP    㦠騥,  ࠡ騥 
                         ⤥.
                        
                      ࠢ筨  SQL - ࠬ         5-39



                                   -- 121 --




                      *    뫠    ⮫  (  㯯
                        ⮫殢) PRIMARY KEY   UNIQUE    ⠡
                        ᭮ 
                      *  뫠   ⮫  PRIMARY  KEY  
                        ⠡ ᭮ 
                      *  뫠   ⠡,     
                          
                      * ॡ,  ⮡  뫨 楬   
                        ਢ REFERENCE    ⠡  ᭮
                        ,     ਢ  REFERENCE   
                        ⮫殢,   뫠  ⠡ -
                         .
                      *    ࠭祭  ⢮ -
                          ⮫  ⠡.  ⥫쭮, 
                        ⮫  뫠 ᪮쪮 ࠧ
                      * ॡ,  ⮡ ⨯  ⮫殢 譥
                           ,     뫠,  뫨
                        묨 (ᮢ   易⥫쭮)
                      *   뫠      ᠬ ⠡ (
                        ᠬ ᥡ)
                      *   뫠   ⮫ ᪮쪮
                        ࠧ (   ⮫   뫠 
                         ᪮쪨  ࠭祭 譥 -
                        ).

                     ᯮ짮  ࠭祭  ⠡  ᨭ-
                    ⠪ FOREIGN  KEY  (column [,column...]) 
                    ।⢮ 祢 ᫮ REFERENCES.

                    ᫨  ࠭祭 FOREIGN KEY  ᮮ⢥-
                    饣 ᯨ᪠ REFERENCES,  ⮫ ।-
                     PRIMARY KEY  ⠡ REFERENCES.

                    ଠ  ࠭祭 ⠡  ⮫殢  -
                           ⠡     ᫮     
                    CONSTRAINT_DEFS  CONSTRAINT_COLUMNS.
                    
                  ࠢ筮 㪮⢮   SQL        5-40




















                                   -- 122 --




         ਬ     ᫨ ⮫ DEPTNO ᬠਢ  ⠡ EMP
                     ⢥ 譥 , 㦠騩   
                      ⠡ EMP,    DEPTNO ᮤন
                    祭 NULL (।,  DEPTNO ࠧ蠥
                    稥 祭 NULL)    ᮢ  -
                      祭 DEPTNO  ⠡  DEPT. 騩
                    ਬ    CREATE TABLE,
                    室  ᮧ 譥    DEPTNO,
                    뫠饣  ⮫ DEPTNO  ⠡ DEPT:

                    CREATE TABLE EMP

                      ( ...,
                       deptno NUMBER REFERENCES scott.dept ( deptno )
                          CONSTRAINT deptno_fk,
                      ... )

                       ⠡ ORDERS  ⮫殢 SHIP_NO
                     CONTEINER_NO    ।  뫪  ⠡
                    SHIP_CONTAINER 짮⥫ BLAKE ᯮ  ᨭ-
                    ⠪ ࠭祭  ⠡, 室 :

                    ALTER TABLE ORDERS
                      ADD (SHIP_NO NUMBER NOT NULL,
                           CONTAINER_NO NUMBER NOT NULL,
                           FOREIGN KEY (SHIP_NO, CONTAINER_NO)
                            REFERENCES BLAKE.SHIP_CONTAINER
                                       CONSTRAINT ORDERS_FK)

         CHECK      ⠪ ࠭祭 CHECK 祭  ⮫
                     ⠡.   ࠭祭  CHECK    뫠
                    ⮫쪮  ⮫ ⮩  ⠡.    ࠭祭
                     ⮫  뫠  㣮 ⮫ 
                    ᪫祭 ᠬ ᥡ;  ࠭祭  ⠡-
                      ᫠  ᪮쪮 ⮫殢.

         ਬ     CREATE TABLE dept
                     (deptno NUMBER CHECK (deptno between 10 and 99),
                      dname CHAR(9) CHECK (dname = upper(dname)),
                      loc CHAR(10) CHECK (loc in ('DALLAS','BOSTON',
                                           'NEW YORK')))

                    ࢠ ࠧ CHECK     ⤥-
                      10   99;   ஢, ⮡
                      ⤥ 뫨  ᨬ 孥-
                     ॣ;  ஢, ⮡  -
                    த,       ᯮ  ⤥  뫨:
                    DALLAS, BOSTON  NEW YORK.

                      ࠭祭,  ࠭饣,  
                       ᨮ   㬬   ॢ
                    5000 ஢, ᫥  ⠪ :

                    ALTER TABLE EMP
                       ADD (CHECK (SAL+COMM 5000))
                    
                      ࠢ筨  SQL - ࠬ         5-41

                                   -- 123 --




         ਬ ⥫ ࠭祭  ⠡

                     ᫥饬   CREATE TABLE  
                    ࠭祭 PRIMARY KEY,   ࠭祭 FOREIGN
                    KEY   ࠭祭 CHECK (  ஢ ⠡-
                    ):

                    CREATE TABLE order_detail
                      (order_id NUMBER,
                       part_number NUMBER,
                       ... ,
                       PRIMARY KEY(order_id, part_number) CONSTRAINT
                                                          oid_pno_pk,
                       FOREIGN KEY(order_id)
                         REFERENCES scott.order(order_id) CONSTRAINT
                                                          oid_fk,
                       FOREIGN KEY(part_number)
                         REFERENCES scott.part(part_number) CONSTRAINT
                                                          pno_fk,
                       CHECK (order_id > 0) CONSTRAINT oid_ck,
                       CHECK (part_number > 0) CONSTRAINT pno_ck
                      ... )

                    1. ᭮   ⠡ ORDER_DETAIL -
                         ⮫殢 ORDER_ID  PART_NUMBER.
                    2. ORDER_ID  -  譨   ⠡ ORDER,
                        PART_NUMBER -  ⠡ PART.
                    3.    ORDER_ID,      PART_NUMBER     ⠡
                       ORDER_DETAIL    祭 NULL, ⠪
                        室  ᭮ . (   -
                       稢 ࠭祭 PRIMARY KEY  ᫥-
                       ⥫쭮,   室  㪠뢠   -
                       쭮  ᠭ ⮫殢).
                    4.     ⮫  譨 砬,
                         ப       ⠡-
                       ,    ⠡ ORDER  㤥 ⢮-
                        ᮮ⢥饣 祭 ORDER_ID,    
                       ⠡ PATR - PART_NUMBER.
                    5. ࠧ CHECK ࠭,      ORDER_ID,  
                       PART_NUMBER   .

                    ,  ᠭ FOREIGN KEY(column1,column2)
                    砥,  column1  column2    
                     譥     .     ⠪    FOREIGN
                    KEY(column1) ...,FOREIGN KEY(column2) ... ᮧ
                     ࠭祭,     譨 .

         易   CREATE TABLE   ᠭ  ⮫-
         ⥬       .
                    








                                   -- 124 --






























































                                   -- 125 --
