oracle长动态sql,ORACLE成长 ：一个动态SQL

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;