PL/SQL模块学习之三、单行函数与空值处理

1.连接元素符的使用

SQL> select ename || 'is a ' || job || ' and 1month salary is :'|| sal as "The imployees's information" from emp  
#这里使用“;”做结尾报错，ORA-00911: 无效字符
2  /
The imployees's information
--------------------------------------------------------------------------------
SMITHis a CLERK and 1month salary is :800
ALLENis a SALESMAN and 1month salary is :1600
WARDis a SALESMAN and 1month salary is :1250
JONESis a MANAGER and 1month salary is :2975

2.Oracle书写规范

• SQL语句关键字大写，对象名小写
• 缩进对齐
• 每个字句一行

3.字符型单行函数

3.1 LOWER(column|expression)

将字符串转为小写

3.2 UPPER(column|expression)

将字符串转为大写

3.3 INITCAP(column|expression)

将字符串首字母大写

3.4 CONCAT(column1|expression1,column2|expression2)

连接两个字符串

SQL> select ename,hiredate,concat(ename,hiredate) "emp_name,hiredate"
  2  from emp
  3  /
ENAME      HIREDATE    emp_name,hiredate
---------- ----------- ------------------------
SMITH      1980/12/17  SMITH17-12月-80
ALLEN      1981/2/20   ALLEN20-2月 -81
WARD       1981/2/22   WARD22-2月 -81
JONES      1981/4/2    JONES02-4月 -81
MARTIN     1981/9/28   MARTIN28-9月 -81
BLAKE      1981/5/1    BLAKE01-5月 -81

3.5 SUBSTR(column|expression,m,[,n])

获取部分字段（空格占一个字符）

SQL> SELECT hiredate,SUBSTR(hiredate,1,5)
  2  FROM emp
  3  /
HIREDATE    SUBSTR(HIREDATE,1,5)
----------- --------------------
1980/12/17  17-12
1981/2/20   20-2月
1981/2/22   22-2月

3.6 LENGTH(column|expression)

获取字符个数

3.7 INSTR(column|expression,‘string’,[,m],[,n])

获取’string’在字符串中所在位置，m,n指定搜索位置

3.8 LPAD|RPAD(column|expression,n,‘string’)

在输出结果补充信息

SQL> SELECT sal,LPAD(sal,10,'*') #LPAD 左补充
  2  FROM emp
  3  WHERE sal > 1500
  4  /
      SAL LPAD(SAL,10,'*')
--------- --------------------
  1600.00 ******1600
  2975.00 ******2975
SQL> SELECT sal,RPAD(sal,10,'*')#RPAD右补充
  2  FROM emp
  3  WHERE sal > 1500
  4  /
      SAL RPAD(SAL,10,'*')
--------- --------------------
  1600.00 1600******
  2975.00 2975******

3.9 TRIM(leading|trailing|both,trim_character FROM Trim_source)

leading|trailing|both:从头部、尾部还是两边剪切字符

SQL> SELECT job,trim('M' FROM job)
2  FROM emp;
JOB       TRIM('M'FROMJOB)
--------- ----------------
SALESMAN  SALESMAN
MANAGER   ANAGER

3.10 REPLACE(text,search_string,replacement_string)

将字符串中的某个字符串替换为另一个

SQL> select replace('my job is IT','IT','internet')
  2  FROM dual;
REPLACE('MYJOBISIT','IT','INTE
------------------------------
my job is internet

4.数字型单行函数

4.1ROUND(column/expression,n)

• n为保留小数位，-1表示保留整数位
• 四舍五入

4.2 TRUNC(column/expression,n)

• n为截断后的小数位
• 不使用四舍五入

4.3 MOD(m,n)

• 求余数

5.日期型单行函数

• 使用内部数字格式存储日期
• 默认显示和输入格式 DD-MON-RR
• 内部存储格式 世纪、年、月、日、时、分、秒
• 外部格式不会影响内部存储格式

5.1 SYSDATE

返回系统当前日期

SQL> SELECT TO_DATE('2019-03-04','yyyy-mm-dd') - SYSDATE #注意to_date的用法
  2  FROM dual;
TO_DATE('2019-03-04','YYYY-MM-
------------------------------
             -366.661516203704

5.2 MONTHS_BETWEEN

计算两个日期之间的月数

5.3 ADD_MONTHS(date,n)

添加N个月到日期数据上

5.4 NEXT_DAY

示例：输出今天到开始，下个周五的日期

SQL> select next_day(sysdate,'Friday')
 2  from dual;
NEXT_DAY(SYSDATE,'FRIDAY')
--------------------------
2020/3/6 16:02:30

5.5 LAST_DAY

返回日期中最后一天的日期

6.空值

• 用comm IS NULL和IS NOT NULL判断是否为空值，而不是=
• 使用NVL空值转换函数后，空值才可进行相关运算

6.1 NVL(expr1,expr2)

• 空值转换函数
• 如果expr1为空，返回expr2
• expr1和expr2数据类型必须一致（数值型，字符型，日期型）

6.2 NVL2(expr1,expr2,expr3)

• 功能同上
• expr2,expr3不能为long数据类型

6.3 NULLIF(expr1,expr2)

• 如果两者相等，则返回NULL,不等则返回第一个值

6.4 COALESCE(expr1,expr2,…exprn)

• 返回第一个不为空值的表达式的值