SQL - 单行函数的使用

使用单行函数自定义输出

Oracle SQL 提供了丰富的内置函数库，可用于各种任务。函数的基本功能可以是字符串的大小写转换、字符串内或子字符串操作、数值数据的数学计算以及日期类型值的日期操作。SQL 函数可以选择性地接受用户的参数，并强制返回一个值。

从更广泛的类别来看，函数分为两种类型：

单行函数 - 单行函数是对单行进行操作并为每行返回一个输出的函数。例如，长度和大小写转换函数是单行函数。

多行函数 - 多行函数作用于一组行，并为整组行返回一个结果。它们也称为分组函数。

单行函数

单行函数可以是字符函数、数值函数、日期函数和转换函数。请注意，这些函数用于操作数据项。这些函数需要一个或多个输入参数，并对每一行进行操作，从而为每一行返回一个输出值。参数可以是列、字面量或表达式。单行函数可以在 SELECT 语句、WHERE 子句和 ORDER BY 子句中使用。单行函数可以是 -

通用函数 - 通常包含 NULL 处理函数。此类别下的函数包括 NVL、NVL2、NULLIF、COALESCE、CASE、DECODE。
大小写转换函数 - 接受字符输入并返回字符值。此类别下的函数包括 UPPER、LOWER 和 INITCAP。
- UPPER 函数将字符串转换为大写。
- LOWER 函数将字符串转换为小写。
- INITCAP 函数仅将字符串的初始字母转换为大写。
字符函数 - 接受字符输入并返回数字或字符值。此类别下的函数包括 CONCAT、LENGTH、SUBSTR、INSTR、LPAD、RPAD、TRIM 和 REPLACE。
- CONCAT 函数连接两个字符串值。
- LENGTH 函数返回输入字符串的长度。
- SUBSTR 函数从给定的起始点到结束点返回字符串的一部分。
- INSTR 函数返回给定字符串中字符或字符串的数字位置。
- LPAD 和 RPAD 函数使用给定的字符将给定字符串填充到特定长度。
- TRIM 函数从开头或结尾修剪字符串输入。
- REPLACE 函数用给定字符替换输入字符串中的字符。
日期函数 - 日期算术运算返回日期或数值。此类别下的函数包括 MONTHS_BETWEEN、ADD_MONTHS、NEXT_DAY、LAST_DAY、ROUND 和 TRUNC。
- MONTHS_BETWEEN 函数返回两个日期之间月份的数量。
- ADD_MONTHS 函数将“n”个月添加到输入日期。
- NEXT_DAY 函数返回指定日期的下一天。
- LAST_DAY 函数返回输入日期月份的最后一天。
- ROUND 和 TRUNC 函数用于舍入和截断日期值。
数字函数 - 接受数字输入并返回数值。此类别下的函数包括 ROUND、TRUNC 和 MOD。
- ROUND 和 TRUNC 函数用于舍入和截断数值。
- MOD 用于返回两个数字之间除法运算的余数。

示例

通用函数

下面的 SELECT 查询演示了 NVL 函数的使用。

SELECT first_name, last_name, salary, NVL (commission_pct,0) 
FROM employees
WHERE rownum < 5;

FIRST_NAME           LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)
-------------------- ------------------------- ---------- ---------------------
Steven               King                           24000                     0
Neena                Kochhar                        17000                     0
Lex                  De Haan                        17000                     0
Alexander            Hunold                          9000                     0

大小写转换函数

下面的 SELECT 查询演示了大小写转换函数的使用。

SELECT UPPER (first_name), INITCAP (last_name), LOWER (job_id)
FROM employees
WHERE rownum < 5;

UPPER(FIRST_NAME)    INITCAP(LAST_NAME)        LOWER(JOB_
-------------------- ------------------------- ----------
STEVEN               King                      ad_pres
NEENA                Kochhar                   ad_vp
LEX                  De Haan                   ad_vp
ALEXANDER            Hunold                    it_prog

字符函数

下面的 SELECT 查询演示了使用 CONCAT 函数连接两个字符串值。

SELECT CONCAT (first_name, last_name) 
FROM employees
WHERE rownum < 5;

CONCAT(FIRST_NAME,LAST_NAME)
--------------------------------
EllenAbel
SundarAnde
MozheAtkinson
DavidAustin

下面的 SELECT 查询演示了 SUBSTR 和 INSTR 函数的使用。SUBSTR 函数返回从第 1 位到第 5 位的输入字符串部分。INSTR 函数返回名字中字符“a”的数字位置。

SELECT SUBSTR (first_name,1,5), INSTR (first_name,'a')
FROM employees
WHERE rownum < 5;

SUBST INSTR(FIRST_NAME,'A')
----- ---------------------
Ellen                     0
Sunda                     5
Mozhe                     0
David                     2

下面的 SELECT 查询演示了使用 LPAD 和 RPAD 美化打印员工和职位信息。

SELECT RPAD(first_name,10,'_')||LPAD (job_id,15,'_')
FROM employees
WHERE rownum < 5;

RPAD(FIRST_NAME,10,'_')||
-------------------------
Steven____________AD_PRES
Neena_______________AD_VP
Lex_________________AD_VP
Alexander_________IT_PROG

数字函数

下面的 SELECT 查询演示了 ROUND 和 TRUNC 函数的使用。

SELECT ROUND (1372.472,1)
FROM dual;

ROUND(1372.472,1)
-----------------
           1372.5

SELECT TRUNC (72183,-2)
FROM dual;

TRUNC(72183,-2)
---------------
          72100

日期算术运算

下面的 SELECT 查询显示了一个日期算术函数，其中计算了员工入职日期和系统日期的差值。

SELECT employee_id, (sysdate - hire_date) Employment_days
FROM employees
WHERE rownum < 5;

EMPLOYEE_ID EMPLOYMENT_DAYS
----------- ---------------
        100      3698.61877
        101      2871.61877
        102      4583.61877
        103      2767.61877

日期函数

下面的 SELECT 查询演示了 MONTHS_BETWEEN、ADD_MONTHS、NEXT_DAY 和 LAST_DAY 函数的使用。

SELECT employee_id, MONTHS_BETWEEN (sysdate, hire_date) Employment_months
FROM employees
WHERE rownum < 5;

EMPLOYEE_ID EMPLOYMENT_MONTHS
----------- -----------------
        100        121.504216
        101        94.3751837
        102        150.633248
        103        90.9558289

SELECT ADD_MONTHS (sysdate, 5), NEXT_DAY (sysdate), LAST_DAY (sysdate)
FROM dual;

ADD_MONTH NEXT_DAY( LAST_DAY(
--------- --------- ---------
01-JAN-14 05-AUG-13 31-AUG-13

打印页面