如何将 Oracle 存储过程包装在由标准 SELECT 查询执行的函数中?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/864695/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to wrap an Oracle stored procedure in a function that gets executed by a standard SELECT query?
提问by Sheraz
I am following these steps, but I continue to get an error and don't see the issue:
我正在按照以下步骤操作,但我继续收到错误消息并且没有看到问题:
1) Create a custom Oracle datatype that represents the database columns that you want to retrieve:
1) 创建一个自定义 Oracle 数据类型,代表您要检索的数据库列:
CREATE TYPE my_object AS OBJECT
(COL1 VARCHAR2(50),
COL2 VARCHAR2(50),
COL3 VARCHAR2(50));
2) Create another datatype that is a table of the object you just created:
2)创建另一个数据类型,它是您刚刚创建的对象的表:
TYPE MY_OBJ_TABLE AS TABLE OF my_object;
3) Create a function that returns this table. Also use a pipeline clause so that results are pipelined back to the calling SQL, for example:
3) 创建一个返回这个表的函数。还可以使用管道子句,以便将结果通过管道传输回调用 SQL,例如:
CREATE OR REPLACE
FUNCTION MY_FUNC (PXOBJCLASS varchar2)
RETURN MY_OBJ_TABLE pipelined IS
TYPE ref1 IS REF CURSOR
Cur1 ref1,
out_rec_my_object := my_object(null,null,null);
myObjClass VARCHAR2(50);
BEGIN
myObjClass := PXOBJCLASS
OPEN Cur1 For ‘select PYID, PXINSNAME, PZINSKEY from PC_WORK where PXOBJCLass = ;1'USING myObjClass,
LOOP
FETCH cur1 INTO out_rec.COL1, out_rec.COL2, out_rec.COL3;
EXIT WHEN Cur1%NOTFOUND;
PIPE ROW (out_rec);
END LOOP;
CLOSE Cur1;
RETURN;
END MY_FUNC;
NOTE: In the example above, you can easily replace the select statement with a call to another stored procedure that returns a cursor variable.
注意:在上面的示例中,您可以轻松地将 select 语句替换为对另一个返回游标变量的存储过程的调用。
4) In your application, call this function as a table function using the following SQL statement:
4) 在您的应用程序中,使用以下 SQL 语句将此函数作为表函数调用:
select COL1, COL2, COL3 from TABLE(MY_FUNC('SomeSampletask'));
采纳答案by tuinstoel
There is no need to use dynamic sql (dynamic sql is always a little bit slower) and there are too many variables declared. Also the for loop is much easier. I renamed the argument of the function from pxobjclass to p_pxobjclass.
没必要用动态sql(动态sql总是慢一点),声明的变量太多了。此外,for 循环要容易得多。我将函数的参数从 pxobjclass 重命名为 p_pxobjclass。
Try this:
尝试这个:
create or replace function my_func (p_pxobjclass in varchar2)
return my_obj_table pipelined
is
begin
for r_curl in (select pyid,pxinsname,pzinskey
from pc_work
where pxobjclass = p_pxobjclass) loop
pipe row (my_object(r_curl.pyid,r_curl.pxinsname,r_curl.pzinskey));
end loop;
return;
end;
EDIT1:
编辑1:
It is by the way faster to return a ref cursor instead of a pipelined function that returns a nested table:
顺便说一下,返回 ref 游标而不是返回嵌套表的流水线函数更快:
create or replace function my_func2 (p_pxobjclass in varchar2)
return sys_refcursor
is
l_sys_refcursor sys_refcursor;
begin
open l_sys_refcursor for
select pyid,pxinsname,pzinskey
from pc_work
where pxobjclass = p_pxobjclass;
return l_sys_refcursor;
end;
This is faster because creating objects (my_object) takes some time.
这更快,因为创建对象 (my_object) 需要一些时间。
回答by Osama Al-Maadeed
I see two problems:
我看到两个问题:
The dynamic query does not work that way, try this:
'select PYID, PXINSNAME, PZINSKEY from PC_WORK where PXOBJCLass ='''||PXOBJCLASS||''''
动态查询不能那样工作,试试这个:
'从 PC_WORK 中选择 PYID、PXINSNAME、PZINSKEY,其中 PXOBJCLass ='''||PXOBJCLASS||''''
You don't need myObjClass, and it seems all your quotes are wrong.
您不需要 myObjClass,而且您的所有引号似乎都是错误的。
The quoting on 'SomeSampletask'...
select COL1, COL2, COL3 from TABLE(MY_FUNC('SomeSampletask'));
对“SomeSampletask”的引用...
从 TABLE(MY_FUNC('SomeSampletask')) 中选择 COL1、COL2、COL3;
回答by Paul Sonier
Maybe I'm misunderstanding something here, but it seems like you want to be using a VIEW.
也许我在这里误解了一些东西,但您似乎想使用 VIEW。