业务说明：

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

要点说明：

　　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 binner 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_dataselect 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;