Oracle PL/SQL Cursor

Yusuf SEZER October 11, 2020 ORACLE

This article contains information about Oracle PL/SQL Cursor and example about PL/SQL Cursor.

What is Oracle PL/SQL Cursor?

When the data in a table is listed, we can read the data row by row using the cursor and we can operate on these rows.

By using various values in the cursor, it receives information about whether it is at the end of the record list and whether there is data.

Oracle Cursor Types

There are two types of Cursors;

Implicit
Explicit

Implicit Cursor in PL/SQL

When the database queries run, the cursor created and managed by oracle is called Implicit Cursor.

We can not control this cursor type.

BEGIN
    INSERT ALL
        INTO MYTABLE(COLUMN1, COLUMN2, ...) VALUES('VALUE1', 'VALUE2', ...)
        INTO MYTABLE(COLUMN1, COLUMN2, ...) VALUES('VALUE1', 'VALUE2', ...)
    SELECT * FROM DUAL;
    DBMS_OUTPUT.put_line(SQL%ROWCOUNT);
    --DBMS_OUTPUT.put_line(sql%FOUND);
    --DBMS_OUTPUT.put_line(SQL%NOTFOUND);
    --DBMS_OUTPUT.put_line(SQL%ISOPEN);
END;

BEGIN

INSERT ALL

INTO MYTABLE(COLUMN1, COLUMN2, ...) VALUES('VALUE1', 'VALUE2', ...)

SELECT * FROM DUAL;

DBMS_OUTPUT.put_line(SQL%ROWCOUNT);

--DBMS_OUTPUT.put_line(sql%FOUND);

--DBMS_OUTPUT.put_line(SQL%NOTFOUND);

--DBMS_OUTPUT.put_line(SQL%ISOPEN);

END;

Explicit Cursor in PL/SQL

This is the cursor most of us knows. We can control this cursor type.

If we need the perform something(query execution, calculation) for all the rows of a query result set we can use cursor.

We can define cursor as follows;

CURSOR cursor_name IS query;

1	CURSOR cursor_name IS query;

Sample Cursor Definition;

CURSOR LIST_TABLE IS SELECT * FROM TABLE_NAME;

1	CURSOR LIST_TABLE IS SELECT * FROM TABLE_NAME;

Once the cursor is defined, we open the Cursor with the OPEN keyword.

OPEN cursor_name;

1	OPEN cursor_name;

OPEN LIST_TABLE;

1	OPEN LIST_TABLE;

After opening the cursor , the values are fetched with FETCH keyword.

FETCH cursor_name INTO variable_1, variable_2;

1	FETCH cursor_name INTO variable_1, variable_2;

FETCH LIST_TABLE INTO variable_1, variable_2;

1	FETCH LIST_TABLE INTO variable_1, variable_2;

After the process is completed, we close the Cursor with the CLOSE keyword.

CLOSE cursor_name;

1	CLOSE cursor_name;

Sample Cursor Usage is as follows;

DECLARE
    fırst_name contacts.fırst_name%type;
    last_name contacts.last_name%type;
    email contacts.emaıl%type;
    CURSOR LIST_CURSOR_EXAMPLE IS
        SELECT first_name, last_name, email FROM contacts;
BEGIN
    OPEN LIST_CURSOR_EXAMPLE;

    LOOP
        FETCH LIST_CURSOR_EXAMPLE INTO first_name, last_name, email;
            IF fırst_name = 'Yusuf' THEN
                EXIT;
            END IF;
            DBMS_OUTPUT.put_line(first_name || ' - ' || last_name || ' - ' || email);
        EXIT WHEN LIST_CURSOR_EXAMPLE%NOTFOUND;
    END LOOP;

    CLOSE LIST_CURSOR_EXAMPLE;
END;

DECLARE

fırst_name contacts.fırst_name%type;

last_name contacts.last_name%type;

email contacts.emaıl%type;

CURSOR LIST_CURSOR_EXAMPLE IS

SELECT first_name, last_name, email FROM contacts;

BEGIN

OPEN LIST_CURSOR_EXAMPLE;

LOOP

FETCH LIST_CURSOR_EXAMPLE INTO first_name, last_name, email;

IF fırst_name = 'Yusuf' THEN

EXIT;

END IF;

DBMS_OUTPUT.put_line(first_name || ' - ' || last_name || ' - ' || email);

EXIT WHEN LIST_CURSOR_EXAMPLE%NOTFOUND;

END LOOP;

CLOSE LIST_CURSOR_EXAMPLE;

END;

We created the variables to be used in the DECLARE according to the table data type and a cursor and query with CURSOR.

Then we opened a cursor in BEGIN, we ran the query step by step with LOOP and FETCH.

Using IF in the FETCH block, we compared the value received with the cursor.

At the end of the BEGIN , we returned the memory by closing the cursor.

Thanks to the cursor, we had the opportunity to run PL / SQL commands on the data step by step.

You can find more detailed information about below topics in the below link.

PL/SQL Tutorial

You will find below topics in this article.

You can find more information about cursor at docs.oracle.com

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux