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

列印頁面