PL/SQL Cursors For Loop

PL/SQL cursor FOR loop has one great advantage of loop continued until row not found. In sometime you require to use explicit cursor with FOR loop instead of use OPEN, FETCH, and CLOSE statement.
FOR loop iterate repeatedly and fetches rows of values from database until row not found.

Explicit Cursor FOR LOOP Example

following one emp_information table:

EMP_NO	EMP_NAME	EMP_DEPT	EMP_SALARY
1	Forbs ross	Web Developer	45k
2	marks jems	Program Developer	38k
3	Saulin	Program Developer	34k
4	Zenia Sroll	Web Developer	42k

Display employee number wise first two employee details emp,

Example Code

cursor_for_loop.sql

SQL>set serveroutput on
SQL>edit cursor_for_loop
DECLARE
 cursor c is select * from emp_information
 where emp_no <=2;
 tmp emp_information%rowtype;
BEGIN 
 OPEN c;
 FOR tmp IN c LOOP
 FETCH c into tmp;
 dbms_output.put_line('EMP_No:    '||tmp.emp_no);
 dbms_output.put_line('EMP_Name:  '||tmp.emp_name);
 dbms_output.put_line('EMP_Dept:  '||tmp.emp_dept);
 dbms_output.put_line('EMP_Salary:'||tmp.emp_salary);   
 END Loop;
CLOSE c;
END; 
/

Example Result

SQL>@cursor_for_loop
EMP_No:    1
EMP_Name:  Forbs ross
EMP_Dept:  Web Developer
EMP_Salary:45k
EMP_No:    2
EMP_Name:  marks jems
EMP_Dept:  Program Developer
EMP_Salary:38k

PL/SQL procedure successfully completed.

PL/SQL Explicit Cursor

Explicit Cursor which are construct/manage by user itself call explicit cursor.

User itself to declare the cursor, open cursor to reserve the memory and populate data, fetch the records from the active data set one at a time, apply logic and last close the cursor.

You can not directly assign value to an explicit cursor variable you have to use expression or create subprogram for assign value to explicit cursor variable.

Step for Using Explicit Cursor

1. Declare cursor
   Declare explicit cursor has this syntax,

   CURSOR cursor_name [ parameter ] RETURN return_type;

   CURSOR cursor_name [ parameter ] [ RETURN return_type ]
      IS SELECT STATEMENT;

   Declaring explicit cursor example,

   CURSOR c RETURN EMP_DEPT%ROWTYPE;    -- Declare c
   
   CURSOR c IS                           -- Define c,
       SELECT * FROM emp_information;       -- all row return type
   
   CURSOR c RETURN EMP_DEPT%ROWTYPE IS    -- Define c,
       SELECT * FROM emp_information;       -- repeating return type 

2. Opening Explicit Cursor
   DECLARE block you are already declare CURSOR now you can OPEN CURSOR by using following way, and allocate some reserve area for process database query.

   OPEN cursor_name [( cursor_parameter )];

3. Loop
   Loop iterate until ROW not found. Once found loop exit control goes next statement (outside loop).
4. Fetching data from cursor
   Using FETCH statement you can fetch CURSOR data into explicit variable.

   FETCH cursor_name INTO variable;

5. Exit loop
6. Closing Explicit Cursor
   This way you can close opened CURSOR.

   CLOSE cursor_name [( cursor_parameter )];

Following emp_information table having employee information, now we update information using Explicit Cursor,

EMP_NO	EMP_NAME	EMP_DEPT	EMP_SALARY
1	Forbs ross	Web Developer	45k
2	marks jems	Program Developer	38k
3	Saulin	Program Developer	34k
4	Zenia Sroll	Web Developer	42k

Now above employee information table update the employee name 'Saulin' department 'Program Developer' update to 'Web Developer'.

Example Code

explicit_cursor.sql

SQL>set serveroutput on
SQL>edit explicit_cursor
DECLARE
 cursor c is select * from emp_information
 where emp_name='bhavesh';
 tmp emp_information%rowtype;
BEGIN 
 OPEN c;
 Loop exit when c%NOTFOUND;
  FETCH c into tmp;
  update emp_information set tmp.emp_dept='Web Developer'
  where tmp.emp_name='Saulin';
 END Loop;
IF c%ROWCOUNT>0 THEN
 dbms_output.put_line(SQL%ROWCOUNT||' Rows Updated');
ELSE
 dbms_output.put_line('NO Rows Updated Found');
END IF;
CLOSE c;
END; 
/

Example Result

SQL>@explicit_cursor
1 Rows Updated

PL/SQL procedure successfully completed.

PL/SQL Implicit Cursor

Oracle uses implicit cursors for its internal processing. Even if we execute a SELECT statement or DML statement Oracle reserves a private SQL area in memory called cursor.

Implicit cursor scope you can get information from cursor by using session attributes until another SELECT statement or DML statement execute.

Implicit Cursor Attributes

Following are implicit cursor attributes,

