oracle - 超有用的小脚本

由表名得到对应的实体类

--APPLICANT为表名
select 'private ' || decode(a.DATA_TYPE,
              'VARCHAR2',
              'String ',
              'NUMBER',
              'Double ',
              'String ') || lower(a.column_name) || ';     //' || b.comments
  from user_tab_cols a,user_col_comments b
 where 
 a.TABLE_NAME = b.TABLE_NAME
 and a.COLUMN_NAME = b.COLUMN_NAME
 and a.table_name = 'APPLICANT' --大写;

数据：

private String residence;     //户籍
private String high;     //身高
private String weight;     //体重
private String drivinglicenceno;     //驾驶执照
private String issmoking;     //是否抽烟
private String dismissdate;     //离职日期
private String deathdate;     //死亡日期
private String contactphone;     //联系电话
private String urgencycontactphone;     //紧急联系电话
private String contactmobile;     //联系手机
private String contactfax;     //联系人传真
private String contactemail;     //联系人电子邮件地址

 

 

表名得到表的字段个数

select a.tname, count(*) field_count
  from tab a, user_col_comments b
 where tabtype = 'TABLE'
   and a.tname = b.table_name
   --and tname='USEINFO'  表名
 group by a.tname

 

 

将数据库里面执行的增删改的sql语句放入一个表(分析最近的操作记录)

declare
  -- Local variables here
  tablename varchar2(40) := 't_recordsql';
begin
  for sqltext in (select instr(sql_text, 'insert') a,
                         instr(sql_text, tablename) b,
                         sql_text
                    from v$sql a
                   where a.LAST_ACTIVE_TIME >=
                         to_date('2013/06/04 16:04:05',
                                 'yyyy/mm/dd hh24:mi:ss')
                     and a.LAST_ACTIVE_TIME <=
                         to_date('2013/06/04 18:04:05',
                                 'yyyy/mm/dd hh24:mi:ss')) loop
    if sqltext.a > 0 and sqltext.b > 0 then
      insert into t_recordsql values (sqltext.sql_text);
    end if;
  end loop;
 
  for sqltext in (select instr(sql_text, 'update') a,
                         instr(sql_text, tablename) b,
                         sql_text
                    from v$sql a
                   where a.LAST_ACTIVE_TIME >=
                         to_date('2013/06/04 16:04:05',
                                 'yyyy/mm/dd hh24:mi:ss')
                     and a.LAST_ACTIVE_TIME <=
                         to_date('2013/06/04 18:04:05',
                                 'yyyy/mm/dd hh24:mi:ss')) loop
    if sqltext.a > 0 and sqltext.b > 0 then
      insert into t_recordsql values (sqltext.sql_text);
    end if;
  end loop;
 
  for sqltext in (select instr(sql_text, 'delete') a,
                         instr(sql_text, tablename) b,
                         sql_text
                    from v$sql a
                   where a.LAST_ACTIVE_TIME >=
                         to_date('2013/06/04 16:04:05',
                                 'yyyy/mm/dd hh24:mi:ss')
                     and a.LAST_ACTIVE_TIME <=
                         to_date('2013/06/04 18:04:05',
                                 'yyyy/mm/dd hh24:mi:ss')) loop
    if sqltext.a > 0 and sqltext.b > 0 then
      insert into t_recordsql values (sqltext.sql_text);
    end if;
  end loop;
end;

 

删除重复的数据

declare
  -- Local variables here
  temp_id varchar2(20);
begin
  --遍历出重复的id
  for id in (select student_id
               from hibernate_student
             having count(student_id) > 1
              group by student_id) loop
    temp_id := id.student_id;
    --遍历出重复的id所对应的operationtime
    for operationtime in (select max(operationtime) tm
                            from hibernate_student a
                           where a.student_id = temp_id) loop
      --执行删除
      delete from hibernate_student b
       where b.operationtime != operationtime.tm
         and b.student_id = temp_id;
      commit;
    end loop;
  end loop;
end;

 

 union all 高级

            select channelid , childchannelid,serviceid, count(*) ic, 0 cc, 0 rc, 0 oc, 0 sc, 0 mc , 0 dc ,0 dm
                  from android_install_notify
                 where recorddate >= 20130301
                   and recorddate <= 20130311
                   and childchannelid in (3980)
                 group by childchannelid ,channelid,serviceid
             union all
                select channelid,childchannelid,serviceid, 0 ic, count(*) cc, 0 rc, 0 oc, 0 sc, 0 mc , 0 dc ,0 dm
                  from android_refuse_cg_notify 
                 where recorddate >= 20130301
                   and recorddate <= 20130311
                   and childchannelid in (3980)
                 group by childchannelid ,channelid,serviceid
                union all
                select channelid ,childchannelid,serviceid, 0 ic, 0 cc, count(*) rc, 0 oc, 0 sc, 0 mc , 0 dc ,0 dm
                  from android_cg_notify 
                 where recorddate >= 20130301
                   and recorddate <= 20130311
                   and childchannelid in (3980)
                 group by childchannelid ,channelid,serviceid
                union all
                select channelid ,childchannelid,serviceid, 0 ic, 0 cc, 0 rc, count(*) oc, 0 sc, 0 mc , 0 dc ,0 dm
                  from ouurms.sms_notify_log
                 where recorddate >= 20130301
                   and recorddate <= 20130311
                   and childchannelid in (3980)
                 group by childchannelid ,channelid,serviceid
                 union all -- 统计比率未扣量的
               select channelid , childchannelid,serviceid, 0 ic,0 cc, 0 rc, 0 oc,count(*) sc,sum(amount) mc , 0 dc ,0 dm
                  from ouurms.sms_log 
                 where result = 1
                   and step >= 11
                   and step != 20
                   and recorddate >= 20130301
                   and recorddate <= 20130311
                   and childchannelid in (3980)
                 group by childchannelid ,channelid,serviceid
                  union all -- 统计比率已扣除
                select channelid , childchannelid,serviceid, 0 ic,0 cc, 0 rc, 0 oc,0 sc,0 mc , count(*) dc , sum(amount) dm
                  from ouurms.sms_log 
                 where result = 1
                   and step = 20
                   and recorddate >= 20130301
                   and recorddate <= 20130311
                   and channelid in (select cid from usercratio)
                   and childchannelid in (3980)
                 group by childchannelid ,channelid,serviceid

 

数字转字符

function switch_dayvalueprem(num in number) return varchar2 is
    str varchar2(20);
  begin
    if num < 1 then
      str := to_char(num, '0.99');
    else
      str := to_char(num, '99.99');
    end if;
    return str;
  end;

 

负数变正数

function switch_dayvalueprem(num in number) return varchar2 is
    str varchar2(10);
    st  number(15,2) := 0;
    --number(15,2);
  begin
     st := abs(num);
     
     
   /* if num <-99 then 
            str := to_char(num, '000.99');
    elsif num <0 and num >=-99 then
      str := to_char(num, '00.99');
    elsif num>=0 and num <1 then*/
    if st >=0 and st <1 then 
      str := to_char(st, '0.9');
    elsif st >= 1 and st < 100 then
      str := to_char(st, '99.9');
    elsif st >= 100 and st < 1000 then
      str := to_char(st, '999.9');
    else
      str := to_char(st, '9999.9');
    end if;
    
    if num < 0 then
      str := '-' || str;
    end if;
    
    return replace(str,' ','');
  end;

 

查找表对应的字段

select lower(t.column_name) from user_tab_cols t where lower(t.table_name)='contract'