知识点

Oracle 常用函数

1. 通用函数

  • sign(n): 比较大小函数，取数字n的符号，大于0返回1，小于0返回-1，等于0返回0。

  • decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值): 流程控制函数，如果条件等于值1，则取翻译值1，等于2，则取翻译值2，……，否则，取缺省值。

  • coalesce(expr1, expr2...exprn): 获取非空值，返回参数列表中第一个非空表达式。必须指定最少两个参数。如果所有的参数都是null，则返回null。

  • merge: 插入或更新

    1
    2
    3
    4
    5
    6
    7
    -- 语法
    MERGE INTO [target-table] A USING [source-table sql] B 
    ON([conditional expression] and [...]...)
    WHEN MATCHED THEN
      [UPDATE sql]
    WHEN NOT MATCHED THEN
      [INSERT sql]

  • 时间函数

    1
    2
    3
    4
    5
    --获取系统时间
    select sysdate from dual;--2019/5/14 12:39:23
    select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;--2019-05-14 12:39:24
    --hh为12小时制，如果时间超过12会报错
    SELECT to_date('2009-12-25 14:23:31','yyyy-mm-dd,hh:mi:ss') FROM dual;--报错

  • with as: 提取出重复数据，优化查询，with 必须紧跟引用的 select 语句，且创建的临时表必须被引用，否则报错

    1
    2
    3
    4
    5
    6
    7
    -- 语法
    WITH 
    <subquery_name>
    AS
    (<the aggregation SQL statement>)
    SELECT
    <query naming subquery_name>;

2. 分析函数

1. 聚合函数

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

2. over(partition by … order by …)

  • partition by: 按哪个字段划分组
  • order by: 按哪个字段排序

3. rank()/dense_rank over(partition by … order by …)

4. min()/max() over(partition by …)

  • MAX() OVER(PARTITION BY .. ORDER BY .. DESC): 排序规则只能为desc，否则不起作用，将查询到目前为止排序值最高字段的对应值
  • MIN() OVER(PARTITION BY .. ORDER BY .. ASC ): 排序规则只能为asc，否则不起作用，将查询到目前为止排序值最低的字段的对应值

5. lead()/lag() over(partition by … order by …)：取前面/后面第n行记录

  • lead(列名,n,m): 当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null
  • lag(列名,n,m): 当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null

6. first_value/last_value(..) over(partition by …)：取首尾记录

7. row_number() over(partition by.. order by ..)：排序（应用：分页）

8. sum()/avg()/count() over(partition by ..)

9. rows/range between … preceding and … following：上下范围内求值

  • unbounded preceding and unbouned following：针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
  • unbounded：不受控制的，无限的
  • preceding：在…之前
  • following：在…之后

3. 排序函数

1. row_number

顺序排序，它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

2. rank

跳跃排序，如果有两个第一级别时，接下来是第三级别，用于返回结果集的分区内每行的排名，行的排名是相关行之前的排名数加一。

简单来说rank函数就是对查询出来的记录进行排名，与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。

3. dense_rank

连续排序，如果有两个第一级别时，接下来是第二级别。

与 rank 函数类似，dense_rank 函数在生成序号时是连续的，而 rank 函数生成的序号有可能不连续。dense_rank 函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第三名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。

4. 集合函数

  • Union: 去重并集，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
  • Union All: 并集，对两个结果集进行并集操作，包括重复行，不进行排序
  • Intersect: 去重交集，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序
  • Minus: 去重差集，对两个结果集进行差操作，不包括重复行，MINUS前面的结果集减后面的结果集，同时进行默认规则的排序

5. 分组处理函数

GROUP函数-GROUP_ID,GROUPING,GROUPING_ID

1. rollup

1. group by rollup(A, B, C)

首先会对 (A、B、C) 进行 group by，然后对 (A、B) 进行 group by，然后是 (A) 进行 group by，最后对全表进行 group by 操作。

1
2
3
4
5
6
7
8
/*rollup两个字段*/
select group_id,job,sum(salary) from tmp_group_test group by rollup(group_id,job);
/*等同于A，B分组求和，union all，仅A分组求和，union all，全表求和*/
select group_id,job,sum(salary) from tmp_group_test group by group_id,job
union all
select group_id,NULL,sum(salary) from tmp_group_test group by group_id
union all
select NULL,NULL,sum(salary) from tmp_group_test;

2. cube

1. group by cube(A, B, C)

