Oracle存储过程中的表变量 [英] Table variable in Oracle stored procedure
问题描述
我是Oracle的新手,我需要将SQL Server存储过程转换为oracle.在SQL Server中,我具有返回表变量的函数.
I'm new to Oracle and I need to translate my SQL Server stored procedure to oracle. In SQL Server, I have function that returns a table variable.
执行如下:
Declare @tblTSVal table(stValue varchar(500), itemKey varchar(255), props varchar(max))
insert @tblTSVal(stValue, itemKey, props)
select * from dbo.fn_ag_valuesToTable(@tsVal)
我在Oracle数据库中编写了此函数,并且可以正常工作.但是我不知道如何在存储过程中声明表变量.
I write this function in my Oracle database and it's working. But I do not know how to declare a table variable in stored procedure.
现在我正在尝试这样:
声明:
type AGValues as object
(
stValue varchar(255),
itemKey varchar(255),
props varchar(8000)
);
type ValTBL is Table of AGValues;
执行中:
insert into tblTSVal(stValue, itemKey, props)
select * from TABLE(dbMis.fn_ag_valuesToTable(tsVal));
这会导致错误:
表或视图不存在.
Table or view does not exist.
请给我一些建议.
推荐答案
ValTBL是所谓的嵌套表.要在其中加载数据,请使用BULK COLLECT选项.
ValTBL is so-called nested table. To load data in it please use BULK COLLECT option.
示例:
SELECT * BULK COLLECT INTO tblTSVal FROM TABLE(dbMis.fn_ag_valuesToTable(tsVal));
但这不是表或视图不存在"的根.错误问题.请检查您执行dbMis.fn_ag_valuesToTable
功能的权限.
But that is not root of the "Table or view does not exist." error problem. Please check your rights to execute dbMis.fn_ag_valuesToTable
function.
使用pl/sql函数作为表是值得阅读的关于流水线和并行表函数的信息. http://docs.oracle.com/cd/E11882_01 /appdev.112/e10765/pipe_paral_tbl.htm#ADDCI2140
To use pl/sql function as a table is worth to read about pipelined and parallel table functions. http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/pipe_paral_tbl.htm#ADDCI2140
这篇关于Oracle存储过程中的表变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!