ORACLE SELECT INTO 语法 ORA-01422、ORA-01403错误的 解决方案

背景

Oracle函数或存储过程中，对变量赋值通常会用到如下语法：

-- 单变量赋值
SELECT [字段1] INTO [变量1] FROM [数据表] WHERE [查询条件];

-- 多变量赋值
SELECT [字段1], [字段2] INTO [变量1], [变量2] FROM [数据表] WHERE [查询条件];

找坑

以下示例都以多变量赋值为例说明, 单变量赋值自行参考修改即可

• 正常情况:

-- 找到指定的一条数据
SET SERVEROUTPUT ON;

DECLARE
    v_user_name VARCHAR2(50);
    v_emp_name VARCHAR2(50);
BEGIN
    SELECT 'zhangs' AS user_name, '张三' AS emp_name 
    INTO v_user_name, v_emp_name 
    FROM DUAL 
    WHERE 1=1;
    
    DBMS_OUTPUT.PUT_LINE('v_user_name：'|| v_user_name || ', v_emp_name: ' || v_emp_name);
END;

-- 输出结果: v_user_name：zhangs, v_emp_name: 张三

• 情况1: 查询找到多条数据

SET SERVEROUTPUT ON;

DECLARE
    v_user_name VARCHAR2(50);
    v_emp_name VARCHAR2(50);
BEGIN
    SELECT user_name, emp_name 
    INTO v_user_name, v_emp_name 
    FROM (
    	-- 模拟源查询返回多条结果
        SELECT 'zhangs' AS user_name, '张三' AS emp_name FROM DUAL
        UNION ALL
        SELECT 'lis' AS user_name, '李四' AS emp_name FROM DUAL
    );
    
    DBMS_OUTPUT.PUT_LINE('v_user_name：'|| v_user_name || ', v_emp_name: ' || v_emp_name);
END;

-- 输出结果: ORA-01422: 实际返回的行数超出请求的行数

• 情况2: 查询找不到数据

SET SERVEROUTPUT ON;

DECLARE
    v_user_name VARCHAR2(50);
    v_emp_name VARCHAR2(50);
BEGIN
    SELECT 'zhangs' AS user_name, '张三' AS emp_name 
    INTO v_user_name, v_emp_name 
    FROM DUAL
    -- 模拟找不到任何数据
    WHERE 1=0;
    
    DBMS_OUTPUT.PUT_LINE('v_user_name：'|| v_user_name || ', v_emp_name: ' || v_emp_name);
END;
    
-- 输出结果: ORA-01403: 未找到任何数据

如上示例, 我们主要需要处理查询无结果集返回和返回多条结果集的情况

填坑

• 情况1: 查询找到多条数据

SET SERVEROUTPUT ON;

DECLARE
    v_user_name VARCHAR2(50);
    v_emp_name VARCHAR2(50);
BEGIN
    SELECT user_name, emp_name 
    INTO v_user_name, v_emp_name 
    FROM (
    	-- 模拟源查询返回多条结果
        SELECT 'zhangs' AS user_name, '张三' AS emp_name FROM DUAL
        UNION ALL
        SELECT 'lis' AS user_name, '李四' AS emp_name FROM DUAL
    ) 
	-- 通过只取第一行来消除报错
	WHERE ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE('v_user_name：'|| v_user_name || ', v_emp_name: ' || v_emp_name);
END;

-- 输出结果: v_user_name：zhangs, v_emp_name: 张三

注意!!!
不建议采用这种方式处理
如果数据有重复, 建议先去重再赋值
如果结果返回了多条数据且不重复说明与预期业务逻辑不符，建议让接口报错再排错

当前项目遇到这种坑，就是因为其他同事随机取了一条，导致最终写入其他业务表的字段值匹配不上，最终引起功能紊乱

• 情况2: 查询找不到数据
  • 方案1: 先通过COUNT函数统计数据条数, 如果为1条则赋值

  SET SERVEROUTPUT ON;
  
  DECLARE
  	v_count int;
  	v_user_name VARCHAR2(50);
  	v_emp_name VARCHAR2(50);
  BEGIN
  	-- 先查询可赋值数据总条数
  	SELECT COUNT(1) INTO v_count FROM (
      	SELECT 'zhangs' AS user_name, '张三' AS emp_name FROM DUAL WHERE 1=0
  	);
  	
  	-- 判断可赋值数据总条数再做后续处理
  	IF v_count = 1 THEN
      	SELECT 'zhangs' AS user_name, '张三' AS emp_name INTO v_user_name, v_emp_name FROM DUAL WHERE 1=0;
  	END IF;
  
  	DBMS_OUTPUT.PUT_LINE('v_user_name：'|| v_user_name || ', v_emp_name: ' || v_emp_name);
  END;
  

  • 方案2: 利用DUAL表查NULL赋值

  利用DUAL查询字段值, 如果有值则返回指定值, 如果没有值则返回NULL, 可以再加其他内置函数处理, 如NVL等

  SET SERVEROUTPUT ON;
  
  DECLARE
  	v_user_name VARCHAR2(50);
  	v_emp_name VARCHAR2(50);
  BEGIN
  	-- 单字段赋值(适用: 只查一个字段、查询字段少且SQL短)
  	SELECT (SELECT 'zhangs' AS user_name FROM DUAL WHERE 1=0) INTO v_user_name FROM DUAL;
  	SELECT (SELECT '张三' AS emp_name FROM DUAL WHERE 1=0) INTO v_emp_name FROM DUAL;
  	
  	-- 多字段赋值(使用: 查多个字段或SQL比较复杂)
  	WITH TEMP AS (
  		-- 模拟找不到结果集
      	SELECT 'zhangs' AS user_name, '张三' AS emp_name FROM DUAL WHERE 1=0
  	)
  	SELECT (SELECT user_name FROM temp), (SELECT emp_name FROM temp) INTO v_user_name, v_emp_name FROM DUAL;
  
  	DBMS_OUTPUT.PUT_LINE('v_user_name：'|| v_user_name || ', v_emp_name: ' || v_emp_name);
  END;
  
  -- 输出结果: v_user_name：, v_emp_name: 
  

总结

1. 对于违背预期逻辑的情况，一定不要为了解决报错而做临时方案处理，避免加大后期问题排查难度

比如返回多条数据，随机取第一条的处理方案

2. 单字段 / 多字段赋值，如果采用 COUNT 判断的方案，则后续维护过程中，如果修改了赋值的SQL语句，一定要记得修改COUNT判断SQL
3. 单字段 / 多字段赋值, 如果采用 利用DUAL 的方案，注意适用场景
   1. 如果是单字段或较少字段, SQL简单的情况，则可以采用单字段SQL赋值
   2. 如果是较多字段或多字段, SQL复杂的情况, 则可以采用 WITH TEMP AS + DUAL赋值