/*

 * 多条记录连接成一条

 * tableName 表名

 * type 类型：可以是insert/update/select之一

 */

 create or replace function my_concat(tableName varchar2,type varchar2)

 return varchar2

 is

  type typ_cursor is ref cursor;

  v_cursor typ_cursor;

  v_temp varchar2(30);

  v_result varchar2(4000):= '';

  v_sql varchar2(200);

 begin

  v_sql := 'select COLUMN_NAME from user_tab_columns where table_name = ''' || upper(tableName) || ''' order by COLUMN_ID asc';

  open v_cursor for v_sql;

  loop

     fetch v_cursor into v_temp;

     exit when v_cursor%notfound;

     if type = 'select' or type = 'insert' then

        v_result := v_result ||',' || v_temp;

     elsif type = 'update' then

        v_result := v_result ||',' || v_temp || ' = ?';

     elsif type = 'javabean' then

        v_result := v_result ||',bean.get' || upper(substr(v_temp,1,1)) || lower(substr(v_temp,2)) ||  '()';

     end if;

  end loop;

  return substr(v_result,2);

 end;  

 /*

 * 通过表名自动生成insert/update/select/javabean get方法语句

 * tableName 表名

 * type 类型：可以是insert/update/select之一

 */

 create or replace procedure autoGenerateSQL(

 tableName varchar2,

 type varchar2,

 out_result out varchar2

 )

 is

   sql_insert varchar2(2000);

   sql_update varchar2(2000);

   sql_select varchar2(2000);

   javabean_str varchar2(2000);

   field_num integer;        --字段个数

   type_info varchar2(20);   --参数类型判断信息

 begin  

 sql_insert := 'insert into ' || upper(tableName) || '(' || my_concat(tableName,type) || ') values (';

 sql_update := 'update ' || upper(tableName) || ' set ';

 sql_select := 'select ';

 javabean_str := '';

 type_info := '';  

 select count(*) into field_num from user_tab_columns where table_name=upper(tableName);

 select decode(type,'insert',type,'update',type,'select',type,'javabean',type,'error') into type_info from dual;  

 if field_num = 0 then             -- 表不存在时

    out_result := '表不存在！请重新输入!';

 elsif type_info = 'error' then    --type参数错误时

    out_result := 'type参数错误：类型只能是insert、update、select、javabean之一';

 elsif field_num > 0 then

   if type = 'insert' then         --生成insert 语句

     for i in 1..field_num

       loop

          sql_insert := sql_insert || '?';

          if i < field_num then

             sql_insert := sql_insert || ',';

          end if;

       end loop;

       sql_insert := sql_insert || ')';

       out_result := sql_insert;

   elsif type = 'update' then      --生成update 语句

       sql_update := sql_update || my_concat(tableName,type);

       out_result := sql_update;

   elsif type = 'select' then      --生成select 语句

       sql_select := sql_select || my_concat(tableName,type) || ' from ' || upper(tableName) || ' a';

       out_result := sql_select;

   elsif type = 'javabean' then    --生成javabean的get方法

       javabean_str := my_concat(tableName,type);

       out_result := javabean_str;

   end if;

 end if;  

 end autoGenerateSQL; 

 declare out_result varchar2(512);

 begin

 autoGenerateSQL('tf_Employee2','insert', out_result);

 dbms_output.put_line(out_result);

 end;