Oracle 存储过程:返回结果集和输出参数 [英] Oracle stored procedure: return both result set and out parameters

问题描述

Oracle 存储过程有 OUT 参数并返回结果集，例如

Oracle stored procedure has OUT parameter and returns result set, e.g.

create or replace procedure foo(empId IN NUMBER, maxSalary OUT NUMBER) AS BEGIN
    select * from Employee e where e.id >=empId;
    select max(salary) into maxSalary from Employee;
END;

错误:

PLS-00428: an INTO clause is expected in this SELECT statement

Mysql 存储过程可以返回结果集和输出参数.oracle db 怎么做?

Mysql stored procedure can return both result sets and out parameters. How to do it for oracle db?

推荐答案

在 Oracle 中，您不能在没有 INTO 子句的情况下直接运行 select 语句.

In Oracle, you cannot run direct select statements without an INTO clause.

如果您使用的是 Oracle 12c 及更高版本，您可以使用 REF CURSOR 和 DBMS_SQL.RETURN_RESULT

If you are using Oracle 12c and above, you may use a REF CURSOR and DBMS_SQL.RETURN_RESULT

create or replace procedure foo(empId IN NUMBER, maxSalary OUT NUMBER) AS
q SYS_REFCURSOR;
 BEGIN
    OPEN q FOR select * from Employee e where e.id >=empId;
     DBMS_SQL.return_result (q); -- This will display the result
    select max(salary) into maxSalary from Employee;
END;

对于以前的版本 (11g,10g)，您可以将 REF CURSOR 作为 OUT 参数传递并从 sqlplus 或 TOAD 打印出来通过作为脚本运行.

For previous versions ( 11g,10g) , You could pass a REF CURSOR as an OUT parameter and print it from sqlplus or TOAD by running as script.

create or replace procedure foo(empId IN NUMBER, maxSalary OUT NUMBER,
   q OUT SYS_REFCURSOR) AS

     BEGIN
        OPEN q FOR select * from Employee e where e.id >=empId;
        select max(salary) into maxSalary from Employee;
    END;

在调用过程之前定义绑定变量.

Define bind variables before calling the procedure.

VARIABLE v_empID NUMBER
VARIABLE v_maxsalary NUMBER
VARIABLE v_q REFCURSOR

EXEC :v_empID := 101
EXEC foo(:v_empID,:v_maxsalary,:v_q ) 
PRINT v_q -- This will display the result from the query.

这篇关于Oracle 存储过程:返回结果集和输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！