Oracle常用语句记录

行转列（合并行数据）

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更新语法：

1. 一般语法

  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) 

  类似地写了多遍

2. 改进语法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

3. 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)