行转列(合并行数据)
SELECT setid, year, guid,
wmsys.wm_concat (addicode) as code,
wmsys.wm_concat (addiname) as name
FROM addisection
WHERE setid=? AND YEAR=? AND guid=?
GROUP BY setid, year, guid
误删恢复(闪回方式)
-- 打开Flash存储的权限
ALTER TABLE bankaccount ENABLE row movement ;
-- 把表还原到指定时间点,后面的参数为要还原的时间点
flashback table bankaccount to timestamp to_timestamp('2020-07-01 19:00:00','yyyy-mm-dd hh24:mi:ss');
获取中文拼音简称
/* 获取拼音简码函数 */
CREATE OR REPLACE FUNCTION GET_PYJM (P_NAME IN VARCHAR2)
RETURN VARCHAR2
AS
V_COMPARE VARCHAR2 (100);
V_RETURN VARCHAR2 (4000);
BEGIN
DECLARE
FUNCTION F_NLSSORT (P_WORD IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN NLSSORT (P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M');
END;
BEGIN
FOR I IN 1 .. LENGTH (P_NAME)
LOOP
V_COMPARE := F_NLSSORT (SUBSTR (P_NAME, I, 1));
IF V_COMPARE >= F_NLSSORT ('吖')
AND V_COMPARE <= F_NLSSORT ('驁')
THEN
V_RETURN := V_RETURN || 'A';
ELSIF V_COMPARE >= F_NLSSORT ('八')
AND V_COMPARE <= F_NLSSORT ('簿')
THEN
V_RETURN := V_RETURN || 'B';
ELSIF V_COMPARE >= F_NLSSORT ('嚓')
AND V_COMPARE <= F_NLSSORT ('錯')
THEN
V_RETURN := V_RETURN || 'C';
ELSIF V_COMPARE >= F_NLSSORT ('咑')
AND V_COMPARE <= F_NLSSORT ('鵽')
THEN
V_RETURN := V_RETURN || 'D';
ELSIF V_COMPARE >= F_NLSSORT ('妸')
AND V_COMPARE <= F_NLSSORT ('樲')
THEN
V_RETURN := V_RETURN || 'E';
ELSIF V_COMPARE >= F_NLSSORT ('发')
AND V_COMPARE <= F_NLSSORT ('猤')
THEN
V_RETURN := V_RETURN || 'F';
ELSIF V_COMPARE >= F_NLSSORT ('旮')
AND V_COMPARE <= F_NLSSORT ('腂')
THEN
V_RETURN := V_RETURN || 'G';
ELSIF V_COMPARE >= F_NLSSORT ('妎')
AND V_COMPARE <= F_NLSSORT ('夻')
THEN
V_RETURN := V_RETURN || 'H';
ELSIF V_COMPARE >= F_NLSSORT ('丌')
AND V_COMPARE <= F_NLSSORT ('攈')
THEN
V_RETURN := V_RETURN || 'J';
ELSIF V_COMPARE >= F_NLSSORT ('咔')
AND V_COMPARE <= F_NLSSORT ('穒')
THEN
V_RETURN := V_RETURN || 'K';
ELSIF V_COMPARE >= F_NLSSORT ('垃')
AND V_COMPARE <= F_NLSSORT ('擽')
THEN
V_RETURN := V_RETURN || 'L';
ELSIF V_COMPARE >= F_NLSSORT ('嘸')
AND V_COMPARE <= F_NLSSORT ('椧')
THEN
V_RETURN := V_RETURN || 'M';
ELSIF V_COMPARE >= F_NLSSORT ('拏')
AND V_COMPARE <= F_NLSSORT ('瘧')
THEN
V_RETURN := V_RETURN || 'N';
ELSIF V_COMPARE >= F_NLSSORT ('筽')
AND V_COMPARE <= F_NLSSORT ('漚')
THEN
V_RETURN := V_RETURN || 'O';
ELSIF V_COMPARE >= F_NLSSORT ('妑')
AND V_COMPARE <= F_NLSSORT ('曝')
THEN
V_RETURN := V_RETURN || 'P';
ELSIF V_COMPARE >= F_NLSSORT ('七')
AND V_COMPARE <= F_NLSSORT ('裠')
THEN
V_RETURN := V_RETURN || 'Q';
ELSIF V_COMPARE >= F_NLSSORT ('亽')
AND V_COMPARE <= F_NLSSORT ('鶸')
THEN
V_RETURN := V_RETURN || 'R';
ELSIF V_COMPARE >= F_NLSSORT ('仨')
AND V_COMPARE <= F_NLSSORT ('蜶')
THEN
V_RETURN := V_RETURN || 'S';
ELSIF V_COMPARE >= F_NLSSORT ('侤')
AND V_COMPARE <= F_NLSSORT ('籜')
THEN
V_RETURN := V_RETURN || 'T';
ELSIF V_COMPARE >= F_NLSSORT ('屲')
AND V_COMPARE <= F_NLSSORT ('鶩')
THEN
V_RETURN := V_RETURN || 'W';
ELSIF V_COMPARE >= F_NLSSORT ('夕')
AND V_COMPARE <= F_NLSSORT ('鑂')
THEN
V_RETURN := V_RETURN || 'X';
ELSIF V_COMPARE >= F_NLSSORT ('丫')
AND V_COMPARE <= F_NLSSORT ('韻')
THEN
V_RETURN := V_RETURN || 'Y';
ELSIF V_COMPARE >= F_NLSSORT ('帀')
AND V_COMPARE <= F_NLSSORT ('咗')
THEN
V_RETURN := V_RETURN || 'Z';
END IF;
END LOOP;
RETURN V_RETURN;
END;
END;
表解锁
-------查询表是否锁住
select object_name,machine,s.sid,s.serial from v$locked_object l,dba_objects o ,v$session s where l.object_id=o.object_id and l.session_id=s.sid;
---------解锁
alter system kill session '535,4729' --(其中24,111分别是上面查询出的sid,serial#)
merge大数据量更新插入
MERGE INTO BANKACCOUNTINFO t1
USING (SELECT * FROM BANKACCOUNTINFO_TEMP WHERE pinyin IS NOT NULL) t2
ON (t1.bankcode=t2.bankcode)
WHEN MATCHED THEN
UPDATE
SET t1.BANKNAME = t2.BANKNAME,
-- ...
WHEN NOT MATCHED THEN
INSERT (
...
) VALUES (
...
);
Oracle更新语法
oracle更新语法:
- 一般语法
update tab set col = .... [where ...]
=后可以有子查询,但是必须对于tab的每一列返回唯一一行与之对应,
where是需要更新的表,部分更新必须加,否则相关子查询的更新会把没有匹配的更新为null,
如
update tab a
set a.col=
(select b.col from b where a.id=b.id)
where exists
(select 1 from b where a.id=b.id)
类似地写了多遍
- 改进语法merge
merge into tab
using (表|视图|子查询等) --子查询需要加括号 on (条件)
when match then
do update
when no match then
do insert
insert语法和update语法有所不同,详细参考文档,10g还支持update,insert的有条件更新和插入,支持update的delete where,支持只有update或insert的
不能修改using里的关联列,同样,必须每一行有唯一与之对应的
上面两种语法如果找不到唯一对应的,需要改进语句,比如加rownum=1
- update inline view的用法
update (select ...........关联查询) set 目标=源
如 update(select a.name,b.name from a,b where a.id=b.id) set a.name=b.name;
需要unique建保证唯一对应,比如上面的必须要b.id有唯一键,也就是preserved key,比如唯一索引什么的都可以,11g之前可以用hint: bypass_ujvc,这样不需要唯一键,但是可能有问题,一对多会更新多次,11g这个hint失效
delete (select …) 也可以,有很多要求,可以看sql文档,insert (select …)限制更多
第3种方法来源于可更新的视图
oracle更新基本有3种sql写法,后面两种往往优化中会使用到,特别第一种的更新关联子查询中源表不走索引,那么更新很多,相当于 nested loop,肯定慢,而且还有个where过滤,多次访问源表
更新前查询是否存在数据
update table_a a
set a.xx =
where exists (select 1
from table_b b
WHERE b.xx = a.xx)