如何将 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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 18:15:22  来源:igfitidea点击:

How to wrap an Oracle stored procedure in a function that gets executed by a standard SELECT query?

oraclestored-proceduresfunction

提问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:

我看到两个问题:

  1. The dynamic query does not work that way, try this:

    'select PYID, PXINSNAME, PZINSKEY from PC_WORK where PXOBJCLass ='''||PXOBJCLASS||''''

  1. 动态查询不能那样工作,试试这个:

    '从 PC_WORK 中选择 PYID、PXINSNAME、PZINSKEY,其中 PXOBJCLass ='''||PXOBJCLASS||''''

You don't need myObjClass, and it seems all your quotes are wrong.

您不需要 myObjClass,而且您的所有引号似乎都是错误的。

  1. The quoting on 'SomeSampletask'...

    select COL1, COL2, COL3 from TABLE(MY_FUNC('SomeSampletask'));

  1. 对“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。