Oracle 中的显式游标和隐式游标有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/74010/
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
What is the difference between explicit and implicit cursors in Oracle?
提问by Brian G
I am a bit rusty on my cursor lingo in PL/SQL. Anyone know this?
我对 PL/SQL 中的游标术语有点生疏。有人知道这个吗？
回答by Sten Vesterli
An implicit cursor is one created "automatically" for you by Oracle when you execute a query. It is simpler to code, but suffers from
隐式游标是 Oracle 在您执行查询时“自动”为您创建的游标。编码更简单，但受苦于
- inefficiency (the ANSI standard specifies that it must fetch twice to check if there is more than one record)
- vulnerability to data errors (if you ever get two rows, it raises a TOO_MANY_ROWS exception)
- 效率低下（ANSI 标准规定它必须取两次以检查是否有多个记录）
- 数据错误的漏洞（如果你得到两行，它会引发一个 TOO_MANY_ROWS 异常）
Example
例子
SELECT col INTO var FROM table WHERE something;
An explicit cursor is one you create yourself. It takes more code, but gives more control - for example, you can just open-fetch-close if you only want the first record and don't care if there are others.
显式游标是您自己创建的游标。它需要更多的代码，但提供更多的控制权 - 例如，如果您只想要第一条记录并且不关心是否有其他记录，您可以只打开-获取-关闭。
Example
例子
DECLARE
CURSOR cur IS SELECT col FROM table WHERE something;
BEGIN
OPEN cur;
FETCH cur INTO var;
CLOSE cur;
END;
回答by stjohnroe
An explicit cursor is defined as such in a declaration block:
显式游标在声明块中是这样定义的：
DECLARE
CURSOR cur IS
SELECT columns FROM table WHERE condition;
BEGIN
...
an implicit cursor is implented directly in a code block:
隐式游标直接在代码块中实现：
...
BEGIN
SELECT columns INTO variables FROM table where condition;
END;
...
回答by Ganesh Pathare
1.CURSOR: When PLSQL issues sql statements it creates private work area to parse & execute the sql statement is called cursor.
1.CURSOR：当PLSQL发出sql语句时，它会创建私有工作区来解析和执行sql语句，称为游标。
2.IMPLICIT: When any PL/SQLexecutable block issues sql statement. PL/SQL creates implicit cursor and manages automatically means implcit open & close takes place. It used when sql statement return only one row.It has 4 attributes SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN.
2.IMPLICIT：当任何PL/SQL可执行块发出sql语句时。PL/SQL 创建隐式游标并自动管理意味着隐式打开和关闭发生。当sql语句只返回一行时使用。它有4个属性SQL%ROWCOUNT、SQL%FOUND、SQL%NOTFOUND、SQL%ISOPEN。
3.EXPLICIT: It is created & managed by the programmer. It needs every time explicit open,fetch & close. It is used when sql statement returns more than one row. It has also 4 attributes CUR_NAME%ROWCOUNT, CUR_NAME%FOUND, CUR_NAME%NOTFOUND, CUR_NAME%ISOPEN. It process several rows by using loop. The programmer can pass the parameter too to explicit cursor.
3.EXPLICIT：由程序员创建和管理。它需要每次显式打开、获取和关闭。当 sql 语句返回多于一行时使用。它还有 4 个属性 CUR_NAME%ROWCOUNT、CUR_NAME%FOUND、CUR_NAME%NOTFOUND、CUR_NAME%ISOPEN。它使用循环处理多行。程序员也可以将参数传递给显式游标。
- Example: Explicit Cursor
- 示例：显式光标
declare
cursor emp_cursor
is
select id,name,salary,dept_id
from employees;
v_id employees.id%type;
v_name employees.name%type;
v_salary employees.salary%type;
v_dept_id employees.dept_id%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_id,v_name,v_salary,v_dept_id;
exit when emp_cursor%notfound;
dbms_output.put_line(v_id||', '||v_name||', '||v_salary||','||v_dept_id);
end loop;
close emp_cursor;
end;
回答by prince
Implicit cursors require anonymous buffer memory.
隐式游标需要匿名缓冲内存。
Explicit cursors can be executed again and again by using their name.They are stored in user defined memory space rather than being stored in an anonymous buffer memory and hence can be easily accessed afterwards.
显式游标可以通过使用它们的名称一次又一次地执行。它们存储在用户定义的内存空间中，而不是存储在匿名缓冲区中，因此以后可以轻松访问。
回答by Ian Carpenter
In answer to the first question. Straight from the Oracle documentation
回答第一个问题。直接来自 Oracle文档
A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.
游标是指向私有 SQL 区域的指针，该区域存储有关处理特定 SELECT 或 DML 语句的信息。
回答by Dave Costa
An explicit cursor is one you declare, like:
显式游标是您声明的游标，例如：
CURSOR my_cursor IS
SELECT table_name FROM USER_TABLES
An implicit cursor is one created to support any in-line SQL you write (either static or dynamic).
隐式游标是为支持您编写的任何内联 SQL（静态或动态）而创建的。
回答by pablo
These days implicit cursors are more efficient than explicit cursors.
如今，隐式游标比显式游标更有效。
http://www.oracle.com/technology/oramag/oracle/04-sep/o54plsql.html
http://www.oracle.com/technology/oramag/oracle/04-sep/o54plsql.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1205168148688
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1205168148688
回答by Kristian
With explicit cursors, you have complete control over how to access information in the database. You decide when to OPEN the cursor, when to FETCH records from the cursor (and therefore from the table or tables in the SELECT statement of the cursor) how many records to fetch, and when to CLOSE the cursor. Information about the current state of your cursor is available through examination of the cursor attributes.
使用显式游标，您可以完全控制如何访问数据库中的信息。您决定何时打开游标，何时从游标（因此从游标的 SELECT 语句中的一个或多个表中）获取记录，以及何时关闭游标。通过检查光标属性可以获得有关光标当前状态的信息。
See http://www.unix.com.ua/orelly/oracle/prog2/ch06_03.htmfor details.
有关详细信息，请参阅http://www.unix.com.ua/orelly/oracle/prog2/ch06_03.htm。
回答by Lalit Kumar B
I know this is an old question, however, I think it would be good to add a practical example to show the difference between the two from a performance point of view.
我知道这是一个老问题，但是，我认为添加一个实际示例来从性能的角度显示两者之间的区别会很好。
From a performance point of view, Implicit cursors are faster.
从性能的角度来看，隐式游标更快。
Let's see the performance difference between the two:
让我们看看两者之间的性能差异：
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_loops NUMBER := 100000;
3 l_dummy dual.dummy%TYPE;
4 l_start NUMBER;
5
6 CURSOR c_dual IS
7 SELECT dummy
8 FROM dual;
9 BEGIN
10 l_start := DBMS_UTILITY.get_time;
11
12 FOR i IN 1 .. l_loops LOOP
13 OPEN c_dual;
14 FETCH c_dual
15 INTO l_dummy;
16 CLOSE c_dual;
17 END LOOP;
18
19 DBMS_OUTPUT.put_line('Explicit: ' ||
20 (DBMS_UTILITY.get_time - l_start) || ' hsecs');
21
22 l_start := DBMS_UTILITY.get_time;
23
24 FOR i IN 1 .. l_loops LOOP
25 SELECT dummy
26 INTO l_dummy
27 FROM dual;
28 END LOOP;
29
30 DBMS_OUTPUT.put_line('Implicit: ' ||
31 (DBMS_UTILITY.get_time - l_start) || ' hsecs');
32 END;
33 /
Explicit: 332 hsecs
Implicit: 176 hsecs
PL/SQL procedure successfully completed.
So, a significant difference is clearly visible.
因此，可以清楚地看到显着差异。
More examples here.
更多例子在这里。
回答by UltraCommit
A cursor is a SELECTed window on an Oracle table, this means a group of records present in an Oracle table, and satisfying certain conditions. A cursor can SELECT all the content of a table, too. With a cursor you can manipulate Oracle columns, aliasing them in the result. An example of implicit cursor is the following:
游标是 Oracle 表上的 SELECTed 窗口，这意味着 Oracle 表中存在的一组记录，并且满足某些条件。游标也可以选择表的所有内容。使用游标，您可以操作 Oracle 列，在结果中为它们设置别名。隐式游标的示例如下：
BEGIN
DECLARE
CURSOR C1
IS
SELECT DROPPED_CALLS FROM ALARM_UMTS;
C1_REC C1%ROWTYPE;
BEGIN
FOR C1_REC IN C1
LOOP
DBMS_OUTPUT.PUT_LINE ('DROPPED CALLS: ' || C1_REC.DROPPED_CALLS);
END LOOP;
END;
END;
/
With FOR ... LOOP... END LOOP you open and close the cursor authomatically, when the records of the cursor have been all analyzed.
使用 FOR ... LOOP... END LOOP 自动打开和关闭游标，当游标的记录全部分析完毕。
An example of explicit cursor is the following:
显式游标的示例如下：
BEGIN
DECLARE
CURSOR C1
IS
SELECT DROPPED_CALLS FROM ALARM_UMTS;
C1_REC C1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO c1_rec;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('DROPPED CALLS: ' || C1_REC.DROPPED_CALLS);
END LOOP;
CLOSE c1;
END;
END;
/
In the explicit cursor you open and close the cursor in an explicit way, checking the presence of records and stating an exit condition.
在显式游标中，您以显式方式打开和关闭游标，检查记录是否存在并声明退出条件。
