Docstoc

تصميم قواعد البيانات - برمجيات

Document Sample
تصميم قواعد البيانات - برمجيات Powered By Docstoc
					                     ‫א‬‫א‬‫א‬
                  ã¹]<gè…‚jÖ]æ<ËÖ]<Üé×Ãj×Ö<íÚ^ÃÖ]<퉉ö¹]
                       ‫א‬‫א‬‫א‬‫א‬




      
‫א‬‫א‬

      ١٦٢
                                           ‫א‬‫א‬                          ‫א‬
        
                                                ١٦٢                                  



                                              
       W،،‫א‬‫א‬،‫א‬
                                                                                               

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
  ‫א‬ ‫א‬  ،‫א‬    ‫א‬ ‫א‬ ‫א‬ ‫א‬ ‫א‬ 
‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬
،‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                      
                                                                    K‫א‬‫א‬‫א‬
                                                                                               

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
  ‫א‬        ،‫א‬ ‫א‬‫א‬  
‫א‬  ‫א‬ ‫א‬ ‫א‬ ‫א‬ ‫א‬   ‫א‬    ، 
‫א‬،‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
     ‫א‬  ‫א‬   ،‫א‬   
                                    K‫א‬‫א‬،‫א‬‫א‬
                                                                                               

‫א‬???‫א‬‫א‬?‫א‬‫א‬
                             K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                               

‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
،‫א‬،‫א‬‫א‬‫א‬‫א‬
                          K‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                               

،‫א‬‫א‬‫א‬‫א‬
                                                                                               K‫א‬
                                                                                               

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




                                 ‫א‬‫א‬
                              ‫א‬‫א‬
‫א‬‫א‬




١
     ‫א‬‫א‬                              ‫א‬‫א‬                    ‫א‬
‫א‬‫א‬                         ١٦٢                            



                                                                                           W‫א‬‫א‬
                                 ‫א‬‫א‬‫א‬
                                                                                         W‫א‬‫א‬
                                                 ‫א‬‫א‬ K١
                                              ‫א‬‫א‬‫א‬ K٢
                                                                                                      
                                                                              W‫א‬‫א‬‫א‬
                               K٪١٠٠‫א‬‫א‬‫א‬
                                                                                                      
                                                                                W‫א‬‫א‬
                                                                                    
                                                                                                      
                                                                                    W‫א‬‫א‬
                                                                                   H
                                                                                                      
                                                                                   W‫א‬‫א‬
                               K‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                      




                                                      -١-
            ‫א‬‫א‬                               ‫א‬‫א‬                              ‫א‬
       ‫א‬‫א‬                          ١٦٢                                      



                                                                              ‫א‬
     ‫א‬‫א‬‫א‬‫א‬‫א‬W‫א‬
                                                                          
     ‫א‬‫א‬‫א‬KK
     ‫א‬K‫א‬‫א‬KKK‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
               
     ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
     ‫א‬‫א‬‫א‬K‫א‬‫א‬‫א‬
                       
     ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                K‫א‬‫א‬





                                                                                                                        
                                                      ‫א‬‫א‬
     ‫؟‬EDatabase Management Information SystemFDBMS‫א‬‫א‬
                                                                                                         
     ،‫א‬‫א‬‫א‬‫א‬‫א‬
     ،‫א‬‫א‬‫א‬‫א‬
                                                                                  
     ‫א‬ ،‫א‬‫א‬‫א‬
      K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬



                                                              -٢-
      ‫א‬‫א‬                            ‫א‬‫א‬                            ‫א‬
 ‫א‬‫א‬                       ١٦٢                                    



                                                                          ‫א‬
                                                             W‫א‬
                                                      WEHardwareF‫א‬‫ א‬J ١
                                         
‫א‬،‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬
                                                          K‫א‬KKK‫א‬‫א‬‫א‬
                                                                  WESoftwareF‫ א‬J ٢
                           W،‫א‬‫א‬‫א‬‫א‬
                                                        
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬W‫א‬ J 
                                         KLinux, Unix, WindowsK…‫א‬
Oracle, ‫א‬‫א‬‫א‬‫א‬W‫א‬ J 
                                                                             KKKKSybase, DB2
                                      
‫א‬‫א‬‫א‬‫א‬‫א‬W‫א‬‫א‬‫א‬‫א‬‫א‬‫ א‬J 
                                                                      KKKK‫א‬‫א‬‫א‬
   W‫א‬‫א‬‫א‬W‫ א‬J ٣
                                                                     
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬W‫א‬ J 
                                                                          W‫א‬
                                   K‫א‬‫א‬‫א‬‫א‬‫א‬ K١
                                                   K‫א‬‫א‬‫א‬‫א‬ K٢
                                                                         K‫א‬ K٣

                     W‫א‬‫א‬‫א‬‫א‬W‫א‬ J 
                                    K‫א‬‫א‬‫א‬ K١
                                    K‫א‬‫א‬‫א‬ K٢
                                                               K‫א‬‫א‬ K٣
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K٤
                                                                      K‫א‬


                                                     -٣-
        ‫א‬‫א‬                            ‫א‬‫א‬                           ‫א‬
   ‫א‬‫א‬                       ١٦٢                                   



                                                                          
 ‫א‬‫ א‬E‫א‬F‫א‬‫א‬ J 
                                                                                   W‫א‬
                                    ‫א‬‫א‬‫א‬‫א‬ K١
                                            K‫א‬‫א‬‫א‬ K٢
                                              K‫א‬‫א‬ K٣
 ‫א‬‫א‬‫א‬‫א‬ K٤
                                                       K‫א‬‫א‬‫א‬‫א‬‫א‬
                                         K‫א‬‫א‬ K٥

 ‫א‬‫א‬‫א‬‫א‬W‫א‬‫ א‬J 
                                                                             W‫א‬
                                                  
 ‫א‬‫א‬EF‫א‬‫א‬ K١
                                                                    K‫א‬
                                     K‫א‬‫א‬‫א‬ K٢
                                                     K‫א‬‫א‬‫א‬‫א‬ K٣

                                                    
 ‫א‬‫א‬‫א‬‫א‬W‫א‬‫א‬J ‫هـ‬
    K‫א‬KKK‫א‬،‫א‬،‫א‬،‫א‬‫א‬‫א‬‫א‬

                                                    
 ‫א‬‫א‬‫א‬‫א‬‫א‬W‫א‬‫א‬‫א‬‫ א‬J ٤
                           K‫א‬

 K‫א‬‫א‬‫א‬‫א‬W‫ א‬J ٥
 ‫א‬‫א‬‫א‬‫א‬
                                                 K‫א‬‫א‬‫א‬




                                                       -٤-
       ‫א‬‫א‬                           ‫א‬‫א‬                             ‫א‬
  ‫א‬‫א‬                      ١٦٢                                     



                                                                    W‫א‬‫א‬
 ،‫א‬
                                             
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K
                                                                                  
E‫א‬F‫א‬‫א‬‫א‬
                                  
‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                     K
                                                                          W‫א‬‫א‬
                                                              W‫א‬‫א‬‫א‬‫ א‬J ١
 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J 
                                                                             
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J 
                                                            K‫א‬‫א‬‫א‬‫א‬‫א‬
                                 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J 
                                    
‫א‬‫א‬‫א‬‫א‬W‫א‬ J ٢
                                                                
‫א‬‫א‬‫א‬‫א‬ ‫א‬
                                                                                        W‫א‬
                                                             
‫א‬F‫א‬‫א‬‫א‬ J 
                                                                              WE‫א‬‫א‬
                                ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K١
‫א‬‫א‬ K٢
                                                                                   K‫א‬
‫א‬‫א‬ K٣
                                                                                    K
‫א‬‫א‬‫א‬، ‫א‬‫א‬‫א‬‫א‬‫א‬ K٤
                                                    
KKKK‫א‬‫א‬‫א‬‫א‬،‫א‬،‫א‬‫א‬‫א‬
                                                                                           K
                                                  KEDBMSF‫א‬‫א‬‫ א‬J 
                                                     -٥-
      ‫א‬‫א‬                         ‫א‬‫א‬                       ‫א‬
 ‫א‬‫א‬                    ١٦٢                               



  KEDBMSF‫א‬‫א‬‫א‬‫א‬ J 
‫א‬‫א‬‫א‬‫א‬‫א‬‫ א‬J 
         KEDBMSF‫א‬‫א‬‫א‬‫א‬
                                                                                    
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬W‫א‬ J ٣
                                     KKKK‫א‬‫א‬‫א‬‫א‬‫א‬

                                                         W‫א‬‫א‬ J ٤
                                    K‫א‬‫א‬ J 
                                                  K‫א‬‫א‬‫א‬‫א‬ J 
،KK‫א‬‫א‬‫א‬‫א‬‫א‬W‫א‬‫א‬ J ٥
       K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                               
‫א‬‫א‬W‫א‬J ٦
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                           K‫א‬
                                                                                                     




                                                  -٦-
     ‫א‬‫א‬                         ‫א‬‫א‬                           ‫א‬
‫א‬‫א‬                    ١٦٢                                   



                                           
                                                          ‫؟‬‫א‬‫א‬‫א‬ J ١
                                            ‫א‬‫א‬‫א‬‫א‬ J 
       K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬J 
                                        K‫א‬‫א‬ J 
                                                                                 W J ٢
                                                                             ‫א‬
                                                                   ‫א‬‫א‬
                                                           ‫؟‬‫א‬ J ٣
                                         K‫א‬‫ א‬J ٤
                         K‫א‬‫א‬،‫א‬‫א‬‫ א‬J ٥
                                                                                                    
                                                                                                     

                                                




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




                          ‫א‬‫א‬
                          ‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬




٢
     ‫א‬‫א‬                        ‫א‬‫א‬                     ‫א‬
 ‫א‬‫א‬‫א‬                         ١٦٢                             



                                                                                       W‫א‬‫א‬
                                      K‫א‬‫א‬‫א‬‫א‬
                                                                                                  
                                                                                           W‫א‬‫א‬
                                              K‫א‬‫א‬‫א‬
                                  KE‫א‬F‫א‬‫א‬‫א‬‫א‬
                        KE‫א‬F‫א‬‫א‬‫א‬‫א‬‫א‬

                                                                                 
                                                                         W‫א‬‫א‬‫א‬
               K٪١٠٠‫א‬‫א‬‫א‬‫א‬
                                                                                                  
                                                                               W‫א‬‫א‬
                                                                                   K
                                                                                                  
                                                                                   W‫א‬‫א‬
                                                                                  KH
                                                                                                  
                                                                                    W‫א‬‫א‬
                                    K‫א‬‫א‬‫א‬‫א‬‫א‬




                                                    -٨-
      ‫א‬‫א‬                           ‫א‬‫א‬                             ‫א‬
  ‫א‬‫א‬‫א‬                            ١٦٢                                     



                                                                     W‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬Codd‫א‬١٩٧٠‫א‬‫א‬‫א‬
                                                                                          
K‫א‬‫א‬‫א‬ ‫א‬‫א‬
‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬ERelationsFETablesF‫א‬‫א‬
                                                              
‫א‬KE‫א‬F‫א‬
                                                                           
‫א‬،EF‫א‬
                                               
‫א‬‫א‬ERecords or TupleF‫א‬‫א‬K‫א‬
                   
‫א‬‫א‬EDomainFEAttributesF‫א‬‫א‬
KK‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬
         
‫א‬‫א‬‫א‬‫א‬‫א‬EPrimary KeyF
                             
‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬E‫א‬F
                              KERelational DatabaseF‫א‬
                                                                                                                
                         ‫א‬EStudentF‫א‬‫א‬‫א‬
                                                                                  Student‫א‬‫ א‬J
                                                                
                                                      K‫א‬ J
   K‫א‬St _No‫א‬‫ א‬J
                                      K‫א‬‫א‬Dept_Code‫ א‬J
J ٢٠٠١‫א‬‫א‬‫א‬‫א‬‫א‬‫ א‬EGpaF‫א‬ J
                                                                                                K١٠J ٠١
Gpa‫א‬W‫א‬ J
‫א‬‫א‬‫א‬‫א‬ Dept_Code‫א‬K٥KK١
                                                                                                   K‫א‬




                                                        -٩-
      ‫א‬‫א‬                       ‫א‬‫א‬                        ‫א‬
  ‫א‬‫א‬‫א‬                        ١٦٢                                




