Oracle_SQL函数-单行函数

SQL函数

SQL函数分类

SQL函数主要有两种，分为单行函数、多行函数
- 单行函数：只对一行进行变换，每行返回一个结果。可以转换数据类型，可以嵌套参数可以是一列或一个值
- 多行函数：多行函数，每次对一组记录进行处理。然后对于这一组记录只返回一个结果。

两种SQL函数

单行函数

单行函数的分类：主要有字符、通用、转换、日期、数值

单行函数

字符函数

字符函数分为：大小写控制函数、字符控制函数

字符函数

大小写控制函数：这类函数改变字符的大小写

 SELECT 'helloworld Java',
        LOWER('helloworld Java') AS "Lower Last_Name",
        UPPER('helloWorld Java') AS "UPper Last_name",
        INITCAP('helloWorld Java') AS "InitCap Last_Name"
FROM dual

大小写控制函数

在全称匹配中，可以通过大小写的转换函数来进行字符的大小写转换。

字符控制函数

字符控制函数的种类

CONCAT(字符连接函数)：CONCAT('Hello', ' World!')

SELECT CONCAT('Hello', ' World!') FROM  dual

SUBSTR(字符字串函数) ：SUBSTR('HelloWorld', 1, 5)
- SUBSTR函数字符串是从 1开始，这点和 Java 有点不同。
- 函数的最后一个参数表示，从开始往后截取多少个字符串。

SELECT SUBSTR('abcdefgh',2,4) FROM dual;

LENGTH：取得字符串的长度LENGTH('HelloWorld!')
INSTR：获取某个字符在字符串中的位置INSTR('HelloWorld!','W')

SELECT LENGTH('HelloWorld!'),INSTR('HelloWorld!','W') FROM dual;

LPAD：左补齐函数LPAD(salary,10,'*')
RPAD：右补齐函数RPAD(salary,10,'*')

SELECT salary,LPAD(salary,10,'*'),RPAD(salary,10,'*') FROM employees;

LPAD,RPAD

TRIM去除前后字符函数，函数能去掉字符的前后空格，或者去掉字符串中首尾特定字符（使用该方式需要使用到FROM关键字）

SELECT TRIM('H' FROM 'HelloHWorldHH') FROM dual;
SELECT TRIM('  HelloHWorldHH   ') FROM dual;

去除特定字符去除空格

REPLACE字符串替换函数。能够替换字符串中所有的特定函数

SELECT REPLACE('abcdabcdabcd','a','m') FROM dual

字符串替换函数

数值函数

数值函数主要有以下：ROUND: 四舍五入、TRUNC: 截断、MOD: 求余
ROUND 函数：通过第二个参数，表示在第几位进行四舍五入，并且舍弃后面的数值

SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1) FROM dual

四舍五入

TRUNC函数，能够直接对于数值进行截断！不做任何进位操作

SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1) FROM dual

截断函数

MOD 函数，对数值进行求余运算。

SELECT salary, MOD(salary,1000) AS Mod_Salary FROM employees;

Mod求余函数

日期函数

Oracle 中的日期型数据实际含有两个值: 日期和时间。
函数SYSDATE既包含日期也包含时间SELECT sysdate FROM dual
日期的数学运算
- 在日期上加上或减去一个数字结果仍为日期。
- 两个日期相减返回日期之间相差的天数。
  - 日期不允许做加法运算，无意义
- 可以用数字除24来向日期中加上或减去天数。
可以对两个日期之间的天数进行操作

SELECT last_name,(SYSDATE-hire_date)/7 AS Weeks
FROM employees
WHERE department_id = 90

日期函数主要有以下几种：

函数	描述
`MONTHS_BETWEEN`	两个日期相差的月数
`ADD_MONTHS`	向指定日期中加上若干个月数
`NEXT_DAY`	指定日期的下一个星期所对应的日期
`LAST_DAY`	本月的最后一天
`ROUND`	日期四舍五入
`TRUNC`	日期截断

时间和日期的表示方法：

yyyy  年、 mm   月、dd   日
hh   小时、 mi  分钟、ss 秒
day  星期

SELECT MONTHS_BETWEEN(SYSDATE+30,SYSDATE),
       ADD_MONTHS(SYSDATE,2),
       NEXT_DAY(SYSDATE,'星期四'),
       LAST_DAY(SYSDATE),
       ROUND(SYSDATE,'mm'),ROUND(SYSDATE,'yyyy'), 
       TRUNC(SYSDATE,'mm'),TRUNC(SYSDATE,'yyyy')
FROM dual;

转换函数

隐式数据类型转换

Oracle 自动完成下列转换：

源数据类型	目标数据类型
`VARCHAR2` or `CHAR`	`NUMBER`
`VARCHAR2` or `CHAR`	`DATE`
`NUMBER`	`VARCHAR2`
`DATE`	`VARCHAR2`

