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

How many clustered indexes can be created on a table?

1121

---

write an sql query to get third maximum salary of an employee from a table named employee_table. : Sql dba

1009

---

What operating systems are supported by oracle sql developer?

1009

---

What is row_number () in sql?

1021

---

Which are sql * plus commands?

1194

---

What is the difference between left join and right join?

986

---

What is normalisation in sql?

1096

---

how to include comments in sql statements? : Sql dba

992

---

What are operators available in sql?

1090

---

What is rowtype?

1075

---

List the different type of joins?

1111

---

how to use like conditions? : Sql dba

1157

---

How do you identify a primary key?

1109

---

how to get @@error and @@rowcount at the same time? : Sql dba

1072

---

what is the difference between char and varchar data types? : Sql dba

1165

---