sir give me Q. "Write a procedure named expensive_books which finds the details of books with a price is more than the average price in pl/sql"

Question

I tried a lot but i can't get answer

I write this program and I also write "set serveroutput on;" but this is not showing output

and this is code

declare
    name book.book_title%type;
    price book.book_price%type;
    p book.book_price%type;

PROCEDURE expensive_books(name in out book.book_title%type,price in out book.book_price%type,p in out number)
is 
begin
expensive_books(name,price,p);
select avg(book_price),book_price,book_title into p,price,name from book;
 end;
 begin
 for i in 1..5 loop
    if  p > price then
     dbms_output.put_line('Book Name='||name);
    end if; 
    end loop;
 end;
/

Unrelated to the question - funny that "expensive" is defined as "price above average". Many years ago I lived in a communist country, where the dictator was mad that there were still counties with agricultural output below the national average. At least that guy had an excuse - he didn't finish fourth grade. — , May 15 '22 at 14:22
why are you calling expensive_books procedure inside the procedure itself? — OldProgrammer, May 15 '22 at 14:32
And why don't you call the procedure within your block? And where do you find the average price? Also, how is this related to PL/SQL Developer? — William Robertson, May 15 '22 at 15:10

MT0 · Answer 1 · 2022-05-15T15:17:43.003

this is not showing output

You never call the procedure.

If you format the PL/SQL block:

declare
  name book.book_title%type;
  price book.book_price%type;
  p book.book_price%type;

  PROCEDURE expensive_books(
    name in out book.book_title%type,
    price in out book.book_price%type,
    p in out number
  )
  is 
  begin
    expensive_books(name,price,p);
    select book_price,book_title into price,name from book;
  end;
begin
  for i in 1..5 loop
    if  p > price then
      dbms_output.put_line('Book Name='||name);
    end if; 
  end loop;
end;
/

Then you can see that in the body of the PL/SQL block (between the BEGIN and the final END) that you never call the procedure so your code is effectively:

declare
  name book.book_title%type;
  price book.book_price%type;
  p book.book_price%type;
begin
  for i in 1..5 loop
    if  p > price then
      dbms_output.put_line('Book Name='||name);
    end if; 
  end loop;
end;
/

Since p and price are not initialised then they will have NULL values and the IF statement is effectively IF NULL > NULL THEN and that will never be true (since NULL compared to anything is never true) so nothing will be output.

If you want to display something then you need to call the procedure and set the value of p, price and name (and your procedure never sets the p value and also calls itself at every iteration so would get into a infinite loop if you did call it).

score 0 · Answer 2 · answered May 20 '22 at 14:28

Better to do the querying in a single query:

begin
  for x in (select book_title, book_price
            from books
            where book_price > (select avg(book_price)
                                from books)
            and rownum <=5) loop

    dbms_output.put_line(x.book_title);
  end loop;
end;

sir give me Q. "Write a procedure named expensive_books which finds the details of books with a price is more than the average price in pl/sql"

2 Answers2