那位大侠帮忙看这段脚本干了些什么.请帮忙.我看不懂所以然.为谢
水平有限.spool;
declare
cursor c1(v_owner varchar2,v_tab_name varchar2) is
select column_name,data_type
from all_tab_columns
where table_name = v_tab_name
and owner = v_tab_name field_list varchar2(4000);
field_list_sel varchar2(4000);
fmt_field varchar2(200);
date_format varchar2:='yyyymmddhh24miss';
v_owner varchar2(30) :=user;
v_tab_name varchar2(30) :=upper(trim('&tab_name'));
begin
dbms_output.enable(1000000);
dbms_output.put_line('set echo off feedback off');
dbms_output.put_line('spool ./data/&tab_name' || '_data.sql');
--print the insert field
dbms_output.put_line('variable field_list varchar2(4000);');
dbms_output.put_line('begin');
dbms_output.put_line(' :field_list:=''insert into &tab_name(''');
for rec1 in c1(v_owner,v_tab_name) loop
if c1%rowcount=1 then
field_list := ' || '' ' || rec1.column_name || '''';
else
field_list :=' || '',' || rec1.column_name || '''';
end if;
dbms_output.put_line(field_list);
end loop;
dbms_out.put_line('||'')''|| chr(10)||''values('';');
dbms_out.put.put_line('end;');
dbms_out.put.put_line('/');
dbms_output.put_line('select :field_list || chr(10) ||');
for rec1 in c1(v_owner,v_tab_name) loop
if rec1.data_type='DATE' then
fmt_field:='''to_date(''''''||to_char('|| rec1.column_name||','''|| date_format ||''')||'''''','''''|| date_format||''''')''';
elseif rec1.data_type in ('CHAR','VARCHAR2') then
fmt_field:='''''''''|| replace('|| rec1.column_name || ','''''''','''''''''''')||''''''''';
else
fmt_field:='to_char('|| rec1.column_name || ')';
end if;
if c1%rowcount=1 then
field_list_sel:=fmt_field;
else
field_list_sel:='||'',''||' || fmt_field;
end if ;
dbms_output.put_line(field_list_sel);
end loop;
dbms_output.put_line(''||''),''');
dbms_output.put_line('from &tab_name ;');
dbms_output.put_line('spoo off;');
end;
spool off;
水平有限.spool;
declare
cursor c1(v_owner varchar2,v_tab_name varchar2) is
select column_name,data_type
from all_tab_columns
where table_name = v_tab_name
and owner = v_tab_name field_list varchar2(4000);
field_list_sel varchar2(4000);
fmt_field varchar2(200);
date_format varchar2:='yyyymmddhh24miss';
v_owner varchar2(30) :=user;
v_tab_name varchar2(30) :=upper(trim('&tab_name'));
begin
dbms_output.enable(1000000);
dbms_output.put_line('set echo off feedback off');
dbms_output.put_line('spool ./data/&tab_name' || '_data.sql');
--print the insert field
dbms_output.put_line('variable field_list varchar2(4000);');
dbms_output.put_line('begin');
dbms_output.put_line(' :field_list:=''insert into &tab_name(''');
for rec1 in c1(v_owner,v_tab_name) loop
if c1%rowcount=1 then
field_list := ' || '' ' || rec1.column_name || '''';
else
field_list :=' || '',' || rec1.column_name || '''';
end if;
dbms_output.put_line(field_list);
end loop;
dbms_out.put_line('||'')''|| chr(10)||''values('';');
dbms_out.put.put_line('end;');
dbms_out.put.put_line('/');
dbms_output.put_line('select :field_list || chr(10) ||');
for rec1 in c1(v_owner,v_tab_name) loop
if rec1.data_type='DATE' then
fmt_field:='''to_date(''''''||to_char('|| rec1.column_name||','''|| date_format ||''')||'''''','''''|| date_format||''''')''';
elseif rec1.data_type in ('CHAR','VARCHAR2') then
fmt_field:='''''''''|| replace('|| rec1.column_name || ','''''''','''''''''''')||''''''''';
else
fmt_field:='to_char('|| rec1.column_name || ')';
end if;
if c1%rowcount=1 then
field_list_sel:=fmt_field;
else
field_list_sel:='||'',''||' || fmt_field;
end if ;
dbms_output.put_line(field_list_sel);
end loop;
dbms_output.put_line(''||''),''');
dbms_output.put_line('from &tab_name ;');
dbms_output.put_line('spoo off;');
end;
spool off;
生成一个脚本.而这个生成的脚本中包含的内容是:
(A)在指定用户的特定表中播入记录.
(B)在指定用户的特定表中查询出记录,并将日期型的字段转为指定的FORMAT格出输出.