Oracle 存储过程、存储函数、程序包

最新推荐文章于 2026-07-27 09:27:28 发布

原创最新推荐文章于 2026-07-27 09:27:28 发布 · 574 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

Oracle系列

本文介绍了Oracle数据库中的存储过程和存储函数，包括创建、调用的方法及示例代码，并展示了如何通过JDBC访问这些数据库对象。

Oracle数据库对象

表
视图
索引
序列
同义词
存储过程
存储函数等

存储过程、存储函数

存储在数据库中供用户程序调用的子程序
相同点：完成特定功能的程序
区别：存储函数用return语句返回值

注：一般来讲：如果只有一个返回值，则用存储函数，否则（有多个或者没有返回值），使用存储过程

存储过程

创建存储过程语法：

create or replace procedure 过程名(参数)
as
plsql子程序体;

调用存储过程：(两种方法)

// 方法一
exec sayhello();

// 方法二
begin
  sayhello();
end;

创建带输入参数（in类型）的存储过程：

create or replace procedure raisesalary(eno in number)
as
  --定义变量
  psal emp.salary%type
begin
  --程序体
  select salary into psal from emp where empno = eno;
  update emp set sal = sal + 100 where empno = eno;
  --注意：一般不在存储过程或者存储函数里面commit或者rollback
  --输出
  dbms_output.put_line('涨工资前薪水：'||psal||' 涨工资后薪水：'||(psal+100));
end;

创建带输入输出参数（in类型，out类型）的存储过程：

create or replace procedure queryempinfo(eno in number,
                                         pname out varchar2(20),
                                         psal  out number, 
                                         pjob  out varchar2(20))
as
begin
  --程序体
  select ename,salary,ejob into pname,psal,pjob from emp where empno = eno;
end;

存储函数

创建存储函数语法：

create or replace function 函数名(参数)
return 函数值类型
as
plsql 子程序体;

创建存储函数：

create or replace function(eno in number)
return number
as
  --定义变量
  psal   emp.salary%type;
  pcomm  emp.comm%type;
begin
  --程序体
  select sal,comm into psal,pcomm from emp where empno = eno;
  --返回值
  return psal * 12 + nvl(pcomm,0);
end;

/*
注意：nvl（a,0）表示如果a为空值，则让a等于0
*/

示例：JDBC 访问存储过程和存储函数：（注：需求jar包：ojdbc14.jar）

private static String driver = "oracle.jdbc.OracleDriver";
private static String url= "jdbc:oracle:thin:@localhost:1521:orcl";
private static String user= "scott";
private static String passsword= "tiger";
=============================================
//访问存储过程示例
@Test
public void testProcedure(){

  String sql = "{call queryempinfo(?,?,?,?)}";--------------------------重点(一个输入参数，三个输出参数)

  Connection conn = null;
  CallableStatement call = null;        --------------------------重点（CallableStatement接口的应用）
  
  conn = DBUtil.getConnection();
  call = conn.prepareCall(sql);         --------------------------重点

  //对于输入参数，赋值
  call.setInt(1,7890);

  //对于输出参数，声明类型
  call.registerOutParameter(2,OracleTypes.VARCHAR);      --------------------------重点
  call.registerOutParameter(3,OracleTypes.NUMBER);
  call.registerOutParameter(4,OracleTypes.VARCHAR);

  //执行调用
  call.execute();

  //取出结果
  String name = call.getString(2);
  double salary = call.getDouble(3);
  String job = call.getString(4);
}

//访问存储函数示例
@Test
public void testProcedure(){

  String sql = "{ ? = call queryempinfo(?)}";--------------------------重点（一个输入参数，一个返回值）

  Connection conn = null;
  CallableStatement call = null;        --------------------------重点（CallableStatement接口的应用）
  
  conn = DBUtil.getConnection();
  call = conn.prepareCall(sql);         --------------------------重点

  //对于输出参数，声明类型
  call.registerOutParameter(1,OracleTypes.NUMBER);      --------------------------重点

  //对于输入参数，赋值
  call.setInt(2,7890);

  //执行调用
  call.execute();

  //取出结果
  double salary = call.getDouble(1);
}

在out参数中使用光标：

声明包结构
包头
包体

创建包：

create or replace package mypackage as

  type empcursor is ref cursor;       ------------自定义一个类型作为输出参数类型
  procedure queryempinfo(dno in number,emplist out empcursor);

end mypackage;

创建包体：

cteate or replace package body mypackage as

  procedure queryempinfo(dno in number,emplist out empcursor)
  as
  begin
  --打开光标
  open emplist for select * from emp where depno = dno;

  end queryempinfo;

end mypackage;

示例：访问包下面的存储过程：
注：需要带上包名

//访问包中的存储过程示例
@Test
public void testProcedure(){

  String sql = "{call mypackage.queryempinfo(?,?)}";--------------------------重点(一个输入参数，一个输出参数)

  Connection conn = null;
  CallableStatement call = null;        --------------------------重点（CallableStatement接口的应用）
  ResultSet rs = null;

  conn = DBUtil.getConnection();
  call = conn.prepareCall(sql);         --------------------------重点

  //对于输入参数，赋值
  call.setInt(1,7890);

  //对于输出参数，声明类型
  call.registerOutParameter(2,OracleTypes.CURSOR);      --------------------------重点

  //执行调用
  call.execute();

  //取出结果
  rs = ((OracleCallableStatement)call).getCursor(2);
  while(rs.next()){
     int empno = rs.getInt("empno");
     String name = rs.getString("ename");
  }
}