정보처리기사 필기 3. 데이터베이스 구축 - Chapter 1. SQL 응용

1 절차형 SQL 작성

1 트리거

1 개념

데이터베이스가 미리 정해 놓은 조건이 충족되거나, 데이터 변경 이벤트가 발생하면 DBMS에서 자동적으로 실행되도록 구현된 프로그램

* DBMS : 사용자와 데이터베이스 사이에서 사용자의 요구에 따라 정보를 생성해주고, 데이터베이스를 관리해주는 소프트웨어

2 목적

자동 실행, 데이터 무결성 유지 및 로그 메시지 출력

* 데이터 무결성 : 데이터베이스에 저장된 데이터 값과 그것이 표현하는 실제 값이 일치하는 정확성

* 로그 메시지 : 시스템의 모든 기록을 담고 있는 데이터

3 구성

반환 無 / DML(데이터 조작어)을 주된 목적으로 한다. ( 프로시저 O )

EVENT 명령어를 통해 트리거 실행을 위한 이벤트를 인지 / 외부 변수 IN, OUT 無 ( 프로시저 X )

* 프로시저 : SQL 명령문들을 하나의 함수처럼 실행하는 쿼리 집합

* 사용자 정의함수 : 사용자가 직접 함수를 정의하여 사용할 수 있는 기능

DECLARE	트리거의 명칭, 변수와 인수와 그에 대한 데이터 타입 정의부
BEGIN/END	트리거의 시작과 종료, Block으로 구성
CONTROL	명령문
SQL	DML
EXCEPTION	예외 처리
TRANSACTION	트리거에서 수행된 DML 수행 내역의 DBMS 적용 여부

4 구문

CREATE( OR REPLACE) TRIGGER [트리거명]
BEFORE [DML] ON [테이블명]
( FOR EACH ROW ) -- 행트리거
BEGIN
    ( 실행할 SQL문 )
    ( EXCEPTION )
END;

* OR REPLACE 로 기존 트리거 존재 시 현재 컴파일하는 내용으로 Overwrite

5 주의사항

DCL(데이터 제어어) 사용 불가능 ( COMMIT, ROLLBACK, GRANT, REVOKE )

6 구현 및 실행 방법

1. 인식이 가능한 이벤트 정의
2. 대상 이벤트와 관련된 테이블 및 데이터 확인 -> 분석
3. 트리거의 설계 -> 작성 -> 컴파일
4. 이벤트 발생 -> 트리거 자동 실행

2 이벤트

1 개념

특정 시간에 특정 쿼리, 프로시저, 함수 등을 실행시키는 기능

2 구문

CREATE EVENT [이벤트명]
ON SCHEDULE [스케줄]
DO ~

스케줄 : AT 연월일시 / EVERY 간격

3 사용자 정의함수

1 개념

절차형 SQL을 활용하여 결과를 단일 값으로 반환할 수 있는 함수

2 특징

호출을 통해 실행, 반환(프로시저 X)

3 구문

CREATE( OR REPLACE) FUNCTION [사용자 정의함수 명(파라미터)]
IS [지역변수]
BEGIN
    ~
    RETURN ~
END

* OR REPLACE 로 기존 사용자 정의함수 존재 시 현재 컴파일하는 내용으로 Overwrite

* INTERVAL : 이벤트 스케줄 등록할 때 사용, 복수지정 불가능

4 호출쿼리 작성

캡슐화를 제공하는 데 많이 사용

* 캡슐화 : 객체의 속성과 행위를 하나로 묶고, 실제 구현 내용 일부를 외부에 감추어 은닉하는 기법

4 SQL 문법

1 분류

데이터 정의어(DDL)	테이블이나 관계의 구조	CREATE, ALTER, DROP
데이터 조작어(DML)	테이블의 데이터 검색, 변경	SELECT, INSERT, DELETE, UPDATE
데이터 제어어(DCL)	데이터의 사용 권한	GRANT, REVOKE

2 WHERE 조건

비교	=, <>, <, <=, >, >=
범위	BETWEEN (이상, 이하)
집합	IN, NOT IN
패턴	LIKE
NULL	IS NULL, IS NOT NULL
복합조건	AND, OR, NOT

3 LIKE와 같이 사용하는 와일드 문자

여러 대상을 한꺼번에 지정할 목적으로 사용하는 기호

+	문자열 연결	'축구' + '감독' : '축구 감독'
%	문자열 일치	LIKE '키워드%' : 키워드로 시작하는 문자열 검색
[ ]	문자 일치	LIKE '[0-8]%' : 0-8의 숫자로 시작하는 문자열
[ ^ ]	문자 불일치	LIKE '[^0-8]%' : 0-8의 숫자로 시작하지 않는 문자열
_	임의의 문자	LIKE '_동%' : 두 번째 위치에 '동'이 들어가는 문자열

4 주석 처리

• 한 줄 : '--'
• 여러 줄 : '/' ~ '/'

5 힌트

SQL 문에 사전에 정보를 주어 빠른 결과를 가져오는 효과를 만드는 문법

• 한 줄 : '--+ 힌트 명(파라미터, ...)'
• 여러 줄 : '/*+ 힌트 명(파라미터, ...) */'

---

2 응용 SQL 작성

DBMS 필수 기능

• 데이터 정의 : 응용 프로그램과 데이터베이스의 인터페이스
• 데이터 조작 : 사용자와 데이터베이스의 인터페이스
• 데이터 제어 : 정확성과 안정성

1 데이터 조작어(DML)

1 개념

데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어

2 유형

