정보처리기사 27일차

3. 데이터베이스

 3.2 SQL 응용

  3.2.1 SQL 응용 능력 단위 정의

  3.2.2 응용 SQL 작성 

  3.2.3 절차형 SQL 작성

---

- SQL 응용 능력 단위 정의

SQL응용이란 관계형 데이터베이스에서 SQL을 사용하여 응용시스템의 요구기능에 적합한 데이터를 정의하고 조작하며 제어하는 능력이다.

 

- 응용 SQL 작성

 

1, 집계성 SQL

 

1.1 집계성 SQL 개요

총합 평균 등의 데이터 분석을 위해 복수 개의 행을 기준으로 데이터를 분석하는 SQL 시스템을 말한다.

집계 함수, 그룹 함수, 윈도우 함수로 구성된다.

 

1.2 집계 함수

 

1. 개요 

GRUOP BY 절과 함께 사용되어 복수 행에 대한 개수, 합계,평균,최소,최대값 등을 계산하는 SQL 함수를 말한다.

 

2. 집계 함수 특징

한 개의 애트리뷰트에 적용되어 단일 값을 반환한다.

SELECT 절과 HAVING절에만 나타날 수 있다.

COUNT(*)를 제외한 모든 집계 함수는 널값을 제거한 후 계산한다.

COUNT(*)는 결과 릴레이션의 모든 행들의 총 개수를 반환한다.

COUNT(애트리뷰트)는 널값이 아닌 값들의 개수를 반환한다.

DISTINCT가 집단 함수 앞에 사용되면 집단 함수가 적용되기 전에 중복을 제거한다.

 

3. 그룹화 방법

GROUP BY절을 이용하여 분석할 데이터 행을 분류할 기준을 정의한다.

GROUP BY절에 사용된 애트리뷰트에 동일한 값을 갖는 튜플들이 각각 하나의 그룹으로 묶여서 각 그룹별로 하나의 결과를 생성한다.

GROUP BY 절을 사용할 경우, SELECT 절에는 집단함수, 그룹화에 사용된 애트리뷰트들만 나타날 수 있다.

 

2. 그룹함수

 

2.1 개요

소그룹의 소계, 총계와 같이 레벨별로 집계 결과를 조회하기 위해 사용한다.

ROLLUP(), CUBE(), GROUPING SETS() 함수가 대표적이다.

 

2.2 ROLLUP() 함수

소계 등 중간 집계값을 산출하기 위해 사용한다.

컬럼의 수보다 하나 더 큰 레벨 만큼의 중간 집계값을 생성한다.

ROLLUP의 지정 컬럼은 계층별로 구성되기 때문에 순서가 바뀌면 수행 결과가 바뀐다.

 

2.3 CUBE() 함수

결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수이다.

CUBE는 내부적으로 대상 컬럼의 순서를 변경하여 또 한 번의 쿼리를 수행하고 총계는 양쪽 쿼리에서 모두 수행 후 한쪽에서 제거한다.

ROLLUP에 비해 계산이 많다.

 

2.4 GROUPING SETS() 함수

집계 대상 컬럼들에 대한 개별 집계를 구한다. ROLLUP이나 CUBE와는 달리 컬럼 간 순서와 무관한 결과를 생성한다.

GROUP BY 한 SQL 들을 UNION ALL 해서 보여주는 결과와 같다.

 

3. 윈도우 함수

 

3.1 개요

행과 행간의 관계를 쉽게 정의하기 위해 만든 함수로 분석 함수 또는 순위 함수라고도 한다.

일반적으로 집계함수나 순위함수 등을 이용해 그룹을 나눠 분석을 수행한다.

 

-절차형 SQL 작성

 

1. 절차형 SQL 기본

 

1.절차형 SQL 개요

 

1.1 절차형 SQL 개념

SQL문의 연속적인 실행이나 조건에 따른 분기, 반복 등이 제어를 활용하여 다양한 기능을 수행하는 데이터베이스 저장 모듈이다.

