[Oracle] PL/SQL(2)

RECORD

레코드(RECORD)는 자바의 클래스와 동일합니다. 다양한 데이터 타입을 갖는 여러 변수를 갖는 자료형입니다.

데이터 타입은 VARCHAR2, NUMBER, DATE 등 다양하게 있습니다. 기본 데이터 타입과 테이블명.컬럼명%TYPE, 테이블명.ROW%TYPE 등 선언 가능합니다. "%TYPE"이라는 키워드를 기억합시다.

 

 

• 레코드의 선언방법

    TYPE 레코드명 IS RECORD(
            변수이름1 데이터타입[NOT NULL, DEFAULT]:=값 OR 수식;
            변수이름2 테이블명.컬럼명%TYPE;
            ....
    )

• 레코드가 다양한 데이터타입의 변수를 갖는 집합으로 볼 수 있고, Java의 class에서 메소드만 빠진 형태입니다.
• 순서가 있고 이를 파악하면 좋습니다.
  1. 레코드 선언
     • TYPE 레코드명 IS RECORD( 변수 정의해줍니다.);
  2. 레코드 변수를 객체로 생성합니다. (JAVA의 생성자처럼 객체 생성)
     • 레코드변수 레코드명;
  3. 본체에서 속성을 각각 정의해주는데 아래와 같이 작성합니다.
     1. 레코드변수.선언부에서 정의한 변수명 := 할당할 값;
     2. 필요시 출력을 합니다. (DBMS_OUTPUT.PUT_LINE(레코드변수.선언부 변수명);)

DECLARE
    --레코드 선언부
    TYPE STU_REC IS RECORD(
        --사용할 다양한 데이터타입의 변수들 선언
        SNO VARCHAR2(5) NOT NULL := '11012',
        SNAME STUDENT.SNAME%TYPE,
        SEX STUDENT.SEX%TYPE,
        SYEAR NUMBER(1) DEFAULT 1,
        MAJOR STUDENT.MAJOR%TYPE,
        AVR STUDENT.AVR%TYPE  
    );

  --레코드 변수 선언
    STUDENTREC STU_REC;
BEGIN
    --속성정의
    STUDENTREC.SNO := '11010'; --위에서 만든 변수명 넣기
    STUDENTREC.SNAME := '홍길동';
    STUDENTREC.SEX := '남';
    STUDENTREC.MAJOR := '화학';
    STUDENTREC.AVR := 2.5;
    
    --출력
    DBMS_OUTPUT.PUT_LINE(STUDENTREC.SNO);
    DBMS_OUTPUT.PUT_LINE(STUDENTREC.SNAME);
    DBMS_OUTPUT.PUT_LINE(STUDENTREC.SEX);
    DBMS_OUTPUT.PUT_LINE(STUDENTREC.MAJOR);
    DBMS_OUTPUT.PUT_LINE(STUDENTREC.AVR);
END;
/

 

이렇게 만든 레코드는 데이터를 저장할 때 사용할 수 있습니다. 먼저 저장할 테이블을 AS SELECT를 통해 생성하고,  다음에 레코드를 활용해서 데이터를 저장합니다. 이 때에는 INSERT INTO 구문을 사용할 수 있습니다.

 

--레코드를 이용해서 데이터 저장할 테이블 생성
CREATE TABLE STUDENT_RECORD
AS SELECT * FROM STUDENT;

 이 작업에서 STUDENT_RECORD라는 테이블을 기존 테이블 값을 그대로 넣어서 생성합니다.

--레코드를 이용해서 데이터 저장
DECLARE
    TYPE STU_REC IS RECORD(
	...
    );
    
    STUDENTREC STU_REC;
    
BEGIN
	...
    --인서트1
    INSERT INTO STUDENT_RECORD
    VALUES STUDENTREC;
    
    --인서트2
    INSERT INTO STUDENT_RECORD
    VALUES STUDENTREC; 
END;
/

 위 코드를 통해, STUDENT_RECORD에 STUDENTREC이라는 레코드변수의 값을 속성정의하고 그것을 INSERT INTO 구문을 통해 바로 입력하여 저장시킬 수 있습니다. 위 코드는 STUDENT_RECORD 테이블에 STUDENTREC에 있는 정의된 속성 정보를 한꺼번에 넣겠다는 의미입니다.

이 때 타입과 순서는 선언부에서 정의한 것과 동일해야 합니다. 위에서 NUMBER로 선언하면 숫자가 들어가고, VARCHAR2면 문자가 들어가며 그 순서가 NUMBER, VARCHAR2면 숫자, 문자 순으로 들어가야 한다는 의미입니다!

 

• 레코드의 사용방법 3가지가 있으며, 대부분 자바의 클래스처럼 .(DOT) 메소드를 사용합니다.

