정보처리기사 3과목 데이터베이스 구축 - 4장 SQL 활용 요점 정리

1. 프로시저

- 절차형 SQL을 활용하여 특정 기능 수행하는 일종의 트랜잭션 언어

- 호출 통해 실행되어 미리 저장해 놓은 SQL 작업 수행, 여러 프로그램에서 호출하여 사용

- DB에 저장되어 수행되기 때문에 스토어드 프로시저라고 불림, 시스템의 일일 마감 작업, 일관(Batch) 작업 등에 쓰임

● 구성

- DECLARE(필수) : 프로시저의 명칭, 변수, 인수, 데이터 타입 정의하는 선언부

- BEGIN / END(필수) : 프로시저의 시작과 종료 의미

- CONTROL : 조건문 or 반복문이 삽입되어 순차적으로 처리, 실행 흐름 제어

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

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

- TRANSACTION : 수행된 데이터 작업들을 DB에 저장할지 취소할지 결정, 적용 여부 결정

● 프로시저의 생성 : CREATE PROCEDURE 명령어 사용

CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)

[지역번수 선언]

BEGIN

프로시저 BODY;

END;

- OR REPLACE : 선택적인 예약어, 동일한 프로시저 이름이 이미 존재하는 경우 기존의 프로시저 대체

- 파라미터

* IN : 호출 프로그램이 프로시저에게 값 전달

* OUT : 프로시저가 호출 프로그램에게 값 전달

* INOUT : 호출 프로그램이 프로시저에게 값 전달하고, 프로시저 실행 후 호출 프로그램에게 값 반환 시 지정

* 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정

* 자료형 : 변수의 자료형을 지정

- 프로시저 BODY

- 프로시저 코드 기록, BEGIN 과 END 사이에 적어도 하나의 SQL문이 존재

● 프로시저 실행 : EXECUTE 프로시저명;, EXEC 프로시저명;, CALL 프로시저명;

● 프로시저 제거 : DROP PROCEDURE 프로시저명;

​

2. 트리거

: 삽입, 갱신, 삭제 등의 이벤트 발생 시마다 관련 작업이 자동으로 수행되는 절차형 SQL

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

- DCL 사용 불가능, DCL이 포함된 프로시저나 함수를 호출하는 경우에도 오류 발생

- 트리거에 오류 있는 경우 트리거가 처리하는 데이터 영향 미침, 외부에서 필드나 데이터 주고받지 않음​

● 구성

- DECLARE(필수)

- EVENT(필수) : 트리거가 실행되는 조건 명시

- BEGIN / END(필수)

- CONTROL

- SQL

- EXCEPTION

● 트리거 생성 : CREAT TRIGGER 명령어 사용

CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명

REFERENCING [NEW | OLD] AS 테이블명

FOR EACH ROW

[WHEN 조건식]

BEGIN

트리거 BODY;

END;

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

* AFTER : 테이블이 변경된 후 트리거 실행

* BEFORE : 테이블 변경 전 트리거 실행

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

* INSERT : 새로운 튜플 삽입

* DELETE : 튜플 삭제

* UPDATE : 튜플 수정

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

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

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

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

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

● 트리거의 제거 : DROP TRIGGER 트리거명;

- 트리거는 데이터 변경 시 자동으로 수행되므로 호출문 존재 X

​

3. 사용자 정의 함수

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

- DB에 저장되어 DML문의 호출에 의해 실행, 예약어 RETURN을 통해 반환되기 때문에 출력 파라미터 없음

- 테이블 조작(DML) 불가능, SELECT를 통한 조회만 가능, 프로시저 호출 사용 불가능,

프로시저 VS 사용자 정의 함수

● 사용자 정의 함수의 구성

- 프로시저의 구성과 유사, 프로시저의 구성 + RETURN(호출 프로그램에 반환할 값이나 변수 정의)

● 사용자 정의 함수 생성 : CREAT FUNCTION

CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)

[지역변수 선언]

BEGIN

사용자 정의 함수 BODY;

RETURN 반환값;

END;

- OR REPLACE

- 파라미터

* IN

* 매개변수명

* 자료형

- 사용자 정의 함수 BODY

- RETURN 반환값

● 사용자 정의 함수 실행 : DML에서 속성명이나 값이 놓일 자리를 대체하여 사용

● 사용자 정의 함수 제거 : DROP FUNCTION 사용자 정의 함수명;

​

4. DBMS 접속 기술

● 개요

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

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

- 웹 응용 프로그램은 웹 응용 시스템을 통해 DBMS에 접근, 웹 응용 시스템은 웹 서버와 WAS로 구성(규모 작을 시 통합)

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

● DBMS 접속 기술

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

