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 |
Does sql backup shrink transaction log?
What is rename command in sql?
What is a primary key example?
i have a column which may contain this kind of value: 123*67_80,12*8889_5,34*8_874 ,12*7_7 (can contain space before a comma, and this string length can be anything) now i want to split this value into two column like: column1: 123*67,12*8889,34*8,12*7 column2: 80,5,874,7 use function for this
If the application is running very slow? At what points you need to go about the database in order to improve the performance?
Explain polymorphism in pl/sql.
Should I use mbr or gpt?
Does access use sql?
What are different clauses used in sql?
Can we join tables without foreign key?
What is use of term?
What is the use of count (*) in sql?