Docstoc

plsql 2

Document Sample
plsql 2 Powered By Docstoc
					                     ‫א‬‫א‬‫א‬
                ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹]
                      ‫א‬‫א‬‫א‬‫א‬




    ‫א‬
‫א‬

    ٢٦٢
                                            ٢٦٢                             ‫א‬‫א‬
                                             ‫א‬                          ‫א‬



                                              
                                                                                                      
       W،،‫א‬‫א‬،‫א‬
                                                                                                      
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
  ‫א‬ ‫א‬  ،‫א‬    ‫א‬ ‫א‬ ‫א‬ ‫א‬ ‫א‬ 
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
 ‫؛‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                 
                                                              K‫א‬‫א‬‫א‬
                                                                                                      
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
  ‫א‬       ،‫א‬ ‫א‬‫א‬  
‫א‬  ‫א‬ ‫א‬ ‫א‬ ‫א‬ ‫א‬   ‫א‬    ،  
‫א‬،‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
     ‫א‬  ‫א‬   ،‫א‬   
                                     K‫א‬‫א‬،‫א‬‫א‬
                                                                                                      
‫א‬?‫א‬??‫א‬?‫א‬‫א‬
                             K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                      
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
،‫א‬،‫א‬‫א‬‫א‬‫א‬ 
                           K‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                      
       ‫א‬ ‫א‬  ‫א‬    ‫א‬
                                                                                                K‫א‬
                                                                                                      
               ‫א‬‫א‬‫א‬‫א‬                                          
                                             ٢٦٢                            ‫א‬‫א‬
                                             ‫א‬                         ‫א‬



                                              

 PL/SQL‫א‬‫א‬‫א‬‫א‬ 
DML‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                    KPL/SQL‫א‬‫א‬‫א‬
 ‫א‬ PL/SQL‫א‬ ‫א‬                      
                        W‫א‬‫א‬‫א‬،PL/SQL‫א‬‫א‬‫א‬
     ‫א‬   ،‫א‬ ‫א‬ W‫א‬ ‫א‬
،‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬
          K‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬ ‫א‬‫א‬    ‫א‬   ،‫א‬ ‫א‬‫א‬ W‫א‬ ‫א‬
،‫א‬‫א‬‫א‬،‫א‬
for‫א‬‫א‬ ‫א‬‫א‬‫א‬K‫א‬ ‫א‬‫א‬
                                                 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
W‫א‬‫א‬ ‫א‬،‫א‬‫א‬W‫א‬ ‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
        ‫א‬    K‫א‬  ‫א‬
           K‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬
‫א‬F‫א‬‫א‬،‫א‬‫א‬W‫א‬‫א‬‫א‬
 ‫א‬‫א‬   ،‫א‬ ‫א‬ ‫א‬    ‫א‬   E‫א‬‫א‬ 
                                                                                   K‫א‬‫א‬
 ‫א‬‫א‬،‫א‬ ‫א‬،‫א‬W‫א‬ ‫א‬
‫א‬  ‫א‬‫א‬‫א‬      ، ‫א‬ ‫א‬‫א‬  
                  K‫א‬‫א‬‫א‬،‫א‬
‫א‬    ‫א‬ ‫א‬‫א‬     ،‫א‬‫א‬ W‫א‬ ‫א‬
            K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬،
                                              ٢٦٢                           ‫א‬‫א‬
                                              ‫א‬                        ‫א‬



  ،‫א‬ ‫א‬    ‫א‬    ،‫א‬ ‫א‬  W‫א‬ ‫א‬
 ‫א‬،‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
  ‫א‬ ‫א‬ ‫א‬   ‫א‬  ،‫א‬ ‫א‬ ‫א‬‫א‬  ، 
                                                                K‫א‬‫א‬‫א‬
                                      ‫א‬‫א‬‫א‬
                                   ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹]
                                       ‫א‬‫א‬‫א‬‫א‬




                 ‫א‬
                  ‫א‬‫א‬
‫א‬‫א‬




١
       ‫א‬‫א‬                             ٢٦٢                           ‫א‬‫א‬
      ‫א‬‫א‬                      ‫א‬                        ‫א‬



                                                                                       W‫א‬‫א‬
                                                                         
،‫א‬   ‫א‬ ‫א‬‫א‬ ‫א‬‫א‬  ‫א‬   ‫א‬ ‫א‬  
 
‫א‬   ‫א‬‫א‬‫א‬ ‫א‬ ‫א‬   ‫א‬
                                              ‫א‬‫א‬
                                                                                        W‫א‬‫א‬

                                                      W‫א‬،‫א‬
                                                                     ‫א‬   .١

        ‫א‬Rolls K٢
 ‫א‬‫א‬‫א‬Revoke‫א‬Grant‫א‬‫א‬‫ א‬K٣
                                                                                                   
                                                                            W‫א‬‫א‬‫א‬
                                                      ٪١٠٠‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                   
                                                             ٥W‫א‬‫א‬
                                                                                                   
                                                                                 W‫א‬‫א‬
                                                                             •
                                                                Projector‫א‬ •
                                                                                         •
                                                                                            •




                                               -١-
        ‫א‬‫א‬                            ٢٦٢                            ‫א‬‫א‬
       ‫א‬‫א‬                     ‫א‬                         ‫א‬



                                                                     ‫א‬‫א‬W‫א‬‫א‬
                                                        ‫א‬‫א‬‫א‬
 ‫א‬ Multi-user Environment‫א‬ 
 W‫א‬‫א‬K‫א‬‫א‬‫א‬
                                                          K System Security‫א‬ K١
 System  ‫א‬   ‫א‬‫א‬ ‫א‬  ‫א‬  ‫א‬  
 ، ‫א‬ ‫א‬  ،‫א‬  ‫א‬  ‫א‬  Level
                                              K‫א‬‫א‬‫א‬
                                                                                          
                                                                Data Security‫א‬ K٢
 ‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬
                                                  K‫א‬‫א‬
                                                                                                  
                                                                           ‫א‬‫א‬
 Database administrator‫א‬،SQL‫א‬‫א‬
 ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
 ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬K
   K‫א‬  ‫א‬ ‫א‬     ‫א‬ 
 ‫א‬F ‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                 KE‫א‬‫א‬‫א‬‫א‬
 ‫א‬ ،Tables ‫א‬  ‫א‬  ‫א‬    ‫א‬  WSchema ‫א‬
 ،  SchemaKSequences‫א‬،Views ‫א‬
                                                                               K‫א‬‫א‬




                                               -٢-
       ‫א‬‫א‬                                   ٢٦٢                           ‫א‬‫א‬
      ‫א‬‫א‬                            ‫א‬                        ‫א‬



                                                                      .System Privileges ‫א‬‫א‬‫א‬
‫א‬‫א‬،Roles ‫א‬ ‫א‬ ٨٠
‫א‬‫א‬‫א‬KDatabase Administrator‫א‬‫א‬
                                                                                                W‫א‬
                                                                                                       
                        ‫א‬‫א‬‫א‬                                   ‫א‬
      ،                          CREATE USER
                                                    KTablespace
                                    K                  DROP USER
‫א‬   ‫א‬   W                    ALTER USER
                        K‫א‬‫א‬،‫א‬
                            Schema                CREATE ANY TABLE
                                                        DROP ANY TABLE
               ‫א‬‫א‬               CREATE SEQUENCE
                                                        ‫א‬        DROP SEGUENCE
                           ‫א‬‫א‬               CREATE TRIGGER
                                                        ‫א‬          DROP TRIGGER
                 ‫א‬،W‫א‬           ALTER TRIGGER
                                      EF                 CREATE VIEW
                                                         DROP ANY VIEW
                                                                  CREATE ROLE
                                 ‫א‬          ALTER ANY ROLE
                                ‫א‬             DROP ANY ROLE
                                  ‫א‬           GRANT ANY ROLE
                ‫א‬‫א‬‫א‬              CREATE SYNONYM
           ‫א‬‫א‬‫א‬ DROP ANY SYSNONYM


                                                     -٣-
       ‫א‬‫א‬                             ٢٦٢                           ‫א‬‫א‬
      ‫א‬‫א‬                      ‫א‬                        ‫א‬



                                                                      ‫א‬
،CREATE USER‫א‬
K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
 ‫א‬   ‫א‬  K ‫א‬‫א‬    ‫א‬ 
                                                           K‫א‬‫א‬
CREATE USER user_name
IDENTIFIED BY password;
                                                                                        W
                               ‫א‬‫א‬‫א‬‫א‬           User_name          
     ‫א‬‫א‬‫א‬‫א‬                 Password         
                                                                                                W
SQL> CREATE USER scott
   2 IDENTIFIED BY tiger;
User Created.
                                                                                                  
‫א‬‫א‬‫א‬  DBA‫א‬‫א‬ 
                                                                        W‫א‬‫א‬‫א‬
                                                                                                  
GRANT privilege [, privilege …]
TO user_name [, user_name, …];
                                                                                        W
                                                  ‫א‬‫א‬‫א‬‫א‬         Privilege
                                            ‫א‬‫א‬‫א‬‫א‬         User_name
                                                                                                  
      W‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                  CREATE SESSION‫א‬‫א‬‫• א‬
                                                       CREATE TABLE  •
                                               CREATE SEQUENCE                   •
                                                          CREATE VIEW  •
                                              CREATE PROCEDURE‫א‬ •

                                               -٤-
        ‫א‬‫א‬                              ٢٦٢                            ‫א‬‫א‬
      ‫א‬‫א‬                        ‫א‬                         ‫א‬



                                                                                               W
SQL> GRANT create session, create table, create sequence, create view
  2 TO scott;
Grant succeeded.

 ‫ﻣﺎ هﻲ اﻟﻮﻇﻴﻔﺔ‬Role
،   ‫א‬  ‫א‬    ‫א‬ ‫א‬‫א‬    ‫א‬
K ‫א‬‫א‬    ‫א‬   ‫א‬  K   
K‫א‬‫א‬
‫א‬     ‫א‬‫א‬   ، ‫א‬  DBA ‫א‬  
                                                                                         K
                                                                                                    
CREATE ROLE role_name;
، Tables ‫א‬ manager‫א‬   ‫א‬‫א‬
 K‫א‬ CLARK‫א‬ BLAKE‫א‬ ،views
                                           K‫א‬‫א‬‫א‬‫א‬
                                                                                                    
SQL> CREATE ROLE manager;
Role Created.
                                                                                                    
SQL> GRANT create table, create view
   2 TO manager;
Grant succeeded.

SQL> GRANT manger to CLARK;
Grant succeeded.
                                                                                                    
                                                                                 ‫א‬
    ،   DBA ‫א‬    
‫א‬     ‫א‬   ‫א‬  K‫א‬   
         KALTER USER‫א‬،‫א‬‫א‬‫א‬‫א‬
ALTER USER user_name IDENTIFIED BY password;
                                                                                                    

                                                 -٥-
       ‫א‬‫א‬                               ٢٦٢                            ‫א‬‫א‬
      ‫א‬‫א‬                        ‫א‬                         ‫א‬



                                                                                           W
                                                          K‫א‬‫א‬        User_name
                                                  K‫א‬‫א‬           Password
                                                                                                    
‫א‬ ‫א‬‫א‬    ، ‫א‬    ‫א‬ ‫א‬   ‫א‬ 
                                                                                       K
                                                                                                    
                                                                   Object Privileges ‫א‬‫א‬‫א‬
     ،View،Table‫א‬‫א‬‫א‬
 ‫א‬‫א‬K‫א‬‫א‬‫א‬KProcedureSequence
                                   K‫א‬‫א‬‫א‬‫א‬
                                                                                                    
     Procedure Sequence View                       Table      ‫א‬‫א‬
                         √                                           √                ALTER
                                      √                              √              DELETE
           √                                                                       EXECUTE
                                                                     √                INDEX
                                                    √                √               INSERT
                                                                     √          REFERENCE
                                 √                  √                √              SELECT
                                                    √                √              UPDATE
                                                                                                    
                                                                ‫א‬‫א‬‫א‬
‫א‬‫א‬K‫א‬‫א‬،‫א‬‫א‬
‫א‬K‫א‬‫א‬ ‫א‬ ‫א‬
with grant    ‫א‬  ‫א‬ ‫א‬ K        
‫א‬K‫א‬‫א‬‫א‬‫א‬‫א‬option
                  K‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                    

                                                 -٦-
      ‫א‬‫א‬                           ٢٦٢                          ‫א‬‫א‬
     ‫א‬‫א‬                    ‫א‬                       ‫א‬



GRANT object_privilege ([columns])
ON     object
TO     {user | role | PUBLIC}
[WITH GRANT OPTION]
                                                                                      W
                                  ‫א‬‫א‬‫א‬‫א‬            Object_privilege
  ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬                      Columns
                           ‫א‬‫א‬‫א‬‫א‬                     ON object
                           ‫א‬‫א‬‫א‬                            TO
                            ‫א‬‫א‬‫א‬                        PUBLIC
  ‫א‬‫א‬‫א‬ WITH GRANT OPTION
              Krichsue‫א‬EMP‫א‬‫א‬‫א‬‫א‬W
                                                                                             
SQL> GRANT select
      ON emp
      TO sue, rich;
Grant succeeded.
               W‫א‬EMP‫א‬‫א‬richsue‫א‬‫א‬‫א‬
SQL> SELECT *
  2 FROM scott.emp;
       W‫א‬‫א‬‫א‬richsue‫א‬
SQL> CREATE SYNONYM emp FOR scott.emp;
SQL> SELECT * FROM emp;

‫א‬‫א‬‫א‬WITH GRANT OPTION‫א‬‫א‬‫א‬
                                                        K‫א‬
SQL> GRANT select, insert
  2 ON dept
  3 TO scott
  4 WITH GRANT OPTION;
Grant succeeded.

                                            -٧-
      ‫א‬‫א‬                              ٢٦٢                       ‫א‬‫א‬
     ‫א‬‫א‬                       ‫א‬                    ‫א‬



