[Oracle] PL/SQL에 대해서 알아봅시다! 2

PL/SQL 종류 및 사용방법에 대해서 살펴봅시다.!!

앞서 PL/SQL이 무엇인지에 대하여 설명하는 시간을 가졌습니다. 

참고 : http://najuung.tistory.com/entry/Oracle-PLSQL%EC%97%90-%EB%8C%80%ED%95%B4%EC%84%9C-%EC%95%8C%EC%95%84%EB%B4%85%EC%8B%9C%EB%8B%A4-1  

오늘은 PL/SQL 변수선언 및 사용방법에 대하여 정리하도록 하려했는데  PL/SQL 종류에 대한 설명이 부족한 것 같아 다시 정리해봅니다.  

1. PL/SQL 종류 

 PL/SQL 유형은 유형은 Anonymous Block(익명블록), Procedure(프로시저), Function(함수) 이렇게 3가지로 나뉩니다. 

-Anonymous Block : 이름없는 블록이라 불리며 간단한 block 수행 시 사용됩니다.

-Procedure :  지정된 특정처리를 실행하는 서브 프로그램의 한 유형으로서 단독으로 실행되거나 다른 프로시저나 다른 툴(Oracle Developer,sqlplus, sqlgate) 등에 호출되어 실행됩니다.

-Function : Procedure와 수행되는 결과가 유사하나 값 반환여부에 따라 차이가 있습니다. 

	Anonymous Block(익명블록)	Procedure(프로시저)	Function(함수)
객체저장	X	O	O
값 반환	X	X	O
이름지정여부	X	O	O
파라미터 사용여부	X	O	O
타 응용프로그래밍 호출여부	X	O	O

ㄱ . Anomynous block 

PL/SQL의 기본 형식은 DECLARE , BEGIN, END 형식으로 구성되어 있다. 

ㄴ.  Procedure 

ㄷ. Function

2. PL/SQL 구조 - 변수선언 

이번엔 PL/SQL 구조입니다. 

먼저 실행부에서 선언되는 변수의 종류를 살펴보도록 하겠습니다. 

변수의 종류는 일반변수, 상수, %TYPE, %ROWTYPE, 레코드(Record), 컬렉션 (Collection)등이 있습니다. 

ㄱ. 일반변수 

설명: 일반 프로그래밍 언어와 유사하나 변수명 데이터타입 순서대로 변수를 선업합니다. 

사용방법 : 변수명 데이터타입;

예제 : user_seq NUMBER ; 

ㄴ. 상수

설명 : 일반변수와 유사하나 CONSTANT라는 키워드가 붙고 선언시에 값을 할당해주어야 합니다. 

사용방법 : 변수명 CONSTANT 데이터타입 :=  값 ;

예제 : user_name CONSTANT VARCHAR2(20) := ' 나정이' ;

ㄷ. %TYPE 

설명 : PL/SQL은 데이터베이스 내의 데이터값을 가지고 프로그래밍하는 것이기 때문에 참조할 데이터 타입을 명확하게 써주지 않으면 에러가 발생됩니다. %TYPE을 통해 참조할 테이블 컬럼 데이터타입을 자동으로 가져와 이러한 번거로움을 덜어줍니다. 

사용방법 : 변수명 테이블.컬럼명%TYPE;

예제 : user_name  tb_user_m.user_name%TYPE; 

ㄹ. %ROWTYPE 

설명 : %TYPE과 유사하게 참조할 테이블의 컬럼 데이터 타입을 자동적으로 가져오나 1개의 컬럼이 아니라 여러 개의 컬럼값을 자동으로 가져오는 역할을 합니다. 

사용방법 : 변수명 테이블%ROWTYPE;

예제 : userInfo  tb_user_m%ROWTYPE; 

ㅁ. 레코드(Record) 

