oracle新手,请教这个包的注释该如何写?多谢了
求助:oracle新手,请问这个包的注释该怎么写???谢谢了
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;
------解决方案--------------------
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 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;