[Oracle] PL/SQL(선언부)

PL/SQL이란?

PL/SQL은 스토어드 프로시저(stored procedure)를 의미하며 프로그래밍 언어로 프로그램을 작성하는 것처럼  SQL문을 기술할 수 있다.

그래서 그냥 SQL을 기술하는 것보다 훨씬 복잡한 처리를 기술할 수 있다.

 

또한 기술한 PL/SQL은 데이터베이스 내에 컴파일된 상태로 놓이기 때문에 실행 시 처리 속도가 빠르다.

실행할 때는 그 PL/SQL을 호출하기만 하면 되기 때문에 한번 작성해놓으면 실행이 매우 쉽다.

또한 네트워크에 거는 부하를 줄일 수 있다.

 

■ PL/SQL 기본 구성

PL/SQL은 아래와 같이 구성을 가진다.

- PL/SQL 구문 구성

DECLARE
  선언부;
BEGIN
  실행부;
EXCEPTION
  예외처리부;
END;

기본 구성
선언부(delcare)	변수・정수・커서를 선언
실행부(begin)	처리를 기술(필수항목임)
예외처리부(exception)	실행부에서 에러가 발생했을 경우의 처리를 기술

 

- PL/SQL 프로그램의 예제

: 이 예제에서는 'INSERT INT O test_tab VALUSE(1);'라고 하는 sql문을 PL/SQL 루프 처리로 10번 반복해서 실행하는 것이다.

이와 같이 sql에서는 다소 번거롭고 어려운 처리 혹은 비슷한 처리를 여러번 해야하는 경우에 PL/SQL을 통해 간단히 구현할 수 있다.

BEGIN
  FOR i IN 1..10 LOOP　　/* 루프 처리의 시작점. 'END LOOP'까지의 처리를 10회 반복한다 */
    INSERT INTO test_tab VALUES(1);　　/* test_tab 테이블에 '1' 삽입 */
  END LOOP;　　/* 루프 처리의 시작 지점으로 돌아가기 */
END;

---

■ 선언부

선언부에서는 PL/SQL 실행부에서 사용할 변수의 이름과 데이터형을 선언한다. 

(C나 JAVA등의 언어로 하는 변수의 선언과 동일)

필수 요소는 아니다. 이름을 정의하고 재 사용할 PL/SQL 블록을 만들기 위해서는 CREATE구문을 사용해야 한다.

 

※PL/SQL에서 사용하는 변수의 데이터형은 CREATE TABLE 등에서 사용하는 데이터형과 조금 다른 부분이 있으니 주의!

 

데이터형
스칼라형(scalar data type) : 프로그래밍 언어에서 여러 값 사이에 순서가 존재하는 데이터 유형	NUMBER	숫자형 [구문] 변수명 NUMBER [:= 초기값] [NOT NULL]
	CHAR	문자형 [구문] 변수명 CHAR [:= 초기값] [NOT NULL]
	NCHAR	문자형 (Unicode) [구문] 변수명 NCHAR [:= 초기값] [NOT NULL]
	VARCHAR2	문자열형 [구문] 변수명 VARCHAR2 [:= 초기값] [NOT NULL]
	NVARCHAR2	문자열형 (Unicode) [구문] 변수명 NVARCHAR2 [:= 초기값] [NOT NULL]
	DATE	날짜형 [구문] 변수명 DATE [:= 초기값] [NOT NULL]
	TIMESTAMP	타임스탬프형 [구문] 변수명 TIMESTAMP [:= 초기값] [NOT NULL]
	BOOLEAN	boolean형 [구문] 변수명 BOOLEAN [:= 초기값] [NOT NULL]
	BYNARY_INTEGER	NUMBER형보다 메모리 소비량이 적음. [구문] 변수명 BINARY_INTEGER [:= 초기값] [NOT NULL]
	PLS_INTEGER	BYNARY_INTEGER형보다 고속 [구문] 변수명 PLS_INTEGER [:= 초기값] [NOT NULL]
참조형(reference data type)	테이블명.컬럼명%TYPE	기존의 컬럼의 타입을 참조. [구문] 변수명 테이블명/컬럼명%TYPE
	테이블명%ROWTYPE	기존의 테이블과 동일한 레코드를 정의 [구문] 변수명 테잉블명%ROWTYPE
컬렉션형(collection data type)	RECORD	필드가 모여서 구성되어 있는 데이터형/구조체/레코드 [구문] TYPE 레코드명 IS RECORD(           필드명1 데이터형 [:= 초기값],           필드명2 데이터형 [:= 초기값], ) 변수명 레코드명;
	TABLE	연관 배열/ 배열 [구문] TYPE 연관배열명 IS TABLE OF 데이터타입[NOT NULL] INDEX BY BINARY_INTEGER; 변수명 연관배열명;
	레코드의 연관배열명	레코드의 배열 레코드와 연관배열을 조합한 데이터형 [구문] TYPE 레코드의 연관배열명 IS TABLE OF 레코드형 INDEX BY BINARY_INTEGER; 변수명 레코드의 연관배열명; ※ 레코드형은 RECORD에서 만든 데이터형이든 %ROWTYPE이든 가능
	NESTED TABLE
	VARRAY	가변 길이 배열(상한치 있음)
LOB형	CLOB	4GB까지의 덱스트데이터(싱글바이트/멀티바이트)
	NCLOB	4GM까지의 텍스트데이터(Unicode)
	BLOB	4GB까지의 이진 데이터
	BFILE	4GB까지의 이진 데이터(외부 파일에 저장)
예외형	EXCEPTION	EXCEPTION을 사용자 정의한다. [구문] 예외명 EXCEPTION 정의한 예외로 에러코드를 설정하는 경우 [구문] PRAGMA EXCEPTION_INIT (예외명, 에러코드); ※ 정의할 수 있는 오류코드의 범위는 -20000~-20999까지

 

<정수>

변수 선언 시 「CONSTANT」라는 키워드를 사용하면 해당 변수는 정수로 취급되며 저장된 값을 변경할 수 없게 된다.

정수를 선언함으로써 부주의하게 값이 바뀌어버리는 것을 방지한다.

정수는 평소에는 고정값이지만 언젠가 값이 변경될 때만 경우에 사용된다.

 

정수의 선언 예제
NUMBER	정수명 COSTANT NUMBER(n) := 숫자;
CHAR	정수명 CONSTANT CHAR(n) := 문자;
VARCHAR2	정수명 CONSTANT VARCHAR2(n) := 문자열;
DATE	정수명 CONSTANT DATE := 날짜;

--NUMBER형 정수 KINGAKU_500（값：500）를 선언
KINGAKU_500 CONSTANT NUMBER(3,0) := 500;

--CHAR형 정수 DELFLG_DELETE（값：'1'）를 선언
DELFLG_DELETE CONSTANT CHAR(1) := '1';

--DATE형 정수 DATE_TODAY（값：sysdate）를 선언
DATE_TODAY CONSTANT DATE := sysdate;

 

<커서>

커서란 여러 행이 반환되는 SELECT문을 실행한 경우에 그 결과 중에서 1행씩 꺼내어 사용하고 싶은 경우에 사용한다.

(JAVA에서 말하는 Result Set 같은 것)

커서 선언 예
CURSOR	커서를 정의. [구문] CURSOR 커서명 IS <SELECT문 ~> 변수를 정의하고 커서를 OPEN할 때 WHERE 구문 등에 값을 지정할 수 있다. [구문] CURSOR 커서명(변수명) IS <SELECT문 ~>

CURSOR cursor1 IS
SELECT col1, col2, col3
FROM test_table
WHERE create_date >= sysdate;