Oracle PL/SQL SELECT INTO 사용 시 주의사항 ORA-01403, ORA-01422, ORA-00913, ORA-00947

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;
/