我也仿写了一个
CREATE OR REPLACE PROCEDURE export_data (
v_tname VARCHAR2,
v_cbatch NUMBER DEFAULT 0
)
AS
v_sql VARCHAR2 (3999);
v_sql1 VARCHAR2 (3999);
sql_stmt VARCHAR2 (3999);
TYPE r_fields IS RECORD (
tname col.tname%TYPE,
cname VARCHAR2 (3999),
coltype col.coltype%TYPE,
width col.width%TYPE
);
TYPE t_fields IS TABLE OF r_fields;
vn_fields t_fields;
TYPE r_stmt IS RECORD (
stmt VARCHAR2 (3999)
);
TYPE t_stmt IS TABLE OF r_stmt;
vn_stmt t_stmt;
BEGIN
EXECUTE IMMEDIATE 'alter session set nls_date_format='
|| ''''
|| 'yyyy-mm-dd hh24:mi:ss'
|| '''';
sql_stmt :=
'select tname, cname,coltype,width from col where tname=:1 order by colno';
EXECUTE IMMEDIATE sql_stmt
BULK COLLECT INTO vn_fields
USING TRIM (v_tname);
v_sql := '''' || 'insert into ' || TRIM (v_tname) || ' values ( ''||';
FOR i IN 1 .. vn_fields.COUNT
LOOP
IF vn_fields (i).coltype = 'NUMBER'
THEN
vn_fields (i).cname :=
'nvl(to_char(' || vn_fields (i).cname || '),''NULL''' || ')';
v_sql := v_sql || vn_fields (i).cname;
ELSIF vn_fields (i).coltype = 'VARCHAR2'
OR vn_fields (i).coltype = 'CHAR'
THEN
vn_fields (i).cname :=
'nvl(to_char(' || vn_fields (i).cname || '),NULL' || ')';
v_sql :=
v_sql || '''''''''||' || vn_fields (i).cname || '||''''''''';
ELSIF vn_fields (i).coltype = 'DATE'
THEN
v_sql :=
v_sql
|| '''to_date(''''''||'
|| 'to_char('
|| vn_fields (i).cname
|| ','''
|| 'YYYY-MM-DD HH24:MI:SS'
|| ''')'
|| '||'''''',''||'''''''
|| 'YYYY-MM-DD HH24:MI:SS'
|| ''''
|| ''')''';
ELSE
vn_fields (i).cname :=
'nvl(to_char(' || vn_fields (i).cname || '),''NULL''' || ')';
v_sql := v_sql || vn_fields (i).tname;
END IF;
IF i < vn_fields.COUNT
THEN
v_sql := v_sql || '||'',''||';
END IF;
END LOOP;
v_sql := v_sql || '||''' || ');''';
sql_stmt := 'select ' || v_sql || ' from ' || TRIM (v_tname);
EXECUTE IMMEDIATE sql_stmt
BULK COLLECT INTO vn_stmt;
FOR j IN 1 .. vn_stmt.COUNT
LOOP
DBMS_OUTPUT.put_line (vn_stmt (j).stmt);
IF MOD (j, v_cbatch) = 0
THEN
DBMS_OUTPUT.put_line ('COMMIT;');
END IF;
END LOOP;
IF v_cbatch = 0 OR vn_stmt.COUNT < v_cbatch
THEN
DBMS_OUTPUT.put_line ('COMMIT;');
END IF;
END export_data
来自:http://www.cnoug.org/viewthread.php?tid=31600&extra=&page=1