،dept‫א‬‫א‬scott‫א‬‫א‬‫א‬
                                                                  K‫א‬‫א‬

                                                 K‫א‬‫א‬‫א‬‫א‬
     ‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬،Data Dictionary‫א‬‫א‬‫א‬
                                                                 K‫א‬‫א‬
                                                                                             
                                                 ‫א‬               ‫א‬
                                                                          Data Dictionary Table
                            ‫א‬‫א‬‫א‬‫א‬           ROLE_SYS_PRIVS
                           ‫א‬‫א‬‫א‬‫א‬           ROLE_TAB_PRIVS
                  ‫א‬‫א‬‫א‬‫א‬‫א‬            USER_ROLE_PRIVS
               ‫א‬‫א‬‫א‬‫א‬‫ א‬USER_TAB_PRIVS_MADE
                        ‫א‬‫א‬‫א‬‫ א‬USER_TAB_PRIVS_RECD
     ‫א‬‫א‬‫א‬‫א‬‫א‬‫ א‬USER_COL_PRIVS_MADE
       ‫א‬‫א‬‫א‬‫ א‬USER_COL_PRIVS_RECD

    ،‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬W
                                     K‫א‬‫א‬‫א‬
                                                                                             




                                               -٨-
     ‫א‬‫א‬                          ٢٦٢                      ‫א‬‫א‬
    ‫א‬‫א‬                   ‫א‬                   ‫א‬



                                                                   K‫א‬‫א‬‫א‬

 ‫א‬‫א‬،REVOKE‫א‬‫א‬‫א‬‫א‬
   ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬،‫א‬‫א‬
            K‫א‬WITH GRANT OPTION‫א‬‫א‬

REVOKE {privilege [,privilege . . .] | ALL }
ON     Object
FROM    {user [, user . . .] | role | PUBLIC}
[CASCADE CONSTRAINTS];
                                                                                       
                                                                                 W
        Referential Integrity        CASCADE
                                                             CONSTRAINTS
       References‫א‬‫א‬ Constraint
                                                                                       
              KEMP‫א‬‫א‬Scott‫א‬‫א‬W
SQL> REVOKE select
  2 ON emp
  3 FROM scott;
Revoke succeeded.
                                                                               




                                          -٩-
      ‫א‬‫א‬                             ٢٦٢                       ‫א‬‫א‬
     ‫א‬‫א‬                      ‫א‬                    ‫א‬



                                                                                       
   ‫א‬‫؟‬‫א‬‫א‬‫א‬‫א‬ E١
                                                       ‫؟‬‫א‬
               _________________________________________________
                                                                         
                              ‫؟‬‫א‬‫א‬‫א‬ E٢
               _________________________________________________
                                                                                   
                                                                 
    ‫؟‬‫א‬‫א‬‫א‬‫א‬‫א‬،‫א‬ E٣
           _________________________________________________
                                                                                   
 ‫א‬‫א‬‫א‬،DBA‫א‬‫א‬ E٤
                 ‫؟‬‫א‬‫א‬‫א‬،‫א‬‫א‬‫א‬
               _________________________________________________
                                                                                            
                              ‫؟‬‫א‬‫א‬‫א‬‫א‬ E٥
               _________________________________________________
                                                                                  
‫א‬DEPT‫א‬‫א‬ALI‫א‬،SALEM‫א‬ E٦
                                                                                 K
                                                                                            
                                                                                            
 KSALEM‫א‬DEPT‫א‬‫א‬ALI‫א‬ E٧
                                                                                            
                                                                                            
 KSALEM‫א‬DEPT‫א‬‫א‬ALI‫א‬ E٨
                                                                                            
                                                                                            
                     K‫א‬‫א‬‫א‬‫א‬DEPT‫א‬‫א‬‫א‬ E٩
                                             - ١٠ -
       ‫א‬‫א‬                      ٢٦٢                    ‫א‬‫א‬
      ‫א‬‫א‬               ‫א‬                 ‫א‬



                                                                                   
                                                                                   
                                                                                   
 Data Dictionary‫א‬USER_TABLES‫א‬ E١٠
                                                          K‫א‬‫א‬
                                                                                   
                                                                                   
                                                                                   
                                                                                   
       KALI‫א‬DEPT‫א‬‫א‬‫א‬،SALEM‫א‬E١١




                                       - ١١ -
                                         ‫א‬‫א‬‫א‬
                                    ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹]
                                          ‫א‬‫א‬‫א‬‫א‬




                    ‫א‬
                    ‫א‬‫א‬‫א‬
‫א‬‫א‬




٢
      ‫א‬‫א‬                         ٢٦٢                               ‫א‬‫א‬
    ‫א‬‫א‬‫א‬                     ‫א‬                            ‫א‬



                                                                                          W‫א‬‫א‬

                                                              
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                ‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                         W‫א‬‫א‬

                                                 W‫א‬،‫א‬
                                                    ‫א‬‫א‬ K١
                                         ‫א‬‫א‬‫א‬ K٢
                                                     ‫א‬‫א‬‫א‬‫ א‬K٣
                                             ‫א‬‫א‬‫א‬‫א‬‫א‬‫ א‬K٤
                                                                                                  
                                                                        W‫א‬‫א‬‫א‬
                                                  ٪١٠٠‫א‬‫א‬‫א‬‫א‬
                                                                                                  
                                                              ٤W‫א‬‫א‬
                                                                                                  
                                                                                  W‫א‬‫א‬
                                                                              •
                                                              Projector‫א‬ •
                                                                                          •
                                                                                           •




                                           - ١٢ -
      ‫א‬‫א‬                           ٢٦٢                          ‫א‬‫א‬
     ‫א‬‫א‬‫א‬                      ‫א‬                       ‫א‬



                                                                                 ‫א‬‫א‬
                                                       Explicit Cursors‫א‬‫א‬‫א‬
 ‫א‬ Select ‫א‬       ‫א‬ ‫א‬‫א‬ ‫א‬
‫א‬  ‫א‬‫א‬K‫א‬ ،
 ‫א‬ ‫א‬  ‫א‬ ‫א‬ ‫א‬     Active set ‫א‬ 
،‫א‬‫א‬ ‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬
                                    K‫א‬‫א‬‫א‬K‫א‬‫א‬

                                       Active Set

                         7369        SMITH          CLERK
          Cursor         7566        JONES        MANAGER        Current Row
                         7788        SCOTT        ANALYST                                      
                         7876       ADAMS           CLERK
                         7902         FORD        ANALYST                                      
                                                                                               
                                                                                               
K‫א‬‫א‬‫א‬‫א‬،‫א‬PL/SQL
                                  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                               




                                             - ١٣ -
      ‫א‬‫א‬                           ٢٦٢                               ‫א‬‫א‬
     ‫א‬‫א‬‫א‬                      ‫א‬                            ‫א‬



                                                                ‫א‬‫א‬‫א‬
                                     W‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                       
                                                                                                       
                                                                       NO                              

                                                                        YES
                                                                                                       
                                                               EMPTY
         DECLARE             OPEN            FETCH                               CLOSE
                                                                                                       
                                                                                                       
              ‫أﻧﺸﺊ‬           ‫ﺣﺪد‬              ‫ﺣﻤﻞ‬              ‫اﻓﺤﺺ‬                ‫أاﻟﻎ‬
           ‫ﻣﻮﻗﻌﺎ ﻓﻲ‬
              ً          ‫اﻟﻤﺠﻤﻮﻋﺔ‬            ‫اﻟﺴﻄﺮ‬             ‫ﻟﺘﺤﺪﻳﺪ‬          ‫اﻟﻤﺠﻤﻮﻋﺔ‬            
            ‫اﻟﺬاﻟﻜﺮة‬       ‫اﻟﻨﺸﻄﺔ‬         ‫اﻟﺤﺈﻟﻰ ﻓﻲ‬           ‫اﻷﺳﻄﺮ‬             .‫اﻟﻨﺸﻄﺔ‬                
              ً
              ‫ﺧﺎﺻﺎ‬                        ‫اﻟﻤﺘﻐﻴﺮات‬       ‫اﻟﻤﺘﻮﻓﺮة ﻓﻲ‬
                                                                                                       
              ‫ﺑﺠﻤﻠﺔ‬                                        ‫اﻟﻤﺠﻤﻮﻋﻪ‬
                                                             ،‫اﻟﻨﺸﻄﺔ‬                                   
                                                                                                       
 W‫א‬‫א‬‫א‬‫א‬‫א‬
                          
‫א‬ ‫א‬ ‫א‬WDeclare‫א‬  K١
                                                                  K‫א‬‫א‬
  ،‫א‬  ‫א‬   ‫א‬ ‫א‬  WOpen ‫א‬   K٢
                                      K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬WFetch‫א‬‫א‬ K٣
‫א‬K‫א‬‫א‬‫א‬‫א‬  
‫א‬ ‫א‬،K‫א‬‫א‬ ‫א‬‫א‬
‫א‬  ‫א‬،‫א‬‫א‬
                                                                            K‫א‬
،‫א‬‫א‬   ‫א‬ ‫א‬  ‫א‬ ‫א‬  WClose ‫א‬   K٤
                                               K‫א‬
    ،‫א‬‫א‬        ‫א‬ 
                                                                                        K‫א‬

                                             - ١٤ -
     ‫א‬‫א‬                       ٢٦٢                    ‫א‬‫א‬
    ‫א‬‫א‬‫א‬                  ‫א‬                 ‫א‬



                                                                   ‫א‬
    ‫א‬‫א‬‫א‬Cursor
                                       KPL/SQL‫א‬‫א‬    •

 INTO Select‫א‬         •

                                                              KFETCH
       CURSOR cursor_name IS
              Select_statement

                K‫א‬‫א‬‫א‬‫א‬ORDER BY‫א‬‫א‬‫א‬
                                                                               W
       DECLARE
        CURSOR emp_cursor IS
            SELECT empno, ename
            FROM emp;

       CURSOR dept_cursor IS
          SELECT *
          FROM dept
          WHERE deptno = 10;
       BEGIN
        ……




                                        - ١٥ -
      ‫א‬‫א‬                           ٢٦٢                          ‫א‬‫א‬
     ‫א‬‫א‬‫א‬                      ‫א‬                       ‫א‬



                                                                               ‫א‬
‫א‬ ‫א‬‫א‬‫א‬  ‫א‬ ‫א‬‫א‬
‫א‬  ‫א‬ ‫א‬     ،‫א‬    ‫א‬ ‫א‬ 
                                                                                       K‫א‬
‫א‬   PL/SQL       ‫א‬     W
                                    KFetch‫א‬،Exception
        OPEN Cursor_name;
                                                                                               
                                                                   W‫א‬‫א‬
K‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬ Fetch 
                                          K‫א‬‫א‬‫א‬‫א‬
        FETCH cursor_name             INTO [variable1, variable2, …] |
        record_name];
                                              W‫א‬Fetch‫א‬‫א‬
                                                                     
 ‫א‬‫א‬  Fetch ‫א‬ INTO‫א‬‫א‬ ‫א‬‫ א‬K١
                                K‫א‬‫א‬‫א‬Select‫א‬
                 KFETCH INTO‫א‬‫א‬Record K٢
                  K،‫א‬‫ א‬K٣
                                                           WFetch‫א‬
                                              ‫א‬‫א‬‫א‬‫א‬ K١
                                K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K٢
  K‫א‬‫א‬‫א‬‫א‬FOR loop‫א‬‫א‬‫ א‬K٣




                                             - ١٦ -
       ‫א‬‫א‬                              ٢٦٢                           ‫א‬‫א‬
     ‫א‬‫א‬‫א‬                          ‫א‬                         ‫א‬



                                                                                               W
        FETCH emp_cursor INTO v_empno, v_ename;
                                                                                                    
                                                                          W‫א‬‫א‬
‫א‬،‫א‬‫א‬‫א‬‫א‬ ‫א‬ ‫א‬ 
K‫א‬ ‫א‬K‫א‬‫א‬‫א‬‫א‬
 ‫א‬ ‫א‬‫א‬
                                                                        KINVALID_CURSOR
 ‫א‬   ‫א‬   ‫א‬ PL/SQL     ‫א‬ 
 ‫א‬                ‫א‬
     K‫א‬‫א‬E‫א‬‫א‬F‫א‬‫א‬
،‫א‬‫א‬‫א‬‫א‬W
 ‫א‬ ‫א‬  OPEN_CURSORS‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                               K٥٠
                                                                                                    
        …
         FOR I IN 1—10 LOOP
           FETCH emp_cursor INTO v_empno, v_ename;
           …
         END LOOP
         CLOSE emp_cursor;
        END;
                                                                                                    




                                                 - ١٧ -
      ‫א‬‫א‬                              ٢٦٢                      ‫א‬‫א‬
     ‫א‬‫א‬‫א‬                         ‫א‬                   ‫א‬



                                                                    ‫א‬‫א‬
        ،‫א‬ ‫א‬    
 K‫א‬‫א‬‫א‬K‫א‬
                            K‫א‬‫א‬‫א‬W
                                                                                              
                      ‫א‬             ‫א‬‫א‬            ‫א‬
       ‫א‬‫א‬
                                           True/False             %ISOPEN
                  K
         ‫א‬
                                           True/False             %NOTFOUND
          ‫א‬
             ‫א‬
                                           True/False             %FOUND
          ‫א‬
               ‫א‬‫א‬
                                             Number               %ROWCOUNT
         ‫א‬‫א‬
                                                                                              
                                                                          %ISOPEN
‫א‬  ‫א‬‫א‬  K ‫א‬    ‫א‬  
 TRUE ‫א‬ K ‫א‬  ‫א‬،‫א‬،%ISOPEN
                       K‫א‬‫א‬‫א‬K‫א‬
                                                                                         W
        IF NOT emp_cursor%ISOPEN THEN
                OPEN emp_cursor;
        END IF;
        LOOP
           FETCH emp_cursor; …

                                            K%ROWCOUNT%NOTFOUND
         K‫א‬‫א‬‫א‬%ROWCOUNT‫א‬‫א‬‫• א‬
                      K‫א‬‫א‬‫א‬%NOTFOUND‫א‬‫א‬‫• א‬
                                                - ١٨ -
       ‫א‬‫א‬                           ٢٦٢                         ‫א‬‫א‬
     ‫א‬‫א‬‫א‬                       ‫א‬                      ‫א‬



                                                                                          W
       DECLARE
         v_empno emp.empno%TYPE;
          v_ename emp.ename%TYPE;
          CURSOR emp_cursor IS
               SELECT empno, ename
                FROM emp;
           BEGIN
           OPEN emp_cursor;
           LOOP
               FETCH emp_cursor INTO v_empno, v_ename;
               EXIT WHEN emp_cursor%ROWCOUNT > 10 OR
                                Emp_cursor%NOTFOUND;
               …
           END LOOP;
           CLOSE emp_cursor;
       END;
