/* 存储过程返回数据集 *//* 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;
大哥,别人只是要你写个过程,又不是做项目,你写那么正式干嘛? 我简单点写好了. create or replace procedure display as cursor crs is select * from dept; begin open cursor; for d in crs loop dbms_output.put_line(d.字段名1||d.字段名2...); end loop; end; /
我今天刚看了一两个游标的例子,是不是也可以这样: create or replace procedure display as cursor crs is select * from dept; begin open crs; fetch crs into var1,var2,...; while crs%FOUND loop dbms_output.put_line(var1||var2||...); fetch crs into var1,var2,...; end loop; close crs; end;
create or replace procedure display as cursor crs is select * from dept; sTmp varchar2(4000); begin sTmp :=''; open cursor; for d in crs loop sTmp :=d.字段名1||chr(9)d.字段名2||chr(9)...||chr(13); end loop; end;把这个数据集通过接口程序调用,按照控制字符解析以后就出来结果集了。
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;
我简单点写好了.
create or replace procedure display as
cursor crs is select * from dept;
begin
open cursor;
for d in crs loop
dbms_output.put_line(d.字段名1||d.字段名2...);
end loop;
end;
/
create or replace procedure display as
cursor crs is select * from dept;
begin
open crs;
fetch crs into var1,var2,...;
while crs%FOUND loop
dbms_output.put_line(var1||var2||...);
fetch crs into var1,var2,...;
end loop;
close crs;
end;
cursor crs is select * from dept;
sTmp varchar2(4000);
begin
sTmp :='';
open cursor;
for d in crs loop
sTmp :=d.字段名1||chr(9)d.字段名2||chr(9)...||chr(13);
end loop;
end;把这个数据集通过接口程序调用,按照控制字符解析以后就出来结果集了。
你看看,把sasacat(傻傻猫) 都累成啥样了,赶快给人家分。