ORACLE 第3节 单行函数

学习目标

1.使用SQL中不同类型的函数

2.在SELECT 语句中使用字符，数字，日期和转换函数

3.使用条件表达式

两种SQL函数

单行函数:

•操作数据对象

•接受参数返回一个结果

•只对一行进行变换

•每行返回一个结果

•可以转换数据类型

•可以嵌套

•参数可以是一列或一个值

function_name [(arg1,arg2,...)]

字符型函数：

        大小写控制函数

大小写控制函数：

这类函数改变字符的大小写。

LOWER      

<span style="color:#333333;"> select lower(last_name) from employees;</span>

UPPER

INITCAP

CONCAT('Hello', 'World')    HelloWorld

SUBSTR('HelloWorld',2,5) elloW

LENGTH('HelloWorld') 10

INSTR('HelloWorld', 'W')6

LPAD(salary,10,'*')*****24000 不足10位往左边补*号

RPAD(salary, 10, '*')24000*****

将一个字符从一个字符串中移除，仅能去除首尾的字符，中间的不关心。首尾的如果是多个连续h，会全部清除

TRIM('H' FROM 'HelloWorld')elloWorld

select trim('wor' from 'helloworld') from dual;

ORA-30001: 截取集仅能有一个字符

将所有的b都替换为m

REPLACE(‘abcd’,’b’,’m’)amcd

•ROUND: 四舍五入

•TRUNC:  截断

•MOD: 求余

日期的数学运算：

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

显式数据类型转换：

SELECT TO_CHAR(sysdate,‘yyyy-mm-ddhh:mi:ss’) FROM dual;

TO_CHAR(date, 'format_model')

•使用 TO_DATE 函数将字符转换成数字:

TO_DATE(char[, 'format_model'])

TO_DATE(‘2012年10月29日 08:10:21’,’yyyy“年”mm”月”dd“日”hh:mi:ss’)
From dual

</pre></p><p><span style="color:#333333"></span></p><div>•使用<span style="color:red">TO_NUMBER</span>函数将字符转换成日期:</div><p></p><p style="line-height:160%; margin-top:0pt; margin-bottom:0pt; direction:ltr; unicode-bidi:embed; vertical-align:baseline"><span style="font-family:'Courier New'"><strong>TO_NUMBER(</strong></span><span style="font-family:'Courier New'"><strong><em>char</em></strong></span><span style="font-family:'Courier New'"><strong>[</strong></span><span style="font-family:'Courier New'"><strong><em>,</em></strong></span><span style="font-family:'Courier New'"><strong>'</strong></span><span style="font-family:'Courier New'"><strong><em>format_model</em></strong></span><span style="font-family:'Courier New'"><strong>'])</strong></span></p><p style="line-height:160%; margin-top:0pt; margin-bottom:0pt; direction:ltr; unicode-bidi:embed; vertical-align:baseline"><pre name="code" class="sql">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)

将空值转换成一个已知的值：（如果commission_pct为空是，commission_pct为原来的值，为空时，commission_pct为expr2）

–NVL(commission_pct,0)

NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。

SELECT last_name, salary, NVL(commission_pct, 0),
  (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

条件表达式：

•在 SQL语句中使用IF-THEN-ELSE逻辑

•使用两种方法:

–CASE 表达式

DECODE函数

在需要使用IF-THEN-ELSE逻辑时:

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,case department_id when 10 then salary*1.1
                                                              when 20 then salary*1.2
                                                              when 30 then salary*1.3
                                                              end "new salary"
from employees
where department_id in (10,20,30)

DECODE 函数

在需要使用IF-THEN-ELSE逻辑时:

DECODE(col|expression, search1, result1 ,

           [, search2, result2,...,]

           [, default])

--练习：查询部门号为 10, 20, 30 的员工信息, 
--若部门号为 10, 则打印其工资的 1.1 倍, 
--20 号部门, 则打印其工资的 1.2 倍, 
--30 号部门打印其工资的 1.3 倍数

select last_name,department_id,salary,decode(department_id,10,salary*1.1,
                                                           20,salary*1.2,
                                                            salary*1.3
                                            ) as "new salary"
from employees
where department_id in (10,20,30)