- JDBC : JAVA 언어, 썬 마이크로시스템에서 출시, 접속하려는 DBMS에 대한 드라이버 필요

- ODBC : 표준 개방형 API, 개발 언어와 상관없음, 마이크로소프트에서 출시, 다양한 DBMS 접근 가능, DBMS 종류 몰라도 됨

- My Batis : JDBC 코드 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈소스 접속 프레임워크, SQL 거의 그대로 사용하여 국내 환경에 적합, SQL 문장 분리하여 XML 파일을 만들고 Mapping을 통해 SQL 실행

● 동적 SQL (Dynamic SQL) : 사용자가 응용 프로그램 실행시킨 후 입력란에 SQL을 직접 입력하여 결과를 확인 가능한 것

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

- NVL 함수 사용 필요 없음, 응용 프로그램 수행 시 SQL이 변형될 수 있어 프리컴파일할 때 구문 분석, 접근 권한 확인 불가능

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

정적 SQL vs 동적 SQL

5. SQL 테스트

● 개요

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

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

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

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

● 단문 SQL 테스트

- DDL, DML, DCL이 포함되어 있는 SQL과 TCL을 직접 실행하여 테스트

- 오류 및 경고 발생 경우 메시지 참조하여 해결

- DESCRIBE 명령어 이용 시 DDL로 작성된 테이블, 뷰 속성, 자료형, 옵션 확인 가능(DESC, 개체의 정보 확인)

- DML로 변경 데이터는 SELECT로 정상적인 변경 여부 확인 가능

- DCL로 설정된 사용자 권한은 SELECT 문으로 조회하거나 SHOW 명령어로 확인 가능

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

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

● 절차형 SQL 테스트

- 디버깅을 통해 기능의 적합성 여부 검증, 실행을 통해 결과를 확인하는 테스트 수행

- SHOW 명령어 통해 오류 내용 확인

- DB에 변화를 줄 수 있는 SQL 문은 주석 처리 후 출력문을 이용하여 결과 확인

- Oracle : DBMS_OUTPUT.ENABLE / DBMS_I=OUTPUT.PUT_LINE(데이터);

- MySQL : SELECT 데이터;

- 디버깅 완료 시 출력문 삭제, 주석 기호 삭제 후 절차형 SQL 실행하여 결과 검토

​

6. ORM

- 객체지향 프로그래밍의 객체와 RDB이 데이터 매핑하는 기술, 생성된 객체들 DB와 독립적

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

- 프로그래밍 코드 or DB와 독립적이므로 재사용 및 유지 보수 용이

- SQL 코드를 직접 입력하지 않기 때문에 직관적이고 간단하게 데이터 조작 가능

● ORM 프레임워크

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

- C++ : ODB, QxOrm 등

- Python : Django, SQLAlchemy, Storm 등

- iOS : DatabaseObjects, Core Data 등

- .NET : NHibernate, DatabaseObjects, Dapper 등

- PHP : Doctrine, Propel, RedBean 등

● ORM의 한계

- 프레임워크가 자동으로 작성하기 때문에 의도대로 작성되었는지 확인할 필요 있음

- 객체지향적 사용을 고려, 설계한 DB가 아닌 경우 프로젝트가 크고 복잡할수록 ORM 기술을 적용하기 어려움

- 기존의 기업들은 ORM 고려하지 않은 DB를 사용하고 있어 ORM에 적합하게 변환하려면 많은 시간과 노력이 필요

​

7. 쿼리 성능 최적화

● 개요

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

- 최적화 전 APM 사용하여 최적화할 쿼리 선정, 선정된 쿼리에서 옵티마이저가 실행 계획 검토 및 SQL 코드와 인덱스 재구성

RBO vs CBO

● 실행 계획

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

- EXPLAIN 명령어를 통해 확인, 그래픽이나 텍스트로 표현

- 요구 사항 처리 위한 연산 순서가 적혀있고, 연산에는 조인, 테이블 검색, 필터, 정렬 등이 있음

● 쿼리 성능 최적화

- 연산 순서, 조인 방식, 테이블 조회 방법 등을 참고하여 SQL 문이 더 빠르고 효율적으로 작동하도록 코드와 인덱스 재구성

- SQL 코드 재구성

* WHERE 절을 추가하여 일부 레코드만 조회

* WHERE 절에 연산자 사용 자제

* 특정 데이터 확인 시 IN 보다 EXSIST 사용

* 힌트를 활용하여 실행 계획의 액세스 경로 및 조인 순서 변경

- 인덱스 재구성

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

* 인덱스를 추가하거나 기존 인덱스의 열 순서 변경

* 단일 인덱스로 쓰거나 수정 없이 읽기로만 사용되는 경우 IOT로 구성

* 불필요한 인덱스 제거