SQL - ⑭ PL SQL (DMCCONET)

hoegon kim·2022년 10월 24일

0

SQL-PLUS

목록 보기

15/17

PL SQL

학습내용

1.PL/SQL
2.변수선언
3.제어문
4.커서

학습목표

PL/SQL은 선언부, 실행부, 예외 처리부 3개의 블록으로 나뉩니다.
PL/SQL에서는 스칼라 변수와 레퍼런스 변수를 사용합니다.
선택문으로는 IF ~ THEN ~ END IF, IF ~ THEN ~ ELSE ~ END IF, IF ~ THEN ~ ELSIF ~
ELSE-END IF 3가지를 제공합니다.
반복 처리를 위해서 BASIC ~ LOOP, FOR ~ LOOP, WHILE LOOP 문을 제공합니다.
PL/SQL에서 SQL 문의 결과로 얻어지는 로우가 여러 개일 경우 이를 처리하기 위해 커서를 사용합니다.

1.PL/SQL

PL/SQL(Oracle's Procedural Language extension to SQL)은 오라클에서 지원하는 프로그래밍 언어의 특성을 수용하여 SQL에서는 사용할 수 없는 절차적 프로그래밍 기능을 가지고 있어 SQL의 단점을 보완합니다.
PL/SQL에서 지원되는 절차적 프로그래밍 기능은 다음과 같습니다.

기본적인 PL/SQL 블록 구조는 선언부, 실행부, 예외처리부 이렇게 세 부분으로 구성됩니다.

PL/SQL 프로그램의 작성 요령은 다음과 같습니다.

메세지 출력하기

2.변수선언

PL/SQL 블록 내에서 변수를 사용하려면 선언부(DECLARE)에서 선언해야 합니다.

1) 스칼라

PL/SQL에서 변수를 선언할 때 사용되는 데이터 타입은 SQL에서 사용하던 데이터 타입과 유사합니다. 숫자, 문자, 날짜, BOOLEAN 4가지로 나뉩니다.

2) 레퍼런스

레퍼런스 타입은 변수의 데이터 타입을 데이터베이스 기존 칼럼에 맞추어 선언하기 위해서 %TYPE Attribute를 이용합니다. 변수를 선언할 때 필요한 데이터 타입을 명시적으로 언급하는 대신 ‘TABLE이름.COLUMN이름%TYPE’으로 지정합니다.
%TYPE은 칼럼 단위로 데이터 타입을 참조합니다.

로우(행) 전체에 대한 데이터 타입을 참조하려면 %ROWTYPE을 사용합니다.

변수에 값을 저장하기 위해서는 ‘:=’를 사용합니다. :=의 좌측에는 변수를, 우측에는 값을 기술합니다.

3.제어문

기본적으로 모든 문장들은 나열된 순서대로 순차적으로 수행됩니다.
하지만 경우에 따라서는 문장의 흐름을 변경할 필요가 있습니다. 이때 사용하는 것이 IF 문입니다.
IF 문은 조건을 제시해서 만족하느냐 하지 않느냐에 따라 문장을 선택적으로 수행하기 때문에 선택문이라고 합니다.
오라클에서는 3가지 형태의 선택문이 제공됩니다.

3-1 IF

IF 문은 조건에 따라 어떤 명령을 선택적으로 처리하기 위해 사용합니다.

3-2 LOOP문

반복문은 SQL 문을 반복적으로 여러 번 실행하고자 할 때 사용합니다.
PL/SQL 에서는 다음과 같이 다양한 반복

BASIC LOOP는 가장 간단한 구조의 루프로 LOOP와 END LOOP 사이에 반복 수행할 문장을 기술됩니다.

실행 상의 흐름이 END LOOP에 도달할 때마다 그와 짝을 이루는 LOOP 문으로 제어가 되돌아갑니다.
이러한 루프를 무한 루프라 하며, 여기서 빠져나가려면 EXIT문을 사용합니다.
기본 LOOP는 LOOP에 들어갈 때 조건이 이미 일치했다 할지라도 적어도 한번은 문장이 실행됩니다.
FOR LOOP는 반복되는 횟수가 정해진 반목문을 처리하기에 용이합니다.