레코드변수이름 레코드명;
레코드변수이름.변수이름1:=값;
DBMS_OUTPUT.PUT_LINE(레코드변수이름.변수이름2);

 

연관배열

• 같은 데이터 타입의 데이터를 여러개 가질 수 있는 자료형. 인덱스를 키라고 부르며 키(key)와 값(value)로 구성됩니다.
• 연관배열 선언 방법

TYPE 배열명 IS TABLE OF 데이터타입[NOT NULL, DEFAULT]
    INDEX BY 인덱스의 형태(BINARY_INTEGER, PLS_INTEGER, VARCHAR2...)

• 연관배열 사용 방법

배열변수이름 배열명;
배열변수이름(index);

 

• 연관배열의 메소드

 

EXISTS(INDEX)	해당 INDEX에 데이터가 존재하는지 여부를 판단합니다. TRUE/FALSE(출력x)
COUNT	배열에 저장된 데이터의 개수를 반환해주는 메소드입니다.
LIMIT	배열의 최대크기 반환합니다.
FIRST	배열의 첫 번째 인덱스 번호 반환합니다.
LAST	배열의 마지막 인덱스 번호 반환합니다.
PRIOR(INDEX)	해당 INDEX 바로 이전 INDEX 반환합니다. 바로 이전 INDEX가 없으면 NULL 반환합니다.
NEXT(INDEX)	해당 INDEX 바로 뒤 INDEX를 반환합니다. 바로 뒤 INDEX가 없으면 NULL 반환합니다.
DELETE	배열에 저장되어 있는 모든 데이터 삭제합니다.
DELETE(INDEX)	해당 INDEX에 들어있는 데이터 삭제합니다.
DELETE(N, M)	N 인덱스부터 M 인덱스 까지의 모든 데이터 삭제합니다.

 

CURSOR

커서(Cursor)는 쿼리문의 결과를 저장해두는 메모리 공간입니다.

PL/SQL에서는 쿼리문의 결과인 커서를사용해서 데이터를 조작합니다. 예를 들면, 결과 값을 변수로 담아내거나 RECORD, ROWTYPE의 형태로 저장해서 사용하게 해줍니다.

 

다만, 커서는 명시적 커서, 묵시적 커서로 나누어 지는데요, 그 형태에 따라 선언 방법이 다릅니다.

 

커서의 명시적 선언

-- 선언부(DECLARE)에서 작성합니다.
DECLARE
    CURSOR 커서명 IS 쿼리문;

 

커서의 사용은 실행부(BEGIN) 이하에서 사용할 수 있습니다.

BEGIN
    OPEN 커서명;
     --커서로부터 읽어온 데이터를 사용
     FETCH 커서명 INTO 변수;
     CLOSE 커서명; --END가 아닙니다!

 

커서는 특수한 속성이 있습니다. 커서 이름에 %로 연결하며, NOTFOUND, FOUND, ROWCOUNT, ISOPEN이 있습니다.

특수한 속성	역할
커서명%NOTFOUND	FETCH로 추출된 행이 없으면 TRUE, 있으면 FALSE 리턴.
커서명%FOUND	FETCH로 추출된 행이 있으면 TRUE, 없으면 FALSE 리턴. (위와
커서명%ROWCOUNT	현재까지 추출된 행의 개수를 리턴.
커서명%ISOPEN	커서가 열려있으면 TRUE, 닫혀있으면 FALSE 리턴.

 

커서는 파라미터를 활용할 수 있습니다. 이 파라미터는 고정적인 값을 조회하지 않고, 변하는 데이터를 커서에 저장해서 사용하고 싶다면 커서에 추가할 수 있습니다.

--선언
CURSOR 커서명(파라미터명 데이터타입, ....)
--사용
FETCH 커서명(데이터타입 입력값)

 

묵시적 커서

 

 위처럼 명시적으로 커서를 선언하지 않고 쿼리문을 실행하면, 오라클 내부에서 결과를 저장했을 때 그 저장된 것을 묵시적 커서로 사용할 수 있습니다. DML(INSERT, DELETE, UPDATE)에서는 처리된 행의 개수를 결과로 저장하는데, 이를 묵시적 커서를 이용하면 그 저장된 결과가 갖는 행의 개수를 가져올 수 있습니다. 관련 속성으로는 SQL%NOTFOUND, SQL%FOUND, SQL%ROWCOUNT, SQL%ISOPEN 정도가 있습니다.

 

예외처리(EXCEPTION)

 마지막으로 예외처리에 대해 간단히 소개하겠습니다. 정상적으로 동작이 수행되지 않으면 오류가 발생합니다. 이 때 EXCEPTION으로 예외처리부를 작성해서 이를 처리합니다. 예외처리부는 거의 롤백(ROLLBACK)으로 처리해서 앞에서 실행된 쿼리가 적용되지 않게 트랜잭션을 취소하는 방향으로 작성됩니다.

 

 예외는 내부 예외 및 사용자 정의 예외로 나누고, 내부 예외는 사전에 정의된 것과 이름 없는 예외로 다시 나누어집니다.

간단히 정리해보면 다음과 같습니다.

내부 예외	사전 정의된 예외	내부 예외 중에서 예외번호와 지정된 이름이 있습니다.
	이름 없는 예외	이름이 없는 예외로, 사용자가 이름을 지정할 수도 있습니다.
사용자 정의 예외	사용자가 직접 정의한 예외를 말합니다.

 

이번에는 자주 쓰는 사전에 정의된 예외를 알아보겠습니다. 대부분 이 사전 정의된 예외로 처리하고, 여기서 해결이 안되면 OTHERS로 처리하는 편입니다.

 

예외 이름	예외 번호	설명
ACCESS_INTO_NULL	ORA-06530	초기화되지 않은 객체 속성 값 할당
CASE_NOT_FOUND	ORA-06592	CASE문 WHEN절에 조건이 없고 ELSE절도 없을 경우
COLLECTION_IS_NULL	ORA-06531	초기화되지 않은 배열(값이 할당되지 않은 배열) 사용
CURSOR_ALREADY_OPEN	ORA-06511	이미 오픈된 커서를 재오픈했을 경우
DUP_VAL_ON_INDEX	ORA-00001	UNIQUE 인덱스가 있는 열에 중복된 값을 저장했을 경우
INVALID_CURSOR	ORA-01001	오픈되지 않은 커서를 클로즈할 때 처럼 커서 잘못 사용
INVALID_NUMBER	ORA-01722	숫자타입에 숫자타입이 아닌 값을 저장할 경우
LOGIN_DENIED	ORA-01017	사용자 이름이나 비밀번호 오입력했을 경우
NO_DATA_FOUND	ORA-01403	SELECT 문의 결과가 0행으로 리턴됐을 경우
NOT_LOGGED_ON	ORA-01012	DB에 접속이 안 되어 있을 경우
PROGRAM_ERROR	ORA-06501	PL/SQL 자체 내부 에러 발생했을 경우
ROWTYPE_MISMATCH	ORA-06504	커서변수와 데이터타입이 호환되지 않을 경우
TIMEOUT_ON_RESOURCE	ORA-00051	자원 대기시간 초과했을 경우
TOO_MANY_ROWS	ORA-01422	SELECT INTO 결과 행이 여러개일 때
VALUE_ERROR	ORA-06502	산술, 변환, 잘림, 제약조건 에러가 발생했을 경우
ZERO_DIVIDE	ORA-01476	0으로 나눴을 경우

 

예외처리부는 아래와 같이 작성합니다.

WHEN 예외이름1 THEN 
  예외이름1인 예외가 발생했을 때 처리할 내용;
WHEN 예외이름2 THEN
  예외이름2인 예외가 발생했을 때 처리할 내용;
...
WHEN OTHERS THEN
  위에서 처리하지 못한 예외가 발생했을 때 처리할 내용;

이름없는 내부 예외는 선언부(DECLARE)에서 예외 이름을 선언해서 만듭니다. 이를 사용하면 다음과 같이 작성할 수 있습니다. 주로 예외번호만 있고 예외이름이 없는 경우에 예외 처리할 때 사용합니다.

DECLARE 
	예외이름 EXCEPTION;
BEGIN    
	PRAGMA EXCEPTION_INIT(예외이름, 예외번호);
    EXCEPTION 선언부에서 만든 이름으로 예외처리
    WHEN 예외이름 THEN
    	처리내용;

 

사용자 정의 예외는, 선언부에서 예외이름을 선언해주는 것은 같습니다. 다만 실행부(BEGIN)에서 RAISE라는 키워드를 사용하는 점이 다릅니다.

DECLARE
	예외이름 EXCEPTION;
BEGIN
	RAISE 예외이름
    EXCEPTION 예외이름으로 처리할 내용
    WHEN 예외이름 THEN 처리내용;

 

관련해서 SQLCODE, SQLEARN을 사용할 수 있습니다. SQLCODE는 에러 코드번호를 반환하고, SQLERRM는 에러 메시지를 반환합니다. SQL ERROR MESSAGE로 기억하면 편합니다.

가령 다음과 같은 예시가 있을 때, SQLCODE는 1722가 되고, SQLERM은 "수치가 부적합합니다"를 반환할 것입니다.

ORA-01722: 수치가 부적합합니다
수치가 부적합
롤백완료