Oracle Sql：如何从过程中输出表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18902765/
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
Oracle Sql: How can I output a table from procedure
提问by Justin Cave
I have a table A(a1,a2,a3);
我有一张表 A(a1,a2,a3);
I want to write a procedure like this :
我想写一个这样的程序：
CREATE OR REPLACE PROCEDURE B
AS
BEGIN
.........
..........
SELECT * FROM A;
END
so that when I do EXECUTE B;
所以当我做 EXECUTE B;
It should output table A
它应该输出表A
as happens in select * from A;
就像发生在 select * from A;
See I tried with the other answers but it was not working for me that is why i posted this question
看到我尝试了其他答案，但它对我不起作用，这就是我发布此问题的原因
回答by Justin Cave
SQL Server (which I believe you indicated you were coming from in a different question) is different than Oracle. You can't just have a procedure that executes a query.
SQL Server（我相信你指出你来自不同的问题）与 Oracle 不同。您不能只有一个执行查询的过程。
You could define your procedure to have an OUTparameter of type SYS_REFCURSOR.
您可以将过程定义为具有OUT类型的参数SYS_REFCURSOR。
CREATE OR REPLACE PROCEDURE b( p_rc OUT SYS_REFCURSOR )
AS
BEGIN
OPEN p_rc
FOR SELECT *
FROM a;
END;
The caller of your procedure would then need to know how to fetch from that ref cursor and do something meaningful with the results. If you are using SQL*Plus
然后，您的过程的调用者需要知道如何从该引用游标中获取数据并对结果执行一些有意义的操作。如果您使用 SQL*Plus
SQL> variable rc refcursor;
SQL> exec b( :rc );
SQL> print rc
If you are using something other than SQL*Plus, the code would likely be different.
如果您使用的不是 SQL*Plus，则代码可能会有所不同。
Alternately, your procedure could return a PL/SQL collection. Or it could be a pipelined table function rather than a procedure in which case you could use it in the FROMclause of a query.
或者，您的过程可以返回一个 PL/SQL 集合。或者它可能是一个流水线表函数而不是一个过程，在这种情况下，您可以在FROM查询的子句中使用它。
回答by sam
I was searching for posts to see a way to output the data from oracle function for my ssis package, so thought i will share the info on working with pipeline in oracle to output like table
我正在寻找帖子以查看从 oracle 函数为我的 ssis 包输出数据的方法，所以我想我将分享有关在 oracle 中使用管道以输出如表的信息
--create table type object to define format of the rows
--创建表类型对象来定义行的格式
CREATE OR REPLACE TYPE OUTPUT_TABLE_TYPE
IS OBJECT (
COL1 VARCHAR(100),
COL2 NUMBER
);
--create table type based on above object
--根据上述对象创建表类型
CREATE OR REPLACE TYPE OUTPUT_TABLE
AS TABLE OF OUTPUT_TABLE_TYPE
CREATE OR REPLACE FUNCTION FN_OUT_TABLE (INPARAM1 IN NUMBER)
RETURN OUTPUT_TABLE PIPELINED
IS
BEGIN
FOR RECORD_OUTPUT IN (
SELECT * FROM (
SELECT CAST('OUTPUT SAM1' AS VARCHAR(100)) AS COL1,CAST( 1 AS NUMBER) AS COL2 FROM DUAL
UNION ALL
SELECT CAST('OUTPUT SAM2' AS VARCHAR(100)) AS COL1,CAST( 2 AS NUMBER) AS COL2 FROM DUAL
) SAM_TEMP -- replace this sub query with your query
WHERE COL2=INPARAM1
)
LOOP
PIPE ROW (OUTPUT_TABLE_TYPE(RECORD_OUTPUT.COL1,RECORD_OUTPUT.COL2));
END LOOP;
END;
To get the output we need to run below select statement
要获得输出，我们需要在 select 语句下运行
SELECT * FROM TABLE(FN_OUT_TABLE(2));
find more info @ http://sqlbisam.blogspot.com/2013/12/output-table-or-multiple-rows-from-stored-procedure-function.html