DATE <---> VARCHAR2 <---> NUMBER

显式的数据类型转换

显式的数据类型转换主要使用到几个转换函数：
- TO_DATE
- TO_CAHR
- TO_NUMBER
TO_CAHR函数对日期的转换的转换
- TO_CHAR(date,'fomat_model')
格式:
- 必须包含在单引号中而且大小写敏感。
- 可以包含任意的有效的日期格式。
- 日期之间用逗号隔开。

select TO_CAHR(SYSDATE,'yyyy-MM-dd hh:mm:dd day') from dual

日期格式的元素

Model	View
YYYY	2016
YEAR	twenty sixteen
MM	06
MONTH	6月(根据本地显示) 或者June
MON	6月(根据本地显示) 或者Jun
DY	星期六或者 SATURDAY
DAY	星期六或者 SAT
DD	18

时间格式

HH:MI:SS AM         02:06:18 下午
HH24:MI:SS          14:06:18

使用双引号向日期中添加字符

-- DD "of" MONTH    18 of 6月 
select to_char(sysdate,'DD "of" MONTH') from dual

TO_DATE 函数对字符的转换
- TO_DATE(CHAR,'fomat_model')
- 使用TO_DATE函数将字符转换为数字
注意：文字与格式的字符串要相匹配！

SELECT TO_DATE('2016年06月18日 14:31:35','yyyy"年"mm"月"dd"日"hh24:mi:ss')
From dual

TO_DATE 函数对数字的转换
- 下面是在TO_CHAR 函数中经常使用的几种格式：
金额格式，如果是用9，不足的位数不会填充；但是使用0，就会在不足的位上补零

SELECT TO_CHAR(salary, '$999,999.00') SALARY
FROM   employees
WHERE  last_name = 'Ernst';

SELECT TO_CHAR(salary, '$000,000.00') SALARY
FROM   employees
WHERE  last_name = 'Ernst';

TO_NUMBER 函数对字符的转换
使用 TO_NUMBER 函数将字符转换成数值:
- TO_DATE(CHAR,'fomat_model')

SELECT TO_NUMBER('￥1,234,567,890.00','L999,999,999,999.99')
FROM dual

通用函数

这些函数适用于任何数据类型，同时也适用于空值：
- NVL (expr1, expr2)
- NVL2 (expr1, expr2, expr3)
- NULLIF (expr1, expr2)
- COALESCE (expr1, expr2, ..., exprn)
NVL 函数：将空值转换成一个已知的值
- 可以使用的数据类型有日期、字符、数字。
- 函数的一般形式:
- NVL(commission_pct,0)
- NVL(hire_date,'01-JAN-97')
- NVL(job_id,'No Job Yet')
NVL2 函数：NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。

SELECT last_name,  salary, commission_pct,
       NVL2(commission_pct, 
            'SAL+COMM', 'SAL') income
FROM  employees 
WHERE department_id IN (50, 80);

NULLIF 函数：NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1

SELECT first_name, LENGTH(first_name) "expr1", 
       last_name,  LENGTH(last_name)  "expr2",
       NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM   employees;

COALESCE 函数
- COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
- 如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。

-- 如果commision_pct 为空，返回salary，如果salary还为空就返回10.  

SELECT   last_name,
         COALESCE(commission_pct, salary, 10) comm
FROM     employees
ORDER BY commission_pct;

条件表达式
- 在 SQL 语句中使用IF-THEN-ELSE 逻辑
- 使用两种方法:
  - CASE 表达式
  - DECODE 函数
CASE 表达式示例

CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

-- 练习：查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
SELECT employee_id,last_name,department_id,salary,
       CASE department_id WHEN 10 THEN salary*1.1
                          WHEN 20 THEN salary*1.2
                          WHEN 30 THEN salary*1.3
                          ELSE salary
       END AS "new_salary"
FROM employees
WHERE department_id in (10,20,30)

DECODE 函数示例

DECODE(col|expression, search1, result1 ,
               [, search2, result2,...,]
               [, default])

-- DECODE版本
SELECT employee_id,last_name,department_id,salary,
       DECODE (department_id,10, salary*1.1,
                             20, salary*1.2,
                             30, salary*1.3,
                             salary) AS "new_salary"
FROM employees
WHERE department_id in (10,20,30)

嵌套函数

单行函数可以嵌套。
嵌套函数的执行顺序是由内到外。

人生如棋，我愿为为卒；行走虽慢，可曾见我后退一步！

Oracle_SQL函数-单行函数

SQL函数

SQL函数分类

单行函数

字符函数

大小写控制函数 ：这类函数改变字符的大小写

字符控制函数

数值函数

日期函数

转换函数

隐式数据类型转换

显式的数据类型转换

通用函数

嵌套函数

大小写控制函数：这类函数改变字符的大小写