PL/SQL编程整理5 -- 编写存储过程

在 ORACLE SERVER上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数.

创建过程语法:

CREATE [OR REPLACE] PROCEDURE Procedure_name [ (argment [ { IN | IN OUT }] Type,       argment [ { IN | OUT | IN OUT } ] Type ] { IS | AS } <类型.变量的说明> BEGIN <执行部分> EXCEPTION <可选的异常错误处理程序> END;

 

1、没有输入和输出参数的存储过程

Create or replace procedure 存储过程名 Is Begin -- 语句 End; /

2、有输入参数的存储过程

Create or replace procedure 存储过程名 （参数1 in 类型，参数2 in 类型 …..） Is Begin -- 语句 End; 例子： Create or replace procedure Sp_test(spName in varchar2,spSal in number) Is Begin     Update emp set sal=spSal where ename=spName End; / JAVA中调用： //得到连接 Connection con=null;  //得到CallableStatement对象 CallableStatement cs = Con.prepareCall(“{call Sp_test(?,?) }”); //给?赋值 cs.setString(1,”SCOTT”); cs.setInt(2,1000); //执行 cs.execute();

3、有输出参数（非列表）的存储过程

Create or replace procedure 存储过程名 （参数1 out 类型，参数2 out 类型 …..） Is Begin -- 语句 End;   例子： Create or replace procedure Sp_test(spName in varchar2,spSal out number) Is Begin     Select sal into spSal from where ename=spName; End; / JAVA中调用： //得到连接 Connection con=null;  //得到CallableStatement对象 CallableStatement cs = Con.prepareCall(“{call Sp_test(?,?) }”); //给?赋值 cs.setString(1,”SCOTT”); cs.registerOutParameter(2,oracle.jdbc.OracleTypes.NUMBER); //执行 cs.execute(); //取出返回值,注意?顺序 int sal = cs.getInt(2);

 

4、有输出参数（列表）的存储过程

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用package，所以要分两部分：

a)         建一个包，定义一个游标类型，如下：

Create or replace package testpackage as TYPE test_cursor is ref curser; End testpackage;

 

b)        建立存储过程

例子： Create or replace procedure Sp_test(spNo in nuber,p_cursor out testpackage. test_cursor) Is Begin     Open p_cursor for Select * from where deptno= spNo; End; / JAVA中调用： //得到连接 Connection con=null;  //得到CallableStatement对象 CallableStatement cs = Con.prepareCall(“{call Sp_test(?,?) }”); //给?赋值 cs.setInt(1,10); cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR); //执行 cs.execute(); //得到结果集 ResultSet rs =(ResultSet)cs.getObject(2); while(rs.next){         System.out.println(rs.getInt(1)+”---------”+rs.getString(2)) }

 

 

注意：

查看错误：show error

PL/SQL中执行过程：

格式：

       EXEC[UTE]  Procedure_name( parameter1, parameter2…);

例子：

CREATE OR REPLACE PROCEDURE proc_demo(     Dept_no NUMBER DEFAULT 10,     Sal_sum OUT NUMBER,     Emp_count OUT NUMBER) IS BEGIN     SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count FROM emp WHERE deptno=dept_no; EXCEPTION    WHEN NO_DATA_FOUND THEN       DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');    WHEN OTHERS THEN       DBMS_OUTPUT.PUT_LINE('发生其它错误!'); END proc_demo;   调用方法:  DECLARE V_num NUMBER; V_sum NUMBER(8, 2); BEGIN     Proc_demo(30, v_sum, v_num); DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||’，人数：’||v_num);     Proc_demo(sal_sum => v_sum, emp_count => v_num); DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||’，人数：’||v_num); END;

 

在PL/SQL 程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数据库中，但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用CREATE OR REPLACE 关键字。例如：

DECLARE   V_num NUMBER;   V_sum NUMBER(8, 2);   PROCEDURE proc_demo(         Dept_no NUMBER DEFAULT 10,         Sal_sum OUT NUMBER,         Emp_count OUT NUMBER)   IS   BEGIN         SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count   FROM emp WHERE deptno=dept_no;   EXCEPTION      WHEN NO_DATA_FOUND THEN         DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');      WHEN OTHERS THEN         DBMS_OUTPUT.PUT_LINE('发生其它错误!');   END proc_demo; BEGIN     Proc_demo(30, v_sum, v_num);   DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||'，人数：'||v_num);     Proc_demo(sal_sum => v_sum, emp_count => v_num);   DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||'，人数：'||v_num); END;