cursor types? explain with example programs?
Cursor Types
- Implicit Cursor
- Explicit Cursor (Without parameters, With Parameters)
############################################
##### Implicit Cursor #####################
############################################
-- assuming there is a table emp in the schema
DECLARE
VENAME EMP.ENAME%TYPE ;
BEGIN
SELECT ENAME INTO VENAME FROM EMP WHERE EMPNO = &EMPLOYEENUMBER ;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME IS ' || VENAME);
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO EMPLOYEE WITH GIVE EMPLOYEE NUMBER ') ;
END ;
/
############################################
##### Explicit Cursor #####################
############################################
-- First method
===============
DECLARE
CURSOR C1 IS SELECT EMPNO, ENAME FROM EMP ;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT(I.EMPNO);
DBMS_OUTPUT.PUT_LINE(' -- ' || I.ENAME);
END LOOP ;
END ;
/
-- Second method
================
DECLARE
CURSOR C1 IS SELECT EMPNO, ENAME FROM EMP ;
E C1%ROWTYPE ;
BEGIN
OPEN C1 ;
LOOP
FETCH C1 INTO E ;
EXIT WHEN C1%NOTFOUND ;
DBMS_OUTPUT.PUT(E.EMPNO);
DBMS_OUTPUT.PUT_LINE(' -- ' || E.ENAME);
END LOOP ;
CLOSE C1 ;
END ;
/
-- With Parameters
==================
DECLARE
CURSOR C1(n number) IS SELECT EMPNO, ENAME FROM EMP where sal < n;
E C1%ROWTYPE ;
BEGIN
OPEN C1(&salary) ;
loop
FETCH C1 INTO E ;
exit when c1%notfound ;
DBMS_OUTPUT.PUT(E.EMPNO);
DBMS_OUTPUT.PUT_LINE(' -- ' || E.ENAME);
end loop ;
CLOSE C1 ;
END ;
/
Is This Answer Correct ? | 4 Yes | 1 No |
Is it important to partition hard disk?
Can we commit inside a trigger?
Can you have multiple SPs with the same name on a database?
What is localdb mssqllocaldb?
What is procedure and function in sql?
What is a temp table?
Why do we need cursors in pl sql?
What is procedure explain with program?
How does left join work in sql?
What are the basic sql commands?
What is substitution variable?
How can we find duplicate records in a table?