create table T_P_TABLE_NAME_TEMP

 (

   owner        VARCHAR2(30) not null,

   table_name   VARCHAR2(30) not null,

   tab_comments VARCHAR2(4000),

   column_name  VARCHAR2(30) not null,

   data_type    VARCHAR2(106),

   col_comments VARCHAR2(4000)

 );

 -- Create table

 create table T_SQL

 (

   scsj       DATE,

   sqlyj      VARCHAR2(4000),

   table_name VARCHAR2(100) not null,

   mxyj       VARCHAR2(4000)

 )

 -- Add comments to the columns

 comment on column T_SQL.scsj

   is '生成时间';

 comment on column T_SQL.sqlyj

   is 'SQL语句';

 comment on column T_SQL.table_name

   is '表名';

 create or replace procedure p_ddpt_sql_init(p_table_name varchar2, --输入表名,必须大写

                                            p_error_no   out int, --错误编号

                                            p_error_info out varchar2  --错误信息

                                            )

 --调度平台 建表语句生成

  as

   nb_begindate date;

   nb_enddate   date;

   v_count      number;

   v_update_sql varchar(2000);

   v_temp_sql varchar(2000);

   v_detail_sql varchar(2000);

   v_out_sql varchar(2000);

   v_owner varchar(2000);

   v_table_name varchar(2000);

   v_tab_comments varchar(2000);

   v_column_name varchar(2000);

   v_data_type varchar(2000);

   v_col_comments varchar(2000);

 begin

   p_error_no   := 0;

   p_error_info := '';

   nb_begindate := sysdate;

   v_out_sql := 'use dm_mms;'||chr(13)||

                'drop table '||p_table_name||';'||chr(13)||

                'create table '||p_table_name||'('||chr(13);

   execute immediate 'truncate table t_p_table_name_temp';

   insert into t_p_table_name_temp

     (owner, table_name, tab_comments, column_name, data_type, col_comments)

     select a.owner,

            a.table_name,

            c.comments tab_comments,

            a.column_name,

            a.data_type,

            b.comments col_comments

       from all_tab_columns a, all_col_comments b, all_tab_comments c

      where a.table_name = b.table_name

        and a.owner = b.owner

        and a.column_name = b.column_name

        and a.table_name = c.table_name

        and a.owner = c.owner

        and c.table_type = 'TABLE'

        and a.table_name = p_table_name

      order by a.table_name;

    commit;

    update t_p_table_name_temp set data_type = 'string' where data_type = 'VARCHAR2';

    update t_p_table_name_temp set data_type = 'double' where data_type = 'NUMBER';

    commit;

    update t_p_table_name_temp set data_type = 'string' where column_name = 'YGBH';

    update t_p_table_name_temp set data_type = 'string' where column_name = 'DWBH';

    update t_p_table_name_temp set data_type = 'string' where column_name = 'FGS';

    update t_p_table_name_temp set data_type = 'string' where column_name = 'KHYF';

    update t_p_table_name_temp set data_type = 'string' where column_name = 'SFYL';

    update t_p_table_name_temp set data_type = 'string' where column_name = 'RSSJ';

    update t_p_table_name_temp set data_type = 'string' where column_name = 'YLSC';

    update t_p_table_name_temp set data_type = 'string' where column_name = 'YGZT';

    update t_p_table_name_temp set data_type = 'string' where column_name = 'SYYLYF';

    update t_p_table_name_temp set data_type = 'string' where column_name = 'YGLX';

    update t_p_table_name_temp set data_type = 'string' where column_name = 'KHBH';

    update t_p_table_name_temp set data_type = 'string' where column_name = 'CSMRZ';

    update t_p_table_name_temp set data_type = 'string' where column_name = 'CSLX';

    update t_p_table_name_temp set data_type = 'string' where column_name like  '%YF';

    update t_p_table_name_temp set data_type = 'string' where column_name like  '%RQ';

    update t_p_table_name_temp set data_type = 'string' where column_name like  '%BH';

    commit;

    --v_update_sql := 'update'|| p_table_name || ' set yj=replace(yj,''chr(13)'',chr(13)) where table_name = '''||p_table_name ||'''';

    dbms_output.put_line(v_update_sql);

    for c_row in (SELECT * FROM t_p_table_name_temp)

    loop

       v_owner := c_row.owner;

       v_table_name := c_row.table_name;

       v_tab_comments := c_row.tab_comments;

       v_column_name := c_row.column_name;

       v_data_type := c_row.data_type;

       v_col_comments := c_row.col_comments;

       if v_temp_sql is null then

          v_temp_sql := v_column_name ||' '||v_data_type || ' comment '||'"'||v_col_comments||'"';

          v_detail_sql := v_column_name;

       else

          v_temp_sql := v_temp_sql ||','||chr(13) || v_column_name ||' '||v_data_type || ' comment '||'"'||v_col_comments||'"';

          v_detail_sql := v_detail_sql||',' || chr(13) || v_column_name;

       end if;

   end loop;

   --select distinct tab_comments into v_tab_comments from t_p_table_name_temp;

   v_out_sql := v_out_sql || v_temp_sql ||') '||chr(13) ||'comment '||'"'|| v_tab_comments||'"'||chr(13);

   v_detail_sql := 'select ' || chr(13) || v_detail_sql || ' from '|| v_owner ||'.' ||v_table_name;

   v_out_sql:=v_out_sql||'partitioned by(busi_date string) '||';';

   nb_enddate := sysdate;

   delete t_sql where table_name = p_table_name;

   insert into t_sql

     (scsj, sqlyj, table_name, mxyj)

   values

     (nb_enddate, v_out_sql, p_table_name, v_detail_sql);

   commit; 

   p_error_no   := 1;

   p_error_info := '调度平台建表语句生成成功！';

   --p_insert_log_info('', nb_begindate, nb_enddate, '调度平台SQL语句生成', 'p_ddpt_sql_init', p_error_no, p_error_info);

 exception

   when others then

     p_error_no   := -1;

     p_error_info := '调度平台建表语句生成失败！' || sqlerrm;

     --p_insert_log_info('', nb_begindate, nb_enddate, '奖励38 调度平台SQL语句生成', 'p_ddpt_sql_init', p_error_no, p_error_info);

     rollback;

 end p_ddpt_sql_init;