데이터베이스 구축 4장 SQL 활용

프로시저(Procedure)

프로시저(Procedure)의 개요

　프로시저는 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어로, 호출을 통해 실행되어 미리 저장해 놓은 SQL 작업을 수행한다. 프로시저를 만들어 데이터베이스에 저장하면 여러 프로그램에서 호출하여 사용할 수 있다. 프로시저는 데이터베이스에 저장되어 수행되기 때문에 스토어드(Stored) 프로시저라고도 불린다. 프로시저는 시스템의 일일 마감 작업, 일괄(Batch) 작업 등에 주로 사용된다. 　프로시저의 구성은 다음과 같다.

• DECLARE: 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부이다.
• BEGIN/END: 프로시저의 시작과 종료를 의미한다.
• CONTROL: 조건문 또는 반복문이 삽입되어 순차적으로 처리된다.
• SQL: DML, DCL이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행한다.
• EXCEPTION: BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의한다.
• TRANSACTION: 수행된 데이터 작업들을 DB에 적용할지 취소할지를 결정하는 처리부이다.

프로시저 생성

　프로시저를 생성하기 위해서는 CREATE PROCEDURE 명령어를 사용한다.

CREATE [OP REPLACE] PROCEDURE 프로시저명(파라미터)
[지역변수 선언]
BEGIN
  프로시저 BODY;
END;

• OR REPLACE
  • 선택적인(Optional) 예약어이다. 이 예약어를 사용하면 동일한 프로시저 이름이 이미 존재하는 경우, 기존의 프로시저를 대체할 수 있다.
• 프로시저명
  • 생성하려는 프로시저의 이름을 지정한다.
• 파라미터
  • IN: 호출 프로그램이 프로시저에게 값을 전달할 때 지정한다.
  • OUT: 프로시저가 호출 프로시저에게 값을 반환할 때 지정한다.
  • INOUT: 호출 프로그램이 프로시저에게 값을 전달하고, 프로시저 실행 후 호출 프로그램에 값을 반환할 때 지정한다.
  • 매개변수명: 호출 프로그램으로부터 전달받은 값을 전달하고, 프로시저 실행 후 호출 프로그램에 값을 반환할 때 지정한다.
  • 자료명: 변수의 자료형을 지정한다.
• 프로시저 BODY
  • 프로시저의 코드를 기록하는 부분이다.
  • BEGIN에서 시작하여 END로 끝나며, BEGIN과 END 사이에는 적어도 하나의 SQL문이 있어야 한다.

프로시저 생행

　프로시저를 생행하기 위해서는 EXECUTE 명령어 또는 CALL 명령어를 사용하며, EXECUTE 명령어를 줄여서 EXEC로 사용하기도 한다.

EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;

프로시저 제거

　프로시저를 제거하기 위해서는 DROP PROCEDURE 명령어를 사용한다.

DROP PROCEDURE 프로시저명;

트리거(Trigger)

트리거(Trigger)의 개요

　트리거는 데이터베이스 시스템에서 데이터의 삽입(Insert), 갱신(Update), 삭제(Delete) 등의 이벤트(Event)가 발행할 때마다 관련 작업이 자동으로 수행되는 정차형 SQL이다. 트리거는 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용된다. 트리거의 구문에는 DCL(데이터 제어어)을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에도 오류가 발생한다. 트리거에 오류가 있는 경우 트리거가 처리하는 데이터에도 영향을 미치므로 트리거를 생성할 때 세심한 주의가 필요하다.

트리거의 구성

　트리거는 선언, 이벤트, 시작, 종료로 구성되며, 시작과 종료 구문 사이에는 제어(CONTROL), SQL, 예외(EXCEPRION)가 포함된다.

• DECLARE: 트리거저의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부이다.
• EVENT: 트리거가 실행되는 조건을 명시한다.
• BEGIN/END: 트리거의 시작과 종료를 의미한다.
• CONTROL: 조건문 또는 반복문이 삽입되어 순차적으로 처리된다.
• SQL: DML문이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행한다.
• EXCEPTION: BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의한다.

트리거의 생성

　트리거를 생성하기 위해서는 CREATE TRIGGER 명령어를 사용한다.

CREATE [OP REPLACE] TRIGGER 트리거명 동작시기 동작 ON 테이블명
[REFERENCEING NEW|OLD AS 테이블명]
[FOR EACH ROW [WHEN 조건식]]
BEGIN
  트리거 BODY;
END;

• OR REPLACE
  • 선택적인(Optional) 예약어이다. 이 예약어를 사용하면 동일한 트리거 이름이 이미 존재하는 경우, 기존의 트리거를 대체할 수 있다.
• 동작시기:
  • 트리거가 실행될 때를 지정한다.
  • AFTER: 테이블이 변경된 후에 트리거가 실행된다.
  • BEFORE: 테이블이 변경되기 전에 트리거가 실행된다.
• 동작
  • 트리거가 실행되게 할 작업의 종류를 지정한다.
  • INSERT: 테이블에 새로운 튜플을 삽입할 때 트리거가 실행된다.
  • DELETE: 테이블의 튜플을 삭제할 때 트리거가 실행된다.
  • UPDATE: 테이블의 튜플을 수정할 때 트리거가 실행된다.

• NEW

  OLD

  • NEW: 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미한다.
  • OLD: 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미한다.
• FOR EACH ROW
  • 각 퓨틀마다 트리거를 적용한다는 의미이다.
• WHEN 조건식
  • 선택적인(Optonal) 예약어이다. 트리거를 적용할 튜플의 조건을 지정한다.
• 트리거 BODY
  • 트리거의 본문 코드를 입력하는 부분이다.
  • BEGIN으로 시작해서 END로 끝나는데, 적어도 하나 이상의 SQL문이 있어야 한다. 그렇지 않으면 오류가 방생한다.

