[정보처리기사] Chapter 04. 데이터베이스 구축: SQL 활용

1. 프로시저(Procedure) / 용어정리, 정의

1.1. 프로시저 개요

 절차형 SQL(DB에서 연속적인 실행, 분기, 반복 등의 제어가 가능한 SQL )을 활용해 특정 기능을 수행하는 일종의 트랜잭션 언어로, 호출을 통해 실행되어 미리 저장해 놓은 SQL 작업을 수행한다.

 프로시저를 만들어 데이터베이스에 저장하면 여러 프로그램에서 호출하여 사용할 수 있다. 프로시저는 데이터베이스에 저장되어 수행되기 때문에 Stored Procedure라고 부른다.

 프로시저는 시스템의 일일 마감 작업, 일괄 작업 등에 주로 사용된다.

 

1.2. 프로시저 구성도

◍ DECLARE: 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언

◍ BEGIN / END: 프로시저의 시작과 종료를 의미

◍ CONTROL: 조건문 또는 반복문이 삽입되어 순차적으로 처리

◍ SQL: DML, DCL이 삽입되어 데이터 관리를 위한 조회/추가/수정/삭제 작업 수행

◍ EXCEPTION: BEGIN ~ END 안의 구문 실행 시 예외 처리 방법 정의

◍ TRANSACTION: 수행된 데이터 작업들을 DB에 적용할지, 취소할지 결정하는 처리부

 

1.3. 프로시저 생성 

표기 형식: CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터) [지역변수 선언] BEGIN     프로시저 BODY; END;

 

◍ CREATE PROCEDURE: 프로시저 생성 명령어

◍ OR REPLACE: 동일한 이름의 프로시저가 이미 있는 경우, 기존 프로시저를 대체하는 옵션 명령어

◍ 프로시저명: 생성하려는 프로시저의 이름 지정한다.

◍ 파라미터: 프로시저 파라미터로 다음과 같은 것들이 올 수 있다.

IN	호출 프로그램이 프로시저에게 값을 전달할 때 지정한다.
OUT	프로시저가 호출 프로그램에게 값을 반환할 때 지정한다.
INOUT	호출 프로그램이 프로시저에게 값을 전달하고, 프로시저 실행 후 호출 프로그램에게 값을 반환할 때 저장한다.
매개변수	호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름 지정한다.
자료형	변수의 자료형을 지정한다.

 

◍ 프로시저 BODY: 프로시저의 코드를 기록하는 부분이다. BEGIN과 END 사이에 하나 이상의 SQL문이 있어야 한다.

​

1.4. 프로시저 실행

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

표기 형식: EXECUTE 프로시저명; EXEC 프로시저명; CALL 프로시저명;

 

1.5. 프로시저를 제거

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

표기 형식: DROP PROCEDURE 프로시저명;

 

 

---

2. 트리거(Trigger) / 용어정리, 정의

2.1. 트리거 개요

 데이터베이스 시스템에서 데이터의 삽입(Insert), 갱신(Update), 삭제(Delete) 등의 이벤트(Event)가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL이다. 트리거는 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적을 위해 사용된다.

 트리거 구문에는 DCL(데이터 제어어)을 사용할 수 없고, DCL(데이터 제어어)이 포함된 프로시저나 함수를 호출하는 경우도 오류가 발생한다. 트리거에 오류가 있는 경우, 트리거가 처리하는 데이터에도 영향을 주기 때문에 트리거를 생성할 때 세심한 주의가 필요하다.

 

2.2. 트리거 구성

◍ DECLARE: 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부

◍ EVENT: 트리거가 실행되는 조건 명시

◍ BEGIN / END: 트리거의 시작과 종료

◍ CONTROL: 조건문 또는 반복문이 삽입되어 순차적으로 처리

◍ SQL: DML 문이 삽입되어 데이터 관리를 위한 조회/추가/수정/삭제 작업 수행

◍ EXECPTION: BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 처리하는 방법 정의

 

2.3. 트리거 생성

표기 형식: CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명 REFERENCING [NEW|OLD] AS 테이블명 FOR EACH ROW [WHEN 조건식] BEGIN     트리거 BODY; END;

 

2.3. CREATE TRIGGER: 트리거 생성 

◍ OR REPLACE: 동일한 이름의 트리거가 이미 존재하는 경우, 기존 트리거를 대체

