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 ;
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)
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');
end sales_register_p;
end sales_stat_pkg;
------解决方案--------------------
- SQL code
create or replacepackage 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)aslv_year char(4);lv_months char(2);ln_quantity number;ln_count INTEGER;begin --beginselect nvl(sum(c.quantity),0) --取c表quantity字段的sum值into ln_quantity from products a,employees b,purchases cwhere b.employee_id =c.employee_idand a.product_id =c.product_idgroup 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_monthsfrom 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;