Product check : Utility Package « Function Procedure Packages « Oracle PL/SQL Tutorial






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.








27.27.Utility Package
27.27.1.Timer package
27.27.2.Match a date format
27.27.3.Validate all objects in a schema
27.27.4.Process date value
27.27.5.Get full name package
27.27.6.Product check
27.27.7.Check the code version
27.27.8.Audit package
27.27.9.Create a package to handle display, update, delete and insert operations
27.27.10.Counter package