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 /测试传表名参数的function(testrerecoretabname
SQL> set serveroutput on
SQL> declare
2 testre mytabletype :=mytabletype();
3 i number :=0;
4 begin
5 testre := testrerecordtabname('a',1);
6 loop
7 i := i+1;
8 dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';');
9 exit when i = testre.count;
10 end loop;
11 end;
12 /
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 /测试传表名参数的function(testrerecoretabname
SQL> set serveroutput on
SQL> declare
2 testre mytabletype :=mytabletype();
3 i number :=0;
4 begin
5 testre := testrerecordtabname('a',1);
6 loop
7 i := i+1;
8 dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';');
9 exit when i = testre.count;
10 end loop;
11 end;
12 /
Line: 17
Text: l_data(l_data.count) := myobjectype(i1,i3,i2);Error: PL/SQL: Statement ignored
Line: 17
Text: l_data(l_data.count) := myobjectype(i1,i3,i2);