Sql Certificate 简明教程

SQL - Using Single-Row Functions

Using Single row functions to customize output

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

Oracle SQL supplies a rich library of in-built functions which can be employed for various tasks. The essential capabilities of a functions can be the case conversion of strings, in-string or substring operations, mathematical computations on numeric data, and date operations on date type values. SQL Functions optionally take arguments from the user and mandatorily return a value.

在更广泛的类别中，有两种类型的功能：-

On a broader category, there are two types of functions :-

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

Single Row functions - Single row functions are the one who work on single row and return one output per row. For example, length and case conversion functions are single row functions.

*多行函数 * - 多行函数对组行执行操作，并针对整组行返回一个结果。它们也称为组函数。

*Multiple Row functions *- Multiple row functions work upon group of rows and return one result for the complete set of rows. They are also known as Group Functions.

Single row functions

单行函数可以是字符函数、数字函数、日期函数和转换函数。注意，这些函数用于处理数据项。这些函数需要一个或多个输入参数并在每一行上运算，从而针对每一行返回一个输出值。参数可以是列、文字或表达式。单行函数可以用于 SELECT 语句、WHERE 和 ORDER BY 子句。单行函数可以是 -

Single row functions can be character functions, numeric functions, date functions, and conversion functions. Note that these functions are used to manipulate data items. These functions require one or more input arguments and operate on each row, thereby returning one output value for each row. Argument can be a column, literal or an expression. Single row functions can be used in SELECT statement, WHERE and ORDER BY clause. Single row functions can be -

General functions - Usually contains NULL handling functions. The functions under the category are NVL, NVL2, NULLIF, COALESCE, CASE, DECODE.
Case Conversion functions - Accepts character input and returns a character value. Functions under the category are UPPER, LOWER and INITCAP. UPPER function converts a string to upper case. LOWER function converts a string to lower case. INITCAP function converts only the initial alphabets of a string to upper case.

Character functions - Accepts character input and returns number or character value. Functions under the category are CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM and REPLACE. CONCAT function concatenates two string values. LENGTH function returns the length of the input string. SUBSTR function returns a portion of a string from a given start point to an end point. INSTR function returns numeric position of a character or a string in a given string. LPAD and RPAD functions pad the given string upto a specific length with a given character. TRIM function trims the string input from the start or end. REPLACE function replaces characters from the input string with a given character.
Date functions - Date arithmetic operations return date or numeric values. Functions under the category are MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND and TRUNC. MONTHS_BETWEEN function returns the count of months between the two dates. ADD_MONTHS function add 'n' number of months to an input date. NEXT_DAY function returns the next day of the date specified. LAST_DAY function returns last day of the month of the input date. ROUND and TRUNC functions are used to round and truncates the date value.
Number functions - Accepts numeric input and returns numeric values. Functions under the category are ROUND, TRUNC, and MOD. ROUND and TRUNC functions are used to round and truncate the number value. MOD is used to return the remainder of the division operation between two numbers.

Illustrations

General functions

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

The SELECT query below demonstrates the use of NVL function.

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

Case Conversion functions

下面的 SELECT 查询演示了类型转换函数的使用。

The SELECT query below demonstrates the use of case conversion functions.

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

Character functions

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

The SELECT query below demonstrates the use of CONCAT function to concatenate two string values.

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”的数字位置。

The SELECT query below demonstrates the use of SUBSTR and INSTR functions. SUBSTR function returns the portion of input string from 1st position to 5th position. INSTR function returns the numeric position of character 'a' in the first name.

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 来美化打印员工和职位信息。

The SELECT query below demonstrates the usage of LPAD and RPAD to pretty print the employee and job information.

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

Number functions

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

The SELECT query below demonstrates the use of ROUND and TRUNC functions.

SELECT ROUND (1372.472,1)
FROM dual;

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

SELECT TRUNC (72183,-2)
FROM dual;

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

Date arithmetic operations

下面的 SELECT 查询显示了一个日期算术函数，其中执行员工招聘日期和 sysdate 之间的差值。

The SELECT query below shows a date arithmetic function where difference of employee hire date and sysdate is done.

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

Date functions

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

The SELECT query below demonstrates the use of MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY and LAST_DAY functions.

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