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;
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;
这个Package功能相关的注释?
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;