INSERT INTO zipcodes VALUES (22301, 'Alexandria');
INSERT INTO zipcodes VALUES (33655, 'Tampa');
INSERT INTO zipcodes VALUES (39200, 'Jackson');
INSERT INTO zipcodes VALUES (45601, 'Chillicothe');
INSERT INTO zipcodes VALUES (79201, 'Childress');
INSERT INTO employees VALUES (1003, 'Blake', 39200, '05-MAR-93');
INSERT INTO employees VALUES (1004, 'Buell', 22301, '19-JUN-91');
INSERT INTO employees VALUES (1005, 'Lloyd', 45601, '23-SEP-92');
INSERT INTO employees VALUES (1006, 'Roman', 79201, '07-DEC-95');
INSERT INTO employees VALUES (1007, 'Young', 33655, '31-MAY-89');
INSERT INTO parts VALUES (10602, 'Titanic', 125, 13.75, 20);
INSERT INTO parts VALUES (10603, 'Toy Story', 55, 10.99, 30);
INSERT INTO parts VALUES (10604, 'Scream', 160, 19.99, 40);
INSERT INTO parts VALUES (10605, 'Fight Club', 205, 9.50, 20);
INSERT INTO parts VALUES (10702, 'Jurassic Park', 179, 24.99, 35);
INSERT INTO customers VALUES (1112, 'Alvin', '112 Sumter St.', 33655, '803-123-4567');
INSERT INTO customers VALUES (1113, 'Cody', '115 Wood Rd.', 22301, '803-123-5555');
INSERT INTO customers VALUES (2221, 'Esther', '223 Emory Ln.', 39200, '863-555-4567');
INSERT INTO customers VALUES (2223, 'Grace', '334 Main St.', 45601, '423-111-2225');
INSERT INTO customers VALUES (3330, 'Jayde', '432 River Ave.', 79201, '901-890-4567');
INSERT INTO orders VALUES (1030, 1112, 1003, '11-DEC-95', '15-JAN-96');
INSERT INTO orders VALUES (1024, 1113, 1007, '09-MAR-92', '11-MAR-92');
INSERT INTO orders VALUES (1040, 2221, 1006, '27-FEB-91', '05-MAR-91');
INSERT INTO orders VALUES (1041, 2223, 1006, '23-SEP-93', '15-OCT-96');
INSERT INTO orders VALUES (1001, 3330, 1004, '13-MAY-92', '20-MAY-92');
INSERT INTO odetails VALUES (1030,10702,1);
INSERT INTO odetails VALUES (1024,10604,2);
INSERT INTO odetails VALUES (1040,10605,3);
INSERT INTO odetails VALUES (1041,10602,1);
INSERT INTO odetails VALUES (1001,10602,1);


SQL> CREATE OR REPLACE PROCEDURE
  2      zipreplace(oldzip IN zipcodes.zip%TYPE, newzip IN zipcodes.zip%TYPE) IS
  3
  4  oldCity zipcodes.city%TYPE;
  5
  6  BEGIN
  7
  8    dbms_output.put_line('Replacing old zip ' || oldzip || ' with new zip ' || newzip);
  9
 10    SELECT city
 11    INTO   oldCity
 12    FROM   zipcodes
 13    WHERE  zip = oldzip;
 14
 15    INSERT INTO zipcodes
 16    VALUES (newzip,oldCity);
 17
 18    UPDATE employees
 19    SET    zip = newzip
 20    WHERE  zip = oldzip;
 21
 22    UPDATE customers
 23    SET    zip = newzip
 24    WHERE  zip = oldzip;
 25
 26    DELETE FROM zipcodes
 27    WHERE  zip = oldzip;
 28  END;
 29  /

PROCEDURE created.


SQL> EXEC zipreplace(67227,67226);
Replacing OLD zip 67227 WITH NEW zip 67226

PL/SQL PROCEDURE successfully completed.

SQL> SELECT * FROM customers;

       CNO CNAME                          STREET
---------- ------------------------------ ------------------------------
       ZIP PHONE
---------- ------------
      1111 Charles                        123 Main St.
     67226 316-636-5555

      2222 Bertram                        237 Ash Ave.
     67226 316-689-5555

      3333 Barbara                        111 Inwood St.
     60606 316-111-1234


       CNO CNAME                          STREET
---------- ------------------------------ ------------------------------
       ZIP PHONE
---------- ------------
      1112 Alvin                          112 Sumter St.
     33655 803-123-4567

      1113 Cody                           115 Wood Rd.
     22301 803-123-5555

      2221 Esther                         223 Emory Ln.
     39200 863-555-4567


       CNO CNAME                          STREET
---------- ------------------------------ ------------------------------
       ZIP PHONE
---------- ------------
      2223 Grace                          334 Main St.
     45601 423-111-2225

      3330 Jayde                          432 River Ave.
     79201 901-890-4567


8 ROWS selected.

SQL> SELECT * FROM employees;

       ENO ENAME                                 ZIP HDATE
---------- ------------------------------ ---------- ---------
      1000 Jones                               67226 12-DEC-95
      1001 Smith                               60606 01-JAN-92
      1002 Brown                               50302 01-SEP-94
      1003 Blake                               39200 05-MAR-93
      1004 Buell                               22301 19-JUN-91
      1005 Lloyd                               45601 23-SEP-92
      1006 Roman                               79201 07-DEC-95
      1007 Young                               33655 31-MAY-89

8 ROWS selected.

SQL> host cat areacode.SQL
ACCEPT areacode PROMPT 'Enter an area code > '

DECLARE
  cursor c IS
    SELECT cname,street
        FROM customers
        WHERE phone
        LIKE '&areacode' || '-%';
  cust c%ROWTYPE;

BEGIN
  OPEN c;
  LOOP
    FETCH c INTO cust;
        EXIT WHEN c%NOTFOUND;
        dbms_output.put_line(cust.cname || ' ' || cust.street);
  END LOOP;
  CLOSE c;
END;
/

SQL> START areacode
Enter an area code > 316
OLD   6:    LIKE '&areacode' || '-%';
NEW   6:    LIKE '316' || '-%';
Charles 123 Main St.
Bertram 237 Ash Ave.
Barbara 111 Inwood St.

PL/SQL PROCEDURE successfully completed.