[SQL PL] PL/SQL 변수제어, 흐름제어 (IF, LOOP, REPEAT)

 

 

 

 

 

 

 

1> PL/SQL 구조

 

 

- Oracle 제공하는 절차적인 기능을 가진 프로그래밍 언어 (Procedural Language / SQL)

 

 

블록 HEAD : 시작 레이블, BEGIN 키워드, ATOMIC 키워드

 

 

- BEGIN : 블록의 시작 표시

 

- ATOMIC : 트랜잭션을 제어하지 않는 블록 지정

 

 

블록 BODY : SQL PL 문장 (SQL 문과 세미콜론(;) 기호로 구분

 

블록 TAIL : END 키워드, 종료 레이블

 

 

l  Setserveroutput on : 해당 명령어를 실행해야 화면에 메시지 출력

l  DBMS_OUTPUT.PUT_LINE : 화면에 메시지 출력 명령어

 

 

 

2> SQL PL 문장

 

 

유형, 변수 선언 : DECLARE

 

변수 할당 : SET 변수, SELECT INTO

 

비교 제어 : IF, CASE

 

반복 제어 : LOOP, WHILE, REPEAT, FOR

 

이동 제어 : ITERATE, LEAVE, GOTO, RETURN

 

 

 

 

3> 사용자 정의 유형

 

 

복합 SQL 스크립트 : 비교적 간단하고 짧은 로직을 구현한 스크립트 (파일 형태)

 

SQL 프로시저 : 데이터 액세스와 업무 로직의 절차를 구현한 데이터베이스 오브젝트

 

SQL 함수 : N개의 매개변수에 대해 단일값 또는 테이블 반환하는 처리 로직을 구현한 데이터베이스 오브젝트

 

트리거 : 테이블 또는 뷰에 대해 특정 SQL 문이 실행될 때 자동적으로 처리

 

SQL 메소드 : 사용자 정의 structured 유형에 대해 메소드를 구현한 데이터베이스 오브젝트

 

모듈 : 여러 개의 프로시저, 함수, 전역변수 등 구성된 데이터베이스 오벡트 (CREATE MODULE, ALTER MODULE)

 

 

 

 

4> SQL PL 변수 제어

 

 

유형 선언 : DECLARE TYPE

 

변수 선언 : DECLARE 변수

 

변수 할당 : SET 변수, SELECT INTO, VALUES INTO

 

 

- CREATE TYPE 문 기능 : distinct 유형, row 유형, array 유형, cursor 유형 등의 사용자 데이터 유형 정의

 

- CREATE [OR REPLACE] TYPE [스키마.] 유형명

AS {distinct 유형 | row 유형 | array 유형 | cursor 유형}

 

 

 

 

5> SELECT INTO 문

 

 

SELECT 문의 실행 결과로 반환된 결과를 변수에 할당

 

SELECT 변수  
INTO 위의 변수 컬럼에 대응하는 scalar 변수 
FROM 테이블;

 

 

Ex>

BEGIN 

DECLARE v_no INT; 

SELECT ENO
INTO v_no
FROM TABLE_TEST
WHERE ENO = 10;

 

 

 

- 흐름 제어 문장

 

 

1> IF 문

 

 

// IF문 시작 지정

 

IF 조건1 THEN

 

SQL PL문;

SQL PL문;

 

ELSEIF 조건2 THEN

 

SQL PL문;

SQL PL문;

 

ELSE

 

SQL PL문;

SQL PL문;

 

END IF // IF문 종료

 

 

 

2> LOOP 문

 

 

LOOP문은 반복적으로 실행할 경우 선언합니다.

 

BODY 문에서 루프 탈출 조건이 없는 경우 무한루프 발생합니다.

 

 

<LOOP문 문법>

 

[레이블 :]

LOOP                 // 루프 시작

 

SQL PL문;           // BODY 부분으로 반복할 조건 선언

SQL PL문;

 

END LOOP [레이블] // 루프 종료

 

 

 

<LOOP문 예시>

 

DECLARE A1 INT DEFAULT 0; 
TEST:

LOOP           

SET A1 = A1 + 1;           
CALL DBMS_OUTPUT.PUT_LINE(‘A1 = ‘ || A1); 

END LOOP TEST;

 

 

<출력문>

 

A1 = 1

A1 = 2

A1 = 3

……

 

 

 

3> REPEAT문

 

 

조건식의 참, 거짓에 따라 반복문을 실행합니다.

 

참 일 경우, 반복문을 종료합니다.

 

 

<REPEAT 문법>

 

REPEAT

 

SQL PL문;

SQL PL문;

 

UNTIL 조건        // 조건식이 거짓이면 반복하고, 참이면 반복이 종료됩니다.

 

END REPEAT

 

 

 

<REPEAT문 예시>

 

DECLARE A1 INT DEFAULT 0; 

REPEAT           
SET A1 = A1 + 1;          
CALL DBMS_OUTPUT.PUT_LINE(‘A1 = ‘ || A1); 
UNTIL (A1 >= 3) 

END REPEAT

 

 

<출력문>

 

A1 = 1

A1 = 2

A1 = 3

 

 

 

- PL/SQL TRIIGGER (트리거)

 

 

서브 프로그램으로 특정 사건이 발생 될 때마다 묵시적(자동)으로 해당 PL/SQL 블록 실행

 

CREATE [OR REPLACE] TRIGGER  trigger_name
timing          
event1 

ON [table_name | view_name | SCHEMA | DATABASE]
[REFERENCING OLD AS old | NEW AS new]
[FOR EACH ROW [WHEN (condition)]]
trigger_body

 

 

- Timing : 트리거가 실행되는 시점 지정 (before, after, instead of)

- Event : 트리거 실행시키는 사건 (DML-이벤트_