Re: Update transaction

What I wish to do is that first the procedure lists all the books of the publisher and each time the books are listed the user inputs the price. If I place the price as a parameter, the user has to input the ISBN number and the price and thus he will only input one price. If I make the price as a parameter can I call the procedure for all the books in the database.. ??

Regards
Jonathan

kskasi_at_hotmail.com wrote:

Hi there

I can't really understand the exact situation. But if you want the price of the book to be updated upon user input, then I assume the user could input only one book price at a time. Even if you have a Multi record block , you could run the procedure for every single line that is updated.

regards
kasi

Jonathan Bartolo wrote:

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 there

Pass :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 helps

Bye...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 please

Regards
Jonathan