oracle 从匿名块调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23977641/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
calling stored procedure from anonymous block
提问by magic
I have problem reading result from plsql stored procedure written in sql developer on Oracle 11gr2 database on local machine.
我在从本地机器上的 Oracle 11gr2 数据库上用 sql developer 编写的 plsql 存储过程读取结果时遇到问题。
This is my table:
这是我的表：
create table MY_TEST_TABLE
(employee_id NUMBER(6)
,first_name VARCHAR2(20)
,last_name VARCHAR2(25)
,email VARCHAR2(25)
,phone_number VARCHAR2(20));
This is procedure declaration:
这是程序声明：
create or replace PACKAGE TEST_PACKAGE AS
procedure test_procedure (i_id in number,
o_data out sys_refcursor);
END TEST_PACKAGE;
This is body:
这是身体：
create or replace PACKAGE BODY TEST_PACKAGE AS
procedure test_procedure (i_id in number,
o_data out sys_refcursor) AS
BEGIN
open o_data for
select employee_id,
first_name,
last_name,
email,
phone_number
from my_test_table
where EMPLOYEE_ID = i_id;
close o_data;
END test_procedure;
END TEST_PACKAGE;
And this is anonymous block call:
这是匿名块调用：
SET serveroutput on
DECLARE
in_id number;
my_cursor sys_refcursor;
current_record my_test_table%ROWTYPE;
BEGIN
in_id := 1;
test_package.test_procedure(in_id, my_cursor);
open my_cursor;
LOOP
FETCH my_cursor INTO current_record;
EXIT WHEN my_cursor%NOTFOUND;
dbms_output.put_line(' - out - ' || current_record.employee_id);
END LOOP;
END;
I am getting error:
我收到错误：
Error starting at line : 2 in command -
DECLARE
in_id number;
my_cursor sys_refcursor;
current_record my_test_table%ROWTYPE;
BEGIN
in_id := 1;
test_package.test_procedure(in_id, my_cursor);
open my_cursor;
LOOP
FETCH my_cursor INTO current_record;
EXIT WHEN my_cursor%NOTFOUND;
dbms_output.put_line(' - out - ' || current_record.employee_id);
END LOOP;
END;
Error report -
ORA-06550: line 8, column 5:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Can someone explain what is wrong? Tnx!
有人可以解释一下有什么问题吗？天啦！
回答by Alex Poole
The cursor is opened in the procedure, so you don't need to, and can't, open it directly in your anonymous block. Well, it should be open, but you're also closing it in the procedure. Remove the closefrom the procedure, and the openfrom the block:
游标在过程中打开，因此您不需要也不能直接在匿名块中打开它。嗯，它应该是打开的，但你也在程序中关闭它。close从过程中删除 ，从open块中删除：
create or replace PACKAGE BODY TEST_PACKAGE AS
procedure test_procedure (i_id in number,
o_data out sys_refcursor) AS
BEGIN
open o_data for
select employee_id,
first_name,
last_name,
email,
phone_number
from my_test_table
where EMPLOYEE_ID = i_id;
-- close o_data;
END test_procedure;
END TEST_PACKAGE;
/
And:
和：
DECLARE
in_id number;
my_cursor sys_refcursor;
current_record my_test_table%ROWTYPE;
BEGIN
in_id := 1;
test_package.test_procedure(in_id, my_cursor);
-- open my_cursor;
LOOP
FETCH my_cursor INTO current_record;
EXIT WHEN my_cursor%NOTFOUND;
dbms_output.put_line(' - out - ' || current_record.employee_id);
END LOOP;
END;
/
SQL Fiddle- just add data...
SQL Fiddle- 只需添加数据...
