프로시저 작성을 위해 PL/SQL을 사용하게 되면서 갓대희님의 블로그를 참고하며 공부를 했다.
공부한 내용을 까먹지 않기 위해 블로그에 정리해보고자 한다.
우선 PL/SQL의 기본적인 정의를 정리해보자.
PL/SQL
- SQL을 확장한 절차적 언어(Procedural Language)
- Oracle의 표준 데이터 엑세스 언어로, 프로시저 생성자를 SQL과 완벽하게 통합
- 유저 프로세스가 PL/SQL 블록을 보내면, 서버 프로세서는 PL/SQL Engine에서 해당 블록을 받고 SQL과 Procedural를 나눠서 SQL은 SQL Statement Executer로 보냄
- PL/SQL 프로그램의 종료는 크게 Procedure, Function, Trigger로 나뉨
- SQL에서는 사용할 수 없는 절차적 프로그래밍의 기능을 가지고 있어 SQL의 단점 보완
위와 같은 정의를 가지는 PL/SQL의 기본적인 특징도 정리해보자.
PL/SQL의 기본 특징
- 블록 단위 실행 제공
- 이를 위해 BEGIN, END; 사용
- 마지막 라인에 /를 입력하면 해당 블록 실행
- 변수, 상수 등을 선언하여 SQL과 절차형 언어에서 사용
- 변수의 선언은 DECLARE 절에서만 가능
- BEGIN 섹션에서 새 값 할당 가능
- IF문을 통해 조건에 따라 문장들 분기 가능
- LOOP문을 사용하여 문장 반복가능
- 커서를 사용하여 여러 행 검색 및 처리
- PL/SQL에서 사용 가능한 SQL은 Query, DML, TCL
- DDL(CREATE, DROP, ALTER, TRUNCATE…), DCL(GRANT, REVOKE) 명령어는 동적 SQL을 이용할 때만 사용 가능
- PL/SQL의 SELECT 문은 결과를 PL/SQL Engine으로 보냄
- 이를 캐치하기 위한 변수를 DECLARE해야 하고, SELECT 문장은 반드시 한 개의 행이 검색되어야 함
- 그리고 이를 위해 INTO 절을 꼭 사용해야 함
- 검색되는 행이 없으면 문제 발생
PL/SQL Block의 기본 구조
| 영역 | 설명 | 필수 여부 |
| DECLARE (선언부) |
PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분 DECLARE로 시작 |
옵션 |
| BEGIN (실행부) |
절차적 형식으로 SQL문을 실행할 수 있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등 로직을 기술할 수 있는 부분 BEGIN으로 시작 |
필수 |
| EXCEPTION (예외 처리부) |
PL/SQL문이 실행되는 중에 에러가 발생할 수 있는데 이를 예외 사항이라고 함 이러한 예외 사항이 발생했을 때 이를 해결하기 위한 문장을 기술하는 부분 |
옵션 |
| END (실행문 종료) |
필수 |
PL/SQL Block의 종류
- 익명 블록 : 이름이 없는 PL/SQL Block
- 이름 있는 블록 : DB의 객체로 저장되는 블록
- 프로시저 : 리턴 값을 하나 이상 가질 수 있는 프로그램
- 함수 : 리턴 값을 반드시 반환해야 하는 프로그램
- 패키지 : 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음
- 트리거 : 지정된 이벤트가 발생하면 자동으로 실행되는 PL/SQL 블록
PL/SQL 프로그램 작성 요령
- PL/SQL 블록 내에서 한 문장이 종료될 때마다 세미콜론(;)을 사용하여 한 문장의 끝을 명시
- END 뒤에 세미콜론(;)을 사용하여 하나의 블록의 끝을 명시
- 단일 행 주석은 --, 여러 행 주석은 /* */
- 쿼리문을 실행하기 위해 '/'가 필수, PL/SQL 블록은 행에 '/'가 있으면 종결된 것으로 간주
[참고] 오라클에서 화면 출력을 위해서 PUT_LINE 프로시저 사용
- DBMS_OUTPUT.PUT_LINE(출력할 내용)
- 위 프로시저를 사용하여 출력되는 내용을 화면에 보여주기 위해서 환경 변수 SERVEROUTPUT ON(기본 값이 OFF)으로 변경
- 메시지 출력하기 예시
SET serveroutput ON
BEGIN
dbms_output.put_line('출력 예시');
END;결과 - 출력 예시
변수 선언
- 블록 내에서 변수를 사용하려면 선언부(DECLARE)에서 선언해야 하며 변수명 다음에는 데이터 타입을 기술
- 문법
- identifier [CONSTANT] datatype [NOT NULL]
[:=|DEFAULT expression];
- identifier [CONSTANT] datatype [NOT NULL]
| identifier | 변수명(식별자) |
| CONSTANT | 상수로 지정(초기값 반드시 할당) |
| datatype | 자료형 기술 |
| NOT NULL | 값을 반드시 포함 |
| expression | Literal, 다른 변수, 연산자나 함수를 포함하는 표현식 |
문법으로만 보면 이해가 어렵기 때문에 예시를 통해 이해를 해보자
예시)
- DECALRE 변수이름 데이터타입;
DECLARE NAME VARCHAR2(10);
- DECLARE 변수이름 데이터타입 := 값;
DECLARE NAME VARCHAR2(10) := 'Pdache';
- DECLARE 변수이름 데이터타입 DEFAULT 기본값;
DECLARE NAME VARCHAR2(10) DEFAULT 'PDH';
- 한 번에 여러 개의 변수 선언
DECLARE
NAME VARCHAR2(20);
AGE NUMBER(2);
GENDER VARCHAR(50) DEFAULT '남';
- 변수 선언하고 사용
DECLARE NAME VARCHAR2(20) := '이효리';
BEGIN
DBMS_OUTPUT.put_Line('이효리'|| NAME); -- 출력
END;
- 변수 Type을 선언할 때 명시적으로 작성하지 않고 사용하는 방법
- %ROWTYPE
- 해당 테이블이나 뷰의 컬럼 속성을 그대로 들고 오는 형태
- 사용 방법 : 변수명 테이블이름%ROWTYPE
- %TYPE
- 해당 테이블의 컬럼 속성을 지정하여 그대로 들고 오는 형태
- 사용 방법 : 변수명 테이블이름.컬럼명%TYPE
- %ROWTYPE
- %ROWTYPE 예시
DECLARE
DATA EMP%ROWTYPE;
BEGIN
SELECT * INTO DATA
FROM EMP
WHERE EMPNO = '1234';
DBMS_OUTPUT.PUT_LINE(DATA.ENAME ||','||DATA.DEPTNO);
END;
- %TYPE 예시
DECLARE
V_ENAME EMP.ENAME%TYPE;
V_DEPTNO EMP.DEPTNO%TYPE;
BEGIN
SELECT ENAME, DEPTNO INTO V_ENAME, V_DEPTNO
FROM EMP
WHERE EMPNO = '1234';
DBMS_OUTPUT.PUT_LINE(V_ENAME ||','||V_DEPTNO);
END;
변수 대입 방법
- 명시적 값 대입
- 변수 값을 저장하기 위해 := 사용
- := 좌측에는 변수, 우측에는 값
- SELECT 문을 이용한 값 대입
- 기존 SELECT 문과는 다르게 INTO 추가
- INTO 절에는 조회 결과 값을 저장할 변수 기술
- SELECT 문은 INTO 절에 의해 하나의 행만을 저장
- SELECT 다음에 기술한 컬럼은 INTO 절에 있는 변수와 1:1로 대응해야 하기 때문에 개수와 데이터 타입, 길이를 일치시켜야 한다.
'Study > Oracle' 카테고리의 다른 글
| PL/SQL_6 - 테이블 함수 (0) | 2021.05.18 |
|---|---|
| PL/SQL 기본_5 - 함수 (0) | 2021.05.17 |
| PL/SQL 기본_4 - 프로시저 (0) | 2021.05.10 |
| PL/SQL 기본_3 - 커서 (0) | 2021.05.10 |
| PL/SQL 기본_2 - 반복문과 제어문 (0) | 2021.05.10 |
