SQL Fundamentals: Using Single-Row Functions to Customize Output使用单行函数自定义输出

时间:2022-06-16 19:23:02

SQL Fundamentals || Oracle SQL语言

DUAL is a public table that you can use to view results from functions and calculations.

The DUAL table is owned by the user SYS and can be accessed by all users.

It contains one column, DUMMY, and one row with the value X.

Using Single-Row Functions to Customize Output

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.

  • Describe various types of functions available in SQL
  • Use character, number, and date functions in SELECT statements

字符、数字、日期函数

1、SQL Functions

SQL Fundamentals: Using Single-Row Functions to Customize Output使用单行函数自定义输出

Functions are a very powerful feature of SQL. They can be used to do the following:

Perform calculations on data

执行数据计算

Modify individual data items

修改单独的数据项

Manipulate output for groups of rows

操纵行组的输出

Format dates and numbers for display

格式化日期和数字进行显示

Convert column data types

转换列数据类型

SQL functions sometimes take arguments and always return a value.

SQL有时候接收参数并总是返回一个值.

2、Two Types of SQL Function:

SQL Fundamentals: Using Single-Row Functions to Customize Output使用单行函数自定义输出

Single-row functions

单行函数

These functions operate on single rows only and return one result per row.

单行函数只操作单个行并为每一行返回一个结果.

- 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.

3、Single-row functions单行函数

Single row functions

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 are used to manipulate data items. They accept one or more arguments and return one value for each row that is returned by the query.

(1)特点

Manipulate data items

操作数据项

Accept arguments and return one value

接收参数并返回一个值

Act on each row that is returned

每一行进行操作

Return one result per row

每一行返回一个值

Many modify the data type

单行函数可以修改数据类型

Can be nested

单行函数可以嵌套

Accept arguments that can be a column or an expression

An argument can be one of the following:

  • User-supplied constant
  • Variable value
  • Column name
  • expression

函数接收的参数可以是列名或者表达式

  • 用户提供的常量
  • 变量值
  • 列名
  • 表达式

(2)、语法

Function_name [(arg1,arg2….)]

(3)、类型

SQL Fundamentals: Using Single-Row Functions to Customize Output使用单行函数自定义输出

字符函数

character functions

Accept character input and can return both character and number values.

数字函数

number functions

Accept numeric input and return numeric values.

日期函数

date functions

Operate on values of the DATE data type (All date functions return a value of the DATE data type except the MONTHS_BETWEEN function, which returns a number)

所有日期函数都返回一个DATE类型的值,除了MONTHS_BETWEEN函数,它返回一个数字.

转换函数

Conversion function

Convert a value from one data type to another

通用函数

General function

      • General functions - Usually contains NULL handling functions. The functions under the category are NVL, NVL2, NULLIF, COALESCE, CASE, DECODE.

NVL:对空值做处理

NVL2:对空值做处理

NULLIF:对空值做处理

COALESCE

CASE

DECODE

General functions

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