create or replace procedure proTmpso_new(sDate varchar2,sMonth Varchar2,SESID VARCHAR2,sCCN varchar2) As
vint integer;
Cursor Tmpso_Cur IS
SELECT CCN,MAS_LOC,ITEM,REVISION rev,to_Char(FORECAST_DATE,'YYYY/MM') f_date,Nvl(sum(QTY),0) qty
FROM forecast
where
CCN=sCCN AND FORECAST_DATE Between to_date(sDate,'YYYY/MM/DD')
AND add_months(to_date(sDate,'YYYY/MM/DD'),9)
Group by CCN,MAS_LOC,ITEM,REVISION,to_Char(FORECAST_DATE,'YYYY/MM');
begin
Insert Into TMPSO009_NEW (CCN,MAS_LOC,item,Revision,Line,Source,SESSION_ID)
Select Distinct
CCN,MAS_LOC,ITEM,REVISION,'000001','Forecast',SesID
From
Forecast
where CCN=sccn AND FORECAST_DATE between to_date(sDate,'YYYY/MM/DD') and Add_months(to_date(sDate,'YYYY/MM/DD'),9);
commit;
For VTmpso_new IN Tmpso_Cur LOOP
vint := Getnum(VTmpso_new.f_Date,sMonth);
IF vint = 1 Then
update tmpso009_new set Data1=VTmpso_new.qty where session_id='||sesid||' and ccn='||VTmpso_new.ccn ||' and item='||VTmpso_new.item||' and revision='||VTmpso_new.Rev||' and mas_loc='||VTmpso_new.mas_loc||' and line='000001' and source='Forecast' ;
ElsIF vint = 2 Then
update tmpso009_new set Data2 =VTmpso_new.qty where session_id='||sesid||' and ccn='||VTmpso_new.ccn ||' and item='||VTmpso_new.item||' and revision='||VTmpso_new.Rev||' and mas_loc='||VTmpso_new.mas_loc||' and line='000001' and source='Forecast' ;
End IF;
End Loop;
commit;
end proTmpso_new;其中的update语句为什么不执行???????
vint integer;
Cursor Tmpso_Cur IS
SELECT CCN,MAS_LOC,ITEM,REVISION rev,to_Char(FORECAST_DATE,'YYYY/MM') f_date,Nvl(sum(QTY),0) qty
FROM forecast
where
CCN=sCCN AND FORECAST_DATE Between to_date(sDate,'YYYY/MM/DD')
AND add_months(to_date(sDate,'YYYY/MM/DD'),9)
Group by CCN,MAS_LOC,ITEM,REVISION,to_Char(FORECAST_DATE,'YYYY/MM');
begin
Insert Into TMPSO009_NEW (CCN,MAS_LOC,item,Revision,Line,Source,SESSION_ID)
Select Distinct
CCN,MAS_LOC,ITEM,REVISION,'000001','Forecast',SesID
From
Forecast
where CCN=sccn AND FORECAST_DATE between to_date(sDate,'YYYY/MM/DD') and Add_months(to_date(sDate,'YYYY/MM/DD'),9);
commit;
For VTmpso_new IN Tmpso_Cur LOOP
vint := Getnum(VTmpso_new.f_Date,sMonth);
IF vint = 1 Then
update tmpso009_new set Data1=VTmpso_new.qty where session_id='||sesid||' and ccn='||VTmpso_new.ccn ||' and item='||VTmpso_new.item||' and revision='||VTmpso_new.Rev||' and mas_loc='||VTmpso_new.mas_loc||' and line='000001' and source='Forecast' ;
ElsIF vint = 2 Then
update tmpso009_new set Data2 =VTmpso_new.qty where session_id='||sesid||' and ccn='||VTmpso_new.ccn ||' and item='||VTmpso_new.item||' and revision='||VTmpso_new.Rev||' and mas_loc='||VTmpso_new.mas_loc||' and line='000001' and source='Forecast' ;
End IF;
End Loop;
commit;
end proTmpso_new;其中的update语句为什么不执行???????
解决方案 »
- 菜鸟:欲用脚本从oracle 10g升级到11g,出现问题!
- select dm , decode(dm,'01','一班','02','一班',……,'100','一班','101',二班', ……'200','二班
- oracle 时间
- 我的plsql连接oracle的时候可以连接得上,可是里面table打开没东西
- 关于将B表中的数据 更新到A表中 出现的数据缺损问题.
- 急!请问如何从DMP 中恢复数据库?(我不知该数据库内容和用户)
- 如何用SQL语名更改字段名?
- 拼字段
- 如何定义一个BOOL类型的列?
- 急,求助大数据量读取的问题
- clob和blob的问题
- 根据参数选出表中的记录,用视图还是存储过程?
SET Data2 = VTmpso_new.qty
WHERE session_id = sesid
AND ccn = VTmpso_new.ccn
AND item = VTmpso_new.item
AND revision = VTmpso_new.Rev
AND mas_loc = VTmpso_new.mas_loc
AND line = '000001'
AND SOURCE = 'Forecast';