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] 删除。
我来说两句