background

In Oracle functions or stored procedures, the following syntax is usually used to assign values to variables:

-- Single variable assignment
SELECT [Field 1] INTO [Variable 1] FROM [data sheet] WHERE [query criteria];

-- Multivariable assignment
SELECT [Field 1], [Field 2] INTO [Variable 1], [Variable 2] FROM [data sheet] WHERE [query criteria];

Pit finding

The following examples take multivariable assignment as an example, and single variable assignment can be modified by reference

• Normal conditions:

-- A specified piece of data was found
SET SERVEROUTPUT ON;

DECLARE
    v_user_name VARCHAR2(50);
    v_emp_name VARCHAR2(50);
BEGIN
    SELECT 'zhangs' AS user_name, 'Zhang San' 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;

-- Output results: v_user_name: zhangs, v_emp_name: Zhang San

• Case 1: multiple pieces of data found by query

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 (
    	-- Simulation source query returns multiple results
        SELECT 'zhangs' AS user_name, 'Zhang San' AS emp_name FROM DUAL
        UNION ALL
        SELECT 'lis' AS user_name, 'Li Si' AS emp_name FROM DUAL
    );
    
    DBMS_OUTPUT.PUT_LINE('v_user_name: '|| v_user_name || ', v_emp_name: ' || v_emp_name);
END;

-- Output results: ORA-01422: The number of rows actually returned exceeds the number of requested rows

• Case 2: the query cannot find data

SET SERVEROUTPUT ON;

DECLARE
    v_user_name VARCHAR2(50);
    v_emp_name VARCHAR2(50);
BEGIN
    SELECT 'zhangs' AS user_name, 'Zhang San' AS emp_name 
    INTO v_user_name, v_emp_name 
    FROM DUAL
    -- Simulation cannot find any data
    WHERE 1=0;
    
    DBMS_OUTPUT.PUT_LINE('v_user_name: '|| v_user_name || ', v_emp_name: ' || v_emp_name);
END;
    
-- Output results: ORA-01403: No data found

As shown in the above example, we mainly need to deal with the case where the query returns no result set and multiple result sets

Pit filling

• Case 1: multiple pieces of data found by query

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 (
    	-- Simulation source query returns multiple results
        SELECT 'zhangs' AS user_name, 'Zhang San' AS emp_name FROM DUAL
        UNION ALL
        SELECT 'lis' AS user_name, 'Li Si' AS emp_name FROM DUAL
    ) 
	-- Eliminate error reporting by taking only the first line
	WHERE ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE('v_user_name: '|| v_user_name || ', v_emp_name: ' || v_emp_name);
END;

-- Output results: v_user_name: zhangs, v_emp_name: Zhang San

be careful!!!
This is not recommended
If there are duplicates in the data, it is recommended to remove them first and then assign values
If the result returns multiple pieces of data and the description is not repeated, which is inconsistent with the expected business logic, it is recommended to let the interface report errors and then troubleshoot them

The current project encountered this kind of pit because other colleagues took one at random, resulting in mismatched field values written to other business tables, resulting in functional disorder

• Case 2: the query cannot find data
  • Scheme 1: first COUNT the number of data through the COUNT function. If it is 1, assign a value

  SET SERVEROUTPUT ON;
  
  DECLARE
  	v_count int;
  	v_user_name VARCHAR2(50);
  	v_emp_name VARCHAR2(50);
  BEGIN
  	-- Query the total number of assignable data first
  	SELECT COUNT(1) INTO v_count FROM (
      	SELECT 'zhangs' AS user_name, 'Zhang San' AS emp_name FROM DUAL WHERE 1=0
  	);
  	
  	-- Judge the total number of assignable data before subsequent processing
  	IF v_count = 1 THEN
      	SELECT 'zhangs' AS user_name, 'Zhang San' 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;
  

  • Scheme 2: use DUAL table to check NULL assignment

  Use DUAL to query the field value. If there is a value, the specified value will be returned. If there is no value, NULL will be returned. You can add other built-in functions, such as NVL

  SET SERVEROUTPUT ON;
  
  DECLARE
  	v_user_name VARCHAR2(50);
  	v_emp_name VARCHAR2(50);
  BEGIN
  	-- Single field assignment(apply: Only one field is queried, there are few fields and SQL short)
  	SELECT (SELECT 'zhangs' AS user_name FROM DUAL WHERE 1=0) INTO v_user_name FROM DUAL;
  	SELECT (SELECT 'Zhang San' AS emp_name FROM DUAL WHERE 1=0) INTO v_emp_name FROM DUAL;
  	
  	-- Multi field assignment(use: Check multiple fields or SQL More complex)
  	WITH TEMP AS (
  		-- Simulation cannot find result set
      	SELECT 'zhangs' AS user_name, 'Zhang San' 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;
  
  -- Output results: v_user_name: , v_emp_name: 
  

summary

1. For cases that violate the expected logic, we must not make temporary solutions to solve the error report, so as to avoid increasing the difficulty of problem troubleshooting in the later stage

For example, the processing scheme of returning multiple pieces of data and randomly taking the first one

1. For single field / multi field assignment, if the scheme of COUNT judgment is adopted, during subsequent maintenance, if the SQL statement of assignment is modified, remember to modify the COUNT judgment SQL
2. For single field / multi field assignment, if the scheme using DUAL is adopted, pay attention to the applicable scenarios
   1. In case of single field or fewer fields and simple SQL, single field SQL assignment can be adopted
   2. If there are many fields or multiple fields and the SQL is complex, the assignment WITH TEMP AS + DUAL can be used