Oracle--单行函数(字符函数、数值函数、日期函数、转换函数、通用函数、条件表达式)详解

喜欢ヅ旅行 2021-08-13 14:31 596阅读 0赞

### 什么是SQL的函数？ ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]

#### 函数的类型 ####

![在这里插入图片描述][20190909220434846.png]

#### 单行函数 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]

#### 字符函数 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]  
示例：接收字符输入返回字符或者数值， d u a l \\color\{red\}\{dual\} dual是伪表  
1.把小写的字符串转换成大写的字符：upper(str)

select upper('tomcat') from dual;

![在这里插入图片描述][20190909220801532.png]  
2.把大写字符变成小写字符：lower(str)

select lower('TOMCAT') from dual;

![在这里插入图片描述][20190909220946658.png]  
3.把首字符大写：initcap(str)

select initcap('tomcat') from dual;

![在这里插入图片描述][20190909221051817.png]4. 字符串的连接可以使用concat或使用“||”，建议使用"||"  
concat(str1,str2)

select concat('hello',' world') from dual;

![在这里插入图片描述][20190909221310699.png]  
5.字符串的截取，使用substr，第一个参数是源字符串，第二个参数是开始索引，第三个参数长度，开始的索引使用1和0效果相同  
substr(str, start, length)

select substr('hello',1,3) from dual;

![在这里插入图片描述][20190909221722774.png]  
6. 获取字符串的长度  
length(str)

select length('hello') from dual;

![在这里插入图片描述][20190909221859431.png]  
7.字符串替换，第一个参数是源字符串，第二个参数是被替换的字符串，第三个是替换字符串  
replace(str,source, target)

select replace('ababab','a','c') from dual;

![在这里插入图片描述][20190909222126465.png]

1.  字符串填充  
    左填充：lpad 右填充：rpad  
    第一个参数为原字符串，第二个参数为填充到多少位，第三个参数为填充的字符

select rpad('tom',10,'@') from dual;

![在这里插入图片描述][20190910161711314.png]

9.去掉前后指定的字符：trim

select trim('h' from 'hello world') from dual;

![在这里插入图片描述][20190910162051114.png]

#### 数值函数 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 3]

1.四舍五入，第一个参数为原数值，第二个参数为保留位数

select round(123.45678,2) from dual;

![在这里插入图片描述][20190909222416161.png]  
2.截断，第一个参数为原数值，第二个参数为保留位数

select trunc(123.45678,2) from dual;

![在这里插入图片描述][20190909222512986.png]  
3.求余

select mod(1500,400) from dual;

![在这里插入图片描述][20190909222609300.png]

#### 日期函数 ####

*  Oracle中的日期  
    Oracle中的日期型数据实际含有两个值；日期和时间。  
    默认的使其格式是：DD-MON-RR
 *  日期的数学运算  
    在日期上加上或减去一个数字结果仍为日期  
    两个日期相减返回日期之间相差的天数  
    可以用数字除24
 *  日期函数  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 4]
 *  日期的四舍五入  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 5]\-日期函数示例

1.  示例：查询雇员进入公司的周数  
    分析：查询雇员进入公司的天数（sysdate - 入职日期）/ 7 就是周数

select ename,round((sysdate - hiredate)/7) from emp;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 6]  
2.获得两个时间段中的月数：MONTHS\_BETWEEN（）  
示例：查询所有雇员进入公司的月数

select ename,round(months_between(sysdate,hiredate)) from emp;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 7]  
3.获得几个月后的日期：ADD\_MONTHS()：ADD\_MONTHS()  
示例：查询所有雇员进入公司的月数

select add_months(sysdate, 3) from dual;

![在这里插入图片描述][20190909225911302.png]

#### 转换函数 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 8]

*  隐式数据类型转换  
    Oracle自动完成数据类型的转换

<table> 
 <thead> 
  <tr> 
   <th align="center">源数据类型</th> 
   <th align="center">目标数据类型</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">VARCHAR2 or CHAR</td> 
   <td align="center">NUMBER</td> 
  </tr> 
  <tr> 
   <td align="center">VARCHAR2 or CHAR</td> 
   <td align="center">DATE</td> 
  </tr> 
  <tr> 
   <td align="center">NUMBER</td> 
   <td align="center">VARCHAR2</td> 
  </tr> 
  <tr> 
   <td align="center">DATE</td> 
   <td align="center">VARCHAR2</td> 
  </tr> 
 </tbody> 
</table>

*  显示数据类型转换  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 9]
 *  **TO\_CHAR函数对日期的转换**

TO_CHAR(date, 'format_model')

日期的格式：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 10]\- TO\_CHAR 函数对数字的转换

TO_CHAR(number, 'format_madel')

数字转换的格式：  
![在这里插入图片描述][20190909232548198.png]

*  TO\_NUMBER和TO\_DATE函数  
    使用TO\_NUMBER函数将字符串转换成数字

TO_NUMBER(char[, 'format_model'])

使用TO\_DATE函数将字符串转换成日期

TO_DATE(char[,'format_model'])

**1.TO\_CHAR:字符串转换函数**  
示例：查询所有的雇员，将年月日分开，此时可以使用TO\_CHAR函数拆分  
注意：拆分时使用通配符  
年：y，年是四位使用yyyy  
月：m，月是两位使用mm  
日：d，日是两位使用dd

select empno,ename,to_char(hiredate,'yyyy-mm-dd') from emp;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 11]在结果中10以下的月前面被补了前导零，可以使用fm去掉前导零

