CURSOR IN ORACLE
TYPES::
1.Implicit Cursor
2.Explicit Cursor
1.Implicit Cursor
If your query returns exactly one row,Then declare the PL/SQL Engine
Default Name .....((SQL))
SQL%attribute_Name;
2.Explicit Cursor
If your query returns more than one row then it's called explicit cursor.
It declared by Programmer.
Cursor_Name%attribute_Name
Cursor Attributes
1.%FOUND
2.%NOTFOUND
3.%ISOPEN
4.%ROWCOUNT
%FOUND & %NOTFOUND::
DECLARE
v_first_name employees.first_name%TYPE;
v_hire_date employees.hire_date%TYPE;
v_salary employees.salary%TYPE;
v_department_id employees.department_id%TYPE;
CURSOR emp_c
IS
SELECT e.first_name,
e.hire_date,
e.salary,
e.department_id
FROM employees e
WHERE e.department_id IN (10, 60, 90);
BEGIN
DBMS_OUTPUT.put_line ('*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-');
OPEN emp_c;
LOOP
FETCH emp_c
INTO v_first_name,
v_hire_date,
v_salary,
v_department_id;
EXIT WHEN emp_c%NOTFOUND;
DBMS_OUTPUT.put_line ('First Name : ' || v_first_name);
DBMS_OUTPUT.put_line ('Hire Date : ' || v_hire_date);
DBMS_OUTPUT.put_line ('Salary : ' || v_salary);
DBMS_OUTPUT.put_line ('department Id : ' || v_department_id);
DBMS_OUTPUT.put_line ('*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-');
--EXIT WHEN emp_c%FOUND;
END LOOP;
CLOSE emp_c;
END;
/
OUTPUT::
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
First Name : Jennifer
Hire Date : 17-SEP-87
Salary : 4400
department Id : 10
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
First Name : Alexander
Hire Date : 03-JAN-90
Salary : 9000
department Id : 60
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
First Name : Bruce
Hire Date : 21-MAY-91
Salary : 6000
department Id : 60
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
%ISOPEN & %ROWCOUNT
DECLARE
CURSOR emp_c
IS
SELECT e.first_name,
e.hire_date,
e.salary,
e.department_id
FROM employees e
WHERE e.department_id IN (10, 60);
v_alldata emp_c%ROWTYPE;
BEGIN
DBMS_OUTPUT.put_line ('*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-');
OPEN emp_c;
LOOP
FETCH emp_c INTO v_alldata;
EXIT WHEN emp_c%NOTFOUND;
DBMS_OUTPUT.put_line ('Record #' || emp_c%ROWCOUNT);
DBMS_OUTPUT.put_line ('First Name : ' || v_alldata.first_name);
DBMS_OUTPUT.put_line ('Hire Date : ' || v_alldata.hire_date);
DBMS_OUTPUT.put_line ('*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-');
END LOOP;
--CLOSE emp_c;
IF emp_c%ISOPEN
THEN
CLOSE emp_c;
DBMS_OUTPUT.put_line ('Cursor Closed....');
ELSE
DBMS_OUTPUT.put_line ('Cursor Already Closed....');
END IF;
END;
/
OUTPUT::
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
Record #1
First Name : Jennifer
Hire Date : 17-SEP-87
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
Record #2
First Name : Alexander
Hire Date : 03-JAN-90
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
Record #3
First Name : Bruce
Hire Date : 21-MAY-91
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
Record #4
First Name : David
Hire Date : 25-JUN-97
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
Record #5
First Name : Valli
Hire Date : 05-FEB-98
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
Record #6
First Name : Diana
Hire Date : 07-FEB-99
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
Cursor Closed....
IMPLICIT CURSOR Example
SQL> CREATE TABLE test_insert1(id NUMBER, name VARCHAR2(30), salary NUMBER(8),
department_id NUMBER)
Table created.
Example Block:
BEGIN
INSERT INTO test_insert1
VALUES (1001,
'Name_1',
17000,
80);
INSERT INTO test_insert1
VALUES (1002,
'Name_2',
14000,
90);
INSERT INTO test_insert1
VALUES (1003,
'Name_3',
24000,
70);
INSERT INTO test_insert1
VALUES (1004,
'Name_4',
53000,
90);
INSERT INTO test_insert1
VALUES (1005,
'Name_5',
34000,
10);
DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
COMMIT;
END;
/
Output:
1
PL/SQL procedure successfully completed.
SQL> SELECT * FROM test_insert1;
ID NAME SALARY DEPARTMENT_ID
---------- -------------------- ---------- -------------
1001 Name_1 17000 80
1002 Name_2 14000 90
1003 Name_3 24000 70
1004 Name_4 53000 90
1005 Name_5 34000 10
BEGIN
UPDATE test_insert1 t
SET t.salary = salary * 2
WHERE t.department_id = 90;
IF SQL%NOTFOUND
THEN
DBMS_OUTPUT.put_line ('No rows updated');
ELSE
DBMS_OUTPUT.put_line (SQL%ROWCOUNT || ' rows(s) get updated');
END IF;
END;
/
2 rows(s) get updated
PL/SQL procedure successfully completed.
FOR UPDATE OF & WHERE CURRENT OF
FOR UPDATE OF
It Blocks or lock the current processing row in a table.
WHERE CURRENT OF
It point out the current Processing row in a table.
Example::
SQL> CREATE table emp_update
2 AS
3 SELECT employee_id, first_name, salary, hire_date, department_id
4 FROM employees
5 WHERE department_id IN (10,20,80,90);
DECLARE
CURSOR empu_c
IS
SELECT *
FROM emp_update
FOR UPDATE OF salary NOWAIT;
BEGIN
--Cursor FOR LOOP
DBMS_OUTPUT.put_line ('*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*');
FOR i IN empu_c
LOOP
IF i.first_name = 'Peter'
THEN
UPDATE emp_update e
SET e.salary = salary * 2
WHERE CURRENT OF empu_c;
END IF;
END LOOP;
COMMIT;
END;
/
Parameterized Cursor
*We can pass parameters into a cursor and use them in the query.
*We can only pass values to the cursor; and cannot pass values out
of the cursor through parameters.
*Only the datatype of the parameter is defined, not its length.
Example 1:
DECLARE
CURSOR PAR_CURSOR(P_CURSOR NUMBER) IS
SELECT * FROM INVOICE_DETAILS WHERE INVOICE_NO = P_CURSOR;
V_TABLE_DATA INVOICE_DETAILS%ROWTYPE;
BEGIN
FOR I IN PAR_CURSOR(515) LOOP
DBMS_OUTPUT.PUT_LINE('INVOICE NO IS: ' || I.INVOICE_NO);
DBMS_OUTPUT.PUT_LINE('INVOICE DATE IS: ' || I.INVOICE_DATE);
DBMS_OUTPUT.PUT_LINE('COMMENTS IS: ' || I.COMMENTS);
END LOOP;
END;
Example 2
DECLARE
CURSOR CUR_REBELLIONRIDER(VAR_E_ID NUMBER) IS
SELECT EMPNO, ENAME,JOB,HIREDATE
FROM EMP
WHERE EMPNO > VAR_E_ID;
BEGIN
FOR L_IDX IN CUR_REBELLIONRIDER(200)
LOOP
DBMS_OUTPUT.PUT_LINE(L_IDX.EMPNO || ' ' || L_IDX.ENAME);
END LOOP;
END;
--Cursor In Oracle End--
