Answer Posted / chandra shekhar
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 |
Post New Answer View All Answers
What is assignment operator in pl sql?
What is implicit cursor in pl sql?
define sql insert statement ? : Sql dba
Describe sql comments?
What are pl sql procedures?
what is a constraint? : Sql dba
How many commands are in sql?
How do you exit in sql?
Why is a trigger used?
What is a data manipulation language?
What are the different dml commands in sql?
Is sql a programming?
List and explain the different types of join clauses supported in ansi-standard sql?
What is basic structure of pl sql?
What is trigger in pl sql with examples?