OGB Appreciation Day: Implicit Cursors

Recently I’ve been working with SQL Server and while it’s not all bad, sometimes it does helps to highlight some of the neat features available in Oracle.  One of these is IMPLICIT cursors, which I shall demonstrate.

First I’ll show how to populate some data in a table and then loop over it in TSQL (SQL Server’s equivalent of PL/SQL):

1> CREATE TABLE demo (col1 TINYINT, col2 TINYINT, col3 TINYINT);
2> GO

1> INSERT INTO demo (col1, col2, col3)
2>           VALUES (11,   12,   13),
3>                  (21,   22,   23),
4>                  (31,   32,   33);
5> GO
(3 rows affected)

1>  DECLARE @col1 tinyint, @col2 tinyint, @col3 tinyint;
2>  DECLARE explicit_cursor CURSOR LOCAL FAST_FORWARD FOR
3>     SELECT col1, col2, col3 FROM dbo.demo;
4>
5>  OPEN explicit_cursor;
6>  FETCH NEXT FROM explicit_cursor INTO @col1, @col2, @col3;
7>
8>  WHILE @@FETCH_STATUS = 0
9>  BEGIN
10>    PRINT CONCAT(@col1, ':', @col2, ':', @col3);
11>    FETCH NEXT FROM explicit_cursor INTO @col1, @col2, @col3;
12> END
13>
14> CLOSE explicit_cursor;
15> DEALLOCATE explicit_cursor;
16>
17> GO
11:12:13
21:22:23
31:32:33

By the way, note the neat way it’s possible to insert 3 records with a single INSERT statement. I didn’t say there weren’t some things that SQL Server does a little better 🙂

Next check out the equivalent SQL statements and PL/SQL code in the Oracle Database. Note my Oracle demos are running on an Autonomous Transaction Processing Database in Oracle Cloud although should work in all versions including Oracle XE, the free to use database.

SQL> CREATE TABLE demo (col1 NUMBER, col2 NUMBER, col3 NUMBER);
Table DEMO created.

SQL> INSERT INTO demo (col1, col2, col3) VALUES (11, 12, 13);
1 row inserted.

SQL> INSERT INTO demo (col1, col2, col3) VALUES (21, 22, 23);
1 row inserted.

SQL> INSERT INTO demo (col1, col2, col3) VALUES (31, 32, 33);
1 row inserted.

SQL> COMMIT;
Commit complete.

SQL> SET SERVEROUTPUT ON SIZE UNLIMITED;
SQL>
SQL> DECLARE
  2      CURSOR explicit_cursor IS
  3         SELECT col1, col2, col3 FROM demo;
  4      explicit_record explicit_cursor%ROWTYPE;
  5  BEGIN
  6      OPEN explicit_cursor;
  7      LOOP
  8          FETCH explicit_cursor INTO explicit_record;
  9          EXIT WHEN explicit_cursor%NOTFOUND;
 10          sys.dbms_output.put_line(explicit_record.col1 || ':' ||
 11                                   explicit_record.col2 || ':' ||
 12                                   explicit_record.col3          );
 13      END LOOP;
 14      CLOSE explicit_cursor;
 15  END;
 16  /

11:12:13
21:22:23
31:32:33

PL/SQL procedure successfully completed.

Already I prefer a few things about the Oracle solution.  The ability to use a cursor %ROWTYPE rather than having to define and use variables for individual columns, the fact there is only one fetch command required and the use of the %NOTFOUND cursor attribute rather than the somewhat arbitrary @@FETCHSTATUS == 0 check.

However Oracle offers an even better method, namely an implicit cursor.

SQL> BEGIN
  2      FOR implicit_record IN (SELECT col1, col2, col3 FROM demo)
  3      LOOP
  4          sys.dbms_output.put_line(implicit_record.col1 || ':' ||  
  5                                   implicit_record.col2 || ':' ||  
  6                                   implicit_record.col3          );
  7      END LOOP;
  8  END;
  9  /

11:12:13
21:22:23
31:32:33

PL/SQL procedure successfully completed

A few things to note.  We’re down from 15 to 8 lines of code which makes this easier to write, and just as importantly with less chance of bugs.  No need to worry about defining rowtypes, or opening or closing cursors, Oracle just does the right thing under the covers including tidying up in case exceptions are thrown.