Re: Update transaction
Date: Tue, 04 Apr 2000 09:20:37 +0200
Message-ID: <38E997C4.80A0BC6F_at_fastnet.net.mt>
First of all thanks for answering my mail, but With making new_book_price as a parameter, I would only be able to change one book. If it is possible I would like to change all books with different prices.
Regards
Jonathan
kskasi_at_hotmail.com wrote:
Hi thereReceived on Tue Apr 04 2000 - 09:20:37 CESTPass :new_book_price as a parameter to the procedure. SO your procedure should look something like this
create or replace Procedure Tran2 (publish IN book.publisher%type,
p_book_price IN book.price%type) is
Type price_data_record is record
(ISBN_NO book.ISBN%TYPE,
book_name book.name%TYPE,
author_name book.author%TYPE,
book_price book.price%type,
new_book_price book.price%type);
price_record price_data_record;
CURSOR c2 is
Select ISBN, name, author, price
from book
where book.publisher = publish;
Begin
Open c2;
LOOP
Fetch c2 into price_record;
exit when C2%NOTFOUND;
DBMS_output.Put_line('ISBN No '|| price_record.ISBN_NO);
DBMS_output.Put_line('Name of Book '|| price_record.book_name);
DBMS_output.Put_line('Author '|| price_record.author_name);
DBMS_output.Put_line('Price Lm'|| price_record.book_price);
DBMS_Output.Put_line('New Price'||p_book_price);
Hope this helpsBye...kasi
Jonathan Bartolo wrote:Can I update a field of a table from a procedure. I have this procedure
create Procedure Tran2 (publish IN book.publisher%type) is
Type price_data_record is record
(ISBN_NO book.ISBN%TYPE,
book_name book.name%TYPE,
author_name book.author%TYPE,
book_price book.price%type,
new_book_price book.price%type);
price_record price_data_record;
CURSOR c2 is
Select ISBN, name, author, price
from book
where book.publisher = publish;
Begin
Open c2;
LOOP
Fetch c2 into price_record;
exit when C2%NOTFOUND;
DBMS_output.Put_line('ISBN No '|| price_record.ISBN_NO);
DBMS_output.Put_line('Name of Book '|| price_record.book_name);
DBMS_output.Put_line('Author '|| price_record.author_name);
DBMS_output.Put_line('Price Lm'|| price_record.book_price);
DBMS_Output.Put_line('New Price'||:new_book_price);This line is the problem ... I need to update this record with a new price. But the user has to input the new price.
When I try to compile this procedure it telling me that i have a bad Variable bind
Can you help me pleaseRegards
Jonathan