Oracle_SQL函数-单行函数

SQL函数

SQL函数分类

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

Oracle_SQL函数-单行函数

单行函数

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

Oracle_SQL函数-单行函数

字符函数

  • 字符函数分为:大小写控制函数字符控制函数

Oracle_SQL函数-单行函数

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

Oracle_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

Oracle_SQL函数-单行函数

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

Oracle_SQL函数-单行函数

  • 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;

Oracle_SQL函数-单行函数

  • LENGTH:取得字符串的长度LENGTH('HelloWorld!')
  • INSTR:获取某个字符在字符串中的位置INSTR('HelloWorld!','W')
SELECT LENGTH('HelloWorld!'),INSTR('HelloWorld!','W') FROM dual;

Oracle_SQL函数-单行函数

  • LPAD:左补齐函数LPAD(salary,10,'*')
  • RPAD:右补齐函数RPAD(salary,10,'*')
SELECT salary,LPAD(salary,10,'*'),RPAD(salary,10,'*') FROM employees;

Oracle_SQL函数-单行函数

  • TRIM去除前后字符函数,函数能去掉字符的前后空格,或者去掉字符串中首尾特定字符(使用该方式需要使用到FROM关键字)
SELECT TRIM('H' FROM 'HelloHWorldHH') FROM dual;
SELECT TRIM(' HelloHWorldHH ') FROM dual;

Oracle_SQL函数-单行函数Oracle_SQL函数-单行函数

  • REPLACE字符串替换函数。能够替换字符串中所有的特定函数
SELECT REPLACE('abcdabcdabcd','a','m') FROM dual

Oracle_SQL函数-单行函数

数值函数

  • 数值函数主要有以下:ROUND: 四舍五入 、TRUNC: 截断、MOD: 求余

    Oracle_SQL函数-单行函数
  • ROUND 函数:通过第二个参数,表示在第几位进行四舍五入,并且舍弃后面的数值
SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1) FROM dual

Oracle_SQL函数-单行函数

  • TRUNC函数,能够直接对于数值进行截断!不做任何进位操作
SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1) FROM dual

Oracle_SQL函数-单行函数

  • MOD 函数,对数值进行求余运算。
SELECT salary, MOD(salary,1000) AS Mod_Salary FROM employees;

Oracle_SQL函数-单行函数

日期函数

  • 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 函数中经常使用的几种格式:

      Oracle_SQL函数-单行函数
  • 金额格式,如果是用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函数-单行函数
上一篇:POI-根据Cell获取对应的String类型值


下一篇:ECMA Script 6_Symbol() 唯一类型值声明函数_Symbol 数据类型