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 or replace package people as
2 type employees_ref_cursor_type is ref cursor return employees%rowtype;
3
4 function get_employee( p_employee_id in number )
5 return employees_ref_cursor_type;
6
7 function get_employees( p_search in varchar2 )
8 return employees_ref_cursor_type;
9
10 end people;
11 /
Package created.
SQL> show error
No errors.
SQL>
SQL> create or replace package body people as
2 function get_employee( p_employee_id in number )
3 return employees_ref_cursor_type
4 is
5 l_emp employees_ref_cursor_type;
6 begin
7 open l_emp for
8 select * from employees where employee_id = p_employee_id;
9 return l_emp;
10 end get_employee;
11
12 function get_employees( p_search in varchar2 )
13 return employees_ref_cursor_type
14 is
15 l_emps employees_ref_cursor_type;
16 begin
17 open l_emps for
18 select * from employees
19 order by employee_id;
20 return l_emps;
21 end get_employees;
22 end people;
23 /
Package body created.
SQL> show error
No errors.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL> --