오라클 DB 용어, 명령문 정리08-PL/SQL

2019. 05. 28 필기

 

< PL/SQL >

PL/SQL이란?
: PL/SQL(Procedural Language extension to SQL) - SQL에 없는 변수 선언, 선택 처리
- 반복처리 기능을 제공하는 확장언어
- 오라클 DB 환경에서 실행되는 절차적인 데이터베이스 프로그래밍 언어
- PL/SQL에서는 프로그램 단위를 블록(block)이라 부르며, 애플리케이션 로직들을 작성

 

■ 3부분의 블럭구조(영역)로 구성하여 선언
1. DECLARE 영역(선언부) : DECLARE 키워드
2. EXECUTABLE 영역(실행부) : *BEGIN 키워드 - 필수
3. EXCEPTION 영역(예외처리) : EXCEPTION 키워드
-- PL/SQL 블록에서 한 문장이 종료할 때마다 세미콜론(;) 사용
-- 마지막 영역은 END; 키워드로 마무리
-- PL/SQL 명령을 실행하기 위해 마지막에 반드시 /를 입력

■ 메세지를 출력할 수 있도록 세션의 환경변수(SERVEROUTPUT)의 설정값 변경
>> SET SERVEROUTPUT ON;

■ 메세지를 출력하는 함수 -- PL/SQL의 실행부에서 호출하여 사용
>> [형식] DBMS_OUTPUT.PUT_LINE(출력대상)

< 간단한 메시지 출력 PL/SQL 예시 >
BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO ORACLE');
END;
/

 

선언부 : 변수 선언 및 초기값 입력

>>[형식] 변수명 [CONSTANT] 자료형 [NOT NULL] [{:=|DEFAULT} 표현식];
-- 자바와는 반대로 변수를 앞에, 자료형을 뒤에 써준다.

- CONSTANT : 변수에 저장한 초기값 고정 키워드 -- 자바의 FINAL 선언과 비슷
- NOT NULL : 변수에 null 저장 불가능
- := : '저장'의 의미. 즉, 대입연산자.
- 표현식 : 상수, 변수, 연산식, 함수에 의해 값을 제공하는 명령

 

실행부 : 선언된 변수에 저장된 값 변경

>>[형식] 변수명 := 표현식;

 

< 여러 종류의 변수들 >

■ 스칼라 변수
: 자료형을 이용하여 변수 선언
-- 스칼라 변수를 선언하여 값을 저장하고 출력하는 PL/SQL 작성

■ 레퍼런스 변수(참조변수)
: 다른 변수의 자료형 또는 테이블 컬럼의 자료형을 이용하여 변수 선언(선언부)
>>[형식] 변수명 {변수명%TYPE|테이블명.컬럼명%TYPE};

※ 테이블 검색행의 컬럼값을 변수에 저장하는 명령(실행부)
>>[형식] SELECT 검색대상, ... INTO 변수명, ... FROM 테이블명 [WHERE 조건식];
-- 검색대상과 변수의 자료형 및 개수가 반드시 동일하게 선언되어야 함

■ 테이블 변수
: 테이블 다중 검색행에 대한 컬럼값을 저장하기 위한 변수 - 배열
>>[형식] TYPE 테이블명 IS TABLE OF {자료형|변수명%TYPE|테이블명.컬럼명%TYPE}
            [NOT NULL] [INDEX BY BINARY INTEGER];
            테이블변수명 테이블명;
-- 변수명(첨자) 형식으로 테이블 변수의 요소값을 표현하여 사용
-- 변수를 선언하기 전에 테이블 변수에 대한 자료형(테이블 타입) 선언

테이블 변수 사용방법
>>[형식] 변수명(첨자)

■ 레코드 변수

: 테이블 행의 컬럼값들을 저장하기 위한 변수 -- 자바의 인스턴스와 비슷한 개념
-- 변수를 선언하기 전에 레코드 변수에 대한 자료형(레코드 타입) 선언
>>[형식] TYPE 레코드명 IS RECORD(필드명 {자료형|변수명%TYPE|테이블명.컬럼명%TYPE}
            [NOT NULL] [{:=| DEFAULT} 표현식], ...);
            레코드변수명 레코드명;

레코드 변수 사용방법
>>[형식] 레코드변수명.필드명

레코드 변수를 테이블의 행을 이용한 레퍼런스 타입으로 선언 가능
>>[형식] 레코드변수명 테이블명%ROWTYPE;

■ 바인딩 변수
: 세션에서 사용할 수 있는 변수 -- 자바의 로컬변수와 비슷한 개념
>>[형식] VARIABLE 변수명 자료형;

바인딩 변수에 저장된 값 출력
>>[형식] PRINT 바인딩변수명;

 

■ IF 선택문

: 조건식에 의해 명령을 선택하여 실행하는 명령문
>>[형식] IF(조건식) THEN 명령; END IF;

조건식의 결과가 거짓일 때도 명령 실행
>>[형식1]
IF(조건식) THEN 참명령;
ELSE 거짓명령;
END IF;

