问题描述
我有这张桌子:
ALLITEMS
---------------
ItemId | Areas
---------------
1 | EAST
2 | EAST
3 | SOUTH
4 | WEST
DDL:
drop table allitems;
Create Table Allitems(ItemId Int,areas Varchar2(20));
Insert Into Allitems(Itemid,Areas) Values(1,'east');
Insert Into Allitems(ItemId,areas) Values(2,'east');
insert into allitems(ItemId,areas) values(3,'south');
insert into allitems(ItemId,areas) values(4,'east');
在 MSSQL 中,我可以从动态 SQL 中获取游标:
In MSSQL, to get a cursor from a dynamic SQL I can do:
DECLARE @v_sqlStatement VARCHAR(2000);
SET @v_Sqlstatement = 'SELECT * FROM ALLITEMS';
EXEC (@v_sqlStatement); --returns a resultset/cursor, just like calling SELECT
在 Oracle 中,我需要使用 PL/SQL 块:
In Oracle, I need to use a PL/SQL Block:
SET AUTOPRINT ON;
DECLARE
V_Sqlstatement Varchar2(2000);
outputData SYS_REFCURSOR;
BEGIN
V_Sqlstatement := 'SELECT * FROM ALLITEMS';
OPEN outputData for v_Sqlstatement;
End;
--result is : anonymous block completed
**但我得到的只是
匿名区块已完成".
如何让它返回光标?
(我知道如果我做 AUTOPRINT,它会打印出 REFCURSOR 中的信息(上面的代码中没有打印,但那是另一个问题))
我将从代码(ODBC、C++)中调用这个动态 SQL,我需要它来返回一个游标.怎么样?
I will be calling this Dynamic SQL from code (ODBC,C++), and I need it to return a cursor. How?
推荐答案
您可以编写一个 PL/SQL 函数来返回该游标(或者,如果您有更多与此相关的代码,您可以将该函数放入一个包中):
You can write a PL/SQL function to return that cursor (or you could put that function in a package if you have more code related to this):
CREATE OR REPLACE FUNCTION get_allitems
RETURN SYS_REFCURSOR
AS
my_cursor SYS_REFCURSOR;
BEGIN
OPEN my_cursor FOR SELECT * FROM allitems;
RETURN my_cursor;
END get_allitems;
这将返回光标.
如果可能,请确保不要将 SELECT
-String 放入 PL/SQL 中的引号中.把它放在字符串中意味着在编译时无法检查它,并且每次使用时都必须对其进行解析.
Make sure not to put your SELECT
-String into quotes in PL/SQL when possible. Putting it in strings means that it can not be checked at compile time, and that it has to be parsed whenever you use it.
如果您确实需要使用动态 SQL,您可以将查询放在单引号中:
If you really need to use dynamic SQL you can put your query in single quotes:
OPEN my_cursor FOR 'SELECT * FROM allitems';
每当调用函数时都必须解析此字符串,这通常会变慢并在运行时隐藏查询中的错误.
This string has to be parsed whenever the function is called, which will usually be slower and hides errors in your query until runtime.
确保尽可能使用绑定变量以避免硬解析:
Make sure to use bind-variables where possible to avoid hard parses:
OPEN my_cursor FOR 'SELECT * FROM allitems WHERE id = :id' USING my_id;
这篇关于如何从执行动态 SQL 的 Oracle PL/SQL 匿名块返回结果集/游标?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!