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 *****
解决方案 »
- 相同机器下2个不同用户使用sqlplus一个用户能使用,另外的报错了。
- oracle配置透明网关tg4msql连接SQLServer遇到的问题
- 如何查询Oracle数据库中个表的数据?
- 数据仓库中的merge into 和 delete + insert into
- ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","insert into AppLog nolo...","sql area","kglhin: temp")
- 在ASP和ORACLE8I编程中,用什么样的类型数据来保存一个40K以上的文档数据?
- 求问一sql语句问题!
- oem导入导出备份任务不能执行,提示验证失败!
- oracle9i安装以后需要什么配置吗?谢谢~
- Oralce816Enterprise Manager进入时初始密码怎样?
- 对分区表有深刻理解的高手请进
- oracle使用序列的问题
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;
如果我把函数修改为存储过程。能否当表来进行使用?