MariaDB 패키지 / 프로시저 / 함수 / 트리거 / PL/SQL과 SQL/PSM

패키지 / 프로시저 / 함수 / 트리거에 대한 개념

패키지

• 패키지는 DB에 저장되어있는 서로 관련있는 PL/SQL 프로시저와 함수들의 집합
• 프로시저 집합의 명세서이자 본체를 뜻하는 논리적인 개념
• 실제 작업이 진행되는 것은 프로시저이고, 프로시저 내의 함수들이다
• 패키지는 선언부와 본문 두 부분으로 나누어짐

프로시저

• 개발자가 자주 실행해야하는 특정 작업을 필요할 때 호출하기 위해 절차적인 언어를 이용해 작성한 이름이 있는 프로그램 모듈(block)
• PL/SQL (Oracle's Procedural Language extension SQL, 절차형 SQL)
  • 응용 프로그램에서의 데이터베이스 처리 향상을 위해 SQL 문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR) 등을 지원하며 오라클 자체에 내장되어 있는 프로시저 언어
  • PL/SQL을 이용하여 다양한 저장 모듈을 개발할 수 있음
• 저장모듈이란 PL/SQL 문장을 DB서버 내에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로 부터 실행될 수 있는 완전한 실행 프로그램.
  • Oracle의 저장 모듈에는 프로시저, 사용자 정의함수, 트리거가 있음
• Block 구조로 되어 있어 다수의 SQL 문을 한 번에 DB로 보내서 처리하므로 수행 속도를 향상 시킬 수 있음
  • 서버로의 통신량을 줄일 수 있고 기능별로 모듈화를 할 수 있음

PL/SQL 블록 (Anonymous Block)의 구조 및 특징

• Declare, Begin, Exception, End로 구성
• 프로시저, 사용자 정의 함수, 트리거도 비슷한 문법

구조

• DECLARE <선택사항>
  • 선언부 (변수, 상수)
• BEGIN ~ END에서 사용할 변수나 인수에 대한 정의 및 데이터 타입 선언
  • BEGIN
    • 처리할 SQL 문과 필요한 로직이 정의되는 실행하는 부분
  • EXCEPTION
    • BEGIN ~ END 에서 실행되는 SQL문에 발생된 에러를 처리하는 부분
• 기타
• BEGIN (DECLARE) ~ END가 PL/SQL의 블럭을 의미하며 이는 중첩될 수 있음
• IS (선언문)은 PL/SQL을 이용하여 작성되는 프로시저, 사용자 정의함수에서 사용되는 문법이며
  DECLARE와 마찬가지로 문장의 시작을 의미하면서 동시에 변수가 선언되는 영역임
  • IS가 DECLARE의 역할까지 대신하므로 IS를 사용하면 DECLARE는 사용할 수 없음
• 대입연산자는 :=
• 값 비교 연산자는 =
• DBMS 정의 에러 혹은 사용자 정의 에러 사용 가능
• PL/SQL은 Oracle에 내장되어 있어 이들을 지원하는 어떤 서버로도 프로그램을 옮길 수 있음

사용자 정의 함수

• 생성방법
  • CREATE FUNCTION
• 실행방법
  • 함수명() 으로 실행
• 특징
  • SQL 로직을 묶은 명령문으로 프로시저와 비슷하나 반드시 결과 값을 return문으로 반환해야함

프로시저

• 생성방법
  • CREATE PROCEDURE
• 실행방법
  • EXECUTE 프로시저명() 으로 실행
• 특징
  • BEGIN ~ END 절 내에서 COMMIT, ROLLBACK과 같은 트랜잭션 명령어 사용이 가능

트리거

• 생성방법
  • CREATE TRIGGER
• 실행방법
  • 특정 쿼리 실행시 DB에 의해 자동 실행
• 특징
  • BEGIN ~ END 절 내에서 트랜잭션 사용 불가능

프로시저에 대한 자세한 개념

