【笔记】Oracle常用函数

RaymondLam
240 阅读5分钟

常用函数

NVL(EXP1, EXP2)

判断EXP1是否为空，若EXP1为空，则返回EXP2，否则返回EXP1。

SELECT LAST_NAME,
       NVL(TO_CHAR(COMMISSION_PCT), 'not applicable') COMMISSION
  FROM EMPLOYEES
 WHERE LAST_NAME LIKE 'b%'
 ORDER BY LAST_NAME;

NVL2(EXP1, EXP2, EXP3)

判断EXP1是否为空，若EXP1不为空，则返回EXP2；否则返回EXP3。

SELECT LAST_NAME,
       SALARY,
       NVL2(COMMISSION_PCT, SALARY + (SALARY * COMMISSION_PCT), SALARY) INCOME
  FROM EMPLOYEES
 WHERE LAST_NAME LIKE 'b%'
 ORDER BY LAST_NAME;

NULLIF(EXP1, EXP2)

判断EXP1与EXP2是否相等，若相等，则返回NULL，否则返回EXP1。

SELECT E.LAST_NAME,
       NULLIF(E.JOB_ID, J.JOB_ID) "old job id"
  FROM EMPLOYEES E, JOB_HISTORY J
 WHERE E.EMPLOYEE_ID = J.EMPLOYEE_ID
 ORDER BY LAST_NAME, "old job id";

DECODE(EXP, SEARCH1, RESULT1, SEARCH2, RESULT2,..., DEFAULT)

将EXP与SEARCH1进行比较，若相等，则返回RESULT1，否则将EXP与SEARCH2进行比较，依次类推，若最后都没有匹配上，则返回DEFAULT。

SELECT PRODUCT_ID,
       DECODE(WAREHOUSE_ID,
              1,
              'Southlake',
              2,
              'San Francisco',
              3,
              'New Jersey',
              4,
              'Seattle',
              'Non domestic') "Location"
  FROM INVENTORIES
 WHERE PRODUCT_UD < 1775
 ORDER BY PRODUCT_ID, "Location";

WM_CONCAT(COLNAME)

行转列，将查询到的某一列所有的值，用逗号进行分隔，串成一个字符串。

SELECT WM_CONCAT(COL1)
  FROM (SELECT 'a' AS COL1
          FROM DUAL
        UNION ALL
        SELECT 'b' AS COL1
          FROM DUAL);

CONNECT BY

构建递归查询。

--往叶子结点找
SELECT T.ORG_ID, T.ORG_NAME, T.PARENT_ID
  FROM B2C_UM_ORG T
 START WITH T.ORG_ID = 1000
CONNECT BY PRIOR T.ORG_ID = T.PARENT_ID;

--往根结点找
SELECT T.ORG_ID, T.ORG_NAME, T.PARENT_ID
  FROM B2C_UM_ORG T
 START WITH T.ORG_ID = 1000
CONNECT BY PRIOR T.PARENT_ID = T.ORG_ID;

SIGN(N)

当N > 0，返回1；当N = 0，返回0；当N < 0，返回-1

SELECT SIGN(-2), SIGN(0), SIGN(2)
  FROM DUAL;

LPAD(EXP1, N, EXP2)

若EXP1的长度不足N，则在EXP1左侧用EXP2进行补充，若没制定EXP2，则缺省用空格进行补充。

SELECT LPAD('abc', 5, '*')
  FROM DUAL;

RPAD(EXP1, N, EXP2)

若EXP1的长度不足N，则在EXP1右侧用EXP2进行补充，若没制定EXP2，则缺省用空格进行补充。

SELECT RPAD('abc', 5, '*')
  FROM DUAL;

ROUND(N[,K INTEGER])

该函数会进行四舍五入操作。

  • 当第二个参数省略时，默认为0，即取N得整数部分；
  • 当第二个参数> 0时，保留小数后K位；
  • 当第二个参数< 0时，舍去小数部分，并将小数点左边K位全置为0，第K位进行四舍五入。
SELECT ROUND(123.456), ROUND(123.456, 2), ROUND(123.456, -2)
  FROM DUAL;

ROUND(DATE[,FRM])

该函数会进行进位操作，根据制定FRM格式来截取日期，并返回日期类型。

  • 6月份之后，算为下一年；
  • 15号之后，算为下一月；
  • 12点之后，算为第二天；
  • 30分之后，算为下一小时；
  • 30秒之后，算为下一分钟。
SELECT ROUND(TO_DATE('2011-07-01 10:20:09', 'yyyy-mm-dd hh24:mi:ss'), 'YY'), 
       ROUND(TO_DATE('2011-05-16 10:20:09', 'yyyy-mm-dd hh24:mi:ss'), 'MM'), 
       ROUND(TO_DATE('2011-05-14 12:20:09', 'yyyy-mm-dd hh24:mi:ss'), 'DD'), 
       ROUND(TO_DATE('2011-05-14 10:30:09', 'yyyy-mm-dd hh24:mi:ss'), 'HH24'), 
       ROUND(TO_DATE('2011-05-14 10:20:30', 'yyyy-mm-dd hh24:mi:ss'), 'MI') 
  FROM DUAL;