select empno,ename,to_char(hiredate,'fmyyyy-mm-dd') from emp;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 12]**TO\_CHAR还可以给数字做格式化**  
示例：把雇员的工资按三位用“，”分隔，在oracle中“9”代表一位数字

select ename,to_char(sal,'99,999') from emp;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 13]  
如果在钱的前面加上国家的符号可以使用“$”代表是美元，如果要使用本地的钱的单位使用“L”

select ename, to_char(sal,'L99,999') from emp;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 14]  
**2.TO\_NUMBER:数值转换函数**  
TO\_NUMBER可以把字符串转换数值

select to_number('10')+to_number('10') from dual;

![在这里插入图片描述][20190910091438185.png]**3.TO\_DATE：日期转换函数**  
TO\_DATE可以把字符串的数据转换成日期类型

select to_date('1985-04-22', 'yyyy-mm-dd') from dual;

![在这里插入图片描述][20190910091624992.png]

#### 通用函数 ####

*  什么是通用函数？  
    这些函数适用于任何数据类型，同时也适用于空值
 *  常用的通用函数
    
    1.  NVL(expr1, expr2)
    2.  NVL2(expr1, expr2, expr3)
    3.  NULLIF(expr1, expr2)
    4.  COALESCE(expr1, expr2, …, exprn)

**通用函数示例：**  
1.空值处理nvl  
示例：查询所有的雇员的年薪

select ename, nvl(comm,0) 奖金, sal*12+nvl(comm,0) 年薪 from emp;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 15]

#### 条件表达式 ####

*  什么是条件表达式？  
    在SQL语句中使用IF-THEN\_ELSE
 *  实现的方式：  
    CASE表达式：SQL99的语法，类似Basic，比较繁琐  
    DECODE函数：Oracle自己的语法，类似Java，比较简单

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

**DECODE函数**

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

示例：根据10号部分员工的工资，显示税率

select ename, sal,
    	decode (trunc(sal/2000, 0),
    								0, 0.00,
    								1, 0.09,
    								2, 0.20,
    								3, 0.30,
    								4, 0.40,
    								5, 0.42,
    								6, 0.44,
    								    0.45) TAX_TATE
    from emp
    where deptno = 10;

![在这里插入图片描述][20190910094754501.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/fa6d6c11823442d2a85f3316bcab56e1.png
[20190909220434846.png]: /images/20210813/032eee363a3043e091b61b4f44191beb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]: /images/20210813/4af5cd08e158473f98ed1e6735672678.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]: /images/20210813/fcf48d61ac594f7d8dc4bcdb885b7813.png
[20190909220801532.png]: /images/20210813/b6dfdee754924dc6821071fefe87ac57.png
[20190909220946658.png]: /images/20210813/38996b0cc2a74334bdb8b3e6a45cc467.png
[20190909221051817.png]: /images/20210813/3ca62fab1a5744f889883727a78a0f8a.png
[20190909221310699.png]: /images/20210813/921d2c11f538488c9ab1f07eb41b404b.png
[20190909221722774.png]: /images/20210813/5f300e30cc11402b8cff82c6271e65cb.png
[20190909221859431.png]: /images/20210813/0b182b101ceb4aad83afc3349c9a7b0d.png
[20190909222126465.png]: /images/20210813/d7e3834057924f9199986bfb6174a741.png
[20190910161711314.png]: /images/20210813/7e5b3cfe208c4e46b1b8173f6f203f16.png
[20190910162051114.png]: /images/20210813/e158da8a3848442fb346234659492a13.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 3]: /images/20210813/579ff440becf4b18b4fdf706033cea87.png
[20190909222416161.png]: /images/20210813/bcf094d3d9164d8789bf0c0025ee9814.png
[20190909222512986.png]: /images/20210813/961d304722e64d279a650066dc82de35.png
[20190909222609300.png]: /images/20210813/c52ccb641f304921aa80cbc0bfed65ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 4]: /images/20210813/70e7f9ec0ed14eac9b3fe0fa15421b3a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 5]: /images/20210813/4abcf02de4ee4afc9d29b807b7e10660.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 6]: /images/20210813/48952eb58a3d4000aec38de1fa3235e7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 7]: /images/20210813/9ccfc485a50a44e89066d5c69829fcda.png
[20190909225911302.png]: /images/20210813/6eeb9c19ba0b4af3b9fe9fd564dc117f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 8]: /images/20210813/50e21cc5ede44eb7bb220d4d15dc221c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 9]: /images/20210813/c1bdb5e2ab294b4eb8a7c6a668aa765f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 10]: /images/20210813/acdc071b9ad14e2fbe4e5a0745495c1a.png
[20190909232548198.png]: /images/20210813/e92d7452e1fb411e8f0a6fac88de6dda.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 11]: /images/20210813/5c34985ed53b49c3adad5e9863e2c5b3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 12]: /images/20210813/38441c88955e4e739c596214ee4fe64e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 13]: /images/20210813/b5765a9279a74c0c9dd74870e3d3cab1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 14]: /images/20210813/efe1495bbf8147b2ba1450948b33e666.png
[20190910091438185.png]: /images/20210813/0df12f4aa6274f5caf3c1112e6b7807d.png
[20190910091624992.png]: /images/20210813/d5d2a3bf5c174bd3a2b04b6f64e090b2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 15]: /images/20210813/6bbbfe5fccb743b6acf6e882801a655b.png
[20190910094754501.png]: /images/20210813/f73ef590703c404083b469b0f234862c.png