Package Assignment  

Design PL/SQL packages for the four remaining tables in the Premiere Products database.  Your PL/SQL packages must allow the user to add, delete, and update data in all four tables (sales_rep, part, orders, order_line).  

You packages must allow the user to update and insert all columns except primary key, last_update, and user. 

Your package must include   Exception/Error Handling.

Due: July 27th, 2004

Turn In: 
1. Source Code
2. Testing commands and output

Testing:

Don’t write a user interface.  To test, just hard code the values to the procedure or function call.

SQL> EXECUTE :g_cust_nr := customer_pkg.add_customer(‘Smith’, ‘John’, ‘1 Main St’, ‘SF’, ‘CA’, ‘94123’, 1000);

SQL> PRINT :g_cust_nr;   -- Assuming some positive number, it is ok.

SQL> SELECT * FROM Customer WHERE customer_number = :g_cust_nr

SQL> EXECUTE :g_retval := customer_pkg.del_customer(123);

SQL> PRINT :g_retval;

SQL> SELECT * FROM Customer WHERE customer_number = 123;

Another Example:

SQL> EXEC :cust_nr := manage_customers.add ('Smith', 'John', '1 Main St', 'S.F.', 'Ca', '94123', 500);

SQL> SELECT * FROM customers WHERE customer_number = :cust_nr;

SQL> EXEC manage_customers.update (10, 'Smyth', 'John', '12 Market', 'S.F.', 'Ca', '94123', 1000);

SQL> SELECT * FROM customers WHERE customer_number = 10;

SQL> EXEC manage_customers.delete(10);

SQL>SELECT * FROM customers WHERE customer_number = 10;