정보처리기사 - SQL 응용 #128~132

128. 커서(Cursor)

128.1 커서(Cursor)

쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터(Pointer)

• 커서의 수행은 열기(Open), 패치(Fetch), 닫기(Close)의 세 단계로 진행

128.2 묵시적 커서(Implicit Cursor)

DBMS에 의해 내부에서 자동으로 생성되어 사용되는 커서

• 커서의 속성을 조회하여 사용된 쿼리 정보를 열람하는 것이 가능
• 수행된 쿼리문의 정상적인 수행 여부를 확인하기 위해 사용
• 속성의 종류

종류	내용
SQL%FOUND	쿼리 수행의 결과로 패치(Fetch)된 튜플 수가 1개 이상이면 TRUE
SQL%NOTFOUND	쿼리 수행의 결과로 패치(Fetch)된 튜플 수가 0개이면 TRUE
SQL%ROWCOUNT	쿼리 수행의 결과로 패치(Fetch)된 튜플 수를 반환
SQL%ISOPEN	- 커서가 열린(Open) 상태이면 TRUE - 묵시적 커서는 자동으로 생성된 후 자동으로 닫히기 때문에 항상 FALSE

128.3 명시적 커서(Explicit Cursor)

사용자가 직접 정의해서 사용하는 커서

• 쿼리문의 결과를 저장하여 사용함으로써 동일한 쿼리가 반복 수행되어 데이터베이스 자원이 낭비되는 것을 방지
• 커서는 기본적으로 '열기(Open) - 패치(Fetch) - 닫기(Close)' 순으로 이루어지며, 명시적 커서를 사용하기 위해서는 열기(Open) 단계 전에 선언(Declare)해야 함

128.3.1 선언(Declare) 형식

1  CURSOR 커서명(매개변수1, 매개변수2, ···)
   IS
2  SELECT문;

1. CURSOR, IS는 예약어로 그대로 적으며, 커서명은 사용자가 임의로 설정
   • (매개변수1, 매개변수2, ···)는 SELECT문의 WHERE절에 사용할 수 있으며 생략이 가능
2. 커서가 열릴(Open) 때 수행할 SELECT문을 작성
   • 커서는 SELECT문의 실행 결과가 저장된 곳의 시작 위치를 가리킴

128.3.2 열기(Open) 형식

1  OPEN 커서명(매개변수1, 매개변수2, ···);

1. 커서를 사용하기 전에 반드시 적음
   • OPEN은 예약어로 그대로 적고, 선언 시 입력한 커서명과 매개변수를 적음

128.3.3 패치(Fetch) 형식

1  FETCH 커서명 INTO 변수1, 변수2 ···;

1. FETCH, INTO는 예약어로 그대로 적음
   • 커서명과 커서에 저장된 튜플들의 각 속성과 같은 자료형을 가진 변수를 적고 데이터를 가져옴

128.3.4 닫기(Close) 형식

1  CLOSE 커서명;

1. 사용된 커서는 메모리 해제를 위해 반드시 닫아야 함
   • CLOSE는 예약어로 그대로 적고, 사용한 커서명을 뒤에 적음

128.3.5 예제

• 다음은 테이블로부터 id가 20보다 크거나 같은 튜플의 name을 출력하는 절차형 SQL을 PL/SQL로 구현한 것임

   DECLARE
1      p_name employee.name%TYPE;
2      CURSOR cur_name(ff INT)
       IS
3      SELECT name FROM employee WHERE id>=ff;

   BEGIN
4      OPEN cur_name(20);
5      LOOP
6          FETCH cur_name INTO p_name;
7          EXIT WHEN cur_name%NOTFOUND;
8          DBMS_OUTPUT.PUT_LINE(p_name);
       END LOOP;
9      CLOSE cur_name;
   END;

1. 테이블의 'name' 속성의 자료형과 동일한 변수 p_name을 선언
2. 전달받은 값을 ff에 저장한 후 사용하는 커서 cur_name을 선언
3. 2번에서 선언된 커서가 OPEN될 때 수행할 SELECT문을 정의
   • 테이블의 'id' 속성이 ff보다 크거나 같은 튜플의 'name' 속성을 조회
4. 커서 cur_name을 열음
   • 20은 2번의 ff로 전달되어 "SELECT name FROM employee WHERE id>=20;"이 수행되고, cur_name에는 결과가 저장된 메모리의 시작 위치가 저장됨
5. LOOP문의 시작
   • 6~8번 문장을 반복하여 수행