◍ 동작시기 옵션: 트리거가 실행될 때를 지정, 옵션 BEFORE/AFTER(테이블이 변경되기 전/후에 트리거 실행)

◍ 동작옵션: 트리거가 실행되게 할 작업의 종류 지정

 

INSERT	테이블에 새로운 튜플을 삽입할 때 트리거 실행
DELETE	테이블의 튜플을 삭제할 때 트리거 실행
UPDATE	테이블의 튜플을 수정할 때 트리거 실행

 

◍ NEW / OLD: 트리거가 적용될 테이블의 별칭 지정

NEW	추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미
OLD	수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미

 

◍ FOR EACH ROW: 각 튜플마다 트리거를 적용하는 명령어

◍ WHEN 조건식: 트리거를 적용할 튜플의 조건 지정

◍ 트리거 BODY: 트리거의 본문 코드 부분이다. BEGIN~END 사이에 적어도 하나 이상의 SQL문이 있어야 한다. (없는 경우 에러가 발생한다. 

 

2.4. ​트리거 제거

표기 형식: DROP TRIGGER 트리거명;

 

 

---

3. 사용자 정의 함수 / 용어정리, 정의

3.1. 사용자 함수 개요

 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속 처리하며, 종료 시 처리 결과를 단일 값으로 반환하는 절차형 SQL이다. 

 사용자 함수는 데이터베이스에 저장되어 SELECT, INSERT, DELETE, UPDATE 등 DML 문의 호출에 의해 실행된다. RETURN을 통해 값을 반환하며, 출력 파라미터가 없다.

 주의할 것은 SELECT를 통해서는 데이터 조회만 가능하고, 테이블 조작(INSERT, DELETE, UPDATE)은 할 수 없다. 또한, 사용자 정의 함수는 프로시저를 호출해서 사용할 수 없다.

SUM(), AVG() 등의 내장 함수처럼 DML문에서 반환 값을 활용할 용도로 사용한다.

​

구분	프로시저	사용자 정의 함수
반환값	없는 경우 또는 1개 이상도 가능	1개
파라미터	입출력 가능	입력만 가능
사용 가능 명령문	DML, DCL	SELECT
호출	프로시저, 사용자 정의 함수	사용자 정의 함수
사용 방법	실행문	DML에 포함

 

3.2. 사용자 함수 구성

◍ DECLARE: 사용자 정의 함수의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부

◍ BEGIN / END: 사용자 정의 함수의 시작과 종료를 의미

◍ CONTROL: 조건문 또는 반복문이 삽입되어 순차적으로 처리 됨

◍ SQL: SELECT 문이 삽입되어 데이터 조회 작업 수행

◍ EXCEPTION: BEGIN ~ END  사이에 발생하는 예외 처리 방법 정의

◍ RETURN: 필수 사항, 호출 프로그램에 반환할 값이나 변수 정의

 

3.3. 사용자 함수 생성

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

 

◍ CREATE FUNCTION: 사용자 정의 함수 생성 명령어

◍ OR REPLACE: 동일한 이름의 사용자 정의 함수가 이미 존재한다면 그것을 대체

◍ 파라미터: 사용자 정의 함수의 파라미터는 다음과 같다.

 

IN	호출 프로그램이 사용자 정의 함수에게 값을 전달할 때 지정
매개변수명	호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정
자료형	변수의 자료형을 지정

 

◍ BODY: 사용자 정의 함수의 코드를 기록하는 부분이다. BEGIN과 END 사이에 적어도 하나의 SQL문이 있어야 한다.

◍ RETURN: 반환할 값이나 반환할 값이 저장된 변수를 호출 프로그램으로 반환한다.

​

3.4. 사용자 정의 함수 실행 명령어

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

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

 

3.5. 사용자 정의 함수 제거 명령어

표기 형식: DROP FUNCTION 사용자정의함수명;

 

 

---

4. DBMS 접속 기술

4.1. DBMS 접속 개요

 DBMS 접속은 사용자가 데이터를 사용하기 위해 응용 시스템을 이용하여 DBMS에 접근하는 것을 말한다. 응용 시스템은 사용자로부터 매개 변수를 전달받아 SQL을 실행하고, DBMS로부터 전달받은 결과를 사용자에게 전달하는 매개체 역할을 한다. 

 인터넷을 통해 구동되는 웹 응용 프로그램은 웹 응용 시스템을 통해 DBMS에 접근한다. 웹 응용 시스템은 웹 서버와 WAS로 구성되며, 서비스 규모가 작은 경우 웹 서버와 WAS를 통합하여 하나의 서버로 운용한다.

 

▶ 웹 응용 시스템의 구조

 

4.2. DBMS 접속 기술

DBMS에 접근하기 위해 사용하는 API 또는 API 사용을 편리하게 도와주는 프레임워크 등

​

1) JDBC (Java Database Connectivity)

