--- cursor using where clause in it
DECLARE
CURSOR ebiz_cursor
IS
SELECT e.department_id,
employee_id,
last_name,
salary
FROM employees e, departments d
WHERE d.department_id = e.department_id AND d.department_id = 60
FOR UPDATE OF salary;
BEGIN
FOR ebiz_record IN ebiz_cursor
LOOP
IF ebiz_record.salary < 5000
THEN
UPDATE employees
SET salary = ebiz_record.salary * 1.10
WHERE CURRENT OF ebiz_cursor;
END IF;
END LOOP;
END;
--simple Refcursor
DECLARE
TYPE ebiz_type IS REF CURSOR;
var_ebiz ebiz_type;
dept_name departments.department_name%TYPE;
emp_name employees.last_name%TYPE;
CURSOR ebiz_cursor
IS
SELECT department_name,
cursor (SELECT e.last_name
FROM employees e
WHERE e.department_id = d.department_id)
employees
FROM departments d
WHERE department_name LIKE '%a%';
BEGIN
OPEN ebiz_cursor;
LOOP
FETCH ebiz_cursor INTO dept_name, var_ebiz;
EXIT WHEN ebiz_cursor%NOTFOUND;
DBMS_OUTPUT.put_line ('Department: ' || dept_name);
LOOP
FETCH var_ebiz INTO emp_name;
EXIT WHEN var_ebiz%NOTFOUND;
DBMS_OUTPUT.put_line (' Employee: ' || emp_name);
END LOOP;
END LOOP;
CLOSE ebiz_cursor;
END;
---cursor using index by table / associative array in it.
DECLARE
TYPE ebiz_type
IS
TABLE OF employees%ROWTYPE
INDEX BY BINARY_INTEGER;
var_ebiz ebiz_type;
v_count NUMBER (3) := 204;
BEGIN
FOR i IN 100 .. v_count
LOOP
SELECT *
INTO var_ebiz (i)
FROM employees
WHERE employee_id = i;
END LOOP;
FOR i IN var_ebiz.FIRST .. var_ebiz.LAST
LOOP
DBMS_OUTPUT.put_line( var_ebiz (i).employee_id
|| ' '
|| var_ebiz (i).last_name
|| ' '
|| var_ebiz (i).salary);
END LOOP;
END;
-- cursor using varray in it.
DECLARE
p_empno NUMBER;
p_projname VARCHAR2 (20);
p_role VARCHAR2 (30);
pl projectlist;
BEGIN
SELECT projects
INTO pl
FROM emp
WHERE empno = p_empno;
pl.EXTEND;
pl (pl.LAST) := project_type (p_projname, p_role);
UPDATE emp
SET projects = pl
Example 12:-cursor using bulk collects in it.
DECLARE
TYPE ebiz_index IS TABLE OF DEPARTMENTS%ROWTYPE;
var_ebiz ebiz_index;
CURSOR ebiz_cursor
IS
SELECT * FROM DEPARTMENTS;
BEGIN
OPEN ebiz_cursor;
FETCH ebiz_cursor BULK COLLECT INTO var_ebiz;
CLOSE ebiz_cursor;
FOR i IN var_ebiz.FIRST .. var_ebiz.LAST
LOOP
DBMS_OUTPUT.put_line( 'DEP_ID='
|| var_ebiz (i).department_id
|| 'NAME='
|| var_ebiz (i).department_name
|| 'LOC ID='
|| var_ebiz (i).location_id);
END LOOP;
END;
Example 13:-bulk collect
DECLARE
TYPE ebiz_index IS TABLE OF departments%ROWTYPE;
var_ebiz ebiz_index;
BEGIN
SELECT *
BULK COLLECT
INTO var_ebiz
FROM departments;
FOR i IN var_ebiz.FIRST .. var_ebiz.LAST
LOOP
DBMS_OUTPUT.put_line( 'Dname = '
|| var_ebiz (i).department_name
|| ' Loc = '
|| var_ebiz (i).location_id);
END LOOP;
END;
Example 14:- cursor using cursor attribute %isopen in it.
DECLARE
CURSOR att_is_open
IS
SELECT * FROM EMPLOYEES;
ebiz_rec EMPLOYEES%ROWTYPE;
BEGIN
OPEN att_is_open;
IF (NOT att_is_open%ISOPEN)
THEN
OPEN att_is_open;
END IF;
FOR i IN 1 .. 107
LOOP
FETCH att_is_open INTO ebiz_rec;
DBMS_OUTPUT.put_line( ebiz_rec.EMPLOYEE_ID
|| ' '
|| ebiz_rec.LAST_NAME
|| ' '
|| ebiz_rec.SALARY
|| ' '
|ebiz_rec.DEPARTMENT_ID);
END LOOP;
CLOSE att_is_open;
END
-- cursor using cursor attribute %found in it.
DECLARE
CURSOR att_found
IS
SELECT *
FROM EMPLOYEES
ORDER BY employee_id;
ebiz_rec EMPLOYEES%ROWTYPE;
BEGIN
OPEN att_found;
LOOP
FETCH att_found INTO ebiz_rec;
IF att_found%FOUND
THEN
DBMS_OUTPUT.put_line( 'ebiz#'
|| ' '
|| ebiz_rec.employee_id
|| ' '
||ebiz_rec.last_name
|| ' '
|| ebiz_rec.salary);
ELSE
DBMS_OUTPUT.put_line (
'------------process finished----------------'
);
EXIT;
END IF;
END LOOP;
CLOSE att_found;
END;
Example 16:- cursor using cursor attribute %notfound in it.
DECLARE
CURSOR att_not_found
IS
SELECT *
FROM EMPLOYEES
ORDER BY employee_id;
rev_rec EMPLOYEES%ROWTYPE;
BEGIN
OPEN att_not_found;
LOOP
FETCH att_not_found INTO ebiz_rec;
EXIT WHEN att_not_found%NOTFOUND;
DBMS_OUTPUT.put_line( 'ebiz#'
|| ' '
||ebiz_rec.employee_id
|| ' '
|| ebiz_rec.last_name
|| ' '
|| ebiz_rec.salary);
END LOOP;
CLOSE att_not_found;
END;
