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