PL/SQL 구성, 요소, 제어문, 사용자 정의 함수 작성 방법

PL/SQL 정리 시작!

 

 PL/SQL 기본구조

오라클 DB를 사용해 프로그래밍을 한다는 것은 SQL과 PL/SQL을 작성하는 것으로 나눌 수 있다.

1)     SQL: 집합적 언어로 특정 조건에 맞는 데이터를 한번에 처리하는 특징

2)     PL/SQL: 집합적 언어 + 절차적 처리

일반 프로그래밍 언어처럼 변수에 값을 할당하고 예외처리도 할 수 있으며 특정 기능을 처리하는 함수나 프로시저를 만들 수 있는 기능 제공

 

‘DB 프로그래밍’

1)     SQL을 사용한 DML문

2)     PS/SQL: 실제 복잡한 비즈니스 로직을 만들며, 일반 프로그래밍 언어에서 제공하는 많은 기능이 탑재되어 있다. DB에 직접 탑재되어 컴파일되고 실행되어 성능 면에서도 우수, DB 관련 처리를 할 때 수많은 기능을 제공한다.

 

PL/SQL 소스 프로그램의 기본 단위를 블록(Block)이라고 한다.

블록: 선언부, 실행부, 예외처리부로 구성

1)     이름이 없는 블록(익명블록)

2)     이름이 있는 블록: Ex) 함수, 프로시저, 패키지 등이 후자에 해당

이름부: 블록의 명칭이 오는데, 생략할 때는 익명 블록이 된다. IS(AS) 선언부: -       DECLARE로 시작되며, -       실행부와 예외 처리부에서 사용할 각종 변수, 상수, 커서 등을 선언한다. -       문장의 끝에 반드시 세미콜론(;)을 찍어야 한다. -       사용할 변수나 상수가 없다면 선언부를 생략할 수 있다. BEGIN 실행부: -       실제 로직을 처리하는 부분이다. -       각종 문장(일반 SQL문, 조건문, 반복문 등)이 올 수 있고 이러한 문장을 사용해 비즈니스 로직을 구현하는 것이다. -       SQL 문장 중 DDL문을 사용할 수 없고 DML 문만 사용할 수 있다. -        모든 문장 끝에는 세미콜론을 붙인다. EXCEPTION 예외 처리부: - EXCEPTION 절로 시작되는 부분으로 실행부에서 로직을 처리하다가 오류가 발생하면 처리할 내용을 기술 - 생략이 가능하다. - PL/SQL 코드의 오류는 컴파일 과정에서 발생하는 오류와 런타임(실행 과정에서 발생하는 오류)가 있는데 런타임 오류가 발생했을 때 처리할 부분을 기술한다. Ex) 나누는 로직에 0이 들어오면 오류가 발생하는 것이다. 이때 “0으로 나눌 수 없습니다”라는 메시지를 보여주는 처리를 예외 처리부에 기술해 주는 것이다.   END;

set serveroutput on -- DBMS_OUTPUT 패키지를 사용해 값을 출력하기 위해서

set timing on -- 총 소요시간을 알 수 있는 명령어

 

PL/SQL의 블록에는 다양한 요소들이 올 수 있는데, 이들에 대해 하나씩 살펴보자

(변수, 상수, 연산자, 주석, DML문, PRAGMA 키워드, 라벨)

1)     변수: 선언부에서 변수 선언을 하고 실행부에서 사용한다.

변수명 데이터타입 := 초깃값;

초기값을 할당하지 않으면 타입에 상관없이 그 변수의 초기값은 NULL이 된다.

PL/SQL 데이터 타입에는 BOOLEAN, PLS_INTEGER, BINARY_INTEGER 등이 있다.

-       BOOLEAN: 참인지 거짓인지 판별할 때 사용, TRUE, FALSE, NULL 3가지 값을  가지고 있다.

-       PLS_INTEGER: 숫자 타입으로, NUMBER 형에 비해 내부적으로 저장 공간을 덜 차지한다.

