Oracle存储的过程返回结果集

Question

我可以在不使用避难所的情况下定义存储过程吗？（例如"返回者"）

我不想使用oracledbtype.refcursor，因为它不是在其他数据库中以dbparameter的形式发送的。

也dbparameter.dbtype = oracledbtype.refcursor;不支持

我不想在下面的代码中定义" retval in of out sys_refcursor"。还有另一种方法吗？

CREATE OR REPLACE procedure SYSTEM.customer_select_row(
    p_email IN CUSTOMER.Email%TYPE,
    p_password IN CUSTOMER."Password"%TYPE,
    retval IN OUT SYS_REFCURSOR
  )
IS
BEGIN
  OPEN retval FOR
    SELECT CustomerId, FirstName, LastName FROM CUSTOMER
    WHERE Email = p_email AND "Password" = p_password 
END customer_select_row;

Answer 1

您可以使用管道功能，

这是一个函数，可作为表

加剧起作用

您可以这样称呼它

SELECT * 
  FROM TABLE(TEST_PIPELINE.STOCKPIVOT(10));

test_pipeline.stockpivot（10）是函数

您可以这样构建：

create or replace PACKAGE TEST_PIPELINE AS
  -- here you declare a type record 
  type t_record is record
  (
    field_1     VARCHAR2(100),
    field_2      VARCHAR2(100));
  -- declare a table type from your previously created type 
  TYPE t_collection IS TABLE OF t_record;
  -- declare that the function will return the collection pipelined
  FUNCTION StockPivot(P_LINES NUMBER) RETURN t_collection PIPELINED;
  END;
/
create or replace PACKAGE BODY TEST_PIPELINE IS
FUNCTION StockPivot(P_LINES NUMBER) RETURN t_collection PIPELINED IS
  -- declare here a type of the record
  T_LINE T_RECORD;
  BEGIN
    -- here is a loop example for insert some lines on pipeline
    FOR I IN 1..P_LINES LOOP
      -- inser data on your line this way
      T_LINE.field_1      := 'LINE - ' || I;
      T_LINE.field_2      := 'LINE - ' || I;
      -- then insert insert the line for result (this kind of functions should not have a return statement)
      PIPE ROW (T_LINE );
    END LOOP;
  END;
END;