‫א‬   NULL %NOTFOUND،
‫א‬   WHEN  ‫א‬     ‫א‬‫א‬     
                                W‫א‬‫א‬‫א‬،TRUE
                                                                                               
        EXIT WHEN emp_curor%NOTFOUND
               OR emp_cursor%NOTFOUND IS NULL;
                                                                                               
                                                 Cursors and Records‫א‬‫א‬‫א‬
 K   ‫א‬         ‫א‬  ‫א‬ 
‫א‬‫א‬‫א‬K‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬ ‫א‬‫א‬‫א‬ ‫א‬،‫א‬‫א‬
                                                                                      K‫א‬
                                                                                               
K‫א‬ PL/SQL ‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬
                  K‫א‬‫א‬‫א‬‫א‬
                                              - ١٩ -
      ‫א‬‫א‬                           ٢٦٢                         ‫א‬‫א‬
     ‫א‬‫א‬‫א‬                      ‫א‬                      ‫א‬



                                                                                              
        DECLARE CURSOR emp_cursor IS
            SELECT empno, ename
            FROM emp;
         Emp_record emp_cursor%ROWTYPE;
         BEGIN
           OPEN emp_cursor;
           LOOP
              FETCH emp_cursor INTO emp_record;
              EXIT WHEN emp_cursor%NOTFOUND;
              INSERT INTO temp_list(empid,empname)
              VALUES (emp_record.empno, emp_record.ename);
           END LOOP;
           COMMIT;
           CLOSE emp_cursor;
         END;

                                                                       KFOR‫א‬
  ،‫א‬‫א‬ ‫א‬‫א‬  ‫א‬ ‫א‬  FOR ‫א‬‫א‬   ‫א‬ ‫א‬‫א‬ 
‫א‬   KFOR ‫א‬‫א‬      ‫א‬  ‫א‬  ،
‫א‬  ‫א‬         ‫א‬‫א‬  ‫א‬
‫א‬ K‫א‬‫א‬ ‫א‬ ،‫א‬
                                                                                           W‫א‬
                                                                                              
        SET SERVEROUTPUT ON
        DECLARE
           CURSOR emp_cursor IS
                 SELECT ename, deptno
                 FROM emp;
        BEGIN
          FOR emp_record IN emp_cursor LOOP
                    -- EF
            IF emp_record.deptno = 30 THEN
                DBMS_OUTPUT.PUT_LINE (‘Employee ‘ ||
                                             - ٢٠ -
      ‫א‬‫א‬                          ٢٦٢                        ‫א‬‫א‬
     ‫א‬‫א‬‫א‬                     ‫א‬                     ‫א‬



                           emp_record.ename || ‘ works in the Sales Dept. ‘);
             END IF;
          END LOOP;--
        END; /

                                           ‫א‬‫א‬‫א‬FOR‫א‬‫א‬‫א‬
‫א‬ ‫א‬     ،‫א‬ ‫א‬ ‫א‬ ‫א‬  ‫א‬ 
‫א‬K‫א‬‫א‬ ‫א‬ ‫א‬ ‫א‬،‫א‬‫א‬
        W‫א‬‫א‬‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬

        SET SERVEROUTPUT ON
        BEGIN
          FOR emp_record IN (SELECT ename, deptno
                              FROM emp) LOOP
                    -- EF
            IF emp_record.deptno = 30 THEN
                DBMS_OUTPUT.PUT_LINE (‘Employee ‘ ||
                       emp_record.ename || ‘ works in the Sales Dept. ‘);
             END IF;
          END LOOP;--
        END; /
                                                                                            




                                            - ٢١ -
      ‫א‬‫א‬                           ٢٦٢                       ‫א‬‫א‬
    ‫א‬‫א‬‫א‬                       ‫א‬                    ‫א‬



                                                                              W
                     K‫א‬‫א‬‫א‬‫א‬‫א‬E١
       SQL> CREATE TABLE top_dogs
            (name             VARCHAR2(25),
             salary            NUMBER(11,2) );

                                   K‫א‬PL/SQLE٢
  Ksubstitution variables‫א‬‫א‬‫א‬‫א‬‫א‬n‫ א‬K١
   KEMP‫א‬‫א‬n‫א‬‫א‬‫א‬‫א‬،‫א‬‫א‬ K٢
                                               KTop_dogs‫א‬‫א‬‫א‬ K٣
                                         K‫א‬‫א‬‫א‬‫א‬‫ א‬K٤


       Please enter the number of top money makers: 5
       NAME                   SALARY
       KING                       5000
       FORD                       3000
       SCOTT                      3000
       JONES                      2975
       BLAKE                      2850
                                                                                            
‫א‬ ‫א‬،‫א‬‫א‬‫א‬‫א‬E٣
                                                                        W،‫א‬
                            KKing, Ford, Scott٢‫א‬‫א‬‫א‬ K١
                 KKing, Ford, Scott and Jones٣‫א‬‫א‬‫א‬ K٢
                       K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K٣




                                             - ٢٢ -
  ‫א‬‫א‬                     ٢٦٢          ‫א‬‫א‬
 ‫א‬‫א‬‫א‬                ‫א‬       ‫א‬



                                                                     
    Please enter the number of top money makers: 2
    NAME                   SALARY
    KING                        5000
    FORD                        3000
    SCOTT                       3000

    Please enter the number of top money makers: 3
    NAME                   SALARY
    KING                        5000
    FORD                        3000
    SCOTT                       3000
    JONES                      2975




                                   - ٢٣ -
                                        ‫א‬‫א‬‫א‬
                                   ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹]
                                         ‫א‬‫א‬‫א‬‫א‬




                   ‫א‬
                   ‫א‬‫א‬
‫א‬‫א‬




٣
      ‫א‬‫א‬                             ٢٦٢                           ‫א‬‫א‬
    ‫א‬‫א‬                       ‫א‬                        ‫א‬



                                                                                          W‫א‬‫א‬

                                                                    
K‫א‬‫א‬‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬‫א‬
                       K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                         W‫א‬‫א‬

                                                     W‫א‬،‫א‬
                                                 ‫א‬‫א‬‫א‬‫א‬ K١
                                              ‫א‬‫א‬‫א‬‫ א‬K٢
                                                    ‫א‬‫א‬‫ א‬K٣
                               ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K٤
                                                                                                  
                                                                            W‫א‬‫א‬‫א‬
                                                      ٪١٠٠‫א‬‫א‬‫א‬
                                                                                                  
                                                              ٤W‫א‬‫א‬
                                                                                                  
                                                                                  W‫א‬‫א‬
                                                                              •
                                                                 Projector‫א‬ •
                                                                                          •
                                                                                           •




                                               - ٢٣ -
        ‫א‬‫א‬                           ٢٦٢                         ‫א‬‫א‬
      ‫א‬‫א‬                     ‫א‬                      ‫א‬



                                                                                 ‫א‬‫א‬
                                                                            ‫א‬‫א‬
                                                                                        W
     ‫א‬ ‫א‬   ‫א‬  ،PL/SQL    ‫א‬
 ‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬‫א‬
                           K‫א‬‫א‬
                                                                                                
                                                                   ‫؟‬
                                                    W،‫א‬
‫א‬K‫א‬‫א‬‫א‬،‫א‬ K١
 ‫א‬ ORA-01403
           KNO-DATA-FOUND‫א‬PL/SQL‫א‬
 ‫א‬ RAISE‫א‬‫א‬‫א‬ K٢
                                   
  ‫א‬ ‫א‬‫א‬،‫א‬‫א‬
                                                                  K‫א‬
 ‫א‬‫א‬  ‫א‬‫א‬  ‫א‬ ‫א‬‫א‬‫א‬‫א‬
 ‫א‬  ‫א‬ ‫א‬ ‫א‬،‫א‬‫א‬
 K‫א‬‫א‬،(Enclosing Block)‫א‬‫א‬‫א‬‫א‬
 ‫א‬ ‫א‬‫א‬‫א‬ 
  K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬Failure
                                                                                                




                                               - ٢٤ -
   ‫א‬‫א‬                               ٢٦٢                                ‫א‬‫א‬
 ‫א‬‫א‬                         ‫א‬                             ‫א‬



                                                                                                      


             ‫א‬‫א‬            ‫א‬‫א‬ ‫א‬‫א‬


                               DECLARE                            DECLARE


                            BEGIN                             BEGIN                    
      ‫א‬                                                                       ‫א‬
                            EXCEPTION                       EXCEPTION                          
            
                                                                                      ‫א‬       
      ‫א‬             END;                                END;                                  
                                                                                                      
                                                                   ‫א‬‫א‬             
                                                                           ‫א‬‫א‬                    

                                                                                                      
                                                                               ‫א‬‫א‬‫א‬‫א‬
                                                         W‫א‬‫א‬‫א‬‫א‬
                                          ‫א‬‫א‬‫א‬‫ א‬K١
                   K‫א‬‫א‬‫א‬
                                             ‫א‬‫א‬‫א‬‫ א‬K٢
     K‫א‬‫א‬‫א‬‫א‬
                                                  ‫א‬‫א‬‫א‬‫ א‬K٣
  K‫א‬‫א‬‫א‬‫א‬
                                                                                                      




                                              - ٢٥ -
       ‫א‬‫א‬                            ٢٦٢                         ‫א‬‫א‬
     ‫א‬‫א‬                      ‫א‬                      ‫א‬



      W‫א‬‫א‬‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬
         EXCEPTION
             WHEN exception1 [ OR exception2 . . . ] THEN
                    Statement1;
                    Statement2;
            [WHEN exception3 [ OR exception4 . . . ] THEN
                    Statement1;
                    Statement2;
                    . . .]
            [WHEN OTHERS THEN
                    Statement1;
                    Statement2;
                    . . .]
‫א‬،‫א‬‫א‬WHEN OTHERS‫א‬‫א‬
             K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                
                                                                             ‫א‬‫א‬
                                               ‫א‬‫א‬‫א‬‫א‬
، ‫א‬ ‫א‬   ‫א‬ ‫א‬  ‫א‬  ‫א‬‫א‬ 
                                                               W‫א‬‫א‬‫א‬
                                                           NO_DATA_FOUND K١
                                                          TOO_MANY_ROWS K٢
                                                           INVALID_CURSOR K٣
                                                              ZERO_DIVIDE K٤
                                                        DUP_VAL_ON_INDEX K٥

‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬ PL/SQL
NO_DATA_FOUND ‫א‬‫א‬،Standard Package
    K‫א‬‫א‬‫א‬TOO_MANY_ROWS
                                                                                    



                                               - ٢٦ -
       ‫א‬‫א‬                               ٢٦٢                          ‫א‬‫א‬
     ‫א‬‫א‬                         ‫א‬                       ‫א‬



                                              ‫א‬‫א‬‫א‬‫א‬
 ،‫א‬ ‫א‬‫א‬‫א‬‫א‬
 ،  ‫א‬  ‫א‬     ‫א‬ ‫א‬  ‫א‬‫א‬
‫א‬‫א‬K‫א‬‫א‬‫א‬OTHER‫א‬‫א‬‫א‬
                            K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                    
                                                                                                    
                                                                                                    
                                                                                                    
                  DECLARE                   ASSOCIATE                       REFERENCE
                                                                                                    
                                                                                                    
                                 Declarative Section                           Exception            
                                                                         Handling Section
                                                                                                    
                  ‫א‬              PRAGMA ‫א‬                   ‫א‬          
                                           EXCEPTION_INIT                         ‫א‬          
                                          ‫א‬                                          
                                                                                                    
                                                                                                    
  ،   ‫א‬ ‫א‬ ‫א‬  PRAGMA ‫א‬ ‫א‬ 
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                KPRAGMA




                                                  - ٢٧ -
      ‫א‬‫א‬                     ٢٦٢                           ‫א‬‫א‬
    ‫א‬‫א‬               ‫א‬                        ‫א‬



                                                                                     W
  DECLARE
    e_emps_remaining   EXCEPTION;
    PRAGMA_EXCEPTION_INIT (e_emps_remaining, -2292) ;
    v_deptno           dept.deptno%TYPE := &p_deptno ;
  BEGIN
     DELETE FROM dept
     WHERE deptno = v_deptno;
     COMMIT;
  EXCEPTION
     WHEN e_emps_remaining THEN
        DBMS_OUTPUT.PUT_LINE (‘Cannot remove dept ‘||
                          TO_CHAR (v_deptno) || ‘. Employees exist ‘);
  END;
                                                                     
                                                 K‫א‬‫א‬‫א‬‫א‬
 W‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬PL/SQL
                                                ‫א‬‫א‬ K١
                             KRAISE‫א‬ K٢
                                                                                          
  DECLARE
     e_invalid_product    EXCEPTION;
  BEGIN
     UPDATE product
     SET descrip = ‘&product_description’
     WHERE prodid = &product_number;
     IF SQL%NOTFOUND THEN
          RAISE e-invalid_product;
     END IF;
     COMMIT;
  EXCEPTION
     WHEN e_invalid_product THEN
           DBMS_OUTPUT.PUT_LINE (‘Invalid product number. ‘);
  END;

                                       - ٢٨ -
       ‫א‬‫א‬                           ٢٦٢                         ‫א‬‫א‬
     ‫א‬‫א‬                     ‫א‬                      ‫א‬



                                                               ‫א‬‫א‬
   W‫א‬‫א‬‫א‬‫א‬‫א‬،‫א‬
                                    ‫א‬SQLCODE K١
                                 KSQLERRM K٢
                                                                                               
                                                                                               

   DECLARE
         v_error_code         number;
         v_error_message varchar2(255);
   BEGIN
   ...
   exception
   WHEN OTHERS THEN
       ROLLBACK;
       v_error_code := SQLCODE;
       v_error_message := SQLERRM;
        INSERT INTO errors VALUES (v_error_code, v_error_message);
   END;
                                                    ‫א‬‫א‬‫א‬
 ‫א‬،‫א‬  ‫א‬
