Example 1: - Simple cursor program.
DECLARE
p_emp_id employees.employee_id%TYPE;
p_name employees.last_name%TYPE;
p_sal employees.salary%TYPE;
CURSOR ebiz_cursor
IS
SELECT employee_id, last_name, salary FROM EMPLOYEES;
BEGIN
OPEN ebiz_cursor;
FOR i IN 1 .. 107
LOOP
FETCH ebiz_cursor INTO p_emp_id, p_name, p_sal;
DBMS_OUTPUT.put_line (
p_emp_id || ' ' || p_name || ' ' || p_sal
);
END LOOP;
END;
Example 2: - simple cursor using record in it.
DECLARE
CURSOR ebiz_cursor
IS
SELECT * FROM EMPLOYEES;
ebiz_record ebiz_cursor%ROWTYPE;
BEGIN
OPEN ebiz_cursor;
FOR i IN 1 .. 107
LOOP
FETCH ebiz_cursor INTO ebiz_record;
DBMS_OUTPUT.put_line( ebiz_record.employee_id
|| ' '
|| ebiz_record.last_name
|| ' '
|| ebiz_record.salary);
END LOOP;
END;
Example 3:- cursor using for loop
DECLARE
CURSOR ebiz_cursor
IS
SELECT * FROM EMPLOYEES;
BEGIN
FOR ebiz_record IN ebiz_cursor
LOOP
DBMS_OUTPUT.put_line( ebiz_record.employee_id
|| ' '
|| ebiz_record.last_name
|| ' '
|| ebiz_record.salary);
END LOOP;
END;;
Example 4:- cursor for lops using subqueries
BEGIN
FOR emp_record IN (SELECT last_name, department_id FROM employees)
LOOP
IF emp_record.department_id = 80
THEN
DBMS_OUTPUT.put_line( 'Employee '
|| emp_record.last_name
|| ' works in the Sales Dept. ');
END IF;
END LOOP;
END;
Example 5:- simple parameterized cursor
DECLARE
CURSOR ebiz_cursor (p_deptno number, p_job varchar2) IS
SELECT employee_id, last_name FROM employees WHERE department_id = p_deptno AND job_id = p_job;
BEGIN
OPEN ebiz_cursor (80, 'SA_REP');
UPDATE employees SET salary=14141 WHERE department_id = 80 OR job_id ='SA_REP' ;
CLOSE ebiz_cursor;
OPEN ebiz_cursor (60, 'IT_PROG');
UPDATE employees SET salary=15151 WHERE department_id = 60 OR job_id = 'IT_PROG';
CLOSE ebiz_cursor;
END;
Example 6:- cursor using for update clause in it
declare
cursor ebiz_cursor is select employee_id, last_name, department_name from
employees,departments
where employees.department_id = departments.department_id and employees.
department_id = 80
for update of salary;
end;
