Oracle Procedure does not work as it should?

user_13862969 0
Viewed 71
SPOOL /Users/gongjiaxi/Desktop/solution2
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 200
SET SERVEROUTPUT ON
create PROCEDURE INSERT_ORDER_DETAIL(order_id ORDER_DETAIL.ORDER_ID%TYPE,
                                     product_name ORDER_DETAIL.PRODUCT_NAME%TYPE,
                                     unit_price ORDER_DETAIL.UNIT_PRICE%TYPE,
                                     quantity ORDER_DETAIL.QUANTITY%TYPE,
                                     discount ORDER_DETAIL.DISCOUNT%TYPE)
    IS
    discontinued PRODUCT.DISCONTINUED%TYPE;
BEGIN
    IF discontinued = 'N' THEN
        INSERT INTO ORDER_DETAIL(ORDER_ID, PRODUCT_NAME, UNIT_PRICE, QUANTITY, DISCOUNT)
        VALUES (order_id, product_name, unit_price, quantity, discount);
    end if;
    IF discontinued = 'Y' THEN
        DBMS_OUTPUT.PUT_LINE('The product is not availiable');
    end if;
end;
/
EXECUTE INSERT_ORDER_DETAIL(1000,'Chai',18.00,1002,0.9);
EXECUTE INSERT_ORDER_DETAIL(1000,'Alice Mutton', 39.00,1000,0.8);

SPOOL OFF

But the result is

Procedure INSERT_ORDER_DETAIL compiled

SQL> EXECUTE INSERT_ORDER_DETAIL(1000,'Chai',18.00,1002,0.9);

PL/SQL procedure successfully completed.

SQL> EXECUTE INSERT_ORDER_DETAIL(1000,'Alice Mutton', 39.00,1000,0.8);

PL/SQL procedure successfully completed."

One of the two pieces of data I executed should insert one piece of data into the table, and the other one should call DBMS_OUTPUT. But none of them succeeded.

sqlplus plsql oracle
2 Answers

You declared the variable discontinued PRODUCT.DISCONTINUED%TYPE; without initializing it with a value, so it's vaule is NULL.
Then in the procedure you are checking the value of this variable in the IF statements
but NULL is neither Y not N, so the procedure is doining nothing.

user_25683410

You need to fetch wether product is discontinued from product table and then use that in IF conditions.

...
...
IS
    discontinued PRODUCT.DISCONTINUED%TYPE;
BEGIN
Select discontinued -- this is column name from your product table
Into discontinued -- this is a variable declared in your procedure
From product 
where name = product_name;
-- name is column name of the product table
-- product_name is input parameter of the procedure

IF discontinued = 'N' THEN
...
...

Suggestion: use different name for variable/parameter than column name of the table.

user_115656290
Related