‫א‬ ‫א‬K‫א‬‫א‬‫א‬‫א‬ ‫א‬
‫א‬ ‫א‬ ‫א‬‫א‬‫א‬ ‫א‬ ‫א‬‫א‬
                                                        K‫א‬‫א‬Enclosing Block




                                              - ٢٩ -
       ‫א‬‫א‬                             ٢٦٢                           ‫א‬‫א‬
     ‫א‬‫א‬                       ‫א‬                        ‫א‬



                                                                            DECLARE
                                                                                                   
                                                                                . . .              
                                                         exception;        e_no_rows
                                                         exception;          e_integrity           
                                             PRAGMA EXCEPTION_INIT (e_integrity, -2292);
                                                                                 BEGIN             
                                                      FOR c_reocrd IN emp_cursor LOOP
                                                                                                   
                                                                         BEGIN
                                                                         BEGIN
                                                                    SELECT …
                                                                    SELECT …                       
                                                                    UPDATE …
                                                                    UPDATE …                       
                                                      IF SQL%NOTFOUND THEN
                                                      IF SQL%NOTFOUND THEN
                                                              RAISE e_no_rows;
                                                              RAISE e_no_rows;
                                                                        END IF;                    
                                                                        END IF;
                                                                   EXCEPTION
                                                                   EXCEPTION
                                                      WHEN e_inventory THEN …
                                                                                                   
                                                      WHEN e_inventory THEN …
                                                       WHEN e_no_rows THEN …
                                                       WHEN e_no_rows THEN …
                                                                          END;
                                                                          END;                     
                                                                                                   
                                                                                                   
                                                                                  END LOOP;
                                                                                 EXCEPTION

‫א‬    ،    ‫א‬   ‫א‬   ‫א‬ 
 ‫א‬‫א‬ ‫א‬  ‫א‬‫א‬       ،NO_DATA_FOUND
 ‫א‬   ‫א‬‫א‬ ‫א‬  ‫א‬   ،‫א‬ ‫א‬   
    K‫א‬‫א‬‫א‬‫א‬‫א‬E‫א‬‫א‬‫א‬F‫א‬‫א‬




                                                - ٣٠ -
      ‫א‬‫א‬                       ٢٦٢                        ‫א‬‫א‬
    ‫א‬‫א‬                 ‫א‬                     ‫א‬



                                      KRAISE_APPLICATION_ERROR‫א‬
‫א‬ ‫א‬   RAISE_APPLICATION_ERROR ‫א‬ ‫א‬‫א‬ 
                                            K‫א‬‫א‬‫א‬‫א‬
                                                                                         
…
EXCEPTION
  WHEN NO_DATA_FOUND THEN
       RAISE_APPLICATION_ERROR (-20201,
                             ‘Manager is not a valid employee.’);
END;

       W‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
…
DELETE FROM emp
WHERE mgr = v_mgr;
IF SQL%NOTFOUND THEN
   RAISE_APPLICATION_ERROR (-20202, ‘This is not a valid manager’);
END IF;
…




                                         - ٣١ -
       ‫א‬‫א‬                            ٢٦٢                           ‫א‬‫א‬
     ‫א‬‫א‬                      ‫א‬                        ‫א‬



                                                                                           
                            K‫א‬‫א‬‫א‬PL/SQL‫א‬ E١
‫א‬  ‫א‬ ‫א‬  ،   ‫א‬   ‫א‬ ‫א‬‫א‬ ‫א‬ K١
        <Salary>‫א‬?‫א‬MESSAGES
   ‫א‬  ‫א‬ ‫א‬      ‫א‬ ‫א‬‫א‬ ‫א‬ K٢
                             K<Salary>‫א‬?‫א‬MESSAGES
                                                  
‫א‬ ‫א‬ MESSAGES    ، ‫א‬‫א‬ ‫א‬  ‫א‬ ‫א‬‫א‬ ‫א‬ K٣
                                                                                       K‫א‬
 ? ‫א‬ MESSAGES    ‫א‬   ‫א‬   K٤
                                                                                    K?
   KMESSAGES‫א‬‫א‬‫א‬‫א‬‫א‬ K٥

                                                                                  RESULTS

                                                                               ٢٠٠٠–
                                                            ٥٠٠‫א‬
                                                                 ٣٥٠٠‫א‬
                                                                                                   
  DEPT ‫א‬‫א‬    ‫א‬    PL/SQL  ‫א‬ E٢
Substitution  ‫א‬ ‫א‬‫א‬ ‫א‬ ‫א‬ ‫א‬‫א‬  ‫א‬‫א‬   ‫א‬
                                                                                      Kvariables
    ‫א‬ ‫א‬‫א‬ ‫א‬ ‫ א‬K١
                                                             K‫א‬‫א‬
                                         K‫א‬PL/SQL K٢




                                               - ٣٢ -
      ‫א‬‫א‬                          ٢٦٢                       ‫א‬‫א‬
    ‫א‬‫א‬                    ‫א‬                    ‫א‬




    Please enter department number: 50
    Please enter department location: Huston
    PL/SQL procedure successfully Completed

    G_MESSAGE
    ‫א‬٥٠‫א‬‫א‬

                                                                                           
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬PL/SQL‫א‬E٣
                                                                              K$١٠٠
             K‫א‬‫א‬،‫א‬‫א‬ K١
    K‫א‬‫א‬‫א‬‫א‬‫א‬،‫א‬‫א‬ K٢
           K‫א‬‫א‬‫א‬‫א‬ K٣




                                            - ٣٣ -
     ‫א‬‫א‬                       ٢٦٢          ‫א‬‫א‬
   ‫א‬‫א‬                 ‫א‬       ‫א‬



                                                                          
Please enter the salary: 800
PL/SQL procedure successfully completed.
G_MESSAGE


٩٠٠٧٠٠‫א‬E١F

Please enter the salary: 3000
PL/SQL procedure successfully completed.

G_MESSAGE


٣١٠٠٢٩٠٠‫א‬E٣F

Please enter the salary: 6000
PL/SQL procedure successfully completed.

G_MESSAGE


٦١٠٠٥٩٠٠‫א‬




                                        - ٣٤ -
                                    ‫א‬‫א‬‫א‬
                                ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹]
                                     ‫א‬‫א‬‫א‬‫א‬




               ‫א‬
                 ‫א‬‫א‬
‫א‬‫א‬




٤
  ‫א‬‫א‬‫א‬                          ٢٦٢                               ‫א‬‫א‬
 ‫א‬‫א‬                       ‫א‬                            ‫א‬



                                                                                       W‫א‬‫א‬

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                     K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬،‫א‬
                                                                                      W‫א‬‫א‬

                                              W‫א‬،‫א‬
                                                    ‫א‬‫א‬ K١
                                          ‫א‬‫א‬‫א‬‫א‬‫א‬ K٢
                                      ‫א‬‫א‬‫א‬‫א‬‫ א‬K٣
                                 ‫א‬‫א‬ K٤
                                          ‫א‬‫א‬‫א‬‫א‬‫א‬ K٥
                                                                                               
                                                                     W‫א‬‫א‬‫א‬
                                               ٪١٠٠‫א‬‫א‬‫א‬
                                                                                               
                                                           ٤W‫א‬‫א‬
                                                                                               
                                                                               W‫א‬‫א‬
                                                                           •
                                                           Projector‫א‬ •
                                                                                       •
                                                                                        •




                                        - ٣٥ -
       ‫א‬‫א‬‫א‬                             ٢٦٢                             ‫א‬‫א‬
      ‫א‬‫א‬                          ‫א‬                           ‫א‬



                                                                                      W‫א‬‫א‬‫א‬
                                                                  Program Constructs‫א‬‫א‬
                                                                                    W
 ،Program Modules ‫א‬ ‫א‬‫א‬ ‫א‬‫א‬   ‫א‬ ‫א‬  
،Modules‫א‬‫א‬‫א‬ ‫א‬  ‫א‬‫א‬
‫א‬،‫א‬‫א‬‫א‬ ‫א‬   
‫א‬‫א‬   Database Server ‫א‬     ‫א‬ ‫א‬‫א‬ 
K‫א‬   ‫א‬‫א‬‫א‬
  Source Code‫א‬‫א‬‫א‬  ‫א‬‫א‬‫א‬
‫א‬‫א‬  ‫א‬  KP-Code  ‫א‬   Compile   ‫א‬ 
                                                                      W’‫א‬‫א‬‫א‬
                                                                                                     
                 Anonymous                                                      Stored               
                                                                              procedure/
                       block                             DECLARE               Function              


                 Application                                BEGIN            Application
                                                                             procedure/
                       trigger                                                Function


                                                    EXCEPTION


                   Database                                                    Package
                    trigger


                                               Object Type




                                                - ٣٦ -
      ‫א‬‫א‬‫א‬                              ٢٦٢                          ‫א‬‫א‬
      ‫א‬‫א‬                          ‫א‬                       ‫א‬



                                                       ‫א‬‫א‬‫א‬‫א‬
     W‫א‬‫א‬‫א‬‫א‬‫א‬
K‫א‬‫א‬‫א‬‫א‬‫א‬WAnonymous Block K١
     K‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬،SubprogramsWNamed Block    K٢
Function, Procedure, Trigger, PackageW‫א‬‫א‬
                                                                       K‫א‬
‫א‬‫א‬‫א‬ ‫א‬ ‫א‬‫א‬
                                                             KNested Blocks‫א‬‫א‬
                                                  Anonymous PL/SQL Block
‫א‬‫א‬  ‫א‬،‫א‬ ‫א‬‫א‬ 
‫א‬    PL/SQL     ،   
                                    W‫א‬‫א‬‫א‬،Execution time

     DECLARE             (Optional ‫)א‬
              ‫א‬‫א‬‫א‬‫א‬
     BEGIN               (Mandatory )
     ‫א‬‫א‬
     EXCEPTION            (Optional ‫)א‬
     
     END;                 (Mandatory )




                                                - ٣٧ -
       ‫א‬‫א‬‫א‬                               ٢٦٢                          ‫א‬‫א‬
      ‫א‬‫א‬                            ‫א‬                       ‫א‬



                 J W‫א‬‫א‬،

                                                                                             
                                                           Declaration section‫א‬ K
  ‫א‬  ‫א‬   Declare ‫א‬  ‫א‬ ‫א‬ 
،‫א‬‫א‬    ،‫א‬ ‫א‬ ‫א‬  KBegin ‫א‬  ‫א‬
                        K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬،‫א‬‫א‬
                                                                                             
                                                             Executable Section‫א‬ K
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
End‫א‬Begin‫א‬‫א‬‫א‬،‫א‬
                                                   K‫א‬‫א‬
                                                                                          
                                                        Exception Section‫א‬‫א‬ K
 ‫א‬ ‫א‬  ‫א‬  Exception ‫א‬  ‫א‬ ‫א‬ 
‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬
                                                                                   K‫א‬
                                                                                        
                                                    Named PL/SQL Block‫א‬‫א‬‫א‬
‫א‬ ‫א‬‫א‬‫א‬
PL/SQL،‫א‬‫א‬‫א‬ Parameters ‫א‬
                         K Procedure‫א‬،Function‫א‬‫א‬‫א‬‫א‬




                                                  - ٣٨ -
       ‫א‬‫א‬‫א‬                             ٢٦٢                         ‫א‬‫א‬
      ‫א‬‫א‬                          ‫א‬                      ‫א‬



                                                                                           
             Header
             IS
                Declaration section
             BEGIN
                Executable Section
             Exception     (optional)
                Exception section
             END;

،Subprograms،‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬،‫א‬
                   W‫א‬‫א‬،Header‫א‬‫א‬‫א‬
                                                                                
                             KFunctionProcedure‫א‬‫א‬‫א‬ K
                                                                                    
                                                                                      
                                                            ‫א‬‫א‬‫א‬‫ א‬K
                                                                                       
                                                  K،‫א‬‫א‬ K
                       KFunctions‫א‬‫א‬‫א‬Return‫א‬ K
                                                                                  
 ‫א‬ IS ‫א‬
          KAnonymous Blocks‫א‬‫א‬‫א‬IS
‫א‬‫א‬‫א‬ Anonymous Blocks ‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬
                                                          K‫؟‬Subprograms‫א‬‫א‬‫א‬
‫א‬   ‫א‬ ‫א‬‫א‬  ‫א‬ ‫א‬ ‫א‬  ‫א‬   
‫א‬ ‫א‬ 
                                               K‫א‬
                                                                                           




                                                - ٣٩ -
       ‫א‬‫א‬‫א‬                             ٢٦٢                           ‫א‬‫א‬
      ‫א‬‫א‬                          ‫א‬                        ‫א‬



                            .Advantages of Subprograms‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                          KImprove Reusability‫א‬‫א‬ K١
 ‫א‬ ‫א‬‫א‬‫א‬   ‫א‬ ‫א‬
 ‫א‬‫א‬‫א‬‫א‬‫א‬،‫א‬ 
                                                                           K‫א‬
                                               KImprove maintenance‫א‬ K٢
‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬ ‫א‬
 ‫א‬‫א‬ ، 
                                 
   ‫א‬ ‫א‬‫א‬        ‫א‬ ‫א‬    
 ‫א‬ ‫א‬   ‫א‬  ،‫א‬   ‫א‬ ‫א‬
                                                                   K‫א‬

                                                     KImprove Performance‫א‬ K٣
‫א‬  ‫• א‬
                       K‫א‬‫א‬
‫א‬ ‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬ •
                                   Network Traffic ‫א‬‫א‬
                                     ‫؟‬‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬  Database Server ‫א‬    ‫א‬ ‫א‬‫א‬  
Procedure Builder   Database Client ‫א‬     KSQL*Plus
                               KForms Builder‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                   
                        KProceduresFunctions‫א‬‫א‬‫א‬‫א‬
                                                                             




                                                - ٤٠ -
                              ‫א‬‫א‬‫א‬
                         ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹]
                               ‫א‬‫א‬‫א‬‫א‬




         ‫א‬
              ‫א‬
‫א‬




٥
     ‫א‬‫א‬                         ٢٦٢                               ‫א‬‫א‬
       ‫א‬                        ‫א‬                            ‫א‬




                                                                                        W‫א‬‫א‬

                                                             
،‫א‬‫א‬‫א‬
                                          K‫א‬‫א‬‫א‬‫א‬

                                                                                       W‫א‬‫א‬

                                               W‫א‬،‫א‬
                                                                ‫א‬ K١
                                                      ‫א‬‫א‬ K٢
                                    ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫ א‬K٣
                                    ‫א‬‫א‬‫א‬‫א‬‫א‬‫ א‬K٤
                                                   K٥
                                                                                                

                                                                             W‫א‬‫א‬‫א‬
                                                ٪١٠٠‫א‬‫א‬‫א‬
                                                                                                

                                                            ٥W‫א‬‫א‬
                                                                                                
                                                                               W‫א‬‫א‬
                                                                           o
                                                            Projector‫א‬ o
                                                                                       o
                                                                                        o




                                         - ٦١ -
      ‫א‬‫א‬                              ٢٦٢                          ‫א‬‫א‬
         ‫א‬                            ‫א‬                       ‫א‬



                                                                     Procedure‫א‬
