


                  ࠢ筮 㪮⢮   SQL        5-42


E                            F

E         CREATE CLUSTER                                F

         祭  ,  ᮤঠ騩    ⠡.
                    ப    ᪮쪨 ⠡, ࠧ-
                    騥      ,    
                     .   ⮣ ᫥,  ६ 㯠
                     ப   箬   蠥-
                    . ।     "砭
                     ᯮ짮".

         ।뫪 室  ਢ RESOURCE.  ᮧ-
                       㣮 짮⥫  -
                      ਢ DBA.

         ⠪  CREATE CLUSTER [user.]cluster
                        (column datatype [,column datatype] ...]
                        [PCTUSED integer] [PCTFREE integer]
                        [SIZE integer]
                        [INITRANS integer] [MAXTRANS integer]
                        [TABLESPACE tablespace]
                        [STORAGE storage]

         祢   user               
         ᫮     cluster           
         ࠬ  column              ᪮쪨 ⮫-
                                     殢, ⠢  .
                                     ।    
                                      "砭  ᯮ짮".
                    datatype            ⢨⥫  ORACLE
                                     ⨯ ,  ᠭ  
                                     1 "樮 ".  -
                                     ,  NULL  NOT NULL  
                                     ᯥ஢.
                    PCTUSED integer  ᯥ   ।,  
                                     ORACLE  㤥  ᯮ짮  
                                     ।,     -
                                        ப.  integer  -
                                      楫 ᫮,  -
                                     饥    業.    
                                     ᠭ PCTUSED  -
                                        CREATE TABLE.
                    PCTFREE integer  ᯥ , १ࢨ-
                                          
                                     饣 ७.  integer  -
                                      楫 ᫮,  -
                                     饥    業.    
                                     ᠭ PCTFREE  -
                                        CREATE TABLE.
                    SIZE             ᯥ ।   祭
                                     , 室  ࠭
                                      ப   祭-
                                       .
                    INITRANS integer  ᯥ  砫쭮  -

                                   -- 126 --



                                     ⢮  ࠫ ࠭権,
                                       ,  -
                                           .
                                     ᠭ  ࠬ INITRANS -
                                            ࠧ   CREATE
                                     TABLE.
                                     
                      ࠢ筨  SQL - ࠬ         5-43


                    MAXTRANS integer ᯥ ᨬ쭮 ᫮
                                     ࠫ ࠭権,  -
                                        ,  -
                                     騩   .  ᠭ
                                     ࠬ MAXTRANS   
                                     ࠧ CREATE TABLE.
                    TABLESPACE       ᯥ ⠡筮   -
                    tablespace       ࠭⢮, ஬ 㤥 ਭ-
                                      .
                    STORAGE storage  ।,        
                                     ।  . .
                                      ᠭ ࠧ STORAGE.


                    ਧ   ᮡ  ப   ࠧ
                    ⠡, 騥       
                     ORACLE.

                    ਧ ᯥ稢   ᮪ ஢
                    ࠢ 䨧᪨  ࠭  ப   
                    .    㬥 ६  㯠 
                    ਧ ⠡栬,  ⠪  ᭨ -
                     ⠡ .  窨 ७ 짮⥫
                    ਧ ⠡   ࠪ   筮
                    ਧ ⠪      ᯮ짮
                     ॡ  䨪権 SQL - ஢.

                    饥 ࠢ  -  ਧ  ⠡,  
                    ꥤ塞 .    ਧ   
                    ᭨ ६ 㯠  ꥤ,  
                     㢥      ᪠஢  ⠡,
                    室  ⠡ .  । -
                    ਧ樥   ᬮ, ᪮쪮 
                    믮 ꥤ  ⠡    -
                    쪮  믮     ᪠஢
                    (ࠢ   ⨢ ﭨ  ਧ樨 
                    믮 権 INSERT,  UPDATE, DELETE). 
                    祭 ⥫쭮 ଠ樨  २-
                     ਧ樨  易     -
                        2 "ன SQL - ஢ 
                    ਫ" 㬥 "ORACLE RDBMS 㪮⢮ 
                    ன ந⥫쭮".

                   ।,    ࠧ ORACLE
            㤥 㯯஢ ப   .  ப  
                     祭      䨧-
                    ᪨ ࠭ .


                                   -- 127 --



                    ⮫, ।塞    CREATE CLUSTER,
                    ⠭  箬  .   ⮫ 
                    ᮮ⢥⢮   ⨯        ࠧ
                    ⮫   ਧ ⠡. ⮫
                    ⠡, ⠢騥  ,  易⥫-
                      ᮢ  .

                     ⫨饥 祭   -
                         ᨬ    ᫠
                    ப,      .   
                    ᪮    㢥稢 ந⥫쭮
                     権.
                    
                  ࠢ筮 㪮⢮   SQL        5-44













































                                   -- 128 --




                     祭   砥  ᥡ:

                       * ROWID (19 ⮢)
                       * ७         ਧ
                         ⮫殢 (        ⮫
                          1  )

                    ਬ,  ᫥饣 :

                    CREATE CLUSTER XYZ (ABC CHAR(5), MNO CHAR(7))

                    ᮧ 祢 祭  33 ⮢:

                       * 19 ⮢  ROWID
                       * 5  7 ⮢  ⮫ ABC  MNO
                       * 2      ⮫殢

                    ᨬ쭮 ⨬  ࠧ   -
                      樮 ⥬.
                     ਧ ⮫殢  易⥫쭮 -
                     ࠭祭,      ⤥-
                     ⠡, 室  .
                       砥   ᮬ,  冷  ⮫殢  
                           -
                    .

         Size       ORACLE ᯮ ࠬ SIZE    ।
                    ,  १ࢨ㥬  ப, ᮮ⢥
                        ,  ᫥⥫쭮,  -
                    쭮  ⢠ 祩    
                    ORACLE. ᫨ SIZE   ⥫ -
                     ⮢   ORACLE, ⥬ 㤥 ᯮ짮-
                      訩  ᫥騩  ⥫.  ᫨  SIZE
                      ࠧ  䨧᪮  ORACLE,  㤥
                     祭, ࠢ  (㣨 ᫮ -
                         ).

                    ᫨  ⨫ ࠧ SIZE, ORACLE  
                      䨧᪨ .

                    ஬ ⮣,    祭  ॠ쭮  祭
                    SIZE     ORACLE  ਭ  
                       (訩  ॡ  -
                    襣 SIZE). ᬮ ⢨⥫ ࠧ SIZE
                    , ᨢ ⮫ KEY_SIZE  ⠡ ᫮
                     USER_CLUSTERS.

                     ᨬ쭮  ᫮ 祩   䨪-
                    ஢   , ORACLE  १ࢨ-
                         . ਬ,
                    ᫨  ᯥ஢ SIZE,  ਢ訩   
                    砬    ORACLE,  6 祩  
                     ᢮ ப    ࠧ, -
                         ॢ ࠧ . 
                     ६      
                       䥪⨢,   - 䨪஢
                    , ⠪   ,  騥    

                                   -- 129 --



                      । 뢠 䨪஢묨.
                    
                      ࠢ筨  SQL - ࠬ         5-45


                    祭 SIZE,  襥 ।     
                     ,    ਢ  ࠭
                        ᪮쪮 .  -
                    ,   讥 祭 ਢ  ᯮ-
                       .

                   ࠧ
         ⠡    CLUSTER    CREATE  TABLE.   
              ᥡ  16 ⠡,   ந-
                    ⥫쭮    ᫥  ਧ樨 4 - 5
                    ⠡.   묨 ⨯ LONG  ⠪  
                    ਧ.

                     ⠡  ᯮ ࠬ -
                       ,  騥  ᫥騥:
                    PCTUSED, PCTFREE, INITRANS, MAXTRANS, TABLESPACE,
                    STORAGE.

                 ᯥ稢      
                    ப,    .

                       室 ᮧ  믮
                    - DML - 権    ⠡栬. 
                    室 ᮧ  ,   ᫥騬
                    ᨭ⠪ᮬ:

                    CREATE INDEX iname ON CLUSTER cname

                     ,    ᮧ    
                      ⮫ ⠪    ᮧ-
                       , । ஬
                    CREATE CLUSTER. . ⠪  CREATE INDEX.
                    
                  ࠢ筮 㪮⢮   SQL        5-46




















                                   -- 130 --




         ਬ     ⮡ ᮧ  PERSONNEL,  ᮤঠ騩 ⠡-
                     DEPT  EMP,  ᯥ஢ ࠬ 
                     ਧ ⠡, :

                    CREATE CLUSTER PERSONNEL (department_number NUMBER)
                      STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10)

                     ਧ樨   ⠡ EMP  DEPT ᫥
                    :

                    CREATE TABLE EMP
                       (empno NUMBER NOT NULL,
                        deptno NUMBER NOT NULL,
                        ... )
                      CLUSTER PERSONNEL (deptno)

                    CREATE TABLE DEPT
                       (deptno NUMBER NOT NULL,
                        dname CHAR(9),
                        loc CHAR(9))
                      CLUSTER PERSONNEL (deptno)

                     , ᮧ  :

                    CREATE INDEX idx_personnel ON CLUSTER personnel

                      ,   ⠢ -
                      ⠡ EMP  DEPT.

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

























                                   -- 131 --




E                            F

