/* 存储过程怎么返回数据集 *//* 1.建立带ref cursor定义的包和包体及过程 */ create or replace package pkg_test as type myrctype is ref cursor; procedure display(p_empno char,p_rc out myrctype); end;create or replace package body pkg_test as procedure display(p_empno char,p_rc out myrctype) is sqlst varchar2(100); begin if p_empno is null then open p_rc for select emp_name from student; else sqlst := 'select emp_name from student where emp_no = :w_empno'; open p_rc for sqlst using p_empno; end if; end; end;调用: declare w_rc pkg_test.myrctype; w_empname student.emp_name%type; begin pkg_test.display('0001', w_rc); loop fetch w_rc into w_empname; exit when w_rc%notfound; dbms_output.put_line(w_empname); end loop; end;
AS
begin
execute immediate 'select * from table1';
end
AS
begin
execute immediate 'select * from table1';
end
在sql*plus中用
exec username.procedure_name;
执行也成功。但是界面上没有反应。存储过程的输出结果到哪了
create or replace package pkg_test
as
type myrctype is ref cursor;
procedure display(p_empno char,p_rc out myrctype);
end;create or replace package body pkg_test
as
procedure display(p_empno char,p_rc out myrctype)
is
sqlst varchar2(100);
begin
if p_empno is null then
open p_rc for select emp_name from student;
else
sqlst := 'select emp_name from student where emp_no = :w_empno';
open p_rc for sqlst using p_empno;
end if;
end;
end;调用:
declare
w_rc pkg_test.myrctype;
w_empname student.emp_name%type;
begin
pkg_test.display('0001', w_rc); loop
fetch w_rc into w_empname;
exit when w_rc%notfound;
dbms_output.put_line(w_empname);
end loop;
end;
当你的SQL语句中查询的不只是一个emp_name变量时在SQL PLUS中如何调试?
variable x refcursor;
exec 调用过程名;
print x;
最前面加一句
print x;--这一句如果有了set autoprint on就可以省去了。
set serverout on;
如
过程为 p_test(par1 out cursor);
那调用时就为
set serverout on;
variable x refcursor;
exec p_test(x);
print x;
数据库是“Oracle8 Release 8.0.5.0.0 - Production”FOR WIN2000。SQL> begin
2 execute immediate 'set role all';
3 end;
4 /
execute immediate 'set role all';
*
错误位于第2行:
ORA-06550: 行2、列11:
PLS-00103: 出现符号"IMMEDIATE"在需要下列之一时:
:=.(@%;
符号":=在"IMMEDIATE"继续之前已插入。/********************/
SQL> desc tb_temp;
名称 是否为空?类型
------------------------------- -------- ----
PHONE_NO NOT NULL VARCHAR2(7)
CITY_CODE NOT NULL VARCHAR2(4)
CITY_NAME NOT NULL VARCHAR2(8)
STATE NOT NULL VARCHAR2(6)
TYPE NOT NULL VARCHAR2(6)
TB_ID NUMBER(38)
INSERTTIME DATESQL> begin
2 execute immediate 'select * from tb_temp';
3 end;
4 /
execute immediate 'select * from tb_temp';
*
错误位于第2行:
ORA-06550: 行2、列11:
PLS-00103: 出现符号"IMMEDIATE"在需要下列之一时:
:=.(@%;
符号":=在"IMMEDIATE"继续之前已插入。
SQL>
2 execute immediate 'set role all';
3 end;
4 /PL/SQL 过程已成功完成。SQL>