簡單的ORACLE預存程序

簡單的ORACLE預存程序

--工資加500，OLD。SAL>2500 不加，NEW。SAL>2500 則改為2500；

SELECT * FROM EMP                                                                --加工資之前

CREATE OR REPLACE PROCEDURE SP_ADD (add IN number) is       --add參數接收傳入的工資增幅

     --DECLARE
     --CURSOR emp_cur IS SELECT * FROM emp;
    
BEGIN
    
     FOR  sal_sum IN (SELECT * FROM emp) LOOP    --使用迴圈遊標隱式開啟遊標emp_cur或    

                                                                                   --（SELECT * FROM emp)(在FOR迴圈中不需要聲明此遊標)
                                                                                   --自動從活動集擷取行，然後在處理完所有行時關閉遊標    
          IF sal_sum.sal > 2500 THEN                                             --逐行進行比較賦值
                UPDATE emp SET sal = sal_sum.sal WHERE empno = sal_sum.empno;
               
          ELSIF (sal_sum.sal + add) > 2500 THEN
                UPDATE emp SET sal = 2500 WHERE empno = sal_sum.empno;
               
          ELSE             
                UPDATE emp SET sal = sal_sum.sal + add  WHERE empno = sal_sum.empno;
          END IF;
         
     END LOOP;
    
END;

BEGIN
    
SP_ADD(500);                                                                
--輸入增資的幅度
                                                                                                                    

END;

SELECT * FROM EMP                                                                 --加工資之後