6. 커서 cur_name으로부터 데이터를 가져와 p_name에 저장
7. cur_name의 %NOTFOUND 속성이 TRUE를 반환
   • 더 불러올 값이 없으면 LOOP문을 탈출
8. p_name의 값을 화면에 출력
9. 커서 cur_name을 닫음

---

129. DBMS 접속(Connection)

129.1 DBMS 접속

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

• 응용 시스템은 사용자로부터 매개 변수를 전달받아 SQL을 실행하고 DBMS로부터 전달받은 결과를 사용자에게 전달하는 매개체 역할을 수행
• 인터넷을 통해 구동되는 웹 응용 프로그램은 웹 응용 시스템을 통해 DBMS에 접근
• 웹 응용 시스템은 웹 서버와 웹 애플리케이션 서버(WAS)로 구성

129.2 DBMS 접속 기술

접속 기술	특징
JDBC (Java DataBase Connectivity)	- Java 언어로 다양한 종류의 데이터베이스에 접속할 때 사용하는 표준 API - 1997년 2월 썬 마이크로시스템에서 출시 - 접속하려는 DBMS에 대한 드라이버가 필요
ODBC (Open DataBase Connectivity)	- 개발 언어에 관계없이 데이터베이스에 접근하기 위한 표준 개방형 API - 1992년 9월 마이크로소프트에서 출시 - 접속하려는 DBMS의 인터페이스에 관계없이 ODBC 문장을 사용하여 접속이 가능
MyBatis	- JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 접속 프레임워크 - SQL 문장을 분리하여 XML 파일을 만들고, Mapping을 통해 SQL을 실행

• SQL Mapping : SQL로 호출되는 테이블이나 열 데이터를 개발하려는 언어의 객체에 맞도록 변환하여 연결하는 것을 의미

129.3 동적 SQL(Dynamic SQL)

다양한 조건에 따라 SQL 구문을 동적으로 변경하여 처리할 수 있는 SQL 처리 방식

• 동적 SQL은 사용자로부터 SQL문의 일부 또는 전부를 입력받아 실행할 수 있음
• 응용 프로그램 수행 시 SQL이 변형될 수 있으므로 프리컴파일 할 때 구문 분석, 접근 권한 확인 등을 할 수 없음
  • 프리컴파일(Precompile) : 고급언어를 기계어로 번역하는 컴파일(Compile) 전에 수행하는 작업으로, 필요한 라이브러리를 불러오거나 코드에 삽입된 SQL문을 DB와 연결하는 작업을 수행
• 동적 SQL은 정적 SQL에 비해 속도가 느리지만, 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발이 가능

---

130. SQL 테스트

130.1 SQL 테스트

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

• 단문 SQL은 SQL 코드를 직접 실행한 후 결과를 확인하는 것으로, 간단히 테스트가 가능
• 절차형 SQL은 테스트 전에 생성을 통해 구문 오류(Syntax Error)나 참조 오류의 존재 여부를 확인
  • 구문 오류(Syntax Error) : 잘못된 문법으로 작성된 SQL문을 실행하면 출력되는 오류
• 정상적으로 생성된 절차형 SQL은 디버깅을 통해 로직을 검증하고, 결과를 통해 최종적으로 확인
  • 디버깅(Debugging) : 오류를 잡기 위해 소스 코드 한 줄 한 줄 추적해 가며 변수 값의 변화를 검사하는 작업

130.2 단문 SQL 테스트

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

• TCL(Transaction Control Language) : 트랜잭션을 제어하는 COMMIT, ROLLBACK, SAVEPOINT 명령을 가리키는 용어
• DDL로 작성된 개체는 DESCRIBE 명령어를 이용하여 속성, 자료형, 옵션들을 확인할 수 있음
  • DESC [개체명]
  • DESCRIBE, DESC : 개체의 정보를 확인할 때 사용하는 명령어
• DML로 변경한 데이터는 SELECT문으로 데이터의 정상적인 변경 여부를 확인할 수 있음
• DCL로 설정된 사용자 권한은 사용자 권한 정보가 저장된 테이블을 조회하여 확인할 수 있음

130.3 절차형 SQL 테스트

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

• 많은 코드로 구성된 절차형 SQL의 특성상 오류 및 경고 메시지가 상세히 출력되지 않으므로 SHOW 명령어를 통해 오류 내용을 확인하고 문제를 수정
  • 형식 : SHOW ERRORS;