설명 : ROWTYPE이 참조할 테이블의 컬럼 데이터타입을 자동으로 가져오는 반면 RECORD는 직접적으로 컬럼타입을 셋팅해줄 수 있습니다. 

테이블 : tb_user_m 

사용방법 : TYPE 레코드 타입명 IS RECORD (변수명 변수타입 ... ); 

               레코드 객체 레코드 타입명 

예제 : 

ㅂ. 컬렉션(Collection)

 

설명 : 컬렉션은 배열과 같은 형태를 가지고 있으며, 컬렉션을 정의해서 생성한 뒤 이를 변수로 선언해서 사용이 가능합니다. 

         컬렉션의 종류는 varray, 중첩테이블, Associative array 이렇게 3 가지로 나뉩니다.

	Varray	중첩테이블	Associative array
형태	Array와 유사	Vector와 유사	HashMap과 유사
배열 크기 설정 유무	O	X	X
참조순서	순서대로	상관없음	상관없음

사용방법 : 

varray : TYPE 배열변수명 IS VARRAY OF 타입(정수) ;

중첩테이블 : TYPE 배열변수명 IS TABLE OF 타입(정수) ; 

Associative array : TYPE 배열변수명 IS TABLE OF 타입(정수) INDEX BY 키타입(정수) 

Associative array에 들어가는 키 데이터타입 유형은 2가지입니다. 

- 숫자일 경우 : BINARY INTEGER(이진정수로 처리) 또는 PLS_INTEGER(원시계산방식으로처리) 

- 문자일 경우 : VARCHAR2 또는 하위 유형중 하나 

예제 : 

2. PL/SQL 구조 - 실행부  

실제 처리할 로직을 담당하며 선언된 변수에 값을 할당하거나 IF,WHILE문등 프로그래밍에서 사용했던 로직, SQL문장을 사용하여 구현하는 부분입니다. 

ㄱ. IF 문 

처리조건이 1개일 경우	IF 조건 THEN     처리문; END IF ;
처리조건이 여러 개 일경우	IF 조건1 THEN    처리문1; ELSEIF 조건2 THEN    처리문2; ELSE    처리문n; END IF;

ㄴ. CASE 문( 자바의 switch 문와 유사 )

CASE grade

WHEN ‘A’ THEN

Dbms_ouput.putline(‘Excellent’);

WHEN ‘B’ THEN

Dbms_output.put_line(‘Good’);

ELSE

Dbms_output.put_line(‘NOT FOUND);

END CASE’

ㄷ. FOR LOOP문 

FOR 카운터 IN [REVERSE] 최소값 .. 최대값 LOOP

      처리문장들

END LOOP;

ㄹ,  WHILE 조건 LOOP문 

WHILE 조건 LOOP

처리문장 .. ;

END LOOP;

ㅁ. LOOP문 

END WHEN은 WHEN이 조건에 만족했을 때 루프를 빠져나가라는 의미입니다. 

LOOP 처리문장 ;

END WHEN 로직 ;

예제 

LOOP

FETCH C1 INTO rec3

DBMS_OUTPUT.put_line(rec3.user_name);

EXIT WHEN C1%NOTFOUND; 

ㅂ. NULL문 

PL/SQL에서 제공하는 NULL은 컬럼이나 변수에서 사용되는 것과 비슷한 개념으로 사용됩니다. 

CASE grade

WHEN ‘A’ THEN

Dbms_ouput.putline(‘Excellent’);

WHEN ‘B’ THEN

Dbms_output.put_line(‘Good’);

ELSE NULL

END CASE;

ㄹ.커서(Cursor) 

query에 의해 반환되는 결과가 메모리에 위치하게 되는데 pl/sql에서는 커서를 통하여 결과에 접근할 수 있습니다.

커서의 종류로는 묵시적 커서, 명시적 커서 이렇게 2가지로 존재합니다.

	묵시적 커서	명시적 커서