트리거의 제거

　트리거를 제거하기 휘해서는 DROP TRIGGER 명령어를 사용한다.

DROP TRIGGER 트리거명;

사용자 정의 함수

사용자 정의 함수의 개요

　사용자 정의 함수는 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하며, 종료 시 처리 결과를 단일값으로 반환하는 절차형 SQL이다. 사용자 정의 함수는 데이터베이스에 저장되어 SELECT, INSERT, DELETE, UPDATE 등 DML문의 호출에 의해 실행된다. 사용자 정의 함수는 예약어 RETURN을 통해 값을 반환하기 때문에 출력 파라미터가 없다.
　사용자 정의 함수는 INSERT, DELETE, UPDATE를 통한 테이블 조작은 할 수 없고 SELECT를 통한 조회만 할 수 있다. 사용자 정의 함수는 프로시저를 호출하여 사용할 수 없고 SUM(), AVG() 등의 내장 함수처럼 DML문에서 반환값을 활용하기 위한 용도로 사용된다.

사용자 정의 함수의 구성

　사용자 정의 함수의 구성은 프로시저와 유사하다. 프로시저의 구성에서 RETURN만 추가하면 된다.

• DECLARE: 사용자 정의 함수의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부이다.
• BEGIN/END: 사용자 정의 함수의 시작과 종료를 의미한다.
• CONTROL: 조건문 또는 반복문이 삽입되어 순차적으로 처리된다.
• SQL: SELECT문이 삽입되어 데이터 조회 작업을 수행한다.
• EXCEPTION: BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의한다.
• RETURN: 호출 프로그램에 반환할 값이나 변수를 정의한다.

사용자 정의 함수 생성

　사용자 정의 함수를 생성하기 위해서는 CREATE FUNCTION 명령어를 생성한다.

CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
[지역변수 선언]
BEGIN
  사용자 정의 함수 BODY;
  RETURN 반환값;
END;

• OR REPLACE
  • 선택적인(Optional) 예약어이다. 이 예약어를 사용하면 동일한 사용자 정의 함수의 이름이 이미 존재하는 경우, 기존의 사용자 정의 함수를 대체할 수 있다.
• 파라미터
  • 사용자 정의 함수의 파라미터로는 다음과 같은 것들이 올 수 있다.
  • IN: 호출 프로그램이 사용자 정의 함수에게 값을 전달할 때 지정한다.
  • 매개변수명: 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정한다.
  • 자료명: 변수의 자료형을 지정한다.
• 사용자 정의 함수 BODY
  • 사용자 정의 함수의 코드를 기록하는 부분이다.
  • BEGIN으로 시작해서 END로 끝나는데, BEGIN과 END 사이에는 적어도 하나 이상의 SQL문이 있어야 한다.
• RETUEN 반환값: 반환할 값이나 반환할 값이 저장된 변수를 호출 프로그램으로 돌려준다.

사용자 정의 함수 실행

　사용자 정의 함수는 DML에서 속성명이나 값이 놓일 자리를 대체하여 사용된다.

SELECT 사용자 정의 함수명 FROM 테이블명;
INSERT INTO 테이블명(속성명) VALUES (사용지 정의 함수명);
DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;
UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

사용자 정의 함수 제거

　사용자 정의 함수를 제거하기 위해서는 DROP FUNCTION 명령어를 사용한다.

DROP FUNCTION 사용자 정의 함수명;

DBMS 접속 기술

DBMS 접속의 개요

　DBMS 접속은 사용자가 데이터를 사용하기 위해 응용 시스템을 이용하여 DBMS에 접속하는 것을 의미한다. 응용 시스템은 사용자로부터 매개 변수를 전달받아 SQL을 실행하고 DBMS로부터 전달받은 결과를 사용자에게 전달하는 매개체 역할을 수행한다. 인터넷을 통해 구동되는 웹 응용 프로그램은 웹 응용 시스템을 통해 DBMS에 접근한다. 웹 응용 시스템은 웹 서버와 웹 애플리케이션 서버(WAS)로 구성되며, 서비스 규모가 작은 경우 웹 서버와 웹 애플리케이션 서버를 통합하여 하나의 서버만으로 운용할 수 있다.

DBMS 접속 기술

　DBMS 접속 기술은 DBMS에 접근하기 위해 사용하는 API 또는 API의 사용을 편리하게 도와주는 프레임워크 등을 의미한다.

• JDBC(Java DataBase Connectivity)
  • JDBC는 Java 언어로 다양한 종류의 데이터베이스에 접속하고 SQL문을 수행할 때 사용되는 표준 API이다
• ODBC(Open DataBase Connectibity)
  • ODBC는 데이터베이스에 접근하기 위한 표준 개방형 API로, 개발언어에 관계없이 사용할 수 있다.
• MyBatis
  • MyBatis는 JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 프레임워크이다.

동적 SQL(Dynamic SQL)

　동적 SQL은 개발 언어에 삽입되는 SQL 코드를 문자열 변수에 넣어 처리하는 것으로, 조건에 따라 SQL 구문을 동적으로 변경하여 처리할 수 있다. 동적 SQL은 사용자로부터 SQL문의 일부 또는 전부를 입력받아 실행할 수 있다. 동적 SQL은 값이 입력되지 않을 경우 사용하는 NVL 함수를 사용할 필요가 없다. 동적 SQL은 응용 프로그램 수행 시 SQL이 변경될 수 있으므로 프리컴파일 할 때 구문 분석, 접근 권한 확인 등을 할 수 없다. 동적 SQL은 정적 SQL에 비해 속도가 느리지만, 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발이 가능하다.