由表名得到對應的實體類
--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'