절차형 SQL은 반복 또는 자주 수행하는 DB작업을 효율적으로 수행할 수 있으며,잘 정의된 절차형 SQL은 소프트웨어 개발 생산성을 높일 수 있다.

 

1.2 절차형 SQL 특징

DBMS 엔진에서 직접 실행되며 BEGIN/END의 Block화된 구조를 갖는다.

조건문, 반복문 등 단일 SQL 문장으로 실행하기 어려운 연속적인 작업처리가 가능하다.

작업에 필요한 데이터를 DBMS 내부에서 직접 처리하기 때문에 In/Out Packet이 적다.

타절차형 언어에 비해 작업의 효율성은 낮은 편이며, DBMS 벤더별로 약간의 문법 차이가 존재하므로 이식시 수정 및 재컴파일이 필요하다.

 

1.3 절차형 SQL 구성

절차형 SQL 종류마다 약간의 차이가 있다.

기본 구성 요소

DECLARE : 대상이 되는 프로시저, 사용자 정의함수 등을 정의

BEGIN : 프로시저, 사용자 정의함수가 실행되는 시작점

END : 프로시저, 사용자 정의함수가 실행되는 종료점

 

2. 절차형 SQL 제어문

 

2.1 절차형 SQL 제어문 종류

절차형 SQL 제어문은 Oracle PL/SQL을 이용하여 설명한다.

 

조건제어 : IF-THEN-ELSE

IF문은 조건에 만족할 경우와 만족하지 않을 경우 각각에 대해 실행할 문장을 선택

할수 있다. 선택 제어문이라고도 한다.

 

반복제어 : LOOP,FOR-LOOP,WHILE-LOOP

LOOP문은 종료조건을 만족하지 않을 경우 무조건 LOOP 안의 문장들을 반복수행한다.

FOR-LOOP는 지정된 횟수만큼 LOOP 안의 문장들을 반복수행한다.

WHILE-LOOP는 WHILE문에 명시된 조건을 만족하는 동안 LOOP 안의 문장들을 반복수행한다.

 

순차제어 : GOTO, NULL

GOTO : 제어가 건너뛰는 곳을 지정하는 레이블과 함께 쓰임

NULL : 실행을 하지 않음을 나타냄

 

2.2 절차형 SQL의 SELECT문

 

반드시 하나의 행만을 추출해야 오류없이 정상적으로 수행하며, 만일 추출되는 데이터행이 없거나 하나 이상일 경우에는 예외가 발생한다.

TOO_MANY_ROWS : 하나 이상의 데이터 행 추출 시

NO_DATA_FOUND : 어떤 데이터도 추출하지 못할 때

다수 개의 데이터 행을 하나씩 추출할 때는 명시적 커서를 사용해야 한다. 

 

2.3 절차형 SQL의 커서

SQL 처리 결과가 저장된 작업 영역에 이름을 지정하고 저장된 정보를 접근할 수 있게 하는 용도로 사용한다.

SQL 명령을 실행시키면 서버는 명령을 parse 하고 실행하기 위한 메모리 영역을 open하는데 이 영역을 cursor라고 한다.

 

커서의 종류

명시적 커서 : 프로그래머가 선언하고 명령하며 블럭의 실행 가능한 부분에서 특정 명령을 통해 조작하는 커서이다.

묵시적 커서 : SQL 문이 실행되는 순간 자동으로 열렸다가 닫힌다. 오라클은 모든 DML과 PL/SQL SELECT 문에 묵시적으로 커서를 생성한다.

 

2. 프로시저 및 호출문 작성

 

2.1 프로시저 개요

 

프로시저 개념

매겨변수를 받을 수 있고 반복해서 사용할 수 있는 이름이 있는 SQL 블록으로 연속실행 또는 구현이 복잡한 트랙잭션을 수행하는 SQL 블록을 데이터베이스에 저장하기 위해 구성한다.

프로시저 호출을 통해 실행되며, 일련의 SQL작업을 포함하는 데이터 조작어를 수행한다.

