oracle卸数装数,oracle unix下卸数-装数存储过程

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;