Scribd
Upload
5 views2 pages

SQL Cursors: Types and Usage Explained

A cursor in SQL is a temporary work area that holds the result set of a query, allowing for row-by-row processing. There are three types of cursors: implicit cursors created automatically by Oracle for single SQL statements, explicit cursors defined by users for handling multiple rows, and ref cursors which allow dynamic SQL queries and parameter passing. Ref cursors can be strong (fixed return type) or weak (variable return type).

Uploaded by

debasisdeo.37
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
5 views2 pages

SQL Cursors: Types and Usage Explained

A cursor in SQL is a temporary work area that holds the result set of a query, allowing for row-by-row processing. There are three types of cursors: implicit cursors created automatically by Oracle for single SQL statements, explicit cursors defined by users for handling multiple rows, and ref cursors which allow dynamic SQL queries and parameter passing. Ref cursors can be strong (fixed return type) or weak (variable return type).

Uploaded by

debasisdeo.37
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

>>> A cursor is a temporary work area created in memory when a SQL statement is

executed.
It holds the result set rows returned by the query and allows you to fetch,
process, or manipulate each row one at a time.

>>Types of cursor:

-- -Implicit Cursor-- It created automatically by oracle when it execute single


sql,DML statement like SELECT INTO,INSERT,UPDATE,DELETE. It close internally as it
created by oracle.

DECLARE

l_Name [Link]%TYPE;
l_Salary [Link]%TYPE;

BEGIN

Select ename,sal INTO l_Name,l_Salary From emp Where empno = 1234;

dbms_output.put_line('Name: ' || l_Name || ', Salary: ' || l_Salary);

End;
/

--- Explicit Cursor --- It created by user or developer to handle queries returning
multiple rows. As it created by us it need to declare,open,fetch,close.

DECLARE
CURSOR emp_cursor is Select ename,sal From emp Where deptno = 10;
l_Name [Link]%TYPE;
l_Salary [Link]%TYPE;

BEGIN

Open emp_cursor;

Loop

Fetch emp_cursor INTO l_name, l_Salary;


EXIt When emp_cursor%NOTFOUND;
dbms_output.put_line('Name :' || l_Name || ', Salary: ' || l_Salary);

End Loop;
Close emp_cursor;

End;
/

--- Ref Cursor--- Its like explicit Cursor, main advantages is it can allow dynamic
sql query and can passed parameters.

DECLARE

TYPE ref_cur IS REF CURSOR;


l_emp ref_cur;
l_Name [Link]%TYPE;
L_Salary [Link]%TYPE;

BEGIN

Open l_emp FOR Select ename,sal from emp where deptno= 10;

Loop
FETCH l_emp INTO l_Name,l_Salary;
Exit When l_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || l_Name || ',Salary:' || l_Salary );

End Loop;
Close l_emp;
end;
/
>> Types of REF Cursor

Strong ref cursor -- return type is fixed


Weak ref cursor ---- return type can vary

You might also like