Oracle易忘知识点记录

1、SQL Select语句完整的执行顺序：

①from子句组装来自不同数据源的数据；

②where子句基于指定的条件对记录行进行筛选；

③group by子句将数据划分为多个分组；

④使用聚集函数进行计算；

⑤使用having子句筛选分组；

⑥计算所有的表达式；

⑦使用order by对结果集进行排序。

⑧select 集合输出。

Having与Where的区别

* where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。

* having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

 

 

2、创建索引（CREATE INDEX）时，将索引分配到专门的索引空间；对于有实时数据改变的表，需要在最后加上online关键字，就不会阻塞DML语句。

CREATE INDEX index_nm ON table_nm(col_nm)  TABLESPACE tablespace_nm ONLINE;

3、其他

在sql脚本中创建存储过程时，存储过程部分代码结束后，在其下一行加一个反斜杠 / ，表示存储过程结束，防止oracle将下面的其他sql语句当成存储过程的一部分。

在某个字段后面追加内容：

UPDATE  table_nm T SET T.IP=T.IP||',127.0.0.1' WHERE 筛选条件;

4、oracle中对于''和null的判断必须使用 IS NULL才能正确得到返回值，使用< = >都是false；使用like进行模糊查询时，'%%'也无法查出null的记录。

5、EXISTS与IN：

exists用法是把主查询中的字段传入到子查询中去。如果有符合的条件，会停止全表检索，返回TRUE。所以效率才要高于IN，IN是要进行完全表检索得到集合才会结束执行。而EXISTS遇到符合的 条件，就会停止执行子查询。

 

6、select ...for update语句

for update：锁定所有表符合条件的行

for update of A.字段：锁定A表符合条件的行

 

7、为字段添加注释时，不要有&字符，oracle会把&解析成变量 。

8、在为大表添加有默认值的字段时，使用匿名块循环处理，将一条语句拆分成三条执行。

ALTER TABLE MKMTJNL ADD (COMT_DIF_AMT NUMBER(9,2));
declare
  I       INT;
  V_COUNT INT;
  V_LOOP  INT;
BEGIN
  SELECT COUNT(*)
    INTO V_COUNT
    FROM MKMTJNL;
  SELECT ceil(V_COUNT / 100000) INTO V_LOOP FROM dual;
  I := 1;
  WHILE I <= V_LOOP LOOP
    UPDATE MKMTJNL SET COMT_DIF_AMT = 0.00 WHERE ROWNUM <= 100000;
    COMMIT;
    I := I + 1;
  END LOOP;
END;

ALTER TABLE MKMTJNL MODIFY (COMT_DIF_AMT DEFAULT 0.00);

 

9、MINUS(减集),，NTERSECT(交集)，UNION ALL(并集)，UNION（去重并集）

MINUS：运用在两个SQL语句上，使用第一条sql的查询结果减去第二条sql的查询结果集，结果的是第一条sql结果集中的有并且第二条sql结果集中没有的记录。

NTERSECT：取两个sql结果集的交集，结果是两个 sql结果集中都存在的记录。

UNION ALL：两个sql结果集的完全并集。

UNION：两个sql结果集记录去重后的并集（distinct）。

使用条件：两个sql查询的列必须明确，不能用*；两个sql列的个数，字段类型，顺序必须一致。

 

10、WITN AS短语

with as 相当于虚拟视图。如果with as短语所定义的表名被调用两次以上，则优化器会自动将with as短语所获取的数据放入一个temp表里，如果只是被调用一次，则不会。

特别对于union all比较有用。因为union all的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用with as短语，则只要执行一遍即可。

WITH stat AS（SELECT * FROM table_nm）select * from stat;

11、LPAD（左侧填充）与RPAD（右侧填充）函数

LPAD（原字符串str，长度/正整数x，[用于填充的字符串]y）：①如果没有第三个参数y（或者长度x比字符串本身长度小），则表示从左侧截取长度x个字符 ②使用y填充str到直到长度达到x。

SELECT lpad('abcde',3) FROM dual;　　　　　　 --abc
SELECT lpad('abcde',8,'x') FROM dual;　　　　--xxxabcde

 12、SUBSTR与REGEXP_SUBSTR函数

SUBSTR(原字符串str，截取开始位置x，[截取个数]y)：①如果第三个参数为空，表示从x位开始（包括x位）截取到最后一位，位数从1开始；②截取x位之后（包括x位）的y个字符。

SELECT SUBSTR('abcdef',3) FROM dual;　　　　　　　　　　--cdef
SELECT SUBSTR('abcdefefgh',3,5) FROM dual;　　　　　　 --cdefe

REGEXP_SUBSTR(String, pattern, position, occurrence, [modifier]) 五个参数分别表示：①要处理的字符串  ②进行匹配的正则表达式 ③匹配的开始位置，默认为1 ④字符串处理后取第几个匹配组，默认为1 ⑤模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

