ORACLE学习笔记-添加更新数据函数篇
在ORACLE数据库的日常操作中，数据的添加与更新是最基础也最常用的操作场景。除了基础的INSERT、UPDATE语句外，ORACLE内置的各类函数能大幅提升数据操作的效率与准确性，本文将对添加、更新数据时常用的函数进行梳理与说明。
一、添加数据时常用函数
执行INSERT语句添加数据时，合理使用函数可以简化字段赋值逻辑，避免手动计算带来的错误。
1. 序列函数：NEXTVAL与CURRVAL
ORACLE中序列（SEQUENCE）是用于生成唯一数值的数据库对象，常用来为表的主键字段赋值，对应的两个核心函数为NEXTVAL和CURRVAL：
NEXTVAL：返回序列的下一个值，每次调用都会让序列的当前值递增CURRVAL：返回序列的当前值，调用前必须先调用过一次NEXTVAL，否则会报错
以下示例演示使用序列函数为用户表添加数据：
-- 先创建序列 CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; -- 创建用户表 CREATE TABLE t_user ( user_id NUMBER(10) PRIMARY KEY, user_name VARCHAR2(50), create_time DATE ); -- 添加用户数据，使用NEXTVAL生成主键，SYSDATE获取当前时间 INSERT INTO t_user (user_id, user_name, create_time) VALUES (user_seq.NEXTVAL, '张三', SYSDATE);
2. 日期与时间函数
添加数据涉及时间字段时，ORACLE提供了多个便捷函数，避免手动拼接时间字符串的错误：
SYSDATE：返回当前数据库服务器的日期和时间，包含年、月、日、时、分、秒SYSTIMESTAMP：返回当前数据库服务器的时间戳，精度更高，包含时区信息TO_DATE(字符串, 格式掩码)：将符合格式的字符串转换为DATE类型
示例代码如下：
-- 使用SYSTIMESTAMP添加带时区的时间
INSERT INTO t_user (user_id, user_name, create_time)
VALUES (user_seq.NEXTVAL, '李四', SYSTIMESTAMP);
-- 使用TO_DATE转换指定格式的字符串为时间
INSERT INTO t_user (user_id, user_name, create_time)
VALUES (user_seq.NEXTVAL, '王五', TO_DATE('2024-05-20 14:30:00', 'YYYY-MM-DD HH24:MI:SS'));3. 空值处理函数：NVL与NVL2
添加数据时如果字段允许为空，或者需要给空值设置默认值，可以使用空值处理函数：
NVL(字段, 默认值)：如果字段值为NULL，返回默认值，否则返回字段本身的值NVL2(字段, 值1, 值2)：如果字段值不为NULL，返回值1，否则返回值2
示例：
-- 添加用户时，如果用户名为空则默认赋值为'未命名' INSERT INTO t_user (user_id, user_name, create_time) VALUES (user_seq.NEXTVAL, NVL(NULL, '未命名'), SYSDATE); -- 添加用户时，根据备注是否为空赋值不同的状态 INSERT INTO t_user (user_id, user_name, create_time) VALUES (user_seq.NEXTVAL, NVL2(NULL, '有备注', '无备注'), SYSDATE);
二、更新数据时常用函数
执行UPDATE语句更新数据时，函数可以帮助我们批量处理字段值，减少重复操作。
1. 字符串处理函数
更新字符类型字段时，常用的字符串函数可以快速完成格式调整：
UPPER(字符串)：将字符串转换为全大写LOWER(字符串)：将字符串转换为全小写TRIM(字符串)：去除字符串首尾的空格SUBSTR(字符串, 起始位置, 长度)：截取字符串的指定部分
示例：将用户表中所有用户名转换为大写，同时去除首尾空格：
UPDATE t_user SET user_name = UPPER(TRIM(user_name)) WHERE user_name IS NOT NULL;
2. 数值计算函数
更新数值类型字段时，数值函数可以完成各类计算需求：
ROUND(数值, 小数位数)：对数值进行四舍五入，指定保留的小数位数TRUNC(数值, 小数位数)：对数值进行截断，不执行四舍五入MOD(数值1, 数值2)：返回数值1除以数值2的余数
假设有员工工资表t_salary，结构如下：
CREATE TABLE t_salary ( emp_id NUMBER(10) PRIMARY KEY, salary NUMBER(10,2), bonus NUMBER(10,2) );
示例：将员工工资四舍五入到整数，同时计算奖金为工资的10%并截断到两位小数：
UPDATE t_salary SET salary = ROUND(salary, 0), bonus = TRUNC(salary * 0.1, 2);
3. 条件判断函数：DECODE与CASE
更新数据需要根据不同条件设置不同字段值时，可以使用条件判断函数，避免写多个UPDATE语句：
DECODE(字段, 值1, 结果1, 值2, 结果2, ..., 默认值)是ORACLE特有的条件判断函数，逻辑类似IF-ELSE：
-- 根据用户名的首字母更新用户类型：首字母为Z的设为VIP，否则设为普通用户 UPDATE t_user SET user_type = DECODE(SUBSTR(user_name, 1, 1), 'Z', 'VIP', '普通用户');
标准SQL的CASE表达式兼容性更好，逻辑更清晰：
UPDATE t_salary SET bonus = CASE WHEN salary < 5000 THEN salary * 0.1 WHEN salary BETWEEN 5000 AND 10000 THEN salary * 0.15 ELSE salary * 0.2 END;
三、注意事项
使用序列的
CURRVAL前，必须保证当前会话已经调用过一次该序列的NEXTVAL，否则会抛出ORA-08002错误更新数据前建议先使用SELECT语句结合对应函数查询要更新的数据，确认结果符合预期后再执行UPDATE，避免误操作
WHERE条件中如果函数使用不当，可能导致索引失效，全表扫描，影响更新效率，例如
WHERE UPPER(user_name) = 'ZHANGSAN'会导致user_name字段上的索引无法使用，需要根据实际情况优化
以上就是在ORACLE中添加、更新数据时常用的函数说明，合理运用这些函数可以让数据操作更高效、更规范。