|
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', 'wvelasq@g.com', SYSDATE, 'DBA', 20000, 4, 1006);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE departments
2 (department_id number(10) not null,
3 department_name varchar2(50) not null,
4 CONSTRAINT departments_pk PRIMARY KEY (department_id)
5 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 1, 'Data Group' );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 2, 'Purchasing' );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 3, 'Call Center' );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 4, 'Communication' );
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> set echo off
SQL> set serveroutput on
SQL> set verify off
SQL> set define '&'
SQL>
SQL> prompt
SQL> prompt 'What table would you like to see?'
'What table would you like to see?'
SQL> accept tab prompt '(L)ocations, (D)epartments, or (E)mployees : '
(L)ocations, (D)epartments, or (E)mployees : prompt
SQL>
SQL> declare
2 type refcur_t is ref cursor;
3 refcur refcur_t;
4
5 type sample_rec_type is record (
6 id number,
7 description varchar2(200));
8 sample sample_rec_type;
9
10 selection varchar2(1) := upper(substr('&tab',1,1));
11 begin
12 if selection = 'D' then
13 open refcur for
14 select department_id, department_name
15 from departments
16 where rownum < 11
17 order by 1;
18 dbms_output.put_line('Sample DEPARTMENT data:');
19
20 elsif selection = 'E' then
21 open refcur for
22 select employee_id, last_name
23 from employees
24 where rownum < 11
25 order by 1;
26 dbms_output.put_line('Sample EMPLOYEE data:');
27
28 else
29 dbms_output.put_line('Please enter ''D'', or ''E''.');
30 return;
31 end if;
32
33 fetch refcur into sample;
34
35 while refcur%FOUND loop
36 dbms_output.put_line('#' || sample.id || ' is ' || sample.description);
37 fetch refcur into sample;
38 end loop;
39 close refcur;
40 end;
41 /
Please enter 'D', or 'E'.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table departments cascade constraints;
Table dropped.
SQL> drop table employees cascade constraints;
Table dropped.
|