TRUNC(N[,K INTEGER])

该函数会进行不四舍五入的截取操作。

  • 当第二个参数省略时，默认为0，即取N得整数部分；
  • 当第二个参数> 0时，保留小数后K位；
  • 当第二个参数< 0时，舍去小数部分，并将小数点左边K位全置为0。
SELECT TRUNC(123.456), TRUNC(123.456, 2), TRUNC(123.456, -2)
  FROM DUAL;

TRUNC(DATE[,FRM])

该函数会进行进位操作，根据制定FRM格式来截取日期，并返回日期类型。

SELECT TRUNC(TO_DATE('2011-07-01 10:20:09', 'yyyy-mm-dd hh24:mi:ss'), 'YY'), 
       TRUNC(TO_DATE('2011-07-01 10:20:09', 'yyyy-mm-dd hh24:mi:ss'), 'MM'), 
       TRUNC(TO_DATE('2011-07-01 10:20:09', 'yyyy-mm-dd hh24:mi:ss'), 'DD'), 
       TRUNC(TO_DATE('2011-07-01 10:20:09', 'yyyy-mm-dd hh24:mi:ss'), 'HH24'), 
       TRUNC(TO_DATE('2011-07-01 10:20:09', 'yyyy-mm-dd hh24:mi:ss'), 'MI') 
  FROM DUAL;

MONTHS_BETWEEN(DATE1, DATE2)

返回日期DATE1和DATE2之间相差的月数。DATE1 > DATE2，返回正数，否则返回负数。

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2011-01-01', 'yyyy-mm-dd'))
  FROM DUAL;

ADD_MONTHS(D, N)

在制定日期D的基础上，加或减N个月。

SELECT ADD_MONTHS(SYSDATE, 2)
  FROM DUAL;

LAST_DAY(DATE)

返回制定日期所在月份的最后一天的日期。

SELECT LAST_DAY(SYSDATE)
  FROM DUAL;

SYSDATE +/- N

指定日期加、减N天。

SELECT SYSDATE + 2, SYSDATE - 2
  FROM DUAL;

ABS(N)

返回指定数字的绝对值。

SELECT ABS(-2)
  FROM DUAL;

CEIL(N)

返回大于或等于N的最小正数。

SELECT CEIL(123.456)
  FROM DUAL;

FLOOR(N)

返回小于或等于N的最大正数。

SELECT FLOOR(123.456)
  FROM DUAL;

MOD(N1, N2)

取余运算，返回N1除以N2的余数，若N2为0，则直接返回N1。

SELECT MOD(10, 3), MOD(10, 0)
  FROM DUAL;

POWER(N1, N2)

返回以N1为底数，N2为指数的值。

SELECT POWER(2, 3)
  FROM DUAL;

RANK() OVER([QUERY_PARTITION_CLAUSE], ORDER BY CLAUSE)

用于次序排名(ORDER BY排序可能会出现序号一致的情况，且次序不连续，如1134)。

--按部门进行分组，并将薪水从高到低排序
SELECT DEPT_ID, LAST_NAME, SALARY,
       RANK() OVER(PARTITION BY DEPT_ID ORDER BY SALARY DESC) SAL_RANK
  FROM EMPLOYEES;

DENSE_RANK() OVER([QUERY_PARTITION_CLAUSE], ORDER BY CLAUSE)

用于次序排名(ORDER BY排序可能会出现序号一致的情况，且次序连续，如1123)。

--按部门进行分组，并将薪水从高到低排序
SELECT DEPT_ID, LAST_NAME, SALARY,
       DENSE_RANK() OVER(PARTITION BY DEPT_ID ORDER BY SALARY DESC) SAL_RANK
  FROM EMPLOYEES;

RTRIM(CHAR[,SET])

将字符串最右边出现在SET中的字符去除；没有指定SET，则默认是一个空格。

SELECT RTRIM('abcdef', 'ef')
  FROM DUAL;

LTRIM(CHAR[,SET])

将字符串最左边出现在SET中的字符去除；没有指定SET，则默认是一个空格。

SELECT RTRIM('abcdef', 'ab')
  FROM DUAL;

TRIM([LEADING|TRAILING|BOTH][SET] FROM CHAR)

将字符串开头/结尾/两端出现的SET的字符去除；没有指定SET，则默认是一个空格；没有指定参数，则默认两端。

SELECT TRIM(LEADING 'a' FROM 'abcdef'),
       TRIM(TRAILING 'f' FROM 'abcdef'),
       TRIM(BOTH 'a' FROM 'abcdefa'),
       TRIM('a' FROM 'abcdefa')
  FROM DUAL;

