Unconstrained Cursors

 
-- This script shows the use of unconstrained cursors

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

CREATE TABLE departments
(department_id             number(10)            not null,
 department_name           varchar2(50)      not null,
 CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

insert into departments ( department_id, department_name )values(1,'Data Group' );
insert into departments ( department_id, department_name )values(2,'Purchasing' );
insert into departments ( department_id, department_name )values(3,'Call Center' );
insert into departments ( department_id, department_name )values(4,'Communication' );
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     TYPE t_cursor IS REF CURSOR;
  3     v_cursor t_cursor;
  4     v_emp emp%ROWTYPE;
  5     v_dept departments%ROWTYPE;
  6  BEGIN
  7     OPEN v_cursor FOR SELECT * FROM emp WHERE empno < 5;
  8     LOOP
  9         FETCH v_cursor INTO v_emp;
 10         EXIT WHEN v_cursor%NOTFOUND;
 11         DBMS_OUTPUT.PUT_LINE('id:' || v_emp.empno || ', name = ' || v_emp.ename ||', salary= ' || v_emp.sal);
 12     END LOOP;
 13
 14     OPEN v_cursor FOR SELECT * FROM departments WHERE department_id < 3;
 15     LOOP
 16         FETCH v_cursor INTO v_dept;
 17         EXIT WHEN v_cursor%NOTFOUND;
 18         DBMS_OUTPUT.PUT_LINE('id:' || v_dept.department_id || ', name = ' || v_dept.department_name);
 19     END LOOP;
 20     CLOSE v_cursor;
 21  END;
 22  /
id:1, name = SMITH, salary= 800
id:2, name = ALLEN, salary= 1600
id:3, name = WARD, salary= 1250
id:4, name = JONES, salary= 2975
id:1, name = Data Group
id:2, name = Purchasing

PL/SQL procedure successfully completed.

SQL>