在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();
}
参考文章
Oracle 存储过程包(Package、Package Body)
oracle如何创建存储过程和调用
Oracle存储过程的调用(返回参数)