在PL/SQL中调用Oracle存储过程

king IIS 2023-02-27 620浏览 0

存储过程
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;