|
SQL>
SQL> CREATE TABLE product
2 (product_id NUMBER(7),
3 product_name VARCHAR2(50),
4 short_desc VARCHAR2(255),
5 longtext_id NUMBER(7),
6 image_id NUMBER(7),
7 suggested_wholesale_price NUMBER(11, 2),
8 wholesale_units VARCHAR2(25)
9 );
Table created.
SQL>
SQL>
SQL> INSERT INTO product VALUES (10011, 'Product A', 'BOOT', 518, 1001, 150, NULL);
1 row created.
SQL> INSERT INTO product VALUES (10012, 'Product B', 'SKI', 519, 1002, 200, NULL);
1 row created.
SQL> INSERT INTO product VALUES (10013, 'Product C', 'SKI BOOT', 520, 1003, 410, NULL);
1 row created.
SQL> INSERT INTO product VALUES (10021, 'Product D', 'POLE', 528, 1011, 16.25, NULL);
1 row created.
SQL> INSERT INTO product VALUES (10022, 'Product E', 'Cat', 529, 1012, 21.95, NULL);
1 row created.
SQL> INSERT INTO product VALUES (10023, 'Product F', 'Dog', 530, 1013, 40.95, NULL);
1 row created.
SQL> INSERT INTO product VALUES (20106, 'Product G', 'Bear', 613, NULL, 11, NULL);
1 row created.
SQL> INSERT INTO product VALUES (20108, 'Product H', 'BALL', 615, NULL, 28, NULL);
1 row created.
SQL> INSERT INTO product VALUES (20201, 'Product I', 'NET', 708, NULL, 123, NULL);
1 row created.
SQL> INSERT INTO product VALUES (20510, 'Product J', 'PADS, PAIR', 1017, NULL, 9, NULL);
1 row created.
SQL> INSERT INTO product VALUES (20512, 'Product K', 'PADS, PAIR', 1019, NULL, 8, NULL);
1 row created.
SQL>
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL> INSERT INTO employee VALUES (1, 'V', 'Ben', 'cv',to_date('03-MAR-90 8:30', 'dd-mon-yy hh24:mi'),NULL, NULL, 'PRESIDENT', 50, 2500, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (2, 'N', 'Haidy', 'ln', '08-MAR-90', NULL,1, 'VP, OPERATIONS', 41, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (3, 'N', 'Molly', 'mn', '17-JUN-91',NULL, 1, 'VP, SALES', 31, 1400, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (4, 'S', 'Mark', 'mq', '07-APR-90',NULL, 1, 'VP, FINANCE', 10, 1450, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (5, 'R', 'AUDRY', 'ar', '04-MAR-90',NULL, 1, 'VP, ADMINISTRATION', 50, 1550, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (6, 'U', 'MOLLY', 'mu', '18-JAN-91',NULL, 2, 'WAREHOUSE MANAGER', 41, 1200, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (7, 'M', 'ROBERTA', 'rm', '14-MAY-90',NULL, 2, 'WAREHOUSE MANAGER', 41, 1250, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (8, 'B', 'BEN', 'ry', '07-APR-90', NULL, 2,'WAREHOUSE MANAGER', 41, 1100, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (9, 'C', 'Jane', 'ac', '09-FEB-92',NULL, 2, 'WAREHOUSE MANAGER', 41, 1300, NULL);
1 row created.
SQL> INSERT INTO employee VALUES (10, 'H', 'Mart', 'mh', '27-FEB-91', NULL, 2,'WAREHOUSE MANAGER', 41, 1307, NULL);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE ord
2 (order_id NUMBER(7),
3 customer_id NUMBER(7),
4 date_ordered DATE,
5 date_shipped DATE,
6 sales_rep_id NUMBER(7),
7 total NUMBER(11, 2),
8 payment_type VARCHAR2(6),
9 order_filled VARCHAR2(1)
10 );
Table created.
SQL>
SQL> INSERT INTO ord VALUES (100, 204, '31-AUG-92', '10-SEP-92', 11, 601100, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (101, 205, '31-AUG-92', '15-SEP-92', 14, 8056.6, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (102, 206, '01-SEP-92', '08-SEP-92', 15, 8335, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (103, 208, '02-SEP-92', '22-SEP-92', 15, 377, 'CASH', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (104, 208, '03-SEP-92', '23-SEP-92', 15, 32430, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (105, 209, '04-SEP-92', '18-SEP-92', 11, 2722.24, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (106, 210, '07-SEP-92', '15-SEP-92', 12, 15634, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (107, 211, '07-SEP-92', '21-SEP-92', 15, 142171, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (108, 212, '07-SEP-92', '10-SEP-92', 13, 149570, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (109, 213, '08-SEP-92', '28-SEP-92', 11, 1020935, 'CREDIT', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (110, 214, '09-SEP-92', '21-SEP-92', 11, 1539.13, 'CASH', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (111, 204, '09-SEP-92', '21-SEP-92', 11, 2770, 'CASH', 'Y');
1 row created.
SQL> INSERT INTO ord VALUES (112, 210, '31-AUG-92', '10-SEP-92', 12, 550, 'CREDIT', 'Y');
1 row created.
SQL>
SQL>
SQL> CREATE TABLE customer
2 (customer_id NUMBER(7),
3 customer_name VARCHAR2(50),
4 phone VARCHAR2(15),
5 address VARCHAR2(400),
6 city VARCHAR2(35),
7 state VARCHAR2(30),
8 country VARCHAR2(30),
9 zip_code VARCHAR2(10),
10 credit_rating VARCHAR2(9),
11 sales_rep_id NUMBER(7),
12 region_id NUMBER(7),
13 comments VARCHAR2(255),
14 preferred_customer VARCHAR2(1) DEFAULT 'N' NOT NULL,
15 shipping_method VARCHAR2(1) DEFAULT 'M' NOT NULL);
Table created.
SQL>
SQL> INSERT INTO customer VALUES (201, 'Jane', '111-1111', '7 AVE','SAO', NULL, 'BRAZIL', NULL, 'EXCELLENT',12, 2, 'A', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (202, 'Todd', '222-2222', '6 BLVD.','OSAKA', NULL, 'JAPAN', NULL, 'POOR', 14, 4, 'B', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (203, 'Sharon', '333-3333', '1 STREET', 'NEW DELHI', NULL, 'INDIA', NULL, 'GOOD', 14, 4,'C', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (204, 'Hong', '444-4444', '2 STREET','SEATTLE', 'WASHINGTON', 'USA', '98101', 'EXCELLENT',11, 1, NULL, 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (205, 'Anderson','555-5555', '5 ROAD', 'HONG KONG', NULL, NULL,NULL, 'EXCELLENT', 15, 4, NULL, 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (206, 'Bob', '666-6666', '1 ROAD','CANNES', NULL, 'FRANCE', NULL, 'EXCELLENT', 15, 5,'D', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (207, 'Cat', '777-7777', '6 STREET','LAGOS', NULL, 'NIGERIA', NULL, 'GOOD', NULL, 3, NULL,'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (208, 'Doge', '888-8888', '4 RASSE', 'STUTTGART', NULL, 'GERMANY', NULL, 'GOOD', 15, 5,'E', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (209, 'Black', '999-9999', '2 MAR','SAN PEDRO DE MACON''S', NULL, 'DOMINICAN REPUBLIC',NULL, 'EXCELLENT', 11, 1, NULL, 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (210, 'Red', '000-0000', '3 ARO','NOGALES', NULL, 'MEXICO', NULL, 'EXCELLENT', 12, 2,'Customer is difficult to reach by phone. Try mail.','N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (211, 'Ted', '123-1231', '7 MOD', 'PRAGUE',NULL, 'CZECHOSLOVAKIA', NULL, 'EXCELLENT', 15, 5, NULL,'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (212, 'Homas', '124-1234', '5 COR','ALEXANDRIA', NULL, 'EGYPT', NULL, 'EXCELLENT', 13, 3,'F', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (213, 'Look', '555-6281', '4 STREET', 'SAN FRANCISCO', 'CA', 'USA', '94117','EXCELLENT', 11, 1, 'G', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (214, 'Yellow', '555-7171', '4 STREET','BUFFALO', 'NY', 'USA', '14202', 'POOR', 11, 1, NULL, 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (215, 'White', '337-3892', '6 YEK','SAINT PETERSBURG', NULL, 'RUSSIA', NULL, 'POOR',15, 5, 'T', 'N', 'M');
1 row created.
SQL>
SQL> CREATE OR REPLACE PACKAGE name_pkg IS
2 TYPE type_name_rec IS RECORD (name VARCHAR2(100));
3 TYPE type_name_refcur IS REF CURSOR RETURN type_name_rec;
4 FUNCTION open_name (p_table_txt IN VARCHAR2, p_id_num IN NUMBER) RETURN type_name_refcur;
5 FUNCTION get_name (p_table_txt IN VARCHAR2, p_id_num IN NUMBER) RETURN VARCHAR2;
6 END name_pkg;
7 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY name_pkg IS
2 FUNCTION open_name (p_table_txt IN VARCHAR2, p_id_num IN NUMBER)
3 RETURN type_name_refcur IS
4 lv_table_txt VARCHAR2(100) := UPPER(p_table_txt);
5 lv_name_rec type_name_refcur;
6 BEGIN
7 IF lv_table_txt = 'EMPLOYEE' THEN
8 OPEN lv_name_rec FOR
9 SELECT last_name || ', '|| first_name
10 FROM employee
11 WHERE employee_id = p_id_num;
12 ELSIF lv_table_txt = 'CUSTOMER' THEN
13 OPEN lv_name_rec FOR
14 SELECT customer_name
15 FROM customer
16 WHERE customer_id = p_id_num;
17 ELSIF lv_table_txt = 'PRODUCT' THEN
18 OPEN lv_name_rec FOR
19 SELECT product_name
20 FROM product
21 WHERE product_id = p_id_num;
22 ELSE
23 RAISE_APPLICATION_ERROR (-20222,
24 'Invalid table specified for name request.');
25 END IF;
26 RETURN lv_name_rec;
27 END open_name;
28 FUNCTION get_name (p_table_txt IN VARCHAR2, p_id_num IN NUMBER)
29 RETURN VARCHAR2 IS
30 lv_name_rec type_name_rec;
31 lv_name_refcur type_name_refcur;
32 BEGIN
33 lv_name_refcur := open_name(p_table_txt, p_id_num);
34 FETCH lv_name_refcur INTO lv_name_rec;
35 IF (lv_name_refcur%NOTFOUND) THEN
36 CLOSE lv_name_refcur;
37 RAISE NO_DATA_FOUND;
38 ELSE
39 CLOSE lv_name_refcur;
40 END IF;
41 RETURN lv_name_rec.name;
42 END get_name;
43 END name_pkg;
44 /
Package body created.
SQL> show error
No errors.
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL> drop table ord;
Table dropped.
SQL>
SQL> drop table customer;
Table dropped.
|