• 개발자가 자주 실행해야하는 특정 작업을 필요할 때 호출하기 위해 절차적인 언어를 이용해 작성한 이름이 있는 프로그램 모듈(block)
• 매개 변수를 받을 수 있는 PL/SQL Block
• 프로시저 내의 변수는 Scalar 변수라고 하여 임시 데이터 1개만 저장할 수 있으며, 모든 형태의 데이터 유형 지정 가능
• PL/SQL에서 사용하는 프로시저 내의 SELECT 문장은 반드시 결과 값이 있어야 하며 결과는 1개여야 함
  • 조회결과가 없거나 2개 이상인 경우에는 에러가 발생
  • 특정 로직을 처리하면서 결과는 있어야 하지만 결과 값을 함수처럼 반환하지는 않음

프로시저 문법

CREATE \[OR REPLACE\] PROCEDURE 프로시저명 (argument1 \[MODE\] data\_type1, argument2 \[MODE\] data\_type2, ... ...)
IS\[AS\]
...
BEGIN
...
EXCEPTION
...
END;
/

• CREATE
  • 생성
• [OR REPLACE]
  • 같은 프로시저가 있을 때 기존의 프로시저를 무시하고 새로운 내용으로 덮어 쓴다
• MODE
  • 매개변수의 역할을 결정
  • IN : 운영체제에서 프로시저로 전달될 변수의 모드
  • OUT : 운영체제로 반환될 프로시저에서 처리된 결과
  • INOUT : IN과 OUT 두 가지 기능 모두 수행
• IS
  • PL/SQL의 Block을 시작
  • 프로시저 내 (BEGIN문 뒤 나오는 SQL문)에서 사용할 변수를 선언
  • LOCAL 변수는 IS와 BEGIN 사이에서 선언
• [AS]
  • IS와 기능적 차이는 없음
  • IS (임베디드) = 블록 / 하위 프로그램 또는 패키지 내 엔티티용
  • AS (독립형) = 블록 외부, 하위 프로그램, 패키지 내 엔티티용
• EXCEPTION
  • BEGIN~END 사이에서 실행되는 SQL문 실행 도중 발생한 에러를 처리하는 예외 처리 부
• END;
  • 실행문의 종료
• /
  • DB에게 프로시저를 컴파일하라는 명령

프로시저 예제

CREATE OR REPLACE PROCEDURE empInsert (
p_Id VARCHAR(255),
p_name VARCHAR(255)
)
BEGIN
INSERT INTO emp (id, name, empDate) VALUES (p_Id, p_name, NOW);
END;

패키지에 대한 자세한 개념

• 패키지는 DB에 저장되어있는 서로 관련있는 PL/SQL 프로시저와 함수들의 집합
• 프로시저 집합의 명세서이자 본체를 뜻하는 논리적인 개념
• 패키지는 선언부와 본문 두 부분으로 나누어짐
  • 선언부(헤더)와 본문(바디)으로 나누어 지는데 선언부에서는 패키지에서 사용할 프로시저, 함수, 변수, 커서, 예외절을 선언
  • 헤더에서 패키지에 포함될 PL/SQL 프로시저나 함수, 커서, 변수, 예외절을 선언하고 이는 패키지 전체에 적용됨
  • 바디에서 헤더에 선언된 기능들의 실행을 정의
    • 실제 프로시저나 함수의 내용에 해당하는 부분
    • 본문에서는 선언부에서 선언된 기능들의 실행을 정의 (프로시저, 함수의 로직을 정의)
    • 패키지 바디는 패키지 헤더 컴파일 후에 컴파일 해야함
  • 패키지의 헤더와 바디를 CREATE PACKAGE와 CREATE PACKAGE BODY를 하면 이를 프로시저로 실행하거나 EXEC 절을 통해 패키지를 직접 실행하여 사용
    • EXEC 패키지명 / EXEC 패키지명.프로시저명
• 참고
  • http://www.gurubee.net/lecture/1075

mariaDB에서의 패키지

• MariaDB에서는 패키지를 공식적으로 지원하지 않음. 그러나 SQL 모드를 오라클로 설정하면 패키지의 생성을 할 수 있음
  • https://mariadb.com/kb/en/create-package/

    SET sql\_mode = ORACLE;

※ PL/SQL과 SQL/PSM

• PL/SQL
  • Oracle 계열 DBMD에서 사용하는 SQL용 프로그래밍 언어
• SQL/PSM
  • mySQL 계열 DBMS에서 사용하는 SQL용 프로그래밍 언어