‫א‬‫א‬  ‫א‬
                        WProcedureReturn value
        CREATE [OR REPLACE] PROCEDURE procedure_name
           [ (Parameter1 [,Parameter2, …] ) ]
        AS
           [ Local Declaration ]
        Begin
             Executable Statements
        [Exception
             exception handlers ]
        END [procedure_name]
                                                                                                 
                                                        
     ،‫א‬‫א‬‫א‬    ‫א‬ Procedure    
                                                                    W ‫א‬
                                                                          Header‫ א‬K١
           ‫א‬‫א‬‫א‬ISAS‫א‬‫א‬
                                                                               Body‫ א‬K٢
 K‫א‬ ‫א‬  ‫א‬‫א‬ ‫א‬‫א‬ ‫א‬  IS    
 ‫א‬ ،‫א‬‫א‬  ،   ‫א‬   REPLACE 
 ‫א‬ ‫א‬  ‫א‬‫א‬      ‫א‬ ‫א‬
                                                                                  K‫א‬




                                               - ٦٢ -
      ‫א‬‫א‬                                ٢٦٢                         ‫א‬‫א‬
         ‫א‬                              ‫א‬                      ‫א‬



                                                                 Procedure‫א‬‫א‬
                        K‫؟‬Procedure‫א‬‫א‬‫א‬‫א‬
                                                                                                  

            1
                                           Oracle
                                                                                  Editor
                                                                               SQL*Plus
                                                                                                  
                                          Code
                                        Procedure                               Code
                                         Builder
                                          Save
                                                                     SQL> START file.sql

                                                          2
                                                                                                  
                                                Source code                                       
                                                                ORACLE
                                                              Compile                             
                                                      P-Code                                      


                                                          Execute
                                                                                                  

‫א‬ SQL*Plus  Procedure Builder ‫א‬ ‫א‬ ‫א‬‫ א‬K١
                                                                             K‫א‬‫א‬
                                                       Compile the code‫א‬ K٢
                      p-code‫א‬Source Code‫א‬‫א‬
                                                                    ‫א‬‫א‬‫א‬ K٣
‫א‬ Start   Procedure Builder ‫א‬ ‫א‬ 
                                         SQL*Plus‫א‬‫א‬‫א‬
                                                                                           




                                                 - ٦٣ -
      ‫א‬‫א‬                              ٢٦٢                     ‫א‬‫א‬
        ‫א‬                             ‫א‬                  ‫א‬



                                          Procedural Parameter Modes ‫א‬‫א‬‫א‬
‫א‬ ‫א‬ ‫א‬ ‫א‬ ‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬،Calling Environment‫א‬‫א‬
، ‫א‬،‫א‬‫א‬ ‫א‬‫א‬‫א‬
  KIN OUT،OUT،INW‫א‬‫א‬‫א‬‫א‬
                                                                                      
                                                                         Procedure
                                                                                      
                                                                      IN parameter

                     Calling Environment                            OUT parameter
                                                                                      
                                                                 IN OUT parameter
                                                                                      
                                                                          (Declare)
                                                                                      
                                                                                      
                                                                             Begin
                                                                                      
                                                                                      
                                                                         Exception
                                                                                      
                                                                                      
                                                                                      
                                   KIN‫א‬W




                                               - ٦٤ -
      ‫א‬‫א‬                               ٢٦٢                               ‫א‬‫א‬
         ‫א‬                             ‫א‬                            ‫א‬



                                                                                                       
                                                                   ‫א‬          ‫א‬
          Procedure‫א‬‫א‬‫א‬‫א‬             E‫א‬‫א‬FIN
                ‫א‬‫א‬Procedure‫א‬‫א‬                  OUT
       ‫א‬‫א‬   Procedure ‫א‬ ‫א‬                   IN OUT
                          Parameter‫א‬‫א‬
                                                                                                       
                Parameter Modes for Formal Parameters‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬ ‫א‬‫א‬، ‫א‬ ‫א‬ ‫א‬
‫א‬‫א‬ Actual Parameter ‫א‬‫א‬،‫א‬ ‫א‬
                                                                    K‫א‬‫א‬‫א‬‫א‬
                   IN OUT                    OUT                               IN
                                                         ‫א‬‫א‬‫א‬
       ‫א‬  ‫א‬                                  ‫א‬  ‫א‬ 
                                      ‫א‬‫א‬‫א‬
                         ‫א‬                                       Subprogram‫א‬
                       ‫א‬ 
              Initialized Variable     Un initialized variable         ‫א‬‫א‬
                                                                    Actual  ‫א‬ ‫א‬
                                                                    ‫א‬  Parameters
             
            ‫א‬                           ،       
                                                                        ،
                                                                                          
                                                                                                       
                                                                                 ‫א‬W
‫א‬    ‫א‬    ،IN    ‫א‬ ‫א‬ ‫א‬
                      
‫א‬  ‫א‬ ‫א‬،‫א‬ ‫א‬
                                                                        K٪١٠‫א‬‫א‬

                                                - ٦٥ -
      ‫א‬‫א‬                             ٢٦٢                         ‫א‬‫א‬
        ‫א‬                            ‫א‬                      ‫א‬



                                                                                               
                                                                                               
     SQL> CREATE OR REPLACE PROCEDURE raise_salary
        2 (v_id in emp.empno%TYPE)
        3    IS
        4 BEGIN
        5       UPDATE emp
        6        SET sal = sal * 1.10
        7        WHERE empno = v_id;
        8     END raise_salary;
        9    /
     Procedure created.
     SQL> EXECUTE raise_salary(7369)
     PL/SQL procedure successfully completed

SQL*Plus‫א‬،‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬ ‫א‬ Raise_salary‫א‬  
                                          ٪١٠‫א‬‫א‬‫א‬‫א‬
                                   EXECUTE‫א‬،SQL*Plus‫א‬
                                                                                               
        SQL> EXECUTE raise_salary (7369)
                                                                                               
‫א‬‫א‬‫א‬،Procedure Builder‫א‬‫א‬‫א‬
                                                                         ‫א‬‫א‬‫א‬
        PL/SQL> raise_salary (7369)
                                                                                               
‫א‬   ‫א‬ ‫א‬ ‫א‬ ‫א‬      ‫א‬‫א‬‫א‬  
                                                                         K




                                              - ٦٦ -
      ‫א‬‫א‬                              ٢٦٢                         ‫א‬‫א‬
        ‫א‬                             ‫א‬                      ‫א‬



                                                                             ‫א‬W
        SQL> CREAE OR REPLACE PROCEDURE query_emp
             (v_id       IN         emp.empno%TYPE,
              v_name     OUT       emp.ename%TYPE,
              v_salary   OUT       emp.sal%TYPE,
              v_comm    OUT        emp.empno%TYPE)
            IS
            BEGIN
                SELECT ename, sal, comm.
                INTO v_name, v_salary, V_comm
                FROM emp
                WHERE empno = v_id;
            END query_emp;
        /
                                                                                                
‫א‬‫א‬ ‫א‬‫א‬ ،‫א‬‫א‬  ‫א‬
                                              K‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬SQL*Plus‫א‬‫א‬‫א‬
                           W‫א‬،‫א‬‫א‬‫א‬‫א‬
                                                                                                
        SQL> START emp_query.sql
        Procedure created.
                                K‫א‬‫א‬‫א‬‫א‬emp_query.sql
                                                                                                
        SQL> VARIABLE g_name                   VARCHAR2(15)
        SQL> VARIABLE g_sal                    NUMBER
        SQL> VARIABLE g_comm                   NUMBER

        SQL> EXECUTE query_emp(7654,:g_name, :g_sal, :g_comm)
        PL/SQL procedure successfully completed.
                                                                                                
                        Host Variables‫א‬‫א‬‫א‬(:)‫א‬‫א‬
                                                                                                
        SQL> PRINT g_name
        G_NAME
        MARTIN
                                               - ٦٧ -
      ‫א‬‫א‬                           ٢٦٢                       ‫א‬‫א‬
        ‫א‬                          ‫א‬                    ‫א‬



                                                                  ‫א‬W
       SQL> CREATE OR REPLACE PROCEDURE format_phone
            (v_phone_no IN OUT VARCHAR2)
           IS
           BEGIN
           v_phone_no :=    ‘(‘ || SUBSTR(V_phone_no,1,3) ||
                            ‘)’ || SUNSTR(V_phone_no,4,3) ||
                            ‘-‘ || SUBSTR(v_phone_no,7);
           END format_phone;
       /
‫א‬ K‫א‬ ‫א‬‫א‬‫א‬
  ‫א‬‫א‬‫א‬‫א‬  ‫א‬‫א‬
                                                                                       K
                                                                                           
       SQL> VARIABLR g_phone_no VARCHAR2(15)

       SQL> BEGIN :g_phone_no := ‘8006330575’; END;
            /
       PL/SQL procedure successfully completed.
       G_PHONE_NO
       8006330575

                                                                                           

       SQL> EXECUTE format_phone (:g_phone_no)
       PL/SQL procedure successfully completed

       SQL> PRINT g_phone_no
       G_PHONE_NO
       (800)633-0575
                                                                                           




                                            - ٦٨ -
      ‫א‬‫א‬                            ٢٦٢                           ‫א‬‫א‬
        ‫א‬                           ‫א‬                        ‫א‬



                                                                       ‫א‬‫א‬‫א‬
‫א‬ ‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬
                                                                K‫א‬،‫א‬W
                                                           ‫א‬                  ‫א‬
                            ‫א‬‫א‬‫א‬‫א‬‫א‬             Position‫א‬
      ‫א‬     ‫א‬  ‫א‬ ‫א‬               Name‫א‬
                       EZ>F‫א‬‫א‬‫א‬‫א‬
      ‫א‬ ‫א‬ ‫א‬ ‫א‬  ‫א‬ ‫א‬   Combination
                                                          K‫א‬
                                                                                                
                                                                                           W
       BEGIN
       add_dept;
       add_dept (‘TRAINING’, ‘NEW YORK’);
       add_dept (v_loc => ‘DALLAS’, v_name => ‘EDUCATION’);
       add_dept (v_loc => ‘BOSTON’);
       END;
       /
                                                                                                
       SQL> SELECT * FROM dept;

       DEPTNO            DNAME                  LOC
        ….               …..                     ….
         41              unknown                 unknown
         42              TRAINING                NEW YORK
         43              EDUCATION               DALLAS
         44              unknown                 BOSTON




                                             - ٦٩ -
      ‫א‬‫א‬                               ٢٦٢                          ‫א‬‫א‬
         ‫א‬                             ‫א‬                       ‫א‬



                                                          ‫א‬‫א‬Default‫א‬‫א‬‫א‬‫א‬
 ‫א‬‫א‬  ‫א‬ Default‫א‬‫א‬‫א‬‫א‬
                              W‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
        SQL> CREATE OR REPLACE PROCEDURE add_dept
              (v_name     IN dept.dname%TYPE DEFAULT ‘unknown’,
               v_loc      IN dept.loc%TYPE       DEFAULT ‘unknown’)
             IS
             BEGIN
                INSERT INTO dept
                VALUES (dept_deptno.NEXTVL, v_name, v_loc);
            END add_dept;
            /
                                                                                                  
                                                                 ‫א‬‫א‬
‫א‬   ‫א‬  SQL*Plus   ‫א‬ ‫א‬     
‫א‬   Named Subprogram       ‫א‬  ‫א‬
     K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬،Anonymous Block
                                                                                             W
        SQL> CREATE OR REPLACE PROCEDURE process_emps
             IS
                CURSOR emp_cursor IS
                  SELECT empno
                  FROM emp;
            BEGIN
                FOR emp_rec IN emp_cursor
                LOOP
                  raise_salary (emp_rec.empno); -- invoke procedure
                END LOOP;
                COMMT;
            END;
        /
                                                                                                  




                                                - ٧٠ -
      ‫א‬‫א‬                            ٢٦٢                         ‫א‬‫א‬
        ‫א‬                           ‫א‬                      ‫א‬



                                  Removing Server-Side procedure‫א‬‫א‬
 W،DROPPROCEDURESQL*Plus‫א‬‫א‬
                                                                                              
       SQL> DROP PROCEDURE raise_salary;
       Procedure dropped.
                                                                                      W
SQL*Plus Compilation Errors ‫א‬ ‫א‬ ‫א‬ 
                                              K‫א‬SHOW_ERRORS‫א‬‫א‬




                                             - ٧١ -
      ‫א‬‫א‬                              ٢٦٢                         ‫א‬‫א‬
        ‫א‬                             ‫א‬                      ‫א‬



                                                                                             
                                                                                                 
                                                                                              E١
PRODUCT‫א‬‫א‬ ADD_PROD   K١
                                     K‫א‬‫א‬‫א‬‫א‬‫א‬
   ‫א‬ ‫א‬  ، ‫א‬ ‫א‬  ‫א‬ ‫א‬  K٢
            K‫א‬PRODUCT‫א‬‫א‬،
                                                                                                
                                                                                              E٢
،PRODUCT UPD_PROD  K١
      
 ‫א‬،‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                               K‫א‬
،  ‫א‬ ‫א‬،‫א‬‫א‬‫א‬ K٢
 ‫א‬ ‫א‬  K‫א‬  PRODUCT ‫א‬   ‫א‬ 
                                               K‫א‬
                                                                                                
                                                                                              E٣
‫א‬،PRODUCT DEL_PROD  K١
                           
 ‫א‬   ‫א‬ ‫א‬ ، ‫א‬‫א‬ ‫א‬   ‫א‬ ‫א‬
                                                                  K
،    ‫א‬ ‫א‬  ، ‫א‬  ‫א‬ ‫א‬    .٢

‫א‬‫א‬K‫א‬PRODUCT‫א‬‫א‬
                                               K‫א‬




                                               - ٧٢ -
                                                         ‫א‬‫א‬‫א‬
                                                    ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹]
                                                          ‫א‬‫א‬‫א‬‫א‬




                                    ‫א‬
                               ‫א‬
