Oracle的存储过程（procedure）

创建

--存储过程定义 
create or replace procedure proce01(a in varchar2, b in out varchar2) 
as  -- 此处 as 等同 is

begin
   -- b := a + b; -- 会将 字符转换为数值 ，注意a为in，所以不能被赋值，这与c里的函数不同
   b := a || b;
   dbms_output.put_line('a:'||a||'   b:'||b);
end proce01;

执行

-- 执行
declare 
     a  varchar2(20):= '01';
     b  varchar2(20):= 'bg';
begin  
    proce01(a ,b);
    dbms_output.put_line('a:'||a||'   b:'||b);
end;

执行结果：
a:01 b:01bg
a:01 b:01bg

删除存储过程

-- 删除存储过程
DROP PROCEDURE proce01

 

 

 

存储过程返回多条记录

Oracle 参照游标(SYS_REFCURSOR)使用

 

-- 创建 procedure
create or replace procedure getResult(p_cursor out sys_refcursor)
as
begin
  open p_cursor for
  select * from user_info;
end;

 

 

-- 使用存储过程

-- 使用存储过程
declare
  v_cursor sys_refcursor;
  u user_info%rowtype;
begin
  getResult(v_cursor);
  loop 
  fetch v_cursor into u.user_id, u.user_name,u.sex;          
  exit when v_cursor%notfound;
  dbms_output.put_line(u.user_id||'-'||u.user_name||'-'||u.sex);
  end loop;
end;

 

Java中调用存储过程

 

参考：java调用oracle存储过程返回多条结果集 - BorD的选择 - 博客园

package vip.yaocn.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

/**
 * 存储过程测试
 *
 * @author yacon
 */