◍  Java 언어로 다양한 종류의 DB에 접속하고 SQL문을 수행할 때 사용하는 표준 API

◍  Java SE(Standard Edition)에 포함, JDBC 클래스는 java, sql, javax.sql에 포함

◍  접속하려는 DBMS에 대한 드라이버 필요

​

2) ODBC (Open Database Connectivity)

◍  DB에 접근하기 위한 표준 개방형 API, 개발 언어에 관계 없이 사용 가능

◍  프로그램 내 ODBC 문장을 사용하여 MS-Access, DBase, DB2, Excel, Text 등 다양한 데이터베이스에 접근이 가능하다.

◍ 접속하려는 DBMS에 대한 드라이버가 필요하지만, 해당 DBMS의 인터페이스를 모르더라도 ODBC 문장을 사용하여 SQL을 작성하면 ODBC에 포함된 드라이버 관리자가 자동으로 연결한다.

​

3) MyBatis

◍ JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 접속 프레임워크이다. 

◍ JDBC로 DB에 접속하려면 다양한 메소드를 호출 및 해제해야 하는데, MyBatis는 이를 간소화함과 동시에 접속 기능을 더욱 강화했다.

◍  SQL 문장을 분리하여 XML 파일을 만들고, Mapping을 통해 SQL을 실행한다.

◍  SQL을 거의 그대로 사용할 수 있기 때문에 SQL 친화적인 환경에 적합하다.

 

4.3. 동적 SQL

 동적 SQL은 개발 언어에 삽입되는 SQL 코드를 문자열 변수에 넣어 처리하는 것으로, 조건에 따라 SQL 구문을 동적으로 변경하여 처리할 수 있다.

​ 동적 SQL은 사용자로부터 SQL 문의 일부 또는 전부를 입력받아 실행 할 수 있으며, 값이 입력되지 않을 경우 사용하는 NVL 함수*를 사용할 필요가 없다.

 응용 프로그램 수행 시 SQL이 변형될 수 있으므로 프리컴파일 할 때 구문 분석, 접근 권한 확인 등을 할 수 없다. 동적 SQL은 정적 SQL에 비해 속도는 느리지만, 상황에 따라 다양한 조건을 첨가하는 유연한 개발이 가능하다.

 

* NVL 함수: NVL(A, B) 형태의 함수로 A가 NULL인 경우 B를 반환하고 아니면 A를 반환한다. 동적 SQL에서는 원하는 조건에 따라 자유롭게 SQL문을 바꿀 수 있어 MVL 함수 없이 SQL 문을 구성할 수 있다. 

* 프리컴파일(Precompile): 프리컴파일은 고급언어를 기계어로 번역하는 컴파일 전에 수행하는 작업으로, 필요한 라이브러리를 불러오거나 코드에 삽입된 SQL문을 데이터베이스에 연결하는 작업을 수행한다. 

 

▶ 정적 SQL과 동적 SQL의 비교 

	정적 SQL (Static SQL)	동적 SQL (Dynamic SQL)
SQL 구성	커서(Cursor)를 통한 정척 처리	문자열 변수에 담아 동적 처리
개발 패턴	커서의 범위 안에서 반복문을 활용하여 SQL 작성	MVL 함수 없이 로직을 통해 SQL 작성
실행 속도	빠름	느림
사전 검사	가능	불가능

* 커서(Cursor)는 SQL 문의 실행 결과로 반환된 복수 개의 튜플들에 접근할 수 있도록 해주는 기능이다. 

 

 

---

5. SQL 테스트

