1 CREATE PROCEDURE fixHomePages AS 2 3 /* adds http:// to the beginning of any homepages in 4 artist that do not have the scheme */ 5 6 home Artist.homepage%TYPE; 7 CURSOR curs IS 8 SELECT homepage 9 FROM Artist 10 FOR UPDATE; 11 BEGIN 12 OPEN curs; 13 LOOP 14 FETCH curs INTO home; 15 EXIT WHEN curs%NOTFOUND; 16 IF home NOT LIKE 'http://%' THEN 17 home := 'http://' || home; 18 UPDATE Artist SET homepage=home WHERE CURRENT OF curs; 19 END IF; 20 END LOOP; 21 CLOSE curs; 22* END; Procedure created. Elapsed: 00:00:00.17 HOMEPAGE -------------------------------------------------------------------------------- www.infectious.com/far www.enjoyincubus.com www.deftones.com www.paparoach.com www.willhaven.com www.officialfilter.com slingshot.stanford.edu www.stanford.edu/~rkapla www.stanford.edu/~jbyron 9 rows selected. Elapsed: 00:00:00.02 1* BEGIN fixHomePages(); END; PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 HOMEPAGE -------------------------------------------------------------------------------- http://www.infectious.com/far http://www.enjoyincubus.com http://www.deftones.com http://www.paparoach.com http://www.willhaven.com http://www.officialfilter.com http://slingshot.stanford.edu http://www.stanford.edu/~rkapla http://www.stanford.edu/~jbyron 9 rows selected. Elapsed: 00:00:00.01 Rollback complete. Elapsed: 00:00:00.04 1 CREATE PROCEDURE fixHomeTown(defaultTown VARCHAR) AS 2 3 /* adds ', CA' to any towns without state names and 4 inserts default whenever hometown is null */ 5 6 currTown Artist.hometown%TYPE; 7 CURSOR curs IS 8 SELECT hometown 9 FROM Artist 10 FOR UPDATE; 11 BEGIN 12 OPEN curs; 13 LOOP 14 FETCH curs INTO currTown; 15 EXIT WHEN curs%NOTFOUND; 16 IF currTown IS NULL THEN 17 currTown := defaultTown; 18 UPDATE Artist SET hometown=currTown WHERE CURRENT 19 OF curs; 20 END IF; 21 IF currTown NOT LIKE '%, __' THEN 22 UPDATE Artist SET hometown= hometown || ', CA' 23 WHERE CURRENT OF curs; 24 END IF; 25 END LOOP; 26 CLOSE curs; 27* END; Procedure created. Elapsed: 00:00:00.18 NAME -------------------------------------------------------------------------------- HOMETOWN -------------------------------------------------------------------------------- Far Sacramento Incubus Los Angeles Deftones Sacramento NAME -------------------------------------------------------------------------------- HOMETOWN -------------------------------------------------------------------------------- Papa Roach Vacaville WillHaven Sacramento Filter Los Angeles NAME -------------------------------------------------------------------------------- HOMETOWN -------------------------------------------------------------------------------- Slingshot The Stanleys Arcadia 9 rows selected. Elapsed: 00:00:00.01 1* BEGIN fixHomeTown('Stanford'); END; PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 NAME -------------------------------------------------------------------------------- HOMETOWN -------------------------------------------------------------------------------- Far Sacramento, CA Incubus Los Angeles, CA Deftones Sacramento, CA NAME -------------------------------------------------------------------------------- HOMETOWN -------------------------------------------------------------------------------- Papa Roach Vacaville, CA WillHaven Sacramento, CA Filter Los Angeles, CA NAME -------------------------------------------------------------------------------- HOMETOWN -------------------------------------------------------------------------------- Slingshot Stanford, CA The Stanleys Stanford, CA Arcadia Stanford, CA 9 rows selected. Elapsed: 00:00:00.01 Rollback complete. Elapsed: 00:00:00.04