SQL>
SQL> create table employee
2 (
3 empl_no integer primary key
4 ,lastname varchar2(20) not null
5 ,firstname varchar2(15) not null
6 ,midinit varchar2(1)
7 ,street varchar2(30)
8 ,city varchar2(20)
9 ,state varchar2(2)
10 ,zip varchar2(5)
11 ,zip_4 varchar2(4)
12 ,area_code varchar2(3)
13 ,phone varchar2(8)
14 ,company_name varchar2(50));
Table created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1,'Jones','Joe','J','10 Ave','New York','NY','11111','1111','111', '111-1111','A Company');
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,'Smith','Sue','J','20 Ave','New York','NY','22222','2222','222', '222-111','B Company');
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,'Anderson','Peggy','J','500 St','New York','NY','33333','3333','333', '333-3333','C Company');
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,'Andy','Jill', null,'930 St','New York','NY','44444','4444','212', '634-7733','D Company');
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,'OK','Carl','L','19 Drive','New York','NY','55555','3234','212', '243-4243','E Company');
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,'Peter','Jee','Q','38 Ave','New York','NY','66666','4598','212', '454-5443','F Inc');
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,'Baker','Paul','V','738 St.','Queens','NY','77777','3842','718', '664-4333','G Inc');
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,'Young','Steve','J','388 Ave','New York','NY','88888','3468','212', '456-4566','H Associates Inc');
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,'Mona','Joe','T','9300 Ave','Kansas City','MO','99999','3658','415', '456-4563','J Inc');
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,'Hackett','Karen','S','Kings Rd. Apt 833','Bellmore','NY','61202','3898','516', '767-5677','AA Inc');
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,'Bob','Jack','S','12 Giant Rd.','Newark','NJ','27377','3298','908', '123-7367','Z Associates');
1 row created.
SQL>
SQL>
SQL> create or replace function get_employee
2 (p_empl_no in employee.empl_no%type)
3 return employee%rowtype
4 as
5 l_cust_record employee%rowtype;
6 begin
7 select * into l_cust_record from employee
8 where empl_no = p_empl_no;
9
10 return(l_cust_record);
11 end;
12 /
Function created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> create or replace procedure x (p_empl_no in employee.empl_no%type) as
2 x employee%rowtype;
3 begin
4 x := get_employee(p_empl_no);
5 dbms_output.put_line(x.lastname);
6 end;
7 /
Procedure created.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
SQL>