일일 마감 작업 또는 일련의 배치 작업 등을 프로시저를 활용하여 관리하고 주기적으로 수행하기도 한다.

 

프로시저 구성

DECLARE : 프로시저의 명칭, 변수와 인수, 데이터 타입을 정의하는 선언부이다.

BEGIN/END : 프로시저의 시작과 종료를 표현하는 필수 요소이며 프로시저 내부에서 Block을 구분하는데 사용하기도 한다.

CONTROL : 순차실행, 주건분기, 반복수행 등 SQL 문장의 수행 순서를 조장하는데 사용한다.

SQL : DQL,DML을 주로 사용하고 경우에따라 DDL 문을 사용하기도 한다.

EXCEPTION :  BEGIN~END 절에서 실행되는 SQL 문이 실행 될 때 예외 또는 오류가 발생하는 경우 예외 처리 방법을 정의한다.

TRANSACTION : 프로시저에서 수행된 DML 수행 내역의 DBMS의 적용 또는 취소 여부를 결정하는 처리부이다.

 

2.2 프로시저 작성

프로시저 정의 문법

CREATE 명령어로 DBMS 내에 프로시저 생성이 가능하다.

OR REPLACE 명령은 기존 프로시저 존재 시에 현재 컴파일하는 내용으로 덮어쓴다는 의미이다. 동명의 프로시저가 존재할때 CREATE 명령문만 사용되면 컴파일시에는 에러가 발생한다.

PARAMETER는 외부에서 프로시저 호출 시 변수를 입력 또는 출력할 수 있다.

MODE는 변수의 입력 또는 출력을 구분하며 다음과 같이 사용한다.

IN : 운영 체제에서 프로시저로 전달되는 MODE

OUT : 프로시저에서 처리된 결과가 운영체제로 전달되는 MODE

INOUT : IN과 OUT의 두가지 기능을 동시에 수행하는 MODE

 

3. 사용자 정의 함수 및 호출쿼리 작성

 

3.1 사용자 정의 함수 개요

함수개념 

매개변수를 받을 수 있고 데이터와 관련된 복잡한 계산 등을 수행하며 해당 동작의 결과를 반환하는 사용자 정의함수를 DBMS에 정의할 수 있다

함수구성

기본사항은 프로시저와 동일하지만, 결과값을 반환하는 부분이 상이하다.

 

4. 트리거

 

4.1 트리거 개요

트리거 개념

데이터베이스에 특정한 변경이 가해졌을 때 발생할 때마다 DBMS가 이에 대응해서 자동적으로 호출하는 일종의 프로시저를 말한다.

프로시저와 함수는 그 실행이 외부적인 실행 명령에 의해 이루어지는데 반해, 트리거의 실행은 트리거링 사건에 의해 내부적으로 이루어진다.

트리거를 이벤트-조건-동작 규칙이라고도 부른다. 여기서 E는 Event, C는 Condition, A는 Action을 의미한다.

 

트리거 구성

트리거를 가동하는 사건과 수행조건, 수행할 동작으로 구성된다.

 

트리거 용도

테이블 생성시 참조 무결성과 데이터 무결성 그 밖의 다른 제약 조건으로 정의할 수 없는 복잡한 요구 사항에 대한 제약조건을 생성하는데 사용한다.

테이블 정의 시 표현할 수 없는 기업의 비즈니스 규칙들을 시행하는 역할을 한다.

테이블의 데이터에 생기는 작업을 감시할 수 있다. 즉, 보안 통제용으로 트리거를 활용할 수 있다.

 

5. 오류처리

 

5.1 데이터베이스 오류 처리의 개념

절차형 SQL 프로그램의 구문 오류 또는 프로그램 실행 상황에 따라 발생하는 오류를 처리하기 위해 예외사항을 정의하고, 예외 상황이 발생했을 때 미리 정의된 처리 루틴으로 분기하도록 정의하는 과정이다.