3과목 - 데이터베이스 구축
4장. SQL 활용
108. 프로시저 (Procedure)
프로시저의 개요
-
프로시저란 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어로, 호출을 통해 미리 저장해 놓은 SQL 작업 수행
-
데이터베이스에 저장되어 수행되기 때문에 Stored Procedure라고도 불림
-
시스템의 일일 마감 작업, 배치 작업 등에 주로 사용
-
프로시저 구성도
DECLARE BEGIN CONTROL SQL EXEPTION TRANSACTION END- DECLARE : 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부
- BEGIN / END : 프로시저의 시작과 종료
- CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
- SQL : DML 또는 DCL 삽입
- EXCEPTION : BEGIN ~ END 안의 구문에서 예외가 발생하면 이를 처리하는 방법을 정의
- TRANSACTION : 수행될 데이터 작업들을 DB에 적용할지 취소할지 결정
프로시저 생성
CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
[지역변수 선언]
BEGIN
프로시저 BODY;
END;- OR REPLACE : 동일한 프로시저 이름이 존재하는 경우, 기존의 프로시저를 대체
- 파라미터
- IN : 호출 프로그램이 프로시저에게 값을 전달할 때 지정
- OUT : 프로시저가 호출 프로그램에게 값을 반환할 때 지정
- INOUT : 호출 프로그램이 프로시저에게 값을 전달하고, 프로시저 실행 후 호출 프로그램에 값을 반환할 때 지정
- 프로시저 BODY : 프로시저의 코드를 기록하는 부분
프로시저 실행
EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;프로시저 제거
DROP PROCEDURE 프로시저명;109. 트리거 (Trigger)
트리거의 개요
- 트리거는 데이터베이스 시스템에서 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL
- 목적 : 데이터 변경 및 무결성 유지, 로그 메시지 출력
- 트리거 구문에는 DCL을 사용할 수 없음
트리거의 구성
DECLARE
EVENT
BEGIN
CONTROL
SQL
EXEPTION
END- EVENT : 트리거가 실행되는 조건 명시
트리거의 생성
CREATE [OR REPLACE] TRIGGER 트기러명 [동작시기 옵션][동작 옵션] ON 테이블명
REFERENCING [NEW | OLD] AS 테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
트리거 BODY;
END;- 동작시기 옵션 : 트리거가 실행될 때를 지정
- AFTER : 테이블이 변경된 후에 트리거 실행
- BEFORE : 테이블이 변경되기 전에 트리거 실행
- 동작 옵션 : 트리거가 실행되게 할 작업의 종류 지정
- INSERT / UPDATE / DELETE
- NEW | OLD : 트리거가 적용될 테이블의 별칭 지정
- NEW : 추가되거나 수정에 참여할 테이블
- OLD : 수정되거나 삭제 전 테이블
- FOR EACH ROW : 각 튜플마다 트리거 적용
트리거의 제거
DROP TRIGGER 트리거명;110. 사용자 정의 함수
사용자 정의 함수의 개요
- 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리
- 종료 시 처리 결과를 단일값으로 반환하는 절차형 SQL (출력 파라미터 없음)
- 사용자 정의 함수는 데이터베이스에 저장되어 DML문의 호출에 의해 실행
- 테이블 조작은 할 수 없고 SELECT를 통한 조회만 가능
사용자 정의 함수의 구성
DECLARE
BEGIN
CONTROL
SQL
EXEPTION
RETURN
END- SQL : SELECT문이 삽입되어 데이터 조회 작업 수행
- RETURN : 호출 프로그램에 반환할 값이나 변수 정의 (단일값)
사용자 정의 함수 생성
CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
[지역변수 선언]
BEGIN
사용자 정의 함수 BODY;
RETURN 반환값;
END;- 파라미터
- IN : 호출 프로그램이 사용자 정의 함수에게 값을 전달할 때 지정
- 매개변수명
- 자료형
- RETURN 반환값
사용자 정의 함수 실행
SELECT 사용자 정의 함수명 FROM 테이블;
INSERT INTO 테이블명(속성명) VALUES (사용자 정의 함수명);
DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;
UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;사용자 정의 함수 제거
DROP FUNCTION 사용자 정의 함수명;111. DBMS 접속 기술
DBMS 접속의 개요
- 사용자가 데이터 사용을 위해 응용 시스템을 이용하여 DBMS에 접근하는 것
- 응용 시스템은 매개 변수를 전달 받아 SQL을 실행하고 DBMS로부터 전달받은 결과를 사용자에게 전달하는 매개체 역할 수행
DBMS 접속 기술
- DBMS에 접근하기 위해 사용되는 API 또는 API 사용을 편리하게 도와주는 프레임워크
- JDBC (Java DataBase Connectivity)
- Java 언어로 다양한 종류의 데이터베이스에 접속하고, SQL문을 수행할 때 사용되는 표준 API
- ODBC (Open DataBase Connectivity)
- 데이터베이스에 접근하기 위한 표준 개방형 API로, 개발 언어에 관계없이 사용 가능
- MyBatis
- JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 접속 프레임워크
- SQL 문장을 분리하여 XML 파일을 만들고, Mapping을 통해 SQL 실행
동적 SQL (Dynamic SQL)
- 개발 언어에 삽입되는 SQL 코드를 문자열 변수에 넣어 처리하는 것
- 동적 SQL은 사용자로부터 SQL문의 일부 또는 전부를 입력받아 실행할 수 있음
- 정적 SQL에 비해 속도가 느리지만 유연한 개발이 가능
112. SQL 테스트
SQL 테스트의 개요
- SQL이 작성 의도에 맞게 원하는 기능을 수행하는지 검증하는 과정
단문 SQL 테스트
- DDL, DML, DCL이 포함되어 있는 SQL과 TCL (Transaction Control Language)을 테스트하는 것으로, 직접 실행
- DESCRIBE 명령어를 이용하여 DDL로 작성된 테이블이나 뷰의 속성 확인 가능
절차형 SQL 테스트
- 프로시저, 사용자 정의 함수, 트리거 등의 절차형 SQL은 디버깅을 통해 테스트
- SHOW 명령어를 통해 오류 확인
113. ORM (Object-Relational Mapping)
ORM의 개요
- ORM은 객체지향 프로그래밍의 객체와 관계형 데이터베이스의 데이터를 매핑하는 기술
- ORM으로 생성된 가상의 객체지향 데이터베이스는 프래그래밍 코드와 독립적이므로 재사용 및 유지보수가 용이
ORM 프레임워크
- JAVA : JPA, Hibernate, EclipseLink 등
- C++ : ODB, QxOrm 등
- Python : Django, SQLAlchemy 등
- iOS : DatabaseObjects, Core Data 등
- .NET : NHibernate, Dapper 등
- PHP : Doctrine, Propel, RedBean 등
ORM의 한계
- 프레임워크가 자동으로 SQL을 작성하기 때문에 의도대로 SQL이 작성되었는지 확인할 필요가 있음
- 객체지향적인 사용을 고려해야 하고, 프로젝트가 크고 복잡해질수록 ORM 기술 적용이 어려움
114. 쿼리 성능 최적화
쿼리 성능 최적화의 개요
- 데이터 입출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것
- 최적화 전 성능 측정 도구인 APM을 사용하여 최적화 할 쿼리 선정
- 최적화 할 쿼리에 대해 옵티마이저가 수립한 실행 계획 검토
- 옵티마이저
- RBO (Rule Based Optimizer, 규칙 기반 옵티마이저)
- 규칙에 정의된 우선순위를 기준으로 최적화
- 성능 기준 : 개발자의 SQL 숙련도
- CBO (Cost Based Optimizer, 비용 기반 옵티마이저)
- 액세스 비용을 기준으로 최적화
- 성능 기준 : 옵티마이저의 예측 성능
- RBO (Rule Based Optimizer, 규칙 기반 옵티마이저)
