[Oracle] CH1, 2 : PL/SQL 블록, 변수와 상수

PL/SQL 특징

- 전차 처리 실행이 가능하기 때문에 SQL 단문으로는 하기 어려운 복잡한 처리 실행 가능

- Oracle에서 사용하는 모든 SQL 데이터 유형을 지원하기 때문에 데이터 타입 변환이 불필요해 Oracle과의 친화력이 좋음

- Windows나 Linux 등 OS가 다른 경우라도 Oracle이 동작하는 모든 환경에서 그대로 사용 가능

- 다른 언어로 SQL을 실행하는 경우, Middleware를 통해 Oracle에 SQL을 문장 단위로 전송하여 많은 SQL을 발행하는 프로그램은 프로세스 간 통신과 네트워크에 걸리는 부하가 크지만, PL/SQL은 블록 내에 작성된 SQL을 포함한 프로그램 단위로 Oracle에 일괄 전송하여 처리하기 때문에 프로세스 간 통신과 네트워크 트래픽 감소호 송수신에 걸리는 부하를 줄일 수 있음

- PL/SQL 블록을 Compile 된 상태로 Oracle에 저장(Sotred Subprogram)하고, Stored 서브 프로그램 실행은 프로그램을 송신할 필요 없이 이름을 지정하여 호출로도 가능하고 분석이 끝난 상태로 저장되어 있기 때문에 실행할 때마다 분석할 필요가 없어서 분석 부하 및 네트워크 부하를 줄일 수 있음

- Stored 서브 프로그램은 여러 사용자 간에 동일한 프로그램을 공유하여 사용할 수 있음

 

PL/SQL은 블록 구조화 언어

PL/SQL로 작성한 프로그램(블록)

- 이름 없는 PL/SQL 블록: 애플리케이션 응용 프로그램 쪽에서 PL/SQL 블록을 송신하여 실행

- Stored 서브 프로그램(이름 지정된 PL/SQL 블록) : 분석된 상태에서 Oracle에 저장하여 실행

 

 

PL/SQL 블록의 구조

DECLARE
        선언부 : 프로그램에서 사용하는 객체(Object)(변수 등)를 정의(선언)
BEGIN
        실행부 : 프로그램의 구체적인 실행 내용을 작성(필수)
EXCEPTION
        예외 처리부 : 예외(실행 시의 오류나 경고) 처리 방법을 작성
END;

굳이 객체를 정의하거나 예외 처리를 지정할 필요가 없는 경우 선언부와 예외 처리부를 생략할 수 있음 == BEGIN, END;는 필수

PL/SQL 블록은 중첩 가능 -> 중첩되어 있어도 논리적으로는 하나의 블록으로 처리

 

---

 

변수

 

* PL/SQL에서 변수ㅡ 전방 참조 불가능

 

DECLARE
	var NUMBER;
BEGIN
	var := 10;
    DBMS_OUTPUT.PUT_LINE(var);
    var := var + 10;
    DBMS_OUTPUT.PUT_LINE(var);
END;
/
---------------------------------
10
20

 

-- 변수 정의
-- <변수명> <데이터 타입> [NOT NULL] [{:= | DEFAULT } <값> ];
var VARCHAR2(10) DEFAULT 'SCOTT';


-- 값 대입
-- <변수> := <값 또는 계산식>
DECLARE
	var NUMBER(5);
    var2 VARCHAR2(10) DEFAULT 'SCOTT';
BEGIN
	var := 10;
    DBMS_OUTPUT.PUT_LINE(var);
    DBMS_OUTPUT.PUT_LINE(var2);
END;
/

-- ----------------------------------------
10
SCOTT

 

변수에 사용 가능한 데이터 타입 - Scalar Type, Composite Type, 참조 타입

 

스칼라 타입

- 숫자나 문자열 등 그 속성이 oracle에 의해 사전 정의된 데이터 타입

속성	데이터 타입	역할	비고
수치형	NUMBER	최대 38자리의 숫자 데이터를 저장	소수점 이하 자리수도 가능. 또한, 값의 범위(최대 Size)를 지정하지 않은 경우 최대 38자리 또는 시스템이 지원하는 최대값이 적용
문자형	CHAR	고정된 길이의 문자 데이터 저장. CHAR(N)의 N에 저장할 수 있는 최대 바이트 크기 지정(최대값은 32,767Bytes)	최대 Byte 크기를 지정하지 않으면 기본값 1 Byte
	VARCHAR2	가변적 길이의 문자 데이터를 저장. 저장할 수 있는 최대값 32,767Bytes	최대 크기 지정을 생략할 수 없음
날짜형	DATE	연, 월, 일, 시, 분, 초 데이터를 고유 형식인 7Bytes의 고정 길이로 저장	기원전 4712년 1월 1일부터 서기 9999년 12월 31일까지의 날짜 처리 가능
Boolean형	Boolean	논리 값인 TRUE, FALSE, NULL을 다루는 데이터 타입	PL/SQL 자체 데이터 타입으로 TRUE나 FALSE를 데이터베이스 열에 삽입하거나 데이터베이스 열의 값을 BOOLEAN형의 변수에 저장할 수 없음

 

