sql>create or replace type mytabletype as table of number;
SQL> create or replace function testrerecordtabname (tablename in varchar2,tableid in number)
2 return mytabletype
3 as
4 l_data mytabletype :=mytabletype();
5 strsql varchar2(50);
6 type v_cursor is ref cursor;
7 v_tempcursor v_cursor;
8 i1 number;
9 i2 varchar2(50);
10 i3 date;
11 begin
12 strsql := 'select * from ' || tablename || ' where id>=' || tableid;
13 open v_tempcursor for strsql;
14 loop
15 fetch v_tempcursor into i1,i2,i3;
16 l_data.extend;
17 l_data(l_data.count) := myobjectype(i1,i3,i2);
18 exit when v_tempcursor%NOTFOUND;
19 end loop;
20 return l_data;
21 end;
22 /
SQL> create or replace function testrerecordtabname (tablename in varchar2,tableid in number)
2 return mytabletype
3 as
4 l_data mytabletype :=mytabletype();
5 strsql varchar2(50);
6 type v_cursor is ref cursor;
7 v_tempcursor v_cursor;
8 i1 number;
9 i2 varchar2(50);
10 i3 date;
11 begin
12 strsql := 'select * from ' || tablename || ' where id>=' || tableid;
13 open v_tempcursor for strsql;
14 loop
15 fetch v_tempcursor into i1,i2,i3;
16 l_data.extend;
17 l_data(l_data.count) := myobjectype(i1,i3,i2);
18 exit when v_tempcursor%NOTFOUND;
19 end loop;
20 return l_data;
21 end;
22 /
借西西代码用用
type v_cursor is ref cursor;类型声明是游标变量
v_tempcursor v_cursor;声明游标变量对象
open v_tempcursor for strsql打开游标
AS
TYPE RefCursor IS REF CURSOR;
Function GetCompany(key IN char) return RefCursor;
END;
/
CREATE OR REPLACE PACKAGE BODY ROME IS
IS
Function GetCompany(key IN char) return RefCursor
Is
v_temp RefCursor;
BEGIN
OPEN v_temp FOR
SELECT * FROM Company WHERE com_ID =key;
return v_temp;
END GetCompany;
END;
type outList is ref cursor;
function P_LOGIN(nsrsbh in varchar2,s0 in number,s1 in varchar2) return outList;end;
create or replace package body types is
/* -----P_LOGIN---- */
function P_LOGIN(nsrsbh in varchar2,s0 in number,s1 in varchar2) return outList is
taxpayerList outList;
ln number;
rn_nsrshb varchar2(50);
begin
--ln:=crypt(p_str => nsrsbh,o_str => rn_nsrshb);
if (s0=1) then
--OPEN taxpayerList FOR select a.* from sb_xt_users a,sb_xt_useres b where a.user_mc=decrypt(b.user_name,'yfzx') and b.user_name=rn_nsrshb and b.user_pass=md5(s1);
OPEN taxpayerList FOR select a.* from sb_xt_users a where a.user_mc=nsrsbh and a.user_mm=md5(s1);
elsif s0=2 then
--OPEN taxpayerList FOR select a.* from sb_xt_users a,sb_xt_useres b where a.user_mc=decrypt(b.user_name,'yfzx') and b.user_name=rn_nsrshb;
OPEN taxpayerList FOR select a.* from sb_xt_users a where a.user_mc=nsrsbh;
else
--OPEN taxpayerList FOR select a.* from sb_xt_users a where a.user_mc=nsrsbh;
OPEN taxpayerList FOR select a.* from sb_xt_users a where a.user_mc=nsrsbh;
end if;
return taxpayerList;
end;
end;