• 데이터베이스에 변화를 줄 수 있는 SQL문은 주석으로 처리하고, 출력문을 이용하여 화면에 출력하여 확인
• 디버깅이 완료되면 출력문을 삭제하고, 주석 기호를 삭제한 후 절차형 SQL을 실행하여 결과를 검토
• 주석(Comment) : 설명을 위해 입력된 부분
  • # : MySQL에서 사용하는 주석 기호로, '#' 다음에 오는 한 줄을 주석으로 처리
  • -- : Oracle에서 사용하는 주석 기호로, '--' 다음에 오는 한 줄을 주석으로 처리
  • /* */ : DBMS에 관계 없이 '/'과 '/' 사이에 있는 문장을 모두 주석으로 처리

---

131. ORM(Object-Relational Mapping)

131.1 ORM(Object-Relational Mapping)

객체지향 프로그래밍의 객체(Object)와 관계형 데이터베이스(Relational DataBase)의 데이터를 연결(Mapping)하는 기술을 의미

• ORM은 객체지향 프로그래밍에서 사용할 수 있는 가상의 객체지향 데이터베이스를 만들어 프로그래밍 코드와 데이터를 연결
• ORM으로 생성된 가상의 객체지향 데이터베이스는 프로그래밍 코드 또는 데이터베이스와 독립적이므로 재사용 및 유지보수가 용이

131.2 ORM 프레임워크

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

• ORM 프레임워크의 종류

기반 언어	ORM 프레임워크
Java	JPA, Hibernate, EclipseLink, DataNucleus, Ebean 등
C++	ODB, QxOrm 등
Python	Django, SQLAlchemy, Storm 등
.NET	NHibernate, DatabaseObjects, Dapper 등
PHP	Doctrine, Propel, RedBean 등

131.3 ORM의 한계

• 프레임워크가 자동으로 SQL을 작성하기 때문에 의도대로 SQL이 작성되었는지 확인해야 함
• 객체지향적인 사용을 고려하고 설계된 데이터베이스가 아닌 경우 프로젝트가 크고 복잡해질수록 ORM 기술을 적용하기 어려움
• 기존의 기업들은 ORM을 고려하지 않은 데이터베이스를 사용하고 있기 때문에 ORM에 적합하게 변환하려면 많은 시간과 노력이 필요

---

132. 쿼리 성능 최적화

132.1 쿼리 성능 최적화

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

• 쿼리 성능을 최적화하기 전에 성능 측정 도구인 APM을 사용하여 최적화할 쿼리를 선정해야 함
• 최적화 할 쿼리에 대해 옵티마이저가 수립한 실행 계획을 검토하고 SQL 코드와 인덱스를 재구성
• APM(Application Performance Management/Monitoring) : 애플리케이션의 성능 관리를 위해 접속자, 자원 현황, 트랜잭션 수행 내역, 장애 진단 등 다양한 모니터링 기능을 제공하는 도구

132.2 옵티마이저(Optimizer)

작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아 주는 모듈

• RBO(Rule Based Optimizer)와 CBO(Cost Based Optimizer) 두 종류가 존재
• RBO는 데이터베이스 관리자(DBA)가 사전에 정의해둔 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저
• CBO는 입·출력 속도, CPU 사용량, 블록 개수, 개체의 속성, 튜플 개수 등을 종합하여 각 DBMS마다 고유의 알고리즘에 따라 산출되는 '비용'으로 최적의 경로를 찾는 비용 기반 옵티마이저
• RBO와 CBO의 차이점

	RBO	CBO
최적화 기준	규칙에 정의된 우선순위	액세스 비용
성능 기준	개발자의 SQL 숙련도	옵티마이저의 예측 성능
특징	실행 계획 예측이 쉬움	성능 통계치 정보 활용, 예측이 복잡
고려사항	개발자의 규칙 이해도, 규칙의 효율성	비용 산출 공식의 정확성

132.3 실행 계획(Execution Plan)

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

• EXPLAIN 명령어를 통해 확인 가능
  • 그래픽이나 텍스트로 표현
• 실행 계획에는 요구사항들을 처리하기 위한 연산 순서가 적혀있으며, 연산에는 조인, 테이블 검색, 필터, 정렬 등이 존재

132.4 쿼리 성능 최적화 방법

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

종류	내용
SQL 코드 재구성	- WHERE 절 추가- WHERE 절에 연산자 사용 제한 - IN을 EXISTS로 대체 - 힌트로 액세스 경로 및 조인 순서 변경
인덱스 재구성	- 조회되는 속성과 조건을 고려하여 인덱스 구성 - 인덱스 추가 및 기존 인덱스의 열 순서 변경 - 테이블을 참조하는 다른 SQL문으로의 영향 고려 - IOT(Index-Organized Table) 구성 고려- 불필요한 인덱스 제거