01.在PL/SQL中调用存储过程－－oracle

1. 在oracle10中写好了存储过程，代码如下：  
2.   
3. CREATE OR REPLACE Procedure Proc_Insert  
4. (  
5.        sName     in     varchar2,  
6.        sAge      in     int,  
7.        sExeTime  in     varchar2  
8. )  
9. is  
10.    
11. begin  
12.        Insert into T_TEST(C_ID, C_NAME, C_AGE, C_INTIME, C_EXETIME)  
13.        values(T_TEST_CID.nextval,sName,sAge, sysdate, to_date(sExeTime, 'yyyy-mm-dd'));  
14. end Proc_Insert;  
15.    
16.   
17. 为了在PL/SQL中调用这个存储过程，采用了如下的代码：  
18.   
19. begin  
20. Proc_Insert('hello6',25,'2005-12-24');  
21. commit;  
22. end;  
23.   
24.    
25.   
26. 另外增加了一个存储过程，但是sExeTime是date类型，那么调用的时候，就必须先把字符串转换成date类型，否则将会调用失败。  
27.   
28. 存储过程：  
29.   
30. CREATE OR REPLACE Procedure Proc_Insert2  
31. (  
32.        sName     in     varchar2,  
33.        sAge      in     int,  
34.        sExeTime  in     date  
35. )  
36. is  
37.    
38. begin  
39.        Insert into T_TEST(C_ID, C_NAME, C_AGE, C_INTIME, C_EXETIME)  
40.        values(T_TEST_CID.nextval,sName,sAge, sysdate, sExeTime);  
41. end Proc_Insert2;  
42.     
43. 调用的代码：  
44.    
45.   
46. begin  
47. Proc_Insert2('hellowhat', 26, to_date('2010-10-20', 'yyyy-mm-dd'));  
48. commit;  
49. end;  

[sql]  view plain  copy

 print ?

1. 调用存储过程  
2.     存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS 、ORACLE开发工具或第三方开发工具中来调用运行。ORACLE 使用EXECUTE 语句来实现对存储过程的调用：  
3.     EXEC[UTE]  Procedure_name( parameter1, parameter2…);  
4.    
5. 例：查询指定员工记录；  
6.    
7. CREATE OR REPLACE PROCEDURE QueryEmp  
8. (v_empno IN emp.empno%TYPE,  
9.      v_ename OUT emp.ename%TYPE,  
10.      v_sal OUT emp.sal%TYPE)  
11. AS  
12. BEGIN  
13.      SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno=v_empno;  
14.      DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已经查到!');  
15. EXCEPTION  
16.      WHEN NO_DATA_FOUND THEN  
17.       DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');  
18.       WHEN OTHERS THEN  
19.       DBMS_OUTPUT.PUT_LINE('发生其它错误!');  
20. END QueryEmp;  
21.    
22. 调用方法:  
23.  DECLARE  
24.     v1 emp.ename%TYPE;  
25.     v2 emp.sal%TYPE;  
26.  BEGIN  
27.    QueryEmp(7788, v1, v2);  
28.    DBMS_OUTPUT.PUT_LINE('姓名:'||v1);  
29.    DBMS_OUTPUT.PUT_LINE('工资:'||v2);  
30.    QueryEmp(7902, v1, v2);  
31.    DBMS_OUTPUT.PUT_LINE('姓名:'||v1);  
32.    DBMS_OUTPUT.PUT_LINE('工资:'||v2);  
33.    QueryEmp(8899, v1, v2);  
34.    DBMS_OUTPUT.PUT_LINE('姓名:'||v1);  
35.    DBMS_OUTPUT.PUT_LINE('工资:'||v2);  
36. END;  
37.    
38. 例．计算指定部门的工资总和，并统计其中的职工数量。  
39.    
40. CREATE OR REPLACE PROCEDURE proc_demo  
41. (Dept_no NUMBER DEFAULT 10,  
42.        Sal_sum OUT NUMBER,  
43.        Emp_count OUT NUMBER)  
44. IS  
45. BEGIN  
46.        SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count  
47. FROM emp WHERE deptno=dept_no;  
48. EXCEPTION  
49.    WHEN NO_DATA_FOUND THEN  
50.       DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');  
51.    WHEN OTHERS THEN  
52.       DBMS_OUTPUT.PUT_LINE('发生其它错误!');  
53. END proc_demo;  
54.    
55. 调用方法:  
56. DECLARE  
57. V_num NUMBER;  
58. V_sum NUMBER(8, 2);  
59. BEGIN  
60.        Proc_demo(30, v_sum, v_num);  
61. DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||’，人数：’||v_num);  
62.        Proc_demo(sal_sum => v_sum, emp_count => v_num);  
63. DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||’，人数：’||v_num);  
64. END;  
65.    
66.        在PL/SQL 程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数据库中，但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用CREATE OR REPLACE 关键字。  
67.    
68. 例：建立本地过程，用于计算指定部门的工资总和，并统计其中的职工数量；  
69.    
70. DECLARE  
71. V_num NUMBER;  
72. V_sum NUMBER(8, 2);  
73. PROCEDURE proc_demo  
74.               (Dept_no NUMBER DEFAULT 10,  
75.               Sal_sum OUT NUMBER,  
76.               Emp_count OUT NUMBER)  
77. IS  
78. BEGIN  
79.               SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count  
80. FROM emp WHERE deptno=dept_no;  
81. EXCEPTION  
82.    WHEN NO_DATA_FOUND THEN  
83.       DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');  
84.    WHEN OTHERS THEN  
85.       DBMS_OUTPUT.PUT_LINE('发生其它错误!');  
86. END proc_demo;  
87. BEGIN  
88.        Proc_demo(30, v_sum, v_num);  
89. DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||’，人数：’||v_num);  
90.        Proc_demo(sal_sum => v_sum, emp_count => v_num);  
91. DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||’，人数：’||v_num);  
92. END;  