FOR LOOP 문에서 사용되는 인덱스는 정수로 자동 선언되므로 따로 선언할 필요가 없다.
FOR LOOP 문은 LOOP을 반복할 때마다 자동적으로 1씩 증가 또는 감소합니다. REVERSE는 1씩 감소함을 의미합니다.
제어 조건이 TRUE인 동안만 일련의 문장을 반복하기 위해 WHILE LOOP 문장을 사용합니다. 조건은 반복이 시작될 때 체크하게 되어 LOOP내의 문장이 한 번도 수행되지 않을 경우도 있습니다. LOOP을 시작할 때 조건이 FALSE이면 반복 문장을 탈출하게 됩니다.

4.커서

SELECT 문의 수행 결과가 여러 개의 로우로 구해지는 경우에 모든 로우에 대한 처리를 하려면 커서를 사용해야 합니다. 커서는 CURSOR, OPEN, FETCH, CLOSE 4단계 명령에 의해서 사용됩니다.

4-1 DECLARE CURSOR

명시적으로 CURSOR를 선언하기 위해 CURSOR 문장을 사용합니다.

4-2 OPEN CURSOR

질의를 수행하고 검색 조건을 충족하는 모든 행으로 구성된 결과 셋을 생성하기 위해 CURSOR를 OPEN합니다. CURSOR는 이제 결과 셋에서 첫 번째 행을 가리킨다.

부서 테이블의 모든 내용을 조회하는 SELECT문과 연결된 커서 C1을 오픈합니다.

C1을 오픈하면 검색 조건에 만족하는 모든 행으로 구성된 결과 셋이 구해지고 부서 테이블의 첫 번째 행을 가리키게 됩니다.

4-3 FETCH CURSOR

FETCH 문은 결과 셋에서 로우 단위로 데이터를 읽어 들인다. 각 인출(FETCH) 후에 CURSOR는 결과 셋에서 다음 행으로 이동합니다.

FETCH 문장은 현재 행에 대한 정보를 얻어 와서 INTO 뒤에 기술한 변수에 저장한 후 다음 행으로 이동합니다. 얻어진 여러 개의 로우에 대한 결과값을 모두 처리하려면 반복문에 FETCH 문을 기술해야 합니다.

커서가 끝에 위치하게 되면 반복문을 탈출해야 합니다.
단순 LOOP는 내부에 EXIT WHEN 문장을 포함하고 있다가 EXIT WHEN 다음에 기술한 조건에 만족하면 단순 LOO을 탈출하게 됩니다.
반복문을 탈출할 조건으로 “C1%NOTFOUND“를 기술하였습니다.
NOTFOUND는 커서의 상태를 알려주는 속성 중에 하나인데 커서 영역의 자료가 모두 FETCH됐다면 TRUE를 되돌립니다.
커서 C1영역의 자료가 모두 FETCH 되면 반복문을 탈출하게 됩니다.

4-4 커서의 상태

FETCH 문을 설명하면서 커서의 속성 중에 NOTFOUND를 언급하였는데 오라클에서는 이외에도 다양한 커서의 속성을 통해 커서의 상태를 알려주는데 이 속성을 이용해서 커서를 제어해야 합니다.

4-5 CLOSE CURSOR

CLOSE문장은 CURSOR를 사용할 수 없게 하고 결과 셋의 정의를 해제합니다.
SELECT 문장이 다 처리된 완성 후에는 CURSOR를 닫는다. 필요하다면 CURSOR를 다시 열수도 있습니다.

4-6 CURSOR 와 FOR LOOP

CURSOR FOR LOOP는 명시적 CURSOR에서 행을 처리합니다. LOOP에서 각 반복마다 CURSOR를 열고 행을 인출(FETCH)하고 모든 행이 처리되면 자동으로 CURSOR가 CLOSE되므로 사용하기가 편리합니다.

OPEN ~ FETCH ~ CLOSE가 없이 FOR ~ LOOP ~ END LOOP문을 사용하여 보다 간단하게 커서를 처리해 봅시다.

이전 포스트

SQL - ⑬ 사용자 권한 (DMCCONET)

다음 포스트

SQL - ⑮ 프로시저와 함수와 트리거 (DMCCONET)

0개의 댓글