정의	쿼리 수행시 ROW수가 한 개만 반환하는 하는 모든 SQL문에 대한 접근	쿼리 수행시 ROW수가 여러 개를 반환하는 모든 SQL문에 대한 접근
문법사용여부	오라클 내부에서 지원하므로 커서 불필요	커서 사용

- 묵시적 커서 

묵시적 커서는 자동적으로 선언해주는 SQL커서로서 사용자 입장에서 생성유무를 알 수 없으며 SELECT, DML(INSERT, DELETE, UPDATE)문이 실행 될 때마다 묵시적 커서가 실행됩니다. 

-명시적 커서 

명시적 커서는 사용자가 선언하여 생성 후 사용하는 커서로 주로 커서를 사용했다고 하는경우는 명시적 커서를 의미한다구 합니다.  

3. PL/SQL 구조 - 예외처리     

PL/SQL에서는 실행문에서 발생한 에러를 오라클 PL/SQL엔진이 오류를 발생시켜 잡을 수 있습니다. 

예외처리 기본문법은 아래와 같으며 예외의 종류는 시스템 오류 예외처리와 프로그래머 정의 예외처리 2가지로 나뉩니다. 

EXCEPTION WHEN 예외1 THEN 예외처리1

                WHEN 예외2 THEN 예외처리2

    .…..        WHEN OTHERS THEN 예외처리

ㄱ. 시스템 오류 ( EX 메모리 초과, 인덱스 중복 키.... ) : 오라클이 정의하는 에러로 보통 PL/SQL 실행 엔진이 오류조건을 탐지하여 발생하는 예외입니다. Exception 이름을 알 경우와 모를 경우에 대하여 나눠서 사용하는 방법은 다릅니다. 

-Exception 이름을 알 경우 

오라클에서 미리 정의 해놓은 Exception 

ACCESS_INTO_NULL	초기화되지 않은 오브젝트에 값을 할당하려하려고 할때
CASE_NOT_FOUND	CASE문장에서 ELSE구문도 없고 WHEN절에 명시된 조건을 만족하는 것이 없을 경우
COLLECTION_IS_NULL	초기화되지 않은 중첩 테이블이나 VARRAY같은 컬렉션을 EXISTS외의 다른 메소드로 접근을 시도할 경우
CURSOR_ALREADY_OPEN	이미 오픈된 커서를 다시 오픈하려고 시도하는 경우
DUP_VAL_ON_INDEX	유일 인덱스가 걸린 컬럼에 중복 데이터를 입력할 경우
INVALID_CURSOR	허용되지 않은 커서에 접근할 경우 (OPEN되지 않은 커서를 닫으려고 할 경우)
INVALID_NUMBER	SQL문장에서 문자형 데이터를 숫자형으로 변환할때 제대로 된 숫자로 변환되지않을 경우
LOGIN_DENIED	잘못된 사용자가 비밀번호로  로그인을 시도할 때
NO_DATA_FOUND	SELECT INTO 문장의 결과로 선택된 로우가 하나도 없을 경우

- Exception 이름을 모를 경우 

오라클에서 지정한 Exception 이름을 모를 경우에는 PRAGMA을  Exception_init 정의하여 사용합니다. 

EXCEPTION_INIT은 컴파일 시간 명령어 또는 예외명과 내부 오류코드를 연관 짓는 프라그마로 컴파일러에게 exception으로 선언된 식별자를 지정된 오류번호와 연관짓게 합니다. 

사용방법 : PRAGMA EXCEPTION_INIT (변수명, 에러코드); 

ㄴ. 프로그래머 정의 예외 

프로그래머가 정의하는 예외로 오라클 지정한수 RAISE_APPLICATION_ERROR를 사용하여 오류코드 (- 20000) ~ (-20999)의 범위 내에서 사용자 정의 예외를 만들거나 RAISE를 발생 시킬 수 있습니다. 

사용방법 : RAISE_APPLICATION_ERROR(에러코드,메세지); 

               RAISE 예외 ;