oracle存储过程的创建与使用_oracle存储过程的创建和使用-优快云博客

在Oracle中使用存储过程可以声明单个存储过程，也可以使用包的方式一个存储过程包（PACKAGE）包含许多存储过程。

1、单个存储过程的创建

CREATE OR REPLACE PROCEDURE PROC_TEST IS
  count number;					---声明变量
  BEGIN

     select * from dual;		---将SQL语句放在BEGIN 和END之间

  EXCEPTIOIN					---异常处理，可以不写，看具体情况决定
    WHEN NO_DATA_FOUND THEN 	---写异常处理的方法
    WHEN OTHERS THEN			---写异常处理的方法
        
  END PROC_TEST; 				---这里的PROC_TEST是方便看什么方法或语句体的结束（方便阅读）。也可以直接写成： "END;"

2、使用存储过程包创建

2.1、先声明存储过程（先创建包）

存储过程的package相当于Java中的接口，而package body就相当于Java中实现该接口的类。

CREATE OR REPLACE PACKAGE PKG_Test IS
--------------------------------------------------------------------------------------
								---不带参数的存储过程
        PROCEDURE update_user;
        
--------------------------------------------------------------------------------------
								---带参数的存储过程
        PROCEDURE get_user(p_food IN VARCHAR2, p_amount IN NUMBER);
        
END PKG_Test ;

2.2、编写存储过程内容

CREATE OR REPLACE PACKAGE BODY PKG_Test IS	---需要写存储包名字 PKG_Test 

--------------------------------------------------------------------------------------
								---不带参数的存储过程
--------------------------------------------------------------------------------------
	PROCEDURE update_user IS
	   name VARCHAR2(12);	--参数声明
	   BEGIN
	
	      ...
	
	   EXCEPTION
	          WHEN NO_DATA_FOUND THEN
	               DBMS_OUTPUT.PUT_LINE('无数据记录');
	          WHEN OTHERS THEN
	               DBMS_OUTPUT.PUT_LINE('异常代码:' + sqlcode);  ---sqlcode代表异常代码
	               DBMS_OUTPUT.PUT_LINE('异常信息:' + sqlerrm);  ---sqlerrm代表异常信息
	　　 END update_user;

--------------------------------------------------------------------------------------
								--带参数的存储过程
--------------------------------------------------------------------------------------
	PROCEDURE get_user(		---in前缀作为输入参数，out前缀作为输出参数
				          in_datestr in varchar2,  		---日期，格式：yyyyMMdd
		                  out_is_success in out number, ---是否查询成功(1-查询成功、0-查询失败)
		                  out_detail out sys_refcursor 	---结果
						 ) IS
						 
	name VARCHAR2(12);	--参数声明
	
	BEGIN
      	open out_detail for		---out_detail 查询结果集会存在这并返回，如果返回的不是一个结果集
      							---可以在存储过程传参处写上你要返回的结果及类型，查询到数据给其赋值即可
      		select t.id,t.name,t.age from t_user t where t.creation_date > to_date(in_datestr,'yyyy-mm-dd hh24:mi:ss');
		out_is_success := 1;
	
	    EXCEPTION
		    WHEN NO_DATA_FOUND THEN
	        	DBMS_OUTPUT.put_line('CATCH EXCEPTIOIN');
	      	WHEN OTHERS THEN
	      		out_is_success := 0;
	            DBMS_OUTPUT.PUT_LINE('异常代码:' + sqlcode);  ---sqlcode代表异常代码
	            DBMS_OUTPUT.PUT_LINE('异常信息:' + sqlerrm);  ---sqlerrm代表异常信息
	END get_user;

END PKG_Test;

3、PLSQL调用存储过程

declare out_is_success number;
		out_detail sys_refcursorp;
begin
   --调用存储过程
   get_user('2021-01-01 00:00:00',out_is_success, out_detail);
   dbms_output.put_line('输出结果：'|| out_detail);
end;

4、java代码调用存储过程

try {
	// 创建存储过程的对象
	CallableStatement c = conn.prepareCall("{call test_in_out_exception(?,?,?)}");
	
	// 给存储过程参数赋值，
	c.setString("in_datestr", "2021-01-01 00:00:00");				//输入参数，根据类型来赋值
	cs.registerOutParameter("out_is_success", OracleTypes.NUMBER);	//返回结果，赋值用OracleTypes类提供的参数
	cs.registerOutParameter("out_detail", OracleTypes.CURSOR);		//输出结果集，赋值用OracleTypes类提供的参数
	// 执行存储过程
	c.execute();
	
	ResultSet rs = (ResultSet) cs.getObject("out_detail");
	while (rs.next()) {
		int age = rs.getInt("age");
		String id = rs.getString("id");
		String name = rs.getString("name");
		System.out.println(id + name + age);
	}
} catch (SQLException e) {
	System.out.println("SQLState : " + e.getSQLState());
	System.out.println("ErrorCode : " + e.getErrorCode());
	System.out.println("Message : " + e.getMessage());
	System.out.println("LocalizedMessage : " + e.getLocalizedMessage());
	e.printStackTrace();
} finally {
     conn.close();
}