Oracle笔记

前言

        本文主要是对ORACLE的操作和优化进行的总结。

函数笔记

1、排序

1）partition by

       row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。

2、最大/小值

1）greatest / least（）

       greatest / least函数是用于返回列表中（列表中元素类型相同）最大/小值，与max/min函数不同的是，前者一般操作行，后者一般操作列。greatest / least函数对应的格式如下：

GREATEST(value1, value2, value3, …)

LEAST(value1, value2, value3, …)

3、行转列

1）pivot（）

       pivot函数是Oracel非常强大的数据转换工具，可以将行数据转化为列数据，使得数据分析和报表生成变得更加灵活和方便。举例为：

WITH A1 AS
 (SELECT 'A' TY, 2 CN
    FROM DUAL
  UNION ALL
  SELECT 'A' TY, 1 CN
    FROM DUAL
  UNION ALL
  SELECT 'A' TY, 3 CN
    FROM DUAL
  UNION ALL
  SELECT 'B' TH, 2 CN
    FROM DUAL
  UNION ALL
  SELECT 'B' TY, 2 CN
    FROM DUAL)
SELECT * FROM A1 PIVOT (SUM(CN) FOR TY IN('A' "D", 'B' "C"))

       其中SUM可以替换为AVG、COUNT等其他聚合函数。

4、日期相关

1）add_months

       add_months函数是用于获取上/下几个月的同一天，具体格式如下：

SELECT ADD_MONTHS(DATE '2023-11-16', +1) FROM DUAL;

       其中“-”和“+”代表上/下几个月。

2）last_day

       last_day函数是用于获取日期所在月份最后一天的函数，具体格式如下：

SELECT LAST_DAY(DATE'2023-11-16') FROM DUAL;

5、查询字符串

1）INSTR()

instr(sourceString,destString,start,appearPosition)

        sourceString代表源字符串； destString代表要从源字符串中查找的子串；start代表查找的开始位置，这个参数可选的，默认为1，如果是负数的话，则代表从右往左进行查找（值得注意的是数据仍然从左向右计算）；appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的， 默认为1。instr函数返回值为数字，代表查找到的字符串的位置，如果返回0，则代表没有查到。

6、查询表空间使用情况

SELECT a.tablespace_name "表空间名",
       total "表空间大小",
       free "表空间剩余大小",
       (total - free) "表空间使用大小",
       total / (1024 * 1024 * 1024) "表空间大小(G)",
       free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
       (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
       round((total - free) / total, 4) * 100 "使用率 %"
  FROM (SELECT tablespace_name, SUM(bytes) free
          FROM dba_free_space
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM dba_data_files
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name

7、long类型转化字符串

        在Oracle中使用已有函数很难实现long类型字段转化为字符串。但是可以通过自定义函数来实现。例如，将DDL语句中字段默认值转化成字符串：

CREATE OR REPLACE FUNCTION DBUSER.LONG_TO_CHAR_TEST240112(U_O IN VARCHAR2,U_T IN VARCHAR2, U_C IN VARCHAR2)
RETURN VARCHAR2
AS
  uVal VARCHAR2(4000);
BEGIN
  SELECT DATA_DEFAULT
    INTO uVal
    FROM all_tab_cols
   WHERE TABLE_NAME = U_T
     AND OWNER = U_O
     AND COLUMN_name = U_C;
  uVal := SUBSTR(uVal, 1, 50);
  RETURN uVal;
END LONG_TO_CHAR;

sql优化

待补充