SQL-修改: 将日期修改为空NULL、修改为空的记录

本文介绍了如何使用SQL语句将特定字段的空值或者空白值更新为NULL或预设值的方法,包括避免默认日期的问题及如何针对NULL值进行操作。
部署运行你感兴趣的模型镜像

1、将日期修改为空NULL

  update 表 set 字段=null where 字段=''

  如果设置为‘’,会默认1900-01-01

 

2、修改为空的记录

  update [dbo].[pub_item_contents] set [range]='0' where [range] is null

转载于:https://www.cnblogs.com/michellexiaoqi/p/8296472.html

您可能感兴趣的与本文相关的镜像

AutoGPT

AutoGPT

AI应用

AutoGPT于2023年3月30日由游戏公司Significant Gravitas Ltd.的创始人Toran Bruce Richards发布,AutoGPT是一个AI agent(智能体),也是开源的应用程序,结合了GPT-4和GPT-3.5技术,给定自然语言的目标,它将尝试通过将其分解成子任务,并在自动循环中使用互联网和其他工具来实现这一目标

<think>我们正在处理Oracle数据库中的日期字段,特别是12个付息日期字段(如interest_date1到interest_date12),这些字段存储的是字符串类型,格式为'MM-DD'(例如'02-29')。在计算最近付息日时,我们需要将这些字符串与年份组合成完整日期,但可能会遇到闰年问题(例如2023年不是闰年,但'02-29'是无效日期),导致ORA-01839错误。 解决方案的核心思路:在组合日期时,避免生成无效日期(如非闰年的2月29日)。我们可以采取以下策略: 1. 尝试使用当前年份和上一年份组合日期,但如果遇到无效日期,则跳过该日期。 2. 使用Oracle的异常处理机制(如PL/SQL)或利用SQL函数(如VALIDATE_CONVERSION)来过滤无效日期。 然而,由于我们是在SQL语句中处理,且需要高效,我们可以采用以下方法: 方法一:使用TO_DATE函数并捕获异常(但SQL中无法直接捕获异常,因此我们采用另一种方法) 方法二:在构造日期时,对于2月29日,我们尝试将其转换为3月1日(或者2月28日)?但用户提到可以接受转换为3月1日。 但是,根据引用[2]中的讨论,用户提到可以将2月29日转换为3月1日。因此,我们可以设计一个函数或直接在SQL中处理: 步骤: 1. 将每个付息日期字段('MM-DD')与年份组合,尝试生成日期。 2. 如果组合后的日期无效(如2月29日在非闰年),则将其转换为3月1日(或者2月28日,根据业务需求)。 但是,注意:我们并不想改变原始数据,只是在计算最近付息日时做临时转换。 具体实现: 我们可以使用CASE语句来检查是否是2月29日,并且当前年份是否是闰年。如果不是闰年,则将其转换为3月1日(或2月28日)。但这样逻辑较复杂。 另一种方法是使用TO_DATE函数并忽略错误,但SQL中无法忽略错误。因此,我们可以使用Oracle 12c及以上版本的新函数VALIDATE_CONVERSION来检查日期是否有效。 在Oracle 12c R2及以上版本,我们可以使用VALIDATE_CONVERSION函数来验证字符串是否可以转换为日期。如果无效,我们可以跳过或者转换。 但是,由于我们可能使用的是Oracle 11g(根据引用[2]),所以不能使用VALIDATE_CONVERSION(该函数在12c R2引入)。因此,我们需要其他方法。 我们可以自己一个函数来检查闰年,然后处理2月29日。 闰年规则:年份能被4整除但不能被100整除,或者能被400整除。 因此,我们可以这样处理: 对于每个日期字符串('MM-DD')和年份(比如当前年份或上一年): - 如果日期是'02-29',并且年份不是闰年,则将其转换为'03-01'(或者'02-28',根据业务需求)。 - 否则,直接使用原日期。 然后组合成完整日期。 但是,注意:我们并不需要修改原始数据,只是在构造日期时临时转换。 因此,在构造日期的表达式中,我们可以这样: 例如,对于给定的年份y和月份日期字符串s(格式'MM-DD'),构造日期: CASE WHEN s = '02-29' AND NOT ( (MOD(y, 4) = 0 AND MOD(y, 100) != 0) OR MOD(y, 400) = 0 ) THEN TO_DATE(y || '-03-01', 'YYYY-MM-DD') -- 或者转换为'02-28' ELSE TO_DATE(y || '-' || s, 'YYYY-MM-DD') END 但是,注意:我们可能不希望将2月29日都转换为3月1日,因为这样会改变付息日。业务上可能更希望转换为2月28日?或者保持2月29日(只在闰年有效)?但问题在于非闰年这个日期不存在,所以必须转换。 根据引用[2]中用户的说法,他们可以接受转换为3月1日,所以我们采用转换为3月1日。 但是,注意:我们的年份可能是当前年或上一年,我们需要分别处理。 因此,修改之前的SQL,在构造pay_date的地方,使用上述逻辑。 原SQL中构造pay_date的表达式为: TO_DATE(EXTRACT(YEAR FROM SYSDATE) + lvl - 1 || '-' || interest_date, 'YYYY-MM-DD') 现在我们需要修改这个表达式,加入闰年判断和转换。 但是,这个表达式会很长,而且每个付息日期都要这样处理,可能会影响性能。而且,我们还需要注意,这个转换只针对2月29日,其他日期正常。 因此,我们可以这样改: 将原来直接拼接的TO_DATE改为一个CASE表达式,判断是否是2月29日且年份不是闰年。 但是,注意:我们无法在TO_DATE之前判断,因为TO_DATE需要整个字符串。所以我们可以先尝试转换,如果转换失败则使用替代日期?但SQL中无法捕获异常。 所以只能通过条件判断。 步骤: 1. 提取月份和日:我们可以从interest_date中提取月份和日,但interest_date是'MM-DD'格式,所以可以直接用字符串比较。 2. 判断年份是否是闰年:我们可以一个函数,但为了在SQL中直接使用,我们使用内联条件。 因此,我们修改pay_date的生成逻辑: 假设我们有一个年份y = EXTRACT(YEAR FROM SYSDATE) + lvl - 1,和字符串d = interest_date。 那么: pay_date = CASE WHEN d = '02-29' AND NOT ( (MOD(y, 4) = 0 AND MOD(y, 100) <> 0) OR MOD(y, 400) = 0 ) THEN TO_DATE(y || '-03-01', 'YYYY-MM-DD') ELSE TO_DATE(y || '-' || d, 'YYYY-MM-DD') END 但是,注意:如果d不是'02-29',那么直接拼接转换,但如果d是其他无效日期(比如'02-30')怎么办?我们假设数据中只有'02-29'在非闰年会出现问题,其他日期都是有效的(如每个月的日期都在合理范围内)。如果数据中还有其他无效日期,那么同样会报错。但根据问题,我们只关心闰年问题。 因此,我们只处理2月29日的情况。 修改后的SQL如下(只修改了pay_date的生成部分): ```sql MERGE INTO bond_info b USING ( WITH dates AS ( SELECT bond_code, -- 构造日期,处理非闰年的2月29日 CASE WHEN interest_date = '02-29' AND NOT ( (MOD(EXTRACT(YEAR FROM SYSDATE) + lvl - 1, 4) = 0 AND MOD(EXTRACT(YEAR FROM SYSDATE) + lvl - 1, 100) <> 0 ) OR MOD(EXTRACT(YEAR FROM SYSDATE) + lvl - 1, 400) = 0 ) THEN TO_DATE(TO_CHAR(EXTRACT(YEAR FROM SYSDATE) + lvl - 1) || '-03-01', 'YYYY-MM-DD') ELSE TO_DATE(TO_CHAR(EXTRACT(YEAR FROM SYSDATE) + lvl - 1) || '-' || interest_date, 'YYYY-MM-DD') END AS pay_date FROM ( SELECT bond_code, interest_date FROM bond_info UNPIVOT ( interest_date FOR seq IN ( interest_date1, interest_date2, interest_date3, interest_date4, interest_date5, interest_date6, interest_date7, interest_date8, interest_date9, interest_date10, interest_date11, interest_date12 ) ) ) unpiv CROSS JOIN ( SELECT LEVEL AS lvl FROM DUAL CONNECT BY LEVEL <= 2 -- 生成当前年和上一年 ) WHERE interest_date IS NOT NULL ) SELECT bond_code, MAX(pay_date) AS last_interest_date FROM dates WHERE pay_date <= TRUNC(SYSDATE) -- 筛选历史付息日 GROUP BY bond_code ) src ON (b.bond_code = src.bond_code) WHEN MATCHED THEN UPDATE SET b.last_interest_date = src.last_interest_date; ``` 注意:在CASE表达式中,我们使用了多个MOD函数来判断闰年,注意括号的匹配。 但是,这个表达式非常长,而且每行都要计算闰年,可能影响性能。我们可以考虑将闰年判断成一个子查询因子,但这里只有两个年份(当前年和上一年),所以我们可以预先计算这两个年份是否是闰年,然后与原始数据连接。 优化:先计算两个年份的闰年标志,然后再拼接日期修改如下: ```sql MERGE INTO bond_info b USING ( WITH year_data AS ( -- 生成两个年份及其闰年标志 SELECT EXTRACT(YEAR FROM SYSDATE) + LEVEL - 2 AS year_val, -- 上一年和当前年?注意:我们这里需要上一年和当前年,所以LEVEL从1到2,对应上一年和当前年 CASE WHEN (MOD(EXTRACT(YEAR FROM SYSDATE) + LEVEL - 2, 4) = 0 AND MOD(EXTRACT(YEAR FROM SYSDATE) + LEVEL - 2, 100) <> 0) OR MOD(EXTRACT(YEAR FROM SYSDATE) + LEVEL - 2, 400) = 0 THEN 1 ELSE 0 END AS is_leap FROM DUAL CONNECT BY LEVEL <= 2 ), dates AS ( SELECT bond_code, -- 根据闰年标志和日期字符串构造日期 CASE WHEN interest_date = '02-29' AND is_leap = 0 THEN TO_DATE(year_val || '-03-01', 'YYYY-MM-DD') ELSE TO_DATE(year_val || '-' || interest_date, 'YYYY-MM-DD') END AS pay_date FROM ( SELECT bond_code, interest_date FROM bond_info UNPIVOT ( interest_date FOR seq IN ( interest_date1, interest_date2, interest_date3, interest_date4, interest_date5, interest_date6, interest_date7, interest_date8, interest_date9, interest_date10, interest_date11, interest_date12 ) ) ) unpiv CROSS JOIN year_data -- 连接年份数据 WHERE interest_date IS NOT NULL ) SELECT bond_code, MAX(pay_date) AS last_interest_date FROM dates WHERE pay_date <= TRUNC(SYSDATE) -- 筛选历史付息日 GROUP BY bond_code ) src ON (b.bond_code = src.bond_code) WHEN MATCHED THEN UPDATE SET b.last_interest_date = src.last_interest_date; ``` 注意:在year_data中,我们生成了两个年份:当前年和上一年。我们使用LEVEL从1到2,但注意: - 当LEVEL=1时,年份为:EXTRACT(YEAR FROM SYSDATE) + 1 - 2 = 今年-1(上一年) - 当LEVEL=2时,年份为:EXTRACT(YEAR FROM SYSDATE) + 2 - 2 = 今年 这样,我们预先计算了这两个年份是否是闰年(is_leap标志),然后在dates中,根据这个标志和日期字符串来构造日期。 这样避免了重复计算闰年,且逻辑更清晰。 另外,注意:如果付息日期字段中还有其他无效日期(如'02-30'),上述方法仍然会报错。如果数据中存在其他无效日期,我们需要更健壮的方法,比如使用PL/SQL函数并异常处理,或者使用更复杂的校验。但根据问题,我们主要解决非闰年的2月29日问题。 如果还有其他无效日期,建议在数据入库时进行校验,或者使用PL/SQL函数来转换: 例如,创建一个函数: ```sql CREATE OR REPLACE FUNCTION safe_to_date(p_year NUMBER, p_mmdd VARCHAR2) RETURN DATE IS v_date DATE; BEGIN BEGIN v_date := TO_DATE(p_year || '-' || p_mmdd, 'YYYY-MM-DD'); EXCEPTION WHEN OTHERS THEN -- 如果是2月29日且错误是ORA-01839,则转换为3月1日 IF p_mmdd = '02-29' THEN v_date := TO_DATE(p_year || '-03-01', 'YYYY-MM-DD'); ELSE -- 其他无效日期,可以返回NULL或者根据业务处理 RAISE; -- 或者返回NULL,这里我们选择抛出异常,因为其他无效日期需要处理 END IF; END; RETURN v_date; END; / ``` 然后在SQL中使用这个函数: safe_to_date(year_val, interest_date) AS pay_date 但是,在SQL中使用自定义函数可能会影响性能,特别是数据量大的时候。 因此,我们根据实际情况选择。如果只有2月29日问题,使用CASE方法即可。 总结:我们通过预先计算年份的闰年标志,然后对2月29日且非闰年的情况转换为3月1日,避免了ORA-01839错误。 注意:如果业务上要求转换为2月28日,只需将上面的'03-01'改为'02-28'即可。 另外,在引用[4]中提到,他们通过分批执行来定位错误日期。如果我们的表很大,并且有很多无效日期,我们可以先定位无效日期记录,单独处理。但我们的方法已经处理了最常见的2月29日问题。 最后,我们建议在数据录入时进行校验,确保付息日期是有效的(对于2月29日,必须对应闰年,否则不允许录入),这样可以避免后续计算的问题。 相关问题: 1. 如何批量校验Oracle表中日期字段的有效性? 2. 在Oracle中,如何优化自定义函数在SQL查询中的性能? 3. 除了闰年问题,还有哪些常见的日期处理陷阱? 4. 如何设计债券付息日期的存储结构以避免此类问题?</think>### 解决 Oracle ORA-01839: "指定月份的日期无效" 错误 #### 错误原因分析 ORA-01839 错误通常发生在以下场景: 1. **闰年日期处理**:尝试将非闰年的日期设为2月29日(如2023-02-29)[^2][^4] 2. **月份天数不匹配**:如4月31日、11月31日等无效日期 3. **日期格式转换错误**:字符串到日期的隐式转换失败 4. **跨年日期计算**:在12月/1月边界处理不当 #### 解决方案(针对付息日期场景) ##### 方法1:安全日期转换函数 ```sql CREATE OR REPLACE FUNCTION safe_to_date( p_year NUMBER, p_mmdd VARCHAR2 ) RETURN DATE IS BEGIN RETURN CASE WHEN p_mmdd = '02-29' AND MOD(p_year, 4) != 0 THEN -- 非闰年处理 TO_DATE(p_year || '-03-01', 'YYYY-MM-DD') -- 转换为3月1日 ELSE TO_DATE(p_year || '-' || p_mmdd, 'YYYY-MM-DD') END; EXCEPTION WHEN OTHERS THEN -- 捕获其他无效日期 RETURN NULL; END; / ``` ##### 方法2:在SQL中直接处理闰年(无需创建函数) ```sql SELECT bond_code, MAX(CASE WHEN interest_date = '02-29' AND NOT((MOD(EXTRACT(YEAR FROM SYSDATE) + lvl - 1, 4) = 0 AND MOD(EXTRACT(YEAR FROM SYSDATE) + lvl - 1, 100) != 0) OR MOD(EXTRACT(YEAR FROM SYSDATE) + lvl - 1, 400) = 0) THEN TO_DATE(TO_CHAR(EXTRACT(YEAR FROM SYSDATE) + lvl - 1) || '-03-01', 'YYYY-MM-DD') ELSE TO_DATE(TO_CHAR(EXTRACT(YEAR FROM SYSDATE) + lvl - 1) || '-' || interest_date, 'YYYY-MM-DD') END) AS last_interest_date FROM bond_info UNPIVOT (interest_date FOR seq IN (interest_date1, interest_date2, ..., interest_date12)) CROSS JOIN (SELECT LEVEL AS lvl FROM DUAL CONNECT BY LEVEL <= 2) WHERE interest_date IS NOT NULL GROUP BY bond_code; ``` #### 最佳实践建议 1. **数据校验前置** 在数据入库时验证日期有效性: ```sql ALTER TABLE bond_info ADD CONSTRAINT chk_date_valid CHECK ( REGEXP_LIKE(interest_date1, '^(0[1-9]|1[0-2])-(0[1-9]|1[0-9]|2[0-8])$') OR (interest_date1 = '02-29' AND ...) -- 特殊处理闰年 ); ``` 2. **日期字段设计优化** - 将`VARCHAR2`类型的`MM-DD`改为`DATE`类型完整日期 - 增加`is_leap_year_adjusted`标志位记录调整状态 3. **批量处理无效日期** 定位问题数据: ```sql -- 查找所有包含2月29日的记录 SELECT bond_code FROM bond_info WHERE interest_date1 = '02-29' OR interest_date2 = '02-29' ... OR interest_date12 = '02-29'; ``` 4. **使用Oracle高级特性**(12c+) ```sql SELECT TO_DATE(2023 || '-' || interest_date DEFAULT NULL ON CONVERSION ERROR, 'YYYY-MM-DD') FROM bond_info; ``` #### 错误处理流程 ```mermaid graph TD A[执行SQL报ORA-01839] --> B{错误是否在2月29日?} B -->|是| C[应用闰年转换逻辑] B -->|否| D[检查月份/日期组合] C --> E[重试SQL操作] D --> F{是否其他无效日期?} F -->|是| G[使用safe_to_date函数] F -->|否| H[检查隐式转换] G --> E H --> I[显式指定日期格式] I --> E E --> J[操作成功] ``` #### 预防措施 1. 始终使用显式日期转换:`TO_DATE('2023-02-28', 'YYYY-MM-DD')`[^4] 2. 避免依赖NLS_DATE_FORMAT会话设置 3. 对用户输入日期进行前端和后端双重验证 4. 使用`VALIDATE_CONVERSION`函数(Oracle 12c R2+): ```sql SELECT interest_date FROM bond_info WHERE VALIDATE_CONVERSION(interest_date AS DATE, 'MM-DD') = 0; ``` #### 扩展场景解决方案 **分布式系统事务错误(ORA-02054)** 当出现未决事务时: ```sql COMMIT; -- 或 ROLLBACK; ALTER SYSTEM KILL SESSION 'sid,serial#'; ``` **标识符无效错误(ORA-00904)** 检查对象权限和名称拼: ```sql SELECT * FROM all_tab_columns WHERE column_name = 'TENANT_ID'; GRANT SELECT ON gate_check TO current_user; ``` [^1]: 面对ORA-01017错误,保持耐心检查细节是关键[^1] [^2]: 处理2月29日的最佳方案是转换为3月1日或2月28日[^2] [^4]: 分批执行并缩小范围可定位无效日期记录[^4] --- ### 相关问题 1. 如何在Oracle中批量修改历史数据中的无效日期? 2. 付息日期计算中如何处理不同国家的日历差异? 3. Oracle 19c的自动无效日期检测机制如何配置? 4. 分布式事务中出现ORA-02054错误时如何保证数据一致性? 5. 如何设计债券付息日期的验证规则避免闰年问题?
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值