在 Oracle 存储过程中运行查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1568824/
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
Run a query inside an Oracle Stored Procedure
提问by Saobi
I have a query
我有一个疑问
select * from myTable
...and I want to wrap this query inside a stored procedure, and have the store procedure output the results of this query.
...我想将此查询包装在存储过程中，并让存储过程输出此查询的结果。
How do I do it?
我该怎么做？
In ms-sql, i can store my query as a string to a string variable. And then do "Execute (variable)". Why no such thing in Oracle?
在 ms-sql 中，我可以将我的查询作为字符串存储到字符串变量中。然后执行“执行（变量）”。为什么在 Oracle 中没有这样的东西？
回答by OMG Ponies
Use:
用：
CREATE OR REPLACE PROCEDURE MY_SPROC() RETURN SYS_REFCURSOR
L_CURSOR SYS_REFCURSOR;
BEGIN
OPEN L_CURSOR FOR
SELECT * from MYTABLE;
RETURN L_CURSOR;
END;
Use this if you want to run dynamic SQL on Oracle:
如果要在 Oracle 上运行动态 SQL，请使用此选项：
CREATE OR REPLACE PROCEDURE MY_SPROC() RETURN SYS_REFCURSOR
L_CURSOR SYS_REFCURSOR;
L_QUERY VARCHAR2(5000) DEFAULT 'SELECT ...';
BEGIN
OPEN L_CURSOR FOR L_QUERY;
RETURN L_CURSOR;
END;
If you want to include bind variables in the dynamic SQL:
如果要在动态 SQL 中包含绑定变量：
CREATE OR REPLACE PROCEDURE MY_SPROC() RETURN SYS_REFCURSOR
L_CURSOR SYS_REFCURSOR;
L_QUERY VARCHAR2(5000) DEFAULT 'SELECT ...';
BEGIN
OPEN L_CURSOR FOR L_QUERY
USING bind_var1;
RETURN L_CURSOR;
END;
回答by David Aldridge
Ref Cursors have been the standard way of doing this for years, but there is a slightly different alternative using pipelined table functions: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2345
多年来，参考游标一直是执行此操作的标准方法，但使用流水线表函数的替代方法略有不同：http: //download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm #sthref2345
They are fairly widely used in data warehousing applications and the execution can be parallelised so they're very high performance (not as good as just running a SELECT though).
它们在数据仓库应用程序中得到了相当广泛的使用，并且可以并行执行，因此它们具有非常高的性能（虽然不如仅运行 SELECT 好）。
回答by Vitaliy
You need to use a ref cursor.
您需要使用引用游标。
Check out the odp documentation. It has a very good example, covering both the DB and the .Net code.
查看 odp 文档。它有一个很好的例子，涵盖了 DB 和 .Net 代码。
It comes with the installation of the oracle client, but it is hidden deep in the directory structure. Go to the -> odp -> doc -> .
它与oracle 客户端的安装一起提供，但它隐藏在目录结构的深处。转到 -> odp -> doc -> 。