콤포지트 타입

- 사용자가 사전에 필수로 데이터 타입을 정의해야 함

- 여러 개의 값을 보유할 수 있음

속성	데이터	역할	비고
레코드	RECORD	RECORD 타입으로 정의된 변수는 테이블의 행과 같이 여러 개의 필드를 한 번에 소유할 수 있음	각 필드의 이름, 데이터 타입은 사용자가 자유로이 정의할 수 있음
컬렉션	TABLE	TABLE 타입으로 정의된 변수는 테이블의 열처럼 동일한 열에 여러 개의 값을 보유할 수 있음	TABLE 타입은 요소 및 인덱스로 구성되며, 정의된 변수를 결합 배열이라고 함
중첩 테이블
VARRAY

 

참조 타입

- 사용자가 사전에 데이터 타입을 정의해야 함

- 참조 타입으로 정의된 변수는 처리 대상 데이터를 참조하는 것이 가능

속성	데이터 타입	역할	비고
참조 타입	REF CURSOR	REF CURSOR 타입으로 정의된 변수는 처리 대상 데이터를 참조 가능	REF CURSOR 타입으로 정의된 변수를 커서 변수라고 함

레코드, 컬렉션, 커서 관련 글 https://devvy-is-free.tistory.com/5

 

---

%TYPE과 %ROWTYPE 속성

 

데이터 타입을 직접 지정하는 것이 아니라 Oracle의 칼럼 데이터 타입이나 이미 정의되어 있는 변수의 데이터 타입 참조

DECLARE
	var dept.deptno%TYPE
BEGIN
	SELECT deptno INTO var FROM dept WHERE loc = 'NEW YORK';
    DBMS_OUTPUT.PUT_LINE(var);
END;
/

-- ------------------------------
PL/SQL 처리가 정상적으로 완료되었습니다.

 

%TYPE과 %ROWTYPE의 장점

- 컬럼 정의가 변경되어도 소스 코드의 수정이 불필요

- 컬럼 정의를 정확히 몰라도 변수를 정의할 수 있음

 

%TYPE

<변수명> <테이블명>.<컬럼명>%TYPE;

- 특정 테이블의 컬럼 또는 미리 정의된 변수의 데이터 타입 및 사이즈를 참조

- 일반 변수와 동일하게 대입 연산자(:=) 또는 DEFAULT 키워드를 사용해 초기 값 지정 가능

 

%ROWTYPE

<변수명> <테이블명>%ROWTYPE;

- 테이블(또는 뷰)의 행 구조를 참조

- 한 개의 변수에는 각 컬럼의 데이터를 저장하기 위한 행 구조와 같은 수의 영역이 확보됨

- 각 필드의 이름과 데이터 타입은 각 컬럼의 이름, 데이터 타입을 그대로 참조

- 행 전체의 데이터를 처리하는 경우에 편리

- 초기값을 지정할 수 없음 -값을 모든 필드에 반드시 대입해야 함 (꺼낼 컬럼의 개수와 변수의 필드 수가 일치해야 함)

 

dept table

deptno	dname	loc
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO

DECLARE
	d_row dept%ROWTYPE;
BEGIN
	SELECT deptno, dname, loc INTO d_row FROM dept WHERE dept = 10;
    DBMS_OUTPUT.PUT_LINE(d_row.dname);
END;
/

-- -------------------------------------------------
ACCOUNTING

---

상수

 

상수 사용 시, 선언부에 항목 정의

- 사용할 상수의 이름

- 저장되는 값의 데이터 타입(필요에 따라 최대 사이즈도 설정)

- 상수 이름과 데이터 타입의 지정 사이에 CONSTANT 키워드 삽입

- 초기값

 

변수와 달리 상수를 정의할 때는 반드시 대입 연산자(:=) 또는 DEFAULT 키워드로 초기값 지정해야 함

- 상수 정의 후에 값을 대입하는 건 불가능

 

<상수명> CONSTANT <데이터 타입> [NOT NULL] {:= DEFAULT} <초기값>;

DECLARE
	const_num CONSTANT NUMBER(8) := 5;
BEGIN
	DBMS_OUTPUT.PUT_LINE(const_num);
END;
/
-- ----------------
PL/SQL 처리가 정상적으로 완료되었습니다.

---

식별자

 

유효 범위

- 식별자가 선언된 블록 안(중첩된 서브 블록 포함)에서만 사용할 수 있음

 

명명 규칙

- 식별자를 정의할 때는 명명 규칙에 따름

- 대소문자 구별 X

- 같은 블록 안에 여러 개의 동일한 식별자 정의 불가능

- 이름의 길이는 최대 30 Bytes

- 하이픈(-), 슬래시(/), 앰퍼샌드(&), 공백은 사용 불가

- SELECT, BEGIN 등과 같은 Oracle 예약어는 지정 불가

 

 

참조 : 책 '전문가를 위한 오라클 PL/SQL 입문'