生成数据库SQL语句

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;
/

分享到：

oracle定期表分析 | 在oracle中进行列行转换

• 2007-10-23 20:57
• 浏览 1428
• 评论(0)
• 查看更多