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;