public class ProcedureTest {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String driverName = "oracle.jdbc.driver.OracleDriver";
        String username = "scott";
        String password = "tiger";
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, username, password);
            String callSql = "{call pack_myPackage.proc_getSomeInfo(?, ?) }";
            cstmt = conn.prepareCall(callSql);
            cstmt.setString(1, "1001");
            cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
            cstmt.execute();
            rs = (ResultSet) cstmt.getObject(2);
            if (rs != null) {
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                if (cstmt != null) {
                    cstmt.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

 

-------------------------------------

2025-05-19

另一个查询多结果集的例子

创建存储过程

CREATE OR REPLACE PROCEDURE get_employees(
  p_dept_id IN NUMBER,
  p_result OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN p_result FOR 
    SELECT * FROM employees 
    WHERE department_id = p_dept_id;
END;

plsql调用：

DECLARE
  v_cursor SYS_REFCURSOR;
  v_emp employees%ROWTYPE;
BEGIN
  get_employees(10, v_cursor);
  LOOP
    FETCH v_cursor INTO v_emp;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.employee_name);
  END LOOP;
  CLOSE v_cursor;
END;

 

 

Java调用：

Connection conn = DriverManager.getConnection(url,user,pwd);
CallableStatement cstmt = conn.prepareCall("{call get_employees(?,?)}");
cstmt.setInt(1, 10);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();
ResultSet rs = (ResultSet)cstmt.getObject(2);
while(rs.next()) {
  System.out.println(rs.getString("employee_name"));
}

 

多个入参多个出参的例子

创建

CREATE OR REPLACE PROCEDURE get_user_stats (
    p_age IN NUMBER,                  -- 输入参数：年龄
    p_name IN VARCHAR2,               -- 输入参数：用户名(支持模糊查询)
    o_total OUT NUMBER,               -- 输出参数：符合条件的用户总数
    o_cursor OUT SYS_REFCURSOR        -- 输出参数：用户信息结果集
)
IS
BEGIN
    -- 统计符合条件用户总数
    SELECT COUNT(*) INTO o_total 
    FROM users 
    WHERE age = p_age 
    AND username LIKE '%' || p_name || '%';

    -- 打开游标返回用户详细信息
    OPEN o_cursor FOR
    SELECT user_id, username, age, create_time, status
    FROM users 
    WHERE age = p_age 
    AND username LIKE '%' || p_name || '%';

EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20003, 
            '用户统计失败[代码:'||SQLCODE||'] '||SQLERRM);
END get_user_stats;
/

 

执行该Oracle存储过程的多种方法

1. ‌PL/SQL块中调用（推荐方式）

DECLARE
  v_total NUMBER;
  v_cursor SYS_REFCURSOR;
  v_user_id users.user_id%TYPE;
  v_username users.username%TYPE;
  v_age users.age%TYPE;
  v_create_time users.create_time%TYPE;
  v_status users.status%TYPE;
BEGIN
  -- 执行存储过程（年龄25岁，用户名包含"张"）
  get_user_stats(
    p_age => 25,
    p_name => '张',
    o_total => v_total,
    o_cursor => v_cursor
  );
  
  -- 输出总数
  DBMS_OUTPUT.PUT_LINE('匹配用户总数: ' || v_total);
  
  -- 处理游标结果
  LOOP
    FETCH v_cursor INTO v_user_id, v_username, v_age, v_create_time, v_status;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(
      'ID:' || v_user_id || 
      ' 用户名:' || v_username || 
      ' 状态:' || v_status
    );
  END LOOP;
  
  -- 关闭游标
  CLOSE v_cursor;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END;
/

 

说明‌：通过PL/SQL块处理输出参数和游标结果集

或者

DECLARE
  v_total NUMBER;
  v_cursor SYS_REFCURSOR;
  v_users users%ROWTYPE;
  
BEGIN
  -- 执行存储过程（年龄25岁，用户名包含"张"）
  get_user_stats(25,'张',v_total,v_cursor);
  
  -- 输出总数
  DBMS_OUTPUT.PUT_LINE('匹配用户总数: ' || v_total);
  
  -- 处理游标结果
  LOOP
    FETCH v_cursor INTO v_users;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(
      'ID:' || v_users.user_id || 
      ' 用户名:' || v_users.username || 
      ' 状态:' || v_users.status
    );
  END LOOP;
  
  -- 关闭游标
  CLOSE v_cursor;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END;
/

 

2. ‌SQL*Plus命令行调用

-- 定义绑定变量
VARIABLE total NUMBER;
VARIABLE cur REFCURSOR;

-- 执行存储过程
EXEC get_user_stats(25, '张', :total, :cur);

-- 打印总数
PRINT total;

-- 打印游标结果
PRINT cur;

--适用场景‌：适用于SQL*Plus等命令行工具

 

3. ‌使用CALL语句（需PL/SQL环境）

DECLARE
  v_total NUMBER;
  v_cursor SYS_REFCURSOR;
BEGIN
  CALL get_user_stats(25, '张', v_total, v_cursor);
  -- 需额外处理游标（同方法1）
END;
/

 

4. ‌开发者工具可视化调用（如PL/SQL Developer）‌
在对象浏览器中找到get_user_stats过程
右键选择 ‌Test‌
输入参数值（如p_age=25, p_name='张'）
点击执行按钮查看输出参数和结果集

 

5. ‌通过JDBC程序调用（Java示例）

try (Connection conn = DriverManager.getConnection(url, user, pwd);
     CallableStatement cstmt = conn.prepareCall(
         "{call get_user_stats(?,?,?,?)}")) {
     
  // 设置输入参数
  cstmt.setInt(1, 25);
  cstmt.setString(2, "张");
  
  // 注册输出参数
  cstmt.registerOutParameter(3, Types.INTEGER);
  cstmt.registerOutParameter(4, Types.REF_CURSOR);//Types.REF_CURSOR 报错，使用 OracleTypes.CURSOR 或 -10
  
  // 执行
  cstmt.execute();
  
  // 获取总数
  int total = cstmt.getInt(3);
  System.out.println("总数: " + total);
  
  // 处理游标
  try (ResultSet rs = (ResultSet)cstmt.getObject(4)) {
    while (rs.next()) {
      System.out.println(
        "ID:" + rs.getInt("user_id") + 
        " 用户名:" + rs.getString("username")
      );
    }
  }
}