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', 'YourName@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> CREATE OR REPLACE PROCEDURE showemps (
2 where_in IN VARCHAR2 := NULL
3 )
4 IS
5 cur INTEGER := DBMS_SQL.OPEN_CURSOR;
6 fdbk INTEGER;
7
8 last_name_tab DBMS_SQL.VARCHAR2_TABLE;
9 hire_date_tab DBMS_SQL.DATE_TABLE;
10 BEGIN
11 DBMS_SQL.PARSE (cur,
12 'SELECT last_name, hire_date FROM employees
13 WHERE ' || NVL (where_in, '1=1') ||
14 'ORDER BY hire_date',
15 DBMS_SQL.NATIVE);
16
17 DBMS_SQL.DEFINE_ARRAY (cur, 1, last_name_tab, 100, 1);
18 DBMS_SQL.DEFINE_ARRAY (cur, 2, hire_date_tab, 100, 1);
19
20 fdbk := DBMS_SQL.EXECUTE_AND_FETCH (cur);
21
22 DBMS_SQL.COLUMN_VALUE (cur, 1, last_name_tab);
23 DBMS_SQL.COLUMN_VALUE (cur, 2, hire_date_tab);
24
25 FOR rowind IN last_name_tab.FIRST .. last_name_tab.LAST
26 LOOP
27 DBMS_OUTPUT.PUT_LINE (
28 last_name_tab(rowind) ||
29 ' was hired on ' ||
30 hire_date_tab(rowind));
31 END LOOP;
32
33 DBMS_SQL.CLOSE_CURSOR (cur);
34 END;
35 /
Procedure created.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL>