sql 代码
- --前提是utl_file_dir不能为空,必需修改initsid.ora文件然后重启数据库
- CREATE OR REPLACE PROCEDURE genins_file(
- p_table IN varchar2,
- p_output_folder IN VARCHAR2,
- p_output_file INVARCHAR2)
- IS
- --
- l_column_list VARCHAR2(32767);
- l_value_list VARCHAR2(32767);
- l_query VARCHAR2(32767);
- l_cursor NUMBER;
- ignore NUMBER;
- l_insertline1 varchar2(32767);
- l_insertline2 varchar2(32767);
- cmn_file_handle UTL_FILE.file_type;
- --
- FUNCTION get_cols(p_table VARCHAR2)
- RETURN VARCHAR2
- IS
- l_colsVARCHAR2(32767);
- CURSORl_col_cur(c_table VARCHAR2) IS
- SELECT column_name
- FROM user_tab_columns
- WHERE table_name = upper(c_table)
- ORDER BY column_id;
- BEGIN
- l_cols :=null;
- FOR rec INl_col_cur(p_table)
- LOOP
- l_cols := l_cols || rec.column_name || ',';
- ENDLOOP;
- RETURNsubstr(l_cols,1,length(l_cols)-1);
- END;
- --
- FUNCTION get_query(p_table IN VARCHAR2)
- RETURN VARCHAR2
- IS
- l_queryVARCHAR2(32767);
- CURSOR l_query_cur(c_table VARCHAR2) IS
- SELECT 'decode('||column_name||',null,''null'','||
- decode(data_type,'VARCHAR2','''''''''||'||column_name||'||'''''''''
- ,'DATE','''''''''||to_char('||column_name||',''YYYY-MM-DDHH24:MI:SS'')||'''''''''
- ,column_name
- ) || ')' column_query
- FROM user_tab_columns
- WHERE table_name = upper(c_table)
- ORDER BY column_id;
- BEGIN
- l_query :='SELECT ';
- FOR rec INl_query_cur(p_table)
- LOOP
- l_query := l_query || rec.column_query || '||'',''||';
- ENDLOOP;
- l_query :=substr(l_query,1,length(l_query)-7);
- RETURNl_query || ' FROM ' || p_table;
- END;
- --
- BEGIN
- l_column_list :=get_cols(p_table);
- l_query := get_query(p_table);
- l_cursor := dbms_sql.open_cursor;
- DBMS_SQL.PARSE(l_cursor, l_query,DBMS_SQL.native);
- DBMS_SQL.DEFINE_COLUMN(l_cursor, 1,l_value_list, 32767);
- ignore := DBMS_SQL.EXECUTE(l_cursor);
- --
- IF NOT UTL_FILE.IS_OPEN(cmn_file_handle)THEN
- cmn_file_handle := UTL_FILE.FOPEN (p_output_folder, p_output_file,'a',32767);
- END IF;
- LOOP
- IFDBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
- DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_value_list);
- l_insertline1:='INSERT INTO '||p_table||'('||l_column_list||')';
- l_insertline2:=' VALUES ('||l_value_list||');';
- UTL_FILE.put_line (cmn_file_handle, l_insertline1);
- UTL_FILE.put_line (cmn_file_handle, l_insertline2);
- ELSE
- EXIT;
- ENDIF;
- END LOOP;
- IF NOT UTL_FILE.IS_OPEN(cmn_file_handle)THEN
- UTL_FILE.FCLOSE (cmn_file_handle);
- ENDIF;
- END;
- /