CREATE OR REPLACE PACKAGE WYUSER.F_MODEL as
type m_cursor is ref cursor;
function MModelbrow(
id1 in number
) return m_cursor;
end;CREATE OR REPLACE PACKAGE BODY WYUSER.F_MODEL as
function MModelbrow(id1 in number)
return m_cursor IS
ACursor m_cursor;
l1 number;
strCreateTable varchar2(600);
BEGIN
l1:=0;
strCreateTable:='CREATE GLOBAL TEMPORARY TABLE re (ID2 number,level2 number) ON COMMIT PRESERVE ROWS';
Execute immediate strCreateTable; Execute immediate 'INSERT INTO RE values (:id1,:l1)' using id1,l1;
open ACursor for 'select * from re';
return ACursor;
end MModelbrow;
end;请教各位大虾。如何调用?
我想把它作为一个表来调用例如:select * from *****
type m_cursor is ref cursor;
function MModelbrow(
id1 in number
) return m_cursor;
end;CREATE OR REPLACE PACKAGE BODY WYUSER.F_MODEL as
function MModelbrow(id1 in number)
return m_cursor IS
ACursor m_cursor;
l1 number;
strCreateTable varchar2(600);
BEGIN
l1:=0;
strCreateTable:='CREATE GLOBAL TEMPORARY TABLE re (ID2 number,level2 number) ON COMMIT PRESERVE ROWS';
Execute immediate strCreateTable; Execute immediate 'INSERT INTO RE values (:id1,:l1)' using id1,l1;
open ACursor for 'select * from re';
return ACursor;
end MModelbrow;
end;请教各位大虾。如何调用?
我想把它作为一个表来调用例如:select * from *****
declare
vresult F_MODEL.m_cursor;
level2 number;
id2 number;
begin
vresult:=F_MODEL.MModelbrow(12334);
loop
exit when vresult%notfound;
fetch vresult into id2,level2 ;
dbms_output.put_line(id2||','||level2);
end loop;
end;
如果在sqlplus里面执行,先执行一下set serveroutput on另外建议你在MModelbrow函数中的
Execute immediate strCreateTable;
下面增加exception 处理,否则表已经存在的情况再建表会报错:
exception when others then null;
你的函数有建表,insert过程,用select肯定是不行的
type m_cursor is ref cursor;
function MModelbrow(
id1 in number
) return m_cursor;
end; CREATE OR REPLACE PACKAGE BODY WYUSER.F_MODEL as
function MModelbrow(id1 in number)
return m_cursor IS
ACursor m_cursor;
l1 number;
strCreateTable varchar2(600);
BEGIN
l1:=0;
strCreateTable:='CREATE GLOBAL TEMPORARY TABLE re (ID2 number,level2 number) ON COMMIT PRESERVE ROWS';
Execute immediate strCreateTable; Execute immediate 'INSERT INTO RE values (:id1,:l1)' using id1,l1;
open ACursor for 'select * from re';
return ACursor;
end MModelbrow;
end;
如果我把函数修改为存储过程。能否当表来进行使用?