PL/SQL程式之預存程序和儲存函數

1. 預存程序和儲存函數  
2. 指儲存在資料庫中供所有使用者程式調用的子程式叫預存程序、儲存函數。  
3. 建立預存程序：  
4. 用CREATE PROCEDURE命令建立預存程序和儲存函數。  
5. 文法：  
6. create [or replace] PROCEDURE 過程名(參數列表)   
7. AS   
8. PLSQL子程式體；  
9. 註：預存程序一般用於高度安全的系統中  
10.   
11. 例一：為所有員工漲10%的工資  
12. 建立預存程序  
13. create or replace procedure updateSal  
14. as  
15. --plsql程式塊   
16. --預存程序不能有declare   
17. create or replace procedure updateSal  
18. as  
19. cursor c1 is select sal,empno from emp;  
20. emp_sal emp.sal%type;  
21. emp_no emp.empno%type;  
22. begin  
23. open c1;  
24. loop  
25.   fetch c1 into emp_sal,emp_no;  
26.   exit when c1%notfound;  
27.   update emp set sal=emp_sal*1.1 where empno=emp_no;  
28.   end loop;  
29.   close c1;  
30.   end;  
31. 調用預存程序  
32. 方式一：  
33. SQL> begin  
34.   2  updateSal;  
35.   3  end;  
36.   4  /  
37. 方式二：  
38. SQL> exec updateSal;  
39. 調用完成後手工提交一下  
40. 【  
41. Commit語句：結束當前事務, 使當前事務所執行的全部修改永久化。  
42. 】  
43.   
44. 儲存函數  
45. 函數（Function）為一命名的儲存程式，可帶參數，並返回一計算值。函數和過程的結構類似，但必須有一個RETURN子句，用於返回函數值。函數說明要指定函數名、結果值的類型，以及參數類型等。  
46. 建立儲存函數的文法：  
47. CREATE [OR REPLACE] FUNCTION 函數名(參數列表)   
48.             RETURN  函數實值型別  
49. AS  
50. PLSQL子程式體；  
51.   
52. 例二：擷取指定員工的年度營收  
53. create or replace function sumSal(emp_no number)--function（參數的值  必須有類型）   
54. --傳回值類型   
55. return number--必須有傳回值   
56. as  
57. --聲明變數   
58. emp_sal emp.sal%type;  
59. emp_comm emp.comm%type;  
60. total emp.sal%type;  
61. begin  
62.   select sal,comm into emp_sal,emp_comm from emp where empno=emp_no;  
63.   total:=emp_sal*12+nvl(emp_comm,0);  
64.   return total;--必須返回  傳回值類型一定相同   
65. end;  
66. 調用方法  
67. 方法一：  
68. SQL> begin  
69.   2  dbms_output.put_line(sumSal(7369));  
70.   3  end;  
71.   4  /  
72. 方法二：  
73. declare  
74. v_sal number;  
75. begin  
76. v_sal:=sumSal(7369);  
77. dbms_output.put_line(v_sal);  
78. end;  
79. 結果  
80. 12777.6