2020 정보처리기사 실기 정리 - SQL 응용

초 간 단 정 리 

(기본은 미리 학습하고 보시면 좋습니다.)

 

(1)프로시저란?

프로시저는 절자형 SQL을 활용하여 특정기능을 수행할 수 있는 트랜잭션 언어이다. 

 

프로시저의 구성 (빈칸 단답식)

1. 선언부 : 프로시저 명칭, 변수와 인수 타입 정의

2. 시작부/종료부 : 시작 종료 표현 다수 실행을 제어하는 기본 단위

3. 제어부 : 순차처리 조건문과 반복문을 이용

4. SQL : DML 주로 사용 

5. 예외부 : 예외처리

6. 실행부 : DBMS 적용 또는 취소 여부 결정 처리

 

선언부

Create[ or replace] Procedure

파라미터명 [mode] 데이터 타입 - mode(IN, OUT, INOUT) ,데이터타입(CHAR, NUMBER, VARCHAR)

.....

IS

변수 선언

 

제어부 

IF ~THEN

  sentence~
ELSIF ~ THEN

  sentence~

ELSE

  sentence~

END IF

 

Case

When ~ THEN

  SET~

When ~ THEN

  SET~

ELSE

  SET~

 

프로시저 SQL

Select, Insert, Update, Delete

 

프로시저 실행부

Commit, Rollback

 

(2)사용자 정의함수

특정 절차형 SQL을 활용하여 SQL을 처리하고 수행결과를 단일값으로 반환하는 SQL.

 

사용자 정의함수 구현

1. 선언부

2. 시작부/종료부

3. 제어부

4. SQL

5. 예외부

6. 반환부 ***

 

선언부 (반환이 있다는 점과 Function의 차이를 제외하고는 유사하다)

Create [OR replace] Function 함수명

Return 데이터_타입

파라미터 명 [Mode] 데이터_차입

...

IS

변수 선언 

 

(3)트리거

특정 테이블에 삽입 수정 삭제 등의 데이터 변경 이벤트가 발생하면 DBMS에서 자동적으로 실행되도록 구현된 프로그램이다.

 

트리거의 목적

특정 테이블에 대한 데이터 변경을 시작점으로 설정 그와 관련한 모든 작업을 자동으로 수행하기 위해 트리거 사용

 

트리거 종류

1. 행 트리거

2. 문장 트리거 

 

트리거의 구성

반환값이 없다는 점 DML을 주된 목적으로 한다는 점에서 프로시저와 유사하지만

EVENT 명령어를 통해 트리거 실행을 위한 이벤트를 인지한다는 점 외부 변수 IN/OUT이 없다는 점은 프로시저와 사용자 정의 함수와 다르다.

1. 선언부

2. 이벤트부

3. 시작/종료부

4. 제어부

5. SQL부

6. 예외부

 

선언부

Create [Or replace] Trigger 트리거 명

 

이벤트부

순서 유형 On 테이블명

[FOR EACH ROW]

 

이벤트 순서 

BEFORE

AFTER

 

이벤트 유형

INSERT

UPDATE

DELETE

 

트리거 작성 시 주의사항

TCL 사용 불가 - 트리거 내에는 Commit, Rollback 등의 TCL 사용시 컴파일 에러 발생

오류의 주의 - 트리거 실행 중 오류가 발생하게 되면 트리거 실행의 운인을 제공한 데이터 작업에도 영향

 

(4)데이터 분석 함수의 개념

데이터 분석을 위해 복수 행 기준의 데이터를 모아서 처리하는 것을 목적으로 하는 다중 행 함수이다.

 

데이터 분석 함수 종류

1. 집계 함수

2. 그룹 함수

3. 윈도 함수

 

집계 함수

여러행 또는 테이블 전체 행으로 부터 하나의 결과값을 반환하는 함수이다.

 

집계 함수의 종류

Count, sum, Avg, Max, Stddev, Varian

 

그룹 함수

테이블의 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수

 

그룹함수 유형(결과를 보고 고르는 문제)

1. Rollup

2. Cube

3. Grouping Sets

 

윈도 함수

윈도 함수는 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 함수

 

윈도 함수의 구문

Select function_name(para~)

 Over

([Partition by columns1, col2,...])

[Order by col1, col2....]

 From table_name

 

윈도 함수의 분류

1. 순위 함수 (rank, Dense_rank, row_number)

2. 행순서 함수 (first_value, Last_value, LAG, LEAD)

3. 그룹 내 비율 함수 (Ratio_To_Report, Percent_rank)

 

(5)응용 시스템 DBMS 접속 기술

 

자바 데이터베이스 연결(JDBC: Java DataBase Connectivity)

JDBC는 SQL을 사용하여 DBMS에 질의하고 데이터를 조작하는 API를 제공한다.

 

MyBatis

MyBatis는 DBMS에 질의하기 위한 SQL 쿼리를 별도의 XML파일로 분리하고 Mapping을 통해 SQL을 실행한다.

 

DCL(데이터 제어어) 

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

 

데이터 제어어의 유형

1. Grant (grant 권환 on 테이블 to 사용자 [with 권한 옵션])

2. Revoke (revoke 권한 on 테이블 from 사용자 [cascade constraints])

 

참고 서적: 수제비 2020 정보처리기사 실기 vol.2