<p>I am a bit rusty on my cursor lingo in PL/SQL. Anyone know this?</p>

[{'answer_id': 74105, 'author': 'Dave Costa', 'author_id': 6568, 'author_profile': 'https://Stackoverflow.com/users/6568', 'pm_score': 2, 'selected': False, 'text': '<p>An explicit cursor is one you declare, like:</p>\n\n<pre><code>CURSOR my_cursor IS\n  SELECT table_name FROM USER_TABLES\n</code></pre>\n\n<p>An implicit cursor is one created to support any in-line SQL you write (either static or dynamic).</p>\n'}, {'answer_id': 74135, 'author': 'stjohnroe', 'author_id': 2985, 'author_profile': 'https://Stackoverflow.com/users/2985', 'pm_score': 4, 'selected': False, 'text': '<p>An explicit cursor is defined as such in a declaration block:</p>\n\n<pre><code>DECLARE \nCURSOR cur IS \n  SELECT columns FROM table WHERE condition;\nBEGIN\n...\n</code></pre>\n\n<p>an implicit cursor is implented directly in a code block:</p>\n\n<pre><code>...\nBEGIN\n   SELECT columns INTO variables FROM table where condition;\nEND;\n...\n</code></pre>\n'}, {'answer_id': 74624, 'author': 'Kristian', 'author_id': 12911, 'author_profile': 'https://Stackoverflow.com/users/12911', 'pm_score': 2, 'selected': False, 'text': '<p>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.</p>\n\n<p>See <a href="http://www.unix.com.ua/orelly/oracle/prog2/ch06_03.htm" rel="nofollow noreferrer">http://www.unix.com.ua/orelly/oracle/prog2/ch06_03.htm</a> for details.</p>\n'}, {'answer_id': 76178, 'author': 'Sten Vesterli', 'author_id': 9363, 'author_profile': 'https://Stackoverflow.com/users/9363', 'pm_score': 6, 'selected': True, 'text': '<p>An implicit cursor is one created "automatically" for you by Oracle when you execute a query. It is simpler to code, but suffers from </p>\n\n<ul>\n<li>inefficiency (the ANSI standard specifies that it must fetch twice to check if there is more than one record)</li>\n<li>vulnerability to data errors (if you ever get two rows, it raises a TOO_MANY_ROWS exception)</li>\n</ul>\n\n<p>Example</p>\n\n<pre><code>SELECT col INTO var FROM table WHERE something;\n</code></pre>\n\n<p>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. </p>\n\n<p>Example</p>\n\n<pre><code>DECLARE   \n  CURSOR cur IS SELECT col FROM table WHERE something; \nBEGIN\n  OPEN cur;\n  FETCH cur INTO var;\n  CLOSE cur;\nEND;\n</code></pre>\n'}, {'answer_id': 78765, 'author': 'ropable', 'author_id': 14508, 'author_profile': 'https://Stackoverflow.com/users/14508', 'pm_score': 0, 'selected': False, 'text': '<p>Every SQL statement executed by the Oracle database has a cursor associated with it, which is a private work area to store processing information. Implicit cursors are implicitly created by the Oracle server for all DML and SELECT statements.</p>\n\n<p>You can declare and use Explicit cursors to name the private work area, and access its stored information in your program block.</p>\n'}, {'answer_id': 80246, 'author': 'Ethan Post', 'author_id': 4527, 'author_profile': 'https://Stackoverflow.com/users/4527', 'pm_score': -1, 'selected': False, 'text': "<p>Explicit...</p>\n\n<p>cursor foo is select * from blah;\nbegin\n   open fetch exit when close cursor yada yada yada</p>\n\n<p>don't use them, use implicit</p>\n\n<p>cursor foo is select * from blah;</p>\n\n<p>for n in foo loop\n   x = n.some_column\nend loop</p>\n\n<p>I think you can even do this</p>\n\n<p>for n in (select * from blah) loop...</p>\n\n<p>Stick to implicit, they close themselves, they are more readable, they make life easy.</p>\n"}, {'answer_id': 84014, 'author': 'pablo', 'author_id': 16112, 'author_profile': 'https://Stackoverflow.com/users/16112', 'pm_score': 2, 'selected': False, 'text': '<p>These days implicit cursors are more efficient than explicit cursors.</p>\n\n<p><a href="http://www.oracle.com/technology/oramag/oracle/04-sep/o54plsql.html" rel="nofollow noreferrer">http://www.oracle.com/technology/oramag/oracle/04-sep/o54plsql.html</a></p>\n\n<p><a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1205168148688" rel="nofollow noreferrer">http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1205168148688</a></p>\n'}, {'answer_id': 2824542, 'author': 'Derek Swingley', 'author_id': 1934, 'author_profile': 'https://Stackoverflow.com/users/1934', 'pm_score': 1, 'selected': False, 'text': '<p>Google is your friend:  <a href="http://docstore.mik.ua/orelly/oracle/prog2/ch06_03.htm" rel="nofollow noreferrer">http://docstore.mik.ua/orelly/oracle/prog2/ch06_03.htm</a></p>\n\n<blockquote>\n  <p>PL/SQL issues an implicit cursor\n  whenever you execute a SQL statement\n  directly in your code, as long as that\n  code does not employ an explicit\n  cursor. It is called an "implicit"\n  cursor because you, the developer, do\n  not explicitly declare a cursor for\n  the SQL statement.</p>\n  \n  <p>An explicit cursor is a SELECT\n  statement that is explicitly defined\n  in the declaration section of your\n  code and, in the process, assigned a\n  name. There is no such thing as an\n  explicit cursor for UPDATE, DELETE,\n  and INSERT statements.</p>\n</blockquote>\n'}, {'answer_id': 2825689, 'author': 'Ian Carpenter', 'author_id': 55640, 'author_profile': 'https://Stackoverflow.com/users/55640', 'pm_score': 2, 'selected': False, 'text': '<p>In answer to the first question. Straight from the Oracle <a href="http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/static.htm#LNPLS00602" rel="nofollow noreferrer">documentation</a></p>\n\n<blockquote>\n  <p>A cursor is a pointer to a private SQL\n  area that stores information about\n  processing a specific SELECT or DML\n  statement.</p>\n</blockquote>\n'}, {'answer_id': 2833203, 'author': 'UltraCommit', 'author_id': 297267, 'author_profile': 'https://Stackoverflow.com/users/297267', 'pm_score': 1, 'selected': False, 'text': "<p>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:</p>\n\n<pre><code>BEGIN\n   DECLARE\n      CURSOR C1\n      IS\n         SELECT DROPPED_CALLS FROM ALARM_UMTS;\n\n      C1_REC   C1%ROWTYPE;\n   BEGIN\n      FOR C1_REC IN C1\n      LOOP\n         DBMS_OUTPUT.PUT_LINE ('DROPPED CALLS: ' || C1_REC.DROPPED_CALLS);\n      END LOOP;\n   END;\nEND;\n/\n</code></pre>\n\n<p>With FOR ... LOOP... END LOOP you open and close the cursor authomatically, when the records of the cursor have been all analyzed.</p>\n\n<p>An example of explicit cursor is the following:</p>\n\n<pre><code>BEGIN\n   DECLARE\n      CURSOR C1\n      IS\n         SELECT DROPPED_CALLS FROM ALARM_UMTS;\n\n      C1_REC   C1%ROWTYPE;\n   BEGIN\n      OPEN c1;\n\n      LOOP\n         FETCH c1 INTO c1_rec;\n\n         EXIT WHEN c1%NOTFOUND;\n\n         DBMS_OUTPUT.PUT_LINE ('DROPPED CALLS: ' || C1_REC.DROPPED_CALLS);\n      END LOOP;\n\n      CLOSE c1;\n   END;\nEND;\n/\n</code></pre>\n\n<p>In the explicit cursor you open and close the cursor in an explicit way, checking the presence of records and stating an exit condition.</p>\n"}, {'answer_id': 3138202, 'author': 'shaiksyedbasha', 'author_id': 378667, 'author_profile': 'https://Stackoverflow.com/users/378667', 'pm_score': 1, 'selected': False, 'text': '<p>Implicit cursor returns only one record and are called automatically. However, explicit cursors are called manually and can return more than one record.</p>\n'}, {'answer_id': 9258147, 'author': 'Ganesh Pathare', 'author_id': 1206438, 'author_profile': 'https://Stackoverflow.com/users/1206438', 'pm_score': 2, 'selected': False, 'text': "<p>1.CURSOR: When PLSQL issues sql statements it creates private work area\n   to parse &amp; execute the sql statement is called cursor.</p>\n\n<p>2.IMPLICIT: When any PL/SQLexecutable block issues sql statement.\n   PL/SQL creates implicit cursor and manages automatically means\n   implcit open &amp; close takes place. It used when sql statement return\n   only one row.It has 4 attributes SQL%ROWCOUNT, SQL%FOUND,\n   SQL%NOTFOUND, SQL%ISOPEN.</p>\n\n<p>3.EXPLICIT: It is created &amp; managed by the programmer. It needs every\n   time explicit open,fetch &amp; close. It is used when sql statement\n   returns more than one row. It has also 4 attributes\n   CUR_NAME%ROWCOUNT, CUR_NAME%FOUND, CUR_NAME%NOTFOUND, \n   CUR_NAME%ISOPEN. It process several rows by using loop. \n   The programmer can pass the parameter too to explicit cursor.</p>\n\n<ul>\n<li>Example: Explicit Cursor</li>\n</ul>\n\n<p>&nbsp;</p>\n\n<pre><code>declare \n   cursor emp_cursor \n   is \n   select id,name,salary,dept_id \n   from employees; \n   v_id employees.id%type; \n   v_name employees.name%type; \n   v_salary employees.salary%type; \n   v_dept_id employees.dept_id%type; \n   begin \n   open emp_cursor; \n   loop \n   fetch emp_cursor into v_id,v_name,v_salary,v_dept_id; \n   exit when emp_cursor%notfound;\n   dbms_output.put_line(v_id||', '||v_name||', '||v_salary||','||v_dept_id); \n   end loop;                    \n   close emp_cursor; \n   end;\n</code></pre>\n"}, {'answer_id': 11323942, 'author': 'prince', 'author_id': 1500760, 'author_profile': 'https://Stackoverflow.com/users/1500760', 'pm_score': 2, 'selected': False, 'text': '<p>Implicit cursors require anonymous buffer memory. </p>\n\n<p>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.</p>\n'}, {'answer_id': 28938170, 'author': 'Lalit Kumar B', 'author_id': 3989608, 'author_profile': 'https://Stackoverflow.com/users/3989608', 'pm_score': 2, 'selected': False, 'text': '<p><strong>From a performance point of view, Implicit cursors are faster.</strong></p>\n<p>Let\'s compare the performance between an explicit and implicit cursor:</p>\n<pre><code>SQL&gt; DECLARE\n  2    l_loops  NUMBER := 100000;\n  3    l_dummy  dual.dummy%TYPE;\n  4    l_start  NUMBER;\n  5    -- explicit cursor declaration\n  6    CURSOR c_dual IS\n  7      SELECT dummy\n  8      FROM   dual;\n  9  BEGIN\n 10    l_start := DBMS_UTILITY.get_time;\n 11    -- explicitly open, fetch and close the cursor\n 12    FOR i IN 1 .. l_loops LOOP\n 13      OPEN  c_dual;\n 14      FETCH c_dual\n 15      INTO  l_dummy;\n 16      CLOSE c_dual;\n 17    END LOOP;\n 18\n 19    DBMS_OUTPUT.put_line(\'Explicit: \' ||\n 20                         (DBMS_UTILITY.get_time - l_start) || \' hsecs\');\n 21\n 22    l_start := DBMS_UTILITY.get_time;\n 23    -- implicit cursor for loop\n 24    FOR i IN 1 .. l_loops LOOP\n 25      SELECT dummy\n 26      INTO   l_dummy\n 27      FROM   dual;\n 28    END LOOP;\n 29\n 30    DBMS_OUTPUT.put_line(\'Implicit: \' ||\n 31                         (DBMS_UTILITY.get_time - l_start) || \' hsecs\');\n 32  END;\n 33  /\nExplicit: 332 hsecs\nImplicit: 176 hsecs\n\nPL/SQL procedure successfully completed.\n</code></pre>\n<p>So, a significant difference is clearly visible. Implicit cursor is much faster than an explicit cursor.</p>\n<p>More examples <a href="http://oracle-base.com/articles/misc/implicit-vs-explicit-cursors-in-oracle-plsql.php" rel="nofollow noreferrer">here</a>.</p>\n'}, {'answer_id': 47336170, 'author': 'Vadzim', 'author_id': 603516, 'author_profile': 'https://Stackoverflow.com/users/603516', 'pm_score': 1, 'selected': False, 'text': '<p>As stated in other answers, implicit cursors are easier to use and less error-prone. </p>\n\n<p>And <a href="https://oracle-base.com/articles/misc/implicit-vs-explicit-cursors-in-oracle-plsql" rel="nofollow noreferrer">Implicit vs. Explicit Cursors in Oracle PL/SQL</a> shows that implicit cursors are up to two times faster than explicit ones too.</p>\n\n<p>It\'s strange that no one had yet mentioned <a href="https://docs.oracle.com/cloud/latest/db112/LNPLS/cursor_for_loop_statement.htm#LNPLS1155" rel="nofollow noreferrer">Implicit FOR LOOP Cursor</a>:</p>\n\n<pre><code>begin\n  for cur in (\n    select t.id from parent_trx pt inner join trx t on pt.nested_id = t.id\n    where t.started_at &gt; sysdate - 31 and t.finished_at is null and t.extended_code is null\n  )\n  loop\n    update trx set finished_at=sysdate, extended_code = -1 where id = cur.id;\n    update parent_trx set result_code = -1 where nested_id = cur.id;\n  end loop cur;\nend;\n</code></pre>\n\n<p>Another example on SO: <a href="https://stackoverflow.com/questions/15854749/pl-sql-for-loop-implicit-cursor">PL/SQL FOR LOOP IMPLICIT CURSOR</a>.</p>\n\n<p>It\'s way more shorter than explicit form.</p>\n\n<p>This also provides a nice workaround for <a href="https://stackoverflow.com/questions/15113086/postgresql-and-oracle-update-multiple-tables-from-common-subquery/47335611#47335611">updating multiple tables from CTE</a>.</p>\n'}, {'answer_id': 55813639, 'author': 'GOVIND DIXIT', 'author_id': 8549281, 'author_profile': 'https://Stackoverflow.com/users/8549281', 'pm_score': 1, 'selected': False, 'text': '<p>In PL/SQL, A cursor is a pointer to this context area. It contains all the information needed for processing the statement. </p>\n\n<p><strong>Implicit Cursors:</strong>\nImplicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.</p>\n\n<p><strong>Explicit Cursors:</strong>\nExplicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.</p>\n\n<p>The syntax for creating an explicit cursor is:</p>\n\n<pre><code>CURSOR cursor_name IS select_statement; \n</code></pre>\n'}]
['https://Stackoverflow.com/questions/74010', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3208/']