加载中…oracle unix下卸数-装数存储过程
(2011-03-24 18:07:19)
标签：
oracle杂谈 |
分类： DATABASE |
---卸数据
CREATE OR REPLACE Procedure Export_Data_Out_Txt
(
i_Date Varchar2,
o_Success Out Integer
) As
v_Date
Date;
v_Start_Time Date;
v_Column
Varchar2(100);
v_Column_Content Varchar2(500);
v_Sql
Varchar2(1000);
v_Table_Name
Varchar2(20);
Type Som_Content_Record_Type Is Record(
Txt_Content Varchar2(2000));
Som_Content_Rec Som_Content_Record_Type;
l_File Utl_File.File_Type;
---定义需要卸数的表游标
Cursor Cursor_Exp_Table Is
Select Table_Name From Stg_Exp_Table;
---定义表字段游标
Cursor Cursor_Tab_Column Is
Select Column_Name From User_Tab_Columns Where Table_Name =
Upper(v_Table_Name) Order By Column_Id;
--动态获取sql游标
Type Cursor_Som_Content Is Ref Cursor;
l_Cursor_Som_Content Cursor_Som_Content;
Begin
v_Date
:=
To_Date(i_Date, 'yyyy-mm-dd');
v_Start_Time :=
Sysdate;
v_Column_Content := '';
--v_Table_Name :=
Upper(i_Pfile_Name);
o_Success := 0;
Open Cursor_Exp_Table;
Loop
Begin
Fetch Cursor_Exp_Table
Into v_Table_Name;
Exit When Cursor_Exp_Table%Notfound;
l_File := Utl_File.Fopen('/home/oracle/exp/', v_Table_Name ||
'_' || i_Date || '.txt', 'w', 10000);
v_Column_Content := '';
Open Cursor_Tab_Column;
Loop
Fetch Cursor_Tab_Column
Into v_Column;
Exit When Cursor_Tab_Column%Notfound;
If v_Column = 'AS_OF_DATE' Then
v_Column_Content := v_Column_Content || 'to_char(' || v_Column
|| ',''yyyymmdd'')' || '||' || 'chr(124)' || '||';
Else
v_Column_Content := v_Column_Content || v_Column || '||' ||
'chr(124)' || '||';
End If;
End Loop;
Close Cursor_Tab_Column;
---去除结尾的||+chr(123)+|| 这12个字符
v_Column_Content := Substr(v_Column_Content, 1,
Length(v_Column_Content) - 12);
v_Sql := 'Select ' || v_Column_Content || ' From ' ||
v_Table_Name;
Open l_Cursor_Som_Content For v_Sql;
Loop
Fetch l_Cursor_Som_Content
Into Som_Content_Rec.Txt_Content;
Exit When l_Cursor_Som_Content%Notfound;
Utl_File.Put(l_File, Som_Content_Rec.Txt_Content);
Utl_File.New_Line(l_File);
End Loop;
Close l_Cursor_Som_Content;
Utl_File.Fflush(l_File);
Utl_File.Fclose(l_File);
l_File := Utl_File.Fopen('/home/oracle/exp/', v_Table_Name ||
'.OK', 'w');
Utl_File.Put_Line(l_File, i_Date);
Utl_File.Fflush(l_File);
Utl_File.Fclose(l_File);
Proc_Load_Log('Exp_' || v_Table_Name, '数据抽取成功', v_Date,
v_Start_Time, Sysdate, 1, 1, 'Success');
Exception
When Others Then
o_Success := -1;
If Utl_File.Is_Open(l_File) Then
Utl_File.Fclose(l_File);
End If;
Proc_Load_Log('Exp_' || v_Table_Name, '数据抽取失败', v_Date,
v_Start_Time, Sysdate, 1, -1, Sqlerrm);
End;
End Loop;
Close Cursor_Exp_Table;
End Export_Data_Out_Txt;
/
---装数据
Create Or Replace Procedure Imp_Data_In_Txt
(
Pfile_Name Varchar2,
Data_Date Varchar2,
x
Out Varchar2
)
Out_Varchar Varchar2(10000);
a
Varchar2(10000);
i
Binary_Integer;
Type Som_Content_Record_Type Is Record(
As_Of_Date
Varchar2(100),
Account_Number
Varchar2(100),
Iso_Currency_Cd Varchar2(100),
Org_Unit_Id
Varchar2(100),
Lob_Id
Varchar2(100),
Common_Coa_Id
Varchar2(100),
Gl_Account_Id
Varchar2(100),
Product_Id
Varchar2(100),
Product_Type_Cd Varchar2(100),
Customer_Id
Varchar2(100),
Cur_Net_Rate
Varchar2(100),
Transfer_Rate
Varchar2(100),
Cur_Par_Bal
Varchar2(100),
Avg_Book_Bal
Varchar2(100),
Accrued_Interest Varchar2(100),
Transfer_Interest Varchar2(100),
Trans_Row
Varchar2(100));
Som_Content_Rec Som_Content_Record_Type;
Type Emp_Type_Array Is Table Of Varchar2(100) Index By
Binary_Integer;
Emp_Rec_Array Emp_Type_Array;
l_File Utl_File.File_Type;
Begin
l_File := Utl_File.Fopen('/home/oracle/exp/', Pfile_Name ||
'_' || Data_Date || '.txt', 'DM_REP_TP_20100930.txt', 'r');
Loop
Utl_File.Get_Line(l_File, Out_Varchar, 1000);
i := 1;
For i In 1 .. 16 Loop
Emp_Rec_Array(i) := Substr(Out_Varchar, 1, Instr(Out_Varchar,
'|') - 1);
Out_Varchar := Substr(Out_Varchar, Instr(Out_Varchar, '|') +
1);
End Loop;
Som_Content_Rec.As_Of_Date
:=
To_Date(Emp_Rec_Array(1), 'yyyy-mm-dd');
Som_Content_Rec.Account_Number
:= Emp_Rec_Array(2);
Som_Content_Rec.Iso_Currency_Cd :=
Emp_Rec_Array(3);
Som_Content_Rec.Org_Unit_Id
:= Emp_Rec_Array(4);
Som_Content_Rec.Lob_Id
:= Emp_Rec_Array(5);
Som_Content_Rec.Common_Coa_Id
:= Emp_Rec_Array(6);
Som_Content_Rec.Gl_Account_Id
:= Emp_Rec_Array(7);
Som_Content_Rec.Product_Id
:=
Emp_Rec_Array(8);
Som_Content_Rec.Product_Type_Cd :=
Emp_Rec_Array(9);
Som_Content_Rec.Customer_Id
:= Emp_Rec_Array(10);
Som_Content_Rec.Cur_Net_Rate
:= Emp_Rec_Array(11);
Som_Content_Rec.Transfer_Rate
:= Emp_Rec_Array(12);
Som_Content_Rec.Cur_Par_Bal
:= Emp_Rec_Array(13);
Som_Content_Rec.Avg_Book_Bal
:= Emp_Rec_Array(14);
Som_Content_Rec.Accrued_Interest :=
Emp_Rec_Array(15);
Som_Content_Rec.Transfer_Interest := Emp_Rec_Array(16);
Insert Into Dm_Rep_Tp_Bak
Values
(Som_Content_Rec.As_Of_Date, Som_Content_Rec.Account_Number,
Som_Content_Rec.Iso_Currency_Cd,
Som_Content_Rec.Org_Unit_Id, Som_Content_Rec.Lob_Id,
Som_Content_Rec.Common_Coa_Id, Som_Content_Rec.Gl_Account_Id,
Som_Content_Rec.Product_Id, Som_Content_Rec.Product_Type_Cd,
Som_Content_Rec.Customer_Id,
Som_Content_Rec.Cur_Net_Rate, Som_Content_Rec.Transfer_Rate,
Som_Content_Rec.Cur_Par_Bal,
Som_Content_Rec.Avg_Book_Bal,
Som_Content_Rec.Accrued_Interest,
Som_Content_Rec.Transfer_Interest);
Commit;
End Loop;
Utl_File.Fflush(l_File);
Utl_File.Fclose(l_File);
x := 0;
Exception
When No_Data_Found Then
x := 0;
When Others Then
x := Sqlcode || Sqlerrm;
If Utl_File.Is_Open(l_File) Then
Utl_File.Fclose(l_File);
Commit;
End If;
Commit;
End;
喜欢
0
赠金笔
前一篇：Cognos内容库表
后一篇：oracle 锁表处理