问题
作为25周年生日庆祝活动的一部分，任何订单金额在R1000至R3000之间，均可获得总额2%的折扣。声明一个参数化游标，该游标接收最小和最大金额作为参数，并通过锁定事务来更新总金额。对整个结果集进行迭代，并将总数减少2%
您的输出必须类似于下面的输出。
PL/SQL procedure successfully completed.
SQL> /
ORDERS THAT ARE BETWEEN R1000 AND R3000.00, RECEIVE 2% DISCOUNT
-------------------------------------------------------------------
ORDER NO. 105 HAVE A TOTAL AMOUNT OF: R2,314.13
ORDER NO. 105 HAVE A NEW TOTAL ANOUNT OF : R2,267.85
ORDER NO. 110 HAVE A TOTAL AMOUNT OF: R1,308.38
ORDER NO. 110 HAVE A NEW TOTAL ANOUNT OF : R1,282.21
ORDER NO. 111 HAVE A TOTAL AMOUNT OF: R2,354.71
ORDER NO. 111 HAVE A NEW TOTAL ANOUNT OF : R2,307.62
PL/SQL procedure successfully completed.
以下是我的代码：
SET SERVEROUTPUT ON
DECLARE
CURSOR ord_cur(p_min NUMBER,p_max NUMBER) IS
SELECT id,total
FROM s_ord
WHERE total BETWEEN p_min AND p_max
FOR UPDATE OF total NOWAIT;
ord_rec ord_cur%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('ORDERS THAT ARE BETWEEN R1000 AND R3000.00,RECEIVE 2% DISCOUNT');
DBMS_OUTPUT.PUT_LINE('********************************************');
OPEN ord_cur(1000,3000);
LOOP
FETCH ord_cur
INTO ord_rec;
EXIT WHEN ord_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ORDER NO '|| ord_rec.id ||' HAVE A TOTAL AMOUNT OF: '||TO_CHAR((ord_rec.total),'L9,999.09'));
IF ord_rec.total >= 1000 AND ord_rec.Total <= 3000 THEN
UPDATE s_ord
SET total = ord_rec.total*0.02
WHERE CURRENT OF ord_cur;
DBMS_OUTPUT.PUT_LINE('ORDER NO '|| ord_rec.id ||' HAVE A NEW TOTAL AMOUNT OF: '||TO_CHAR((ord_rec.total),'L9,999.09'));
END IF;
END LOOP;
END;
/
以下是我的输出，与问题的输出不同：
ORDERS THAT ARE BETWEEN R1000 AND R3000.00,RECEIVE 2% DISCOUNT
********************************************
ORDER NO 105 HAVE A TOTAL AMOUNT OF: $2,722.24
ORDER NO 105 HAVE A NEW TOTAL AMOUNT OF: $2,722.24
ORDER NO 110 HAVE A TOTAL AMOUNT OF: $1,539.13
ORDER NO 110 HAVE A NEW TOTAL AMOUNT OF: $1,539.13
ORDER NO 111 HAVE A TOTAL AMOUNT OF: $2,770.00
ORDER NO 111 HAVE A NEW TOTAL AMOUNT OF: $2,770.00
PL/SQL procedure successfully completed.
我做错了什么？
以下是数据表：SQL>选择*2 FROM_ord；
ID CUSTOMER_ID DATE_ORDERED DATE_SHIPPED SALES_REP_ID TOTAL PAYMENT_TYPE ORDER_FILLED
100 204 31/AUG/92 10/SEP/92 11 601100 CREDIT Y
101 205 31/AUG/92 15/SEP/92 14 8056.6 CREDIT Y
102 206 01/SEP/92 08/SEP/92 15 8335 CREDIT Y
103 208 02/SEP/92 22/SEP/92 15 377 CASH Y
104 208 03/SEP/92 23/SEP/92 15 32430 CREDIT Y
105 209 04/SEP/92 18/SEP/92 11 2722.24 CREDIT Y
106 210 07/SEP/92 15/SEP/92 12 15634 CREDIT Y
107 211 07/SEP/92 21/SEP/92 15 142171 CREDIT Y
108 212 07/SEP/92 10/SEP/92 13 149570 CREDIT Y
109 213 08/SEP/92 28/SEP/92 11 1020935 CREDIT Y
110 214 09/SEP/92 21/SEP/92 11 1539.13 CASH Y
111 204 09/SEP/92 21/SEP/92 11 2770 CASH Y
97 201 28/AUG/92 17/SEP/92 12 84000 CREDIT Y
98 202 31/AUG/92 10/SEP/92 14 595 CASH Y
99 203 31/AUG/92 18/SEP/92 14 7707 CREDIT Y
112 210 31/AUG/92 10/SEP/92 12 550 CREDIT Y
选择了16行。
考虑到你需要一个按照家庭作业说明的光标，你实际上并不遥远。输出问题的直接原因是，虽然更新语句更改了数据库上的值，但它不会更改光标中的值。但是您在两个dbms_output语句中都使用了游标值。其次，您不需要IF语句。您的游标包含一个where子句，该子句使IF语句每次计算的值都为true。最后你有一个小的计算问题。公式"；total＝ord_rec.total*0.02"；不给2%的折扣，给98%的折扣。综合这些，你会得到：申报
cursor ord_cur(p_min number,p_max number) is
select id,total
from s_ord
where total between p_min and p_max
for update of total nowait;
ord_rec ord_cur%rowtype;
begin
dbms_output.put_line('ORDERS THAT ARE BETWEEN R1000 AND R3000.00,RECEIVE 2% DISCOUNT');
dbms_output.put_line('********************************************');
open ord_cur(1000,3000);
loop
fetch ord_cur
into ord_rec;
exit when ord_cur%notfound;
dbms_output.put_line('ORDER NO '|| ord_rec.id ||' HAVE A TOTAL AMOUNT OF: '||to_char(ord_rec.total),'L9,999.09'));
update s_ord
set total = ord_rec.total*0.98 --- ord_rec.total - (ord_rec.total*0.02)
where current of ord_cur;
dbms_output.put_line('ORDER NO '|| ord_rec.id ||' HAVE A NEW TOTAL AMOUNT OF: '||to_char((ord_rec.total*0.98),'L9,999.09'));
end loop;
end;