Oracle Procedure无法正常工作？

用户名

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

但是结果是

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."

我执行的两段数据之一应将一张数据插入表中，另一段应调用DBMS_OUTPUT。但是他们都没有成功。

大力水手

您需要从product表中获取停产的产品，然后在IF有条件的情况下使用它。

...
...
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：对于变量/参数，使用与表的列名称不同的名称。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。