5.1. SQL 테스트 개요

 SQL 테스트는 SQL이 작성된 의도에 맞게 원하는 기능을 수행하는지 검증하는 과정이다.  단문 SQL은 SQL 코드를 직접 실행한 후 결과를 확인하는 것으로 간단한 테스트가 가능하다. 절차형 SQL의 경우, 생성을 통해 구문 오류나 참조 오류의 존재 여부를 확인한다. 정상적으로 생성된 절차형 SQL은 디버깅을 통해 로직을 검증하고, 결과를 통해 최종적으로 확인한다.

 

5.2. 단문 SQL 테스트

 DDL, DML, DCL이 포함되어 있는 SQL과 TCL을 테스트하는 것으로, 직접 실행하여 결과물을 확인한다. 실행 시 오류나 경고가 발생할 경우 메시지를 참조하여 문제를 해결할 수 있다. 

 DESCRIBE 명령어를 이용하면 DDL로 작성된 테이블이나 뷰의 속성, 자료형, 옵션들을 확인 가능하다. 

DML로 변경한 데이터는 SELECT 문으로 데이터의 정상적인 변경 여부를 확인한다. DCL로 설정된 사용자 권한은 사용자 권한 정보가 저장된 테이블을 SELECT로 조회하거나, SHOW 명령어로 확인이 가능하다. 

 

◍ Oracle: SELECT*FROM DBA_ROLE_PRIVES WHERE GRANTEE = 사용자;

◍ MySQL: SHOW GRANTS FOR 사용자@호스트;

 

* TCL: 일부에서는 DCL 에서 트랜잭션을 제어하는 명령인 COMMIT 과 ROLLBACK 만을 따로 분리해서 TCL (Transaction Control Language) 라고 표현하기도 한다.

 

5.3. 절차형 SQL 테스트

 프로시저, 사용자 정의 함수, 트리거 등의 절차형 SQL은 디버깅을 통해 기능의 적합성 여부를 검증하고, 실행을 통해 결과를 확인하는 테스트를 수행한다.

 많은 코드로 구성된 절차형 SQL의 특성상 오류 및 경고 메시지가 상세히 출력되지 않으므로, SHOW 명령어(SHOW ERRORS*;)를 통해 오류 내용을 확인하고 문제를 수정한다. 

 데이터베이스에 변화를 줄 수 있는 SQL 문은 주석으로 처리하고, 출력문을 이용하여 화면에 출력하며 확인해야 한다.

 

▶ Oracle

◍ DBMS_OUTPUT.ENABLE;  : 화면에 출력하기 위해 DBMS_OUTPUT 패키지를 불러온다.

◍ DBMS_OUTPUT.PUT_LINE(데이터);   : ‘데이터’에 넣은 변수나 값을 화면에 출력한다. 

 

▶ MySQL

◍ SELECT 데이터;   : ‘데이터’에 넣은 변수나 값을 화면에 출력한다. 

◍ 디버깅이 완료되면 출력문을 삭제하고, 주석 기호를 삭제한 후 절차형 SQL을 실행하여 결과를 검토한다. 

 

 

---

6. ORM(Object-Relational Mapping)

6.1. ORM 개요

 ORM(Object-Relational Mapping)은 객체 지향 프로그래밍의 객체(Object)와 ‘관계형 데이터베이스’의 데이터를 연결(Mapping)하는 기술이다. 객체 지향 프로그래밍에서 사용할 수 있는 가상의 객체 지향 데이터베이스를 만들어 프로그래밍 코드와 데이터를 연결한다. 

 ORM으로 생성된 가상의 객체 지향 데이터베이스는 프로그래밍 코드 또는 데이터베이스와 독립적이므로 재사용 및 유지보수가 용이하다. 

 ORM은 SQL 코드를 직접 입력하지 않고, 선언문이나 할당 같은 부수적인 코드가 생략되기 때문에 직관적이고 간단하게 데이터를 조작할 수 있다.

​

6.2. ORM 프레임워크

 ORM 프레임워크는 ORM을 구현하기 위한 구조와 구현을 위해 필요한 여러 기능들을 제공하는 소프트웨어를 의미한다. 

 

◍ JAVA: JPA, Hibernate, EclipseLink, DataNucleus, Ebean 등

◍ C++: ODB, QxOrm 등

◍ Python: Django, SQLAlchemy, Storm 등

◍ iOS: DatabasObjects, Core Data 등

◍ .NET: NHibernate, DatabaseObjects, Dapper 등

◍ PHP: Doctrine, Propel, RedBean 등

​

6.3. ORM의 한계

