1.由同一字段的不同状态插入不同的表
--Insert multiple rows into different tables with a single statement:
INSERT ALL
WHEN type=1 THEN INTO tab1 VALUES (myseq.NEXTVAL, val)
WHEN type=2 THEN INTO tab2 VALUES (myseq.NEXTVAL, val)
WHEN type IN (3,4,5) THEN INTO tab3 VALUES (myseq.NEXTVAL, val)
ELSE INTO tab4 VALUES (myseq.NEXTVAL, val)
SELECT type, val FROM source_tab;
2 查询那些用户,操纵了那些表造成了锁机
SELECT s.username,
decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#,
s.terminal,
s.machine,
s.program,
s.osuser
FROM v$session s, v$lock l, all_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null;
3 根据B表中的CB列的内容查询A表中CA列 LIKE '% CB %'的记录
select A.* from A,B where instr(A.CA,B.CB)>0;
4 四舍五入,并取指定位小数
如果用round(13.000,2),结果为13,不保留小数位,若要保留指定的小数据位,可用to_char(要转换的数值,'格式化结果'),这样即使小数位为0,也会保留
--示例:将13.0000,保留三位小数
select round(13.0000,3),to_char(13.0000,'999.999') from dual;
--结果
--round=13
--to_char=13.000