1、由表名得到对应的实体类--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; //联系人电子邮件地址
2、表名得到表的字段个数--USEINFO为表名
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
3、将数据库里面执行的增删改的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;
4、删除重复的数据
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;
5、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
6、数字转字符
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;
7、负数变正数
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;
8、查找表对应的字段
select lower(t.column_name) from user_tab_cols t where lower(t.table_name)='contract'