首页 > 数据库技术 > 详细

Oracle 存储过程笔记.

时间:2019-01-22 00:12:02      阅读:233      评论:0      收藏:0      [点我收藏+]

业务说明:

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

要点说明:

  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;

 

Oracle 存储过程笔记.

原文:https://www.cnblogs.com/namelessmyth/p/10301621.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!