Syntax for create cursor with open fetch statement in Oracle PL/SQL

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:
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.