Moving a sqlplus script to pl/sql question

I have a simple sql script to update two tables and delete a row from another, and get's its info from a user entered variable in the where clause.
I realize that for my purposes, this is probably left better in a sqlplus script, but I want to expand my knowlege of PL/SQL and what better way than hands on right?

The script is as follows, and I thought I'd create a pl/sql procedure to do the same thing, with some simple error handling, such as if the value being updated was not what it was supposed to be, ie - the user entered a value for 'Vnum' that did not have a col_1 set as 'Y', it would return an error message telling him that and prompting to exit the procedure.

In the script 2 tables are updated with the value 'N' and a row is deleted from a third table that originally had a corresponding record inserted when the value had been set to 'Y' on creation (of the record). appreciate it if anyone could give me kick-start here!

change_and_del_record.sql

connect usr/pwd_at_dbase
PROMPT Enter Number to be updated:
ACCEPT Vnum char FORMAT 'A7'
update table
set col_1 = 'N'
where col_2= UPPER('&Vnum')
and col_1 = 'Y'
/

PROMPT updating 2nd table
update table2
set col_1 = 'N'
where col_2= UPPER('&Vnum')
and col_1 = 'Y'
/

PROMPT deleting from table 3
delete from table3
where col_2 = UPPER('&Vnum')
/

PAUSE press enter to commit the changes to the database commit
/

thanks in advance! Received on Thu Oct 18 2001 - 16:21:15 CDT