TO_CHAR(NUMBER[, FRM])

返回指定格式的字符串。

SELECT TO_CHAR(SALARY, '$99,999.99'), TO_CHAR(SALARY, '99,999.99'), SALARY
  FROM EMPLOYEES;

TO_CHAR(DATETIME[, FRM])

返回指定格式的字符串。

SELECT TO_CHAR(SYSTIMESTAMP, 'yyyy-mm-dd hh24:mi:ss.ff')
  FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')
  FROM DUAL;

TO_NUMBER(CHAR[, FRM])

返回指定格式的数字类型。

SELECT TO_NUMBER('01c00009', 'xxxxxxxxxx') --十六进制转十进制
  FROM DUAL;

GREATEST(EXP1, EXP2[,..])

返回EXP1至EXPN中最大值，其中EXP1将决定返回的类型。

SELECT GREATEST('HARRY', 'HARRIOT', 'HAROLD') "Greatest"
  FROM DUAL;

LEAST(EXP1, EXP2[,..])

返回EXP1至EXPN中最小值，其中EXP1将决定返回的类型。

SELECT LEAST(4, 5, 6, 1)
  FROM DUAL;

LENGTH(CHAR)

返回字符串长度。

SELECT LENGTH('Hello world.')
  FROM DUAL;

UPPER(CHAR)

返回大写的字符串。

SELECT UPPER('Hello world.')
  FROM DUAL;

LOWER(CHAR)

返回小写的字符串。

SELECT LOWER('Hello world.')
  FROM DUAL;

SUBSTR(STRING, POS[, SUBSTRING_LEN])

返回指定位置，指定长度的字符串。

  • POS为0时，Oracle自动转换为1；
  • POS为正数，代表从原字符串左边计算POS位置；
  • POS为负数，代表从原字符串右边计算POS位置；
  • SUBSTRING_LEN不指定时，代表从POS位置到原字符串的结尾。
SELECT SUBSTR('abcdef', 0, 3),
       SUBSTR('abcdef', 2, 3),
       SUBSTR('abcdef', 2),
       SUBSTR('abcdef', -3, 2)
  FROM DUAL;

INITCAP(CHAR)

返回首字母大写，其他小写。

SELECT INITCAP('HELlo wORld!')
  FROM DUAL;

INSTR(STRING, SUBSTRING[,POS[,OCCURENCE]])

返回字符串匹配的位置，返回值为0，代表没有找到相应的匹配。

  • POS为0时，Oracle自动转换为1；
  • POS为正数，代表从原字符串左边计算POS位置；
  • POS为负数，代表从原字符串右边计算POS位置，并从右向左进行搜索；
  • OCCURENCE代表成功匹配的次数。
SELECT INSTR('abcdef', 'b'),
       INSTR('abcdef', 'b', 3),
       INSTR('abcdef', 'b', 3, 2),
       INSTR('abcdef', 'b', -1)
  FROM DUAL;

REPLACE(CHAR, SEARCH_STRING[,REPLACE_STRING])

将字符串中SEARCH_STRING替换成REPLACE_STRING；若REPLACE_STRING不指定，则直接将匹配到的SEARCH_STRING删除。

SELECT REPLACE('abcdef', 'd', 's')
  FROM DUAL;

INTERSECT

对两个结果集进行交集操作，不包括重复行，同时进行默认规则排序。

SELECT VENDOR_NAME
  FROM B2C_MERCHANT_VENDOR
INTERSECT
SELECT NAME
  FROM B2C_MALL_MEMBERINFOR;

MINUS

对两个结果集进行差集操作，不包括重复行，同时进行默认规则排序。

SELECT VENDOR_NAME
  FROM B2C_MERCHANT_VENDOR
MINUS
SELECT NAME
  FROM B2C_MALL_MEMBERINFOR;

UNION ALL

对两个结果集进行并集操作，包括重复行，不进行排序。

SELECT VENDOR_NAME
  FROM B2C_MERCHANT_VENDOR
UNION ALL
SELECT NAME
  FROM B2C_MALL_MEMBERINFOR;

UNION

对两个结果集进行并集操作，不包括重复行，同时进行默认规则排序。

SELECT VENDOR_NAME
  FROM B2C_MERCHANT_VENDOR
UNION
SELECT NAME
  FROM B2C_MALL_MEMBERINFOR;

DUAL 伪表

通常与CONNECT BY结合，用于构造测试数据。

INSERT /*+ APPEND */
  INTO TMP_TEST
       (IDS, NAMES, STATUS)
    SELECT LPAD(ROWNUM, 10, '0'),
           DBMS_RANDOM.STRING('a', 10),
           TRUNC(DBMS_RANDOM.VALUE(0, 10))
      FROM DUAL
    CONNECT BY LEVEL < 1000;
avatar
文章
阅读
粉丝