strong ref cursor : ref cursor « Cursor « Oracle PL / SQL






strong ref cursor

 
SQL>
SQL> CREATE TABLE employees
  2  ( employee_id          number(10)      not null,
  3    last_name            varchar2(50)      not null,
  4    email                varchar2(30),
  5    hire_date            date,
  6    job_id               varchar2(30),
  7    department_id        number(10),
  8    salary               number(6),
  9    manager_id           number(6)
 10  );

Table created.

SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
  2                values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 );

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1004,  'Kyte', 'tkyte@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1005, 'Viper', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
  2                 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1008, 'Oracle', 'oracle1@g.com', SYSDATE, 'DBA', 20000, 4, 1006);

1 row created.

SQL>
SQL> CREATE TABLE JOBS (
  2    JOB_ID      VARCHAR2 (10)  NOT NULL,
  3    JOB_TITLE   VARCHAR2 (35)  CONSTRAINT JOB_TITLE_NN   NOT NULL,
  4    MIN_SALARY  NUMBER (6),
  5    MAX_SALARY  NUMBER (6),
  6    CONSTRAINT JOB_ID_PK PRIMARY KEY ( JOB_ID ) ) ;

Table created.

SQL>
SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( 'DBA', 'President', 20000, 40000);

1 row created.

SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( 'PROG', 'Administration Vice President', 15000, 30000);

1 row created.

SQL> INSERT INTO JOBS ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY ) VALUES ( 'MGR', 'Administration Assistant', 3000, 6000);

1 row created.

SQL>
SQL>
SQL>
SQL>  declare
  2      type emp_job_rec is record(
  3        employee_id     number,
  4        employee_name   varchar2(56),
  5        job_title       varchar2(35)
  6      );
  7
  8      type emp_job_refcur_type is ref cursor
  9        return emp_job_rec;
 10
 11      emp_refcur emp_job_refcur_type;
 12
 13      emp_job    emp_job_rec;
 14    begin
 15      open emp_refcur for
 16        select e.employee_id,
 17               e.last_name "employee_name",
 18               j.job_title
 19          from employees e, jobs j
 20         where e.job_id = j.job_id
 21           and rownum < 11
 22         order by 1;
 23
 24      fetch emp_refcur into emp_job;
 25      while emp_refcur%FOUND loop
 26        dbms_output.put(emp_job.employee_name || '''s job is ');
 27        dbms_output.put_line(emp_job.job_title);
 28        fetch emp_refcur into emp_job;
 29      end loop;
 30    end;
 31    /
Lawson's job is Administration Assistant
Wells's job is President
Bliss's job is Administration Vice President
Kyte's job is Administration Assistant
Viper's job is Administration Vice President
Beck's job is Administration Vice President
Java's job is Administration Vice President
Oracle's job is President

PL/SQL procedure successfully completed.

SQL>
SQL> drop table employees;

Table dropped.

SQL> drop table jobs;

Table dropped.

 








Related examples in the same category

1.declare ref cursors
2.Cursor Variables Assignment
3.REF CURSOR variable
4.Use native dynamic SQL to process queries.