Syntax for Creating cursor in Oracle PL/SQL
Cursor
A cursor is a pointer to a private SQL area with metadata for running a select query or DML statements.
Two types of Cursors:
Explicit Cursor: Defined the cursor on SQL statement to processed row one by one.
Implicit Cursor: Run SQL statement without declaration like Select, Insert statement etc.
Syntax of Cursor:
DECLARE
CURSOR <cursor_name> IS <SELECT statement>
<variable> <declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;
END;
Declare cursor in three ways in Declare section of PL/SQL block:
-- Simple cursor
CURSOR cursor_name IS SELECT statement;
-- Parameter cursor
CURSOR cursor_name (<parameter_name> <datatype>) IS SELECT statement;
--Cursor with return clause
CURSOR cursor_name RETURN field%ROWTYPE IS SELECT statement;
Example using OPEN FETCH syntax for Cursor:
SET SERVEROUTPUT ON
DECLARE
CURSOR cur_emp IS SELECT first_name FROM employees;
v_emp_name employees.emp_name%type;
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp INTO lv_emp_name;
IF cur_emp%NOTFOUND
THEN
EXIT;
END IF;
Dbms_output.put_line('Employee Fetched:'||lv_emp_name);
END LOOP;
CLOSE cur_emp;
END:
/