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 |
What is not null in sql?
Why use subqueries instead of joins?
Can I create table without primary key?
how to find the second highest salary from emp table?
211 Answers CIS, Cognizant, Cosmosoft, DAS, EDS, GreenTech, HOV Services, IBM, Infosys, National Institute of Science and Technology, Patni, Persistent, Polaris, TCS, Wipro, Yardi, Zensar,
What is the default isolation level in sql server? : Transact sql
What is the difference between syntax error and runtime error?
Can procedure in package be overloaded?
How many sql commands are there?
What is substitution variable?
what are the advantages of primary key over unique+notnull
7 Answers Consultancy, LGS, Oracle,
What is t-sql? : Transact sql
where are cookies actually stored on the hard disk? : Sql dba