Mega Code Archive
Use update statement in stored procedure
<![CDATA[
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> CREATE OR REPLACE PROCEDURE reset_ship_dates (date_shipped IN DATE) IS
2 BEGIN
3 UPDATE ord
4 SET date_shipped = NULL
5 WHERE date_shipped = date_shipped;
6 COMMIT;
7 END reset_ship_dates;
8 /
Procedure created.
SQL>
SQL> drop table ord;
Table dropped.
SQL>
]]>