thanks spence!
"Spencer" <spencerp_at_swbell.net> wrote in message
news:ayMz7.611$JI1.365291674_at_newssvr11.news.prodigy.com...
> 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 Fri Oct 19 2001 - 09:29:12 CDT
