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 ;
/

table structure: ---------------- col1 col2 ----- ----- 01-mar-2012 11:12:46 01-mar-2012 11:12:10 01-mar-2012 11:12:46 01-mar-2012 11:11:23 Write a query to display the result as shown below: col1 col2 ----- ----- 01-mar-2012 11:12:46 01-mar-2012 11:12:10

4094

---

what is a field in a database ? : Sql dba

989

---

How to avoid using cursors? What to use instead of cursor and in what cases to do so?

1124

---

How to create your own reports in sql developer?

972

---

How do you get column names only for a table (sql server)?

1100

---

how do you know the version of your mysql server? : Sql dba

935

---

what is isam? : Sql dba

950

---

Does truncate need commit?

893

---

What is index example?

950

---

How do you explain an index?

1222

---

How to fetch values from testtable1 that are not in testtable2 without using not keyword?

1156

---

Explain the rollback statement?

961

---

what are rollup and cube in t-sql? : Transact sql

1047

---

When you have to use a default "rollback to" savepoint of plvlog?

1123

---

How does sql developer connect to oracle database?

949

---