E    CREATE DATABASE                                    F
                                                                 F
    祭       ﬨ:

                 * ⠭  ᨬ쭮  ⢠  ஢,
                 䠩    䠩 ୠ ୮ -
                 
                 * 㪠   ࠧ஢    䠩 -
                  ୮ 믮
                 * 롮 ०  ᯮ짮  ୠ  (ARCHIVELOG
                  NOARCHIVELOG)

    ।뫪  ॡ ਢ DBA

                 ।०:   ⮢   
                 砫쭮 ᯮ짮      -
                  .  ⮩   ⮫쪮
                 㧭   .

    ⠪    CREATE DATABASE [database]
                     [CONTROLFILE REUSE]
                     [LOGFILE filespec [,filespec] ...]
                     [MAXLOGFILES integer ]
                     [DATAFILE filespec [,filespec] ...]
                     [MAXDATAFILES integer ]
                     [MAXINSTANCES integer ]
                     [ARCHIVELOG | NOARCHIVELOG]
                                   
                     [EXCLUCIVE]
                      
    祢 ᫮ database       砥 饥    祬
     ࠬ                   쬨 ᨬ   .
                                  稢    ᯥ-
                                    ࠬ DB_NAME 䠩 INIT.
                                  ORA.    ஢ 
                                  ࠢ饬 䠩   -
                                    .

                 filespec         ᯥ䨪 䠩   
                                  ଥ:
                                  'filename' [SIZE integer [K|M][REUSE]

                 SIZE integer     㪠뢠 SIZE  . ⢨
                                  ⮣  ࠬ  ।   
                                  㬮砭 ᯮ짮 䠩 
                                  ࠧ஬ 10  ⮢    ୠ
                                  ॣ樨  -    500 ⮢.
                                  ᫨ 㪠 "", integer 㬭-
                                    1024, "" -  1048576.

                 REUSE            㪠뢠,     ୮ -
                                  짮  騥   䠩,
                                  ଠ    ࠧ-
                                  .


                                   -- 132 --



    
                  ࠢ筮 㪮⢮   SQL        5-48

























































                                   -- 133 --



                 CONTROLFILE      㪠뢠,  騥  ࠢ-
                 REUSE            騥 䠩, ᯥ㥬 -
                                  ஬ CONTROL_FILES          䠩
                                  INIT.ORA,  ᯮ짮 -
                                  ୮,   ९뢠  -
                                   ᮤঠ   ଠ.
                                   ᯮ,   ࢮ ᮧ-
                                    .
                 LOGFILE          ᯥ      䠩-
                 filespec         ,     ᯮ짮
                                   ୠ  ୮ 믮.
                                  ᫨    㪠,  㬮-
                                  砭 ᮧ  䠩   -
                                  , 騬  ⭮ -
                                  樮 ⥬.
                 MAXLOGFILES      ᯥ          ᨬ쭮
                 integer          ⢮ 䠩  ୠ, 
                                  -   ᯮ짮  
                                  ⮩  .   ᫮  -
                                   ᮫ ,  ४-
                                  뢠 ࠬ LOG_FILES.  ᫮
                                       㢥祭 ஬ 
                                   ᮧ   .   
                                  祭 2-256.   㪠
                                  襣 ᫠ ⥫  -
                                     ࠧ ࠢ 䠩-
                                  .
                 DATAFILE         ᯥ      
                 filespec         䠩,   ᯮ짮-
                                     ࠭  .  ᫨  
                                  㪠, ᮧ  䠩   -
                                  , 騬  樮 -
                                  ⥬.  䠩,  㪠   ⮬
                                  㬥   ਭ  ⠡-
                                  筮 ࠭ SYSTEM.
                 MAXDATAFILES     ᯥ ᨬ쭮    -
                 integer          ⢮ 䠩   , 
                                  -     ᯮ짮.
                                   ᫮  ⠭ ᮫
                                  ,      ४뢠     ࠬ
                                  DB_FILES.   ᫮    
                                  㢥祭  ஬   ᮧ-
                                   .  祭 1-255 
                                      樮  ⥬.
                                    㪠  襣 ᫠
                                  䠩      -
                                  ⥫쭮 ⮫쪮  ࠧ ࠢ
                                  䠩. 稢 祭 32.
                 MAXINSTANCES     㪠뢠 ᨬ쭮   ⢮
                 integer          ஢,    ६
                                   ஢  뢠 .
                                   ᫮  ⠭ ᮫
                                  , ४뢠 ࠬ INSTANCES
                                   祭 1-255.
         
                      ࠢ筨  SQL - ࠬ         5-49



                                   -- 134 --




                 ARCHIVELOG       ᯥ 砫  ०  -
                 NOARCHIVELOG     짮 䠩  ୠ -
                                   믮.     
                                       ALTER DATABASE.
                                   㬮砭            ਭ
                                  NOARCHIVELOG,   砥 -
                                   ᯮ짮 䠩   ।-
                                  ⥫쭮 娢஢;   ⠪
                                  ୠ   ᯮ짮 ⮫쪮
                                   ⠭      
                                  ᯮ  ⠭ -
                                  ⥫. ARCHIVELOG 砥,   -
                                  ।   ᯮ짮 䠩
                                  ୠ   娢஢,  
                                      ਬ 
                                  ⠭   , ⠪
                                   ⥫.

                 EXCLUSIVE        ᯥ,   㤥  -
                                    ᫥ 襭 ᮧ-
                                  . EXLUSIVE 㪠뢠,   
                                     ⮫쪮 
                                  .         
                                  ᥣ ᮧ  ० EXCLUSIVE,
                                   祢 ᫮  易⥫쭮.

                                   㯠     ᪮-
                                   ஢  室  ࠧ-
                                  ஢   ஢  ०
                                  SHARED,  ᯮ 樨 DISMOUNT 
                                  MOUNT    SHARED      ALTER
                                  DATABASE.

    砭   CREATE DATABASE 㤥 㤠   
    ᯮ짮-  㪠 䠩    ⮢   -
              砫쭮 ᯮ짮.   ⥫쭮,  ᫨  
                  ਬ  饩   , 
                 ᮤঠ 㤥 ﭮ.

    ਬ      ⮡ ᮧ    ,  ᯮ 㬠稢
                 祭   㬥⮢, :

                 CREATE DATABASE

                    㪠 㬥⮢:

                 CREATE DATABASE NEWTEST
                     CONTROLFILE REUSE
                     LOGFILE 'LOGONE.LOG' SIZE 50000,
                             'LOGTWO.LOG' SIZE 50000
                     MAXLOGFILES 5
                     DATAFILE 'DBONE.DAT' 100000
                     MAXDATAFILES 10
                     MAXINSTANCES 2
                     ARCHIVELOG
                     EXCLUSIVE


                                   -- 135 --



    易     ALTER  DATABASE,  CREATE ROLLBACK SEGMENT,
                 CREATE TABLESPACE;     SQL*DBA:   STARTUP   
                 SHUTDOWN (  ਫ   - "ࠢ筨  SQL*DBA"
                 㬥 "ORACLE RDBMS  㪮⢮  
                  ").
                 
                  ࠢ筮 㪮⢮   SQL        5-50




















































                                   -- 136 --




E                            F

E         CREATE DATABASE LINK                          F


         祭  裡  쭮    짮-
                    ⥫  㤠 .    
                         ORACLE, ⠪  , -
                     - DB2  IBM.

         ।뫪 室       짮⥫ 
                    㤠   .    裡   ⨯
                    PUBLIC    ⮫쪮 짮⥫  ਢ-
                     DBA.    쭮,    㤠 
                           ⠫஢  ணࠬ
                    ᯥ祭 SQL*Net.

         ⠪  CREATE [PUBLIC] DATABASE LINK link
                         [CONNECT TO user IDENTIFIED BY password]
                         USING 'connect_string'

         祢   PUBLIC           ᫨  PUBLIC 饭,  ᮧ-
         ᫮                       筠    (㯭
         ࠬ                   ⮫쪮 짮⥫,   ᮧ-
                                     襣). ᫨     ᯥ-
                                     ஢,      ⠭
                                     㯭 ᥬ  짮⥫ 
                                     ᪫祭 ,  ᮧ -
                                        ⥬  .

                    link             , ஥   ᮧ
                                     裡.

                    user,password    ⢨⥫쭮   짮⥫
                                      ஫      ᯥ஢
                                     㤠  .

                    connect_string   ᯥ䨪 㤠  -
                                     , 㯭   १  SQL*Net.
                                     ଠ     ᯥ஢
                                     㤠    ਢ
                                      "㪮⢥  짮⥫  
                                     SQL*Net"   ⭮ -
                                      । .

         砭    ,      
          ᯮ- ⠡栬 㤠     ,
             뫠    ࠧ FROM  ⥬   䥪⮬,
                         אַ  祭    ।
                    짮⥫  㤠 .

                    ᫨ ࠧ, ᮤঠ  짮⥫  ஫,
                    , 㤥  ᯮ짮     ஫
                    ⥪饣 짮⥫.  ਬ,  ᫨ SCOTT -
                      ᮧ 裡    짮-



                                   -- 137 --



                    ⥫  ஫, ORACLE 㤥 ᯮ짮  -
                    㯠   㤠       짮⥫
                    SCOTT   ஫.

                     ⠡ ᯥ    
                    ⠡   裡  (@linkname)    ࠧ FROM
                     SELECT.
                    
                      ࠢ筨  SQL - ࠬ         5-51


















































                                   -- 138 --




                      ,  믮塞 १ -
                     , 稭 ᫥騬 ࠭祭:

                       * ⢮  痢,   
                         ᯮ짮   ,   設⢥
                         ⥬ ࠭稢 .   ।
                          , 㢥稢 祭 ࠬ
                         OPEN_LINKS 䠩 INIT.ORA.
                       * १        ࠭ ⮫
                         ⨯ LONG.
                       *   ॢ    (ᯮ ࠧ
                         CONNECT BY):

                           *  PRIOR    ਬ  
                             ࠧ, ⫨  CONNECT BY
                           * ࠧ START WITH   ᮤঠ -
                             ᮢ
                           *  ࠧ CONNECT BY,   START  WITH  
                              ᮤঠ 㭪樨 USERENV('ENTRYID'),
                             ROWNUM    ।    짮⥫
                             㭪樨.

         ਬ     ⮡ ।   BOSTON, 
                    뫠  짮⥫ SCOTT  ஫ TIGER 
                     D:BOSTON-MFG, :

                    CREATE DATABASE LINK BOSTON
                       CONNECT TO SCOTT IDENTIFIED BY TIGER
                       USING 'D:BOSTON-MFG'

                    ᫥ ᮧ   裡   -
                    訢 ⠡ 짮⥫ SCOTT ᫥騬 -
                    :

                    SELECT * FROM EMP@BOSTON

                    ஬ ⮣,    訢 ⠡  -
                     짮⥫.   ᫥騩 ਬ ।-
                    ,  SCOTT    ⠡ DEPT -
                    ⥫ ADAMS:

                    SELECT * FROM ADAMS@BOSTON

                    ।騩      㤥     ᮥ     
                    짮⥫ SCOTT  㤠   ⥬ -
                    訢 ⠡ DEPT 짮⥫ ADAMS.

                      䠪,   ⠡ EMP  짮⥫
                    SCOTT 室  㤠 ,  ᯮ-
                     ᨭ. 騩  ਢ 
                    ⮬,  쭥訥   ⠡ EMP 
                      㤠  :

                    CREATE SYNONYM EMP FOR SCOTT.EMP@BOSTON




                                   -- 139 --



         易   CREATE SYNONYM  SELECT.
         ⥬
                                                      
                  ࠢ筮 㪮⢮   SQL        5-52


E                            F

E         CREATE INDEX                                  F

         祭   ⠡      .
                     ᯥ稢  אַ   ப ⠡-
                    , ᭨ ⥬ ᠬ ६ 㯠.   -
                    ন    祭,  -
                    饣  㥬 ⮫.   饥 ६
                     ᯮ  ⠪    ᯥ祭 㭨-
                    쭮.

         ।뫪 室  楬 ⠡     
                     ਢ INDEX   ਢ DBA.

         ⠪  CREATE [UNIQUE] INDEX index ON
                     {table (column [ASC|DESC][,column [ASC|DESC]]...]
                     | CLUSTER cluster}
                     [INITRANS n] [MAXTRANS n]
                     [TABLESPACE tablespace]
                     [STORAGE storage]
                     [PCTFREE n]
                     [NOSORT]

         祢   UNIQUE           ࠭,   ⠡  -
         ᫮                          ப    묨
         ࠬ                   祭ﬨ      ஢
                                     ⮫. ᫨ 㭨 
                                      ᮧ,  ⠡  ᮤ-
                                      㡫騥 ப.

                    index             ᮧ .   
                                      㣨 ꥪ⮢  ,
                                         㭨-
                                      । ꥪ⮢  -
                                     ⥫.

                    table             饩   ⠡,  
                                     ன ᮧ .

                    column            ⮫ ⠡.

                    ASC              ⨬  ᮢ⨬  DB2,
                    DESC                砥  ᮧ-
                                       浪 ⠭.

                    CLUSTER           ,  ண 㤥
                    cluster          ᮧ  .

                    INITRANS         (.    ⮩  ᠭ
                    MAXTRANS         ࠬ஢ INITRANS  MAXTRANS 
                                      CREATE TABLE).

                                   -- 140 --



                    TABLESPACE        ⠡筮  ࠭⢠,  
                    tablespace       ஬ 㤥 ᮤঠ .

                    STORAGE storage  (.  ᠭ ࠧ STORAGE)
                                     
                      ࠢ筨  SQL - ࠬ         5-53





















































                                   -- 141 --




                    PCTFREE          ᯥ 業    ,
                                     饩 ᢮     
                                           ᫥
                                       ⠢.

                    NOSORT           㪠뢠 ⥬  ORACLE,   
                                        ⠡  ᯮ 
                                     饬 浪 , ᫥-
                                     ⥫쭮,   ஢  
                                     ப ஢  .

         砭  ORACLE  ᯮ짮  ᠬ    楫
          ᯮ- 㢥祭 ந⥫쭮  ᫥ :
         
                         *  㯥  ⠡栬   ⮫-
                           栬
                         *   ᪥ ப  祭ﬨ, 묨
                            ஢ ⮫殢.

                    ,   樨   ,
                    㤠   ப, ⠪   -
                     ⠪ 㦤  ᮯ஢.

         -        祭  ᨬ쭮 16
         㥬     ⮫殢.    ⮬ 砥 ।⠢-
         ⮫     ᮡ ⥭    ⮫殢.
                    ⮫   㪠뢠   浪  
                    冷    ᯮ짮 .

                     室 ORACLE   ᯮ짮 
                       砫 .  ⨬, 
                    IDX1 ᮧ  ⮫ A,B,C ⠡ TAB1 ( -
                    浪 A,B,C).  ORACLE  ᯮ짮  -
                      뫪  ⮫ A,B,C ( ),
                     ⮫ A,B   ⮫ C.  뫪  ⮫
                    B,C   ᯮ짮  IDX1.    -
                    筮    ᮧ  ⮫쪮  ⮫-
                    殢 B  C.

         ᪮쪮    ⠡  ᮧ    ࠧ-
         ᮢ    ⮫殢    (    ࠧ   権
          ⠡  ⮫殢).   ,     ᭨-
                     ந⥫쭮  䨪樨  .  
                    ⢮ ᮢ,     ᮧ  
                     ⠡, ࠭祭  뢠.

         ७     ⮫ ⮨ :
         ଠ
                 * ROWID ப (6 ⮢  㭨쭮  7
                          ⮢ -  㭨쭮 )
                        * ७    㥬 ⮫-
                          殢 (      ⮫  
                            ⥫쭮)
                           
                  ࠢ筮 㪮⢮   SQL        5-54



                                   -- 142 --




                    ਬ, 㭨    ⮫  ENAME
                    CHAR(10) ॡ 18 ⮢:

                        * 7 ⮢  ROWID
                        * 10 ⮢ -  ⮫
                        * 1 ⥫ 

                    ᨬ쭮 ⨬  ࠧ    
                      襩 樮  ⥬.  ᠭ
                    ७ ଠ  ࠧ筮 ⨯  -
                    ⥬ ORACLE     6 -  " 
                    ORACLE" 㬥 "ORACLE RDBMS 㪮⢮ -
                      ".

           㭨      㯯
             ⮫殢,  ࠭,   ⠡  㤥
                       ⨬ ⮫栬 ப.   ⠡-
                       ᮧ  ᫮ 㭨 -
                    ᮢ; 設⢮ ⠡       
                    㭨 :    ᭮  ⠡-
                    .

         襭    ᫥ ⠭⠬  -
          -   ⥬  ORACLE  ⫨  㣨 
               ꥪ⮢   짮⥫.    ਢ
                    ⥫ ४樨    -
                    ᮢ:

                          * 砩   ᮢ  ⠡ 
                            ⮫殢,    ᮧ.
                          * ।   㪢 "I".
                          * ࠧ  㭪樥    ⠡  
                            ⮫殢.

                    ਬ, ,  ᮧ   ⮫  SAL  
                    COMM ⠡ EMP  : I_EMP$SAL_COMM.

         ந-    ᮧ  ORACLE 㤥 ⠢
         ⥫쭮   PCTFREE   . ਬ,
                    ᫨ PCTFREE ࠢ 20,     㤥
                      80%  ᢮ .  襥 祭
                    PCTFREE    믮  襥 ⢮
                    ⠢ । ⥬,     ࠧ-
                      (  ॡ  ⥫ ६
                    ).  讥 祭 PCTFREE 室 
                     ⠡,   ⭮ ࠧ-
                      .  쪨   
                    ᮡ ᪮७  ᬮ.

                     砫쭮    ⠡  饬 砥
                    㤥 ॥,  ᫨  ᮧ ⠡, -
                     ,    ⥬  ᮧ .  ᫨  
                    ᮧ     ⠡,    㤥
                    ஢  ⠢  ப.
                  
                      ࠢ筨  SQL - ࠬ         5-55


                                   -- 143 --




         NOSORT      NOSORT  ⢥ ᭨ ६, -
                    室    ᮧ .   ଠ쭮
                    ᮧ  ᯥࢠ  ⠡    
                    㥬 ⮫栬,   㦥 ⥬ ந -
                    .   ஢ 筮  ⥫쭠
                     ६ ஥ .  ᫨  ப
                    ⠡ 㦥 ஢   浪  ⠭
                     㥬 ⮫栬,  NOSORT 
                    ⥬ ORACLE ய 䠧 ஢.

                     ᮧ      NOSORT  -
                    짮 .

                     NOSORT  㤥  ⠪  ᭨  饥 -
                    ⢮ ,  室  ஥ -
                    .   ஢  ORACLE  ᯮ ६
                    ᥣ   ஢ 믮   㤥,
                     ᮧ   ࠧ 襩 ६ -
                    .

                     ⮣, ⮡ ᯮ짮 樥 NOSORT, 
                     ࠭஢,  ப ⠡ ᯮ-
                      饬 浪.     䨧᪠
                    ᨬ   ᥬ ⥬ ࠢ-
                     ५樮묨   (ᮡ - -
                    ⥬  ORACLE),      ᯮᮡ 
                    ७ 浪 ᯮ ப  ⠡.
                    ⮬ ᮧ     樥 NOSORT 
                    믮 ࠧ  ᫥ 㧪  ⠡ -
                    ஢ .

                    ᪠ ஥    樥 NOSORT,  
                    祬  ,  ⠪  ᫨ 冷 ப  
                    ⠡  襭, ORACLE 뤠 訡
                      ᬮ  ᮧ   㦥  
                    樨 NOSORT.

         Nulls      祭 NULL     ,  ᫥⥫쭮
                    :

                    SELECT ENAME FROM EMP WHERE COMM IS NULL

                     㤥 ᯮ짮 , ᮧ  ⮫-
                     COMM.

                      ⠪  ᬠਢ 祭
                    NULL  㡫騥      ᪮쪨
                    ⠪ ப    ᮡ 訡.

             砫쭮  ᮧ  ORACLE ⮬-
         ᮢ   ᪨  ᮧ   ,   
             ᮧ,   ஬  믮 -
                    樨 DML.  ,    ஬  CREATE
                    INDEX   ᮧ   , -
                      ⠡,    . ⥫-
                    ,   ᯮ짮 ࠧ CLASTER ⮫쪮 ᫨
                      㪠  ⮬      CREATE  INDEX

                                   -- 144 --



                     ⠡.
                    
                  ࠢ筮 㪮⢮   SQL        5-56


                     ᮧ    EMPLOYEE :

                    CREATE INDEX IC_EMP ON CLUSTER EMPLOYEE

                    ,   ⮬  㪠뢠 ⮫, 
                     ᮧ ,  ⠪    
                    ⢥   ⮫  .

         ਬ     ஢ ⠡ EMP   ⮫  ENAME
                    室 :

                    CREATE INDEX I_EMP$ENAME ON EMP (ENAME)

                      ,    砥 ࠧ㬭
                     㭨 ,  ⠪  ᪮쪮 -
                        䠬.

                     ஢  ⠡  EMP  ⮫ EMPNO
                    :

                    CREATE UNIQUE INDEX I_EMP$EMPNO ON EMP (EMPNO)

                      砥  㭨쭮  ࠭-
                    ,    ⠡   ᥭ  㦠
                      ⠡ ஬.

         易   ALTER  INDEX,  DROP   INDEX,   VALIDATE
         ⥬       INDEX; ࠧ STORAGE.
             
                      ࠢ筨  SQL - ࠬ         5-57
























                                   -- 145 --




E                            F


E    CREATE ROLLBACK SEGMENT                                    F


    祭    ᥣ ⪠, 㪠  :

                 * ᬮ  ᮧ  ᥣ  ।  
                    (PUBLIC),   ⮫쪮 
                 ஢,          ᢮   䠩
                 INIT.ORA (private).
                 * ⠡筮 ࠭⢮, ᮤঠ饥 ᥣ
                 * ࠬ 

    ।뫪  ॡ ਢ DBA.

    ⠪    CREATE [PUBLIC] ROLLBACK SEGMENT rollback_segment
                   [ TABLESPACE tablespace ]
                   [ STORAGE storage ]

    祢 ᫮ PUBLIC         ᫨ 㪠,   ⮬,  -
     ࠬ                    ᥣ ⪠ 㤥 騬, ..
                                  㯭  ᯮ짮 
                                  ஬.᫨ 饭, ᥣ -
                                   .
                 rollback_segment ࠢ 䨪 ORACLE -
                                    30 ᨬ.
                 TABLESPASE        ⠡筮 ࠭⢮,  -
                 tablespace       ஬ 㤥 ᮧ ᥣ. 
                                  㬮砭 ਭ SYSTEM.
                 STORAGE storage  ᯥ ࠬ ।
                                    ᥣ ⪠. ࠢ
                                   .  ᠭ ࠧ STORAGE.

    砭    ᥣ    ⠡筮  ࠭
    ᯮ짮-  ᫥    ﭨ online.   ⠡-
               ࠭⢥   ᪮쪮 ᥣ⮢  -
                 .   饬  砥,  ᪮쪮  ᥣ⮢ ⪠
                 㢥稢 ந⥫쭮.  㦤 ⨬-
                  ᫠  ࠧ஢ ᥣ⮢ ⪠    
                 5 㬥  "ORACLE  RDBMS 㪮⢮  ன
                 ந⥫쭮".


    ਬ      ⮡ ᮧ  ᥣ ⪠  ⠡筮 ࠭-
                 ⢥ SYSTEM, :

                 CREATE PUBLIC ROLLBACK SEGMENT RBS_2
                 TABLESPACE SYSTEM
                 STORAGE (INITIAL 50000 INCREMENT 50000 MAXEXTENTS 10)

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

                                   -- 146 --




E                            F

E         CREATE SEQUENCE                               F

         祭  ꥪ  ,    ண
                     짮⥫  ஢ 㭨
                    楫 ᫠.  ᫥⥫     -
                    짮  ⮬᪮  樨 ᭮
                    祩.

         ।뫪 ॡ  ਢ  RESOURCE    - 
                     ⠡筮 ࠭⢥.  ᮧ ᫥-
                    ⥫쭮, ன 㤥  㣮 -
                    ⥫, 室 ਢ DBA.

         ⠪  CREATE SEQUENCE [user.]sequence
                        [INCREMENT BY n]
                        [START WITH n]
                        [MAXVALUE n | NOMAXVALUE]
                        [MINVALUE n | NOMINVALUE]
                        [CYCLE | NOCYCLE]
                                 
                        [CACHE n | NOCACHE]
                        [ORDER | NOORDER]

         祢   user                  ᫥⥫쭮.
         ᫮                      ࠧ㬥    짮⥫,
         ࠬ                   뤠訩  CREATE SEQUENCE

                    sequence          ᫥⥫쭮.   
                                     ᫥ ᮣ襭  ORACLE 
                                      ꥪ⮢  .

                    INCREMENT BY     । ࢠ  ᫥-
                                     ⥫묨 ࠬ. ᫨ -
                                      ⥫쭮  祭,   
                                     ᫥⥫쭮    뢠,
                                     ᫨ ⥫쭮 -  ᫥-
                                     ⥫쭮 . -
                                       楫 ᫮    ࠢ
                                     . 砭 1 ().

                    START WITH         㥬  ᫥-
                                     ⥫ .   㬮砭 
                                      ᫥⥫쭮⥩
                                     ࠢ MINVALUE,    뢠
                                     - MAXVALUE. ᯮ  -
                                     , ᫨  , ⮡ ᫥-
                                     ⥫쭮 稭  㬠-
                                     稢 祭.

                    MINVALUE         쭮 祭  ᫥-
                    NOMINVALUE       ⥫쭮  ,  ஥ 㤥
                                     ஢.   ࠧ -
                                       ࠭ ᫥-



                                   -- 147 --



                                     ⥫쭮.   -
                                     ⥫쭮⥩  ࠢ  1,  
                                     뢠 10e2 -1.

                    MAXVALUE         ᨬ쭮 祭 ᫥-
                    NOMAXVALUE       ⥫쭮  ,  ஥ 㤥
                                     ஢.   ࠧ -
                                        ࠭ ᫥-
                                     ⥫쭮.   뢠 -
                                     ⥫쭮⥩  ࠢ  1,  
                                      10e -1.
                              
                      ࠢ筨  SQL - ࠬ         5-59














































                                   -- 148 --




                    CYCLE             㬮砭      ।
                    NOCYCLE          NOCYCLE,  砥 襭
                                     ஢   ᫥⥫
                                     ஢  ⨦  -
                                     ⥫쭮.    ⪠
                                     ᣥ஢   ᫥ ⮣
                                     ਢ  訡.
                                     ᫨ ᯥ஢ CYCLE, -
                                     ⥫쭮 ᫥ ⨦
                                     MAXVALUE ( )  -
                                      MINVALUE.      뢠
                                     ᫥⥫쭮⥩ ᫥ 뤠
                                     MINVALUE 뤠 MAXVALUE.

                    CACHE            CACHE ࠭ ⮢ ᫥-
                    NOCACHE          ⥫ , ⠪   -
                                          ,
                                      १ 祣  -
                                     ⥫ ஢ 믮-
                                      ॥.

                                      㬮砭 । -
                                     祭 CACHE     20.    祭,
                                     㪠뢠   CACHE    
                                     ॢ ࠧ  MAXVALUE
                                      MINVALUE.

                    ORDER            ORDER ᯥ,   ࠭-
                    NOORDER               ᫥-
                                     ⥫ ஢  浪 -
                                     .     뢠       
                                     ਫ,    ᯮ
                                     ᫥⥫      -
                                     ⢥ ⮪ ६. 筮 
                                     , ᫨ ⠡ ᯮ
                                     ᫥⥫쭮  -
                                      ᭮ 祩.  ᫨
                                     ࠬ ORDER  㪠, ᫥-
                                     ⥫쭮 ᪮॥ ᥣ 㤥
                                     ஢ ⠪,   ᫨ 
                                       㪠.

         砭     ᯮ짮  ᫥⥫  
          ᯮ-  ⮬᪮ 樨  ᭮  祩  
             ,    ⠪ न஢   -
                    쪨 ⮫殢  ⠡.   ᮧ ᫥-
                    ⥫쭮    ਢ RESOURSE
                      -   ⠡筮  ࠭⢥,  
                     㯠  㦮 ᫥⥫쭮 室
                       ਢ SELECT.  DBA  -
                    ஢ ᫥⥫쭮  栬, ⫨-
                      ᮧ饣 짮⥫. ᫥⥫쭮
                        ⬥.   ॣ-
                     㯠  .  ஬ ⮣,  ᫥⥫-
                       ᨭ.
                    
                  ࠢ筮 㪮⢮   SQL        5-60

                                   -- 149 --




                    ⢨⥫ ᫥⥫   -
                     ⥬  ORACLE  ⮬᪨ ,  ᫥-
                    ⥫쭮,   㧪 ⮬,   砥,
                     ᫥⥫       
                    ஢ ਫ.  ਬ,   ॠ樨  
                    ஢ ਫ  ࠭  -
                    ஢ ⠡ ᫥⥫ ஢, 㢥-
                     ᫥⥫     ⥬ ᢮
                    ⠡.  ⠪ ॠ樨  ६ 
                     ᣥ஢  ⮫쪮    ᫥⥫
                    . ⨢,    ᫥⥫쭮⥩
                    ⥬ ORACLE ᪠ ࠫ쭮 ஢-
                     ᪮쪨 ᫥⥫ ஢, ࠭-
                      ⮬  㭨쭮.

                       ᫥⥫   -
                    ᨬ   ⠡,          
                    ᫥⥫쭮⥩     ᯮ짮  
                       ᪮쪨  ⠡.  ⨬,  ⮡
                    ᫥⥫    ய᪠. 
                    易  ⥬,   ࠭,   ᣥ஢-
                    , 뫠 ⪠祭 (roll back).   ⮣,  -
                     짮⥫    ,   ⮩
                     ᫥⥫쭮   㣨 짮-
                    ⥫.

                    ⮡ ᬮ,    ᫥⥫쭮 
                     ,      ᫮ 
                    USER_SEQUENCES  ALL_SEQUENCES.

                    ᫥⥫쭮  ᯮ஢    -
                    ஢ ࠢ  ⠡栬. ᯮ -
                     ᫥⥫ ,  ⠪  ᫥  -
                     砥 १,   ᯮ 
                      믮﫨.

         砭  砭   ᫥⥫쭮⥩ ஥஢
          ᫥- ⠪ ࠧ,   㪠  ࠧ  ᮧ-
         ⥫-    ᫥⥫쭮,  稭-
         ⥩       1,   1    孥-
                     ।.  ᫨      ᯥ஢   ⮫쪮
                    "INCREMENT BY -1", ᮧ 뢠 ᫥-
                    ⥫쭮 稭  -1     
                    ࠭祭. ࠧ,   ᠭ     
                    "祢 ᫮  ࠬ", ᪠ ࠧ
                    ਠ樨  ⠭ ᫥⥫쭮⥩.

                ᫥⥫쭮 :
         ᫥-
         ⥫-        *  ࠭筮
         ⥩         * ⠭  ࠭  ।
                        * 稭 ᭮ ᫥ ⨦ ࠭
                           

                    ⮡ ᮧ ᫥⥫쭮    ࠭  
                    ᯥ MAXVALUE   ᫥-
                    ⥫쭮⥩  MINVALUE -  뢠,   ⠪

                                   -- 150 --



                    ᯥ NOCYCLE.
                    
                      ࠢ筨  SQL - ࠬ         5-61


                    ⮡ ᮧ ᫥⥫쭮, ⠭-
                          ।  ,  ᯥ
                    MAXVALUE    ᫥⥫쭮⥩  
                    MINVALUE -  뢠,   ⠪ ᯥ
                    NOCYCLE.  ⪠  ᣥ஢  ᫥-
                    ⥫  ᫥ ⨦ ࠭祭 ਢ-
                      訡.

                    ⮡ ᮧ  ᫥⥫쭮,   稭
                    ᭮ ᫥ ⨦ ।,  ᯥ 
                    MAXVALUE  MINVALUE.  ⠪  CYCLE.

                    ࠧ START WITH  ⮫쪮   ᫥-
                    ⥫ .     砫 ᫥-
                    ⥫쭮 㤥 稭  MINVALUE   砥
                    ⠭  MAXVALUE -  砥 뢠.

         ᯮ짮-  ᫮ ᫥⥫  ஢,    
           ,  । 㬥⮬ ࠧ CACHE. -
                    㥬  ᫥⥫쭮  ᯥ稢  
                       ᫥⥫  ஢.  
                        ᫥⥫쭮 
                    ᫥ ࢮ     ⮩  ᫥-
                    ⥫쭮.   砥  ⥬  㥬
                    ᫥⥫ ,   ᯮ짮  -
                    ᠭ ࠭ (commited), . ⥭-
                    樠쭮 ᫮  ﭭ  ஢  ࠢ  ,
                    㪠  ࠧ CACHE.

                    ࠬ, 騥     ᫥-
                    ⥫쭮⥩,   䠩 INIT.ORA.  -
                       ਫ  D 㬥 "ORACLE RDBMS 㪮-
                    ⢮   ".

                     㬮砭 ।,      
                    ஢ 20 ᫥⥫ ஢, 
                     ⥬ ORACLE ࠭   ᪮-
                     㯠.

         NEXTVAL    ᥢ⮫ NEXTVAL  ᯮ  -
                     ।    㪠  ᫥-
                    ⥫쭮. 뫪        NEXTVAL   ਢ   
                    ஢ । .  饭 
                    ᫥騩 ᨭ⠪:

                    sequence.NEXTVAL

                     sequence -   ᫥⥫쭮.  ᫥-
                     ࠧ 㦤,    NEXTVAL  
                     ᯮ짮.
                    
                  ࠢ筮 㪮⢮   SQL        5-62


                                   -- 151 --




         CURRVAL    ᥢ⮫ CURRVAL -  ⥪饥 祭 -
                    ⥫쭮 . 뫪  NEXTVAL ਢ
                     ⮬,   ⥪饥 祭  頥 
                    CURRVAL.  ⥪饬 ᥠ NEXTVAL   -
                    짮    ࠧ  뫪   CURRVAL.
                    饭  CURRVAL  ᫥騩 ᨭ⠪:

                    sequense.CURRVAL

                     sequence -  ᫥⥫쭮,   
                    뫠 NEXTVAL.

          -.NEXTVAL  CURRVAL  ᯮ짮:
          ᯮ-
                 *  ࠧ SELECT   SELECT  (᪫
         NEXTVAL        )
         CURRVAL       *  ᯨ᪥ 祭  INSERT
                       *  ࠦ SET  UPDATE

         ࠭-  NEXTVAL  CURRVAL  ࠧ襭 ᯮ짮:
          
         NEXTVAL      *  
         CURRVAL       *  ᯨ᪠ select  
                       *  祢 ᫮ DISTINCT
                       *  ࠧ ORDER BY, GROUP BY  HAVING -
                          SELECT
                       *  ﬨ ꥤ  (UNION, INTERSECT,
                         MINUS)


         ਬ     ᫥ ᮧ ᫥⥫쭮  ᯮ짮-
                      ஢ ஢. ⨬  ᫥-
                     ਬ,   짮⥫  ELLY  ᮧ
                    ᫥⥫쭮   ESEQ:

                    CREATE SEQUENCE ESEQ INCREMENT BY 10

                    ࢮ 饭  ESEQ.NEXTVAL ୥ 1, ᫥-
                    饥 ୥ 11.   ᫥  뫪  㤥
                     祭  10 襥 ।饣.

                    NEXTVAL ᥣ  室 ।  -
                    ⥫쭮: ESEQ.NEXTVAL.    ᫥-
                    ⥫쭮 뫪  NEXTVAL   
                      믮  . ᪮쪮 -
                             
                     祭 ᫥⥫쭮 .
                    
                      ࠢ筨  SQL - ࠬ         5-63









                                   -- 152 --




                    NEXTVAL   ஥  DML - -
                    :

               INSERT INTO EMP VALUES (ESEQ.NEXTVAL,'LEWIS','TIMOTHY')

                    ( ।    EMP       ⮫:
                    EMPNO, LAST_NAME, FIRST_NAME).

                     樨  ᫥⥫쭮   -
                     ᨬ  ⮣,  ᠭ  ⪠祭
                    ࠭. ᫨    짮⥫  ६
                       ᫥⥫쭮,  
                     짮⥫    ஬⪨, ⠪
                      뢭 ᫥⥫쭮 ६
                       짮⥫.    
                    짮⥫   㤥    ᫥⥫
                    , ᣥ஢  㣮 짮⥫.

                    ᫥ 믮   ।饣    INSERT
                     㤥 믮 ᫥ :

                    INSERT INTO EMP_PROJ VALUES (ESEQ.CURRVAL, 101)

                    (।,  EMP_PROJ ᮤন   ⮫:
                    EMPNO  PROJNO).

                    ᯮ짮          NEXTVAL  
                    CURRVAL ਢ  ⮬,      -
                     ᯮ짮  NEXTVAL ᨬ  浪
                       .

                    ⮡ 㢨 ᫥ ,  ᣥ஢ 
                    襬 ᥠ,  믮 ᫥騩 :

                    SELECT ESEQ.CURRVAL FROM any_table

         ᫥-   ⨬,  ।⨥ ந ⥫.
         ⥫쭮-  ᯮ   ᫥⥫쭮⥩ 
           -  ᮧ  ਩  ஢ ᪠ ⥫-
               ஢.  砩 ࠭ ⥫  
         UPDATE      ⮬ 䠪    ஢-
                    ,  ⮬ ᯮ ਩  -
                    .   ᫨      ᯮ  ᫥⥫쭮
                    TV_SERIAL, ⠢ ப  ⠡ TV  䨪-
                     ⠡ TEST_RESULT 㤥 룫拉 ᫥騬
                    ࠧ:

                    INSERT INTO TV VALUES (TV_SERIAL.NEXTVAL, SYSDATE,
                                           '19 INCH')
                    UPDATE TEST_RESULT
                        SET TV_NUMBER=TV_SERIAL.CURRVAL
                        WHERE TEST_NUMBER = 1015

         易   ALTER SEQUENCE,  AUDIT,  DROP SEQUENCE,
         ⥬       GRANT, REVOKE.  "᭮ "   1 - "-



                                   -- 153 --



                    樮 "  㪮⢠.
                    
                  ࠢ筮 㪮⢮   SQL        5-64


E                            F

E         CREATE SYNONYM                                F


         祭  ᨭ  ⠡,  ஢, ᫥-
                    ⥫쭮⥩  ﬨ:

                       * ᮧ 饣 ᨭ (PUBLIC)
                       * ᮧ ᨭ, 뫠饣  㤠
                          

         ।뫪      楬   ਢ
                     SELECT ⠡  ,    ᮧ
                     ᨭ. ⮡  ᮧ  ᨭ ⨯ PUBLIC 
                     ᨭ  ꥪ, 楬 ண -
                      㣮 짮⥫,  室  ਢ-
                      DBA.

         ⠪   CREATE [PUBLIC] SYNONYM [user.]synonym
                         FOR [user.]table [@databaselink]

         祢    PUBLIC          ᯥ,  ᨭ 㤥
         ᫮                      㯥 ᥬ  짮⥫. 
         ࠬ                   㬮砭 ᨭ 㯥 ⮫-
                                       ᮧ⥫.

                    synonym           ᨭ, ᫥饥 ᮣ-
                                        ꥪ⮢.

                    user             饥      
                                     짮⥫. ᫨   饭,
                                      CREATE  SYNONYM ।-
                                     ,     ⠡
                                      .

                    table             饩 ⠡, 
                                      ᫥⥫쭮.

                    databaselink          㤠
                                      .  ᫨  user 饭,
                                     ᨭ 뫠      ⠡,
                                     ਭ饩 짮⥫, ᯥ-
                                     ஢      
                                     裡.


         砭  ᯮ짮 ᨭ த⮢ ᮮࠦ-
          ᯮ-  ᭮  㤮⢠,  ᫥騥:
         
                        *  饭  ⠡    㪠-
                            


                                   -- 154 --



                        *  饭  ⠡    㪠-
                            , ன  ਭ
                        *  祭 㣮  ⠡

                    㪠 ᢮⢠      ᯥ稢      
                    ᨬ , ⠪  ஧筮; ᨭ-
                      㭪樮஢ ਫ  -
                       ,  짮⥫ ਭ-
                     ⠡    ,    ⠪ -   
                      ࠭.
                    
                      ࠢ筨  SQL - ࠬ         5-65















































                                   -- 155 --




         ⥪    ᨭ   ,  ⫨筮  -
                    ⠫ ꥪ⮢  짮⥫. ORACLE -
                    ⠥ ࠧ  뫪    ꥪ⠬    ஢
                    짮⥫ । ꥪ⠬  ஢ "PUBLIC" -
                    ᨭ. ਬ, ᫨ 짮⥫ SCOTT -
                     ⠡楩   DEPT,   DBA ᮧ PUBLIC
                    ᨭ    DEPT  ꥪ BLAKE.DEPT, 
                    ᫨ SCOTT 믮 ᫥騩 :

                    SELECT * FROM DEPT

                     롥 ப   SCOTT.DEPT.    롮ન
                    ப   ⠡ 㣮 짮⥫
                    SCOTT  믮 ᫥騩 :

                    SELECT * FROM BLAKE.DEPT

                    ORACLE 㤥  ࠧ 뫪  ꥪ⠬
                    १ ᨭ,  ᫨  ᫥騥 ।-
                    :

                         * ⠡       ।  
                           짮⥫
                         *   ⠡       ᫥
                             裡

         ਬ    ।,   ।⠢   ⠡-
                     MARKET_RESEARCH 짮⥫ SCOTT   ᮧ-
                     ᨭ   MARKET,   뫠饣    ⠡
                    MARKET_RESEARCH, 室  믮  ᫥騩
                    :

                    CREATE SYNONYM MARKET
                        FOR SCOTT.MARKET_RESEARCH

                     ᮧ 饣 ᨭ ⠡ EMP  짮-
                    ⥫ SCOTT, 室饩   㤠  -
                     SALES, DBA  믮 ᫥饥:

                    CREATE PUBLIC SYNONYM EMP
                        FOR SCOTT.EMP@SALES

                    ,   ᫨  ⠡  ਭ 㣮
                    짮⥫,  ᮧ ᨭ  ⥬  -
                    ,   ⠡.

         易   CREATE  DATABASE  LINK,  CREATE  TABLE,
         ⥬       CREATE VIEW.
                    
                  ࠢ筮 㪮⢮   SQL        5-66








                                   -- 156 --




E                            F

E         CREATE TABLE                                  F


         祭     ࠭   
                     - ⠡  ᫥騬 ﬨ:

                    * । ࠪ⨪ । 
                    * ᯥ஢ ⠡筮 ࠭⢮  ⠡-
                      
                    * ਧ ⠡
                    * 㧨  ⠡  १⠬  ந쭮
                      

         ।뫪 ॡ ਢ RESOURCE.  ᮧ ⠡-
                    , 楬 ன   , -
                     ਢ DBA.

         ⠪  CREATE TABLE [user.]table
                      ( {column_element | table_constraint}
                     [, {column_element | table_constraint} ]...)
                     [PCTFREE n] [PCTUSED n]
                     [INITRANS n] [MAXTRANS n]
                     [TABLESPACE tablespace]
                     [STORAGE storage]
                     [CLUSTER cluster (column [,column]...)]
                     [AS query]

         祢   user              ⠡.  ᫨   㪠-
         ᫮                      ,   짮⥫,  뤠
         ࠬ                    CREATE TABLE, ⠭
                                     楬 ⠡.

                    table             ⠡, ᫥饥 ⠭-
                                       ᮣ襭    
                                     ꥪ⮢   ( ࠢ,
                                      ⠡,  ,  ,
                                     ,  裡, ᨭ-
                                       ᫥⥫쭮 
                                            짮⥫
                                     ࠧ ).

                    column_element   । ⮫     易-
                                     ⥫ ࠭祭  ⮫殢
                                     ⠡.   ,   ⠡
                                      ᮤঠ    ⮫.
                                     . ⠪   ࠧ  "
                                     ᠭ  ⮫".

                    table_constraint ।  ࠭祭  ⠡-
                                     . .  ⠪      ࠧ
                                     "ࠧ CONSTRAINT".

                    tablespace       । ⠡筮  ࠭-
                                     ⢮,  ஬ 㤥  ਭ-
                                      ⠡.

                                   -- 157 --



                    storage          । 饥 ।
                                        ⠡.  .   
                                     ࠧ "ࠧ STORAGE".

                    cluster           騩    ,
                                     楬 ண     
                                     짮⥫.
                                     
                      ࠢ筨  SQL - ࠬ         5-67


















































                                   -- 158 --




                    query            ࠢ  ( ।-
                                        ࠧ "SELECT").  ᫨
                                      ᯥ query,   -
                                          CREATE  TABLE
                                     । ⮫쪮    ⮫-
                                     殢.    ࠧ -
                                        ᮮ⢥  ⮫-
                                     殢, ᯥ஢  .
                                     ᫨  ⮫   
                                     ࠢ쭮 ।     㭨-
                                      , (column...) 
                                      饭.   ⮬ 砥 -
                                      ᯮ짮   ⮫-
                                     殢     .    ᫨    
                                     (column...) ᯥ஢, -
                                     ⢮ ⮫殢   ᮢ-
                                        ⢮  ⮫殢  
                                     . ᫥ ⮣,  ⠡
                                     㤥 ᮧ, १ -
                                       ᥭ   ⠡.
                                          ᮤঠ ࠧ
                                     ORDER BY  UPDATE.
                                     ᫨ ᯮ  , ࠧ
                                     column_element    ᮤঠ
                                     ⮫쪮  ⮫殢.   -
                                        ࠧ୮  
                                     ந묨  १⮢ -
                                     . ࠭祭    ⠡ 
                                     ⮫殢     
                                      ࠧ    ADD   
                                     ALTER TABLE.  䨪樨 -
                                     ࠭祭  ⮫殢  ⠡
                                     室  ⬥    -
                                       (DROP  ADD).
                    騥  ࠧ    㪠   
                    浪,     ᫥.
                    PCTFREE          १ࢨ  饩  
                                        ⠡  ᫥-
                                     騥 䨪樨       .
                                     PCTFREE ᯥ   -
                                     ⥫쭮 ᫮  0   100 
                                     㪠뢠,   業 
                                      㤥  १ࢨ஢ 
                                     䨪樨. PCTFREE   ࠢ  0
                                       ⮬,     
                                       . 祭 0
                                     ᯮ, ⠪   ⮬  -
                                     砥   ⠡   㤥
                                      .     㬮砭
                                     । 10 業⮢. -
                                        㬠稢  -
                                     , ⠢塞   ப  
                                      ᨬ쭮 90% -
                                      .
                                     PCTFREE   ⠪    
                                      CREATE   CLUSTER    
                                     CREATE INDEX.  PCTFREE

                                   -- 159 --



                                      PCTUSED ।,  㤥  
                                     ⠢塞 ப   
                                     騩   -  .
                                     
                  ࠢ筮 㪮⢮   SQL        5-68


                                      ࠭⢮, १-
                                     ஢      PCTFREE,
                                     ࠧ襭 ᯮ짮   -
                                     ७ ப       .
                                      ⮣,  ⮡  
                                      ⥫  ,  -
                                      ப   -
                                      ᢮     .
                                       ⮩  ᢮  
                                     ⠭  筮, ப
                                       㣨 , -
                                     ࠧ 楯  ப.  楯-
                                      ப ᭨ ந-
                                     ⥫쭮, ⠪   㯠 
                                     ⮩  ଠ樨 室 -
                                        ࠡ. PCTFREE
                                     ᫥ 롨 ᫥  ⥫-
                                      業,   ᪮쪮   ᨫ쭮
                                     ப      -
                                      䨪樨     .
                                     誮 쪮 祭  -
                                          楯   ப,
                                     ᫨誮 讥 -   䥪⨢-
                                      ᯮ짮 .
                    PCTUSED          ᯥ    -
                                      ᯮ짮 , -
                                      ORACLE 㤥  ন 
                                       ⠡.  ⮫쪮
                                     業 ᯮ㥬  -
                                       PCTUSED,   ⠭-
                                      ⮬       ⠢
                                     ப. PCTUSED   ᯥ
                                      ⥫쭮 ᫮   0 
                                     100.  㬮砭 ।
                                     40.
                                      ப  ⠢ 
                                     ,      ᯮ㥬
                                      ,    祬   祭
                                     PCTUSED.  ⠭  -
                                     ⮬   ⠢  ப  -
                                       ᯨ᮪ ᢮ ,
                                      業  ᯮ짮 -
                                          ⠭   
                                     㪠  PCTUSED.  㤠-
                                       ᯨ᪠ ᢮, ᫨
                                      १ ⠢ ப -
                                     業 ᯮ짮  ⠭-
                                       PCTUSED.  ,  
                                          㤠,
                                      ࠡ  PCTUSED 40  -
                                     . 襥   祭   PCTUSED

                                   -- 160 --



                                     ਢ      䥪⨢
                                     ᯮ짮   
                                      ᭨   ந⥫쭮
                                        室 㦨
                                     ᯨ᪠ ᢮ .
                                       CREATE CLUSTER ࠧ
                                     PCTUSE  ⠪  祭.
                                     
                      ࠢ筨  SQL - ࠬ         5-69

                    MAXTRANS         ᯥ     ᨬ쭮
                                     ᫮ ࠭権,   
                                     ࠫ쭮    -
                                     .  ࠭,  -
                                      ,   ॡ   
                                     ࠭樨 (     SELECT).
                                     , 室     
                                     ࠭樨,    -
                                      ⥬   筮 ࠢ 23
                                     ⠬, ।騬   ᢮-
                                         .  MAXTRANS
                                        1  255   
                                     㬮砭 ।  255. 
                                     ⮬ 砥  ଠ쭮 -
                                     稭 ᢮    ᪮॥
                                     ॢ ,    ௠
                                     MAXTRANS.
                                       MAXTRANS  ࠥ 
                                      ஫,    ⠡.
                    INITRANS         । 砫쭮 -
                                      ⮢ ࠭権, -
                                     塞     .
                                         ࠭権
                                     ORACLE १ࢨ 23    
                                     ᢮     ( 
                                       樮 ⥬)
                                      ,    -
                                     . INITRANS    -
                                         1  255 
                                      㬮砭  ।  1.
                                      ᫮ ࠭権, -
                                        ⮬      ॢ
                                     INITRANS, ORACLE 㤥 ।-
                                      ⥫     
                                     ࠭権     ௠
                                          ॢ
                                     祭 MAXTRANS.  INITRANS -
                                     ࠭, ⠪ ࠧ,  
                                     砫쭮 ᯮ짮   
                                     ⠪ ⢮ ࠭権  -
                                      ஢   -
                                     . ଥ  ⮣,    ࠬ
                                     ।頥  室,
                                     易  ᪨ -
                                      ⮢ ࠭権.
                                     INITRAN ࠥ     ஫  
                                     ஢  ᮢ,   ᪫-
                                     祭 ⮣,   ⠬ 㬠稢-

                                   -- 161 --



                                      祭   INITRANS=2.
                    TABLESPACE         ⠡筮 ࠭-
                                     ⢠,  ஬ 㤥 ᮧ
                                     ⠡. ᫨   㪠, 
                                     ।  㤥 믮
                                      㬠稢 ⠡筮  -
                                     ࠭⢥,    ஬
                                     ALTER USER.
                    STORAGE          ࠢ  । 
                                      ⠡.   -
                                     । ⠪,  ⮡ -
                                     ஢  । -
                                      ⪮.  .    ࠧ
                                     "ࠧ STORAGE".
                                     
                  ࠢ筮 㪮⢮   SQL        5-70


                    CLUSTER          ᯥ,  ⠡ -
                                       祭    .  
                                     ⮬  砥  columns  - ⮫
                                     ⠡, ᮮ⢥騥 -
                                     ਧ㥬  ⮫栬  .  
                                     饬 砥   ⮫,  -
                                     ⠢騥  ᭮   -
                                     騥  .  -
                                         ⮫ ⠡
                                       ⮫   -
                                     .  ⮫   ᮮ⢥-
                                     ⢮  樨,    -
                                     .        ⠡   㤥
                                     짮 ।  -
                                        , ᫥⥫쭮,
                                      ⠭  樥 cluster -
                                     ࠬ     PCTUSED,    PCTFREE,
                                     TABLESPACE  STORAGE    -
                                      訡.


         砭     ᮤঠ    254  ⮫殢.  
          ᯮ-  ᮮ⢥⢮ ⠭ ᮣ襭 
              ꥪ⮢  .

                     ᪫祭 ࠬ query, ⠡ ᮧ
                     .  筮  ப   
                     INSERT.

          ᮮ⢥⢨ PCTUSED  PCTFREE.

                     ᯮ짮  PCTUSED  PCTFREE  -
                       ᯮ짮    ⠡  -
                    䥪⨢. 㬬  稭      
                    100.

                     ⠢  ப ORACLE   । ⠢-
                       ,  ᮤঠ騥  ᯨ᪥ ᢮
                    .  ⨬  ⭮  ,  
                     ᮤঠ  ᥡ ப. ORACLE 㤥 த

                                   -- 162 --



                    ࠡ       ᯨ᪠  ᢮  
                       ॢ PCTFREE.    
                    ᯨ᪠ ᢮    ௠,   ࠭
                    ப  । .


E                                          F

                     CREATE  TABLE  ⠢ ORACLE -
                     ଠ  ⠡,  ⮫  ࠭-
                       .     -
                      ⠡  ⮬᪨  ਢ  
                    ⮡ࠦ   ᫮ .

                    騥  ᫮  ᮤঠ ଠ-
                    , ⭮  ⠡栬:

                        * USER_TABLES
                        * USER_OBJECTS
                        * USER_TAB_COLUMNS
                        * USER_INDEXES
                        * ALL_TABLES
         
                      ࠢ筨  SQL - ࠬ         5-71



































                                   -- 163 --





E         ࠭祭                           F

                    ORACLE Version 6.0 ন ᨭ⠪ ࠭-
                    祭   ⠡  ⮫殢.  ᪠ -
                    প 㤥 ⢫  ᫥ .

         ਬ

                    ⮡ ।      ਬ   ⠡    EMP
                    짮⥫ SCOTT, :

                    CREATE TABLE EMP
                       (EMPNO NUMBER NOT NULL PRIMARY KEY
                        ENAME CHAR(10) NOT NULL
                                     CHECK (ENAME = UPPER(ENAME)),
                        JOB CHAR(9),
                        MGR NUMBER REFERENCES SCOTT.EMP(EMPNO),
                        HIREDATE DATE CHECK (HIREDATE >= SYSDATE),
                        SAL NUMBER(10,2) CHECK (SAL > 500),
                        COMM NUMBER(9,0) DEFAULT NULL,
                        DEPTNO NUMBER(2) NOT NULL REFERENCES
                                              SCOTT.DEPT(DEPTNO)
                     PCTFREE 5 PCTUSED 75

                     ⮬   ।   ⠡    ᯥ
                    PCTFREE 5  PCTUSED 75,  ᮮ⢥ ⭮-
                    ⥫쭮 筮 ⠡. । ⠪ -
                    쪮 ࠭祭  ⮫殢 ⠡ EMP.

                     ਬ SALGRADE, 室  ⠡筮
                    ࠭⢥ HUMAN_RESOURCE,   让 
                        ⥫ । ᮧ
                    ᫥騬 ࠧ:

                    CREATE TABLE SALGRADE (GRADE NUMBER,
                                           LOSAL NUMBER,
                                           HISAL NUMBER)
                      TABLESPACE HUMAN_RESOURCE
                      STORAGE (INITIAL 6144 NEXT 6144
                               MINEXTENTS 1 MAXEXTENTS 5
                               PCTINCREASE 5)

         易   ALTER  TABLE,  CREATE  CLUSTER,  CREATE
         ⥬       INDEX, CREATE   TABLESPACE,   DROP  TABLE;  ࠧ
                    CONSTRAINT  STORAGE;  ᠭ ⮫.
             
                  ࠢ筮 㪮⢮   SQL        5-72










                                   -- 164 --




E                            F

E    CREATE TABLESPACE                                  F


    祭    ⠡筮 ࠭⢮, 稢:

                 *  䠩, ⠢  ࠭⢮
                 * 㬠稢 ࠬ 
                 * 㤥  ⠡筮 ࠭⢮ ᫥ ᮧ 
                   online  offline.

    ।뫪  ॡ ਢ DBA

    ⠪    CREATE TABLESPACE tablespace
                     DATAFILE {filespec [,filespec] ...}
                      [DEFAULT STORAGE storage]
                      [ONLINE | OFFLINE]

    祢 ᫮ tablespace     䨪 ORACLE   30
     ࠬ                   ᨬ ᯮ  
                                   ⠡筮 ࠭.
                 filespec         ᯥ䨪 䠩   
                                  ଥ:
                                  filename [SIZE integer [K|M][REUSE]
                 SIZE integer     㪠뢠 ⢮ ⮢. -
                                  ⢨ ⮣ ࠬ । 
                                  㬮砭 ᯮ짮 䠩 
                                  ࠧ஬ 10 ⮢  ୠ -
                                  樨 -  500 ⮢. ᫨
                                  㪠 "", integer 㬭 
                                  1024, "" -  1048576.

                 REUSE            㪠뢠,   ୮ ᯮ-
                                   騥 䠩, ଠ-
                                      ࠧ蠥.
                 DATAFILE         । 䠩  䠩, ⠢-
                                  騥 ⠡筮 ࠭⢮. 筠
                                  ᯥ䨪 䠩   -
                                  樮 ⥬.
                 DEFAULT          㬠稢 ࠬ  
                 STORAGE           ꥪ⮢,  ᮧ   ⮬
                                  ࠭⢥. 稢 祭
                                     樮 -
                                  ⥬.  ᠭ ࠬ஢ -
                                   .  ࠧ STORAGE.
                 ONLINE            ⠡筮 ࠭⢮ ᮮ⢥-
                 OFFLINE          ⢥ 㯭  㯭.
                                  砭 - ONLINE - 砥, 
                                  ⠡筮 ࠭⢮   ⠭
                                  㯭 짮⥫, 騬 -
                                     ,  .  
                                  ᫮  DBA_TABLESPACES -
                                  뢠   ⠡筮 -
                                  ࠭⢠.
                                            
                      ࠢ筨  SQL - ࠬ         5-73

                                   -- 165 --





    砭  筮 ࠭⢮  楩 ஢
    ᯮ짮-  /⠭   .   ᮤন ᥣ
               ⠡,  ᮢ,  ᥣ ⪠  ६.
                      ࠩ   ⠡筮
                 ࠭⢮ SYSTEM, ஥ ᮧ   ᮧ-
                   .  ᨬ  ⮣,   -
                 ﭨ 室 ⠡筮 ࠭⢮,   -
                   䠩 樮 ⥬. 筮,
                       㤠  ᨬ    ﭨ
                 (online  offline),     ४ ।
                 ⬥ 뢮 ⠡筮 ࠭⢮    offline.
                 ⨬  ࠭,     襭-
                  ࠭権   ⠡筮 ࠭⢥.

                  ⠡筮 ࠭⢮,   ᮧ 
                   ࠩ  -  ᥣ ⪠.


      SIZE  REUSE

                 ᫨   ᯥ஢    祢  ᫮  SIZE 
                 REUSE, ORACLE 㤥 ᯮ짮 㪠 䠩 
                 ,  ᫨     㪠 ࠧ.
                  ⨢ 砥 ⥬ ORACLE ᮧ 䠩  㪠-
                  ࠧ.

                 ᫨   ᯥ஢  SIZE    ⨫ 祢
                 ᫮ REUSE,  䠩   ⢮   -
                 .   ⮬  砥  ORACLE ᮧ 䠩 㪠
                 ࠧ.

                 ᫨ 饭 ࠬ SIZE, 䠩   㦥 -
                  ⢮.


    ਬ       ⮡ ᮧ ⠡筮 ࠭⢮, 饥 
                  䠩, :

                 CREATE TABLESPACE TABSPACE_2
                 DATAFILE 'TABSPACE_FILE2.DAT' SIZE 20M
                 DEFAULT STORAGE (
                    INITAL 10K NEXT 50K
                    MINEXTENTS 1 MAXEXTENTS 999
                    PCTINCREASE 10
                 )
                    ONLINE


      易      ALTER TABLESPACE  DROP TABLESPACE.
      ⥬
                 
                  ࠢ筮 㪮⢮   SQL        5-74





                                   -- 166 --




E                            F

E         CREATE VIEW                                   F


         祭 ।  (᪮ ⠡,  -
                    饩    ᪮쪨 ⠡).

         ।뫪   楬   ਢ SELECT
                     ⠡()  .  ⮡ ᮧ  ,
                    楬 ண    ,  室
                     ਢ DBA.

         ⠪  CREATE VIEW [user.]view [(alias [,alias]...)]
                         AS query
                         [WITH CHECK OPTION [CONSTRAINT constraint]]

         祢   user              ᮧ .
         ᫮ 
         ࠬ  view              

                    query            । ⮫  ப ⠡-
                                     (),   㤥 -
                                      .  ⢥ query
                                      㯠  ࠢ
                                      SELECT,    ᮤঠ騩
                                     ࠧ ORDER BY  FOR UPDATE. -
                                     ᠭ ᨭ⠪   .  
                                      6   -   "ࠧ   
                                     SELECT"  㪮⢠.

                    WITH CHECK OPTION ᯥ,    ⠢  
                                     䨪樨, 믮塞   १
                                      , 饭, ᫨ -
                                      ப     
                                     १   .  WITH   CHECK
                                     OPTION   ᯮ짮    
                                     , 饬    㣮
                                     .

                    CONSTRAINT       , 砥     ࠭祭
                                     "WITH CHECK  OPTION".  ᫨  
                                       㪠,   砥
                                     ⥬  ଥ:  SYS_Cn,  n
                                     - 楫,  饥  ࠭-
                                      㭨  । -
                                     .
                                     
                      ࠢ筨  SQL - ࠬ         5-75









                                   -- 167 --




         砭   -  ᪮ ""       
          ᯮ- ⠡.   ᫥騬 ᢮⢠:
         
                         *     ࠭ ,   ⮣ 
                           뢠   㣨 ⠡  (-
                           ,  ᢮ ।,   ࠬ).
                         *   ᯮ짮  SQL -  -
                            ,    ᯮ짮 ⠡
                           (.. -       INSERT,   UPDATE,
                           DELETE, SELECT)  ᪮쪨 ࠭祭-
                           .     ⮫쪮  롨  (select)
                             ஢,   ᮤঠ:
                                * ꥤ
                                * ࠧ  GROUP  BY,  CONNECT  BY  
                                  START WITH
                                * ࠧ DISTINCT, ᥢ⮫ (-
                                  ਬ ROWNUM)  ࠦ  ᯨ-
                                   ⮫殢

                      ஢ , ᮤঠ騥 ᥢ-
                    ⮫  ࠦ,  ᫨  UPDATE 
                    뫠  ᥢ⮫  ࠦ  .

                      ᯮ짮 :
                         * ᯥ祭 ⥫쭮  ஢ 
                           ⠡, ࠭稢   ࠭ ।-
                            ப / ⮫栬  ⠡-
                           .
                         * 뢠 ⢨⥫쭮 ᫮ .
                           ਬ,     ᯮ짮 
                            ⢨  ⠡楩,   -
                                ନ஢ १ -
                            ᪮쪮 ⠡.
                         *  ।⠢ .  ਬ, -
                             ᯮᮡ  २  ⮫殢
                            ॠ쭮  ﭨ   । -
                           쭮 ⠡.
                         * ᯥ稢 ஢ ⭮⥫쭮 楫-
                           .

E         ࠢ  ஢                    F

                     ⮫殢      ᮣᮢ뢠
                     ᫥騬 ࠢ:
                         *  ⮫殢  ᫥  ⠭-
                            ᮣ襭    ꥪ⮢  
                           (⠫쭮 ᬮ७    3 "
                           몠 SQL").
                         *  ⮫殢  ,  ᫨  -
                            ந묨  ࠦ  
                         *   ⮫殢       ࠧ묨
                           (⠡, ⠢騥     
                             ⮫殢).
                         * ᫨   ᯮ,   ⮫-
                           殢       ந묨  
                           ⮫殢 .    ⮫殢  
                           ⠪ ᯮ짮  ᯨ᪥ select  -

                                   -- 168 --



                           .
                           
                  ࠢ筮 㪮⢮   SQL        5-76


                         *  ᯮ ,    ⢮
                            ᮢ    ᫮  롨ࠥ  
                            ⮫殢.  ਬ,  ᫨  
                           롨ࠥ 4  ⮫,  ᮢ   
                           ⠪ 4.  ࢮ  ⮫    㤥
                           祭   ,  ஬ ⮫ -
                           ன   ⠪ .

                      頥  , ORACLE த뢠
                    ᫥騥 蠣:

                    1. 롨ࠥ  ।    㪠뢠 
                        CREATE VIEW      ᫮-
                        .
                    2. ஢  ਢ 㯠.
                    3. ᯮ     롮ન 
                        ⠡()  .   - 
                       ⠡, ᯥ஢   .

                      ,  ᫨  ।  
                    "SELECT * FROM table",    﫨 ।
                    ⠡,  ࠡ ⠭.   ஡
                      㤠   ᮧ  -
                    .


          㯠  ࠬ

                    稢묨 ﬨ ॣ樨    -
                     ꥤ 権 ॣ樨  ⠡,
                    ⠢ .

         ਬ    ⮡ ᮧ  DEPT20   ᭮  ⠡
                    EMP, 뢠騩 㦠 ⤥ 20   
                    ,  ᫥騩 :

                    CREATE VIEW DEPT20
                       AS SELECT ENAME, SAL*12 ANNUAL_SALARY
                           FROM EMP
                           WHERE DEPTNO = 20

                    ,     ⮫   -
                    室, ⠪  ࠦ SAL*12  㦥  
                     ⮫  (ANNUAL_SALARY).

                    ⮡ ᮧ    ROSTER   㦠 
                    ⠡ EMP,    뢠 ⮫쪮  -
                     , 䠬  title ( 砥  -
                     ࠭祭 WITH CHECK OPTION     WCO),
                    :




                                   -- 169 --



                    CREATE VIEW ROSTER (ID_NUMBER, PERSON, TITLE)
                       AS SELECT EMPNO, ENAME, JOB, DEPTNO
                           FROM EMP
                           WHERE DEPTNO IN (SELECT DISTINCT DEPTNO
                                                   FROM DEPT)
                       WITH CHECK OPTION CONSTRAINT WCO
                    
                      ࠢ筨  SQL - ࠬ         5-77


                     ⮬,     㪠  ࠭祭  WITH
                    CHECK OPTION,  ORACLE 㤥 ।  
                     ப   ROSTER, ᫨  ⤥ -
                     㤭    ⠡ DEPT. -
                     ,    ப ⢨⥫쭮 㤥
                    ⠢  ⠡ EMP ⮫쪮  ᫥  ᯥ譮
                    ஢ન WITH CHECK OPTION.

         易   CREATE TABLE, CREATE SYNONYM, DROP VIEW
         ⥬        RENAME.
                    






































                                   -- 170 --
