Oracle 存储过程笔记.

业务说明：

　　主要用于计算采购加权平均价。入参为年份和月份，首先判断输入的年月是否已经结账，如果已经结账就将所有物料和供应商的采购加权平均价返回。

要点说明：

　　1.如何在存储过程中定义临时表

　　答：oracle正常是不允许直接在存储过程中直接创建临时表的，所以只能使用动态SQL的方式。创建之前，请先确认执行存储过程的用户拥有create any table 的权限。否则会报错。

　　2.如何在存储过程定义动态SQL，并且包含转义符

　　答：有2种方式：

　　　　1) 可以在SQL中定义参数，然后执行的时候在传进去。例如：

sql := 'select * from tableA where colA = :1 and colB = 1';
execute immediate sql using 'colA_value'

　　　　2) 使用转义符，2个单引号，例如：

execute immediate 'select * from tableA where colA = ''a'' and colB = 1';

　　3. 如何将结果集返回出去。

　　答:在存储过程定义中声明一个出参out_return out sys_refcursor，然后在最后open out_return for 'select * from base_data';

全部代码：

create or replace PROCEDURE KD_Po_Weight_Avg_Price(
fyear number,
fmonth number,
out_return out sys_refcursor
)
AS
--定义局部变量
month_diff NUMBER(6,2);
input_date date;
current_period date;
input_year_month number(6);
temp_sql varchar2(1000);
insert_sql varchar2(2000);
result_sql varchar2(200);
table_count number(3);

--开始业务处理
BEGIN
--入参日期
input_date := to_date(fyear||'-'||fmonth,'yyyy-mm');
--入参日期(数字)
input_year_month := fyear * 100 + fmonth;

--查询当前账期和当前时间相差的月份
select TO_DATE(sy.FVALUE || '-' || sp.FVALUE, 'YYYY-MM') into current_period 
from T_BD_ACCOUNTBOOK b
inner join T_BAS_SYSTEMPROFILE sy on  b.fbookid = sy.FACCOUNTBOOKID and sy.FKEY = 'CurrentYear' and sy.FCATEGORY = 'GL' 
inner join T_BAS_SYSTEMPROFILE sp on  b.fbookid = sp.FACCOUNTBOOKID and sp.FKEY = 'CurrentPeriod' and sp.FCATEGORY = 'GL'
where b.fnumber = '001';

if months_between(input_date,current_period) >= 1 then
  --如果差值小于1证明,当前月份已经结账了.再查询当前表里是否已经有数据了,
  DBMS_OUTPUT.PUT_LINE('继续处理,要求的日期已结账,当前账期'||current_period);  
else
  DBMS_OUTPUT.PUT_LINE('当前账期还未结账'); 
  --如果未结账直接抛异常,程序终止
  RAISE_APPLICATION_ERROR(-20001, '当前账期还未结账.请结账后再试');
end if;

select count(1) into table_count from user_tables t where upper(t.TABLE_NAME) = upper('base_data');
if table_count >= 1 then
  execute immediate 'drop table base_data';
end if;

temp_sql := 'create global temporary table base_data(
  year_month number(6),
  FYear number(4),
  FMonth number(4),
  KdYear number(4),
  FQuarter number(1),
  item_number varchar2(50),
  supplier_number varchar2(50),
  FAmount number(28,10),
  FQty number(28,10)
) ON COMMIT PRESERVE ROWS';
execute immediate temp_sql;

insert_sql := 'insert into base_data
select t.* from (
  select extract(year from i.fdate)*100+extract(month from i.fdate) as year_month,
  extract(year from i.fdate) as year, extract(month from i.fdate) as month,
  decode(sign(extract(month from i.fdate) - 3), -1, extract(year from i.fdate) - 1, extract(year from i.fdate)) as kdyear,
  case when (extract(month from i.fdate) in (1,2,3)) then 4
    when (extract(month from i.fdate) in (4,5,6)) then 1
    when (extract(month from i.fdate) in (7,8,9)) then 2
    when (extract(month from i.fdate) in (10,11,12)) then 3
  end as kdquarter,
  m.fnumber as itemNumber, s.fnumber as supplierNumber,
  nvl(if.FALLAMOUNT,0) as famount,nvl(ie.FREALQTY,0) as fqty
  from T_STK_INSTOCK i
  inner join T_BAS_BILLTYPE b on i.fbilltypeid = b.fbilltypeid and b.fnumber = ''RKD01_SYS''
  inner join T_STK_INSTOCKEntry ie on i.fid = ie.fid
  left join T_STK_INSTOCKENTRY_F if on IE.FENTRYID = if.FENTRYID
  left join T_BD_SUPPLIER s on i.fsupplierid = s.fsupplierid
  inner join T_BD_SUPPLIER_L sl on s.fsupplierid = sl.fsupplierid
  left join T_BD_MATERIAL m on ie.FMATERIALID = m.FMATERIALID
  inner join T_BD_MATERIAL_L ml on m.FMATERIALID = ml.FMATERIALID
  where I.FCANCELSTATUS = ''A''
) t where t.year_month <= :1 ';
execute immediate insert_sql using input_year_month;

open out_return for 'select * from base_data';
/*
exception
  when too_many_rows then  
  DBMS_OUTPUT.PUT_LINE('返回值多于1行');  
  when others then  
  DBMS_OUTPUT.PUT_LINE('未知异常！'); 
  */
--结束业务处理
END KD_Po_Weight_Avg_Price;