‫א‬‫א‬K‫א‬‫א‬ J
                                                                                   W‫א‬‫א‬

       Student
        St _No              St _Name                 Gpa              Birth_Date         Dept_Code
    J ٠١J ٢٠٠١                  Sami                 ٣{٧٥           ١٩٨١J ٠١J ٠١             Comp
                   ١٠
    J ٠٢J ٢٠٠١                  Khalid                 ٣{٥          ١٩٨٢J ١٠J ١٠                   Math
                   ٩٩
    J ٠١J ٢٠٠٠                       Ali               ٤{٢          ١٩٨٠J ٠٨J ١٢                Comp
                 ١٠١
                                                                                                        


                                                   - ١٠ -
       ‫א‬‫א‬                          ‫א‬‫א‬                              ‫א‬
   ‫א‬‫א‬‫א‬                            ١٦٢                                     



                                                                         WE‫א‬F‫א‬‫א‬
                                              
‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                            W‫א‬‫א‬
                           
‫א‬‫א‬‫א‬WESuper KeyF‫א‬‫ א‬J 
‫א‬‫א‬K‫א‬‫א‬‫א‬
                                                                                                      K
             St_No
             St_No, St _Name
             St_No ,dept_code
‫א‬‫א‬E‫א‬F‫א‬WECandidate KeyF‫א‬‫ א‬J 
    
‫א‬‫א‬
                                              KENullF‫א‬
St_No, St _Name
                                                               
St_No
                   KSt_No،،‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬WEPrimary KeyF‫א‬‫ א‬J 
                    
            KSt_NoK‫א‬‫א‬
                                                                               
،‫א‬W‫א‬‫ א‬J 
                    Customer _id‫א‬‫א‬
   
E>‫؟‬‫א‬F‫א‬
‫א‬‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬
                                                                                       K
         Customer _id Customer name                                       tel            Address

WEForeign KeyF‫א‬‫א‬J 
                    
EStudentF‫א‬EDept_CodeF‫א‬EUniqueF
                                       EDepartmentF‫א‬EForeign KeyF

                                                       - ١١ -
      ‫א‬‫א‬                          ‫א‬‫א‬                            ‫א‬
  ‫א‬‫א‬‫א‬                           ١٦٢                                    




                                                                         WE‫א‬F‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                             W‫א‬‫א‬K‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬WE١W١F‫א‬‫א‬ K١
                                                                                        ‫א‬‫א‬




‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                    K‫א‬
                            Person             1               1       Passport
                        


                                                      - ١٢ -
        ‫א‬‫א‬                       ‫א‬‫א‬                             ‫א‬
    ‫א‬‫א‬‫א‬                           ١٦٢                                  



                                                                                     
 ‫א‬‫א‬‫א‬E١WNNW١F‫א‬‫א‬ K٢
K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬




                    
                                                                         
 ‫א‬‫א‬‫א‬
                                                                                    K‫א‬‫א‬
                                  Student        n               1     Department
                          

     ‫א‬‫א‬‫א‬‫א‬WEN:NF K٣
                   K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬




                              
                                                                                                          
                                                                                     
 ‫א‬
                                                                                                       K
                                                                                                               
                                  Student    n                   n    Class

                          




                                                       - ١٣ -
      ‫א‬‫א‬                        ‫א‬‫א‬                             ‫א‬
  ‫א‬‫א‬‫א‬                         ١٦٢                                     



                                                                                                  
                                                                                     W J ١
                                                                              ‫א‬‫א‬
                                                                             ‫א‬‫א‬
                                                                             ‫א‬‫א‬
                                   K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J ٢
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J ٣
                                                                               K
                                ‫א‬‫א‬ J ٤
                                                                      EN :NF‫א‬‫א‬
                                                                       E NW١F‫א‬
                                                                  EN :NF
                                                                                              




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




                          ‫א‬‫א‬
                         ‫א‬‫א‬
‫א‬‫א‬




٣
     ‫א‬‫א‬                        ‫א‬‫א‬                              ‫א‬
 ‫א‬‫א‬                         ١٦٢                                      



                                                                                   W‫א‬‫א‬
               
‫א‬‫א‬‫א‬‫א‬
                                                                        K‫א‬‫א‬
                                                                                                                           
                                                                                                         W‫א‬‫א‬
                            K‫א‬‫א‬‫א‬
                           K‫א‬‫א‬

                                                                                     
                                                                                W‫א‬‫א‬‫א‬
                                 K٪١٠٠‫א‬
                                                                                                                           
                                                                                W‫א‬‫א‬
                                                                                 K٤
                                                                                                                           
                                                                                      W‫א‬‫א‬
                                                                                    H
                                                                             
                                                                                 W‫א‬‫א‬
                                        K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬




                                                   - ١٥ -
      ‫א‬‫א‬                              ‫א‬‫א‬                                 ‫א‬
  ‫א‬‫א‬                                ١٦٢                                        



                                                                                     W
                                                            
‫א‬،‫א‬‫א‬‫א‬‫א‬
‫א‬K‫א‬‫א‬‫א‬
                                                                 
K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                    
K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
Entity                                                                             
              ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                               KRelationship (ER)Diagram
                                                                                                                         
                                                                                                             W‫א‬
                                                                                                       ‫؟‬‫א‬
                  K‫א‬‫א‬EF‫א‬
‫א‬K‫א‬
                    
 ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬
                                                         
‫א‬‫א‬‫א‬K‫א‬‫א‬
‫א‬‫א‬K‫א‬‫א‬‫א‬
  
E‫א‬F‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
،‫א‬‫א‬E‫א‬F‫א‬E‫א‬F‫א‬
                                                      
‫א‬‫א‬E‫א‬F‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                        K‫א‬‫א‬‫א‬




                                                       ‫ﻣﺨﻄﻂ ﻣﻨﺰل‬
                                
                                                                                                                         




                                                           - ١٦ -
      ‫א‬‫א‬                            ‫א‬‫א‬                        ‫א‬
  ‫א‬‫א‬                             ١٦٢                                



                                                                                 W‫א‬‫א‬
                                                               
‫א‬‫א‬‫א‬
                                           W‫א‬‫א‬‫א‬KK‫א‬
                                                                              
EObjectsF‫א‬‫א‬ ‫א‬EEntity SetF‫א‬
                                     
EEntityF‫א‬K‫א‬‫א‬‫א‬
                       
،‫א‬،‫א‬K‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬K‫א‬‫א‬‫א‬
                                                                   
‫א‬‫א‬K‫א‬
                                                                                                  K‫א‬
                                      Patient                            Student
                                       ‫ﻣﺮﻳﺾ‬                                ‫ﻃﺎﻟﺐ‬
                        
                                                 
‫א‬،‫א‬‫א‬WEAttributesF‫א‬‫א‬
                   
KK‫א‬‫א‬‫א‬‫א‬‫א‬
،‫א‬،‫א‬‫א‬،‫א‬،‫א‬،‫א‬،‫א‬
                                             
‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬،‫א‬
                                                                                              K

                            Stu_address             Stu_name                   Stu_major




                                                     Student
              
         
‫א‬‫א‬‫א‬ WEDomainF‫א‬
                                                                          
٣٠‫א‬‫א‬،‫א‬
                          
‫א‬KE٣{٥F٥KK٠‫א‬‫א‬ ‫א‬،
                                                                             
‫א‬K٢٢‫א‬‫א‬
                                  
K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                  
 Eprimary keyF‫א‬‫א‬E‫א‬F‫א‬
                                                                                   K‫א‬
                                                        - ١٧ -
       ‫א‬‫א‬                             ‫א‬‫א‬                               ‫א‬
   ‫א‬‫א‬                              ١٦٢                                       



                                Car_model
                                                                                        Car_year



                                                              car
                         Car_Code


                                                        ‫א‬
                              WSimple and Composite Attributes‫א‬‫א‬‫א‬J 
                                    
 K‫א‬‫א‬،‫א‬
 ،‫א‬،‫א‬،‫א‬F‫א‬‫א‬،E‫א‬‫א‬،‫א‬،‫א‬‫א‬F
                                                            
‫א‬KKE‫א‬
             K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                      Fname                 Init            Lname




                                                                Name




                                                            Student


   WSingle-Valued or Multiple-Valued Attributes‫א‬J 
 ‫א‬E‫א‬،‫א‬F‫א‬‫א‬‫א‬
                                                  
 E‫א‬‫א‬،‫א‬،F
                                                                                               
 ‫א‬‫א‬K 
                      K‫א‬‫א‬‫א‬‫א‬‫א‬

                                                                           Car_Col or

                                        car
                         

                                                          - ١٨ -
      ‫א‬‫א‬                           ‫א‬‫א‬                           ‫א‬
  ‫א‬‫א‬                            ١٦٢                                   



                                                          WEDerived AttributesF‫א‬‫א‬
                                                                        
‫א‬‫א‬‫א‬‫א‬
                                      
‫א‬ K‫א‬‫א‬‫א‬‫א‬
                                                        K‫א‬‫א‬‫א‬‫א‬
                                                                         K‫א‬–‫א‬‫א‬Z‫א‬

                                                                    ِAge




                                             Student
                          
                                                     
K‫א‬‫א‬‫א‬
                                                                                               
‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                            K‫א‬‫א‬‫א‬

                                                                  WERelationshipsF‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
KEKKKK،،،F‫א‬‫א‬
                                      
‫א‬‫א‬‫א‬KK‫א‬‫א‬‫א‬‫א‬
                             KEN:NFEN :1F‫א‬E١W١F‫א‬‫א‬
                               K‫א‬‫א‬

                                     N                                         N
                                                       Enroll                            Class
                   Student

        




                                                       - ١٩ -
      ‫א‬‫א‬                     ‫א‬‫א‬                          ‫א‬
  ‫א‬‫א‬                      ١٦٢                                  



                                                         
‫א‬‫א‬‫א‬ W‫א‬‫א‬
                                                        
‫א‬
‫א‬‫א‬K‫א‬‫א‬‫א‬،‫א‬‫א‬
                                                                               
E‫א‬F‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                          
‫א‬‫א‬K ‫א‬
                                                 
‫א‬‫א‬F‫א‬‫א‬‫א‬‫א‬‫א‬
                               KE‫א‬‫א‬‫א‬
                                                                           Name
                        No               Employee



                                                                         Relationship
                   D_Name




                                       Dependent                            B_date




                              : ESupertype and SubtypeF‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                      
SupertypeEF‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬
                             
Account ‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬،‫א‬
                                                                                      K‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬W
                                                                       
EOverdraft AmountF‫א‬ECurrent AccountF‫א‬‫א‬W‫א‬
                                         
‫א‬‫א‬K ‫א‬
                                                    KEInterest RateF‫א‬‫א‬
 KEISAF‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

                                                 - ٢٠ -
     ‫א‬‫א‬                  ‫א‬‫א‬                    ‫א‬
 ‫א‬‫א‬                   ١٦٢                            



                                                                                          




         
                                                   :ERecursiveF‫א‬
‫א‬‫א‬‫א‬،‫א‬‫א‬‫א‬
                                                                 
E‫א‬‫א‬F
                                                   E‫א‬F‫א‬




                                             - ٢١ -
      ‫א‬‫א‬                         ‫א‬‫א‬                           ‫א‬
  ‫א‬‫א‬                          ١٦٢                                  



                            
F ‫א‬‫א‬W‫א‬
 
‫א‬‫א‬‫א‬KEER Diagram‫א‬‫א‬
                         K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                    
F‫א‬‫א‬‫א‬ J ١
                                                               
‫א‬‫א‬‫א‬‫א‬،E‫א‬‫א‬
                                K‫א‬،‫א‬‫א‬‫א‬‫א‬
                         K‫א‬‫ א‬J ٢
                                                             
‫א‬‫א‬ ‫א‬ J ٣
       
‫א‬،‫א‬‫א‬F‫א‬‫א‬
                                K‫א‬‫א‬‫א‬‫،א‬‫،א‬E‫א‬‫א‬‫א‬‫،א‬‫א‬‫א‬F
                       
‫א‬‫א‬‫א‬‫א‬F‫ א‬J ٤
                        K‫א‬‫א‬‫א‬E‫א‬
،‫،א‬‫א‬،،‫א‬‫א‬‫א‬ J ٥
                           
                                                                                           K‫א‬‫א‬
                                   K‫א‬‫א‬‫א‬ J ٦
                                                                                                           




                                                     - ٢٢ -
      ‫א‬‫א‬       ‫א‬‫א‬    ‫א‬
 ‫א‬‫א‬         ١٦٢            




 




                                   - ٢٣ -
     ‫א‬‫א‬                        ‫א‬‫א‬                   ‫א‬
 ‫א‬‫א‬                         ١٦٢                           



                                               
                                                                                WJ ١
                                                                           KEntity‫א‬
                                                                        KAttribute‫א‬
                                                                      KRelationship‫א‬
                            K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬J ٢
                           
      ‫؟‬‫א‬‫א‬ ‫؟‬‫א‬‫א‬‫א‬‫א‬‫א‬J ٣
                                                                                  
                             K‫א‬‫א‬‫א‬‫א‬J ٤
                                               W‫א‬‫א‬‫א‬J ٥
 ‫א‬‫א‬J 
                               K‫א‬‫א‬،
‫א‬‫א‬J 
                                                                                K‫א‬
‫א‬‫א‬‫א‬J 
                                                                     K‫א‬
                                             
‫א‬‫א‬‫א‬J ٦
‫א‬‫א‬‫א‬‫א‬
     ‫א‬‫א‬‫א‬‫א‬‫א‬
     K‫א‬‫א‬‫א‬‫א‬
                                     W‫א‬‫א‬‫א‬J ٧




                                                   - ٢٤ -
         ‫א‬‫א‬        ‫א‬‫א‬    ‫א‬
     ‫א‬‫א‬         ١٦٢            









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




                 ‫א‬‫א‬
                    ‫א‬‫א‬
‫א‬‫א‬




٤
 ‫א‬‫א‬‫א‬                    ‫א‬‫א‬                        ‫א‬
 ‫א‬‫א‬                         ١٦٢                                



                                                                                 W‫א‬‫א‬
                         K3NF‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                            
                                                                                     W‫א‬‫א‬
   : EData AnomaliesF‫א‬‫א‬‫א‬
                               ‫א‬‫א‬‫א‬
                         ‫א‬‫א‬‫א‬‫א‬
                         ‫א‬‫א‬‫א‬‫א‬
                         ‫א‬‫א‬‫א‬‫א‬
                                                                            
                                                                       W‫א‬‫א‬‫א‬
       K٪١٠٠3NF‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                            
                                                                       W‫א‬‫א‬
                                                                           
                                                                                            
                                                                              W‫א‬‫א‬
                                                                             H
                                                                                            
                                                                         W‫א‬‫א‬
                               K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬




                                           - ٢٦ -
      ‫א‬‫א‬‫א‬                             ‫א‬‫א‬                              ‫א‬
      ‫א‬‫א‬                                  ١٦٢                                      



                                                                                                        W
‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬K‫א‬‫א‬
                                                                       
‫א‬KK‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                   .EThird Normal Form 3NFF‫א‬
                                                             : EData AnomaliesF‫א‬‫א‬
         Employee_department
   Empno Ename                 Job Salary Deptno               Dname           Loc
       101 Sami              clerk     3000         10 Accounting Riyadh
       205 Khalid manager              2500         10 Accounting           Riyadh
       303        Ali salesman         1200         20           Sales      Jeddah
       502 Saeed salesman              2100         20           Sales      Jeddah
       601 Salem             clerk     1000         30     Operation Dmmam
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
     W‫א‬‫א‬‫א‬‫א‬‫א‬
                                           
،‫א‬‫א‬‫א‬W‫א‬ J ١
                                                 K‫א‬‫א‬‫א‬،
                                                                     
٢٠‫א‬ELocF‫א‬‫א‬‫א‬‫א‬W‫א‬ J ٢
‫א‬‫א‬‫א‬‫א‬‫א‬Riyadh Jeddah
                                                                         
K‫א‬‫א‬‫א‬‫א‬
K‫א‬٣٠٣‫א‬‫א‬‫א‬
٣٠٣‫א‬Jeddah ‫א‬‫א‬‫א‬
                                                                                                K‫א‬
،‫א‬٣٠‫א‬W‫א‬ J ٣
                         K‫א‬٣٠‫א‬٦٠٦‫א‬




                                                         - ٢٧ -
         ‫א‬‫א‬‫א‬                      ‫א‬‫א‬                           ‫א‬
         ‫א‬‫א‬                           ١٦٢                                   



                                              WEFunctional Dependency FD F‫א‬‫א‬
     EF EF‫א‬‫א‬
                                                          A           B
    KBAA‫א‬‫א‬ B
     
    ‫א‬‫א‬‫א‬‫א‬‫א‬
    ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                           KEAnomaliesF
                             W‫א‬‫א‬ ‫א‬‫א‬W
                                                               FD1 : Empno                 Ename
                                                               FD2 : Empno                Deptno
                                              ‫א‬‫א‬‫א‬
                                                      FD1 : Empno                Ename,Deptno

                                                         FD :Functional Dependency
                                                                                ‫א‬‫א‬
    Functional F‫א‬‫א‬‫א‬‫א‬
                            W‫א‬‫א‬‫א‬‫א‬‫א‬EDependency FD
                           EX‫א‬YFFXY‫א‬WReflexive ‫ א‬J ١
                                                           YX
                                                     1- X ⊇ Y :         X Y
                                                                    
    XZ YX‫א‬WAugmentation‫א‬‫א‬ J ٢
                                                  YZ 
                                               2- { X Y} ╞ XZ YZ
              ZY YX‫א‬WTransitive‫א‬ J ٣
                                                                      KZX
                                                                                                                 
                                                 3- {X Y ,Y Z} ╞ X Z
                                       
     ZX YX‫א‬WUnion‫א‬ J ٤
                                                                        KYZX

                                                     - ٢٨ -
      ‫א‬‫א‬‫א‬                         ‫א‬‫א‬                          ‫א‬
      ‫א‬‫א‬                              ١٦٢                                  


                                                 4- {X Y ,X Z} ╞ X YZ
                            ‫א‬ Decomposition‫א‬ J ٥
                                          5- {X YZ } ╞ X Y , X Z
                        ‫א‬ Pseudotransitive ‫א‬‫א‬‫א‬ J ٦
                                         6- {X Y , WY Z } ╞ WX Y

                              K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬╞

                               :EFirst Normal Form 1NF F‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
               K‫א‬EF
                                                                                                       
                                           WEmployee‫א‬‫א‬١
           No                          Name                                     Adresse
               Fname         Mid Lname               city            Street House no
        100       Ali Salem             musa Riyadh Immam saud                      210
        120 Saeed            Eisa         Ali Riyadh           King Fahad           202
 
‫א‬‫א‬‫א‬‫א‬‫א‬
         
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬K‫א‬
          
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬1NF, ‫א‬‫א‬
                                                                                         
            No Fname   Mid Lname    city                                  Street House no
           100   Ali Salem  musa Riyadh                                 Immam                210
                                                                           saud
        120 Saeed              Eisa           Ali Riyadh King Fahad                          202
‫א‬‫א‬‫א‬‫א‬
                                                                        K1NF ‫א‬‫א‬‫א‬
‫א‬HOURS ‫א‬‫א‬‫א‬W ٢
                                           ‫א‬‫א‬‫א‬PROJECTS
                                                     - ٢٩ -
       ‫א‬‫א‬‫א‬                        ‫א‬‫א‬                          ‫א‬
       ‫א‬‫א‬                             ١٦٢                                  




    NO Name Project_Code   Hours   Deptno                                                     Dname
    210  Ali    P1,p2,p3 12,20,40 10,20,20 Research,                                       Operation,
                                                                                          Operation
    201 Salem                    P1,p3          30,15             10,20          Research Operation
    305    Ali                   P2,p3          40,20             20,20          Operation, Operation
                                                                                               
                                                              
 ‫א‬‫א‬‫א‬‫א‬
     
 ‫א‬Deptno‫א‬Hours‫א‬Project_Code‫א‬
 ،‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬
                                                  K‫א‬‫א‬‫א‬‫א‬
            NO       Name Project_Code Hours Deptno                    Dname
            210        Ali                  P1        12        10 Research
            210        Ali                 p2        20       20 Operation
            210        Ali                 p3         40       20 Operation
            201 salem                       P1        30        10 Research
            201 salem                      p3        15        20 Operation
            305        Ali                  P2        40        20 Operation
            305        Ali                p3         20         20 Operation
 ‫א‬
                       
  ‫א‬‫א‬EPrimary KeyF
      ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K
                                                                    FD 1 :No Name
                                                            K‫א‬‫א‬
                                                        FD 2 : Project_Code Deptno
                                                  K‫א‬
                                                               FD 3 : Deptno Dname
                                                                K‫א‬‫א‬
                                                          
 ‫א‬‫א‬‫א‬‫א‬
                            K‫א‬Ali


                                                     - ٣٠ -
      ‫א‬‫א‬‫א‬                       ‫א‬‫א‬                          ‫א‬
      ‫א‬‫א‬                            ١٦٢                                  



                                                    
P1‫א‬‫א‬‫א‬
                                                                     K
٢٠‫א‬‫א‬‫א‬
                                                                 K‫א‬
                                    
‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                     
‫א‬E‫א‬‫א‬F
                                                                                         W‫א‬
                                                 ‫א‬‫א‬
                                                  FD 4 :No, Project_Code name
                                               FD 5 :No, Project_Code Deptno
                                                 FD 6 :No, Project_Code Hours
                                                          FD 7 : Deptno Dname
                     FD 8 :No, Project_Code Name ,Hours, Deptno, Dname
‫א‬‫א‬‫א‬FD1,FD2FD4,FD5
‫א‬‫א‬‫א‬‫א‬FD5 ،‫א‬
                                                                              
‫א‬‫א‬،‫א‬
                                                                         KE1NFF‫א‬‫א‬‫א‬
                                 :ESecond Normal Form 2NF F‫א‬‫א‬‫א‬
                                           W‫א‬‫א‬‫א‬‫א‬
                                          K‫א‬‫א‬‫א‬‫א‬‫א‬ J ١
                                              K‫א‬‫א‬‫א‬ J ٢
 ‫א‬‫א‬‫א‬‫א‬E‫א‬F‫א‬W‫א‬‫א‬

                                         A
                                                                    C
                                          B
                         
 C C  B A,B ‫א‬‫א‬CCA,B 
 K‫א‬‫א‬‫א‬‫א‬KB‫א‬‫א‬
                                                   - ٣١ -
     ‫א‬‫א‬‫א‬                     ‫א‬‫א‬                        ‫א‬
     ‫א‬‫א‬                          ١٦٢                                



                                                                                                
              NO       Name      Project_Code            Hours     Deptno       Dname
              210         Ali              P1               12          10    Research
              210         Ali             p2               20         20      Operation
              210         Ali              p3               40         20     Operation
              201      Salem               P1               30          10     Research
              201      Salem              p3               15          20     Operation
              305         Ali              P2               40          20    Operation
              305         Ali             p3               20           20    Operation
                                                                                                
                                            ‫؟‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                 W‫א‬‫א‬‫א‬
                                                      ‫؟‬‫א‬‫א‬‫א‬‫א‬J ١
           K‫א‬،،
                                                             ‫؟‬‫א‬J ٢
                                            ‫א‬‫א‬
                                                                      FD 1 :No Name
                                                FD 2 : Project_Code Deptno,Dname
                                      FD 3 :No, Project_Code name ,deptno, hours
‫א‬‫א‬NameNo No, Project_Code‫א‬‫א‬
                                                                                          
K‫א‬ DnamedeptnoProject_Code
                                                      
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                         W‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                   
‫א‬‫א‬‫א‬‫א‬‫א‬ J ١
                                                              KE‫א‬‫א‬F
                                                    
‫א‬‫א‬‫א‬‫א‬ J ٢
                                                    KE‫א‬‫א‬F‫א‬
                                         KE‫א‬F‫א‬ J ٣
                                W‫א‬‫א‬‫א‬‫א‬‫א‬ J ٤
                                                - ٣٢ -
        ‫א‬‫א‬‫א‬                        ‫א‬‫א‬                          ‫א‬
        ‫א‬‫א‬                             ١٦٢                                  



                                                                                                              
                          NO     Project_Code            Hours             NO    Name
                          210         P1                  12               210    Ali
                          210         p2                  20               210    Ali
                          210         p3                  40               210    Ali
                          201         P1                  30               201   salem
                          201         p3                  15               201   salem
                          305         P2                  40               305    Ali
                          305         p3                  20               305    Ali

                          Project_Code                    Deptno              Dname
                                    P1                         10           Research
                                   p2                        20             Operation
                                   p3                         20            Operation
                                                                                                              
                                         WEThird Normal Form 3NF F‫א‬‫א‬‫א‬
                                              W‫א‬‫א‬‫א‬‫א‬
                                             K‫א‬‫א‬‫א‬‫א‬ J ١
                                               K‫א‬‫א‬‫א‬ J ٢
 K‫א‬‫א‬‫א‬‫א‬E‫א‬F‫א‬W‫א‬‫א‬

                                                                       B
                                             A

                                                                       C
                           
                                   
  C BA ‫א‬‫א‬C,BC,B A 
  ‫א‬‫א‬‫א‬KB‫א‬‫א‬ C
                                                                                                 K
                                                  ‫؟‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                         W‫א‬‫א‬‫א‬
                                                      - ٣٣ -
     ‫א‬‫א‬‫א‬                      ‫א‬‫א‬                      ‫א‬
     ‫א‬‫א‬                           ١٦٢                              



                                                      ‫؟‬‫א‬‫א‬‫א‬‫א‬‫א‬J ١
         K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                             ‫؟‬‫א‬J ٢
                                       ‫א‬‫א‬
                                                                              ‫א‬‫א‬J 
                                                                       FD 1 :No Name
                                                                       K‫א‬
                                                                          ‫א‬‫א‬J 
                                                          FD 1 :No, Project_Code         hours
                                                                       K‫א‬
                                                                       ‫א‬‫א‬J 
                                                  FD 1 : Project_Code Deptno,Dname
                                                               FD 2 : Deptno Dname

                  
 Deptno‫א‬DeptnoDnameProject_Code ‫א‬‫א‬
                                                               
‫א‬‫א‬K‫א‬Dname
                                           
‫א‬‫א‬ ‫ א‬ ‫א‬‫א‬‫א‬
                                                        EDeptnoF‫א‬‫א‬
 K‫א‬‫א‬‫א‬‫א‬‫א‬   J ١
                                 W‫א‬‫א‬‫א‬‫א‬‫א‬ J ٢
                                                                     

           NO          Project_Code     Hours                     NO      Name
           210              P1           12                       210      Ali
           210              p2           20                       210      Ali
           210              p3           40                       210      Ali
           201              P1           30                       201     Salem
           201              p3           15                       201     Salem
           305              P2           40                       305      Ali
           305              p3           20                       305      Ali

          Project_Code            Deptno                     Deptno            Dname
                                                 - ٣٤ -
     ‫א‬‫א‬‫א‬                       ‫א‬‫א‬                       ‫א‬
     ‫א‬‫א‬                            ١٦٢                               



                   P1                10                            10          Research
                   p2                20                            20          Operation
                   p3                20


                                                                                 
‫א‬3NF‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                         K‫א‬‫א‬




                                                  - ٣٥ -
    ‫א‬‫א‬‫א‬                       ‫א‬‫א‬                             ‫א‬
    ‫א‬‫א‬                            ١٦٢                                     



                                            
                       K‫א‬ EData AnomaliesF‫א‬‫א‬‫א‬ K١

                             ‫؟‬EFunctional Dependency FD F‫א‬‫א‬ K٢

         K‫א‬‫א‬‫א‬‫ א‬K٣

                                                                            ‫א‬ K٤
                                                                      K‫א‬‫א‬‫ א‬J 
                                                                      K‫א‬‫א‬‫ א‬J 
                                                                      K‫א‬‫א‬‫ א‬J 
                                                                                     
                                                                     W K٥
                                                    K‫א‬‫א‬‫א‬ J 
                                                   K‫א‬‫א‬‫א‬ J 
                                                   K‫א‬‫א‬‫א‬ J 
                                                                                                 
      ‫א‬‫א‬‫؟‬‫א‬‫א‬‫א‬‫א‬‫א‬ K٦
                                                    K‫א‬‫א‬‫א‬‫א‬
                                                                                                        
Course     Sec_       Dept   Credit     Course    Ins_id      Semeste Year          Date       Room         No_of
_No        No                _Hours     Level                 r                                _No          _
                                                                                                            stu
    ‫ א‬‫ א‬‫ א‬                         ‫ א‬ ‫ א‬   ‫ א‬ 
  ‫א‬                       ‫א‬     ‫ א‬‫ א‬‫א‬‫ א‬‫א‬‫א‬               ‫ א‬‫א‬‫ א‬
                                                                                                        


                                                                                                         



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




                                                         ‫א‬‫א‬
                                             ‫א‬‫א‬
‫א‬‫א‬




٥
            ‫א‬‫א‬                      ‫א‬‫א‬            ‫א‬
‫א‬‫א‬           ١٦٢                    



                                                                                    W‫א‬‫א‬
                                K‫א‬‫א‬ ‫א‬
                                                                                                  
                                                                                 W‫א‬‫א‬
                       K‫א‬‫א‬‫א‬
                                                                                                  
                                                                   W‫א‬‫א‬‫א‬
     K٪١٠٠‫א‬‫א‬‫א‬
                                                                                                  
                                                                                W‫א‬‫א‬
                                                                                    
                                                                                                  
                                                                                    W‫א‬‫א‬
                                                                                   H
                                                                                                  
                                                                                     W‫א‬‫א‬
                                          K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬




                                                      - ٣٧ -
             ‫א‬‫א‬                    ‫א‬‫א‬          ‫א‬
 ‫א‬‫א‬         ١٦٢                  



                                                                                       
 E‫א‬‫א‬ ‫א‬‫א‬ ‫א‬
‫א‬‫א‬KE‫א‬‫א‬F‫א‬‫א‬
    E‫א‬ ‫א‬ F‫א‬    ‫א‬
                                                                        . ‫א‬‫א‬




                                                                                           
 WE‫א‬‫א‬F‫א‬‫א‬‫א‬



                                                     - ٣٨ -
             ‫א‬‫א‬                    ‫א‬‫א‬                 ‫א‬
 ‫א‬‫א‬            ١٦٢                      



                                                                      W
‫א‬ Relation (R)‫א‬ Entity (E) J ١
 ‫א‬‫א‬‫א‬‫א‬‫א‬
K  ‫א‬ ‫א‬       ، 




                                                  
                                                                   W‫א‬‫א‬
Employee
    Eno             Fname              Mid    Lname               sex Birthdate       Salary
                                                                                                      
                                       KEF‫א‬




                          
                        Department
                            Deptno     Dname Location
  ‫א‬EF‫א‬
                                                            KEQueryF‫א‬‫א‬




                                                       - ٣٩ -
              ‫א‬‫א‬                      ‫א‬‫א‬                ‫א‬
  ‫א‬‫א‬           ١٦٢                        




                                      
                                                        
                                       Project
                                     Pnumber                Pname         Plocation
                                                 W Weak Entity‫א‬
 Relation (R)‫א‬ EWeak EntityF
    ‫א‬  ‫א‬ ‫א‬  ‫א‬ ‫א‬   ‫א‬
‫א‬   ‫א‬  ‫א‬ ‫א‬  ‫א‬    ،  
 ‫א‬ ‫א‬       ، ‫א‬ ‫א‬  ‫א‬
                                                             KE‫א‬‫א‬‫א‬F‫א‬‫א‬




                Dependent
                     Eno                  Name               Sex Birthdate Relationship
                                                                                                    




                                                        - ٤٠ -
              ‫א‬‫א‬                        ‫א‬‫א‬                ‫א‬
  ‫א‬‫א‬             ١٦٢                        



E ١W١F ‫א‬  ‫א‬   ‫א‬  ‫א‬       W‫א‬ 
‫א‬E NWNFE NW١F‫א‬
                                                                                                W‫א‬‫א‬
                                                                     E١W١F‫א‬‫א‬J ١
      ‫א‬    E ١W١F ‫א‬  ‫א‬  
                                                                                            K‫א‬‫א‬


                                                1                    1



F‫א‬‫א‬‫א‬‫א‬ EMgrF ‫א‬
                 K‫א‬‫א‬‫א‬‫א‬‫א‬E‫א‬
                      Department
                         Deptno                Dname Location Mgr Startdate

                                                            EN: 1F‫א‬ J ٢
     EF   EN:1F  ‫א‬  
                                                    KE١F‫א‬‫א‬‫א‬‫א‬ENF‫א‬




                                               1                    N
                                           
Employee
    Eno             Fname            Mid        Lname              sex      Birthdate Salary       Mgr
                                                          - ٤١ -
               ‫א‬‫א‬                         ‫א‬‫א‬                  ‫א‬
   ‫א‬‫א‬              ١٦٢                          



  ‫א‬‫א‬  ‫א‬ ‫א‬    EMgrF     ‫א‬  
                                                                         E‫א‬F‫א‬

                                                                         N
                                                     1




Employee
    Eno             Fname            Mid            Lname         sex         Birthdate Salary     Mgr Deptno

 F‫א‬‫א‬‫א‬EDeptnoF ‫א‬
                                                                                      E‫א‬




                                                              N


                                                                1



                                                

                                  Project
                                Pnumber                Pname Plocation Deptno

 ‫א‬ ‫א‬  ‫א‬ ‫א‬    EDeptnoF     ‫א‬  
                                                                                KE‫א‬F




                                                            - ٤٢ -
              ‫א‬‫א‬                    ‫א‬‫א‬                     ‫א‬
  ‫א‬‫א‬          ١٦٢                            



                                                                  EN:NFJ ٣
    ‫א‬ ‫א‬      EN:NF     
                                     ‫א‬K‫א‬‫א‬‫א‬


                                                 N                      N

                   
                                                                                                            

                          Works_for
                                 Eno Pnumber Hours
 (‫א‬،‫א‬،‫א‬ )‫א‬
‫א‬‫א‬‫א‬ ‫א‬E‫א‬،‫א‬F
                                                                                     
                        K‫א‬‫א‬،‫א‬
              ISAESuper Type F‫א‬‫א‬‫א‬ESubtypeF‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                             W‫א‬
                                                                             ‫א‬
                                                                  ‫א‬‫א‬ J ١
                                        ‫א‬ J ٢
                                                           K‫א‬‫א‬‫א‬




                                                      - ٤٣ -
            ‫א‬‫א‬                    ‫א‬‫א‬                 ‫א‬
‫א‬‫א‬         ١٦٢                         




                                                         W‫א‬‫א‬‫א‬

                                                                      S_Employee
                                                                                                 
 H_Employee
                                                                        Eno          Salary
    Eno     Hour_Rate                                                   




                                                    - ٤٤ -
             ‫א‬‫א‬                     ‫א‬‫א‬             ‫א‬
 ‫א‬‫א‬          ١٦٢                     



                                                  
                               ‫؟‬‫א‬‫א‬‫א‬ K١
                                               KN:N‫א‬‫א‬ K٢
Super F‫א‬‫א‬‫א‬ESubtypeF‫א‬‫א‬‫א‬‫ א‬‫א‬‫א‬ K٣
                                                                                 KISAEType
                                                         ‫א‬‫א‬ K٤




 
                                                                                                      
                K‫א‬‫א‬٦‫א‬‫א‬‫א‬‫א‬ K٥
                                                                                                      




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




                ‫א‬‫א‬
                  ‫א‬‫א‬
‫א‬‫א‬




٦
 ‫א‬‫א‬                      ‫א‬‫א‬                        ‫א‬
 ‫א‬‫א‬                            ١٦٢                                



                                                                                  W‫א‬‫א‬
                                                       KPLSQL‫א‬‫א‬‫א‬‫א‬
                                                                                             
                                                                                    W‫א‬‫א‬
                                 PLSQL ‫א‬‫א‬
                                 PLSQL ‫א‬‫א‬‫א‬
           K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                          K‫א‬Datatypes‫א‬‫א‬‫א‬
                                                                                              
                                                                        W‫א‬‫א‬‫א‬
                                  K٪١٠٠‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                             
                                                                         W‫א‬‫א‬
                                                                            
                                                                                             
                                                                             W‫א‬‫א‬
                                                                       -
                                                                           H -
                                                                                             
                                                                           W‫א‬‫א‬
             K‫א‬‫א‬ ‫א‬‫א‬‫א‬SQL‫א‬‫א‬‫א‬




                                             - ٤٧ -
  ‫א‬‫א‬                             ‫א‬‫א‬                              ‫א‬
  ‫א‬‫א‬                                   ١٦٢                                      



                                                                           W
 Programming Language FSQL‫א‬‫؟‬PL/SQL
‫א‬‫א‬OracleEStructured Query Language
                                      
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                   
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬SQL
                                                                                             KKKK‫א‬
،Modules‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬K‫א‬‫א‬KPL/SQL
    
،Modules‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬
                                                                 
Database Server‫א‬‫א‬
                                                                       
  ‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                  
Source ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬
                           
‫א‬KP-Code‫א‬Compile‫א‬Code
                                                               W‫א‬‫א‬‫א‬‫א‬‫א‬




           
      W‫א‬‫א‬‫א‬‫א‬
                                                                             
‫א‬‫א‬‫א‬‫א‬‫א‬WAnonymous Block K١
‫א‬‫א‬‫א‬K
                    K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K‫א‬
                                                     - ٤٨ -
  ‫א‬‫א‬                            ‫א‬‫א‬                                     ‫א‬
  ‫א‬‫א‬                                  ١٦٢                                             



‫א‬‫א‬،SubprogramsWNamed Block K٢
                                
Function, Procedure, Trigger, W‫א‬‫א‬‫א‬
                                                                      KPackage
                                                                   
‫א‬‫א‬‫א‬‫א‬
                                                                KNested Blocks‫א‬‫א‬

                                                                           W (Blocks)‫א‬‫א‬
                                                               W‫א‬(Block) ‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬Declarative E‫א‬F‫א‬ K١
‫א‬‫א‬‫א‬‫א‬ECursors F‫א‬‫א‬،(Block)‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                K‫א‬(Block) 
‫א‬‫א‬‫א‬ SQLExecutable:‫א‬‫ א‬K٢
  
   E‫א‬،‫א‬،‫א‬‫א‬F‫א‬
                                       
،‫א‬‫א‬،‫א‬(Block)‫א‬‫א‬‫א‬PLSQL
                            K‫א‬‫א‬‫א‬‫א‬‫א‬KKKKK‫א‬‫א‬
                    
‫א‬‫א‬‫א‬‫א‬WEExceptionF‫א‬‫א‬‫א‬‫ א‬K٣
    K‫א‬‫א‬‫א‬‫א‬‫א‬

              DECLARE                                                                      ‫א‬
                                                    ‫א‬‫א‬‫א‬‫א‬‫א‬،‫א‬‫א‬
              BEGIN                                                ‫א‬‫א‬‫א‬ 
                    SQL                                                                  SQL 
                    PLSQL                                                           PLSQL
              EXCEPTION                                                                  ‫א‬
                                      
                                                  E‫א‬F‫א‬‫א‬‫א‬
              END;                                                   ‫א‬‫א‬ 
                                                                                                            


                                                    - ٤٩ -
  ‫א‬‫א‬                         ‫א‬‫א‬                              ‫א‬
  ‫א‬‫א‬                               ١٦٢                                      



                                                                            W‫א‬‫א‬‫א‬‫א‬
F‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                   WPLSQL(Block) 
                                                                            W‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                         W‫א‬‫א‬ K١
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                 K‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                               W‫א‬‫א‬ K٢
                                                                              
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                 K‫א‬‫א‬‫א‬
                                                                                 W‫א‬ K٣
                                                            
E‫א‬‫א‬F%ROWTYPE%Type‫א‬‫א‬
                                                                                   
‫א‬ 
                                                                                       
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                     K‫א‬‫א‬‫א‬
                                                                                                       
                                                                     W‫א‬‫א‬
            
‫א‬‫א‬‫א‬‫א‬PLSQL‫א‬‫א‬‫א‬‫א‬
                    
‫א‬،‫א‬‫א‬‫א‬‫א‬‫א‬
                                  
                
PLSQL (Block) ‫א‬
               W‫א‬‫א‬‫א‬‫א‬‫א‬ KE;F
identifier [CONSTANT] datatype [NOT NULL] [:= DEFAULT | expression ] ;




                                                 - ٥٠ -
   ‫א‬‫א‬                                ‫א‬‫א‬                                 ‫א‬
   ‫א‬‫א‬                                      ١٦٢                                         



                                                                                                                    
                                                                             ‫א‬‫א‬             Identifier
‫א‬‫א‬‫א‬‫א‬          CONSTANT
                                                                       K
 K‫א‬‫א‬CONSTANT‫א‬‫א‬‫א‬
                                        ‫א‬‫א‬‫א‬                               datatype
NOT NULL‫א‬‫א‬‫א‬،
                                                                                                    NOT NULL
                                                                             K‫א‬‫א‬
، PLSQL
                                                                                                      expression
                           EF‫א‬
                                                                                                                
                                                                                                          W
     Declare
     v_name VARCHAR2(10) ;                                     ‫א‬
                                                                          ‫א‬‫א‬
      v_date         DATE ;                                                   
      v_id NUMBER(2) NOT NULL 
     :=10 ;
                                
                                ١٠‫א‬‫א‬
                                                                               NULL
      V_comm CONSTANT NUMBER 
     :=120;
                              ‫א‬‫א‬‫א‬
                                                                                                        ١٢٠‫א‬‫א‬
     BEGIN
     ……
     END;




                                                         - ٥١ -
  ‫א‬‫א‬                           ‫א‬‫א‬                             ‫א‬
  ‫א‬‫א‬                                 ١٦٢                                     



                                              W‫א‬‫א‬‫א‬‫א‬‫א‬
                                                    KSQL‫א‬‫א‬‫א‬‫ א‬K١
(Block) ‫א‬‫א‬ J
                                                                                      K
        K(Block) ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J
                                                            K٣٠‫א‬ J
                  KSQL‫א‬‫א‬‫א‬‫א‬‫א‬ J
                                                  A-Z, a-z, 0-9, _, #,$ J
 CONSTANTNOT NULL‫א‬‫א‬‫א‬‫א‬‫א‬ K٢
                                             K‫א‬‫א‬‫א‬
                    KDEFUALT ‫א‬‫א‬‫א‬:=‫א‬‫א‬‫א‬ K٣
                                                         K‫א‬ K٤
                                                                                  W‫א‬‫א‬
                                                         ‫א‬‫א‬
          K‫א‬:=‫א‬‫א‬‫א‬‫א‬ J
Identifier := expression ;
                                                                                     W
  v_name :=’Ali’ ;                        v _name Ali ‫א‬‫א‬
   net_sal := v_sal –v_sal*.08;  net_sal ‫א‬
     SELECT ‫א‬‫א‬‫א‬ J
                                                                                                 J
    SELECT ename
    INTO v_name
    FROM emp
    WHERE empno = 7788;
                                       
‫א‬ ename ‫א‬‫א‬‫א‬‫א‬
                                                                                            v_name
 ename ‫א‬‫א‬‫א‬
                                                                     K

                                                   - ٥٢ -
 ‫א‬‫א‬                             ‫א‬‫א‬                         ‫א‬
 ‫א‬‫א‬                                   ١٦٢                                 



                                                                W Datatypes ‫א‬‫א‬‫א‬
                                WScalar Datatype ‫א‬‫א‬‫א‬‫א‬J ١
               
 W‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬‫א‬‫א‬
                                                                       ‫א‬                ‫א‬
   ‫א‬ size‫א‬‫א‬‫א‬   VARCHAR2(size)
   ‫א‬K‫א‬‫א‬
                                  Byte ٣٢{٦٧٦K‫א‬
   ‫א‬‫א‬ size‫א‬‫א‬‫א‬
                                                                                   CHAR[(SIZE)]
   ‫א‬‫א‬‫א‬K‫א‬
                           Byte٣٢{٦٧٦١‫א‬‫א‬
   ‫א‬‫א‬Precision‫א‬‫א‬‫א‬                     NUMBER
                                                                                    (precision,scale)
                                ‫א‬‫א‬scale
   EF‫א‬‫א‬                DATE
   ٩٩٩٩‫א‬٤٧١٢‫א‬‫א‬
                                                                       K‫א‬
   ‫א‬ size‫א‬‫א‬‫א‬               LONG
   ‫א‬K‫א‬‫א‬
   Byte٣٢{٦٧٠K‫א‬
                       Byte٢{١٤٧{٤٨٣{٦٤٧LONG‫א‬
                            K‫א‬ EBinaryF‫א‬‫א‬              LONG RAW
                                  TRUE,FLASE‫א‬‫א‬                       BOOLEAN
                                                        ‫א‬ BINARY_INTEGER
                                       ٢{١٤٧{٤٨٣{٦٤٧J ٢{١٤٧{٤٨٣{٦٤٧
    ٢{١٤٧{٤٨٣{٦٤٧J ٢{١٤٧{٤٨٣{٦٤٧‫א‬                             PLS_INTEGR
                   BINARY_INTEGERNUMBER



                                                    - ٥٣ -
  ‫א‬‫א‬                           ‫א‬‫א‬                        ‫א‬
  ‫א‬‫א‬                                 ١٦٢                                



                                                                                              W
 v_job                     VARCHAR2(9);
 v_count             BINARY_INTEGER := 0;
 v_total_sal         NUMBER(9,2) := 0;
 v_orderdate                 DATE := SYSDATE + 7;
 c_tax_rate           CONSTANT NUMBER(3,2) := 8.25;
 v_valid              BOOLEAN NOT NULL := TRUE;
                                 
‫א‬%TYPE‫א‬‫א‬‫א‬
                                                                K‫א‬
                                                                                                   
   v_enameemp.ename%TYPE; 
                       emp‫א‬ ename‫א‬‫א‬ v_ename‫א‬

  v_balanceNUMBER(7,2);
                                                               v_bqlqnce‫א‬

v_min_balancev_balance%TYPE := 10;
      K١٠‫א‬‫א‬ v_balance‫א‬‫א‬ v_min_balance‫א‬

                                                                WBOOLEAN ‫א‬‫א‬‫א‬
                                      KTRUE , FLASE, NULL ‫א‬‫א‬‫א‬J
                               AND,OR ,NOT‫א‬‫א‬‫א‬‫א‬‫א‬J
                    ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                             KFALSE TRUE(A<B)
                                                                                                   W
V_Sal1 NUMBER: =1000 ;
V_Sal2 NUMBER: =2500 ;
 Valid BOOLEAN:= (V_Sal1            >
 V_Sal2);
‫א‬V_SAL1 > V_SAL2 Valid‫א‬
                           KTRUE Valid‫א‬‫א‬‫א‬TRUE ‫א‬

                                                   - ٥٤ -
  ‫א‬‫א‬                          ‫א‬‫א‬                               ‫א‬
  ‫א‬‫א‬                                ١٦٢                                       



                                                 WComposite Datatype ‫א‬‫א‬‫א‬J ٢
                                                                          
                                     
‫א‬ RECORDS ‫א‬J 
                                                                                      K‫א‬‫א‬
            ‫א‬‫א‬‫א‬EMP_REC ‫א‬ W
                                                     EMP_REC
                             ID            NAME       SALARY
                                                                           
                             ١٠١              ALI          3000
                                                                                                       
                                                                          
‫א‬TABLES ‫א‬‫א‬J 
                                 
F‫א‬‫א‬‫א‬
                                              K‫א‬‫א‬KE‫א‬
       WLOB Datatype ( Large Objectas) ‫א‬‫א‬‫א‬‫א‬J ٣
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                              K‫א‬‫א‬‫א‬‫א‬K‫א‬
                  
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬WBind Variables ‫א‬‫א‬J ٤
(Block) ‫א‬‫א‬‫א‬‫א‬‫א‬ESQL* PlusF‫א‬(Block)‫א‬
                         K(Block) ‫א‬‫א‬‫א‬
                                                           W‫א‬‫א‬
 SQL > VARIABLE                           Emp_Sal       Emp_Sal
 NUMBER
                                                                    EHostF‫א‬NUMBER
  SQL > VARIABLE                       emp_name                       Emp_ Name 
 varchar2(20)
                                                           ٢٠VARCHAR2(2)
                                                                                             K EHostF‫א‬
                                                         
 ‫א‬‫א‬‫א‬‫א‬(Block) ‫א‬‫א‬‫א‬‫א‬
                    E:F‫א‬‫א‬‫א‬‫א‬(Block) ‫א‬
                                                          : Emp_Name:= ‘AHMED’;
                                                  - ٥٥ -
  ‫א‬‫א‬                     ‫א‬‫א‬                    ‫א‬
  ‫א‬‫א‬                           ١٦٢                            



                  PRINT ‫א‬‫א‬SQL* Plus‫א‬
    SQL> PRINT Emp_ Name
                                                                          :‫א‬‫א‬
                                                         
PUT_LINE‫א‬‫א‬‫א‬‫א‬(Block) ‫א‬
                   W‫א‬‫א‬DBMS_OUTPUT‫א‬‫א‬
 DBMS_OUTPUT.PUT_LINE(‘Well Come to PLSQL Programming’);
                    ‫א‬‫א‬‫א‬‫א‬‫א‬
 SQL >SET SERVEROUTPUT ON
                                    K(Block) ‫א‬‫א‬‫א‬‫א‬
 SET SERVEROUTPUT ON
 ACCEPT p_annual_sal PROMPT ' Please enter the annual salary '
 Declare
 V_sal NUMBER(9,2) :=&p_annual_sal ;
 BEGIN
 V_sal :=V_sal /12;
 DBMS_OUTPUT.PUT_LINE('Monthly Salary is '||TO_CHAR(V_sal));
 END;




                                             - ٥٦ -
 ‫א‬‫א‬                              ‫א‬‫א‬                   ‫א‬
 ‫א‬‫א‬                                    ١٦٢                           



                                                 
         ‫؟‬‫א‬PLSQL‫א‬‫א‬‫א‬‫א‬ K١
         1-        DECLARE
                   VI_ID NUMBER (4);

          2-       DECLARE
                   v_x ,v_y ,v_z   VARACHAR2(10);

         3-        DECLARE
                   V_Date DATE NOT NULL;


         4-        DECLARE
                   V_valid BOOLEAN :=1;
 
                                        ‫א‬‫א‬‫א‬‫א‬ K٢
           1-         V_days := v_date-SYSDATE;


           2-         V_sender := USER||’ :’|| TO_CHAR(V_DEPTNO);

           3-         v_sum := $100 + $3000;

           4-         V_days:=v_date-SYSDATE;

           5-         v_flage := TRUE;

           6-         v_n1 := v_n2 > ( 2 * v_n3);

           7-         v_value := NULL;


                   K‫א‬MY PLSQL WORKS PLSQL(Block) ‫ א‬K٣
          G_MESSAGE
          --------------------------------
          My PL/SQL Block Works

                                                     - ٥٧ -
  ‫א‬‫א‬                  ‫א‬‫א‬                    ‫א‬
  ‫א‬‫א‬                        ١٦٢                            




               V-CHRPLSQL (Block)  K٤
                                                               ’42 is the answer’
‫א‬V-CHRV_NUM 
 Kp6q4.sqlBlock‫א‬SQL * Plus ‫א‬‫א‬
SQL> PRINT g_char

G_CHAR
----------------------
42 is the answer

SQL> PRINT g_num

    G_NUM
----------
      42




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




                       ‫א‬‫א‬
                       ‫א‬‫א‬
‫א‬‫א‬




٧
 ‫א‬‫א‬                          ‫א‬‫א‬                        ‫א‬
‫א‬‫א‬                           ١٦٢                                



                                                                                        W‫א‬‫א‬
                                            K‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                  
                                                                                         W‫א‬‫א‬
                   KEBlockF‫א‬‫א‬‫א‬‫א‬‫א‬
                                       K‫א‬‫א‬‫א‬
                        K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                        K‫א‬‫א‬‫א‬
                                       K‫א‬‫א‬‫א‬‫א‬
                                                                                 
                                                                            W‫א‬‫א‬‫א‬
                                         K٪١٠٠‫א‬‫א‬‫א‬
                                                                                                  
                                                                            W‫א‬‫א‬
                                                                                
                                                                                                  
                                                                                W‫א‬‫א‬
                                                                         K -
                                                                                  H -
                                                                                                  
                                                                            W‫א‬‫א‬
          K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                  




                                                 - ٥٩ -
  ‫א‬‫א‬                          ‫א‬‫א‬                          ‫א‬
 ‫א‬‫א‬                           ١٦٢                                  



                                                                                           
،PLSQL EBlockF  ‫א‬ ‫א‬ ‫א‬    ‫א‬ ‫א‬  
 (Block),‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬
                                                                K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                      PLSQL  ESyntax F‫א‬
‫א‬‫א‬     E  ،‫א‬F ‫א‬‫א‬    PLSQL ‫א‬ 
‫א‬KKKK‫א‬،‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬
‫א‬K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                        K
                                                                               W‫א‬J
                                                                         WE‫א‬F‫א‬‫ א‬K١
                                                          
                                                        K‫א‬٣٠‫א‬J
                                                          ‫א‬‫א‬J
                                                                           KJ
                                                                     
                        K(Block) ‫א‬‫א‬J
                                                                                    W
    v_name varchr2(20);
    xyx number;
    birth_date date;
                                                                                W
    1no number ;
    Dept%id number(2);
    Select varchar2(10);
                                                            W Literal Values ‫א‬‫ א‬K٢
 ‫א‬Date‫א‬Character‫א‬‫א‬‫א‬J
                                                                                   KE‘ ‘F
    v_name:=’ali’;
                                    
            K‫א‬‫א‬‫א‬‫א‬Numbers‫א‬‫א‬J
    v_id :=201;
                                                  - ٦٠ -
  ‫א‬‫א‬                      ‫א‬‫א‬                          ‫א‬
 ‫א‬‫א‬                       ١٦٢                                  



                                                                WOperations‫ א‬K٣
                                                                  K ENOT,**F‫א‬‫א‬J
                                                                     EJ ،HF‫א‬‫ א‬J
                                                                     EL،*F‫א‬‫א‬J
             KE =,>,<,<=, <=, IS NULL, LIKE, BETWEEN, IN F‫א‬J
                                                        EAND OR F‫א‬‫א‬J J
                                                  WComments ‫א‬‫א‬J
‫א‬‫א‬ ‫א‬‫א‬‫א‬
                                            K‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬E--F‫א‬‫א‬‫א‬J
                                                                   K
‫א‬E*/ F‫א‬‫א‬E/* F‫א‬‫א‬ J
                                                        K‫א‬
    …..
    V_name varchar2(20) ;-- this variable used to hold the employee name
    Begin
    /* this code is used to read
    The employee salary and calculate the annual salary
    And print the annual salary
    */
    …….
    End ;
                                               K  Using Functions ‫א‬‫א‬‫א‬‫א‬
                                  WEBlockF‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                 W SQL‫א‬‫א‬‫א‬‫א‬ K١
      (ROUND ,TRUNC, SQRT ….FNumber Functions‫א‬‫א‬‫ א‬J
    v_sal:= ROUND(v_sal,2) ;

   ECONCAT, INITCAP, LOWER,…FCharacter Functions‫א‬‫א‬‫ א‬J
    SELECT INITCAP (enam) INTO v_name
                                              - ٦١ -
  ‫א‬‫א‬                            ‫א‬‫א‬                         ‫א‬
 ‫א‬‫א‬                             ١٦٢                                 


    FROM emp
    WHERE empno =7788;

   (ADD_MONTHS, MONTHS_BETWEEN,… ) Date   ‫א‬ ‫א‬ J
                                            Functions

    Num_months := MONTHS_BETWEEN(SYSDATE,v_date);

                                             ‫א‬‫א‬ SQL ‫א‬ J
                                                                     Decode J
                               EMIN, MAX, AVG, ….. FGroup Functions J
                                                        W‫א‬‫א‬‫א‬‫א‬‫א‬ K٢
                             TO_CHAR             ◄ CHARACTER ‫א‬J 
                          KTO_NUMBER◄  NUMBER ‫א‬J 
                             K TO_DATE◄  DATE ‫א‬J
                                                                                                 
                                                  ENested Blocks F‫א‬‫א‬‫א‬‫א‬
‫א‬،‫א‬EBlockF
‫א‬‫א‬EBlockF،ENested BlockF‫א‬‫א‬
                     K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K
                            ...
                              x BINARY_INTEGER;
                            BEGIN                                                ‫ ﻣﺠﺎل‬x
                              ...
                              DECLARE
                                y NUMBER;
                              BEGIN
                                ...                                     ‫ ﻣﺠﺎل‬y
                              END;
                              ...
                            END;


                    
                                                                                                       
                                                    - ٦٢ -
  ‫א‬‫א‬                          ‫א‬‫א‬                          ‫א‬
 ‫א‬‫א‬                           ١٦٢                                  



 x   ‫א‬EBlockF‫א‬   x   ‫א‬ ‫א‬ 
‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬‫א‬  (Block) ‫א‬
                                             
‫א‬(Blocks) ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬y K(Block) ‫א‬
                         K‫א‬(Block) ‫א‬‫א‬‫א‬
   ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J
                                               K‫א‬‫א‬(Block) ‫א‬
                                                                                             
                        ...
                            x BINARY_INTEGER;
                        BEGIN
                         ...
                        DECLARE
                          X NUMBER;
                        BEGIN
                           ...                                       ‫ داﺧﻞ هﺬﻩ‬X ‫أي اﺳﺘﺨﺪام‬
                        END;                                         ‫( ﻳﻜﻮن ل‬Block) ‫اﻟﻮﺣﺪة‬
                         ...
                        END;




                                                                                                   

    DECLARE
     v_sal                      NUMBER(7,2) := 60000;
     v_comm                    NUMBER(7,2) := v_sal * .20;
     v_message                 VARCHAR2(255) := ' eligible for commission';
    BEGIN
                                                              DECLARE
                         NUMBER(7,2) := 50000;                        v_sal
                             NUMBER(7,2) := 0;                     v_comm
               NUMBER(7,2) := v_sal + v_comm;              v_total_comp
                                                                   BEGIN
                              v_message := 'CLERK not' || v_message;
                                                                     END;



                                                  - ٦٣ -
   ‫א‬‫א‬                          ‫א‬‫א‬                          ‫א‬
 ‫א‬‫א‬                            ١٦٢                                  




   v_message := 'SALESMAN'||v_message;
    END;
                         W‫א‬‫א‬‫א‬‫א‬‫א‬
                                               ESub BlockF‫א‬‫א‬‫א‬ v_messageJ
                                             ‫א‬EBlockF‫א‬v_total_compJ
                                              ESubBlockF‫א‬‫א‬‫א‬v_comm J
                                                  ‫א‬EBlockF‫א‬ v_comm J
                                               ‫א‬EBlockF‫א‬ v_messageJ J
                                                                                                  
                                             EProgramming GuidelinesF‫א‬
   ‫א‬  ، ‫א‬ ‫א‬ ‫א‬ ‫א‬   ‫א‬   
                                                                          
‫א‬   ‫א‬ ‫א‬   ‫א‬ ‫א‬‫א‬  ‫א‬ ‫א‬ 
‫א‬ ‫א‬   ‫א‬ ‫א‬‫א‬ ‫א‬   K     
                                                                                      W‫א‬
‫א‬‫א‬‫א‬‫א‬ K١
                                            K‫א‬‫א‬‫א‬‫א‬‫א‬
                                        
                                        
E Case conventionF ‫א‬‫א‬‫א‬‫א‬‫ א‬K٢
                     W‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬

                                                  ‫א‬                             ‫א‬
               SELECT, INSERT                                                 SQL
        DECLARE ,BEGIN, END                                          ‫א‬‫א‬
                 v_sal, id, g_sal                                    ‫א‬‫א‬‫א‬
              emp, dept, ename                                 ‫א‬‫א‬‫א‬
                                                                                                        



                                                   - ٦٤ -
 ‫א‬‫א‬                             ‫א‬‫א‬                    ‫א‬
‫א‬‫א‬                              ١٦٢                            



                                                                           
                                   W‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                    ‫א‬                    ‫א‬
                   v_empno, v_sal                          v_name              variables‫א‬‫א‬
                       c_sal , c_tax                       c_name                         ‫א‬‫א‬
                       emp_cursor,                      Name_cursor             cursors ‫א‬‫א‬
                       e_too_many                         e_name            exception‫א‬‫א‬
                         p_empno                           p_name               ‫א‬‫א‬‫א‬
                                                                          substitute variables 
                          g_sal                           g_name            global‫א‬‫א‬‫א‬
                                                                                            
             W‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ K٣
                                 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
BEGIN                          BEGIN
 IF x=0 THEN                    IF x=0 THEN            y:=1; ELSE y:=2; END IF;
   y:=1;                       END;
 ELSE
  y:=2;
 END IF;
END;
                                                    




                                                    - ٦٥ -
   ‫א‬‫א‬                           ‫א‬‫א‬                             ‫א‬
  ‫א‬‫א‬                            ١٦٢                                     



                                                
                                                                                                    J ١
DECLARE
 v_weight number(3):=600;
v_message       VARCHAR2(255):='Product 10012';
BEGIN
              /* SUB BLOCK ‫א‬‫א‬‫/* א‬
     DECLARE
      v_weight number(3):=1;
     v_message VARCHAR2(255):='Product 11001';
     v_new_loc VARCHAR2(50):= 'Europe ';
     BEGIN
             v_weight :=        v_weight +1;
       v_new_loc:= 'Western '||     v_new_loc;
       END;
     v_weight :=     v_weight +1;
    v_message := v_message || 'is in Stock ';
    v_new_loc:= 'Western '||      v_new_loc;
END ;

      W‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                 KESub Block F‫א‬‫א‬‫א‬v_weight                 J 
                               KESub Block F‫א‬‫א‬‫א‬v_new_loc J 
                               KEmain Block F‫א‬‫א‬v_weight  J 
                              KEmain Block F‫א‬‫א‬v_message J 
                                KEmain Block F‫א‬‫א‬v_new_locJ 
                                                                                                           
‫א‬  EBlockF J ٢
    ‫א‬   ،‫א‬ ‫א‬   ‫א‬  ‫א‬ ‫א‬ 
                                                                                K‫א‬‫א‬
                                                                                                       
                                                                             
                                                    - ٦٦ -
   ‫א‬‫א‬                          ‫א‬‫א‬                    ‫א‬
  ‫א‬‫א‬                           ١٦٢                            



         Please enter the first number : 2
         Please Enter The Second Number :4

         G_RESULT
         -----------------------
         4.5


 ‫א‬ ‫א‬‫א‬  ‫א‬   EBlockF     J ٣
‫א‬ ‫א‬‫א‬    ‫א‬‫א‬     K ‫א‬  
                            K‫א‬K‫א‬‫א‬‫א‬

                                                                                       W
 KE 0.15١٥‫א‬‫א‬F‫א‬ J
                                   
                     KENVL‫א‬F‫א‬‫א‬‫א‬ J

 Please enter the salary amount: 50000
 Please enter the bonus percentage: 10

 PL/SQL procedure successfully completed.


   G_TOTAL
 ----------
     55000
                                                                                           




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




                          ‫א‬‫א‬
                         Oracle ‫א‬
Oracle‫א‬




٨
  ‫א‬‫א‬                          ‫א‬‫א‬                      ‫א‬
Oracle‫א‬                          ١٦٢                              



                                                                                     W‫א‬‫א‬
                               KEBlockF‫א‬‫א‬Oracle ‫א‬‫א‬
                                                                                                 
                                                                                        W‫א‬‫א‬
              EBlockF‫א‬‫א‬‫א‬‫א‬
                         EBlockF‫א‬‫א‬‫א‬‫א‬
             Transactions‫א‬‫א‬
            SQL CursorSQL‫א‬‫א‬‫א‬
                                                                                                 
                                                                            W‫א‬‫א‬‫א‬
                                K٪١٠٠Oracle‫א‬‫א‬
                                                                                                 
                                                                           W‫א‬‫א‬
                                                                               
                                                                                                 
                                                                               W‫א‬‫א‬
                                                                         -
                                                                                 H -
                                                                                                 
                                                                              W‫א‬‫א‬
                 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬




                                                 - ٦٨ -
   ‫א‬‫א‬                        ‫א‬‫א‬                           ‫א‬
 Oracle‫א‬                        ١٦٢                                   



                                                                              
 PL/SQL SQL‫א‬‫א‬ ‫א‬‫א‬‫א‬
 ETransactionF      PL/SQL      ‫א‬
،Block ‫א‬ ‫א‬  COMMIT, SAVEPOINT, ROLLBACK  ‫א‬‫א‬ 
CREATE TABLE,   Block‫א‬‫א‬‫א‬‫א‬ DDL 
                           KGRANT, REVOKE DCLALTER TABLE
                                                                                                    
                                                            Select Statement ‫א‬
                                                                                               
                                                                                   SELECT select_list
                                                             {variable_name[, variable_name]... INTO
                                                                               | record_name}
                                                                                    table      FROM
                                                                             condition;     WHERE



                ‫א‬‫א‬‫א‬‫א‬                select_list
                                                 ‫א‬‫א‬‫א‬‫א‬
     record_nameVariablesSelect_ListK                              INTO
             ‫א‬‫א‬‫א‬‫א‬E‫א‬‫א‬F‫א‬             variable_name
                                                  
                select_list‫א‬‫א‬‫א‬‫א‬
                             ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬            record_name
                                                                   ‫א‬‫א‬                   table
      ‫א‬‫א‬F‫א‬                           condition
                           Exception ‫א‬
                                                                                                        




                                                - ٦٩ -
   ‫א‬‫א‬                     ‫א‬‫א‬                     ‫א‬
Oracle‫א‬                       ١٦٢                            



                                                                                       W
DECLARE

 v_deptno NUMBER(2);
 v_loc   VARCHAR2(15);

BEGIN

 SELECT                  deptno, loc
 INTO                    v_deptno, v_loc
 FROM                    dept
 WHERE                   dname = 'SALES';

END;

                                                                                           
‫א‬'SALES'‫א‬‫ א‬‫א‬‫א‬‫א‬‫א‬
                                                 K‫א‬v_locv_deptno‫א‬‫א‬
                                         W‫א‬‫א‬‫א‬‫א‬
                                                                                           
                                    KE;F‫א‬ J
                                            KINTO‫א‬ J
  ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J
                                       K‫א‬‫א‬‫א‬
                  K%TYPE ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J
   v_deptno               dept.deptno%TPYPE;
   v_loc             dept.loc%TPYPE;
  K‫א‬‫א‬WHERE J

‫א‬‫א‬ Group Function ‫א‬‫א‬‫א‬ J
                 KPLSQL‫א‬‫א‬Group Function‫א‬
                                                                                           
                                              - ٧٠ -
   ‫א‬‫א‬                         ‫א‬‫א‬                ‫א‬
 Oracle‫א‬                         ١٦٢                        



                                                                                  
    DECLARE
     v_sum_sal            emp.sal%TYPE;
     v_deptno              NUMBER NOT NULL := 10;
    BEGIN
     SELECT               SUM(sal) -- group function
     INTO                 v_sum_sal
     FROM                 emp
     WHERE                deptno = v_deptno;
    END;


            Control Transaction‫א‬DML‫א‬‫א‬
Data ManipulationDML‫א‬‫א‬‫א‬‫א‬
                                                                                     W
                                K‫א‬WINSERT‫א‬ E١
 BEGIN
  INSERT INTO emp(empno, ename, job, deptno)
  VALUES    (empno_sequence.NEXTVAL, 'HARDING', 'CLERK', 10);
END;
                       K‫א‬‫א‬‫א‬WUPDATE‫א‬ E٢
                ‫א‬‫א‬‫א‬WHERE
                           K ‫א‬
       :=‫א‬‫א‬Z‫א‬‫א‬‫א‬ J
DECLARE
 v_sal_increase emp.sal%TYPE := 2000;
BEGIN
 UPDATE emp
 SET             sal = sal + v_sal_increase
 WHERE           job = 'ANALYST';
END;



                                                 - ٧١ -
   ‫א‬‫א‬                       ‫א‬‫א‬                      ‫א‬
 Oracle‫א‬                       ١٦٢                              



                                     K‫א‬WDELETE‫א‬ E٣
                           K ‫א‬
 DECLARE
  v_deptno emp.deptno%TYPE := 10;
 BEGIN
  DELETE        FROM emp
  WHERE        deptno = v_deptno;
 END;
‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬W
 ‫א‬ ‫א‬‫א‬ K ‫א‬     ‫א‬ WHERE ‫א‬   
‫א‬‫א‬ord‫א‬ordid‫א‬‫א‬‫א‬ ordid
             ‫א‬‫א‬،‫א‬‫א‬‫א‬‫א‬
 WHERE ordid = ordid ;
‫א‬‫א‬ 601‫א‬ ordid‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬ ordid
                             K‫א‬‫א‬‫א‬
DECLARE
  orderdate      ord.orderdate%TYPE;
  shipdate       ord.shipdate%TYPE;
  ordid          ord.ordid%TYPE := 601;
BEGIN
  SELECT       orderdate, shipdate
  INTO         orderdate, shipdate
  FROM ord
 WHERE         ordid = ordid;
END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested
number of rows
ORA-06512: at line 6

                                               - ٧٢ -
    ‫א‬‫א‬                     ‫א‬‫א‬                          ‫א‬
 Oracle‫א‬                      ١٦٢                                  



                                                                                                 
                                               WControl Transaction‫א‬
‫א‬E،،F‫א‬WTransaction
‫א‬‫א‬K‫א‬ Transaction‫א‬
                                                                                          W‫א‬
        K‫א‬‫א‬‫א‬‫א‬‫א‬COMMITJ
 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ROLLBACKJ
 ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬SAVEPOINT J
                                                        K‫א‬‫א‬
    COMMIT [WORK];
    SAVEPOINT savepoint_name;
    ROLLBACK [WORK];
    ROLLBACK [WORK] TO [SAVEPOINT] savepoint_name;

                                                      SQL Cursor SQL‫א‬‫א‬
‫א‬ Cursor ‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬‫א‬
                                                                   K‫א‬‫א‬
   ‫א‬   ‫א‬ ESQL CURSORS F‫א‬‫א‬ ‫א‬ ‫א‬  
                                                                                          K‫א‬
                                                                              W‫א‬J
 ‫א‬‫א‬‫א‬‫א‬
                                                                             W‫א‬
              KSQL‫א‬‫א‬        SQL%ROWCOUNTJ ١
                 KSQL‫א‬ETRUEF          SQL%FOUNDJ ٢
          KSQL‫א‬ETRUEF             SQL%NOTFOUNDJ ٣




                                              - ٧٣ -
      ‫א‬‫א‬                  ‫א‬‫א‬                        ‫א‬
 Oracle‫א‬                     ١٦٢                                



                         
SQL CURSORS K ‫א‬  ‫א‬ ETRUEF  SQL%ISOPEN J ٤
‫א‬‫א‬‫א‬‫א‬ FALSE‫א‬‫א‬‫א‬‫א‬
                                                                             K‫א‬
                                                                                         
 1                      SQL> VARIABLE rows_deleted VARCHAR2(30)

 2                 DECLARE
 3                 v_ordid NUMBER := 605;
 4                 BEGIN
 5                 DELETE FROM item
 6                 WHERE        ordid = v_ordid;
 7                 :rows_deleted := (SQL%ROWCOUNT ||' rows deleted.');
 8                 END;
 9                 /
 10                SQL >PRINT rows_deleted

                                                                                              
    SQL*Plus    rows_deleted ‫א‬   ‫א‬ ‫א‬ 
605orderitem‫א‬‫א‬EBlockF
‫א‬ ‫א‬‫א‬ SQL%ROWCOUNT‫א‬‫א‬ ٧‫א‬
Krows_deleted‫א‬ ' rows deleted.' ||‫א‬‫א‬،
                                                                KSQL*Plus‫א‬




                                             - ٧٤ -
   ‫א‬‫א‬                         ‫א‬‫א‬                            ‫א‬
 Oracle‫א‬                         ١٦٢                                    



                                             
                                                            WEBlockF‫א‬J ١
                                                     KDEPT ‫א‬J
                                                                KSQL*Plus‫א‬J
                                                                      K‫א‬‫א‬J
                                                                        Kp8q1.sql ‫א‬J
 G_MAX_DEPTNO
 ------------
        40
‫א‬ DEPT ‫א‬‫א‬‫א‬ ‫א‬ J ٢
                                                                    W‫א‬ . p8q2.sql 
                       K‫א‬‫א‬‫א‬١٠‫א‬‫א‬J
              K‫א‬‫א‬SQL*PlusSubstitution Variable ‫א‬J
                                                             K location NULLJ
                                                                         KEBlockF‫א‬J
Please enter the department name: EDUCATION

PL/SQL procedure successfully completed.
                               K‫א‬‫א‬‫א‬J
  DEPTNO        DNAME            LOC
   --------- --------------    -------------
    50       EDUCATION
 DEPTK location ‫א‬EBlockF‫א‬J ٣
                                            W‫א‬K . p8q3.sql ‫א‬
            K‫א‬SQL*PlusSubstitution Variable ‫א‬J
     Klocation ‫א‬SQL*PlusSubstitution Variable ‫א‬J
 Please enter the department number: 50
 Please enter the department location: HOUSTON
PL/SQL procedure successfully completed.

                                                 - ٧٥ -
   ‫א‬‫א‬                      ‫א‬‫א‬                         ‫א‬
 Oracle‫א‬                      ١٦٢                                 



                                      K‫א‬‫א‬J
   DEPTNO DNAME                        LOC
 ----------  --------------            -------------
      50    EDUCATION                HOUSTON

‫א‬ K‫א‬‫א‬‫א‬‫א‬‫א‬ EBlockF‫א‬ J ٤
                                                  W‫א‬K . p8q4.sql 
            K‫א‬SQL*PlusSubstitution Variable ‫א‬J
                                                         K‫א‬‫א‬J
KPlease enter the department number: 50
PL/SQL procedure successfully completed.

G_RESULT
--------------------------------------------------------------------------------
1 row(s) deleted.

                                   ‫؟‬‫א‬J
Please enter the department number: 99
PL/SQL procedure successfully completed.

G_RESULT
--------------------------------------------------------------------------------
0 row(s) deleted.
                                                                                                
                                              . ‫א‬‫א‬J




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




            ‫א‬‫א‬
               ‫א‬
‫א‬




٩
 ‫א‬‫א‬                         ‫א‬‫א‬                        ‫א‬
  ‫א‬                                  ١٦٢                                



                                                                                     W‫א‬‫א‬
                    K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                
                                                                                         W‫א‬‫א‬
                              K‫א‬‫א‬‫א‬‫א‬
                                      K‫א‬‫א‬‫א‬
                                    K‫א‬‫א‬‫א‬‫א‬
                                                        K‫א‬‫א‬‫א‬
                                           K‫א‬‫א‬‫א‬‫א‬‫א‬

                                                                          W‫א‬‫א‬‫א‬
                                 K٪١٠٠‫א‬‫א‬‫א‬
                                                                                                
                                                                             W‫א‬‫א‬
                                                                               ٣
                                                                                                
                                                                                  W‫א‬‫א‬
                                                                           -
                                                                               H -
                                                                                                
                                                                                  W‫א‬‫א‬
                             K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬




                                               - ٧٧ -
    ‫א‬‫א‬                          ‫א‬‫א‬                     ‫א‬
     ‫א‬                                   ١٦٢                             



                                                                                             W
 ‫א‬    ‫א‬ ‫א‬  ‫א‬   EBlockF  ‫א‬  
 ‫א‬‫א‬‫א‬‫א‬
  ٪٨‫א‬‫א‬ ٣٠٠٠ ‫א‬ ٪١٠‫א‬‫א‬
 K‫א‬‫א‬‫א‬ K٣٠٠٠‫א‬‫א‬
                         K‫א‬‫א‬‫א‬‫א‬
                                                                                    
                                                            KIF Statement ‫א‬‫א‬
 ‫א‬    ‫א‬‫א‬ ‫א‬  ‫א‬   ‫א‬  IF  
                                                                             K‫א‬
  IF condition THEN
   statements;
 END IF;

 Ali  v_ename‫א‬‫א‬‫א‬ v_sal٣٥٠٠‫א‬‫א‬
   . v_sal :=  ‫א‬   ‫א‬ E‫א‬F‫א‬     True  ‫א‬  
 False‫א‬Aliv_ename‫א‬‫א‬3500
               W END IF‫א‬‫א‬‫א‬v_sal := 3500;‫א‬
  ….
  IF v_ename = ‘Ali’ THEN
   v_sal := 3500;
  END IF;
  …….
                                                   : IF THEN ELSE ‫א‬
 ‫א‬‫א‬‫א‬‫א‬‫א‬ IF THEN ELSE
                      K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬




                                                   - ٧٨ -
   ‫א‬‫א‬                         ‫א‬‫א‬                         ‫א‬
     ‫א‬                                 ١٦٢                                 




       
IF CONDITION1 THEN
 Statement1;
ELSE
Statement2.
END IF;

IF v_deptno = 10 THEN
     UPDATE emp
     SET sal = sal * 1.10
     WHERE deptno = v_deptno;
ELSE
     UPDATE emp
     SET sal = sal * 1.08
     WHERE deptno = v_deptno;
END IF;

                                                                                         
‫א‬‫א‬١٠‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬
‫א‬‫א‬E‫א‬F‫א‬‫א‬،٪١٠‫א‬
                                                                                K٪٨‫א‬
IF CONDITION1 THEN
 Statement1;
 ELSE
                                                 - ٧٩ -
   ‫א‬‫א‬                         ‫א‬‫א‬                        ‫א‬
    ‫א‬                                  ١٦٢                                



  IF CONDITION2 THEN
  Statement2;
   END IF;
END IF;

 IF v_deptno = 10 THEN
       UPDATE emp
       SET sal = sal * 1.10
       WHERE deptno = v_deptno;
 ELSE
    IF v_job = ‘SALESMAN’ THEN
       UPDATE emp
       SET sal = sal * 1.11
       WHERE job = v_job;
 END IF;
‫א‬‫א‬١٠‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬E‫א‬F‫א‬‫א‬،٪١٠‫א‬
     K٪٨‫א‬‫א‬‫א‬‫א‬SALESMAN 
                                                                                                  
                                                    WIFTHEN ELSIF ‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬ IF THEN ELSIF
   (ELSIF) ‫א‬ ‫א‬    IF  ‫א‬  ‫א‬  
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
 IF IF IF
                                                           KESLE‫א‬




                                                 - ٨٠ -
  ‫א‬‫א‬           ‫א‬‫א‬    ‫א‬
   ‫א‬                    ١٦٢            




IF CONDITION1 THEN
  Statement1;
ELSIF CONDITION2 THEN
    Statement2;
ELSIF CONDITION3 THEN
    Statement3;
.
.
.
ELSE
    StatementN;
END IF;
IF v_grade >100 OR v_grade < 0 THEN
     DBMS_OUTPUT.PUT_LINE(‘Invalid Grade ’);
ELSEF v_grade >= 90 THEN
     DBMS_OUTPUT.PUT_LINE(‘A’);
ELSIF v_grade >= 80 THEN
     DBMS_OUTPUT.PUT_LINE(‘B’);

ELSIF v_grade >= 70 THEN
   DBMS_OUTPUT.PUT_LINE(‘C’);
ELSIF v_grade >= 60 THEN
                                  - ٨١ -
     ‫א‬‫א‬                     ‫א‬‫א‬                      ‫א‬
      ‫א‬                                ١٦٢                            



     DBMS_OUTPUT.PUT_LINE(‘D’);
  ELSE
     DBMS_OUTPUT.PUT_LINE(‘F’);
  END IF;
    ٠‫א‬‫א‬v_grade‫א‬‫א‬‫א‬‫א‬‫א‬
  ‫א‬K١٠٠٠‫א‬‫א‬١٠٠
      W‫א‬‫א‬‫א‬‫א‬‫א‬ ١٠٠٠‫א‬
                                     ‫א‬         ‫א‬
                                       A           ١٠٠٩٠
                                       B            ٨٩٨٠
                                       C            ٧٩٧٠
                                       D            ٦٩٦٠
                                       F             ٥٩J ٠
                                                                     W‫א‬‫א‬‫א‬‫א‬
  ‫א‬‫א‬‫א‬ IF 
  ‫א‬‫א‬IF
                                                                         W‫א‬
   IS NULL ‫א‬NULL ‫א‬‫א‬‫א‬‫א‬‫א‬J
  IF v_name IS NULL THEN
  …
  ….
 END IF
             KNULL ‫א‬NULL J 
  v_sal := 3000;
 v_comm ;v_ annual_salary := 12 * v_sal +
              v_ ‫א‬NULL ‫א‬‫א‬ v_comm‫א‬
                                                                  KNULLannual_salary
            K‫א‬NULL NULL ‫א‬J
     …
    v_job := NULL;
                                                - ٨٢ -
   ‫א‬‫א‬                         ‫א‬‫א‬                 ‫א‬
    ‫א‬                                  ١٦٢                         



    v_name:=’AHMED’;
    v_info:= v_name || ‘ is ‘ || v_job ;
    ….
v_jobAHMED is v_info‫א‬‫א‬‫א‬
                                                                                 K
                     AND ,OR , NOT ‫א‬‫א‬‫א‬J

  IF v_sal > 3000 AND v_job = ‘SALESMAN’ OR v_deptno = 10 THEN
   …..
  END IF ;
                  W‫א‬‫א‬‫א‬
            AND                TRUE            FLASE           NULL
           TRUE                TRUE            FLASE           NULL
           FLASE              FLASE            FLASE           FLASE
           NULL                NULL            FLASE           NULL

                  OR                 TRUE                  FLASE         NULL
                 TRUE                TRUE                  TRUE          TRUE
                 FLASE               TRUE                  FLASE         NULL
                 NULL                TRUE                  NULL          NULL

                  NOT
                 TRUE                FLASE
                 FLASE               TRUE
                 NULL                TRUE




                                                 - ٨٣ -
   ‫א‬‫א‬                        ‫א‬‫א‬                        ‫א‬
    ‫א‬                                 ١٦٢                                



                                                                                              
 ;  v_reorder_flag ANDv_reorder_flag                     v_flag :=

                                                         W‫א‬‫א‬v_flag 
                                            
             v_reorder_flag          v_reorder_flag                v_flag
                     TRUE                TRUE                      TRUE
                     TRUE               FALSE                      FALSE
                     NULL                TRUE                      NULL
                     NULL               FALSE                      FALSE

                                                                       KLoops ‫א‬‫א‬
    ‫א‬     ‫א‬‫א‬ ‫א‬‫א‬  PL /SQL  
 ‫א‬ ‫א‬ ‫א‬‫א‬‫א‬،‫א‬
                      W‫א‬PL /SQLK‫א‬‫א‬
                                                   KBasic Loop‫א‬‫א‬‫א‬ J
                                                                      KFOR‫א‬‫א‬ J
                                                                  KWHILE ‫א‬‫א‬ J
                                             KNested LOOPS ‫א‬‫א‬‫א‬‫א‬ J
                                                        :Basic Loop ‫א‬‫א‬‫א‬
، End Loop‫א‬‫א‬ Loop ‫א‬‫א‬‫א‬‫א‬
E‫א‬‫א‬F‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬
  ‫א‬‫א‬      ‫א‬   E‫א‬ ‫א‬‫א‬   F ‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬‫א‬ Exit
                                                             K End Loop‫א‬‫א‬




                                                - ٨٤ -
 ‫א‬‫א‬                 ‫א‬‫א‬                          ‫א‬
  ‫א‬                          ١٦٢                                  



                                                                                          
      LOOP                                                   ‫א‬‫א‬‫א‬
                                                           ‫א‬‫א‬
         statement1;
         ...                                                            
                                       F‫א‬
       EXIT [WHEN
                                               E‫א‬‫א‬
      condition];

                                                         ‫א‬‫א‬

      END LOOP;



                                                                                     
        DECLARE

        item.ordid%TYPE := 601; v_ordid
        NUMBER(2) := 1;       v_counter

        BEGIN

          LOOP

            INSERT INTO item(ordid, itemid)
            VALUES(v_ordid, v_counter);
            v_counter := v_counter + 1;
            EXIT WHEN v_counter > 10;

        END LOOP;

        END;

                                                                                          

                                       - ٨٥ -
   ‫א‬‫א‬                    ‫א‬‫א‬                    ‫א‬
    ‫א‬                             ١٦٢                            



‫א‬‫א‬ item items ١٠‫א‬‫א‬
                                    K١٠v_counter‫א‬‫א‬
                                                                                         
                                            W‫א‬‫א‬‫א‬‫א‬W
                                                                                   J 
DECLARE
v_counter NUMBER :=0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('v_counter = '||v_counter);
EXIT WHEN v_counter > 5;
v_counter:=v_counter+1;
END LOOP;
END ;

                                                                                   J 
DECLARE
v_counter NUMBER :=0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('v_counter = '||v_counter);
EXIT WHEN v_counter > 5;
v_counter:=v_counter-1;
END LOOP;
END ;

                                                                                    J 
DECLARE
v_counter NUMBER :=10;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('v_counter = '||v_counter);
EXIT WHEN v_counter > 5;
v_counter:=v_counter+1;
                                            - ٨٦ -
   ‫א‬‫א‬                  ‫א‬‫א‬                       ‫א‬
    ‫א‬                           ١٦٢                               




       For counter in [REVERSE]                                          ‫א‬‫א‬‫א‬
       Lower_bound .. upper_pound
       LOOP
                                                                         ‫א‬‫א‬
               statement1;
          statement2;
          ...                                                       ‫א‬‫א‬

   END LOOP;
 END LOOP;
 END ;
                                                                                             
                                                                          ‫א‬٧          J 
                                                            ‫א‬‫א‬          J 
                                                                     ‫א‬١          J 
‫א‬‫א‬EF‫א‬‫א‬‫א‬‫א‬W‫א‬
                                                      ‫؟‬Loop 
                                                                                   J 
                                                                                   J 
                                                                                          J 
                                                              : FOR ‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬ ‫א‬ FOR‫א‬‫א‬
                                  KKKK،١٥،‫א‬١٠‫א‬




                                          - ٨٧ -
  ‫א‬‫א‬                          ‫א‬‫א‬                           ‫א‬
   ‫א‬                                   ١٦٢                                   




‫א‬Lower_bound ‫א‬‫א‬ J
                                                                                           Counter
                     ‫א‬‫א‬‫א‬‫א‬‫א‬١‫א‬
       KE‫א‬‫א‬reverse ‫א‬‫א‬Fupper_pound
                                  K‫א‬‫א‬J
                
      ‫א‬‫א‬‫א‬‫א‬‫א‬J
                                                                             K‫א‬‫א‬
                                                 K‫א‬‫א‬J
‫א‬‫א‬‫א‬‫א‬‫א‬J                REVERSE
                                                                            ‫א‬
         K‫א‬‫א‬‫א‬‫א‬‫א‬‫ א‬J
                                                                                       Lower_cound
                        
  F‫א‬ J
                                                       KE
                     ‫א‬‫א‬‫א‬‫א‬‫א‬J
                                                                                       upper_cound
                              
 F‫א‬J
                                                                        KE


  BEGIN
   FOR i IN 1..5 LOOP
   DBMS_OUTPUT.PUT_LINE('i= '||i);
   END LOOP;
  END;
SQL> /
i= 1
i= 2
i= 3
i= 4
i= 5



                                                 - ٨٨ -
   ‫א‬‫א‬                   ‫א‬‫א‬                          ‫א‬
    ‫א‬                            ١٦٢                                  



                                       W‫א‬‫א‬‫א‬Block ‫א‬
  DECLARE
 v_lower   number:=1;
 v_upper number:=5;
BEGIN
   FOR i IN v_lower.. v_upper LOOP
   DBMS_OUTPUT.PUT_LINE('i= '||i);
   END LOOP;
  END;
SQL> /
i= 1
i= 2
i= 3
i= 4
i= 5

                                                              : WHILE ‫א‬‫א‬
‫א‬‫א‬ ‫א‬ ‫א‬     ‫א‬ ‫א‬  WHILE  ‫א‬‫א‬  
                                    K‫א‬‫א‬‫א‬‫א‬‫א‬،
               WHILE (condition)                                 ‫א‬‫א‬‫א‬
                                                               ‫א‬‫א‬
                     statement1;
                             ...                             ‫א‬‫א‬
                     END LOOP;
                                                                                        J 
DECLARE
v_counter NUMBER :=0;
BEGIN
WHILE (v_counter <= 5) LOOP
DBMS_OUTPUT.PUT_LINE('v_counter = '||v_counter);
 v_counter:=v_counter+1;
END LOOP;
END ;


                                           - ٨٩ -
  ‫א‬‫א‬                      ‫א‬‫א‬                      ‫א‬
   ‫א‬                               ١٦٢                              



  ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬while ‫א‬‫א‬
    K‫א‬‫א‬K‫א‬‫א‬‫א‬
DECLARE
v_counter NUMBER :=0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('v_counter = '||v_counter);
EXIT WHEN v_counter > 5;
v_counter:=v_counter+1;
END LOOP;
END ;

  WHILE‫א‬‫א‬‫א‬‫א‬‫א‬W‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬W
                                                                                item
ACCEPT p_new_order PROMPT 'Enter the order number: '
ACCEPT p_items -
 PROMPT 'Enter the number of items in this order: '
DECLARE
NUMBER(2) := 1;           v_count
BEGIN
 WHILE v_count <= &p_items LOOP
  INSERT INTO item (ordid, itemid)
  VALUES (&p_new_order, v_count);
  v_count := v_count + 1;
 END LOOP;
 COMMIT;
END;

                                                                                            




                                             - ٩٠ -
   ‫א‬‫א‬                      ‫א‬‫א‬                       ‫א‬
    ‫א‬                               ١٦٢                               



                                                   : Nested Loops ‫א‬‫א‬‫א‬‫א‬
       ‫א‬ ‫א‬‫א‬     
‫א‬KEWHILE FOR،Basic Loops‫א‬F‫א‬‫א‬
‫א‬‫א‬‫א‬K‫א‬ELabelF‫א‬‫א‬
‫א‬‫א‬    ‫א‬  (Exit)‫א‬   ‫א‬ ‫א‬‫א‬ ELabelF‫א‬ 
                           K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
 BEGIN
‫א‬‫א‬‫א‬‫א‬ <<Outer_loop>>
  LOOP
   v_counter := v_counter+1;
  EXIT WHEN v_counter>10;
‫א‬‫א‬‫א‬‫א‬‫א‬ <<Inner_loop>>
   LOOP
     ...
     EXIT Outer_loop WHEN total_done = 'YES';
‫א‬‫א‬‫א‬       -- Leave both loops
     EXIT WHEN inner_done = 'YES';
‫א‬‫א‬‫א‬‫א‬‫א‬    -- Leave inner loop only
     ...
   END LOOP Inner_loop;
   ...
  END LOOP Outer_loop;
 END;




                                              - ٩١ -
   ‫א‬‫א‬                    ‫א‬‫א‬                      ‫א‬
    ‫א‬                             ١٦٢                              



                                       
                                               W‫א‬‫א‬‫א‬‫א‬J ١
CREATE TABLE messages
VARCHAR2(60))  (results
                                                                                           
     KEFOR‫א‬‫א‬‫א‬FK٨،٦‫א‬‫א‬١٠KKK١‫א‬J
                          KEBlockF‫א‬‫א‬‫א‬‫א‬J
                                          Kmessages‫א‬J
  RESULTS
----------
       1
       2
       3
       4
       5
       7
       9
      10

            WNULLsalary ‫א‬‫א‬،emp‫א‬‫א‬J ٢
                                                                                           
insert into emp
values (8000, 'DOE', 'CLERK', 7698, SYSDATE, NULL,
NULL, 10);


‫א‬‫א‬E PL/SQL BlockF J
                                                                W‫א‬‫א‬
                          Ksubstitution variable ‫א‬‫א‬‫א‬ J 
        W‫א‬‫א‬‫א‬(commission)‫א‬ J 
                                                                                      

                                            - ٩٢ -
   ‫א‬‫א‬                      ‫א‬‫א‬                         ‫א‬
    ‫א‬                               ١٦٢                                 



                                          ‫א‬               ‫א‬‫א‬
                                                     ٪١٠         ١٠٠٠
                                                     ٪١٥        ١٥٠٠J ١٠٠٠
                                                     ٪٢٠        ١٥٠٠‫א‬
                                                                  NULL
                                                                                        
                             K‫א‬emp‫א‬ J 
‫א‬message E‘The number is odd’Fp6q4.sql‫א‬J ٣
                                                                            
             K‫א‬‫א‬‫א‬E‘The number is even’F‫א‬
                                        KempSTARS  K١
                                         
‫א‬‫א‬١٠٠STARS‫א‬* E PL/SQL BlockF K٢
‫א‬KE**********STARS١٠٠٠‫א‬‫א‬‫א‬F
                                                                          Kp9q3.sql
                           Ksubstitution variable ‫א‬‫א‬‫א‬J 
                                   K‫א‬ J
                                                                                        
                                                                                                




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




                   ‫א‬‫א‬
                    ‫א‬‫א‬
‫א‬‫א‬




١٠
  ‫א‬‫א‬                    ‫א‬‫א‬                            ‫א‬
 ‫א‬‫א‬                        ١٦٢                                    



                                                                                 W‫א‬‫א‬
                                      K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                
                                                                                        W‫א‬‫א‬
                                                        K‫א‬‫א‬
                           K‫א‬‫א‬‫א‬‫א‬
                                  K‫א‬‫א‬‫א‬
                                                                                                
                                                                        W‫א‬‫א‬‫א‬
                      K٪١٠٠‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                                
                                                                           W‫א‬‫א‬
                                                                             3
                                                                                                
                                                                                W‫א‬‫א‬
                                                                        K -
                                                                              KH -
                                                                                                
                                                                           W‫א‬‫א‬
                         K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬




                                           - ٩٤ -
    ‫א‬‫א‬                             ‫א‬‫א‬                             ‫א‬
   ‫א‬‫א‬                                 ١٦٢                                     



                                                                                           W
                     
‫א‬Block ‫א‬‫א‬‫א‬
                                                                
‫א‬‫א‬‫א‬ ‫א‬‫א‬K‫א‬‫א‬
‫א‬،‫א‬‫א‬‫א‬‫א‬
                                 
‫א‬‫א‬K‫א‬‫א‬‫א‬KEErrorOracleF
                          
‫א‬‫א‬‫א‬،‫א‬‫א‬
                                                                         K EBlockF‫א‬
                                                                            W‫א‬‫א‬‫א‬
         K‫א‬‫א‬‫א‬
                                                                   W‫א‬‫א‬
                          : Predefined Oracle Server errors ‫א‬‫ א‬J ١
                                                                             
‫א‬K‫א‬‫א‬‫א‬‫א‬٢٠
                                                
 Oracle FOracle  ERAISEF
                                                                                         KEServer

                : Non-Predefined Oracle server errors ‫א‬‫ א‬J ٢
K‫א‬oracle
 Oracle ,EBlockF‫א‬‫א‬
                                                                             KEOracle Server F

                                    WUser Defined Exceptions‫א‬‫א‬‫ א‬J ٣
                   
EBlockF‫א‬‫א‬
                                                              
                                 K‫א‬‫א‬  ‫א‬‫א‬K‫א‬‫א‬
                                                       
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                             
KEBlockF‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬WHEN
                                                             
K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
  
K‫א‬‫א‬‫א‬WHEN OTHERS THEN
                    
‫א‬‫א‬‫א‬‫א‬‫א‬WHEN     OTHERS
                                                      - ٩٥ -
      ‫א‬‫א‬                                  ‫א‬‫א‬                                   ‫א‬
    ‫א‬‫א‬                                        ١٦٢                                          



             
   ‫א‬WHEN‫א‬K
 K‫א‬‫א‬‫א‬‫א‬‫א‬
  EXCEPTION
    WHEN exception1 [OR exception2 . . .] THEN
       statement1;
       statement2;
       ...
    [WHEN exception3 [OR exception4 . . .] THEN
       statement1;
       statement2;
       . . .]
    [WHEN OTHERS THEN
       statement1;
       statement2;
       . . .]

                                                                            K‫א‬‫א‬‫א‬
  ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
                                                                                         
  ‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬
                                                                                                                KEBlockF
                                                                                 
 oracle ‫א‬‫א‬‫א‬‫א‬
                                                          
  ‫א‬K‫א‬‫א‬KEOracle Server F
  ‫א‬‫א‬‫א‬‫א‬EBlockF‫א‬‫א‬‫א‬
                         K‫א‬‫א‬‫א‬‫א‬‫א‬K




                                                              - ٩٦ -
  ‫א‬‫א‬                       ‫א‬‫א‬                  ‫א‬
 ‫א‬‫א‬                           ١٦٢                          




                       ‫א‬                                            ‫א‬
                              ACCESS_INTO_NULL
                               initialization
  nested collection method                     COLLECTION_IS_NULL
 Kinitialization  varraytables
                                       ‫א‬          CURSOR_ALREADY_OPEN
                                           DUP_VAL_ON_INDEX
                                              INVALID_CURSOR
  ‫א‬‫א‬‫א‬‫א‬                      LOGIN_DENIED
                .Select ‫א‬                NO_DATA_FOUND
   ‫א‬‫א‬                NOT_LOGGED_ON
                                 ‫א‬                  PROGRAM_ERROR
   ‫א‬‫א‬‫א‬                   ROWTYPE_MISMATCH
                                       PL/SQL
        ‫א‬‫א‬‫א‬‫א‬                   STORAGE_ERROR
 collection method ‫א‬ SUBSCRIPT_BEYOND_COUNT
       ‫א‬varraytables nested
                                        K‫א‬
        ‫א‬‫א‬‫א‬‫א‬               TIMEOUR_RESOURCE
        Select ‫א‬                      TOO_MANY_ROWS
                         VALUE_ERROR
                                             ‫א‬
                                  ‫א‬                   ZERO_DEVIDE




                                              - ٩٧ -
  ‫א‬‫א‬                    ‫א‬‫א‬                     ‫א‬
 ‫א‬‫א‬                        ١٦٢                             




DECLARE
V_ename   emp.ename%Type;
V_empno emp.empno%Type := &p_eno;
Begin
 SELECT ename INTO
 v_ename
 From    emp
 WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE ('Employee Name is ' || v_ename );

EXCEPTION

WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (' Invalid Employee Number ' || v_empno);
END;

‫א‬‫א‬٧٧٧٧،٧٧٨٨‫א‬‫א‬‫א‬
                                       K٧٧٧٧‫א‬
Enter value for p_eno: 7788

Employee Name is SCOTT

PL/SQL procedure successfully completed.

SQL> /
Enter value for p_eno: 7777

Invalid Employee Number 7777

PL/SQL procedure successfully completed.




                                           - ٩٨ -
  ‫א‬‫א‬                  ‫א‬‫א‬               ‫א‬
 ‫א‬‫א‬                      ١٦٢                       



                                                                            
DECLARE
V_ename   emp.ename%Type;
V_job emp.job%Type := upper( '&p_ejob');
Begin
 SELECT ename INTO
 v_ename
 From    emp
 WHERE job= v_job;
DBMS_OUTPUT.PUT_LINE ('Employee Name is ' || v_ename );
EXCEPTION

WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('There is no Job Employee has this '|| V_job
);

WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('The Job '|| V_job || ' has more than one
Employee ' );

END;
          DRIVER ،MANAGER،PRESIDENT‫א‬‫א‬‫א‬
 MANAGER‫א‬‫א‬‫א‬‫א‬
                            KDRIVER

  Enter value for p_ejob: PRESIDENT

old 3: V_job         emp.job%Type := upper( '&p_ejob');
new 3: V_job          emp.job%Type := upper( 'PRESIDENT');

Employee Name is KING

PL/SQL procedure successfully completed.

SQL> /

                                         - ٩٩ -
   ‫א‬‫א‬                       ‫א‬‫א‬                           ‫א‬
  ‫א‬‫א‬                           ١٦٢                                   



Enter value for p_ejob: MANAGER

old 3: V_job          emp.job%Type := upper( '&p_ejob');
new 3: V_job           emp.job%Type := upper( 'MANAGER');

The Job MANAGER has more than one Employee

PL/SQL procedure successfully completed.

SQL> /
Enter value for p_ejob: DRIVER


There is no Job Employee has this DRIVER

PL/SQL procedure successfully completed.
                                                              :‫א‬‫א‬‫א‬
                           
K‫א‬oracle
       oracle,EBlockF‫א‬‫א‬
                                                                  KEOracle Server F 




                                          W‫א‬‫א‬‫א‬‫א‬
                                      :EBlockF‫א‬‫א‬‫א‬ K١
                                                                                   DECLARE
                                                                                              …..
                                                     Exception_name EXCEPTION;
                                              - ١٠٠ -
    ‫א‬‫א‬                    ‫א‬‫א‬                    ‫א‬
  ‫א‬‫א‬                         ١٦٢                            



 ‫א‬PRAGMA_EXCEPTION_INIT‫א‬‫א‬‫א‬ K٢
                                                        
   ‫א‬‫א‬ ‫א‬‫א‬
                                                      K‫א‬‫א‬
                  PRAGMA_EXCEPTION_INIT(Exception_name ,error_number);
                             WEBlockF‫א‬‫א‬‫א‬‫א‬ K٣
  ‫א‬WHEN‫א‬‫א‬‫א‬J
                                                                            K‫א‬
 K‫א‬‫א‬‫א‬J
                                                                                  W

 DECLARE                                                                                  
EXCEPTION;             e_emps_remaining                                      ‫א‬

   PRAGMA EXCEPTION_INIT                                              ‫א‬‫א‬
( e_emps_remaining , -2292);
                                                                                  -٢٢٩٢

  dept.deptno%TYPE := &p_deptno; v_deptno
 BEGIN
  DELETE FROM dept
 deptno = v_deptno; WHERE
  COMMIT;
 EXCEPTION
  WHEN e_emps_remaining THEN                                                              
   DBMS_OUTPUT.PUT_LINE ('Cannot remove
                                                                            ‫א‬
 dept ' ||
   TO_CHAR(v_deptno) || '. Employees exist. ');
 END;

  ‫א‬J ٢٢٩٢‫א‬e_emps_remaining‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬
                                        K‫א‬،

                                            - ١٠١ -
   ‫א‬‫א‬                       ‫א‬‫א‬                            ‫א‬
  ‫א‬‫א‬                           ١٦٢                                    



                                                           W‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬
     ‫א‬EBlockF‫א‬‫א‬
                                                           
                              K‫א‬‫א‬  ‫א‬‫א‬K‫א‬




                                           W‫א‬‫א‬‫א‬‫א‬
                                        :EBlockF‫א‬‫א‬‫א‬ J ١
DECLARE
…..
Exception_name EXCEPTION;
                    
 EBlockF‫א‬‫א‬‫א‬‫א‬ J ٢
                       K‫א‬RAISE ‫א‬‫א‬
Begin
….
RAISE Exception_name             ;
….
                                     WKEBlockF‫א‬‫א‬‫א‬‫א‬ J ٣
WHEN‫א‬‫א‬‫א‬‫א‬
                                                                 K‫א‬‫א‬
،‫א‬‫א‬‫א‬‫א‬‫א‬ W
    ‫א‬ e_invalid_product‫א‬E‫א‬F‫א‬
                                             K‫א‬‫א‬‫א‬‫א‬K




                                              - ١٠٢ -
  ‫א‬‫א‬                    ‫א‬‫א‬                     ‫א‬
 ‫א‬‫א‬                        ١٦٢                             




DECLARE

 EXCEPTION;e_invalid_product                                              ‫א‬
BEGIN
product UPDATE
descrip = '&product_description'                       SET
prodid = &product_number; WHERE

 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;


                                           : SQLERRM ‫ و‬SQLCODE‫א‬‫- א‬
  ‫א‬‫א‬‫א‬‫א‬‫א‬SQLERRMSQLCODE J
‫א‬‫א‬‫א‬‫א‬
                               K‫א‬‫א‬‫ א‬‫א‬
                                             K‫א‬‫א‬‫א‬WSQLCODE J
                                   K‫א‬‫א‬‫א‬SQLERRM J
                                            ‫א‬       SQLCODE
                                   ‫א‬                 ٠
                         ‫א‬‫א‬                  ١
                            NO_DATA_FOUND                         H١٠٠
                     ORACLE              

                                          - ١٠٣ -
  ‫א‬‫א‬                      ‫א‬‫א‬                                ‫א‬
 ‫א‬‫א‬                          ١٦٢                                        



                                                            ‫؟‬‫א‬‫א‬
   ‫א‬‫א‬‫א‬‫א‬ ‫א‬‫א‬‫א‬
      ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬‫א‬
                               K‫א‬KKKK‫א‬‫א‬

DECLARE
 ...
     EXCEPTION;         e_no_rows
EXCEPTION;      e_integrity
 PRAGMA EXCEPTION_INIT (e_integrity, -2292);
BEGIN
 FOR c_record IN emp_cursor LOOP

                                                                                                        
             BEGIN
                                                         ‫א‬‫א‬‫א‬‫א‬
             SELECT ...
             UPDATE ...                                      
                                                         ‫א‬Sub Block               
             IF SQL%NOTFOUND THEN                        ‫א‬‫א‬‫א‬‫א‬
             RAISE e_no_rows;
             END IF;                                                            
                                                         ‫א‬‫א‬
             EXCEPTION                                   Main         Block‫א‬‫א‬
             WHEN e_integrity THEN ...                                                  
                                                         ‫א‬
             WHEN e_no_rows THEN ...
             END;                                                                  
                                                         ‫א‬‫א‬
                                                                                 Main Block

END LOOP;                      ‫א‬‫א‬
EXCEPTION
                               ‫א‬Main Block ‫א‬
 WHEN NO_DATA_FOUND THEN . . .
                                                                       
 WHEN TOO_MANY_ROWS THEN . . . ‫א‬ ‫א‬
END;                                                                    
                                SQL ‫א‬‫א‬‫א‬
                                                                                *Plus




                                            - ١٠٤ -
  ‫א‬‫א‬                      ‫א‬‫א‬                   ‫א‬
 ‫א‬‫א‬                          ١٦٢                           



                               WRAISE_APPLICATION_ERROR ‫א‬‫א‬‫א‬‫א‬
‫א‬‫א‬‫א‬‫א‬‫א‬
            Procedures, F‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬K‫א‬
                                                                            KEFunctions

   RAISE_APPLICATION_ERROR(error_number,message,[TRUE,FLASE]);
    ‫א‬K‫א‬‫א‬        error_number
                                       J ٢٠٩٩٩J ٢٠٠٠٠‫א‬
                                     ‫א‬            message
    Stack.‫א‬‫א‬‫א‬‫א‬TRUEK‫א‬           [TRUE,FLASE]
         K‫א‬‫א‬‫א‬FLASE
                                                  EDefualtF‫א‬‫א‬
  ‫א‬‫א‬RAISE_APPLICATION_ERROR‫א‬‫א‬
                                                                               K‫א‬‫א‬
…
BEGIN
….

IF (v_grade > 100 OR v_grade < 0) THEN
RAISE_APPLICATION_ERROR(-20210,’invlid grade ’);
END IF;
…..
END;

…
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20010,’invlid employee number’);
…..
END;


                                            - ١٠٥ -
  ‫א‬‫א‬                          ‫א‬‫א‬                             ‫א‬
 ‫א‬‫א‬                              ١٦٢                                     



                                       
 KE‫א‬‫א‬F‫א‬‫א‬‫א‬‫א‬E PL/SQL BlockF J ١
          ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J
         Kmessage More than one employee with a salary<salary>
                    ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J
                      Kmessage No employee with a salary<salary>   
                                                                
                       ‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J
                                                                                Kmessage
                                                                    K‫א‬‫א‬ J
RESULTS
------------------------------------------------------------
SMITH – 800
More than one employee with a salary of 3000
No employee with a salary of 6000
  ‫א‬‫א‬‫א‬‫א‬p8q3.sql‫א‬ J ٢
                                                                   Kdept
                              K‫א‬
Please enter the department number: 50
Please enter the department location: HOUSTON

PL/SQL procedure successfully completed.

G_MESSAGE
--------------------------------------------------------------------------------
Department 50 is an invalid department
  ‫א‬‫א‬‫א‬E PL/SQL BlockF J ٣
                                                                                        
                                                                       K١٠٠‫א‬ ‫א‬‫א‬‫א‬
 K‫א‬‫א‬‫א‬‫א‬‫א‬‫א‬ J
                        K‫א‬‫א‬‫א‬‫א‬‫א‬ J
                          ‫א‬‫א‬‫א‬‫א‬‫א‬ J
                                               K(Some other error occurred )
                                                - ١٠٦ -
   ‫א‬‫א‬                      ‫א‬‫א‬                       ‫א‬
 ‫א‬‫א‬                           ١٦٢                               



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

G_MESSAGE
--------------------------------------------------------------------------------
There is/are 1 employee(s) with a salary between 700 and 900


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

 G_MESSAGE
--------------------------------------------------------------------------------
There is/are 3 employee(s) with a salary between 2900 and 3100

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

G_MESSAGE
--------------------------------------------------------------------------------
There is no employee salary between 5900 and 6100




                                             - ١٠٧ -
                                ‫א‬‫א‬              ‫א‬
     
                                     ١٦٢                      



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> SELECT * FROM emp;
EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
------ ---------- --------- ---------- -------- ---------- ---------- ----------
  7839 KING       PRESIDENT            17/11/81       5000                    10
  7698 BLAKE      MANAGER         7839 01/05/81       2850                    30
  7782 CLARK      MANAGER         7839 09/06/81       2450                    10
  7566 JONES      MANAGER         7839 02/04/81       2975                    20
  7654 MARTIN     SALESMAN        7698 28/09/81       1250       1400         30
  7499 ALLEN      SALESMAN        7698 20/02/81       1600        300         30
  7844 TURNER     SALESMAN        7698 08/09/81       1500           0        30
  7900 JAMES      CLERK           7698 03/12/81        950                    30
  7521 WARD       SALESMAN        7698 22/02/81       1250        500         30
  7902 FORD       ANALYST         7566 03/12/81       3000                    20
  7369 SMITH      CLERK           7902 17/12/80        800                    20
  7788 SCOTT      ANALYST         7566 09/12/82       3000                    20
  7876 ADAMS      CLERK           7788 12/01/83       1100                    20
  7934 MILLER     CLERK           7782 23/01/82       1300                    10

SQL>   DESCRIBE dept


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

SQL> SELECT * FROM     dept;
    DEPTNO      DNAME               LOC
----------         --------------               -------------
        10            ACCOUNTING      NEW YORK
        20            RESEARCH           DALLAS
        30            SALES                    CHICAGO
        40          OPERATIONS        BOSTON



                                     - ١٠٨ -
                                       ‫א‬‫א‬        ‫א‬
     
                                            ١٦٢                


SQL>   DESCRIBE SALGRADE
Name                                                  Null?    Type
 ----------------------------------------------------- -------- -------------
-------
 GRADE                                                          NUMBER
 LOSAL                                                           NUMBER
 HISAL                                                            NUMBER


SQL> SELECT *       FROM SALGRADE;
     GRADE          LOSAL      HISAL
---------- -            ---------            ----------
         1                       700             1200
         2                      1201            1400
         3                      1401            2000
         4                      2001            3000
         5                      3001            9999


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>   SELECT *         FROM   ord;

   ORDID     ORDERDAT   C     CUSTID SHIPDATE      TOTAL
--------     --------   - ---------- -------- ----------
     610     07/01/87   A        101 08/01/87      101.4
     611     11/01/87   B        102 11/01/87         45
     612     15/01/87   C        104 20/01/87       5860
     601     01/05/86   A        106 30/05/86        2.4
     602     05/06/86   B        102 20/06/86         56
     604     15/06/86   A        106 30/06/86        698
     605     14/07/86   A        106 30/07/86       8324
     606     14/07/86   A        100 30/07/86        3.4
     609     01/08/86   B        100 15/08/86       97.5
     607     18/07/86   C        104 18/07/86        5.6
     608     25/07/86   C        104 25/07/86       35.2
     603     05/06/86            102 05/06/86        224
     620     12/03/87            100 12/03/87       4450
     613     01/02/87            108 01/02/87       6400
                                            - ١٠٩ -
                                             ‫א‬‫א‬                    ‫א‬
        
                                                  ١٦٢                            


        614     01/02/87             102   05/02/87              23940
        616     03/02/87             103   10/02/87                764
        619     22/02/87             104   04/02/87               1260
        617     05/02/87             105   03/03/87              46370
        615     01/02/87             107   06/02/87                710
        618     15/02/87 A           102   06/03/87             3510.5
        621     15/03/87 A           100   01/01/87                730

SQL> DESCRIBE PRODUCT

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

SQL>       SELECT *           FROM     PRODUCT ;

PRODID                          DESCRIP
----------                         ------------------------------
    100860                      ACE TENNIS RACKET I
    100861                      ACE TENNIS RACKET II
    100870                      ACE TENNIS BALLS-3 PACK
    100871                      ACE TENNIS BALLS-6 PACK
    100890                      ACE TENNIS NET
    101860                      SP TENNIS RACKET
    101863                      SP JUNIOR RACKET
    102130                      RH: "GUIDE TO TENNIS"
    200376                      SB ENERGY BAR-6 PACK
    200380                      SB VITA SNACK-6 PACK

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>     SELECT *      FROM    ITEM;


  ORDID     ITEMID     PRODID ACTUALPRICE        QTY    ITEMTOT
------- ---------- ---------- ----------- ---------- ----------
    610          3     100890          58          1         58
    611          1     100861          45          1         45
    612          1     100860          30        100       3000
    601          1     200376         2.4          1        2.4
    602          1     100870         2.8         20         56
                                                  - ١١٠ -
                        ‫א‬‫א‬                    ‫א‬
 
                             ١٦٢                            


604       1   100890             58                3      174
604       2   100861             42                2       84
604       3   100860             44               10      440
603       2   100860             56                4      224
610       1   100860             35                1       35
610       2   100870            2.8                3      8.4
613       4   200376            2.2              200      440
614       1   100860             35              444    15540
614       2   100870            2.8             1000     2800
612       2   100861           40.5               20      810
612       3   101863             10              150     1500
620       1   100860             35               10      350
620       2   200376            2.4             1000     2400
620       3   102130            3.4              500     1700
613       1   100871            5.6              100      560
613       2   101860             24              200     4800
613       3   200380              4              150      600
619       3   102130            3.4              100      340
617       1   100860             35               50     1750
617       2   100861             45              100     4500
614       3   100871            5.6             1000     5600
616       1   100861             45               10      450
616       2   100870            2.8               50      140
616       3   100890             58                2      116
616       4   102130            3.4               10       34
616       5   200376            2.4               10       24
619       1   200380              4              100      400
619       2   200376            2.4              100      240
615       1   100861             45                4      180
607       1   100871            5.6                1      5.6
615       2   100870            2.8              100      280
617       3   100870            2.8              500     1400
617       4   100871            5.6              500     2800
617       5   100890             58              500    29000
617       6   101860             24              100     2400
617       7   101863           12.5              200     2500
617       8   102130            3.4              100      340
617       9   200376            2.4              200      480
617      10   200380              4              300     1200
609       2   100870            2.5                5     12.5
609       3   100890             50                1       50
618       1   100860             35               23      805
618       2   100861          45.11               50   2255.5
618       3   100870             45               10      450
621       1   100861             45               10      450
621       2   100870            2.8              100      280
615       3   100871              5               50      250
608       1   101860             24                1       24
608       2   100871            5.6                2     11.2
609       1   100861             35                1       35
606       1   102130            3.4                1      3.4
605       1   100861             45              100     4500
605       2   100870            2.8              500     1400
605       3   100890             58                5      290
605       4   101860             24               50     1200
                             - ١١١ -
                                ‫א‬‫א‬                ‫א‬
     
                                     ١٦٢                        


    605          5    101863               9            100   900
    605          6    102130             3.4             10    34
    612          4    100871             5.5            100   550
    619          4    100871             5.6             50   280
SQL> DESCRIBE PRICE

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


SQL> SELECT   * FROM PRICE;

    PRODID   STDPRICE   MINPRICE STARTDAT ENDDATE
---------- ---------- ---------- -------- --------
    100871        4.8        3.2 01/01/85 01/12/85
    100890         58       46.4 01/01/85
    100860         35         28 01/06/86
    100860         30         24 01/01/85 31/12/85
    100861         45         36 01/06/86
    100861         39       31.2 01/01/85 31/12/85
    100870        2.8        2.4 01/01/86
    100870        2.4        1.9 01/01/85 01/12/85
    100871        5.6        4.8 01/01/86
    101860         24         18 15/02/85
    101863       12.5        9.4 15/02/85
    102130        3.4        2.8 18/08/85
    200376        2.4       1.75 15/11/86
    200380          4        3.2 15/11/86


SQL> DESCRIBE CUSTOMER

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

                                     - ١١٢ -
                                      ‫א‬‫א‬                         ‫א‬
     
                                           ١٦٢                                 




  CUSTID     NAME                                          ADDRESS
--------     --------------------------------------------- ----------------------
-
     100     JOCKSPORTS                                              345 VIEWRIDGE
     101     TKB SPORT SHOP                                          490 BOLI RD.
     102     VOLLYRITE                                               9722 HAMILTON
     103     JUST TENNIS                                             HILLVIEW MALL
     104     EVERY MOUNTAIN                                          574 SUYYYYY RD.
     105     K + T SPORTS                                            3476 EL PASEO
     106     SHAPE UP                                                908 SEQUOIA
     107     WOMENS SPORTS                                           VALCO VILLAGE
     108     NORTH WOODS HEALTH AND FITNESS SUPPLY CENTER            98 LONE PINE WAY

CITY                      ST ZIP                     AREA PHONE            REPID
CREDITLIMIT
------------------        -- --------- ---------- --------- ---------- ----------
-
BELMONT                   CA 96711                    415 598-6609          7844
5000
REDWOOD CITY              CA 94061                    415 368-1223          7521
10000
BURLINGAME                CA 95133                    415 644-3341          7654
7000
BURLINGAME                CA 97544                    415 677-9312          7521
3000
CUPERTINO                 CA 93301                    408 996-2323          7499
10000
SANTA CLARA               CA 91003                    408 376-9966          7844
5000
PALO ALTO                 CA 94301                    415 364-9777          7521
6000
SUNNYVALE                 CA 93301                    408 967-4398          7499
10000
HIBBING                   MN 55649                    612 566-9123          7844
8000

  CUSTID   COMMENTS
--------   ----------------------------------------------------------------------------
     100   Very friendly people to work with -- sales rep likes to be called Mike.
     101   Rep called 5/8 about change in order - contact shipping.
     102   Company doing heavy promotion beginning 10/89. Prepare for large orders during
     103   Contact rep about new line of tennis rackets.
     104   Customer with high market share (23%) due to aggressive advertising.
     105   Tends to order large amounts of merchandise at once. Accounting is considering
     106   Support intensive. Orders small amounts (< 800) of merchandise at a time.
     107   First sporting goods store geared exclusively towards women. Unusual
           Promotional style and very willing to take chances towards new products!
     108




                                           - ١١٣ -
                      ‫א‬‫א‬                             ‫א‬
      ‫א‬
                           ١٦٢                                     



                      ‫א‬
 ‫א‬                         ‫א‬
                                                                  W‫א‬‫א‬
 ١                                        K‫א‬‫א‬ 
                                                                  W‫א‬‫א‬
 ٨                                              K‫א‬‫א‬‫א‬ 
                                                                W‫א‬‫א‬
 ١٥                                           K‫א‬‫א‬ 
                                                                    W‫א‬‫א‬‫א‬
 ٢٦                                                       K‫א‬‫ א‬
                                                        W‫א‬‫א‬
 ٣٧              K‫א‬‫א‬ 
                                                                 W‫א‬‫א‬
 ٤٦                                                      K‫א‬‫א‬ 
                                                                    W‫א‬‫א‬
 ٥٩                                                 K‫א‬‫א‬ 

                                                                W‫א‬‫א‬
 ٦٨                                           KOracle‫ א‬
                                                                 W‫א‬‫א‬
 ٧٧                                                        K‫א‬ 
                                                                  W‫א‬‫א‬
 ٩٤                                                   K‫א‬‫א‬ 
 ١٠٨                                                                        ‫א‬
                                

                                




                                

                                

                                




                                

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

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