Oracle prints the data in the table into the SQL statement of the insert statement

Print the data in the table into the SQL statement of the insert statement

Create or replace procedure print_insert (v_tname varchar2, v_cbatch number default 0)
/*
V_tname name of the table to be output SQL Author: xzh2000@hotmail.com
V_cbatch output commit interval blog:Http://blog.itpub.net/xzh2000
*/
As
/* Declare a dynamic cursor variable */
Type cur_alldata is ref cursor;
Rochelle data cur_alldata;
/* Write a single row of data into v_row */
V_ SQL varchar2 (3999 );
V_row varchar2 (3999 );
/* Function Forward Declaration */
Function formatfield (v_tname varchar2, v_cname varchar2, v_colno number) return varchar2;
/* Format the data output */
Function formatdata (v_tname varchar2, v_row varchar2) return varchar2
As
V_ldata varchar2 (32765 );
V_rdata varchar2 (32765 );
V_cname varchar2 (3999 );
V_instr number (8 );
V_count number (6 );
Begin
V_instr: = instr (v_row ,'(');
V_ldata: = substr (v_row, 1, v_instr );
V_rdata: = substr (v_row, v_instr + 1 );
V_instr: = instr (v_rdata ,')');
V_rdata: = substr (v_rdata, 1, v_instr-1 );

V_count: = 0;
Loop
V_instr: = instr (v_rdata ,',');
V_count: = v_count + 1;

Exit when v_instr = 0;

V_cname: = substr (v_rdata, 1, v_instr-1 );
V_rdata: = substr (v_rdata, v_instr + 1 );

/* Format different data types */
V_cname: = formatfield (v_tname, v_cname, v_count );

/* Add the processed field value to v_ldata */
If v_count = 1 then
V_ldata: = v_ldata | v_cname;
Else
V_ldata: = v_ldata | ',' | v_cname;
End if;

End loop;

/* Add the value of the last field */
If v_count = 1 then
V_ldata: = v_ldata | formatfield (v_tname, v_rdata, v_count) | ');';
Else
V_ldata: = v_ldata | ',' | formatfield (v_tname, v_rdata, v_count) | ');';
End if;
Return v_ldata;
End;
/* Process different data types */
Function formatfield (v_tname varchar2, v_cname varchar2, v_colno number) return varchar2
As
V_name varchar2 (3999 );
V_type varchar2 (99 );
Begin
Select coltype into v_type from Col where tname = upper (v_tname) and colno = v_colno;
If v_type = 'date' then
V_name: = 'to _ date ('| ''' | v_cname | ''' | ', '| ''' | 'yyyy-mm-dd hh24: MI: ss' | ''' | ')';
Elsif v_type = 'varchar2' then
V_name: = ''' | v_cname | '''';
Else
V_name: = v_cname;
End if;
Return v_name;
End;
/* List fields in the input table */
Function getfields (v_tname varchar2) return varchar2
As
V_fields varchar2 (3999 );
Begin
For cur_fname in (select cname, coltype from Col where tname = upper (v_tname) order by colno) loop
If v_fields is null then
V_fields: = 'nvl ('| cur_fname.cname |', '| ''' | '0' | ''' | ')';
Else
V_fields: = v_fields | '| '','' |' | 'nvl ('| cur_fname.cname | ', '| ''' | '0' | ''' | ')';
End if;
End loop;
V_fields: = 'select' | ''' | 'insert' | v_tname | 'values ('| ''' |' | v_fields | '| ''' | ') '| ''' | 'from' | v_tname;
Return v_fields;
End;
Begin
Execute immediate 'alter session set nls_date_format = '| ''' | 'yyyy-mm-dd hh24: MI: ss' | '''';
Dbms_output.put_line (*** xzxh2000 say hello to you! ***');

V_ SQL: = getfields (v_tname );
-- Dbms_output.put_line (v_ SQL );
Open l_alldata for v_ SQL;

Loop
Fetch l_alldata into v_row;
Exit when l_alldata % notfound;
-- Dbms_output.put_line (v_row );

Dbms_output.put_line (formatdata (v_tname, v_row ));
If Mod (l_alldata % rowcount, v_cbatch) = 0 then
Dbms_output.put_line ('commit ;');
End if;
End loop;

Close l_alldata;
End;

Create or replace function sum_string (v_ SQL varchar2)
Return varchar2
/*
By xzh2000@hotmail.com
Blog:Http://blog.itpub.net/xzh2000
*/
As
/* Declare a dynamic cursor variable */
Type cur_alldata is ref cursor;
Rochelle data cur_alldata;
/* Query SQL statements and their variables */
V_row varchar2 (99 );
V_sum varchar2 (3999 );
Begin
Open l_alldata for v_ SQL;
Loop
Fetch l_alldata into v_row;
/* Exit without data */
Exit when l_alldata % notfound;

V_sum: = v_sum | ',' | v_row;
End loop;

V_sum: = substr (v_sum, 2 );
Close l_alldata;

Return v_sum;
End;

 

20th floor Large Medium SmallPosted onView the author onlyI also copied a 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 from: http://www.cnoug.org/viewthread.php? Tid = 31600 & extra = & page = 1