Oracle之通过表名自动生成insert/update/select语句

最新推荐文章于 2021-04-10 04:35:14 发布

yuaoi

最新推荐文章于 2021-04-10 04:35:14 发布

阅读量546

点赞数

分类专栏： ORACLE笔记文章标签： Oracle SQL Bean 框架

ORACLE笔记专栏收录该内容

6 篇文章 0 订阅

订阅专栏

目前使用这个自个开发的框架，十分轻量，功能比较有限。很不好的地方就是还要自己写sql语句，一个简单的单表insert/update都不能save(javabean)，还要自己写，十分容易出错，以前还天真的傻傻的写了不少，现在终于下决心找点时间写个简单的工具，让自动生成一下，然后再在基础上改，可能比较方便点吧：


/*     
* 多条记录连接成一条     
* 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;

生成结果形如：
[b]insert:[/b]


insert into JBPM4_TASK(DBID_,CLASS_,DBVERSION_,NAME_,DESCR_,STATE_,SUSPHISTSTATE_,ASSIGNEE_,FORM_,PRIORITY_,CREATE_,DUEDATE_,PROGRESS_,SIGNALLING_,EXECUTION_ID_,ACTIVITY_NAME_,HASVARS_,SUPERTASK_,EXECUTION_,PROCINST_,SWIMLANE_,TASKDEFNAME_) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

[b]update:[/b]


update JBPM4_TASK set DBID_ = ?,CLASS_ = ?,DBVERSION_ = ?,NAME_ = ?,DESCR_ = ?,STATE_ = ?,SUSPHISTSTATE_ = ?,ASSIGNEE_ = ?,FORM_ = ?,PRIORITY_ = ?,CREATE_ = ?,DUEDATE_ = ?,PROGRESS_ = ?,SIGNALLING_ = ?,EXECUTION_ID_ = ?,ACTIVITY_NAME_ = ?,HASVARS_ = ?,SUPERTASK_ = ?,EXECUTION_ = ?,PROCINST_ = ?,SWIMLANE_ = ?,TASKDEFNAME_ = ?

[b]select:[/b]


select DBID_,CLASS_,DBVERSION_,NAME_,DESCR_,STATE_,SUSPHISTSTATE_,ASSIGNEE_,FORM_,PRIORITY_,CREATE_,DUEDATE_,PROGRESS_,SIGNALLING_,EXECUTION_ID_,ACTIVITY_NAME_,HASVARS_,SUPERTASK_,EXECUTION_,PROCINST_,SWIMLANE_,TASKDEFNAME_ from JBPM4_TASK

[b]javabean get:[/b]


bean.getDbid_(),bean.getDbversion_(),bean.getExecution_(),bean.getOutcome_(),bean.getAssignee_(),bean.getPriority_(),bean.getState_(),bean.getCreate_(),bean.getEnd_(),bean.getDuration_(),bean.getNextidx_(),bean.getSupertask_()