ORACLE常用sql

1)删除重复数据：利用rowid实现
eg: delete FROM hdm.dtmg_data_exrt a
WHERE ROWID NOT IN (SELECT MAX(b.rowid)
FROM hdm.dtmg_data_exrt b
WHERE a.crcycd = b.crcycd
AND a.torycd = b.torycd
AND a.etl_dt = b.etl_dt);

2)查找重复的记录：group by+having
eg: select custno,acctdt,count() from cust_table
group by custno,acctdt having count()>1;

3)把etl_ac_inf_sum表中的账户一样的选择出来
（a）SELECT * FROM etl_ac_inf_sum WHERE acno IN (
SELECT acno FROM(
SELECT acno,Row_Number() over (PARTITION BY orgid,acno ORDER BY acno desc) rn
FROM etl_ac_inf_sum ORDER BY rn desc)
WHERE rn>1) ORDER BY acno;
（b）SELECT * FROM etl_ac_inf_sum WHERE acno IN (
SELECT acno FROM(
SELECT orgid,acno,Count(1) ct FROM etl_ac_inf_sum GROUP BY orgid,acno ORDER BY 1,2)
WHERE ct>1) ORDER BY acno;

4)查询记录一条变多条显示，利用rownum实现（重复显示）
SELECT t.* FROM ca_bat_task t, (SELECT rownum FROM ca_bat_task where rownum between 1 and 2) t2
where t.shortname=‘BDSS’ ORDER BY TASK_CODE ASC；

5)查看被锁的session(需要sys权限）
查看被锁的session,
SELECT OBJECT_NAME,MACHINE,S.SID,S.SERIAL# FROM V$LOCKE{D}_{O}BJECTL,DB{A}_{O}BJECTSO,V$SESSION S WHERE L.OBJECT_ID=O.OBJECT_ID AND L.SESSION_ID=S.SID;
杀死session;
ALTER SYSTEM KILL SESSION ‘100,17235’;
ALTER SYSTEM disconnect SESSION ‘1264,32387’ immediate;

6)参看失败的索引，并重建(DROP PARTITION后索引会无效）
SELECT INDEX_NAME,STATUS FROM USER_INDEXES WHERE STATUS=‘UNUSABLE’;
ALTER INDEX INDEX_NAME REBUILD;

7）取出表中各部门工资金额前三的员工号
SELECT * FROM (
SELECT orgid,ac_bal,acno,row_number() over (PARTITION BY orgid ORDER BY ac_bal DESC) rn
FROM etl_ac_inf_sum ) WHERE rn<4；

8） 按客户号分组再统计每个客户的总金额
sum(ac_bal) over (partition by cst_id) ac_bal_sum或常规的sum(ac_bal) group by cst_id

9)判断字符串是否是数字
TRANSLATE(REPLACE(‘入参’,‘.’,‘’),‘/1234567890’,‘/’) IS NULL

10）表数据恢复到之前某个时点
select log_mode,flashback_on from v$database;（查看开启闪回）
select *
from test as of timestamp to_timestamp(‘2014-05-28 11:00:00’,‘yyyy-mm-dd hh24:mi:ss’)；（闪回查询需要回滚段支持，能查询到数据的前镜像。但是回滚段大小不够时，老的数据会被覆盖，因此能闪回的是有个时间范围，不可能做到任意时间）

列行转换
1）列转行wm_concat()
eg:SELECT ac_id,To_Char(wm_concat(cst_id)) cstid FROM ETL_SGNJOIN_AC_INF GROUP BY ac_id ORDER BY ac_id;–一个ac_id对应多个cst_id的情况，把cst_id都在一行内拼接显示；
把一个字段的值拆开成多条记录：
eg:SELECT REGEXP_SUBSTR (‘1,2,3,4’, ‘[^,]+’, 1,ROWNUM)
FROM dual
CONNECT BY ROWNUM <=
LENGTH (‘1,2,3,4’) - LENGTH (REPLACE (‘1,2,3,4’, ‘,’,‘’)) + 1
eg2:SELECT REGEXP_SUBSTR (A.V_SUBJ_CODE, ‘[^-]+’, 1,B.LEV) SUBJ,
REGEXP_SUBSTR (A.V_SUBJ_NM, ‘[^-]+’, 1,B.LEV) SUBJ,
A.*
FROM D_JGL_SUBJ_CON_TEM A
INNER JOIN (SELECT LEVEL LEV FROM DUAL CONNECT BY LEVEL <= 100) B
ON 1 = 1
WHERE B.LEV <= LENGTH(A.V_SUBJ_CODE) -
LENGTH(REPLACE(A.V_SUBJ_CODE, ‘-’)) + 1

