create or replace
package body sales_stat_pkg as   
ln_group_id sales_report.GROUP_ID%type;
ln_product_type sales_report.PRODUCT_TYPE_ID%type;
function get_group_id_cur  
return group_id_cur is  
v_group_id_cur group_id_cur;
begin  
open v_group_id_cur for  --游标定义
select group_id  
from employees;
fetch v_group_id_cur into ln_group_id ; --游标的数据存到ln_group_id中
return v_group_id_cur;
end get_group_id_cur;

function get_product_type_cur  
return product_type_cur is  
v_product_type_cur product_type_cur;
begin  
open v_product_type_cur for  --打开游标
select product_type_id  
from product_types;
fetch v_product_type_cur into ln_product_type;
return v_product_type_cur;
end get_product_type_cur;


procedure sales_register_p(
p_purchase_id IN purchases.purchase_id%TYPE,  
p_product_id IN products.product_id%TYPE,
p_customer_id IN customers.customer_id%TYPE,   
p_employee_id IN employees.employee_id%TYPE,   
p_quantity IN NUMBER
)as
lv_year char(4);
lv_months char(2);
ln_quantity number;
ln_count INTEGER;
begin  
--
begin
select nvl(sum(c.quantity),0) --取c表quantity字段的sum值
into ln_quantity  
from products a,employees b,purchases c
where b.employee_id =c.employee_id
and a.product_id =c.product_id
group by b.group_id, a.product_type_id; --分组
end;
--取得年月
select to_char(add_months(sysdate,-1),'YYYY'),to_char(add_months(sysdate,-1),'MM')
into lv_year ,lv_months
from dual;  
--写表
insert into sales_report(month,year,group_id,product_type_id)
values (lv_months,lv_year,ln_group_id ,ln_product_type);
commit;

exception  --写表有异常时的操作
when others then  
DBMS_OUTPUT.PUT_LINE('An exception occurred');--输出'An exception occurred'
end sales_register_p;
end sales_stat_pkg;