SELECT	데이터 조회	해당 테이블을 구성하는 특정 튜플을 검색하여 주기억장치 상의 임시 테이블로 구성
INSERT	데이터 생성	해당 테이블에 새로운 튜플을 삽입
DELETE	데이터 삭제	해당 테이블에 있는 튜플 중 특정 튜플을 삭제
UPDATE	데이터 변경	해당 테이블에 있는 튜플 중 특정 튜플을 변경

 

3 구문

-- SELECT
SELECT [(DISTINCT) 속성명1, (DISTINCT) 속성명2]
  FROM [테이블명1, ...]
  (WHERE [조건])
  (GROUP BY [속성명1, ...])
  (HAVING [그룹조건])
  (ORDER BY 속성 ASC|DESC);


-- INSERT
INSERT INTO [테이블 명(속성1, ...)]
  VALUES([데이터1], [...]);


-- DELETE
DELETE FROM [테이블 명]
  WHERE [조건];


-- UPDATE
UPDATE [테이블 명]
  SET [속성 명 = 데이터], [...]
  WHERE [조건];

* DISTINCT의 경우 동일한 튜플을 제거하고 검색

2 데이터 제어어(DCL)

1 개념

데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 하기 위해 DBA가 사용하는 제어용 언어

2 유형

GRANT	사용 권한 부여	DBA가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어
REVOKE	사용 권한 취소	DBA가 사용자에게 부여했던 권한을 회수하기 위한 명령어

3 구문

-- GRANT
GRANT [권한] ON [테이블] TO [사용자]
[WITH 권한 옵션];


-- REVOKE
REVOKE [권한] ON [테이블] FROM [사용자]
[CASCADE];

* GRANT 명령문으로 부여할 수 있는 권한 유형

• 시스템 권한
  • CREATE USER : 계정 생성
  • DROP USER : 계정 삭제
  • CREATE TABLE : 테이블 생성
  • DROP ANY TABLE : 테이블 삭제
  • CREATE SESSION : 데이터베이스 접속
  • CREATE VIEW : 뷰 생성
  • CREATE SEQUENCE : 시퀀스 생성
  • CREATE PROCEDURE : 함수 생성
• 객체 권한
  • ALTER
  • INSERT
  • DELETE
  • SELECT
  • UPDATE
  • EXECUTE : 프로시저 실행

* CASCADE : 연쇄적인 권한 해제 시 (WITH GRANT OPTION으로 부여된 사용자들의 권한까지 취소)

3 윈도우 함수

1 개념

행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수

데이터베이스를 사용한 온라인 분석 처리 용도를 사용하기 위해 추가된 기능

OLAP(Online Analytical Processing) 함수라고도 한다.

2 구문

SELECT [함수명(파라미터)]
  OVER
  ([PARTITION BY 컬럼1, ...])
  [ORDER BY 컬럼A, ...]
  FROM [테이블명]

3 분류

집계 함수

여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수, 대다수의 DBMS에서 지원

순위 함수

• RANK : 레코드의 순위 계산, 동일 순위 레코드 존재 시 후순위는 넘어간다.(2등 2개 -> 2위, 2위, 4위)
• DENSE_RANK : 레코드의 순위 계산, 동일 순위 레코드 존재 시 후순위를 넘어가지 않는다.(2등 2개 -> 2위, 2위, 3위, ...)
• ROW_NUMBER : 레코드의 순위 계산, 동일 순위 레코드 존재 시 무관하게 연속 번호 부여(2위 2개 -> 2위, 3위, 4위, ...)

행 순서 함수

• FIRST_VALUE : 파티션 별 윈도우에서 가장 먼저 나오는 값( = MIN )
• LAST_VALUE : 파티션 별 윈도우에서 가장 늦게 나오는 값( = MAX )
• LAG : 파티션별 윈도우에서 1부터 이전 몇 번째 행의 값을 가져온다.
• LEAD : 파티션별 윈도우에서 1부터 이후 몇 번째 행의 값을 가져온다.

그룹 내 비율 함수

• RATIO_TO_REPORT
• PERCENT_RANK

4 그룹 함수

1 개념

소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 함수

2 유형

ROLLUP

소그룹간 소계 출력

SELECT [컬럼명1], ..., [그룹함수]
  FROM [테이블명]
  (WHERE [조건])
  GROUP BY [컬럼명A], ...
  ROLLUP [컬럼명a], ...
  (HAVING ...)
  (ORDER BY ...)

* ROLLUP의 지정 컬럼은 계층별로 구성 -> 순서에 따라 결과가 다르다.

CUBE

GROUP BY 항목들과 다차원 소계 출력

SELECT [컬럼명1], ..., [그룹함수]
  FROM [테이블명]
  (WHERE [조건])
  GROUP BY [컬럼명A], ...
  CUBE [컬럼명a], ...
  (HAVING ...)
  (ORDER BY ...)

* 연산 多 -> 시스템에 부담

GROUPING SETS

특정 항목에 대한 소계 출력

SELECT [컬럼명1], ..., [그룹함수]
  FROM [테이블명]
  (WHERE [조건])
  GROUP BY [컬럼명A], ...
  ROLLUP [컬럼명a], ...
  (HAVING ...)
  (ORDER BY ...)

* 컬럼 간 순서와 무관한 결과

5 오류 처리

1 개념

프로그램 코드 상의 오류나 프로시저 실행 시 예외나 에러가 발생했을 때, 문제를 해결하고 의미 있는 에러 메시지를 부여하는 과정

2 핸들러 선언 구문

DECLARE [액션] HANDLER
  FOR [상대 값] [명령문]

액션 : CONTINUE - 명령문 계속 실행 / EXIT - 명령문 한 번 실행

상대 값 : 에러코드

• SQLWARNIG : 경고
• NOTFOUND : 레코드를 가져오지 못했을 때
• SQLEXCEPTION : 에러