Returns the total from an order number being passed in. : Utility Procedure « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Utility Procedure

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>
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> CREATE OR REPLACE PROCEDURE order_process(p_ordering_num NUMBER) IS
  2     CURSOR cur_get_order (p_ord_num ord.order_id%TYPE) IS
  3        SELECT *
  4        FROM   ord
  5        WHERE  order_id = p_ord_num;
  6     lv_order_rec cur_get_order%ROWTYPE;
  7  BEGIN
  8     OPEN cur_get_order (p_ordering_num);
  9     FETCH cur_get_order INTO lv_order_rec;
 10     DBMS_OUTPUT.PUT_LINE('Order Total: ' ||
 11        TO_CHAR(lv_order_rec.total));
 12     CLOSE cur_get_order;
 13     EXCEPTION
 14        WHEN OTHERS THEN
 15           RAISE_APPLICATION_ERROR(-20100, 'Order Problem.', FALSE);
 16  END;
 17  /

Procedure created.

SQL>
SQL> show error
No errors.
SQL>
SQL>
SQL> drop table ord;

Table dropped.

SQL>