[정보처리기사] 3과목 데이터베이스 구축 : 4장. SQL 활용

1. 프로시저 ***
2. 트리거
3. 사용자 정의 함수
4. DBMS 접속 기술
5. SQL 테스트
6. ORM (Object-Relational Mapping)
7. 쿼리 성능 최적화

 

 

 

1. 프로시저 ***

 

프로시저의 개요

- 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어로, 호출을 통해 실행되어 미리 저장해 놓은 SQL 작업

- 데이터베이스에 저장되어 수행되기 때문에 Stored 프로시저라고 불림

- 시스템의 일일 마감 작업, 일괄 작업 등에 주로 사용됨

​

프로시저 생성

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

- OR REPLACE: 선택적인 예약어로 이 예악어를 사용하면 동일한 프로시저 이름이 이미 존재하는 경우, 기존의 프로시저를 대체할 수 있음

- 파라미터

(1) IN: 호출 프로그램이 프로시저에게 값을 전달할 때 지정

(2) OUT: 프로시저가 호출 프로그램에게 값을 반환할 때 지정

(3) INOUT: 호출 프로그래밍 프로시저에게 값을 전달하고, 프로시저 실행 후 호출 프로그램에 값을 반환할 때 지정

​

프로시저 실행

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

프로시저 제거

DROP PROCEDURE 프로시저명;

​

 

 

2. 트리거

 

트리거의 개요

- 데이터베이스 시스템에서 데이터의 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

- 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용됨

- 트리거의 구문에는 DCL을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에도 오류가 발생함

- 트리거에 오류가 있는 경우 트리거가 처리하는 데이터에도 영향을 미치므로 트리거를 생성할 때 세심한 주의 필요

​

트리거의 생성

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

 

- 동작시기 옵션: 트리거가 실행될 때를 지정함

(1) AFTER: 테이블이 변경된 후에 트리거가 실행됨

(2) BEFORE: 테이블이 변경되기 전에 트리거가 실행됨

 

- 동작 옵션: 트리거가 실행되게 할 작업의 종류, INSERT, DELETE, UPDATE가 있음

 

- NEW | OLD: 트리거가 적용될 테이블의 별칭을 지정

 

(1) NEW: 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미

(2) OLD: 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미

 

- FOR EACH ROW: 각 튜플마다 트리거를 적용한다는 의미

- WHEN 조건식: 선택적인 예약어로 트리거를 적용할 튜플의 조건을 지정

​

트리거의 제거

DROP TRIGGER 트리거명;

​

 

 

3. 사용자 정의 함수

 

사용자 정의 함수의 개요

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

- 데이터베이스에 저장되어 DML문의 호출에 의해 실행됨

- 예약어 RETURN을 통해 값을 반환하기 때문에 출력 파라미터가 없음

- INSERT, DELETE, UPDATE를 통한 테이블 조작은 할 수 없고 SELECT를 통한 조회만 할 수 있음

- 프로시저를 호출하여 사용할 수 없음

​

사용자 정의 함수 생성

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

 

사용자 정의 함수 실행

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

 

사용자 정의 함수 제거

DROP FUNCTION 사용자_정의_함수명;

​

 

 

4. DBMS 접속 기술

 

DBMS 접속 기술의 개요

- 사용자가 데이터를 사용하기 위해 응용 시스템을 이용하여 DBMS에 접근하는 것

- 응용 시스템은 사용자로부터 매개 변수를 전달받아 SQL을 실행하고 DBMS로부터 전달받은 결과를 사용자에게 전달하는 매개체 역할을 수행

- 인터넷을 통해 구동되는 웹 응용 프로그램은 웹 응용 시스템을 통해 DBMS에 접근함

- 웹 응용 시스템은 웹 서버와 웹 애플리케이션 서버로 구성되며, 서비스 규모가 작은 경우 웹 서버와 웹 애플리케이션 서버를 통합하여 하나의 서버만으로 운용할 수 있음

- 사용자 ↔ 웹 서버 ↔ WAS ↔ DBMS

​

DBMS 접속 기술

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

 

1) JDBC

- Java 언어로 다양한 종류의 데이터베이스에 접속하고 SQL문을 수행할 때 사용되는 표준 API

- Java SE에 포함되어 있으며, JDBS 클래스는 java, sql, javax.sql에 포함되어 있음

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

2) ODBC

- 데이터베이스에 접근하기 위한 표준 개방형 API로, 개발 언어에 관계없이 사용할 수 있음

- ODBC 문장을 사용하여 MS-Access, DBase, DB2, Excel, Text 등 다양한 데이터베이스에 접근할 수 있음

- ODBC도 접속하려는 DBMS에 맞는 드라이버가 필요하지만, 접속하려는 DBMS의 인터페이스를 알지 못하더라도 ODBC 문장을 사용하여 SQL을 작성하면 ODBC에 포함된 드라이버 관리자가 해당 DBMS의 인터페이스에 맞게 연결해 주므로 DBMS의 종류를 몰라도 됨

3) MyBatis

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

- JDBC로 데이터베이스에 접속하려면 다양한 메소드를 호출하고 해제해야 하는데, MyBatis는 이를 간소화했고 접속 기능을 더욱 강화함

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

