Cursors In Pl Sql

What is a cursor in Oracle

A cursor is a pointer to the private memory area allocated by the Oracle server.
There are two types of cursors:

Implicit: Created and managed internally by the Oracle server to process SQL statements

Explicit: Explicitly declared by the programmer using cursor name .

The syntax to declare a cursor is shown in the slide. In the syntax:
cursor_name Is a PL/SQL identifier


select_statement Is a SELECT statement without an INTO clause

There are four steps in we can use an Explicit Cursor.

1. DECLARE in this section we define cursor name.
2. OPEN the cursor in the Execution Section.
3. FETCH the data from cursor into PL/SQL variables or records in the Execution Section.

4  Finally CLOSE the cursor in the Execution Section before you end the PL/SQL Block.



Syntax to open a cursor is:OPEN cursor_name;

Syntax to fetch records from a cursor is:


FETCH cursor_name INTO record_name;

OR


FETCH cursor_name INTO variable_list;

CLOSE cursor_name;


Syntax to close a cursor is:


Write a program to accept a deptno and display who are working in that dept?
DECLARE
Dno EMP.DePartment_id%TYPE:=&DEPartment_id;
CURSOR cur IS
SELECT * FROM EMP WHERE DEPTNO=Dno;
recemp cur%ROWTYPE;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || emprec.EMPloyee_id);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || emprec.last_name);
DBMS_OUTPUT.PUT_LINE('SAL IS ' || emprec.SALary);
DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || emprec.Manager_id);
DBMS_OUTPUT.PUT_LINE('COMM IS ' || emprec.COMMission_pct);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || emprec.HIRE_DATE);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || emprec.DEPartment_id);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || emprec.JOB_id);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;