在 Oracle 中，打开和打开带参数的游标有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2315757/
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
In Oracle what is the difference between open-for and opening a cursor with parameters?
提问by dtc
What is the difference between these two pieces of code?
这两段代码有什么区别？
TYPE t_my_cursor IS REF CURSOR;
v_my_cursor t_my_cursor;
OPEN v_my_cursor FOR SELECT SomeTableID
FROM MYSCHEMA.SOMETABLE
WHERE SomeTableField = p_parameter;
And...
和...
CURSOR v_my_cur(p_parameter VARCHAR2) IS
SELECT SomeTableID
FROM MYSCHEMA.SOMETABLE
WHERE SomeTableField = p_parameter;
OPEN presf_cur(p_subscriber_id);
They both seem to work. Are they the same or is there some difference I should be aware of?
他们两个似乎都有效。它们是相同的还是我应该注意的一些差异？
采纳答案by APC
The second example is an explicit cursor, and it is static. That is, it is a variable associated with one SQL statement. There is a implicit equivalent...
第二个示例是显式游标，它是静态的。也就是说，它是一个与一条 SQL 语句相关联的变量。有一个隐含的等价物......
FOR lrec in ( SELECT SomeTableID
FROM MYSCHEMA.SOMETABLE
WHERE SomeTableField = p_parameter )
LOOP
do_something_with (lrec.sometableid);
END LOOP;
The first example is a ref cursor, which is a pointer to a SQL statement and so can be dynamic. For instance we can extend that example like this:
第一个示例是引用游标，它是一个指向 SQL 语句的指针，因此可以是动态的。例如，我们可以像这样扩展该示例：
TYPE t_my_cursor IS REF CURSOR;
v_my_cursor t_my_cursor;
...
if flag = 1 then
OPEN v_my_cursor FOR SELECT SomeTableID
FROM MYSCHEMA.SOMETABLE
WHERE SomeTableField = p_parameter;
else
OPEN v_my_cursor FOR SELECT SomeTableID
FROM MYSCHEMA.ANOTHERTABLE
WHERE AnotherTableField = p_parameter;
end if;
Or even:
甚至：
l_stmt := 'SELECT * FROM your_table WHERE ';
if p_parameter is not null then
l_stmt := l_stmt ||'id = :1';
open v_my_cursor for l_stmt using p_parameter;
else
l_stmt := l_stmt ||'created_date > trunc(sysdate)';
open v_my_cursor for l_stmt;
end if;
So using a ref cursor gives us a lot more control over the final SQL statement which gets executed. The other difference is that, because a ref cursor is a pointer it can be passed between programs. This is very useful for passing data from PL/SQL to other languages, for instance a JDBC result set.
因此，使用 ref 游标可以让我们更好地控制执行的最终 SQL 语句。另一个区别是，因为引用游标是一个指针，它可以在程序之间传递。这对于将数据从 PL/SQL 传递到其他语言（例如 JDBC 结果集）非常有用。
回答by Brian
- Strongly typed cursors can be 'described'.
- If your building an API (a package) you can place your cursor definitions at the specification level and give the client programmer a better sense of what your API does and returns without needing to be aware of the source code.
- Layout/IDE/GUI tools will likely play nicer with a named cursor.
- There is possibly a negligible performance benefit having a known typed cursor; but I wouldn't count on it being anything significant.
- 可以“描述”强类型游标。
- 如果您构建 API（包），您可以将游标定义放在规范级别，让客户端程序员更好地了解您的 API 的作用和返回，而无需了解源代码。
- 布局/IDE/GUI 工具可能会更好地使用命名光标。
- 已知类型的游标可能带来的性能优势可以忽略不计；但我不会指望它有什么重要意义。
