问在Oracle中执行函数X次并返回结果集
EN

Stack Overflow用户

提问于 2014-12-26 18:52:19

回答 1查看 334关注 0票数 0

我有SQL Server背景，目前在Oracle数据库上工作，所以请容忍我与不同的术语或缺乏基本的Oracle知识。

在Oracle12中，我有一个函数"ITEM_NUMBER_TYPE_SQL.GET_NEXT(O_ERROR_MESSAGE，IO_ITEM_NO，I_ITEM_TYPE);“如果成功，它将返回true/false并填充"IO_ITEM_NO”变量/参数。如果发生错误，它将填充"O_ERROR_MESSAGE“变量/参数。到目前为止，我可以成功地执行这个函数，并通过使用返回下一个项目编号(即IO_ITEM_NO)的.Net应用程序获得值。.Net应用程序已经知道它需要多少项目编号，并在.Net应用程序端(而不是在oracle端)多次执行此函数，但由于.Net应用程序盒与oracle盒在地理上是分开的，因此它花费了太多时间(每次函数调用大约0.5秒，即使使用相同的连接)。

如何在Oracle端执行此函数X次，并将IO_ITEM_NO值存储到Oracle端的临时表/存储中，并使用一次调用在.Net端检索结果集？

执行此函数的用户将只有execute和read权限，并且不可能在Oracle端创建对象，除非它是execute/read权限的一部分。(例如，在SQL server中，具有读取权限的用户可以创建临时表，但不能创建永久表)

下面是一个伪代码。

DECLARE  v_bool BOOLEAN;
DECLARE O_ERROR_MESSAGE VARCHAR2(200);
DECLARE IO_ITEM_NO VARCHAR2(25);
DECLARE COUNT INT;
DECLARE TEMP_TABLE TABLE AS (ITEM_NO VARCHAR2(25));
BEGIN
                COUNT := 10; -- Means we need to execute ITEM_NUMBER_TYPE_SQL.GET_NEXT function 10 times
                FOR i IN 1 .. COUNT
                                v_bool := ITEM_NUMBER_TYPE_SQL.GET_NEXT(O_ERROR_MESSAGE, IO_ITEM_NO, 'ITEM');
                                IF(v_bool = TRUE) 
                                                THEN SELECT 1 INTO :v_Return FROM DUAL; 
                                                -- Following statement inserts the IO_ITEM_NO value which was returned from the function into the temp table
                                                INSERT INTO TEMP_TABLE (ITEM_NO)
                                                SELECT IO_ITEM_NO FROM DUAL; 
                                END IF;
                                IF(v_bool = FALSE) THEN 
                                                SELECT 0 INTO :v_Return FROM DUAL; 
                                                i := 10; -- Breaking the loop as error encountered
                                END IF;
                END LOOP;
                -- Finally send the resultset back
                SELECT ITEM_NO FROM TEMP_TABLE;
END;

asp.net

oracle

function

回答 1

Stack Overflow用户

发布于 2014-12-26 19:51:30

首先，Oracle与MSSQL非常不同。你应该捕捉到任何‘像在MSSQL中一样’的尝试，并检查‘这个问题应该如何用Oracle来处理’。最常见的错误是尝试使用MSSQL中的临时表。

其次，对单个任务进行多次调用在任何地方都是糟糕的决定。如果可能的话，应该避免这种情况。

一般来说，看起来甲骨文的pipelined functions就是为这项任务而设计的。这些函数可以逐行返回结果集，直到您的函数返回false为止。它应该是这样的：

select * from table(my_table_function(10));

function my_table_function(limit integer) is
begin
  while get_next(...) loop
    pipe row(some_data_returned);
  end loop;
end;