정보처리기사 04. SQL 응용-3 (제어문, 커서, DBMS 접속, SQL 테스트, ORM)

 

01 제어문

 

- 제어문은 위에서 아래로 차례대로 실행되는 절차형 SQL의 진행 순서를 변경하기 위해 사용하는 명령문이다.

- 제어문에는 IF, LOOP 등이 있다.

 

 

IF문

- IF문은 조건에 따라 실행할 문장을 달리하는 제어문이다.

- 형식1: 조건이 참일 때만 실행한다. 

IF 조건 THEN // IF와 THEN은 예약어로 그대로 적는다.
			// 조건에는 참 또는 거짓이 결과로 나올 수 있는 수식을 적는다.
	
    실행할 문장1; // 조건이 참일 경우 설명할 문장을 적는다.
    실행할 문장2;
    
END IF; // IF문이 종료되었음을 알리는 예약어로 그대롤 적는다.

- 형식2: 조건이 참일 때와 거짓일 때 실행할 문장이 다르다.

IF 조건 THEN
	실행할 문장1; // 조건이 참일 경우 실행할 문장을 적는다.
ELSE
	실행할 문장2; // 조건이 거짓일 경우 실행할 문장을 적는다.
END IF;

 

LOOP문

- LOOP문은 조건에 따라 실행할 문장을 반복 수행하는 제어문이다.

LOOP			// LOOP문의 시작을 알리는 예약어로 그대로 적는다. 
	실행할 문장;	// 반복 수행할 문장을 적는다.
	EXIT WHEN 조건;	// EXIT, WHEN은 예약어로 그대로 적는다.
    				// 조건에는 참 또는 거짓이 결과로 나올 수 있는 수식을 적는다. 조건이 참인 경우 LOOP을 빠져나간다.

END LOOP;	// LOOP문이 종료되었음을 알리는 예약어로 그대로 적는다.

---

 

02 커서(Cursor)

 

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

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

 

 

묵시적 커서(Implicit Cursor)

- 묵시적 커서는 DBMS에 의해 내부에서 자동으로 생성되어 사용되는 커서이다.

- 커서의 속성을 조회하여 사용된 쿼리 정보를 열람하는 것이 가능하다.

- 수행된 쿼리문의 정상적인 수행 여부를 확인하기 위해 사용된다.

 

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

 

명시적 커서(Explicit Cursor)

- 명시적 커서는 사용자가 직접 정의해서 사용하는 커서이다.

- 쿼리문의 결과를 저장하여 사용함으로써 동일한 쿼리가 반복 수행되어 데이터베이스 자원이 낭비되는 것을 방지한다.

- 커서는 기본적으로 '열기(OPEN) - 패치(FETCH) - 닫기(CLOSE)' 순으로 이루어지며, 명시적 커서를 사용하기 위해서는 열기(Open) 단계 전에 선언해야한다.

 

• 선언(Declare)형식

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

• 열기(Open)형식

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

• 패치(Fetch)형식

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

• 닫기(Close)형식

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

 

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

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

BEGIN
	OPEN cur_name(20);
    LOOP
    	FETCH cur_name INTO p_name;
        EXIT WHEN cur_name%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(p_name);
	END LOOP;
    CLOSE cur_name;
END;

 

---

 

03 DBMS 접속

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

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

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

- 웹 응용 시스템은 웹 서버와 웹 애플리케이션 서버(WAS)로 구성된다.

 

DMBS 접속 기술

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

 

접속 기술	특징
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을 실행함

 

03 동적 SQL(Dynamic SQL)

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

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

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

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

 

---

 

04 SQL 테스트

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

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

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

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

 

단문 SQL 테스트

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

- DDL로 작성된 개체는 DESCRIBE 명령어를 이용하여 속성, 자료형, 옵션들을 확인할 수 있다.

DESC [개체명];

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

- DCL로 설정된 사용자 권한은 사용자 권한 정보가 저장된 테이블을 조회하여 확인할 수 있다. 

 

예제) MySQL에서 <학생> 테이블의 정보를 확인하는 SQL문을 작성하시오.

DESC 학생;

 

절차형 SQL 테스트

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

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

SHOW ERRORS;

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

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

---

 

05 ORM(Object-Relational Mapping)

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

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

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

 

ORM 프레임워크

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

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

 

ORM의 한계

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

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

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