Cursor Attribute	Cursor Variable	Description
%ISOPEN	SQL%ISOPEN	Oracle engine automatically open the cursor If cursor open return TRUE otherwise return FALSE.
%FOUND	SQL%FOUND	If SELECT statement return one or more rows or DML statement (INSERT, UPDATE, DELETE) affect one or more rows If affect return TRUE otherwise return FALSE. If not execute SELECT or DML statement return NULL.
%NOTFOUND	SQL%NOTFOUND	If SELECT INTO statement return no rows and fire no_data_found PL/SQL exception before you can check SQL%NOTFOUND. If not affect the row return TRUE otherwise return FALSE.
%ROWCOUNT	SQL%ROWCOUNT	Return the number of rows affected by a SELECT statement or DML statement (insert, update, delete). If not execute SELECT or DML statement return NULL.

Syntax

cursor_attribute ::=
 { 
  cursor_name | 
  cursor_variable_name | 
  :host_cursor_variable_name
 }
 % {FOUND | ISOPEN | NOTFOUND | ROWCOUNT}

Explanation :

cursor_name : cursor_name identifies the current scope which are previously declared.

cursor_variable_name : cursor variable or parameter identifies the current scope which are previously declared.

host_cursor_variable_name : host_cursor_variable_name must be prefixed with a colon. Host cursor variable datatype must be compatible with the PL/SQL cursor variable.

Implicit Cursor Example

Following one emp_information table:

EMP_NO	EMP_NAME	EMP_DEPT	EMP_SALARY
1	Forbs ross	Web Developer	45k
2	marks jems	Program Developer	38k
3	Saulin	Program Developer	34k
4	Zenia Sroll	Web Developer	42k

Now above employee information table update the employee name 'Saulin' department 'Program Developer' update to 'Web Developer'.

Example Code

implicit_cursor.sql

SQL>set serveroutput on
SQL>edit implicit_cursor
BEGIN
 UPDATE emp_information SET emp_dept='Web Developer'
  WHERE emp_name='Saulin';

 IF SQL%FOUND THEN
  dbms_output.put_line('Updated - If Found');
 END IF;

 IF SQL%NOTFOUND THEN
  dbms_output.put_line('NOT Updated - If NOT Found');
 END IF; 

 IF SQL%ROWCOUNT>0 THEN
  dbms_output.put_line(SQL%ROWCOUNT||' Rows Updated');
 ELSE
  dbms_output.put_line('NO Rows Updated Found');
 END;
/

Example Result

SQL>@implicit_cursor
Updated - If Found
1 Rows Updated

PL/SQL procedure successfully operation.

PL/SQL Cursors (Implicit, Explicit, For Loop, Parameterized Cursor)

What is Cursor?

Cursor is the work area which Oracle reserves for internal processing of SQL statements. This work area is private for oracles reserved are called cursor.

Cursor area also saying session cursor. because session cursor store information until the session end. Both way you can manage session cursor either implicit cursor or explicit cursor.

Using procedural statement you can get any information using session attribute.

How to Use Cursor

In PL/SQL block SELECT statement can not return more than one row at a time. So Cursor use to some group of rows (more than one row) for implementing certain logic to get all the group of records.

Classification of CURSORS

Cursors can be classified as:

1. Implicit Cursor or Internal Cursor : Manage for Oracle itself or internal process itself.
2. Explicit Cursor or User-defined Cursor : Manage for user/programmer or external processing.

Implicit Cursor

Oracle uses implicit cursors for its internal processing. Even if we execute a SELECT statement or DML statement Oracle reserves a private SQL area in memory called cursor.

Implicit cursor scope you can get information from cursor by using session attributes until another SELECT statement or DML statement execute. Read More...

Explicit Cursor

Explicit Cursor which are construct/manage by user itself call explicit cursor.

User itself to declare the cursor, open cursor to reserve the memory and populate data, fetch the records from the active data set one at a time, apply logic and last close the cursor.

You can not directly assign value to an explicit cursor variable you have to use expression or create subprogram for assign value to explicit cursor variable.

Step for Using Explicit Cursor :

1. Declare cursor
2. Open cursor
3. Loop
4. Fetch data from cursor
5. Exit loop
6. Close cursor

PL/SQL Case Statement

PL/SQL CASE statement comparing one by one sequencing conditions. CASE statement attempt to match expression that is specified in one or more WHEN condition. CASE statement are following two forms,

1. Simple CASE Statement
2. Searched CASE Statement

Simple CASE Statement

PL/SQL simple CASE statement evaluates selector and attempt to match one or more WHEN condition.

Syntax

CASE selector
 WHEN value-1 
  THEN statement-1;
 WHEN value-2
  THEN statement-2;
 ELSE
  statement-3;
END CASE

Example Code

SQL>DECLARE
 a number := 7;
BEGIN
 CASE a 
  WHEN 1 THEN
   DBMS_OUTPUT.PUT_LINE('value 1'); 
  WHEN 2 THEN
   DBMS_OUTPUT.PUT_LINE('value 2'); 
  WHEN 3 THEN
   DBMS_OUTPUT.PUT_LINE('value 3');  
  ELSE
   DBMS_OUTPUT.PUT_LINE('no matching CASE found'); 
 END CASE; 
