[정보처리기사 실기] SQL 응용

1. 절차형 SQL 작성하기

절차형 SQL

• 개요 : 절차 지향적인 프로그램이 가능하며, 분기, 반복 등의 제어를 활용하여 다양한 기능을 수행하는 저장 모듈을 생성

• 특징

  • DBMS 엔진에서 직접 실행

  • Block화된 구조로 기능별 모듈화가 가능

  • 조건문, 반복문 등 연속적인 작업 처리 가능

  • DBMS 내부에서 처리하기 때문에 Input/Output Packet이 적음

 

프로시저(Procedure)

• 개요 : 절차형 SQL을 활용하여 특정 기능을 수행하는 트랜잭션 언어, 호출을 통해 미리 저장해 놓은 SQL 작업을 수행

• 구성

• 프로시저 예시

#프로시저 생성
CREATE OR REPLACE PROCEDUER emp_change_s(i_사원번호 IN INT)
IS	/*변수를 사용하지 않으므로 예약어만 입력*/
BEGIN
	UPDATE 급여 SET 지급방식 = 'S' WHERE 사원번호 = i_사원번호;
    EXCEPTION
    	WHEN PROGRAM_ERROR THEN
        	ROLLBACK;
    COMMIT;
END;

#프로시저 호출
EXCUTE emp_change_s(32);

#프로시저 제거
DROP PROCEDURE emp_change_s;

 

사용자 정의 함수(Function)

• 개요 : 프로시저와 유사하게 절차형 SQL을 활용하여 일련의 작업을 처리하고 결과를 단일값으로 반환하는 절차형 SQL

• 프로시저 vs 사용자 정의 함수

구분	프로시저	사용자 정의 함수
반환값	없거나 1개 이상	1개
파라미터	입·출력 가능	입력만 가능
사용 가능 명령문	DML, DCL	SELECT

• 구성

• 사용자 정의 함수 예시

#사용자 정의 함수 생성
CREATE FUNCTION Get_S_성별(i_성별코드 IN INT)
RETURN VARCHAR2
IS
BEGIN
	IF i_성별코드 = 1 THEN
		RETURN '남자';
    ELSE
    	RETUEN '여자';
    END IF;
END;

#사용자 정의 함수 호출
SELECT 이름, Get_S_성별(성별코드) FROM 사원;

#사용자 정의 함수 제거
DROP FUNCTION Get_S_성별

 

트리거(Trigger)

• 개요 : 데이터베이스 시스템에서 삽입, 삭제, 갱신 등의 이벤트가 발생할 때마다 자동으로 수행되는 절차지향 SQL

• 구성

• 트리거 예시

#트리거 생성
CREATE TRIGGER 학년정보_tri BEFORE INSERT ON 학생
REFERENCING NEW AS new_table
FOR EACH ROW	/*각 튜플마다 적용*/
WHEN(new_table.학년 IS NULL)
BEGIN
	:new_table.학년 := '신입생';
    /*REFERENCING된 테이블 앞에는 콜론(:)을 붙임*/
    /*A := B 는 A에 B를 치완하라는 의미*/
END;

#트리거 제거
DROP TRIGGER 학년정보_tri;

 

2. 응용 SQL 작성하기

집계 함수(Aggergate Function)

• 개요 : 여러행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수, 그룹 함수나 윈도우 함수에도 적용 가능

• 분류

집계 함수	설명
COUNT(속성명)	튜플의 수를 구하는 함수
SUM(속성명)	합계를 구하는 함수
AVG(속성명)	평균을 구하는 함수
MAX(속성명)	최대값을 구하는 함수
MIN(속성명)	최소값을 구하는 함수
STDDEV(속성명)	표준편차를 구하는 함수
VARIAN(속성명)	분산을 구하는 함수

 

그룹 함수(Group Function)

• 개요 : 집계 함수와 유사한 개념, 그룹별로 속성의 값을 집계

• 분류

그룹 함수	설명
ROLLUP(속성명, 속성명,···)	인수로 주어진 속성을 대상으로 그룹별 소계를 구함
	속성의 개수 n+1 레벨까지, 하위레벨에서 상위 레벨 순으로 데이터 집계
CUBE(속성명, 속성명,···)	인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구함
	속성의 개수 n²레벨까지 상위레벨에서 하위레벨 순으로 데이터 집계

• 일반 형식

SELECT [PREDICATE] 그룹함수(속성명) [AS 별칭]
FORM 테이블명
[WHERE 조건]
GROUP BY 속성명
[HAVING 조건]

 

윈도우 함수(Window Function)

• 개요 : GROUP BY절을 이용하지 않고 함수의 인수로 지정한 속성을 범위로 하여 속성의 값을 집계

• 윈도우(Window) : 함수의 인수로 지정한 속성, 대상 레코드의 범위

• 분류

윈도우 함수	설명
RANK()	윈도우별로 순위를 반환, 공동 순위를 반영
DENSE_RANK()	윈도우별로 순위를 반환, 공동 순위를 무시
ROW_NUMBER()	윈도우별로 각 레코드에 대한 일련 번호를 반환

• 일반 형식

SELECT [PREDICATE] [테이블명.]속성명 [AS 별칭]
[, WINDOW함수 OVER (PARTITION BY 속성명 ORDER BY 속성명) [AS 별칭]]
FORM 테이블명

 

 

DBMS 접속

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

• 구조

사용자 : 웹 서버에 접속하여 데이터를 주고 받음

웹 서버 : 사용자의 대용량의 데이터 요청을 WAS에게 전달

WAS : 수신한 요청을 트랜잭션 언어로 변환하여 DBMS에 전달, DBMS로부터 데이터를 받아 요청한 웹 서버로 전달

• DBMS 접속 기술

접속 기술	설명
JDBC (Java DataBase Connectivity)	Java 언어로 다양한 종류의 데이터베이스에 접속하고 SQL문을 수행할 때 사용되는 표준 API
ODBC (Open DataBase Connectivity)	데이터베이스에 접근하기 위한 표준 개방형 API, 계발 언어에 관계없이 사용 가능
MyBatis	SQL Mapping 기반 오픈 소스 접속 프레임워크