‫א‬




٦
     ‫א‬‫א‬                         ٢٦٢                            ‫א‬‫א‬
‫א‬           ‫א‬                         ‫א‬
                                          



                                                                                      W‫א‬‫א‬

                                                              
     K‫א‬‫א‬‫א‬‫א‬‫א‬

                                                                                     W‫א‬‫א‬

                                                W‫א‬،‫א‬
                                                                ‫א‬‫א‬ K٦
                                                            ‫א‬‫א‬‫א‬ K٧
                                                   ‫א‬‫א‬ K٨
                                                       ‫א‬‫א‬ K٩
                                                       ‫א‬‫א‬‫א‬‫ א‬K١٠
                                                                                              

                                                                           W‫א‬‫א‬‫א‬
                                                 ٪١٠٠‫א‬‫א‬‫א‬
                                                                                              

                                                         ٤W‫א‬‫א‬
                                                                                              

                                                                              W‫א‬‫א‬
                                                                          o
                                                            Projector‫א‬ o
                                                                                     o
                                                                                      o




                                          - ٧٣ -
       ‫א‬‫א‬                                   ٢٦٢                        ‫א‬‫א‬
  ‫א‬                     ‫א‬                     ‫א‬
                                                      


                                                                Functions‫א‬‫א‬‫א‬‫א‬
  ‫א‬‫א‬  ‫א‬‫א‬
‫א‬KExpression‫א‬‫א‬‫א‬،Return value
                                                                                  WFunction
         CREATE [OR REPLACE] FUNCTION function_name
             [ (argument1 [mode1] datatype1 [,argument2 [mode2] datatype2, …] ) ]
         RETURN datatype
         IS|AS
             [ Local Declaration ]
         Begin
               Executable Statements
         [Exception
               exception handlers ]
         END [function_name]
                                                                                                      
     ‫א‬‫א‬‫א‬     Function ‫א‬   
                                   W‫א‬ ‫א‬‫א‬،‫א‬
                                                                                Header‫ א‬K١
،‫א‬ ‫א‬ ‫א‬‫א‬‫א‬ IS  AS‫א‬‫א‬
      ‫א‬‫א‬ ‫א‬‫א‬  ‫א‬  REPLACE ‫א‬ 
                                                                               
‫א‬ ‫א‬  RETURN ‫א‬‫א‬ ‫ א‬K٢
                                                                    Ksize
                                                                                    Body‫ א‬K٣
،‫א‬ ‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬ IS
                                 RETURN (variable)‫א‬‫א‬




                                                      - ٧٤ -
       ‫א‬‫א‬                                 ٢٦٢                                 ‫א‬‫א‬
  ‫א‬                   ‫א‬                              ‫א‬
                                                    


                                                                     Function‫א‬‫א‬‫א‬
                            K‫؟‬Function‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                             
                                                                                                             

                                                                                    Editor
     1                                 Oracle
                                      Code                                        SQL*Plus
                                    Procedure                                      Code
                 Compile             Builder
                                      Save
                                                                      SQL> START file.sql


                                                        2               Compile


                                           Source code
                                                             ORACLE                                          
                                                                                                             
                                                 P-Code
                                                                                                             
                                                        Execute                                              
                                                                                                             

‫א‬ SQL*Plus  Procedure Builder‫א‬ ‫א‬ ‫א‬‫ א‬K١
                                                                                     K‫א‬‫א‬
                                                            Compile the code‫א‬ K٢
                        p-code‫א‬Source Code‫א‬‫א‬
                                                                          ‫א‬‫א‬‫א‬ K٣
‫א‬ Start   Procedure Builder ‫א‬ ‫א‬
                                           SQL*Plus‫א‬‫א‬‫א‬




                                                    - ٧٥ -
       ‫א‬‫א‬                         ٢٦٢                       ‫א‬‫א‬
  ‫א‬           ‫א‬                    ‫א‬
                                            

‫א‬‫א‬‫א‬‫א‬KSQL*Plus‫א‬‫א‬‫א‬ ‫א‬ ‫א‬‫א‬‫א‬
                                        KNUMBER،‫א‬
                                                                                           
         SQL> CREATE OR REPLACE FUNCTION get_sal
                 (v_id IN emp.empno%TYPe)
                 RETURN NUMBER
              IS
                 v_salary emp.sal%TYPE := 0;
              BEGIN
                  SELECT sal
                  INTO v_salary
                  FROM emp
                  WHERE empno = v_id;
                   RETURN (v_salary);
              END;
         /
                                                                                           
                                                                           W‫א‬‫א‬
                                                     
‫א‬ ،  ‫א‬‫א‬ ‫א‬‫א‬‫א‬
‫א‬ ‫א‬ ‫א‬  PL/SQL‫א‬‫א‬‫א‬K
                                                                         K‫א‬‫א‬




                                            - ٧٦ -
       ‫א‬‫א‬                                  ٢٦٢                        ‫א‬‫א‬
  ‫א‬                    ‫א‬                     ‫א‬
                                                     




                       Calling Environment                    GET SAL function
                                                                                                     
                                                                                                     
                                   7934                                    v id                      

                                                              RETURN v_salary


                                                   SQL> START get_salary.sql
                                                           Function Created

                                             SQL> VARIABLE g_salary number

                                   SQL> EXECUTE :g_salary := get_sal(7934)
                                                                                                     
                                    PL/SQL procedure successfully completed                          
                                                                                                     

PL/SQL ‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬
                                                                                                 W
                                                                                                     
                                                                 SELECT ‫א‬‫א‬ E١
                                                               HavingWhere‫א‬ E٢
                                      Group byConnect By, Start with, Order by E٣
                                                                        InsertValues E٤
                                                                          UpdateSet E٥


INSERT,        ‫א‬  ‫א‬    ‫א‬‫א‬  
                K‫א‬‫א‬‫א‬UPDATE, DELETE




                                                     - ٧٧ -
       ‫א‬‫א‬                                ٢٦٢                         ‫א‬‫א‬
  ‫א‬                  ‫א‬                      ‫א‬
                                                   

                                           Remove Server side functions‫א‬‫א‬‫א‬
 Drop‫א‬ SQL  SQL*Plus‫א‬‫א‬ ‫א‬
                                                                                        W‫א‬‫א‬
         Drop Function get_sal;
         Function dropped.

Drop function  DDL  Rollback ‫א‬     W
                                  K‫א‬‫א‬‫א‬‫א‬
                                                                                                    
                                                                           ‫א‬‫א‬‫א‬
                                                                                                    
                                             ‫א‬‫א‬                                 ‫א‬
             Expression                         PL/SQL
         RETURN data type                                 
        ‫א‬       ‫א‬‫א‬
                                                                                 

                                                                       W‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                             ‫א‬ K١
                                                            ‫א‬ K٢
                                                                     ‫א‬ K٣




                                                   - ٧٨ -
       ‫א‬‫א‬                           ٢٦٢                           ‫א‬‫א‬
  ‫א‬             ‫א‬                        ‫א‬
                                              


                                                                                           
                                                                                                
                                                                                            E١
                                                                                
‫א‬‫א‬‫א‬ PROD_DESC‫א‬ ‫א‬‫ א‬K
                                                  PRODUCTS‫א‬‫א‬
                                                                                               
                                                                      K‫א‬‫א‬‫א‬ K
                                                  KDescSQL*Plus‫א‬ K
                                                                                       
                                                        KDesc‫א‬‫א‬‫א‬‫א‬ K
                                                                                              
                                                                                                   
                                                                                                   
                                                                                             E٢
                                           
   empno ‫א‬   ‫א‬‫א‬  Annual_Sal ‫א‬ ‫א‬ ‫ א‬K
                                                                                           
   ‫א‬ ‫א‬   ، ‫א‬ ‫א‬  ‫א‬
                  KNULLcomm‫א‬‫א‬،(sal*12)+comm
                                                                                    
 ، ‫א‬ ‫א‬ ‫א‬ ‫א‬ ،‫א‬   ‫א‬  ‫ א‬K
                                 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬،‫א‬




                                              - ٧٩ -
                              ‫א‬‫א‬‫א‬
                         ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹]
                               ‫א‬‫א‬‫א‬‫א‬




         ‫א‬
               ‫א‬
‫א‬




٧
 ‫א‬‫א‬                         ٢٦٢                               ‫א‬‫א‬
    ‫א‬                        ‫א‬                            ‫א‬

                                      

                                                                                     W‫א‬‫א‬

                                                           
   K‫א‬‫א‬‫א‬
                                                                                    W‫א‬‫א‬

                                            W‫א‬،‫א‬

                                                        ‫א‬‫א‬ K١
                                                        ‫א‬‫א‬ K٢
                                                        ‫א‬‫א‬ K٣
                                                  ‫א‬‫א‬ K٤
                                                                                                
                                                                   W‫א‬‫א‬‫א‬
                                             ٪١٠٠‫א‬‫א‬‫א‬
                                                                                                
                                                      ٨W‫א‬‫א‬
                                                                                                
                                                                             W‫א‬‫א‬
                                                                         •
                                                         Projector‫א‬ •
                                                                                     •
                                                                                         •




                                      - ٨٠ -
       ‫א‬‫א‬                             ٢٦٢                               ‫א‬‫א‬
          ‫א‬                            ‫א‬                            ‫א‬

                                                

                                                                                    W‫א‬‫א‬
                                                                             ‫א‬‫א‬
،‫א‬  Database Trigger‫א‬‫א‬
‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ 
                                                                                           K‫א‬
      Application Trigger ‫א‬     
   KForms Builder‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬،‫א‬
‫א‬،Views ‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬ ‫א‬DML
                                                                    Kinstead of ??


                                                                                   ‫א‬‫א‬
                                        W‫א‬‫א‬‫א‬Trigger‫א‬
                       K‫א‬‫ א‬K١
                                        
  ‫א‬   ‫א‬ ‫א‬ ‫א‬  ‫א‬ ‫א‬  ‫ א‬K٢
                                                                  K‫א‬‫א‬‫א‬‫א‬
،‫א‬‫א‬   ‫א‬  K٣
                                                                          
Constraint   Primary key ‫א‬‫א‬ 
                                                                     K‫א‬
 ‫א‬ ‫א‬‫א‬‫א‬‫א‬‫ א‬K٤
                                                                         K‫א‬
                                                                                                       




                                                - ٨١ -
      ‫א‬‫א‬                                 ٢٦٢                             ‫א‬‫א‬
         ‫א‬                                ‫א‬                          ‫א‬

                                                   

                                                                                            ‫א‬
‫א‬‫א‬‫א‬‫א‬ 
‫א‬‫א‬،‫א‬‫א‬‫א‬،‫א‬،‫א‬‫א‬
            W‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬‫א‬
                                                                                                        
         ‫א‬‫א‬                          ‫א‬                             ‫א‬
           Before              
                                       ‫א‬  
           After                                                       Trigger Timing‫א‬
                                             K‫א‬
         Instead of         
                                      ‫א‬   
         Insert             ‫א‬
                                      ‫א‬  ‫א‬  DML Trigger Event ‫א‬
       Update               
                                        view ‫א‬
           Delete            
                                                                ‫א‬
                    
                       Statement ‫א‬             Trigger Type ‫א‬

    Row        
                                        ‫א‬ ‫א‬         Trigger body ‫א‬
               PL/SQL 
                                                                ‫א‬
                                                                                                        
‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                      K‫א‬E‫א‬F




                                                   - ٨٢ -
       ‫א‬‫א‬                                 ٢٦٢                              ‫א‬‫א‬
          ‫א‬                                ‫א‬                           ‫א‬

                                                    

                                                                                                ‫א‬
      W‫א‬،‫א‬‫א‬‫א‬‫א‬
،‫א‬‫א‬‫א‬W Before K١
                                                                      W‫א‬‫א‬‫א‬‫א‬
Trigger     ‫א‬ ‫א‬      ‫א‬  •
 ‫א‬ ‫א‬ ‫א‬  K     Firing
‫א‬ ‫א‬‫א‬‫א‬
          KRollback‫א‬‫א‬‫א‬‫א‬K‫א‬
                            K‫א‬‫א‬‫א‬ •
                                                                                                   
،‫א‬‫א‬‫א‬WAfter                      K٢
                                                        W‫א‬‫א‬‫א‬‫א‬
     ‫א‬‫א‬‫א‬‫א‬ •
??‫א‬‫א‬  ??  •
                                                                                 K‫א‬
‫א‬‫א‬ ‫א‬WInstead of K٣
‫א‬  views ‫א‬ ‫א‬   ‫א‬   K   ‫א‬
                                                    K‫א‬‫א‬
  ،‫א‬  ‫א‬   ‫א‬ ? ?   ‫א‬  
?‫א‬‫א‬ Insert, update, delete
                                                                                K‫א‬‫א‬?




                                                    - ٨٣ -
      ‫א‬‫א‬                            ٢٦٢                              ‫א‬‫א‬
         ‫א‬                           ‫א‬                           ‫א‬

                                              

                                                                        ‫א‬‫א‬‫א‬
K‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬
               Kinsert ,update or delete‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬update ‫א‬‫א‬‫א‬‫א‬
 ‫א‬ ‫א‬ 
                                                                                              K‫א‬
                                                                                                    
     . . . . UPDATE OF sal . . . .

                                                                                                    
          Ksal‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                   W‫א‬‫א‬
     . . . . INSERT or UPDATE or DELETE . . . .



     . . . . INSERT or UPDATE OF jobs . . . .



                                                                                          W‫א‬
 ‫א‬W‫א‬‫א‬ ‫א‬‫א‬‫א‬
 ‫א‬   EUPDATE     F ‫א‬    
                         K‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                    
،‫א‬‫א‬‫א‬W K١
‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬
                                                                K‫א‬


                                              - ٨٤ -
       ‫א‬‫א‬                               ٢٦٢                             ‫א‬‫א‬
          ‫א‬                              ‫א‬                          ‫א‬

                                                  

‫א‬‫א‬ ‫א‬‫א‬W K٢
‫א‬،‫א‬
‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬‫א‬
                                                K‫א‬‫א‬‫א‬
                                                                                                   
                                     KDEPT‫א‬‫א‬‫א‬‫ א‬W
SQL> INSERT INTO dept (deptno, dname, loc)

        VALUES (50, ‘EDUCATION’, ‘NEW YORK’);

