0

I'm trying to create a procedure that takes a parameter (ID number) and output some information about that particular item. Assume that I have two tables: product( id, name, desc, price) line_item (prod_id, total, quantity, cust_id)

This is what I have so far:

CREATE OR REPLACE PROCEDURE product_query (p_id IN Number)
RETURN NUMBER
  IS
       v_product_id     NUMBER;
       v_description    NUMBER;
       v_sub    NUMBER; 
       v_total_quantity    NUMBER;
       v_order_count NUMBER;
BEGIN

SELECT p.product_id, sum(l.subtotal), sum(l.quantity), count(*)
INTO v_product_id, v_sub, v_total_quantity, v_order_count
FROM line_item l, product p
       WHERE p.product_id = p_id
       AND
       l.product_id = l.product_id
       group by p.product_id;
       DBMS_OUTPUT.PUT_LINE('ID: ' || p_product_id);
       DBMS_OUTPUT.PUT_LINE('Subtotal: ' || v_sub);
       DBMS_OUTPUT.PUT_LINE('Total Qt: ' || v_total_quantity);
       DBMS_OUTPUT.PUT_LINE('Total Order: ' || v_order_count);
END product_query;

But it doesn't display any output. I don't see any issue with the code. Should I change the way I output the information? Is there a better way than DBMS_OUTPUT.PUT_LINE? Thanks,

seemvision
  • 242
  • 1
  • 2
  • 12
  • There are some typos in your code. If your procedure works, I suppose your editor doesn't show the output. You may try SET SERVEROUTPUT ON – Multisync Dec 08 '14 at 21:26
  • Thanks, but isn't that a SQL*Plus command? I'm using SQL Developer UI. Also, I think the issue is due to RETURN NUMBER at top, but it requires a return after IS. – seemvision Dec 08 '14 at 21:38
  • 1
    As Justin Cave points out in his response: You are declaring a `PROCEDURE` not a `FUNCTION`. It's only the latter, which does `RETURN` a value. If you want to get back something from a procedure, you need to use `OUT` or `IN OUT` parameters in its parameter list. Like `CREATE [OR REPLACE] PROCEDURE procName (paramName OUT DataType) ` or `CREATE [OR REPLACE] PROCEDURE procName (paramName IN OUT DataType)` – Abecee Dec 09 '14 at 03:54

1 Answers1

1

I would assume that you're getting a compilation error when you try to create the procedure-- procedures do not return anything so RETURN NUMBER isn't valid, that would only be valid in a function. If you're getting a compilation error, though, posting the error would be helpful.

Once the procedure compiles, I'd expect you'd get a runtime error (indicating that your query returned too many rows) when you called the procedure because the join is wrong-- you're joining the line_item table to itself rather than to the product table. 

AND l.product_id = l.product_id

should, presumably, be

AND l.product_id = p.product_id

This is one of the reasons that I prefer the SQL 99 join syntax over the old syntax you're using here-- it makes it much easier to differentiate join conditions from filter predicates which makes it easier to notice this sort of error.

Once your procedure compiles and can be called successfully, you'd need to enable output. That's going to be done differently in different client tools but here's a question that walks through enabling dbms_output in SQL*Plus and SQL Developer.

Of course, in real code, you almost certainly wouldn't define a procedure whose only purpose was to write to dbms_output because you generally wouldn't assume that anyone would ever see that data. dbms_output is really only used for quick-and-dirty debugging. I assume, though, that you are a student and that this is part of a homework assignment.

Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384