存储过程 1 什么是存储过程？ 用于在数据库中完成特定的操作或者任务。是一个PLSQL程序块，可以永久的保存在数据库中以供其他程序调用。 2 存储过程的参数模式 存储过程的参数特性： IN类型的参数 OUT类型的参数 IN-OUT类型的参数 值被 传递给子程序 返回给调用环境 传递给子程序 返回给调用环境 参数形式 常量 未初始化的变量 初始化的变量 使用时 默认类型 必须明确指定 必须明确指定
在oracle10中写好了存储过程，代码如下：
1 CREATE OR REPLACE Procedure Proc_Insert
2 (
3 sName in varchar2,
4 sAge in int,
5 sExeTime in varchar2
6 )
7 is
8
9 begin
10 Insert into T_TEST(C_ID, C_NAME, C_AGE, C_INTIME, C_EXETIME)
11 values(T_TEST_CID.nextval,sName,sAge, sysdate, to_date(sExeTime, 'yyyy-mm-dd'));
12 end Proc_Insert;
为了在PL/SQL中调用这个存储过程，采用了如下的代码：
1 begin
2 Proc_Insert('hello6',25,'2005-12-24');
3 commit;
4 end;
另外增加了一个存储过程，但是sExeTime是date类型，那么调用的时候，就必须先把字符串转换成date类型，否则将会调用失败。 存储过程：
1 CREATE OR REPLACE Procedure Proc_Insert2
2 (
3 sName in varchar2,
4 sAge in int,
5 sExeTime in date
6 )
7 is
8
9 begin
10 Insert into T_TEST(C_ID, C_NAME, C_AGE, C_INTIME, C_EXETIME)
11 values(T_TEST_CID.nextval,sName,sAge, sysdate, sExeTime);
12 end Proc_Insert2;
调用的代码：
1 begin
2 Proc_Insert2('hellowhat', 26, to_date('2010-10-20', 'yyyy-mm-dd'));
3 commit;
4 end;
调用存储过程 存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS 、ORACLE开发工具或第三方开发工具中来调用运行。ORACLE 使用EXECUTE 语句来实现对存储过程的调用： EXEC[UTE] Procedure_name( parameter1, parameter2…); 例：查询指定员工记录
1 CREATE OR REPLACE PROCEDURE QueryEmp
2 (v_empno IN emp.empno%TYPE,
3 v_ename OUT emp.ename%TYPE,
4 v_sal OUT emp.sal%TYPE)
5 AS
6 BEGIN
7 SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno=v_empno;
8 DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已经查到!');
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE('发生其它错误!');
14 END QueryEmp;
调用方法:
DECLARE
v1 emp.ename%TYPE;
v2 emp.sal%TYPE;
BEGIN
QueryEmp(7788, v1, v2);
DBMS_OUTPUT.PUT_LINE('姓名:'||v1);
DBMS_OUTPUT.PUT_LINE('工资:'||v2);
QueryEmp(7902, v1, v2);
DBMS_OUTPUT.PUT_LINE('姓名:'||v1);
DBMS_OUTPUT.PUT_LINE('工资:'||v2);
QueryEmp(8899, v1, v2);
DBMS_OUTPUT.PUT_LINE('姓名:'||v1);
DBMS_OUTPUT.PUT_LINE('工资:'||v2);
END;
例．计算指定部门的工资总和，并统计其中的职工数量。
1 CREATE OR REPLACE PROCEDURE proc_demo
2 (Dept_no NUMBER DEFAULT 10,
3 Sal_sum OUT NUMBER,
4 Emp_count OUT NUMBER)
5 IS
6 BEGIN
7 SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count
8 FROM emp WHERE deptno=dept_no;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE('发生其它错误!');
14 END proc_demo;
调用方法:
1 DECLARE
2 V_num NUMBER;
3 V_sum NUMBER(8, 2);
4 BEGIN
5 Proc_demo(30, v_sum, v_num);
6 DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||’，人数：’||v_num);
7 Proc_demo(sal_sum => v_sum, emp_count => v_num);
8 DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||’，人数：’||v_num);
9 END;
在PL/SQL 程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数据库中，但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用CREATE OR REPLACE 关键字。 例：建立本地过程，用于计算指定部门的工资总和，并统计其中的职工数量
1 DECLARE
2 V_num NUMBER;
3 V_sum NUMBER(8, 2);
4 PROCEDURE proc_demo
5 (Dept_no NUMBER DEFAULT 10,
6 Sal_sum OUT NUMBER,
7 Emp_count OUT NUMBER)
8 IS
9 BEGIN
10 SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count
11 FROM emp WHERE deptno=dept_no;
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
15 WHEN OTHERS THEN
16 DBMS_OUTPUT.PUT_LINE('发生其它错误!');
17 END proc_demo;
18 BEGIN
19 Proc_demo(30, v_sum, v_num);
20 DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||’，人数：’||v_num);
21 Proc_demo(sal_sum => v_sum, emp_count => v_num);
22 DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||’，人数：’||v_num);
23 END;
无参数存储过程的使用
CREATE OR REPLACE PROCEDURE 过程名 [(parameter,...)] IS 定义变量 Begin Plsql程序 End;
例：创建一个存储过程，用于向数据库中插入一条记录。 第一步：创建
1 CREATE OR REPLACE PROCEDURE pro_1
2 IS
3 Begin
4 insert into person values (11,'aa','aav');
5 End;
第二步：在sql*plus中执行该过程
exec pro_1;
第三步：通过JDBC使用存储过程 private Connection conn = null; private ResultSet rs = null; private CallableStatement state = null; //调用一个无参数的存储过程 public void testPro() { conn = Tools.getConnection(); try { state = conn.prepareCall("{call pro_1}"); state.execute(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } }
4 带有IN类型参数的存储过程的使用。 例：创建一个存储过程，用于向数据库中插入一条记录。 第一步：创建
1 CREATE OR REPLACE PROCEDURE pro_2(id number,name varchar2,email varchar2)
2 IS
3 Begin
4 insert into person values (id,name,email);
5 End;
第二步：在sql*plus中执行该过程
1 exec pro_2(12,'aaa','aaa');
第三步：通过JDBC使用存储过程。 //使用一个带有 IN 类型参数的存储过程
1 public void testPro_in(int id,String name,String email)
2 {
3 conn = Tools.getConnection();
4 try {
5 state = conn.prepareCall("{call pro_2(?,?,?)}");
6 state.setLong(1, id);
7 state.setString(2, name);
8 state.setString(3, email);
9 state.execute();
10 } catch (Exception e) {
11 // TODO Auto-generated catch block
12 e.printStackTrace();
13 }
14 }
5 带有out类型参数的存储过程的使用。 例：创建一个存储过程，用于返回数据库中的Person表的总行数。 第一步：创建
1 CREATE OR REPLACE PROCEDURE pro_3(num out number)
2 IS
3 mynum number;
4 Begin
5 select count(*) into mynum from person;
6 num := mynum;
7 End;
或者
1 CREATE OR REPLACE PROCEDURE pro_3(num out number)
2 IS
3 Begin
4 select count(*) into num from person;
5 End;
第二步：在sql*plus中执行该过程 declare a number; begin pro_3(a); dbms_output.put_line(a); end; 第三步：通过JDBC使用存储过程。 public void testPro_out() { conn = Tools.getConnection(); try { state = conn.prepareCall("{call pro_3(?)}"); state.registerOutParameter(1, Types.NUMERIC); state.execute(); int num = state.getInt(1); System.out.println(num); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } 6 带有in-out类型参数的存储过程的使用。 创建： CREATE OR REPLACE PROCEDURE pro_4(num in out number) IS a number := 100; Begin num := a*num; End; 在sql*plus中执行该过程 declare a number := 12; begin pro_4(a); dbms_output.put_line(a); end;