K‫א‬‫א‬‫א‬‫א‬ ‫א‬
??   ،‫א‬     ‫א‬ ?? ‫א‬    ‫א‬ 
‫א‬??‫א‬‫א‬??‫א‬‫א‬
‫א‬    ‫א‬ ‫א‬      K‫א‬   
 ‫א‬‫א‬‫א‬‫א‬‫א‬
                                                            K‫א‬

                                                                                    Before Statement
                                                                                                         
DEPT table

DEPTNO                 DNAME            LOC
                                                                                Before Row trigger
10                     RESEARCH         DALLAS
                                                                                 After Row trigger
20                     SALES            CHICAGO

30                     OPERATIONS BOSTON                                      After Statement trigger
                          Firing Sequence of Database Triggers on a Single                               




                                                  - ٨٥ -
      ‫א‬‫א‬                           ٢٦٢                          ‫א‬‫א‬
         ‫א‬                          ‫א‬                       ‫א‬

                                             

          K٣٠‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬W


     SQL> UPDATE emp

             SET sal = sal * 1.1

             WHERE deptno = 30;



‫א‬ ‫א‬‫א‬‫א‬
، ‫א‬‫א‬??‫א‬، ‫א‬
‫א‬??‫א‬‫א‬??
 K‫א‬‫א‬??KUPDATE‫א‬
                                                                                               
                                                                                               
                                                                 Before Statement trigger


    EMPNO             ENAME          DEPTNO                            Before Row trigger
                                                                        After Row trigger
    7839              KING
                                                                       Before Row trigger
    7698              BLAKE                                             After Row trigger
                                                                      Before Row trigger
    7788              SMITH                                            After Row trigger


                                                                    After Statement trigger



             ‫א‬‫א‬‫א‬




                                             - ٨٦ -
       ‫א‬‫א‬                               ٢٦٢                           ‫א‬‫א‬
          ‫א‬                              ‫א‬                        ‫א‬

                                                  

                                                                                         W‫א‬
                      
 ‫א‬ ‫א‬‫א‬‫א‬‫א‬
،PL/SQL SQL‫א‬‫א‬،
                                                   K،‫א‬‫א‬،‫א‬‫א‬،‫א‬‫א‬
‫א‬‫א‬‫א‬ ‫א‬ 
Correlation ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬

anonymous ‫א‬‫א‬ Knames

                                                                     W‫א‬‫א‬PL/SQL Block
     [DECLARE]

     BEGIN

     [EXCEPTION]
     END;

                                                                     W‫א‬
     CREATE [OR REPLACE] TRIGGER trigger_name

     Timing event1 [OR timing event2 Or timing event3]

     ON table_name
     PL/SQL block;



                                                       ‫א‬‫א‬‫א‬‫א‬        ‫א‬‫א‬
                      ‫א‬‫א‬‫א‬‫א‬        ‫א‬
                              ‫א‬‫א‬‫א‬          ‫א‬
                                        ‫א‬‫א‬‫א‬‫א‬  ‫א‬‫א‬
‫א‬  ،‫א‬    ‫א‬ ‫א‬‫א‬ ‫א‬  ‫א‬ ‫א‬   ‫א‬‫א‬
                    ENDBEGINDECLARE‫א‬‫א‬
                                                  - ٨٧ -
      ‫א‬‫א‬                            ٢٦٢                          ‫א‬‫א‬
         ‫א‬                           ‫א‬                       ‫א‬

                                              

                                                           K??W
                                                                                                
     SQL> CREATE OR REPLACE TRIGGER secure_emp

             BEFORE INSERT ON emp

             BEGIN
               IF (TO_CHAR (sysdate,’DY’) IN (‘THU’,’FRI’)) OR

                      (TO_CHAR (sysdate, ‘HH24’) NOT BETWEEN ‘08’ AND ‘18’)

               THEN RAISE_APPLAICATION_ERROR (-20500,
               ‘You may only insert into EMP during normal hours.’);

               END IF;

              END;
              /

                                                                                                
‫א‬??‫א‬‫א‬‫א‬،
                                 K‫א‬‫א‬‫א‬‫א‬
،‫א‬ ‫א‬
  ‫א‬ ‫א‬K‫א‬‫א‬‫א‬
                                      
  ‫א‬‫א‬  ‫א‬‫א‬
‫א‬K‫א‬‫א‬‫א‬‫א‬
                                                                          KRollback‫א‬
Server side built in‫א‬ RAISE_APPLICATION_ERROR ‫א‬
            K‫א‬‫א‬‫א‬‫א‬‫א‬




                                              - ٨٨ -
      ‫א‬‫א‬                          ٢٦٢                       ‫א‬‫א‬
         ‫א‬                         ‫א‬                    ‫א‬

                                            

                                                                                           
     SQL> INSERT INTO emp (empno, ename, deptno)

            2 VALUES            (77, ‘ALI’ 40);

     INSERT INTO emp (empno, ename, deptno)

                      *
     ERROR at line 1:

     ORA-20500: You may only insert into EMP during normal hours.

     ORA-06512: at “SCOTT.SECURE_EMP”, line 4
     ORA04088: error during execution of trigger ‘SCOTT.SECURE_EMP’

                                                                                           
    KORACLE‫א‬‫א‬W
                                                                                           
                                   W Using Conditional Predicates‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
 K‫א‬‫א‬‫א‬‫א‬INSERTING, DELETING, UPDATING‫א‬‫א‬
                                                                         
EMP‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬W
                                                                   K‫א‬‫א‬




                                            - ٨٩ -
 ‫א‬‫א‬                                ٢٦٢                      ‫א‬‫א‬
     ‫א‬                              ‫א‬                     ‫א‬

                                             

                                                                                            
SQL> CREATE OR REPLACE TRIGGER secure_emp

         BEFORE INSERT OR UPDATE OR DELETE ON emp

         BEGIN

         IF (TO_CHAR(sysdate, ‘DY’) IN (‘THU’,’FRI’)) OR
                (TO_CHAR (sysdate, ‘HH24’) NOT BETWEEN ‘08’ and ‘18’)

         THEN

               IF INSERTING THEN
                 RAISE_APPLICATION_ERROR (-20500,

                         ‘You may only insert into EMP during normal hours’);

               ELSIF DELETING
                 RAISE_APPLICATION_ERROR (-20502,

                         ‘You may only delete from EMP during normal hours’);

               ELSIF UPDATING(‘SAL’)
                 RAISE_APPLICATION_ERROR (-20503,

                         ‘You may only update SAL during normal hours’);

               ELSE

                 RAISE_APPLICATION_ERROR (-20504,

                         ‘You may only update EMP during normal hours’);

               END IF;

       END IF;

    END;

/


                                             - ٩٠ -
       ‫א‬‫א‬                              ٢٦٢                             ‫א‬‫א‬
          ‫א‬                            ‫א‬                            ‫א‬

                                                 

    ‫א‬    ‫א‬ ‫א‬ ‫א‬   ??   
                                                                                K‫א‬
                                                                                                      
‫א‬‫א‬‫א‬،‫א‬  ‫א‬W
                              W‫א‬‫א‬‫א‬‫א‬‫א‬،
USER         TABLENAME COLUMN INS                         UPD     DEL      MAX        MAX        MAX

_NAME                            _NAME                                     _INS       _UPD       _DEL

SCOTT        EMP                             1            1       1        5          5          5

SCOTT        EMP                 SAL                      1                           5

JONES        EMP                             0            0       0        5          0          0

                                                                                     
‫א‬ SAL‫א‬‫א‬ ‫א‬‫א‬،‫א‬ 
                                                               K‫א‬‫א‬‫א‬‫א‬‫א‬
     SQL> CREATE OR REPLACE TRIGGER check_salary_count

              AFTER UPDATE OF sal ON EMP

              DECLARE

                    V_salary_changes NUMBER;

                    V_max_changes      NUMBER;

              BEGIN

                       SELECT upd, max_upd

                       INTO v_salary_changes, v_max_changes

                       FROM audit_table

                       WHERE user_name = user
                         AND tablename = ‘EMP’

                         AND column_name = ‘SAL’;
                                                 - ٩١ -
     ‫א‬‫א‬                              ٢٦٢                           ‫א‬‫א‬
        ‫א‬                             ‫א‬                        ‫א‬

                                               

                     IF v_salary_changes > v_max_changes THEN

                       RAISE_APPLICATION_ERROR (-20501,

                       ‘ You may only make a maximum of ‘ ||



                      TO_CHAR (v_max_changes) || ‘ changes to the SAL column’);
                  END IF;

           END;

/



                                                                  W‫א‬
    CREATE [OR REPLACE] TRIGGER trigger_name

    Timing event1 [OR timing event2 Or timing event3]

    ON table_name
     [ REFERENCING OLD AS OLD | NEW AS new]

    FOR EACH ROW

     [WHEN condition]

    PL/SQL block;



                                                                                                  
                                            ‫א‬‫א‬‫א‬‫א‬                ‫א‬‫א‬
           ‫א‬‫א‬‫א‬‫א‬               ‫א‬
                       ‫א‬‫א‬‫א‬              ‫א‬
                                ‫א‬‫א‬‫א‬‫א‬             ‫א‬‫א‬
            ‫א‬‫א‬‫א‬‫א‬                       ‫א‬
                                               - ٩٢ -
 ‫א‬‫א‬                             ٢٦٢                         ‫א‬‫א‬
    ‫א‬                            ‫א‬                      ‫א‬

                                          

                  K‫א‬‫א‬‫א‬            
                                                                    FOR EACH ROW



                                                                             
   ‫א‬‫א‬‫א‬‫א‬‫א‬W
                                               K‫א‬‫א‬،
                                                                                           
SQL> CREATE OR REPLACE TRIGGER audit_emp

        AFTER DELETE OR INSERT OR UPDATE ON emp

        FOR EACH ROW

        BEGIN

              IF DELETING THEN

                 UPDATE audit_table SET del = del + 1

                 WHERE user_name = user AND tablename = ‘EMP’
                     AND col_name IS NULL;

              ELSIF INSERTING THEN

                 UPDATE audit_table SET ins = ins + 1
                 WHERE user_name = user AND tablename = ‘EMP’

                     AND col_name IS NULL;

              ELSIF UPDATING(‘SAL’) THEN
                 UPDATE audit_table SET upd = upd + 1

                 WHERE user_name = user AND tablename = ‘EMP’

                     AND col_name = ‘SAL;

              ELSE

                 UPDATE audit_table SET upd = upd + 1
                                          - ٩٣ -
       ‫א‬‫א‬                               ٢٦٢                      ‫א‬‫א‬
            ‫א‬                            ‫א‬                   ‫א‬

                                                  

                         WHERE user_name = user AND tablename = ‘EMP’

                             AND col_name IS NULL;

                      END IF;

                  END; /

                                                                   
          AUDIT_EMP_TABLEEMP‫א‬‫א‬W
‫א‬ ‫א‬  ‫א‬   KEMP   ‫א‬   ‫א‬ ‫א‬ 
                                                                     K‫א‬‫א‬
                                                                                                
      SQL> CREATE OR REPLACE TRIGGER audit_emp_values

                AFTER DELETE OR INSERT OR UPDATE ON emp

                FOR EACH ROW

                BEGIN

                       INSERT INTO audit_emp_table
                                (user_name, timestamp, id, old_last_name, new_last_name,

                                old_title, new_title, old_salary, new_salary)

                       VALUES (USER, SYSDATE, :OLD.empno, :OLD.ename,
                                 :NEW.ename, :OLD.job, :NEW.job, :OLD.sal,

                                 :NEW.sal);

                 END;
      /

                                                                   
                                  Audit_Emp_Table‫א‬‫א‬‫א‬
USER_NAME TIMESTAMP ID                                OLD_LAST_NAME NEW_LAST_NAME

SCOTT                  12-NOV-97      NULL            NULL                 HUSTON

ALI                    10-DEC-97      7844            MAGE                 TURNER
                                                  - ٩٤ -
       ‫א‬‫א‬                               ٢٦٢                        ‫א‬‫א‬
          ‫א‬                              ‫א‬                     ‫א‬

                                                  



     OLD_TITLE                 NEW_TITLE               OLD_SALARY          NEW_SALARY

     NULL                      ANALYST                 NULL                3500

     CLERK                     SALESMAN                1100                1100

                                                                                                  
 ‫א‬ ‫א‬       ‫א‬  ،  ‫א‬ ‫א‬ 
                                                   KNEWOLD‫א‬‫א‬‫א‬
                                                                                                  
                           ‫א‬‫א‬              ‫א‬‫א‬              ‫א‬
                            ‫א‬‫א‬                      NULL      INSERT‫א‬

                        ‫א‬‫א‬      ‫א‬‫א‬     UPDATE‫א‬
                                   NULL          ‫א‬‫א‬        DELETE‫א‬


                       ‫א‬‫א‬NewOLD‫• א‬
            PL/SQLSQLEWF،‫א‬‫א‬‫א‬ •
                          KWHEN‫א‬‫א‬‫א‬‫א‬‫א‬EWF •


                         
، ‫א‬‫א‬ ‫א‬
                                                                                   KWhen clause
                                                                   
 Employee’s Commission‫א‬ EMP‫א‬ ‫א‬ W
                                                        K‫א‬




                                                  - ٩٥ -
 ‫א‬‫א‬                           ٢٦٢                  ‫א‬‫א‬
     ‫א‬                         ‫א‬               ‫א‬

                                        

                                                                                  
SQL> CREATE OR REPLACE TRIGGER derive_commmission_pct

         BEFORE INSERT OR UPDATE OF sal ON emp

         FOR EACH ROW

         WHEN (NEW.job = ‘SALESMAN’)
         BEGIN

               IF INSERTING THEN

                  :NEW.comm := 0;
               ELSIF :OLD.comm IS NULL THEN

                  :NEW.comm := 0;

               ELSE
                  :NEW.comm := :OLD.comm * (:NEW.sal/:OLD.sal);

               END IF;

        END;
/

                                                                                  




                                        - ٩٦ -
      ‫א‬‫א‬                                   ٢٦٢                            ‫א‬‫א‬
         ‫א‬                                ‫א‬                           ‫א‬

                                                     

                                                                  ‫؟‬‫א‬‫א‬‫א‬
                              W‫א‬‫א‬‫א‬‫א‬
                       ‫א‬                                               ‫א‬
            CREATE TRIGGER‫א‬‫ א‬K١                             CREATE PROCEDURE‫א‬‫ א‬K١



