oracle
  1. oracle-cursor
PreviousNext

Cursor - ( Oracle Advance )

In Oracle, a cursor is a pointer or reference to a private SQL area in the database. Cursors are used to retrieve and manipulate data from the result set of a query. Cursors are mainly used in database procedures, functions, and triggers.

Syntax

The basic syntax for declaring a cursor in Oracle is as follows:

DECLARE
    cursor_name    CURSOR IS
    SELECT_statement;
BEGIN
    -- code to process cursor
END;

Here, cursor_name is the name of the cursor, SELECT_statement is the SELECT statement used to retrieve data from the database, and the code inside the BEGIN and END block is used to process the data retrieved by the cursor.

Example

DECLARE
    cursor_emp IS
    SELECT EMPLOYEE_ID, LAST_NAME, SALARY
    FROM EMPLOYEES
    ORDER BY SALARY DESC;
    emp_id EMPLOYEES.EMPLOYEE_ID%TYPE;
    last_name EMPLOYEES.LAST_NAME%TYPE;
    salary EMPLOYEES.SALARY%TYPE;
BEGIN
    OPEN cursor_emp;
    LOOP
        FETCH cursor_emp INTO emp_id, last_name, salary;
        EXIT WHEN cursor_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(emp_id || ' - ' || last_name || ' - ' || salary);
    END LOOP;
    CLOSE cursor_emp;
END;

Output

The above code will output a list of all employees in descending order of their salary:

100 - King - 24000
101 - Kochhar - 17000
102 - De Haan - 17000
145 - Abel - 11000
146 - Hutton - 8800
201 - Hartstein - 13000
 ...

Explanation

In the above example, we have declared a cursor called cursor_emp that retrieves the EMPLOYEE_ID, LAST_NAME, and SALARY fields from the EMPLOYEES table. We have then used a loop to fetch each record from the cursor and output the EMPLOYEE_ID, LAST_NAME, and SALARY fields to the console using the DBMS_OUTPUT.PUT_LINE() procedure.

Use

Cursors are mainly used in database procedures, functions, and triggers to declare an explicit pointer to the resultset returned by a SELECT statement. They are used to process data returned from a query one row at a time.

Important Points

  • Cursors are used to retrieve and manipulate data from the result set of a query.
  • Cursors are mainly used in database procedures, functions, and triggers.
  • A cursor is a pointer or reference to a private SQL area in the database.

Summary

In summary, a cursor in Oracle is a pointer or reference to a private SQL area in the database. Cursors are used to retrieve and manipulate data from the result set of a query and are mainly used in database procedures, functions, and triggers. Cursors allow you to process data returned from a query one row at a time, which can be useful in certain situations.

FunctionTrigger
Published on: