[오라클로 배우는 데이터베이스 입문] 18. 특정 열을 선택하여 처리하는 커서

1. 특정 열을 선택하여 처리하는 커서

- 커서란?

SELECT문 또는 데이터 조작어 같은 SQL문을 실행했을 때 해당 SQL문을 처리하는 정보를 저장한 메모리 공간

-SELECT INTO

SELECT 열1,열2,...,열n INTO 변수1,변수2,...,변수n FROM...

SELECT절에 명시한 열과 INTO절에 명시한변수는 개수와 자료형이 일치해야 함

SELECT INTO를 사용한 단일행 데이터 저장하기

※ 데이터 조회의 결과 값은 하나인 경우보다 여러 개인 경우가 흔하며 결과 행이 하나일지 여러 개일지 알 수 없는 경우도 존재하므로 대부분 커서를 활용함.

- 명시적 커서: 사용자가 직접 커서를 선언하고 사용하는 커서

단계	명칭	설명
1단계	커서 선언(declaration)	사용자가 직접 이름을 지정하여 사용할 커서를 SQL문과 함께 선언
2단계	커서 열기(open)	커서를 선언할 때 작성한 SQL문을 실행, 이 때 실행한 SQL문에 영향을 받는 행을 active set이라고 함
3단계	커서에서 읽어온 데이터 사용(fetch)	실행된 SQL문의 결과 행 정보를 하나씩 읽어 와서 변수에 저장한 후 필요한 작업을 수행, 각 행별로 공통 작업을 반복해서 실행하기 위해 여러 종류의 LOOP문을 함께 사용할 수 있음
4단계	커서 닫기(close)	모든 행의 사용이 끝나고 커서를 종료

기본형식 DECLARE CURSOR 커서 이름 IS SQL문; ---- 커서 선언 BEGIN OPEN 커서 이름; ---- 커서 열기(Open) FETCH 커서 이름 INTO 변수 ---- 커서로부터 읽어온 데이터 사용(Fetch) CLOSE 커서 이름; ---- 커서 닫기(Close) END;

○ 하나의 행만 조회되는 경우

단일행 데이터를 저장하는 커서 사용하기

○ 여러 행이 조회되는 경우 사용하는 LOOP문

FETCH문에서 쓸 수 있는 속성
속성	설명
커서 이름%NOTFOUND	수행된 FETCH문을 통해 추출된 행이 있으면 false, 없으면 true를 반환
커서 이름%FOUND	수행된 FETCH문을 통해 추출된 행이 있으면 true, 없으면 false를 반환
커서 이름%ROWCOUNT	현재까지 추출된 행 수를 반환
커서 이름%ISOPEN	커서가 열려있으면 true,닫혀있으면 false반환

○ 여러 개의 행이 조회되는 경우(FOR LOOP)

FOR 루프 인덱스 이름 IN 커서 이름 LOOP 결과 행별로 반복 수행할 작업; END LOOP; ※ FOR LOOP문을 사용하면 OPEN,FETCH,CLOSE문 작성 안함

※ 루프인덱스는 커서에 저장된 각 행이 저장되는 변수를 뜻하며 '.'를 통해 행의 각 필드에 접근할 수 있음

○ 커서에 파라미터 사용하기

CURSOR 커서 이름(파라미터 이름 자료형,...) IS SELECT ...

파라미터를 사용하는 커서 알아보기

커서에 사용할 파라미터 입력받기

- 묵시적 커서 : 별다른 선언 없이 SQL문을 사용했을 때 오라클에서 자동으로 선언되는 커서

※ 묵시적 커서의 속성: 속성을 사용하면 현재 커서의  정보를 확인할 수 있음

속성	설명
SQL%NOTFOUND	묵시적 커서 안에 추출된 행이 있으면 false, 없으면 true를 반환/DML명령어로 영향을 받는 행이 없을 경우에도 true반환
SQL%FOUND	묵시적 커서 안에 추출된 행이 있으면 true, 없으면 false를 반환/DML명령어로 영향을 받는 행이 있으면 true반환
SQL%ROWCOUNT	묵시적 커서에 현재까지 추출한 행 수 또는 DMP 명령어로 영향받는 행 수를 반환
SQL%ISOPEN	묵시적 커서는 자동으로 SQL문 실행한 후 CLOSE되므로 이 속성을 항상 false를 반환

2. 오류가 발생해도 프로그램이 비정상 종료되지 않도록 하는 예외 처리

 - 오류란? SQL또는 PL/SQL이 정상 수행되지 못하는 상황으로 크게 2가지로 구분됨

○ 컴파일 오류, 문법 오류: 문법이 잘못되었거나 오타로 인한 오류

○ 런타임 오류, 실행 오류: 명령문의 실행 중 발생한 오류 [ 이것을 '예외(exception)'라고 함]

예외가 발생하는 PL/SQL

예외를 처리하는 EXCEPTION문 삽입

EXCEPTION문을 삽입하면 예외가 발생한 코드 이후 내용은 실행되지 않음

- 예외종류

예외종류		설명
내부예외 (internal exception)	사전 정의된 예외 (predefined name exceotions)	내부 예외 중 예외 번호에 해당하는 이름이 존재하는 예외
	이름이 없는 예외 (unnamed exceptions)	내부 예외 중 이름이 존재하지 않는 예외 (사용자가 필요에 따라 이름을 지정할 수 있음)
사용자 정의 예외(user-defined exceptions)		사용자가 필요에 따라 직접 정의한 예외

○ 자주 발생하는 사전 정의된 예외

예외 이름	예외 번호(SQLCODE)	설명
ACCESS_INTO_NULL	ORA-06530:-6530	초기화되지 않은 객체 속성 값 할당
CASE_NOT_FOUND	ORA-06592:-6592	CASE문의 WHERE절에 조건이 없고 ELSE도 없는경우
COLLECTION_IS_NULL	ORA-06531:-6531	초기화되지 않은 중첩 테이블, VARRAY에 EXIT 외 컬렉션 메서드를 사용할 경우 또는 초기화되지 않은 중첩 테이블이나 VARRAY에 값을 대입하려 할 경우
CURSOR_ALREADY_OPEN	ORA-06511:-6511	이미 OPEN된 커서를 OPEN시도할 경우
DUP_VAL_ON_INDEX	ORA-00001:-1	UNIQUE 인덱스가 있는 열에 중복된 값을 저장하려고 했을 경우
INVALID_CURSOR	ORA-01001:-1001	OPEN되지 않은 커서를 CLOSE 시도하는 것과 같이 잘못된 커서 작업을 시도하는 경우
INVALID_NUMBER	ORA-01722:-1722	문자에서 숫자로의 변환이 실패했을 경우
LOGIN_DENIED	ORA-01017:-1017	사용자 이름이나 패스워드가 올바르지 않은 상태에서 로그인을 시도할 경우
NO_DATA_FOUND	ORA-01403:+100	SELECT INTO문에서 결과 행이 하나도 없을 경우
NOT_LOGGED_ON	ORA_01012:-1012	데이터베이스에 접속되어 있지 않은 경우
PROGRAM_ERROR	ORA_06501:-6501	PL/SQL 내부 오류가 발생했을 경우
ROWTYPE_MISMATCH	ORA-06504:-6504	호스트 커서 변수와 PL/SQL커서 변수의 자료형이 호환되지 않을 경우
SELF_IS_NULL	ORA-30625:-30625	초기화되지 않은 오브젝트의 MEMBER 메서드를 호출한 경우
STORAGE_ERROR	ORA-06500:-6500	PL/SQL 메모리가 부족하거나 문제가 발생한 경우
SUBSCRIPT_BEYOND_COUNT	ORA-06532:-6532	정상 범위외 인덱스 번호를 사용하여 중첩 테이블이나 VARRAY 요소 참조를 시도할 경우
SYS_INVALID_ROWID	ORA-01410:-1410	문자열을 ROWID로 변환할 때 값이 적절하지 않은 경우
TIMEOUT_ON_RESOURCE	ORA-00051:-51	자원 대기 시간을 초과했을 경우
TOO_MANY_ROWS	ORA-01422:-1422	SELECT INTO문의 결과 행이 여러 개일 경우
VALUE_ERROR	ORA-06502:-6502	산술, 변환, 잘림, 제약 조건 오류가 발생했을 경우
ZERO_DIVIDE	ORA-01476:-1476	숫자 데이터를 0으로 나누려고 했을 경우

○ 이름 없는 예외 : ORA-XXXXX식으로 번호는 있지만 이름은 정해져 있지 않기 때문에 예외처리부에서 이름을 직접 붙여서 사용해야 함

- 예외 처리부 작성

EXCEPTION WHEN 예외이름1[OR 예외 이름2] THEN ----예외 핸들러(exception handler) 예외처리에 사용할 명령어; WHEN 예외이름3[OR 예외 이름4] THEN 예외 처리에 사용할 명령어; ... WHEN OTHERS THEN ---- 먼저 작성한 어느 예외와도 일치하는 예외가 없는 경우 처리할 내용 예외 처리에 사용할 명령어;

○ 사전 정의된 예외 사용

○ 이름 없는 예외 사용

DECLARE 예외이름1 EXCEPTION; PRAGMA EXCEPTION_INIT(예외 이름1, 예외 번호); . . . EXCEPTION WHEN 예외이름1 THEN  예외처리에 사용할 명령어; ... END;

○ 사용자 정의 예외 사용
오라클에 정의되어 있지 않은 특정 상황을 오류로 정의하는 방식, RAISE 키워드를 사용하여 예외를 직접 만들 수 있음

DECLARE 사용자 예외 이름 EXCEPTION; ... BEGIN IF 사용자 예외를 발생시킬 조건 THEN RAISE 사용자 예외 이름 ... END IF; EXCEPTION WHEN 사용자 예외 이름 THEN  예외처리에 사용할 명령어; ... END;

○ 오류 코드와 오류 메시지 사용

- 오류 처리부가 잘 작성되어 있으면 오류가 발생해도 PL/SQL은 정상종료됨

- 발생한 오류 내역 알고 싶을 때 쓰는 함수(PL/SQL에서만 가능)

함수	설명
SQLCODE	오류 번호를 반환하는 함수
SQLERRM	오류 메시지를 반환하는 함수

오류 코드와 오류 메시지 사용하기