[DBP] CH3. PL/SQL
🍀 PL/SQL
개요 및 특징
- Oracle에서 제공하는 SQL을 확장한 절차적 프로그래밍 언어
- SQL 문장에서 변수 정의, 조건 처리, 반복 처리 등 프로그래밍 언어가 보이는 제어 구조 제공
- SQL과 프로그래밍 언어의 통합 접근 중 한 형태
- ESQL, SQLJ 등 기술과 달리, 상용 프로그래밍 언어가 아닌 Oracle 자체적인 프로그래밍 언어 사용
엔진 실행 구조
언어 구조
- 블록 단위 구조
- 임의의 수의 중첩된 하위 블록 포함
- 선언부
DECLARE, 실행부BEGIN ... END, 예외처리부EXCEPTION으로 구성
DECLARE -- 선택
-- 변수, 상수, 커서, 사용자 지정 예외 선언
BEGIN -- 필수
-- SQL 문장
-- PL/SQL 제어 문장
EXCEPTION -- 선택
-- 에러 발생시 수행될 액션
END; -- 필수
블록 유형
- 익명 (Anonymous)
- 이름이 없는 PL/SQL 블록
- 저장 프로시저 (Stored Procedure) 및 함수 (Function)
- 매개 변수를 받을 수 있고, 반복해서 사용할 수 있는 이름이 있는 PL/SQL 블록
- 패키지 (Package)
- 관련된 저장 프로시저, 함수를 모은 이름이 있는 PL/SQL 블록
- 트리거 (Trigger)
- 데이터베이스의 테이블과 연결되어 자동적으로 실행되는 이름이 있는 PL/SQL 블록
식별자 (Identifiers)
- 첫 자리는 알파벳으로 시작
- 총 자릿수는 30자 이내
- 특수문자 사용 가능 (&, -, /, space) 제외
- 대소문자 구별 X
연산자
| 연산자 | 설명 |
|---|---|
| +, -, *, /, ** | 덧셈, 뺄셈, 곱셈, 나눗셈, 지수 연산자 |
| =, <, >, <>, !=, <=, >= | 관계 연산자 |
| (, ) | 식 또는 리스트 구분자 |
| ; | 문장 끝마침 구분자 |
| % | 속성 인자 |
| , | 아이템 또는 문자열 구분자 |
| := | 지정(assignment) 연산자 |
| .. | 범위(range) 연산자 |
| || | 문자열 연산자 |
| – | 주석 연산자(한 라인 이하) |
| /* */ | 주석 연산자(복수 라인) |
리터럴
- 숫자
- 정수의 일반 숫자 값 (123, -4.6)
- 지수로 표현된 숫자 값 (1.43E5)
- 문자
- 작은 따옴표로 구분된 문자열 (‘STRING’)
- NULL 값 표현 가능 (‘’)
- 불리언
- TRUE, FALSE, NULL
주석 (Comment)
/* */: 한 줄 이상의 줄 주석--: 한 줄 단위의 주석
🍀 PL/SQL 사용
- SQL*PLUS에서 SQL 언어를 작성하는 것과 유사하게 작성
SQL>프롬프트 상에서 사용
- 대개의 경우 익명 블록 형태가 아닌 저장 프로시저, 함수, 트리거 등의 형태로 사용
🍀 PL/SQL 결과 출력
PL/SQL의 결과를 확인하기 위해 출력하는 방법
- `Set ServerOutput On;`
- `DBMS_OUTPUT.PUT_LINE(...)`
출력 방법
SQL> Set ServerOutput On;
SQL>
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO');
END;
/
HELLO
🍀 PL/SQL 데이터 타입
개요
- SQL에서 제공하는 모든 데이터 타입 + 추가 데이터 타입 제공
- 추가 데이터 타입
- BOOLEAN
- BINARY_INTEGER(부호 있는 정수), NATURAL(자연수), POSITIVE(양수)
- %TYPE
- %ROWTYPE
- PL/SQL 테이블과 레코드
- 데이터 타입 유형
- 스칼라(Scalar) 타입
- 복합(Composite) 타입
- 참조 타입
변수 선언
- 형식
식별자 [CONSTANT] 데이터타입 [NOT NULL] [ := 상수값이나 표현식];
- 식별자: 변수나 상수의 이름
- CONSTANT: 식별자가 그 값이 변할 수 없도록 선언, 반드시 초기화
- 데이터 타입: 스칼라 또는 복합 데이터 타입 선언시 사용
- NOT NULL: NOT NULL로 제한된 변수는 반드시 초기화 필요
- 초기값을 정의하지 않으면 식별자는
NULL값을 가짐
스칼라 데이터 타입
- 단수 데이터 형으로 하나의 데이터 값을 저장하는 데이터 타입
| 데이터 타입 | 설명 |
|---|---|
| BINARY_INTEGER | -2147483647, 2147483647 사이의 정수, 디폴트 값 = 1 |
| NUMBER[(p,s)] | 정수와 실수 |
| CHAR[(최대길이)] | 32767바이트까지의 고정 길이 문자 최대길이 미지정시 디폴트 길이는 1 |
| LONG | 32760바이트까지의 가변 길이 문자열 |
| VARCHAR2(최대길이) | 32767바이트까지의 가변 길이 문자 데이터 |
| DATE | 날짜와 시간 데이터 |
| BOOLEAN | TRUE, FALSE, NULL 중 한 가지 값 저장 |
- ex)
v_gender CHAR(1); v_count BINARY_INTEGER := 0; v_total_sal NUMBER(9, 2) := 0; v_order_date DATE := SYSDATE + 7; c_tax_rate CONSTANT NUMBER(3, 2) := 8.25; v_valid BOOLEAN NOT NULL := TRUE;
%TYPE
- 데이터베이스 테이블의 컬럼 데이터 타입을 모를 경우 사용
- 데이터베이스 컬럼의 데이터 타입이 변경될 경우 다시 수정할 필요 없음
- 이미 선언된 다른 변수나 데이터베이스 컬럼의 데이터 타입을 이용하여 선언
%TYPE앞에 올 수 있는 것은 데이터베이스 테이블과 컬럼, 이미 선언한 변수명- ex)
v_empno emp.empno%TYPE := 7900;- v_empno의 데이터 타입: emp 테이블의 empno 컬럼의 데이터타입
v_ename emp.ename%TYPE;- v_ename의 데이터 타입: emp 테이블의 ename 컬럼의 데이터 타입
- 예시)
DECLARE v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; v_sal emp.sal%TYPE; BEGIN SELECT empno, ename, sal INTO v_empno, v_ename, v_sal FROM emp WHERE empno = p_empno; DBMS_OUTPUT.PUT_LINE('사원번호: '||v_empno); DBMS_OUTPUT.PUT_LINE('사원이름: '||v_ename); DBMS_OUTPUT.PUT_LINE('사원급여: '||v_sal); END;
복합 데이터 타입
- 하나 이상의 데이터 값을 가지는 데이터 타입
- PL/SQL 테이블과 레코드, %ROWTYPE
%ROWTYPE
- 특징
- 테이블이나 뷰 내부의 컬럼 집합의 이름, 데이터 타입, 크기, 속성을 그대로 사용하여 선언
%ROWTYPE앞에 오는 것은 테이블명
- 장점
- 데이터베이스의 컬럼들의 수나 데이터 타입을 모를 때 편리
- 해당 데이터베이스 컬럼들의 수나 데이터 타입이 변경될 경우 수정하지 않아도 됨
- SELECT 문을 이용하여 하나의 행 조회할 때 편리
- 예시)
DECLARE v_emp emp%ROWTYPE; BEGIN SELECT empno, ename; INTO v_emp.empno, v_emp.ename FROM emp WHERE empno = 7844; DBMS_OUTPUT.PUT_LINE('번호: '||v_emp.empno); DBMS_OUTPUT.PUT_LINE('이름: '||v_emp.ename); END;
PL/SQL 테이블
- 특징
- 일차원 배열과 유사
- 테이블 크기는 제한이 없으며, ROW의 수는 데이터가 들어옴에 따라 자동 증가
- 형식
TYPE type_name IS TABLE OF datatype [NOT NULL] [INDEX BY BINARY_INTEGER]; Identifier type_name;
- 예시)
DECLARE TYPE empno_table IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER; TYPE ename_table IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; empno_tab empno_table; ename_tab ename_table; i BINARY_INTEGER := 0; BEGIN FOR emp_list IN (SELECT empno, ename FROM emp WHERE deptno = 20) LOOP i := i + 1; empno_tab(i) := emp_list.empno; ename_tab(i) := emp_list.ename; DBMS_OUTPUT.PUT_LINE('번호: '||empno_tab(i)); DBMS_OUTPUT.PUT_LINE('이름: '||ename_tab(i)); END LOOP; END;
- 메소드
- COUNT
- PL/SQL 테이블의 전체 행 수 반환
- ex)
i := ename_tab.COUNT;: ename_tab에 저장된 전체 데이터 행의 수
- DELETE
- PL/SQL 테이블의 특정 행 삭제
- ex1)
ename_tab.DELETE(3);: ename_tab의 인덱스가 3인 행 삭제 - ex2)
ename_tab.DELETE;: ename_tab의 전체 행 삭제 - ex3)
ename_tab.DELETE(2,3);: ename_tab의 2~3행 삭제
- EXISTS
- PL/SQL 테이블의 특정 행이 존재하면
TRUE, 존재하지 않으면FALSE반환 - ex) ename_tab에 1행이 존재하는지 검사
IF ename_tab.EXISTS(1) THEN DBMS_OUTPUT.PUT_LINE('ename_tab(1) exists'); ELSE DBMS_OUTPUT.PUT_LINE('ename_tab(1) does not exist'); END IF
- PL/SQL 테이블의 특정 행이 존재하면
- FIRST, LAST
FIRST는 PL/SQL 테이블의 첫번째 행 인덱스LAST는 마지막 행의 인덱스 번호 반환 (인덱스는 0부터 시작하지 않아도 됨)- ex)
ename_tab(70) := '이름1'; ename_tab(75) := '이름2'; ename_tab(73) := '이름3'; -- 인덱스 번호 70을 v_index_first에 지정 v_index_first := ename_tab.FIRST; -- 70 -- 인덱스 번호 75를 v_index_last에 지정 v_index_last := ename_tab.LAST; -- 75
- NEXT, PRIOR
NEXT는 PL/SQL 테이블의 다음 행의 인덱스PRIOR는 이전 행의 인덱스 번호 반환 (순서대로 되어있지 않을 수 있음)- ex)
-- 첫번째 인덱스 지정 i := ename_tab.FIRST; WHILE i IS NOT NULL LOOP -- do something with ename_tab(i) -- 현재 인덱스 번호의 다음 인덱스 번호 지정 i := ename_tab.NEXT(i); -- i는 현재 인덱스 번호 END LOOP;
- COUNT
PL/SQL 레코드
- 특징
- 사용자 정의 레코드
- 여러 개의 데이터 타입을 가지는 변수들의 집합
- 데이터 처리를 위해 테이블에서 하나의 행씩 FETCH할 때 편리
- 개별 필드 이름 부여가 가능하고, 타입 선언시 초기화 가능
-
형식
TYPE type_name IS RECORD (필드이름1 필드타입 [NOT NULL {:=|DEFAULT} 식], 필드이름2 필드타입 [NOT NULL {:=|DEFAULT} 식], ...); Identifier type_name; -
ex)
DECLARE TYPE emp_record IS RECORD (v_empno NUMBER, v_ename VARCHAR2(30)); emp_rec emp_record; BEGIN SELECT empno, ename INTO emp_rec.v_empno, emp_rec.v_ename FROM emp WHERE empno = 1; DBMS_OUTPUT.PUT_LINE('번호: '||emp_rec.v_empno); DBMS_OUTPUT.PUT_LINE('이름: '||emp_rec.v_ename); END;
🍀 PL/SQL 제어 구조
개요
- 조건 제어
IF-THEN-ELSE
- 반복 제어
LOOP,FOR-LOOP,WHILE-LOOP
- 순차 제어
GOTO,NULL
조건 제어
- 형식
IF condition THEN statements; [ELSEIF condition THEN statements;] [ELSE statements;] END IF; - ex)
DECLARE sMonth CHAR(2); BEGIN SELECT TO_CHAR(SYSDATE, 'MM') INTO sMonth FROM DUAL; IF (sMonth >= '03' and sMonth <= '08') THEN DBMS_OUTPUT.PUT_LINE('1학기'); ELSE DBMS_OUTPUT.PUT_LINE('2학기'); END IF; END;
반복 제어
- LOOP 형식
LOOP statements; ... EXIT [WHEN condition]; END LOOP; - ex)
DECLARE i NUMBER := 0; nSum NUMBER := 0; BEGIN LOOP i := i + 1; nSum := nSum + i; EXIT WHEN i >= 100; END LOOP; DBMS_OUTPUT.PUT_LINE('1~100까지의 합: '||TO_CHAR(nSum)); END;
- FOR-LOOP 형식
FOR index IN [REVERSE] 시작값..끝값 LOOP statements; ... END LOOP; - ex)
DECLARE i NUMBER := 0; nSum NUMBER := 0; BEGIN FOR i IN 1..100 LOOP nSum := nSum + i; END LOOP; DBMS_OUTPUT.PUT_LINE('1~100까지의 합: '||TO_CHAR(nSum)); END;
- WHILE-LOOP 형식
WHILE condition LOOP statements; ... END LOOP; - ex)
DECLARE i NUMBER := 0; nSum NUMBER := 0; BEGIN WHILE i < 100 LOOP i := i + 1; nSum := nSum + i; END LOOP; DBMS_OUTPUT.PUT_LINE('1~100까지의 합: '||TO_CHAR(nSum)); END;
순차 제어
- GOTO
- 제어가 건너뛰는 곳을 지정하는 레이블이 함께 쓰임
- ex)
DECLARE i NUMBER; BEGIN FOR i IN 1..50 LOOP IF i = 30 THEN GOTO my_label; END IF; END LOOP; <<my_label>> DBMS_OUTPUT.PUT_LINE('i = 30'); END;
- NULL
- 실행하지 않음을 나타냄
- ex)
DECLARE i NUMBER := 0; nValue NUMBER := 0; BEGIN FOR i IN 0..100 LOOP nValue := 1000/i; END LOOP; EXCEPTION WHEN ZERO_DIVIDE THEN NULL; END;