Oracle SQL:如何从过程中输出表 [英] Oracle Sql: How can I output a table from procedure

问题描述

我有一张桌子A(a1，a2，a3);

I have a table A(a1,a2,a3);

我想写一个像这样的程序:

I want to write a procedure like this :

CREATE OR REPLACE PROCEDURE B
AS
BEGIN
.........
..........
SELECT * FROM A;
END

这样当我做EXECUTE B;

它应该输出表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

推荐答案

SQL Server(我相信您表示您来自不同的问题)与Oracle不同.您不能只拥有执行查询的过程.

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.

您可以将过程定义为具有SYS_REFCURSOR类型的OUT参数.

You could define your procedure to have an OUT parameter of type SYS_REFCURSOR.

CREATE OR REPLACE PROCEDURE b( p_rc OUT SYS_REFCURSOR )
AS
BEGIN
  OPEN p_rc 
   FOR SELECT *
         FROM a;
END;

然后，过程的调用者将需要知道如何从该引用游标中获取信息，并对结果进行有意义的处理.如果您使用的是SQL * Plus

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> variable rc refcursor;
SQL> exec b( :rc );
SQL> print rc

如果您使用的不是SQL * Plus，则代码可能会有所不同.

If you are using something other than SQL*Plus, the code would likely be different.

或者，您的过程可以返回PL/SQL集合.或者它可以是流水线表函数，而不是过程，在这种情况下，您可以在查询的FROM子句中使用它.

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 FROM clause of a query.

这篇关于Oracle SQL:如何从过程中输出表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！