PL/SQL程序之存储过程和存储函数

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