CREATE OR REPLACE Procedure Filldpb(Username1 In Varchar2) Is
Str_Sql Varchar2(1000);
v_Customer_Cc1 Constant Varchar2(20) := 'DP';
v_Customer_Cc2 Constant Varchar2(20) := 'mm';
v_Customer_Cc3 Constant Varchar2(20) := 'yyyy';
Logid Number(20);
Begin
Logid := 0;
Select Decode((Select Nvl(Menologyid, 0)
From Dpdt.Menology Ec
Where Ec.Brand = 'DP' And Ec.Branch = Username1 And Ec.Yearid = To_Char(Sysdate, 'yyyy') And
Ec.Monthid = To_Char(Sysdate, 'mm')), Null, 0, 1)
Into Logid
From Dual;
If (Logid <> 0) Then
Str_Sql := 'Update dpdt.menology w Set w.sumsale=(Select Nvl(Sum(Sprc), 0) From ' || Username1 ||
'.Zsale T1 Where To_Char(T1.Sdate,' || '''' || v_Customer_Cc2 || '''' || ') Like To_Char(Sysdate,' || '''' ||
v_Customer_Cc2 || '''' || '))';
Else
Str_Sql := 'Insert into dpdt.menology values(Portsequence.Nextval, ' || '''' || v_Customer_Cc1 || '''' || ', ' || '''' ||
Username1 || '''' || ', To_Char(Sysdate, ' || '''' || v_Customer_Cc3 || '''' || '), To_Char(Sysdate, ' || '''' ||
v_Customer_Cc2 || '''' || '),(Select Nvl(Sum(Sprc), 0) From ' || Username1 ||
' .Zsale T1 Where To_Char(T1.Sdate, ' || '''' || v_Customer_Cc2 || '''' || ') Like To_Char(Sysdate, ' || '''' ||
v_Customer_Cc2 || '''' || ')))';
/*Str_Sql := ' Merge Into Dpdt.Menology Using (Select * From Dpdt.Menology Ec Where Ec.Brand = ' || '''' ||
v_Customer_Cc1 || '''' || ' And Ec.Branch = ' || '''' || Username || '''' ||
' And Ec.Yearid = To_Char(Sysdate, ' || '''' || v_Customer_Cc3 || '''' ||
') And Ec.Monthid = To_Char(Sysdate, ' || '''' || v_Customer_Cc2 || '''' ||
')) Cc On (Cc.Menologyid Is Not Null) When Matched Then Update Set Sumsale = (Select Nvl(Sum(Sprc), 0) From ' || Username || ' .Zsale T1 Where To_Char(T1.Sdate, ' || '''' || v_Customer_Cc2 || '''' || ') Like To_Char(Sysdate, ' || '''' ||
v_Customer_Cc2 || '''' || '))
When Not Matched Then Insert(Menologyid, Brand, Branch, Yearid, Monthid, Sumsale) Values(Portsequence.Nextval, ' || '''' || v_Customer_Cc1 || '''' || ', ' || '''' || Username || '''' ||
', To_Char(Sysdate, ' || '''' || v_Customer_Cc3 || '''' || '), To_Char(Sysdate, ' || '''' || v_Customer_Cc2 || '''' ||
'),(Select Nvl(Sum(Sprc), 0) From ' || Username || ' .Zsale T1 Where To_Char(T1.Sdate, ' || '''' || v_Customer_Cc2 || '''' || ') Like To_Char(Sysdate, ' || '''' ||
v_Customer_Cc2 || '''' || ')) ';*/
End If;
Execute Immediate Str_Sql; --动态执行DDL语句
Commit;
Exception
When Others Then
Dbms_Output.Put_Line(Sqlerrm);
Rollback;
End Filldpb;