Mega Code Archive
Create a package containing stored procedure DELETE_ORDERS and stored function GET_employee_NAME
<![CDATA[
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> create table ord
2 (
3 order_no integer primary key
4 ,empl_no integer
5 ,order_date date not null
6 ,total_order_price number(7,2)
7 ,deliver_date date
8 ,deliver_time varchar2(7)
9 ,payment_method varchar2(2)
10 ,emp_no number(3,0)
11 ,deliver_name varchar2(35)
12 ,gift_message varchar2(100)
13 );
Table created.
SQL>
SQL> insert into ord(order_no,empl_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,add_months(sysdate, -1), 235.00, '14-Feb-1999', '12 noon', 'CA',1, null, 'Gift for wife');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,add_months(sysdate, -2), 50.98, '14-feb-1999', '1 pm', 'CA',7, 'Rose', 'Happy Valentines Day to Mother');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,add_months(sysdate, -3), 35.99, '14-feb-1999', '1 pm', 'VS',2, 'Ruby', 'Happy Valentines Day to Mother');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,add_months(sysdate, -4), 19.95, '14-feb-1999', '5 pm', 'CA',2, 'Coy', 'Happy Valentines Day to You');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,add_months(sysdate, -7), 35.95, '21-jun-1999', '12 noon', 'VS', 2, 'Fill', 'Happy Birthday from Joe');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, add_months(sysdate, -8), 35.95, '1-jan-2000', '12 noon', 'DI',3, 'Laura', 'Happy New Year''s from Lawrence');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, add_months(sysdate, -9), 75.95, '2-jan-2000', '12 noon', 'CA',7, 'Sara', 'Happy Birthday from Lawrence' );
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, add_months(sysdate, -10), 19.95, sysdate, '2:30 pm', 'VG',2, 'OK', 'Happy Valentines Day to You');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, add_months(sysdate, -11), 30.00, sysdate+2, '1:30 pm', 'VG',2, 'Hi', 'Happy Birthday Day to You');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, add_months(sysdate, -12), 21.95, sysdate-2, '3:30 pm', 'CA',2, 'Jack', 'Happy Birthday Day to You');
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, add_months(sysdate, -1), 21.95, sysdate, '3:30 pm', 'CA',2, 'Jay', 'Thanks for giving 100%!');
1 row created.
SQL>
SQL> create table ord_history
2 (
3 order_no integer primary key
4 ,empl_no integer
5 ,order_date date not null
6 ,total_order_price number(7,2)
7 ,deliver_date date
8 ,deliver_time varchar2(7)
9 ,payment_method varchar2(2)
10 ,emp_no number(3,0)
11 ,deliver_name varchar2(35)
12 ,gift_message varchar2(100)
13 );
Table created.
SQL>
SQL> create or replace package mypackage as
2 procedure delete_orders (p_days in number);
3 function get_employee_name (p_empl_no in number) return varchar2 ;
4 end ;
5 /
Package created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> create or replace package body mypackage as
2 procedure delete_orders
3 (p_days in number)
4 is
5 begin
6 insert into ord_history
7 select *
8 from ord
9 where order_date < sysdate - p_days;
10 if sql%notfound then
11 dbms_output.put_line('No orders old than ' || p_days || 'days');
12 end if;
13 delete from ord
14 where order_date < sysdate - p_days;
15 commit;
16 end;
17
18 function get_employee_name
19 (p_empl_no in number)
20 return varchar2
21 is
22 v_name varchar2(40);
23 begin
24 select lastname || ', ' || firstname into v_name
25 from employee
26 where empl_no = p_empl_no;
27 return(v_name);
28 exception
29 when no_data_found then
30 raise_application_error(-20001, 'employee not found.');
31 when others then
32 raise_application_error (-20002, 'Unexpected error.');
33 end;
34 end;
35 /
Package body created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> select mypackage.get_employee_name(1) from dual ;
MYPACKAGE.GET_EMPLOYEE_NAME(1)
--------------------------------------------------------------------------------
Jones, Joe
SQL>
SQL> drop table ord;
Table dropped.
SQL> drop table ord_history;
Table dropped.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
]]>