Oracle-自动生成insert、update、select、javabean语句

首先把下面语句 在Oracle中执行一下

create or replace function my_concat(tableName varchar2,type varchar2)return varchar2is 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;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);   --参数类型判断信息beginsql_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;

执行完后 会有一个函数,和一个存储过程

在存储过程哪里 右键 点击测试
填上表名和insert、update、select、javabean
就会生成相应的语句.