ORM은 프레임워크가 자동으로 SQL을 작성하기 때문에 의도대로 작성되었는지 작성자가 재차 확인해야 한다.

 객체지향적인 사용을 고려하고, 설계된 데이터베이스가 아닌 경우 프로젝트가 크고 복잡해질수록 ORM 기술을 적용하기가 어려워진다.

 기존 기업들은 ORM을 고려하지 않은 DB를 사용하기 때문에 ORM에 적합하게 변환하려는 시간과 노력이 필요하다.

 

 

---

7. 쿼리 성능 최적화

7.1. 쿼리 성능 최적화 개요

 쿼리 성능 최적화는 데이터 입·출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것을 말한다. 쿼리 성능을 최적화하기 전에 성능 측정 도구인 APM을 사용하여 최적화 할 쿼리를 선정한다. 최적화 대상 쿼리에 대해 옵티마이저가 수립한 실행 계획을 검토하고 SQL 코드와 인덱스를 재구성한다.

 

* APM (Application Performance Management/Monitoring): 애플리케이션의 성능 관리를 위해 접속자, 자원 현황, 트랜잭션 수행 내역, 장애 진단 등 다양한 모니터링 기능을 제공하는 도구

 

7.2. 실행 계획, Execution Plan

 DBMS의 옵티마이저가 수립한 SQL 코드의 실행 절차와 방법을 의미한다. EXPLAIN 명령어를 통해 그래픽이나 텍스트로 표현 되는 실행 계획을 확인할 수 있다. 실행 계획에는 요구사항들을 처리하기 위한 연산 순서가 적혀 있고, 연산에는 조인, 테이블 검색, 필터, 정렬 등이 있다.

 

7.3. 쿼리 성능 최적화

 쿼리 성능 최적화는 실행 계획에 표시된 연산 순서, 조인 방식, 테이블 조회 방법 등을 참고하여 SQL문이 더 빠르고 효율적으로 작동하도록 SQL 코드와 인덱스를 재구성하는 것을 말한다.

​

1) SQL 코드 재구성

◍ WHERE 절을 추가하여 일부 레코드만 조회하게 함으로써 조회에 들어가는 비용을 줄인다.

◍ WHERE 절에 연산자가 포함되면 INDEX를 활용하지 못하므로 가능한 연산자 사용을 자제한다.

◍ 서브 쿼리에 특정 데이터가 존재하는지 확인할 때는 IN보다 EXISTS*를 활용한다.

◍ 옵티마이저의 실행 계획이 잘못되었다고 판단되는 경우, 힌트*를 활용하여 실행 계획의 액세스 경로 및 조인 순서를 변경한다.

​

* EXISTS: EXIST는서브 쿼리의 모든 데이터를 확인하는 IN과 달리 데이터의 존재여부가 확인되면 검색이 종료되므로 IN보다 처리속도가 빠르다.

* 힌트(Hint): 힌트는 SQL문에 추가되어 테이블 접근 순서를 변경하거나, 인덱스 사용을 강제하는 등의 문장을 말한다. 이러한 힌트는 실행 계획에 영향을 줄 수 있다.

 

2) 인덱스 재구성

◍ SQL 코드에서 조회되는 속성과 조건들을 고려하여 인덱스를 구성한다.

◍ 실행 계획을 참고하여 인덱스를 추가하거나 기존 인덱스의 순서를 변경한다.

◍ 인덱스의 추가 및 변경은 해당 테이블을 참조하는 다른 SQL문에도 영향을 주기 때문에 신중히 결정한다. 

◍ 단일 인덱스로 쓰거나 수정 없이 읽기만 사용되는 테이블의 경우 IOT(Index-Organized Table)로 구성하는 것을 고려한다. 

◍ 찾는 과정을 거치는데, IOT는 인덱스 안에 테이블 데이터를 직접 삽입하여 저장하기 때문에 주소 얻는 과정을 생략하여 빠른 조회 가능하다.

◍ 불필요한 인덱스는 제거한다. 

 

* IOT: 일반적으로 인덱스가 있는 테이블을 조회할 때, 인덱스를 검색하여 주소를 얻으면 주소를 다시 찾아가는 과정을 거치지만, IOT는 인덱스 안에 테이블 데이터를 직접 삽입하여 저장함으로써 주소를 얻는 과정이 생략된다. 때문에 더욱 빠른 조회가 가능하다.