Re: Moving a sqlplus script to pl/sql question

connect usr/pwd_at_dbase
PROMPT Enter Number to be updated:
ACCEPT Vnum char FORMAT 'A7'

set serveroutput on
declare
  v_num table.col2%type := UPPER('&Vnum'); begin
  update table
  set col_1 = 'N'
  where col_2 = lv_num
  and col_1 = 'Y';
  dbms_output.put_line('updating 2nd table');   update table2
  set col_1 = 'N'
  where col_2 = lv_num
  and col_1 = 'Y';
  dbms_output.put_line('deleting from table 3');   delete from table3
  where col_2 = lv_num;
  dbms_output.put_line('committing');
  commit;
end;
/

"Glen A Stromquist" <gstromquist_at_hotmail.com> wrote in message news:fJHz7.12545$Og4.1805000_at_news0.telusplanet.net...
> 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 - 21:50:46 CDT