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 |
explain mysql aggregate functions. : Sql dba
What is the difference between rollback and rollback to statements?
Is sql an operating system?
what are the features and advantages of object-oriented programming? : Sql dba
define data blocks ? : Sql dba
What is java sql connection?
what is purge command explain about oracle performance tuning
2 Answers Accenture, eCentric Solutions,
What are the two types of cursors in pl sql?
What is rowid in sql?
How do you clear the screen in sql?
What is primary key and foreign key?
Is sql a microsoft product?