|
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>
|