17,090
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure test_cursor(p_1 in number,out_cursor out SYS_REFCURSOR)
as
begin
open out_cursor for
select t.* from emp t;
end;
create procedure test_cursor(p_1 in number) as
/*declare cur cursor with return for
select Uid,UserId,SendNum from userfee where uid in (select Uid from userdata where ucase(loginid)=ucase(piLoginId) and ucase(UserId)<>ucase(piLoginId)) order by uid;
open cur;*/ 这是DB2的写法
CREATE PROCEDURE "DEV2"."SYNCUSRFEEINFO_B"
("PIUID" IN NUMBER,
"PILASTDBREMAINED" IN NUMBER,
"PIREMAINED" IN NUMBER,
"PIPREPAYUSED" IN NUMBER,
"PIPOSTPAYUSED" IN NUMBER,
out_cursor out SYS_REFCURSOR
)
AS
BEGIN
OPEN out_cursor FOR
SELECT SENDNUM-piPrePayUsed as sendnum from userfee where uid=piUid;
end;
open P_RES for select *.....;
create or replace package PagePK
is
type t_cursor is ref cursor;
procedure retcur(
P_CODE varchar2,
P_MESSAGE out varchar2,
P_SQLSTATUS out varchar2,
P_RES out t_cursor
);
end PagePK;
/
CREATE OR REPLACE package BODY PagePK
is
procedure retcur
(
P_CODE varchar2,
P_MESSAGE out varchar2,
P_SQLSTATUS out varchar2,
P_RES out t_cursor
)
is
P_SQLSTATUS:='0';
P_MESSAGE:='执行成功';
begin
null;
open P_RES for select *.....;
Exception
WHEN OTHERS
Then
P_SQLSTATUS:='-1';
P_MESSAGE:=' (CCGC.GJDM)系统错误:SQLCODE=' || to_CHAR(SQLCODE)||' ';
end retcur;
end PagePK;
create or replace procedure test_cursor(p_1 in number,out_cursor out SYS_REFCURSOR)
as
v1 emp.col1%type;
v2 emp.col2%type;
v3 emp.col3%type;
begin
open out_cursor for
select col1,col2,col3 from emp t where col=p_1;
for out_cursor_rec in out_cursor
loop
v1:=out_cursor_rec.col1;
v2:=out_cursor_rec.col2;
v3:=out_cursor_rec.col3;
dbms_output.put_line('col1的结果'||to_char(v1)||' '||'col2的结果'||to_char(v2)||' '||'col3的结果'||to_char(v3));
end loop;
close out_cursor;
end;
--使用函数
CREATE OR REPLACE FUNCTION test_cursor(p_1 IN NUMBER) RETURN SYS_REFCURSOR IS
out_cursor SYS_REFCURSOR;
BEGIN
OPEN out_cursor FOR
SELECT t.* FROM emp t;
RETURN out_cursor;
END;
--忘记释放游标了
create or replace procedure test_cursor(p_1 in number,out_cursor out SYS_REFCURSOR)
as
v1 emp.col1%type;
v2 emp.col2%type;
v3 emp.col3%type;
begin
open out_cursor for
select col1,col2,col3 from emp t where col=p_1;
loop
fetch out_cursor into v1,v2,v3;
exit when out_cursor%notfound;
dbms_output.put_line('col1的结果'||to_char(v1)||' '||'col2的结果'||to_char(v2)||' '||'col3的结果'||to_char(v3))
end loop;
close out_cursor;
end;
create or replace procedure test_cursor(p_1 in number,out_cursor out SYS_REFCURSOR)
as
v1 emp.col1%type;
v2 emp.col2%type;
v3 emp.col3%type;
begin
open out_cursor for
select col1,col2,col3 from emp t where col=p_1;
loop
fetch out_cursor into v1,v2,v3;
exit when out_cursor%notfound;
dbms_output.put_line('col1的结果'||to_char(v1)||' '||'col2的结果'||to_char(v2)||' '||'col3的结果'||to_char(v3))
end loop;
end;