SELECT REGEXP_SUBSTR('111,222,333,444', '[^,]+', 1, 1) FROM dual;　　　　--111
SELECT REGEXP_SUBSTR('111,222,333,444', '[^,]+', 1, 2) FROM dual;　　　　--222

[^,]+ 表示匹配以非,开始，非，结束的规则。（^用在方括号[]里面时表示非，否则表示字符串开始位置）。

常用应用：

SELECT REGEXP_SUBSTR('111,222,333,444', '[^,]+', 1, LEVEL) spli,LEVEL FROM dual 
CONNECT BY LEVEL <= REGEXP_COUNT('111,222,333,444', '[,]', 1) + 1;　　--仅限单条记录的表

SELECT REGEXP_SUBSTR('111,222,333,444', '[^,]+', 1, ROWNUM) spli,ROWNUM FROM dual 
CONNECT BY ROWNUM <= REGEXP_COUNT('111,222,333,444', '[,]', 1) + 1;　　　　--仅限单条记录的表

当连接条件（connect by条件）没有限制记录之间的关系（即 connect by里没有类似 id=prior pid的条件，而是 connect by rownum<xxx 或connect by level<xxx ）时，每一条记录都会作为自己或者其他记录的子节点，也就说，每一条记录的子节点就是表上所有的记录。而树的层数就是rownum（或是level）值。

13、REPLACE函数

REPLACE（原字符串str，被替换的字符串x，[替换字符串]y}）：①如果第三个参数为空，则将原字符串str中所有的x都删除（换成空字符串）②使用y替换str中出现的所有x。

SELECT REPLACE('abcabcabc','b') FROM dual;　　　　　　--acacac
SELECT REPLACE('abcabcabc','b',' ') FROM dual;　　　　--a ca ca c

14、WM_CONCAT函数

WM_CONCAT（列名）：把列值用逗号分隔并显示成一行（列转行）

SELECT WM_CONCAT(COL)
  FROM (SELECT 1 COL FROM DUAL
        UNION ALL
        SELECT 2 COL FROM DUAL);　　　　　　--1,2

15、CAST函数

CAST(expr AS type_name) ：将一种类型转换成另一种类型。而不仅仅是限于使用用to_number、to_char（）以及to_date（）类型。

使用to_char（）将日期转换成字符串时，注意分钟使用mi，而不是mm（mm表示月份）。

16、DECODE与SIGN函数

SIGN（val）：根据val的值是0、正数还是负数，分别返回0、1、-1

decode(条件/字段,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值):根据条件与各个值比对，如果相等，则取对应的返回值；如果没有匹配的值，则返回缺省值。

SELECT DECODE('x','y','值y','z','值z','缺省') FROM dual;　　　　--缺省
SELECT DECODE('x','x','值x','z','值z','缺省') FROM dual;　　　　--值x

sign与decode联合使用

SELECT DECODE(SIGN(4-3),0,'表达式等于0',1,'表达式大于0',-1,'表达式小于0') FROM dual;　　　　--表达式大于0

使用decode进行自定义排序

SELECT *FROM 
       (SELECT 'x' COL FROM DUAL
        UNION ALL
        SELECT 'y' COL FROM DUAL
        UNION ALL
        SELECT 'z' COL FROM DUAL)
 ORDER BY DECODE(COL, 'x', 3, 'y', 2, 'z', 1);

结果：

17、LENGTH与LENGTHB

 LENGTH（字符串/字段值）：计算字符串的字符个数。

 LENGTHB（字符串/字段值）：计算字符串的字节个数。

SELECT  length('这是6个字符') FROM dual;　　　　--6
SELECT lengthb('这是12个字节') FROM dual;　　　　--12

18、OVER(partition by 按哪个字段划分组，order by 按哪个字段排序)分析函数

说明：聚合函数（如sum()、max()、COUNT()等）可以计算基于组的某种聚合值，但是聚合函数对于某个组只能返回一行记录。若想对于某组返回多行记录，则需要使用分析函数。

 COUNT() OVER()：应用在查询明细的同时查询总记录数，可使用partition by分组

SELECT COL,COUNT(*)OVER(PARTITION BY COL) COUNT_NUM FROM 
       (SELECT 'x' COL FROM DUAL
        UNION ALL
        SELECT 'x' COL FROM DUAL
        UNION ALL
        SELECT 'y' COL FROM DUAL
        UNION ALL
        SELECT 'y' COL FROM DUAL);

SUM()OVER() ：累加，统计总和，使用方法与COUNT()OVER()相同

偏移分析函数LAG（） LEAD()

lag与lead函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。偏移分析函数必须使用order by进行排序，否则报错。

SELECT COL 本行COL,
       LAG(COL,1,NULL)OVER(ORDER BY col) 上一行COL,
       LEAD(COL,1,NULL)OVER(ORDER BY col) 下一行COL FROM 
       (SELECT 'a' COL FROM DUAL
        UNION ALL
        SELECT 'b' COL FROM DUAL
        UNION ALL
        SELECT 'c' COL FROM DUAL
        UNION ALL
        SELECT 'd' COL FROM DUAL);