|
SQL>
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>
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE procesproducts IS
2 TYPE type_prod_table IS TABLE OF product%ROWTYPE
3 INDEX BY BINARY_INTEGER;
4 pvg_prod_table type_prod_table;
5 PROCEDURE populate_prod_table;
6 PROCEDURE check_product_id (p_prod_id_num product.product_id%TYPE);
7 PROCEDURE check_product_name (p_prod_name_txt product.
8 product_name%TYPE);
9 END procesproducts;
10 /
Package created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY procesproducts IS
2 PROCEDURE populate_prod_table IS
3 CURSOR cur_product IS
4 SELECT *
5 FROM product;
6 BEGIN
7 pvg_prod_table.DELETE;
8 FOR lv_prod_rec IN cur_product LOOP
9 pvg_prod_table(lv_prod_rec.product_id).product_id := lv_prod_rec.product_id;
10 pvg_prod_table(lv_prod_rec.product_id).product_name := lv_prod_rec.product_name;
11 pvg_prod_table(lv_prod_rec.product_id).short_desc := lv_prod_rec.short_desc;
12 pvg_prod_table(lv_prod_rec.product_id).suggested_wholesale_price := lv_prod_rec.suggested_wholesale_price;
13 END LOOP;
14 EXCEPTION
15 WHEN OTHERS THEN
16 RAISE_APPLICATION_ERROR(-20100,
17 'Error in procedure POPULATE_PROD_TABLE.', FALSE);
18 END populate_prod_table;
19
20 PROCEDURE check_product_id(p_prod_id_num product.product_id%TYPE) IS
21 BEGIN
22 IF pvg_prod_table.EXISTS(p_prod_id_num) THEN
23 DBMS_OUTPUT.PUT_LINE('Product ID: ' ||
24 pvg_prod_table(p_prod_id_num).product_id );
25 DBMS_OUTPUT.PUT_LINE('Product Name: ' ||
26 pvg_prod_table(p_prod_id_num).product_name );
27 DBMS_OUTPUT.PUT_LINE('Description: ' ||
28 pvg_prod_table(p_prod_id_num).short_desc );
29 DBMS_OUTPUT.PUT_LINE('Wholesale Price: ' ||
30 TO_CHAR(pvg_prod_table(p_prod_id_num).
31 suggested_wholesale_price, '$9999.00'));
32 DBMS_OUTPUT.PUT_LINE(CHR(10));
33 ELSE
34 DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_prod_id_num) || ' is invalid.');
35 END IF;
36 EXCEPTION
37 WHEN OTHERS THEN
38 RAISE_APPLICATION_ERROR(-20102,
39 'Error in procedure CHECK_PRODUCT_ID.', FALSE);
40 END check_product_id;
41 PROCEDURE check_product_name
42 (p_prod_name_txt product.product_name%TYPE) IS
43 lv_index_num NUMBER;
44 lv_match_bln BOOLEAN := FALSE;
45 BEGIN
46 IF pvg_prod_table.COUNT <> 0 THEN
47 lv_index_num := pvg_prod_table.FIRST;
48 LOOP
49 IF (INSTR(UPPER(pvg_prod_table(lv_index_num).product_name),UPPER(p_prod_name_txt)) > 0) THEN
50 lv_match_bln := TRUE;
51 DBMS_OUTPUT.PUT_LINE('Product ID: ' ||
52 pvg_prod_table(lv_index_num).product_id );
53 DBMS_OUTPUT.PUT_LINE('Product Name: ' ||
54 pvg_prod_table(lv_index_num).product_name );
55 DBMS_OUTPUT.PUT_LINE('Description: ' ||
56 pvg_prod_table(lv_index_num).short_desc );
57 DBMS_OUTPUT.PUT_LINE('Wholesale Price: ' ||
58 TO_CHAR(pvg_prod_table(lv_index_num).
59 suggested_wholesale_price, '$9999.00'));
60 DBMS_OUTPUT.PUT_LINE(CHR(10));
61 END IF;
62 EXIT WHEN (lv_index_num = pvg_prod_table.LAST) OR
63 lv_match_bln;
64 lv_index_num := pvg_prod_table.NEXT(lv_index_num);
65 END LOOP;
66 IF NOT lv_match_bln THEN
67 DBMS_OUTPUT.PUT_LINE('Product: ' || p_prod_name_txt ||
68 ' is invalid.');
69 END IF;
70 ELSE
71 DBMS_OUTPUT.PUT_LINE('There are no products in the table.');
72 END IF;
73 EXCEPTION
74 WHEN OTHERS THEN
75 RAISE_APPLICATION_ERROR(-20102,
76 'Error in procedure CHECK_PRODUCT_NAME.', FALSE);
77 END check_product_name;
78
79 end procesproducts;
80 /
Package body created.
SQL> show error
No errors.
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL> drop package procesproducts;
Package dropped.
|