oracle 常用函数的应用

在使用开发中会经常遇到数据类型转换、显示系统时间等情况，需要使用函数来实现。通过函数来实现业务需求会非常的省事便捷，函数可以用在适当的dml语句和查询语句中。
Oracle 数据库中主要使用两种类型的函数：
(1)单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果， 比如：MOD(x，y)返回 x 除以 y 的余数(x 和 y 可以是两个整数，也可以是表中的整 数列)。常用的单行函数有：
字符函数：对字符串操作。
数字函数：对数字进行计算，返回一个数字。
转换函数：可以将一种数据类型转换为另外一种数据类型。
日期函数：对日期和时间进行处理。
(2)聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。
本章介绍一些Oracle系统自带的常用转换函数、日期函数、分组函数。
6.1常用函数
6.1.1转换字符函数to_char()
To_char()函数是将数值型或日期型转化为字符型。
具体使用格式如下：
to_char (input_value，format_code)
input_value：需要转换的字段。
format_code：转换后的格式。
【例6-1】建立产品信息表，包括产品名称、产品单价、库存数、最后进货日期、
将最后一次进货时间用完整时间方式显示。把产品单价转换成字符型。
建表代码如下：
–第六章
–建立产品信息表
CREATE TABLE plsql101_product (
product_name varchar2(25)，
product_price NUMBER(4，2)，
quantity_on_hand NUMBER(5，0)，
last_stock_date DATE
)
–插入数据
INSERT INTO PLSQL101_PRODUCT
select ‘Small Widget’，500，30 ，to_date(‘20220902’，‘yyyymmdd’) from dual;
INSERT INTO PLSQL101_PRODUCT
select ‘Medium Wodge’，800，600 ，to_date(‘20220806’，‘yyyymmdd’)from dual;
INSERT INTO PLSQL101_PRODUCT
select ‘Chrome Phooba’，650，120 ，to_date(‘20220716’，‘yyyymmdd’)from dual;
Commit;

将最后一次进货时间用完整时间方式显示，代码如下：
select PRODUCT_NAME，
PRODUCT_PRICE，
QUANTITY_ON_HAND，LAST_STOCK_DATE，
to_char(LAST_STOCK_DATE， ‘yyyy-mm-dd，HH24:MI’)
from PLSQL101_PRODUCT;
查询结果如图6-1所示。

图6-1查询产品信息表数据
把产品单价转换成字符型，代码如下：
SELECT product_name，
TO_CHAR(product_price， ‘$9，999.00’) “Price”，
quantity_on_hand，
last_stock_date
FROM plsql101_product;
查询结果如图6-2所示。

图6-2查询产品信息表数据
6.1.2 转换日期函数to_date()
to_date函数格式如下：
TO_DATE(input_value，format_code)
input_value：需要转换的字段。
format_code：转换后的格式。
【例6-2】把产品名称为Small Widget的最后一次进货时间更新为2022年9月10日下午4点半。
更新产品信息表，代码如下：
–第六章\hs.sql

UPDATE plsql101_product
SET last_stock_date = TO_DATE(‘2022/09/10 16:30:30’ ，‘yyyy/mm/dd hh24:mi:ss’)
WHERE product_name=‘Small Widget’;
Commit;
–查询数据
Select * from plsql101_product;

查询结果如图6-3所示。

图6-3查询更新后产品信息表数据
6.1.3decode()函数
Decode函数的格式如下：
DECODE(条件，值1，翻译值1，值2，翻译值2，…值n，翻译值n，缺省值)
该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　…
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

【例6-3】建立雇员部门信息表，包括雇员编号、部门编号、部门名称 。把部门编号为10的记录部门名称更新成为财务。部门编号为20的记录更新部门为公关，部门编号为空的更新成未知。
建表插入测试数据，代码如下：
–第六章\hs.sql

create table sm_emp_dept
(id char(10)，
deptno char(2)，
dept varchar2(20));

insert into sm_emp_dept(id，deptno) values(‘001’，‘10’);
insert into sm_emp_dept(id，deptno) values(‘002’，‘20’);
insert into sm_emp_dept(id，deptno) values(‘003’，‘10’);
insert into sm_emp_dept(id，deptno) values(‘007’，null);
insert into sm_emp_dept(id，deptno) values(‘008’，‘80’);
Commit;
先查询数据，代码如下：
select * from sm_emp_dept t
执行后如图6-4所示。

图6-4查询部门信息表表数据

更新数据，代码如下：
update sm_emp_dept set dept= decode
(deptno ， 10 ， ‘财务’，
20 ， ‘公关’，
‘未知’);
Commit;

select * from sm_emp_dept

执行查询，结果如图6-5所示。

图6-5查询更新后部门信息表数据

6.1.4 nvl()函数
nvl函数可以实现空值的转换，基本格式如下：
NVL(输入值，输出值)
输入值为NULL，则返回输出值，否则仍为输入值。输出值必须与输入值类型相同。