>>[형식2]
IF(조건식) THEN 명령1;
ELSIF(조건식) THEN 명령2;
... [ELSE 명령];
END IF;

 

■ CASE 조건문

: 변수에 저장된 값 또는 조건식을 이용하여 명령을 선택 실행하는 조건문
>>[형식]
CASE 변수명
WHEN 값1 THEN 명령1;
WHEN 값2 THEN 명령2;
...;
END CASE;

>>[형식2]
CASE
WHEN 조건식1 THEN 명령1;
WHEN 조건식2 THEN 명령2;
...;
END CASE;

 

■ LOOP 반복문

: 명령을 반복적으로 실행하는 명령

- BASIC LOOP
: 무한반복-선택문을 이용하여 EXIT 명령이 실행될 경우 반복문 종료
>>[형식] LOOP 명령 END LOOP;
-- 자바의 WHILE문과 유사

- FOR LOOP
: 반복횟수가 정해져 있는 경우 사용하는 반복문
>>[형식] FOR INDEX_COUNTER [REVERSE] IN LOWER_BOUND..HIGH_BOUND LOOP 명령 END LOOP;
-- 자바의 FOR문과 유사

>>[형식] FOR 레코드변수 IN (다중행 검색 명령) LOOP 명령 END LOOP;

- WHILE LOOP
: 반복의 횟수가 정해져 있지 않을 경우 사용되는 반복문
>>[형식] WHILE 조건식 LOOP 명령 END LOOP;

 

■ 저장 프로시저(STORED PROCEDURE)

: PL/SQL 프로시저를 저장하여 필요한 경우 호출하여 기능을 저장
-- 쿼리문을 저장해놓고 필요할 때 불러와 쓸 수 있는 것 -- 자바의 메소드와 비슷
>>[형식] CREATE [OR REPLACE] PROCEDURE 프로시저명[(매개변수 [MODE] 자료형, ...)]
            IS [변수선언부] BEGIN 명령 END;

※ 현재 접속 사용자의 저장 프로시저 및 함수 목록 반환하는 딕셔너리 : USER_SOURCE

■ 저장 프로시저 호출
>>[형식] EXECUTE 프로시저명[(값 또는 변수, ...)];

■ 컴파일러 로그 확인
>>[형식] SHOW ERROR;
-- 저장 프로시저 생성시 에러가 발생된 경우 확인해보자

■ 저장 프로시저 삭제
>>[형식] DROP PROCEDURE 프로시저명;

 

■ 매개변수 형태(MODE)
1) IN : 외부값을 저장 프로시저에 전달받아 사용할 목적의 매개변수 선언
2) OUT : 저장 프로시저의 내부값을 외부로 제공할 목적의 매개변수 선언
3) INOUT : IN 기능과 OUT 기능을 모두 제공하는 매개변수 선언

 

저장함수
: 저장 프로시저와 동일한 역할이되, 반환값을 제공하는 함수
-- 저장함수는 SQL 명령에 포함하여 사용 가능

저장함수 생성
>>[형식] CREATE [OR REPLACE] FUNCTION 함수명 [(매개변수 [MODE] 자료형, ...)] RETURN 자료형
            IS [변수선언부] BEGIN 명령 END;
-- 저장함수에서는 반드시 RETURN 명령으로 값을 반환해야 함
-- FUNCTION과 PROCEDURE의 차이점
   - FUNCTION : 값을 반환 
   - PROCEDURE : 값을 반환하지 않음 

커서(CURSOR)
: 테이블의 검색행을 처리하기 위한 기능을 제공
1. 묵시적 커서 : 검색 결과가 단일행인 경우 처리하기 위한 커서
2. 명시적 커서 : 검색 결과가 다중행인 경우 처리하기 위한 커서

명시적 커서의 선언 및 사용 방법
>>[형식]
DECLARE
         CURSOR 커서명 IS 검색명령;
     BEGIN
         OPEN 커서명;
         FETCH 커서명 INTO 변수명;
         CLOSE 커서명;
     END;

- FOR LOOP 구문을 이용할 경우 커서에 대한 OPEN,FETCH,CLOSE 구문 미사용
- 커서를 서브쿼리 형식으로 표현하여 사용할 수도 있다.

트리거(TRIGGER)
: 특정 SQL 명령이 실행될 경우 PL/SQL 명령으로 자동 실행되는 기능 제공

트리거 생성
>>[형식] CREATE [OR REPLACE] TRIGGER 트리거명 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 테이블명
            [FOR EACH ROW] [WITH 조건식] BEGIN 명령 END;
- FOR EACH ROW : 생략된 경우 문장 레벨 트리거로 생성하며, 선언된 경우 행 레벨 트리거로 생성
- 문장 레벨 트리거 : 이벤트 DML 명령이 실행되면 트리거 명령이 한번만 실행
- 행 레벨 트리거 : 이벤트 DML 명령이 실행되면 트리거 명령이 여러번 실행

- NEW.column : 저장행 또는 변경행의 컬럼 표현
- OLD.column : 삭제행 또는 변경행의 컬럼 표현

트리거 삭제
>>[형식] DROP TRIGGER 트리거명;