Total Pageviews

March 9, 2016

3/09/2016 03:09:00 AM
1
Oracle Cursors

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;

1 comments:

Post a Comment