Data Dictionary ‫א‬   K٢               Data Dictionary ‫א‬   K٢
‫א‬ Source ‫א‬ ‫א‬                    ‫א‬ Source ‫א‬ ‫א‬ 
                                      p_code                                                  p_code



                   EF‫א‬ K٣                   EF‫א‬ K٣


Transaction  ‫א‬   K٤                       Transaction  ‫א‬   K٤
 Commit, Savepoint   Processing                   Commit, Savepoint   Processing
 K‫א‬‫א‬Rollback                         ‫א‬‫א‬Rollback
                                                 


              ‫א‬                                           ‫א‬
                                                 
                   EF‫א‬ K١                   EF‫א‬ K١


Transaction Processing‫א‬ Transaction  ‫א‬   K٢
Rollback  Commit, Savepoint    Commit, Savepoint   Processing
                K‫א‬‫א‬                  ‫א‬‫א‬Rollback


                                                     - ٩٧ -
      ‫א‬‫א‬                             ٢٦٢                           ‫א‬‫א‬
         ‫א‬                            ‫א‬                        ‫א‬

                                               

‫א‬ p_code‫א‬‫א‬
‫א‬‫א‬‫א‬ ‫א‬  ، Data Dictionary
         K‫א‬K‫א‬
                                                                         ‫א‬‫א‬‫א‬
 EnabledW‫א‬
‫א‬،‫א‬‫א‬KDisabled
Data base integrity ‫א‬‫א‬‫א‬ 

 ALTER TRIGGER ‫א‬‫א‬‫א‬‫א‬KConstraints
                                                                                  W
                                                                                                  
                                                              W‫א‬
     ALTER TRIGGER trigger_name DISABLE | ENABLED

                                                                                                  
                                   W‫א‬‫א‬‫א‬
     ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS

                                                                                                  
                                              W‫א‬‫א‬
     ALTER TRIGGER trigger_name COMPILE

                                  
                    K‫א‬‫א‬‫א‬




                                               - ٩٨ -
       ‫א‬‫א‬                             ٢٦٢                            ‫א‬‫א‬
          ‫א‬                            ‫א‬                         ‫א‬

                                                 

                                                                                           
   ‫א‬  ‫א‬   ‫א‬        
                                                      W‫א‬‫א‬‫א‬،‫א‬
     DROP TRIGGER trigger_name

                                                                                               W
     SQL> DROP TRIGGER secure_emp;



                                                                               W‫א‬
        W‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬  Foreign Key‫א‬‫א‬،Primary Key ‫א‬‫א‬ K١
                             K‫א‬‫א‬‫א‬‫א‬Unique Key‫א‬
  W‫א‬‫א‬Mutating‫א‬،Mutating‫א‬‫ א‬K٢
‫א‬K‫א‬ ‫א‬،‫א‬‫א‬ ‫א‬ ‫א‬ •
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ 
                                                                                   K‫א‬
Foreign ‫א‬‫א‬‫א‬ ‫א‬ •

                                                                                     KKey
                                                                                               W
                                                                                         
     ‫א‬     ،  ‫א‬   ‫א‬ ‫א‬
                          K‫א‬‫א‬‫א‬‫א‬‫א‬




                                                - ٩٩ -
        ‫א‬‫א‬                                ٢٦٢                       ‫א‬‫א‬
           ‫א‬                               ‫א‬                    ‫א‬

                                                    

                                                                                                   
   SQL> CREATE OR REPLACE TRIGGER check_salary

               BEFORE INSERT Or UPDATE OF sal, job ON emp

               FOR EACH ROW

               WHEN (:new.job <> ‘PRESIDENT’)
               DECLARE

                     v_minsalary emp.sal%type;

                 v_maxsalary emp.sal%type;
               BEGIN

                     SELECT MIN(sal), MAX(sal)

                     INTO v_minsalary, v_maxsalary
                     FROM emp

                     WHERE job = :new.job;

                     IF :new.sal < v_minsalary OR :new.sal > v_maxsalary THEN
                         RAISE_APPLICATION_ERROR (-20505, ‘out of range’);

                     END IF;

               END;

   /



 K‫א‬‫א‬،‫א‬
                        WK‫א‬‫א‬،‫א‬‫א‬‫א‬




                                                   - ١٠٠ -
      ‫א‬‫א‬                          ٢٦٢                       ‫א‬‫א‬
         ‫א‬                         ‫א‬                    ‫א‬

                                            

                                                                                           
      SQL> UPDATE emp

              SET sal = 1500

             WHERE ename = ‘SMITH’;

      ERROR at line 2
      ORA-04091: table EMP is mutating, trigger/function may not see it

      ORA-06512: at “CHECK_SALARY”, line 5

      ORA-04088: error during execution of trigger ‘CHECK_SALARY’

                                                                                           
mutating‫א‬‫א‬،‫א‬ ‫א‬‫א‬
                                       K‫א‬KEMP
                                                                                           
                                                                                           
                                                                                           
                                                                                           
                                                                                           
                                                                                           




                                           - ١٠١ -
       ‫א‬‫א‬                            ٢٦٢                         ‫א‬‫א‬
          ‫א‬                           ‫א‬                      ‫א‬

                                               

                                                                                         W
                                                                                           E١
 ٨W٠٠‫א‬،‫א‬‫א‬‫א‬ ‫א‬‫א‬
                               W‫א‬K‫א‬‫א‬٦W٠٠‫א‬
                                                                                                
    ‫א‬ SECURE_DML  ‫א‬      K١
                                           W،‫א‬‫א‬‫א‬‫א‬
                           ?‫א‬‫א‬‫א‬‫א‬?
‫א‬ ‫א‬ ‫א‬  ‫א‬ PRODUCT      ‫א‬  K٢
                                                                            
                                                                                     
                                                                                                
                                                                                           E٢
      COMM   SALESMAN ‫א‬    ‫א‬
 ‫א‬    EMP ‫א‬    ‫א‬   K ‫א‬
                                                              KCUSTOMER‫א‬
‫א‬   ‫א‬‫א‬ ‫א‬ K‫א‬      ‫ א‬K١
‫א‬‫א‬‫א‬‫א‬‫א‬،‫א‬‫א‬،customer ID
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬
،‫א‬‫א‬‫א‬EMP‫א‬‫א‬‫א‬
                                                         K٪٥‫א‬‫א‬
                                                                                 
     ‫א‬‫א‬‫א‬‫א‬ORD‫א‬‫א‬
                                                                 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬




                                              - ١٠٢ -
                       ٢٦٢                   ‫א‬‫א‬
                       ‫א‬                ‫א‬




SQL> DESCRIBE emp;



Name                          Null?          Type
--------------------          --- ------     --------------------
EMPNO                         NOT NULL       NUMBER(4)
ENAME                                        VARCHAR2 (10)
JOB                                          VARCHAR2 (9)
MGR                                          NUMBER (4)
HIREDATE                                     DATE
SAL                                          NUMBER (7,2)
COMM                                         NUMBER (7,2)
DEPTNO                        NOT NULL       NUMBER (2)




SQL> DESCRIBE dept;



Name                          Null?          Type
--------------------          --- ------     --------------------
DEPTNO                        NOT NULL       NUMBER(2)
DNAME                                        VARCHAR2 (14)
LOC                                          VARCHAR2 (13)




                          - ١٣٤ -
                          ٢٦٢                   ‫א‬‫א‬
                          ‫א‬                ‫א‬




SQL> DESCRIBE salgrade;



Name                             Null?          Type
--------------------             --- ------     --------------------
GRADE                                           NUMBER(4)
LOSAL                                           VARCHAR2 (10)
HISAL                                           VARCHAR2 (9)




SQL> DESCRIBE ord;



Name                             Null?          Type
--------------------             --- ------     --------------------
ORDID                            NOT NULL       NUMBER(4)
ORDERDATE                                       DATE
COMMPLAN                                        VARCHAR2 (1)
CUSTID                           NOT NULL       NUMBER (6)
SHIPDATE                                        DATE
TOTAL                                           NUMBER (8,2)




                             - ١٣٥ -
                             ٢٦٢                 ‫א‬‫א‬
                           ‫א‬                ‫א‬




SQL> DESCRIBE product;



Name                                Null?        Type
------------------------------      --- ------   --------------------
PRODID                              NOT NULL     NUMBER(6)
DESCRIP                                          VARCHAR2 (30)




SQL> DESCRIBE item;



Name                                Null?        Type
------------------------------      --- ------   --------------------
ORDID                               NOT NULL     NUMBER(4)
ITEMID                              NOT NULL     NUMBER(4)
PRODID                                           NUMBER(6)
ACTUALPRICE                                      NUMBER(8,2)
QTY                                              NUMBER(8)
ITEMTOT                                          NUMBER(8,2)




                              - ١٣٦ -
                             ٢٦٢                 ‫א‬‫א‬
                           ‫א‬                ‫א‬



                            ‫א‬


SQL> DESCRIBE customer;



Name                                Null?        Type
------------------------------      --- ------   --------------------
CUSTID                              NOT NULL     NUMBER(6)
NAME                                NOT NULL     VARCHAR2 (45)
ADDRESS                                          VARCHAR2 (40)
CITY                                             VARCHAR2 (30)
STATE                                            VARCHAR2 (2)
ZIP                                              VARCHAR2 (9)
AREA                                             NUMBER (3)
PHONE                                            VARCHAR2 (9)
PERID                               NOT NULL     NUMBER (4)
CREDITLIMIT                                      NUMBER (9,2)
COMMENTS                                         LONG




SQL> DESCRIBE price;



Name                                Null?        Type
------------------------------      --- ------   --------------------
PRODID                              NOT NULL     NUMBER(6)
STDPRICE                                         NUMER (8,2)
MINPRICE                                         NUMBER (8,2)
STARTDATE                                        DATE
ENDDATE                                          DATE




                              - ١٣٧ -
      ‫א‬                              ٢٦٢                     ‫א‬‫א‬
                                     ‫א‬                  ‫א‬



                                      ‫א‬‫א‬
                                                                   ‫א‬‫א‬‫א‬
                                                                                         
‫א‬‫א‬–‫א‬‫א‬‫א‬،‫א‬،8i/8‫א‬‫א‬ •
                                                                       K١٤٢٢


                                                                    ‫א‬‫א‬‫א‬
                                                                                         
               Introduction to SQL and PL/SQL, Oracle University book, volume 2      •

                          Develop PL/SQL Program Units, Oracle University book •




                                        - ١٣٩ -
        ‫א‬        ٢٦٢                         ‫א‬‫א‬
                   ‫א‬                      ‫א‬



                    ‫א‬


 ١                                           ‫א‬‫א‬W‫א‬‫א‬
 ٢                              ‫א‬‫א‬‫א‬
 ٢                                                            ‫א‬‫א‬
 ٣                                                         ‫א‬‫א‬‫א‬
 ٤                                                       ‫א‬
 ٦                                                       ‫א‬
 ٧                                                   ‫א‬‫א‬‫א‬
 ٨                       ‫א‬‫א‬‫א‬‫א‬
 ٨                                                   ‫א‬‫א‬‫א‬
 ١٠                                                                   
 ١٣                                      ‫א‬‫א‬‫א‬W‫א‬‫א‬
 ١٤                                        ‫א‬‫א‬‫א‬
 ١٥                                                        ‫א‬
 ١٦                                                             ‫א‬
 ١٦                                                ‫א‬‫א‬
 ١٧                                                           ‫א‬‫א‬
 ١٨                                            ‫א‬‫א‬
 ١٩                                                     ‫א‬‫א‬‫א‬
 ٢٠                                       For‫א‬‫א‬‫א‬
 ٢٢                                                               
 ٢٥                                      ‫א‬‫א‬W‫א‬‫א‬
 ٢٥                                      ‫א‬
 ٢٦                                                     ‫א‬‫א‬‫א‬
 ٢٧                                                    ‫א‬‫א‬
 ٢٩                                               ‫א‬‫א‬
        ‫א‬        ٢٦٢                            ‫א‬‫א‬
                   ‫א‬                         ‫א‬



 ٣٠                                ‫א‬‫א‬

 ٣١                        RAISE_APPLICATION_ERROR‫א‬
 ٣٢                                                                       
 ٣٦                                             ‫א‬‫א‬W‫א‬‫א‬‫א‬
 ٣٦                                                                       
 ٣٧                                                   ‫א‬‫א‬‫א‬
 ٣٧                                                     ‫א‬
 ٣٨                                                     ‫א‬‫א‬
 ٣٩                                         ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
 ٤٢                                                   ‫א‬W‫א‬‫א‬
 ٤٣                                                         ‫א‬‫א‬
 ٤٤                                                    ‫א‬‫א‬
 ٤٩                                                       ‫א‬‫א‬
 ٥٠                          ‫א‬‫א‬Default‫א‬‫א‬‫א‬‫א‬
 ٥٠                                      ‫א‬‫א‬‫א‬
 ٥١                                               ‫א‬‫א‬
 ٥٢                                                                       
 ٥٣                                                   ‫א‬‫א‬W‫א‬‫א‬
 ٥٤                                                          ‫א‬‫א‬‫א‬‫א‬
 ٥٦                                                         ‫א‬‫א‬‫א‬
 ٥٦                                                        ‫א‬‫א‬
 ٥٨                                                 ‫א‬‫א‬‫א‬
 ٥٨                                            ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
 ٥٩                                                                       
 ٦١                                       ‫א‬‫א‬‫א‬W‫א‬‫א‬
 ٦١                                                     ‫א‬‫א‬
 ٦٢                                                                ‫א‬
      ‫א‬        ٢٦٢                          ‫א‬‫א‬
                 ‫א‬                       ‫א‬



٦٧                                     ‫א‬
٦٩                                             ‫א‬‫א‬‫א‬‫א‬


٧٢                                     ‫א‬
٧٧                                ‫א‬‫א‬‫א‬
٧٩                                                             ‫א‬
٧٩                                                    ‫א‬
٨٢                                                                    
٨٣                                                                  ‫א‬
                                

                                

                                




                                

                                

                                

                                

                                

                                

        ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

     ‫א‬E‫א‬F‫א‬‫א‬
GOTEVOT appreciates the financial support provided by BAE SYSTEMS

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:4/2/2013
language:
pages:103