-       BINARY_INTEGER도 숫자 타입이다.

그 밖에 커서 타입, RECORD 타입 등이 있다.

PLS_INTEGER 타입에는 하위 데이터 타입이 존재한다. (NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, SIMPLE_INTEGER 등_

2)     상수: 변수와 달리 한번 값을 할당하면 변하지 않는다.

상수명 constant 데이터타입 := 상수값;

상수를 선언할 때는 반드시 CONSTANT란 키워드를 붙여 변수와 구분하며, 선언할 때는 반드시 초기화해야 하고 실행부에서 상수를 다른 값으로 변경할 수 없다.

3)     연산자: 여러가지가 있지만 우선순위가 가장 높은’**’ 연산자는 제곱 연산자를 뜻하는데 PL/SQL에서만 사용이 가능하다.2**3은 2의 3제곱을 뜻한다.

4)     주석,(--) DML문

 

PL/SQL 제어문

절차적 언어인 PL/SQL은 다른 프로그래밍 언어에서 제공하는 다양한 처리문들을 포함하는데 이들을 통칭해 PL/SQL 제어문이라고 한다.

제어문에는 특정 조건에 맞을 때 처리하는 조건문, 반복문, GOTO나 NULL과 같은 순차적 제어문이 있다.

1)     IF문

특정 조건에 따라 처리를 하는 것을 조건문이라 하는데 대표적인 예가 IF문이다.

<조건이 1개인 경우> If 조건 then 조건 처리; End if ================ <조건이 n개인 경우> If 조건1 then 조건 처리1; Elseif 조건2 then 조건 처리2; … Else 조건 처리n; End if;

 

오라클에서는 조건 다음에는 THEN을, 문장 맨 마지막에는 END IF를 명기해야 한다. 또한 조건이 1개 이상이면 ELSE IF가 아닌 ELSEIF를 사용한다.

 

l  PL/SQL의 사용자 정의 함수

지금까지의 예제는 익명 블록을 사용했는데, 사실 별로 사용하지 않는다. 일반적으로는 이름이 있는 서브 프로그램인 함수나 프로시저를 사용해 비즈니스 로직을 구현해 처리할 때가 많다. 익명 블록은 한번 사용하고 나면 없어져 버리는 휘발성 블록이지만, 서브 프로그램은 컴파일을 거쳐 데이터베이스 내에 저장되어 재사용이 가능하다.

 

l  사용자 지정 함수 생성

CREATE OR REPLACE FUNCTION 함수 이름 (매개변수1, 매개변수2, …) RETURN 데이터타입; IS[AS] 변수, 상수 등 선언 BEGIN 실행부   RETURN 반환 값; [EXCEPTION 예외 처리부] END [함수 이름];

l  CREATE OR REPLACE FUNCTION:CREATE OR REPLACE 구문을 사용해 함수를 생성한다. 최초 함수를 만들고 나서 수정을 하더라도 이 구문을 사용해 계속 컴파일할 수 있고 마지막으로 수정된 최종본이 반영된다.

l  매개변수: 함수로 전달되는 매개변수로, “매개변수명 데이터 타입” 형태로 명시한다. 매개변수는 생략할 수 있다.

l  RETURN 데이터 타입: 함수가 반환할 데이터 타입을 지정한다.

l  RETURN 반환값: 매개변수를 받아 특정 연산을 수행한 후 반환할 값을 명시한다.

Ex)

Create or replace function my_mod(num1 number, num2 number) Return number Is vn_remainder number := 0; vn_quotient number :=0; begin vn_quotient := floor(num1 /num2); vn_remainder := num1 – (num2 * vn_quotient); return vn_remainder; end;

l  함수 호출

<매개변수가 없는 함수 호출>

함수명 혹은 함수명()

<매개변수가 있는 함수 호출>

함수명(매개변수1, 매개변수2,….)

Ex) select my_mod(14,3) reminder from dual;

 

 

실무에서 알아야 할 기술은 따로있다! 오라클SQL과 PL/SQL을 다루는 기술 정리