Use update statement in stored procedure : Update « PL SQL Programming « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Programming
3. Update

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