2.txt

来自「Oracle Database 10g PL／SQL入门教程的全部例子」· 文本 代码 · 共 59 行

CREATE OR REPLACE PROCEDURE do_commissions IS
	commission_rate NUMBER :=2;
	total_sale NUMBER :=0;
	current_person CHAR(3) :=' ';
	next_person CHAR(3) ;
	quantity_sold NUMBER :=0;
	item_price NUMBER :=0;
	CURSOR sales_cur IS
		SELECT purc.salesperson,
		purc.quantity,
		prod.product_price
FROM plsql101_purchase purc,
plsql101_product prod
WHERE purc.product_name = prod.product_name
ORDER BY salesperson;
BEGIN
OPEN sales_cur;
LOOP
FETCH sales_cur INTO
next_person,quantity_sold,item_price;
WHILE (next_person=current_person 
AND 
sales_cur%FOUND)
LOOP
total_sale :=
total_sale+(quantity_sold*item_price);
FETCH sales_cur INTO
next_person,quantity_sold,item_price;
END LOOP;
IF(sales_cur%FOUND)
THEN
IF(current_person!=next_person)
THEN
IF(current_person!=' ')
THEN
dbms_output.put_line
(current_person||
' '||
total_sale||
' '||
total_sale*commission_rate/100);
END IF;
total_sale :=quantity_sold*item_price;
current_person:=next_person;
END IF;
ELSE IF(current_person!=' ')
THEN
dbms_output.put_line(current_person||
' '||
total_sale||
' '||
total_sale * commission_rate/100);
END IF;
END IF;
EXIT WHEN sales_cur%NOTFOUND;
END LOOP;
CLOSE sales_cur;
END do_commissions;