Oracle学习利器：自动生成SQL脚本工具详解

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);
 v_insert varchar(2000);
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;
           v_insert := v_insert ||' '||','||'?';
    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;
if type ='select' then 
    v_result :='select '||substr(v_result,2)||' from '||upper(tableName);
elsif type ='insert' then
        v_result :='insert into '||upper(tableName)||' ( '||substr(v_result,2)||' ) values ( '||substr(v_insert,2)||' ) ';
elsif type = 'update' then