- SQL을 거의 그대로 사용할 수 있어 SQL 친화적인 국내 환경에 적합하여 많이 사용됨

​

동적 SQL

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

- 사용자로부터 SQL문의 일부 또는 전부를 입력받아 실행할 수 있음

- 값이 입력되지 않을 경우 사용하는 NVL 함수를 사용할 필요가 없음

- 응용 프로그램 수행 시 SQL이 변형될 수 있으므로 프리컴파일 할 때 구문 분석, 접근 권한 확인 등을 할 수 없음

- 정적 SQL에 비해 속도가 느리지만, 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발 가능

 

 

 

5. SQL 테스트

 

SQL 테스트의 개요

- SQL이 작성 의도에 맞게 원하는 기능을 수행하는지 검증하는 과정

- 단문 SQL은 SQL 코드를 직접 실행한 후 결과를 확인하는 것으로 간단히 테스트가 가능

- 절차형 SQL은 테스트 전에 생성을 통해 구문 오류나 참조 오류의 존재 여부를 확인

- 정상적으로 생성된 절차형 SQL은 디버깅을 통해 로직을 검증하고, 결과를 통해 최종적으로 확인

​

1) 단문 SQL 테스트

- DDL, DML, DCL이 포함되어 있는 SQL과 TCL을 테스트하는 것으로 직접 실행하여 결과물을 확인

- 실행 시 오류나 경고가 발생할 경우 메시지를 참조하여 문제를 해결

- DESCRIBE 명령어를 이용하면 DDL로 작성된 테이블이나 뷰의 속성, 자료형, 옵션들을 확인할 수 있음 (DESC [개체명];)

- DML로 변경한 데이터는 SELECT문으로 데이터의 정상적인 변경 여부를 확인할 수 있음

- DCL로 설정된 사용자 권한은 사용자 권한 정보가 저장된 테이블을 SELECT로 조회하거나, SHOW 명령어로 확인할 수 있음

(1) Oracle: SELECT * FROM DBA_ROLE_PRIVES WHERE GRANTEE=사용자;

(2) MySQL: SHOW GRANTS FOR 사용자@호스트;

​

2) 절차형 SQL 테스트

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

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

(SHOW ERRORS;)

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

(1) Oracle: DBMS_OUTPUT.ENABLE / DBMS_OUTPUT.PUT_LINE(데이터)

(2) MySQL: SELECT 데이터;

 

 

 

6. ORM (Object-Relational Mapping)

 

ORM의 개요

- 객체지향 프로그래밍의 객체와 관계형 데이터베이스의 데이터를 연결하는 기술

- 객체지향 프로그래밍에서 사용할 수 있는 가상의 객체지향 데이터베이스를 만들어 프로그래밍 코드와 데이터를 연결

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

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

​

ORM 프레임워크

- ORM을 구현하기 위한 구조와 구현을 위해 필요한 여러 기능들을 제공하는 소프트웨어

(1) JAVA: JPA, Hibernate, EclipseLink, DataNucleus, Ebean

(2) C++: ODB, QxOrm

(3) Python: Django, SQLAlchemy, Storm

(4) iOS: DatabaseObjects, Core Data

(5) .NET: NHibernate, DatabaseObjects. Dapper

(6) PHP: Doctrine. Propel. RedBean

​

ORM의 한계

- 프레임워크가 자동으로 SQL을 작성하기 때문에 의도대로 SQL이 작성되었는지 확인해야 함

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

- 기존의 기업들은 ORM을 고려하지 않은 데이터베이스를 사용하고 있기 때문에 ORM에 적합하게 변환하려면 많은 시간과 노력이 필요함

 

 

 

7. 쿼리 성능 최적화

 

쿼리 성능 최적화의 개요

- 데이터 입출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것

- 쿼리 성능을 최적화하기 전에 성능 측정 도구인 APM을 사용하여 최적화 할 쿼리를 선정

- 최적화 할 쿼리에 대해 옵티마이저가 수립한 실행 계획을 검토하고 SQL 코드와 인덱스를 재구성

​

- RBO / CBO

- RBO: 규칙 기반 옵티마이저

- CBD: 비용 기반 옵티마이저

​

실행 계획

- DBMS의 옵티마이저가 수립한 SQL 코드의 실행 절차와 방법을 의미

- EXPLAIN 명령어를 통해 확인할 수 있으며, 그래픽이나 텍스트로 표현됨

- 실행 계획에는 요구사항들을 처리하기 위한 연산 순서가 적혀있으며, 연산에는 조인, 테이블 검색, 필터, 정렬 등이 있음

​

쿼리 성능 최적화

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

​

1) SQL 코드 재구성

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

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

- 서브 쿼리에 특정 데이터가 존재하는지 확인할 떄는 IN 보다는 EXISTS를 활용

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

​

2) 인덱스 재구성

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

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

- 인덱스의 추가 및 변경은 해당 테이블을 참조하는 다른 SQL문에도 영향을 줄 수 있으므로 신중히 결정

- 단일 인덱스로 쓰거나 수정 없이 읽기로만 사용되는 테이블의 경우 IOT로 구성하는 것을 고려

- 불필요한 인덱스를 제거