PL SQL Tutorial

---

PL SQL CURSORS

     In PL/SQL Cursors are Name Assigned to Memory Area. This area contains SQL statement and data returned by SQL Statement. There can be either static cursors or dynamic cursors, Static cursors where SQL statement is determined at compiled time, Dynamic Cursors where SQL statement is determined at runtime.

• Static Cursors are used only for DML Operations
• Dynamic Cursors are used for any SQL, i.e DML,DDL,DCL Statements, using EXECUTE_IMMEDIATE statement.

Cursor Types

• Exlicit Cursors
• Implicit Cursors
• REF Cursors

Implicit Cursor

     Implicit Cursors, Opening a cursor,Fetching records, and Closing a cursor, these operations done by PL/SQL Engine. User has no knowledge how it is done, internally PL/SQL performs These operations on behalf of User. Anyway Programmer can check cursor status using following Cursor Attributes.

Implicit Cursor Attributes

• SQL%FOUND
• SQL%NOTFOUND
• SQL%ROWCOUNT
• SQL%ISOPEN

    for loop is used to loop through implicit cursors. it internally, opens the cursor, fetches the data, and close the cursor. This process done, automatically.

Example: Get scott schema emp table using Implicit cursor

   Here SQL statement specified in the for itself. IDX is a record,it contains all fields specified in the SQL Statement.

idx.empno gives employee no, idx.ename gives employee name etc., in above SQL statement '*' is used means fetch all fields. for simplicity used only 2 fields ,empno,ename

BEGIN 
	FOR IDX IN (SELECT * FROM EMP) 
	LOOP
		   DBMS_OUTPUT.PUT_LINE(IDX.EMPNO|| ' ' || IDX.ENAME);
	END LOOP;
END;

SQL>/
7839 KING
7698 BLAKE
7782 CLARK
.....

	

Example: Get scott schema emp table using Implicit cursor

   In this example
    cursor is declared in the declare section,
    Cursor variable is used in the For loop, as shown below.

EMP_REC is a Record type,using which all data can be fetched. for loop fetches one-record at a time, loop terminates when SQL%NOTFOUND is returns a value.

		set serveroutput on;

DECLARE 

CURSOR C IS SELECT EMPNO,ENAME,SAL FROM EMP;

BEGIN

     FOR EMP_REC IN C
     LOOP
        DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPNO||' '||EMP_REC.ENAME||' '||EMP_REC.SAL);
        
     END LOOP;
END;

SQL>/
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7788 SCOTT 3000
.......
	

Explicit Cursor

    

Steps required to use Explicite Cursor

1. Declare Cursor variable
2. OPEN cursor
3. FETCH
4. Check Cursor Attribute
5. CLOSE CURSOR

Explicit Cursor Attributes

• cursor_name%ISOPEN

    returns TRUE when cursor is opened.

• cursor_name%FOUND

   returns TRUE when record fetched successfully

• cursor_name%NOTFOUND

    returns TRUE when record was not fetched successfully

• cursor_name%ROWCOUNT

    returns number of records fetched from the cursor.

PL/SQL Explicit Cursor Example:

     Cursor C points to SQL Query(fetch Depart name,ID, Employee Name, JOB from emp and dept table)

RECORD REC is of type Cursor C
OPEN -- open cursor C
FETCH REC from Cursor C
CLOSE Cursor C

Loops continues Cursor%NOTFOUND is false, (%NOTFOUND=True means no more records to fetch)

DECLARE

CURSOR C IS
SELECT E.DEPTNO,DNAME,ENAME,JOB FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

REC C%ROWTYPE;

BEGIN

OPEN C;

	LOOP
	    FETCH C INTO REC;
	    IF c%notFOUND THEN EXIT;END IF;

		DBMS_OUTPUT.PUT_LINE(REC.DEPTNO||' '||REC.DNAME||' '||REC.ENAME||' '||REC.JOB);    

	END LOOP;

CLOSE C;

END;	

SQL>/

10 ACCOUNTING KING PRESIDENT
30 SALES BLAKE MANAGER
10 ACCOUNTING CLARK MANAGER
20 RESEARCH JONES MANAGER
20 RESEARCH SCOTT ANALYST

.
.
.

PL/SQL cursor with Paramaters

    Parameters can be specified to cursor at the time of declaration. Like procedures or functions, parameters can be passed,

Syntax:

		cursor is cursor_variable(param1,param2....) is  SQL Statement;
	

Example:

Get dept 10 employees

		DECLARE
		 CURSOR C(DNO NUMBER) IS SELECT * FROM EMP WHERE DEPTNO=DNO;
	

In the Execution Section

		BEGIN
			FOR I IN C(10)
			LOOP
				DBMS_OUTPUT.PUT_LINE(I.EMPNO||' '||I.ENAME);
			END LOOP;
			
		END;
		
		SQL>/
		7839 KING
		7782 CLARK
		7934 MILLER
		
	

PL/SQL REF CURSORS

Syntax:

	TYPE  ref_cursor_type_name is REF CURSOR 
	               [RETURN record_type];
	               
	cursor_variable  ref_cursor_type_name;
	

• Weakly-Typed REF Cursor

The cursor doesnot have RETURN statement is called weakly typed ref cursor, For weakly typed REF cursors you can use built-in SYS_REFCURSOR

• Strongly-Typed REF Cursor

The cursor which have RETURN statement

Declaring Strongly Typed REF CURSOR

		DECLARE
		 TYPE emp_ref_type is REF CURSOR RETURN emp%ROWTYPE;
		 emp_rec  emp_ref_type;
	        
	

Declaring Weakly Typed REF CURSOR

		TYPE emp_ref_type is REF CURSOR;
		emp_rec emp_ref_type;
		
		or 
		
		emp_ref  SYS_REFCURSOR;
	

REF CURSORS in Functions Stored Functions/Functions
REF CURSORS in Stored Procedures PL/SQL Stored Procedures
REF CURSORS in Packages PL/SQL Packages