Oracle or Question Solve(二)

数据库常用语句和函数

----update update()函数主要注意的是后面的where限制条件
--例子：
update tab_a a set a.v1 = (select b.v1 from tab_b b where a.key1=b.key1 and ...),...
where exists (select 'x' from tab_b b where a.key1=b.key1 and ...);

update tab_a a set (a.v1,a.v2) = (select b.v1,b.v2 from tab_b where a.key1=b.key1 and ...)
where exists (select 'x' from tab_b b where a.key1=b.key1 and ...);

----merge into ;
merge into a table_a ----要更新或插入的目标数据
using (select ... from ... where ...) table_b ---更新或插入的源数据
on (table_a.key1 = table_b.key1 and table_a.key2 = table_b.key2 ....)
----判断更新或插入的条件，只是两表之间的关联，如果b表内部有限制，直接在源数据上修改
when matched then     ---当on 里面的条件成立有结果的时候
   update set table_a.v1 = table_b.v1 , table_a.v2 = table_b.v2
when not matched then   ---当on里面的条件成立无结果的时候
   insert (table_a.v1,table_a.v2...)
   values   (table_b.v1,table_b.v2...); ----matched 和 not matched 可以只用一个，也可以同时都用

--例子：
create table test_a (
       a varchar2(5),
       b varchar2(5),
       c varchar2(5)
);

alter table test_a
add constraint PK_TEST_A primary key (a);

create table test_b (
       a varchar2(5),
       b varchar2(5),
       c varchar2(5)
);

alter table test_b
add constraint PK_TEST_B primary key (a);

insert into test_b (
select '1','2','3' from dual
union
select '2','3','4' from dual);

insert into test_b (
select '1','1','1' from dual
union
select '3','4','5' from dual);

merge into test_a a
using test_b b
on (a.a=b.a)
when matched then
   update set a.b=b.b,a.c=b.c
when not matched then
   insert (a.a,a.b,a.c)
   values(b.a,b.b,b.c);

--merge into 和 update 比较，merge into 是查出整批结果后一起更新，update 是查出一个结果后去更新，直到所有数据更新完成，
--因此，merge into 无法找到相关更新的记录数，而update可以，但是，因为update 更新一条数据后，需要重新判断where 的限制条件，所以merge into 的执行效率是update 的将近10倍

----asciistr()
--将字符串转换成ascii字符串
--例子：
select asciistr('我是好人 a') from dual ;
--该转换函数可以用来判断字符串是否有中文，如果是中文字符，那转换后，就会带‘/’ ，需要注意的是，如果本身就带‘/’，那转换后还是有‘/’，
--所以要先去掉原先的‘/’，再做判断
--例子：
select asciistr('/ asd我是 ?!@#$% ') from dual where --replace(asciistr('/ asd我是 ?!@#$% '),' ',' ') -- like '%\%' ;
where asciistr(replace('/ asd我是 ?!@#$% ',' ',' ')) like '%\%';

---chr()
---chr()函数是将ascii码->字符，对应关系参照ascii编码表
select chr(32) from dual ;

----ascii()
----ascii()函数是chr()函数的反函数，是将字符转换成ascii码
select a from (
select chr(10) a from dual ) where ascii(a) ='10';

----replace()
--替换函数，replace(a,b,c) 将字符串a中所有的b字符或字符串替用c字符或字符串替换，也可以去空格
select replace('asd asaa' ,' ','') from dual ;

----trim()
---去空格函数，需要注意的是，trim函数，只是去掉左右两端的空格，且只能是用space敲打的空格，如果是其他形式的空格，如回车，是无法去掉的
select trim(' 我想做个好人 ') from dual ;

--去除其他形式的空格
--1.先判断这个空格的实际对应的ascii码值，ascii码32对应的是正常的空格，小于32，对应的是其他形式的空格
select ascii(substr('abcd ',5,1)) from dual ;
--2.用replace 加存在这个ascii码值对应的字符替换成空
select replace('abcd ',chr(ascii(substr('abcd ',5,1))),'') from dual ;

----fn_jqzd()，截取函数，参数有3个，字符串和分割符和位数
select fn_jqzd('asda|1231','|',1), fn_jqzd('asda|1231','|',2) from dual ;

--查看一个表的主键
select a.constraint_name, a.column_name
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'P'
and a.table_name = 'TAB';

---重复数据只保留一条 ,rowid 是唯一标志物理位置的一个id,因此，只要保留一个最大的，或者最小的rowid
--根据主键分组，取每组的最大或最小的rowid，保留下来，就行了

delete from table_a where (key1, rowid) not in
(select key1, max(rowid) from table_a group by key1);
--比较下面的写法
delete from table_a where key1 in (select key1 from table_a group by key1 having count(*)>1) and rowid
not in (select max(rowid) from table_a group by key1 having count(*)>1);

delete from table_a where key1 in (select key1 from table_a group by key1 having count(*)>1) and rowid
not in (select max(rowid) from table_a group by key1);

delete from table_a where rowid not in
(select max(rowid) from table_a group by key1);
--rowid 和 rownum ,rowid 是唯一标志物理位置的一个id，rownum 是对结果集的编排顺序
--rownum 始终是从1开始，所以rownum>2永远为假，是无法查出数据的，如果要用，要么实例化，要么用rownumber() over
--例1：
select rownum ,t.a from (
select '1' a from dual
union all
select '2' a from dual
)t where rownum>=2;
--例2：
select rn,a from (
select rownum rn,t.a from (
select '1' a from dual
union all
select '2' a from dual
)t) where rn>=2;

----数据库闪回语句（闪回到固定时间点上）
alter table jw_pk_kbcdb enable row movement;
select count(*) from jw_pk_kbcdb as of timestamp to_timestamp('2016-10-06 9:30:00','yyyy-mm-dd hh24:mi:ss');
flashback table jw_pk_kbcdb to timestamp to_timestamp('2016-10-06 9:30:00','yyyy-mm-dd hh24:mi:ss');

--查看死锁情况(如果存在死锁情况，执行最后一个text字段下的内容解锁)
SELECT   A.OWNER, A.OBJECT_NAME,B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.ORACLE_USERNAME,B.OS_USER_NAME,B.PROCESS,B.LOCKED_MODE,C.MACHINE,C.STATUS,
C.SERVER,C.SID,C.SERIAL#,C.PROGRAM,'alter   system   kill   session   '''||sid||','||serial#||''';' text
FROM   ALL_OBJECTS   A,V$LOCKED_OBJECT   B, SYS.GV_$SESSION   C
WHERE   (A.OBJECT_ID=B.OBJECT_ID)AND(B.PROCESS=C.PROCESS)ORDER   BY   1,2 ;

--闪回被drop掉的表
flashback table test_drop to before drop;