2）pivot(oracle 11g)
eg:create table demo(id int,name varchar(20),nums int);
select * from (select name, nums from demo) pivot (sum(nums) for name in (‘苹果’ as a, ‘橘子’ as b, ‘葡萄’ as c, ‘芒果’ as d));

3)unpivot(oracle 11g)
eg:create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);
select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )
多列例子：CREATE TABLE T5 (idnum NUMBER,f1 NUMBER(10,5),f2 NUMBER(10,5),f3 NUMBER(10,5),e1 NUMBER(10,5),e2 NUMBER(10,5),h1 NUMBER(10,5),h2 NUMBER(10,5));
INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (1,‘10.2004’,‘5.009’,‘7.330’,‘9.008’,‘8.003’,‘.99383’,‘1.43243’);
INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (2,‘4.2004’,‘6.009’,‘9.330’,‘4.7008’,‘4.60333’,‘1.993’,‘3.3243’);
INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (3,‘10.2040’,‘52.6009’,‘67.330’,‘9.5008’,‘8.003’,‘.99383’,‘1.43243’);
INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (4,‘9.20704’,‘45.009’,‘17.330’,‘29.008’,‘5.003’,‘3.9583’,‘1.243’);
COMMIT;
select *
from (select IDNUM,F1,F2,F3,E1,E2,H1,H2, null as E3,null as H3
from T5)
UnPivot((F,E,H) for sk in ((F1,E1,H1) as 1, (F2,E2,H2) as 2, (F3,E3,H3) as 3))
order by IDNUM,SK;

4）PLSQ使用游标
使用PL/SQL
DECLARE
–存放最终的SQL
LV_SQL VARCHAR2(3000);
–存放连接的SQL
SQL_COMMOND VARCHAR2(3000);
–定义游标
CURSOR CUR IS
SELECT COURSE FROM KECHENG GROUP BY COURSE;
BEGIN
–定义查询开头
SQL_COMMOND := 'SELECT NAME ';

FOR I IN CUR LOOP
–将结果相连接
SQL_COMMOND := SQL_COMMOND || ’ ,SUM(DECODE(course,‘’’ || I.COURSE ||
‘’‘,score,0)) ’ || I.COURSE;
DBMS_OUTPUT.PUT_LINE(SQL_COMMOND);
END LOOP;
SQL_COMMOND := SQL_COMMOND || ’ from KECHENG group by name’;
LV_SQL := 'INSERT INTO temp_ss ’ || SQL_COMMOND;
DBMS_OUTPUT.PUT_LINE(LV_SQL);
EXECUTE IMMEDIATE LV_SQL;
END;

分区
分区类型
RANGE(序列）,LIST(列表)，hash(散列)，
组合分区(range-hash，range-list)

1)ORACLE系统的分区表V_DBA_TAB_PARTITIONS，可能查看所有表的分区；
其他相关表：user_tab_partitions，user_part_tables，dba_tab_partitions，dba_part_tables

2）创建带有maxvalue和子分区模板的分区表
create table TB_TEST (
BUS_DT number,
AREA_NO varchar2(100),
QTY number,
AMOUNT number
)
partition by range(BUS_DT)
SUBPARTITION BY LIST (AREA_NO)
SUBPARTITION TEMPLATE (
SUBPARTITION SP_GZ VALUES ( 16 ),
SUBPARTITION SP_SZ VALUES ( 20 )
)
(
partition t_p1 values less than (20210131),
partition t_p2 values less than (20210228),
partition t_p3 values less than (20210331),
partition t_pmax values less than (maxvalue));
PS:设置子分区模板之后，创建新的分区就会自动生成符合格式要求的子分区

3)增加，删除，清理分区
增加分区
ALTER TABLE CUST_DEFLT_INFO ADD PARTITION P201509300000 values less than (‘201509300001’);
清理分区数据：　
ALTER TABLE A TRUNCATE PARTITION(P201512310000);
删除分区：
ALTER TABLE A DROP PARTITION(P201512310000);

4）拆分区
ORACLE分区类型是RANGE的话，不能插入分区，就是表已经有了1,3，不能通过增加分区插入2；只能用SPLIT拆临近比它大的分区:
例如：TB_LOG中已经有了P201509300000这个分区，现在要增加20150831：
ALTER TABLE TB_LOG SPLIT PARTITION P201509300000
AT(‘201508310001’)INTO(PARTITION P201508310000,PARTITION P201509300000);
指定了maxvalue的也也不增加分区，需要拆分:
alter table TB_TEST split partition t_pmax at(20210430) into (partition t_p4 partition t_pmax);

5）可以通过在线重定义把普通表转为分区表
可参考：https://blog.csdn.net/Hehuyi_In/article/details/101203868

分区表优缺点:(数据量大于2G)
1)提高查询性能
2)节约维护时间和成本
3)均衡IO