How To Create An Explicit Cursor In Oracle Database

 

First and foremost, if you construct a cursor in the database, your statement will write four items.

1.       Declare

2.       Begin

3.       Exception

4.       End

 

In the declare statement, you define all of the variables in the program. In my situation, there are just three variables declared. There's also a cursor.

DECLARE

CUR SYS_REFCURSOR;

V_EMPNO EMP.EMPNO%TYPE;

V_ENAME EMP.ENAME%TYPE;

V_DEPTNO EMP.DEPTNO%TYPE;

With the assistance of the select query, you must describe the query that will be used to determine what kind of data is called in your statement in the begin statement.

BEGIN

OPEN CUR FOR SELECT EMPNO,ENAME,DEPTNO FROM EMP ORDER BY DEPTNO;

Finally, retrieve the data from the cursor in order to obtain the fetch statement.

 

LOOP

FETCH CUR INTO V_EMPNO,V_ENAME ,V_DEPTNO;

After that, write the cursor's exit statement.

EXIT WHEN CUR%notfound;

DBMS_OUTPUT.PUT_LINE(CUR%ROWCOUNT||' '||V_EMPNO||' ' ||V_ENAME || ' '|| V_DEPTNO);

END LOOP;

CLOSE CUR;

END;

Program:

DECLARE

CUR SYS_REFCURSOR;

V_EMPNO EMP.EMPNO%TYPE;

V_ENAME EMP.ENAME%TYPE;

V_DEPTNO EMP.DEPTNO%TYPE;

BEGIN

OPEN CUR FOR SELECT EMPNO,ENAME,DEPTNO FROM EMP ORDER BY DEPTNO;

LOOP

FETCH CUR INTO V_EMPNO,V_ENAME ,V_DEPTNO;

EXIT WHEN CUR%notfound;

DBMS_OUTPUT.PUT_LINE(CUR%ROWCOUNT||' '||V_EMPNO||' ' ||V_ENAME || ' '|| V_DEPTNO);

END LOOP;

CLOSE CUR;

END;

Output:

I hope it's helpful for you. If you have any queries, don't hesitate to contact me.

Name : Muhammad Sahal Qasim

E-mail : s.m.sahal789@gmail.com

Thank you.