首先会对 (A、B、C) 进行 group by，然后依次是 (A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行 group 操作。

3. group

1. grouping()

grouping()用于区分分组后的普通行和聚合行。如果是聚合行，则返回1，反之，则是0。

2. grouping_id()

grouping_id() 是 grouping 的增强版，与 grouping 只能带一个表达式不同，它能带多个表达式。grouping_id 在功能上相当于将多个 grouping 函数的结果串接成二进制数，返回的是这个二进制数对应的十进制数。

3. group_id()

用于区分相同分组标准的分组统计结果。可通过 having group_id() < 1 来剔除重复的分组。

6. DBA 函数

1. 锁表与解锁

1. 查询被锁表

1
2
3
4
5
6
7
8
9
10
11
-- 查询锁表
select object_name
      ,machine
      ,sql_exec_start
      ,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;

2. 解除锁表

1
2
-- 解除锁表：sid,serial#
alter system kill session '977,117';

2. 表大小

1. hello 用户下，所有表

1
2
select table_name,tablespace_name,partitioned from user_tables where user='hello';
select * from all_tables where table_name='t_tmp_d';

2. 表大小

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select t1.table_name,tablespace_name,partitioned,"Size(G)",MMX,MMI,cnt from(
select table_name,tablespace_name,partitioned from user_tables where user='hello'
) t1
left join (
select  Owner,Segment_name, sum(bytes) / 1024 / 1024 / 1024 as "Size(G)"
  From Dba_segments
 Where tablespace_name like 'hello_data' 
 group by Owner,Segment_name
) t2 on t1.table_name=t2.Segment_name
LEFT JOIN(select table_name,max(partition_name) AS MMX,min(partition_name) AS MMI,count(1) AS CNT from user_tab_partitions where table_name IN (
select table_name from user_tables where user='hello' and partitioned='YES' and partition_name not in ('P19000101','P99999999','P999999','P190001'))
group by table_name
order by table_name) t3 on t1.table_name=t3.table_name
order by table_name;

3. 查看表状态

1
select * from user_tab_partitions where tablespace_name='BOARD_DATA' and table_name='FACT_KB_FM_ANL_D';

4. 查看表最大，最小分区

1
2
3
4
select table_name,max(partition_name),min(partition_name),count(1) from user_tab_partitions where table_name IN (
select table_name from user_tables where user='BOARD' and partitioned='YES' and partition_name not in ('P19000101','P99999999','P999999','P190001'))
group by table_name
order by table_name;

5. 查询分区

1
select * from user_tab_partitions where table_name='TB_ALO_LOG_VISIT' order by partition_name;

6. 表空间

1
2
3
4
5
6
7
8
9
10
11
select
　　total.tablespace_name,
　　round(total.GB,2) as Total_GB,
　　round(total.GB-free.GB, 2) as Used_GB,
    round(free.GB, 2) as free_GB,
　　round((1-free.GB/total.GB)*100, 2) as Used_Pct
　　from
　　(select tablespace_name, sum(bytes)/1024/1024/1024 as GB from dba_free_space group by tablespace_name) free,
　　(select tablespace_name, sum(bytes)/1024/1024/1024 as GB from dba_data_files group by tablespace_name) total
　　where
　　free.tablespace_name=total.tablespace_name;

3. 表的操作记录

1. 首先查找表的操作记录

1
select * from v$sqlarea a where a.SQL_TEXT like '%TABLE_NAME%' order by last_load_time desc;

2. 从上面的记录中找到 update 语句对应的 sql_id

1
select * from v$sqltext a,v$sqlarea b where a.SQL_ID=b.SQL_ID and b.SQL_ID in('cq53826tk4u3c','afftnrfhu5utk') order by b.LAST_ACTIVE_TIME desc;

3. 从上面的记录中找到最新的sql操作记录，然后找到用户名和主机

1
select * from sys.v_$session l,sys.v_$sql s where s.SQL_ID='cq53826tk4u3c' and l.USERNAME is not null;

4. drop 掉的表回收站恢复

1. 首先保证开启回收站功能，且确认当前用户的默认表空间不是 system ，否则不会进入回收站

1
2
3
4
5
6
7
8
# 命令窗口执行，保证 recyclebin=on
SQL> show parameter recycle;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle                  string      
db_recycle_cache_size                big integer 0
recyclebin                           string      on

2. 查找回收站中表的 object_name

1
select object_name,original_name,partition_name,type,ts_name,createtime,droptime from recyclebin;

3. 撤回drop 操作

1
2
# 命令窗口，利用表的 object_name 恢复表
SQL> flashback table "BIN$qBqFu9FDVG3gU47CAgrjvA==$0" to before drop;