PL/SQL 에서 SELECT INTO 사용 시 주의사항에 대해 알아봅시다. 이번 주제는 ORA-01403 (no data found), ORA-01422 (exact fetch returns more than requested number of rows) 등의 오류가 발생하는 상황에 대한 예시를 알아보는 시간입니다.
먼저 예시테이블(EMP 테이블)명세를 간단히 안내해드리고 진행하겠습니다.
예시 테이블 확인사항입니다~ 해당 자료를 토대로 의도적으로 예외를 발생시킬 예정이오니, 참고해 주세요!
첫 번째, EMP 테이블에는 [EMPNO]가 '0000'인 사원이 존재하지 않습니다. 만약 [EMPNO]가 '0000'인 사원을 조회하면 어떻게 될 지 알아봅시다.
두 번째, EMP 테이블에서 [JOB]이 'SALESMAN'인 사원은 적어도 2명 이상입니다. 두 개 이상의 결과 데이터를 조회하면 어떻게 될 지 알아보도록 합시다.
이 2가지 부분을 염두에 두고 진행하겠습니다~
ORA-01403: 데이터를 찾을 수 없습니다. (ORA-01403: no data found)
[EMPNO]가 '0000'인 사원을 찾으려고 시도해보겠습니다. [EMPNO]가 '0000'인 사원이 없을 때, 어떠한 결과를 가져올까요?
SQL에서는 No Row를 반환하겠으나 PL/SQL에서는 No Data Found 예외가 발생합니다. 아래는 예시 쿼리입니다.
--ORA-01403: no data found
SET SERVEROUTPUT ON
DECLARE
V EMP.ENAME%TYPE;
BEGIN
SELECT ENAME
INTO V
FROM EMP
WHERE EMPNO = '0000';
END;ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다. (ORA-01422: exact fetch returns more than requested number of rows)
[JOB]이 'SALESMAN'인 사원을 찾는 시도를 진행하겠습니다. 참고로 SALESMAN이 직업인 사원은 2개 로우 이상입니다.
PL/SQL에서는 varchar2와 같은 Scalar Data Type에 많은 수의 행을 추출하려면 예외가 발생합니다. 다음은 예시쿼리입니다.
--ORA-01422: exact fetch returns more than requested number of rows
SET SERVEROUTPUT ON;
DECLARE
V EMP.ENAME%TYPE;
BEGIN
SELECT ENAME
INTO V
FROM EMP
WHERE JOB = 'SALESMAN';
END;ORA-00913: 값의 수가 너무 많습니다 (ORA-00913: too many values)
이번에는 EMP 테이블의 로우타입에 VARCHAR2 형식의 하나의 컬럼을 할당하도록 해보겠습니다.
로우타입은 총 8개의 컬럼(EMP 테이블 컬럼 8개)으로 구성되어 있는데 하나의 컬럼으로 설정을 시도하므로, 값의 수가 너무 많다는 오류를 반환합니다.
--PL/SQL: ORA-00913: too many values
SET SERVEROUTPUT ON;
DECLARE
V EMP%ROWTYPE;
BEGIN
SELECT ENAME
INTO V
FROM EMP
WHERE EMPNO = '7839';
END;ORA-00947: 값의 수가 충분 하지 않습니다. (ORA-00947: not enough values)
이번에는 반대로 스칼라 데이터 타입에 [ENAME], [EMPNO]와 같이 여러 개의 컬럼을 할당하도록 시도해봅니다. 이때에는 값의 수가 충분하지 않다는 오류를 반환합니다.
--PL/SQL: ORA-00947: not enough values
SET SERVEROUTPUT ON;
DECLARE
V EMP.ENAME%TYPE;
BEGIN
SELECT ENAME, EMPNO
INTO V
FROM EMP
WHERE EMPNO = '7839';
END;다음 자료는 테스트 해볼 수 있는 예시를 모아보았습니다~ 참고하여 확인해 주세요.
SET SERVEROUTPUT ON
DECLARE
TYPEEMPEMPNO EMP.ENAME%TYPE;
ROWTYPEEMP EMP%ROWTYPE;
--SELECT INTO 에 대해 알아봅시다
VCHAR VARCHAR2 (100);
VTYPE TYPEEMPEMPNO%TYPE;
VROWTYPE ROWTYPEEMP%TYPE;
BEGIN
--1.스칼라 타입
SELECT ENAME
INTO VCHAR
FROM EMP
WHERE EMPNO = '7839';
DBMS_OUTPUT.PUT_LINE ('Scalar Type: ' || VCHAR);
--2.타입
SELECT 'TYPE' INTO VTYPE FROM DUAL;
DBMS_OUTPUT.PUT_LINE ('Type: ' || VTYPE);
--3.로 타입
SELECT NULL,
'ROW TYPE',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
INTO VROWTYPE
FROM DUAL;
DBMS_OUTPUT.PUT_LINE ('Row Type: ' || VROWTYPE.ENAME);
--ORA-01403: no data found , 데이터를 찾을 수 없습니다.
BEGIN
--EMPNO 가 0000 인 행이 없습니다.
SELECT ENAME
INTO VCHAR
FROM EMP
WHERE EMPNO = '0000';
DBMS_OUTPUT.PUT_LINE ('Test: ' || VCHAR);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('EXCEPTION OTHERS: ' || SQLERRM);
END;
--ORA-01422: exact fetch returns more than requested number of rows , 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다.
BEGIN
--JOB 이 SALESMAN 인 행은 복수개입니다.
SELECT ENAME
INTO VCHAR
FROM EMP
WHERE JOB = 'SALESMAN';
DBMS_OUTPUT.PUT_LINE ('Test: ' || VCHAR);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('EXCEPTION OTHERS: ' || SQLERRM);
END;
--TYPE 또한 마찬가지로 행이 없거나 너무 많은 경우 예외가 발생합니다.
BEGIN
SELECT 'TYPE'
INTO VTYPE
FROM DUAL
WHERE 0 = 1;
DBMS_OUTPUT.PUT_LINE ('Test: ' || VCHAR);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('EXCEPTION OTHERS: ' || SQLERRM);
END;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('EXCEPTION OTHERS: ' || SQLERRM);
END;
/