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
- 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
- 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
- For single field / multi field assignment, if the scheme using DUAL is adopted, pay attention to the applicable scenarios
- In case of single field or fewer fields and simple SQL, single field SQL assignment can be adopted
- If there are many fields or multiple fields and the SQL is complex, the assignment WITH TEMP AS + DUAL can be used