[sql]  view plain  copy

 print ?

1. 存储过程  
2. 1 什么是存储过程？  
3.  用于在数据库中完成特定的操作或者任务。是一个PLSQL程序块，可以永久的保存在数据库中以供其他程序调用。  
4. 2 存储过程的参数模式  
5.   
6. 存储过程的参数特性：  
7.  IN类型的参数 OUT类型的参数 IN-OUT类型的参数  
8. 值被 传递给子程序 返回给调用环境 传递给子程序  
9. 返回给调用环境  
10. 参数形式 常量 未初始化的变量 初始化的变量  
11. 使用时 默认类型 必须明确指定 必须明确指定  
12.   
13. 3 无参数存储过程的使用：  
14. CREATE  OR  REPLACE  PROCEDURE  过程名  [(parameter,...)]  
15. IS  
16. 定义变量  
17. Begin  
18. Plsql程序  
19. End;  
20. 例：创建一个存储过程，用于向数据库中插入一条记录。  
21. 第一步：创建  
22. CREATE  OR  REPLACE  PROCEDURE  pro_1  
23. IS  
24. Begin  
25.   insert into person values (11,'aa','aav');  
26. End;  
27. 第二步：在sql*plus中执行该过程  
28. exec pro_1;  
29. 第三步：通过JDBC使用存储过程。  
30.  private Connection conn = null;  
31.  private ResultSet rs = null;  
32.  private CallableStatement state = null;  
33.  //调用一个无参数的存储过程  
34.  public void testPro()  
35.  {  
36.   conn = Tools.getConnection();  
37.   try {  
38.    state = conn.prepareCall("{call pro_1}");  
39.    state.execute();  
40.   } catch (Exception e) {  
41.    // TODO Auto-generated catch block  
42.    e.printStackTrace();  
43.   }  
44.  }  
45.   
46. 4 带有IN类型参数的存储过程的使用。  
47. 例：创建一个存储过程，用于向数据库中插入一条记录。  
48. 第一步：创建  
49. CREATE  OR  REPLACE  PROCEDURE  pro_2(id number,name varchar2,email varchar2)  
50. IS  
51. Begin  
52.   insert into person values (id,name,email);  
53. End;  
54. 第二步：在sql*plus中执行该过程  
55. exec pro_2(12,'aaa','aaa');  
56. 第三步：通过JDBC使用存储过程。  
57.  //使用一个带有 IN 类型参数的存储过程  
58.  public void testPro_in(int id,String name,String email)  
59.  {  
60.   conn = Tools.getConnection();  
61.   try {  
62.    state = conn.prepareCall("{call pro_2(?,?,?)}");  
63.    state.setLong(1, id);  
64.    state.setString(2, name);  
65.    state.setString(3, email);  
66.    state.execute();  
67.   } catch (Exception e) {  
68.    // TODO Auto-generated catch block  
69.    e.printStackTrace();  
70.   }  
71.  }  
72. 5 带有out类型参数的存储过程的使用。  
73. 例：创建一个存储过程，用于返回数据库中的Person表的总行数。  
74. 第一步：创建  
75. CREATE  OR  REPLACE  PROCEDURE  pro_3(num out number)  
76. IS  
77. mynum number;  
78. Begin  
79.   select count(*) into mynum from person;  
80.   num := mynum;  
81. End;  
82. 或者  
83. CREATE  OR  REPLACE  PROCEDURE  pro_3(num out number)  
84. IS  
85. Begin  
86.   select count(*) into num from person;  
87. End;  
88. 第二步：在sql*plus中执行该过程  
89. declare  
90. a number;  
91. begin  
92.   pro_3(a);  
93.   dbms_output.put_line(a);  
94. end;  
95. 第三步：通过JDBC使用存储过程。  
96. public void testPro_out()  
97.  {  
98.   conn = Tools.getConnection();  
99.   try {  
100.    state = conn.prepareCall("{call pro_3(?)}");  
101.    state.registerOutParameter(1, Types.NUMERIC);  
102.    state.execute();  
103.    int num = state.getInt(1);  
104.    System.out.println(num);  
105.   } catch (Exception e) {  
106.    // TODO Auto-generated catch block  
107.    e.printStackTrace();  
108.   }  
109.  }  
110. 6 带有in-out类型参数的存储过程的使用。  
111. 创建：  
112. CREATE  OR  REPLACE  PROCEDURE  pro_4(num in out number)  
113. IS  
114. a number := 100;  
115. Begin  
116.   num := a*num;  
117. End;  
118. 在sql*plus中执行该过程  
119. declare  
120.   a number := 12;  
121. begin  
122.   pro_4(a);  
123.   dbms_output.put_line(a);  
124. end;  
125.   
126. 练习：  
127. 已知有一员工表，请编写一个存储过程，可以通过部门ID得到该部门员工的平