END;
/

Example Result

no matching CASE found

PL/SQL procedure successfully operation.

Searched CASE Statement

PL/SQL searched CASE statement has not selector and attempt to match one or more WHEN clauses condition.

Syntax

CASE
 WHEN condition-1 THEN 
  statement-1;
 WHEN condition-2 THEN 
  statement-2;
 ELSE
  statement-3;
END CASE;

Example Code

SQL>DECLARE
 a number := 3;
BEGIN
 CASE 
  WHEN a = 1 THEN
   DBMS_OUTPUT.PUT_LINE('value 1'); 
  WHEN a = 2 THEN
   DBMS_OUTPUT.PUT_LINE('value 2'); 
  WHEN a = 3 THEN
   DBMS_OUTPUT.PUT_LINE('value 3');  
  ELSE
   DBMS_OUTPUT.PUT_LINE('no matching CASE found'); 
 END CASE; 
END;
/

Example Result

value 3

PL/SQL EXIT CONTINUE GOTO Statements

PL/SQL EXIT, CONTINUE, GOTO Statements (Sequential Control Statements) are control your iteration loop, This three statement are sequential control statement,

1. EXIT Statement : This statement to exit the loop.
   EXIT WHEN Statement : This statement to exit, when WHEN clauses condition true.
2. CONTINUE Statement : to skip the current iteration with in loop.
   CONTINUE WHEN Statement : to skip the current iteration with in loop when WHEN clauses condition true.
3. GOTO Statement : Transfers the program execution flow unconditionally.

EXIT Statement

EXIT statement unconditionally exit the current loop iteration and transfer control to end of current loop. EXIT statement writing syntax,

Syntax

[ label_name ] LOOP 
   statement(s);
   EXIT; 
END LOOP [ label_name ];

Example Code

DECLARE
   no NUMBER := 5;
BEGIN
 LOOP
  DBMS_OUTPUT.PUT_LINE ('Inside value:  no = ' || no);
     no := no -1;
     IF no = 0 THEN
        EXIT;
     END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Outside loop end');   -- After EXIT control transfer this statement
END;
/

Example Result

Inside value: no = 5
Inside value: no = 4
Inside value: no = 3
Inside value: no = 2
Inside value: no = 1
Outside loop end

PL/SQL procedure successfully completed.

EXIT WHEN Statement

EXIT WHEN statement unconditionally exit the current loop iteration when WHEN clause condition true. EXIT WHEN statement writing syntax,

Syntax

[ label_name ] LOOP 
   statement(s);
   EXIT WHEN condition;
END LOOP [ label_name ];

Example Code

SQL>DECLARE
 i number;
BEGIN
 LOOP 
  dbms_output.put_line('Hello');
  i:=i+1;
  EXIT WHEN i>5;
 END LOOP; 
END;
/

Example Result

Hello
Hello
Hello
Hello

PL/SQL procedure successfully completed.

CONTINUE Statement

CONTINUE Statement unconditionally skip the current loop iteration and next iteration iterate as normal, only skip matched condition.

Syntax

IF condition THEN
 CONTINUE;
END IF;

Example Code

DECLARE
   no NUMBER := 0;
BEGIN
 FOR no IN 1 .. 5 LOOP
     IF i = 4 THEN
        CONTINUE;
     END IF;
     DBMS_OUTPUT.PUT_LINE('Iteration : ' || no);
   END LOOP;
END;
/

Example Result

Iteration # 1
Iteration # 2
Iteration # 3
Iteration # 5

PL/SQL procedure successfully completed.

CONTINUE WHEN Statement

CONTINUE WHEN Statement unconditionally skip the current loop iteration when WHEN clauses condition true,

Syntax

CONTINUE WHEN condition;
 statement(s);

Example Code

DECLARE
   no NUMBER := 0;
BEGIN
 FOR no IN 1 .. 5 LOOP
     DBMS_OUTPUT.PUT_LINE('Iteration : ' || no);
  CONTINUE WHEN no = 4
   DBMS_OUTPUT.PUT_LINE('CONTINUE WHEN EXECUTE Iteration : ' || no);
   END LOOP;
END;
/

Example Result

Iteration # 1
Iteration # 2
Iteration # 3
CONTINUE WHEN EXECUTE Iteration : 4
Iteration # 5

PL/SQL procedure successfully completed.

GOTO Statement

GOTO statement unconditionally transfer program control. GOTO statement writing syntax,

Syntax

GOTO code_name
 -----------
 -----------
<<code_name>>
-----------
-----------

Example Code

SQL>BEGIN
FOR i IN 1..5 LOOP
 dbms_output.put_line(i);
 IF i=4 THEN
  GOTO label1;
 END IF; 
END LOOP; 
<<label1>>
DBMS_OUTPUT.PUT_LINE('Row Filled');
END;
/

Example Result

1
2
3
4
Row Filled

PL/SQL procedure successfully completed.