输入输出的标准游标写法//返回游标//建包CREATE OR REPLACE PACKAGE pkg_testASTYPE myrctype IS REF CURSOR;PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);END pkg_test;///建过程CREATE OR REPLACE PACKAGE BODY pkg_testASPROCEDURE get (p_id NUMBER, p_rc OUT myrctype)ISsqlstrVARCHAR2 (500);BEGINIF p_id = 0 THENOPEN p_rc FORSELECT ID, NAME, sex, address, postcode, birthdayFROM student;ELSEsqlstr :='select id,name,sex,address,postcode,birthdayfrom student where id=:w_id';OPEN p_rc FOR sqlstr USING p_id;END IF;END get;END pkg_test;///测试declarev_ID varchar2(10);v_NAME varchar2(30);v_sex varchar2(2);v_address varchar(50);v_postcode varchar(6);v_birthday date;v_rc pkg_test.myrctype;beginpkg_test.get(0,v_rc);loopfetch v_rc into v_id,v_name,v_sex,v_address,v_postcode,v_birthday;exit when v_rc%notfound;dbms_output.put_line(v_id||v_name||v_sex||v_address||v_postcode||v_birthday);end loop;end;/例二://返回游标//建包CREATE OR REPLACE PACKAGE testASTYPE myrctype IS REF CURSOR;END test;///建过程create procedure pro(c out test.myrctype)asstr varchar2(200);beginstr:='select * from student';open c for str;end;///测试declarev_ID varchar2(10);v_NAME varchar2(30);v_sex varchar2(2);v_address varchar(50);v_postcode varchar(6);v_birthday date;v_rc test.myrctype;beginpro(v_rc);loopfetch v_rc into v_id,v_name,v_sex,v_address,v_postcode,v_birthday;exit when v_rc%notfound;dbms_output.put_line(v_id||v_name||v_sex||v_address||v_postcode||v_birthday);end loop;end;/
strsql varchar2(200);
begin
strsql:='select count(*) from '||p_tab||' where '||p_col||' =100';
execute immediate strsq
end;
strsql varchar2(200);
p_out number(100);
begin
strsql:='select count(*) from '||p_tab||' where '||p_col||' =100';
execute immediate strsql into p_out;
return p_out;
exception
when others then
return 0;
end;
CREATE OR REPLACE PACKAGE "PKG_TEST" AS
TYPE myrcType IS REF CURSOR;
FUNCTION get(strSQL VARCHAR) RETURN myrcType;
END pkg_test;CREATE OR REPLACE PACKAGE BODY "PKG_TEST" AS
FUNCTION get(strSQL IN VARCHAR) RETURN myrcType IS
rc myrcType;
BEGIN
OPEN rc FOR strSQL;
RETURN rc;
END get;
END pkg_test;
我以前一直考虑一个问题就是游标的关闭问题,游标变量c1作为参数返回的时候,比如赋予另一个游标变量c2,为什么c2没有关闭呢,应该关闭的吧,游标变量是否可以看作是指针,也就是关闭c2的时候也关闭了c1?