(정보처리기사 실기) SQL 응용

프로시저

프로시저 : 절차형 SQL을 활용하여 특정 기능을 수행할 수 있는 트랜잭션 언어, 데이터 조작어(DML)수행

프로시저 구성 요소(순서)

-선언부

-시작/종료부

-제어부

-SQL

-예외부

-실행부

 

제어부(CONTROL) 

조건문

IF문

IF 조건 THEN
 문장;
ELSIF 조건 THEN
 문장;
ELSE
 문장;
END IF;

 

CASE문

CASE 변수
  WHEN 값1 THEN
    SET 명령어;
  WHEN 값2 THEN
    SET 명령어;
  ELSE
    SET 명령어;
END CASE;

 

반복문

LOOP문

LOOP
    문장;
    EXIT WHEN 탈출조건
END LOOP;

 

WHILE문

WHILE 반복 조건 LOOP
 LOOP
 문장;
END LOOP;

 

FOR LOOP문

FOR 인덱스 IN 시작 값... 종료값
 LOOP
 문장;
END LOOP;

 

예외부(EXCEPTION)

EXCEPTION
 WHEN 조건 THEN
   SET 명령어;

 

실행부(TRANSACTION)

COMMIT : 트랜잭션 성공

ROLLBACK : 트랜잭션 비정상 종료로 연산을 다시 하거나 연산 취소

 

사용자 정의 함수

사용자 정의 함수 : 프로시저와동일하고 반환에서의 부분만 프로시저와 다르다, 종료시 단일값을 반환

 

-선언부

-시작/종료부

-제어부

-SQL

-예외부

-반환부

 

트리거 : 삽입, 삭제, 수정등(DML)의 데이터변경 이벤트가 발생하면 DBMS에서 자동 실행 

트리거 목적 : 무결성 유지, 로그 메시지 출력 등과 작업 자동 실행

트리거 종류

-행 트리거 : 변화가 생길때마다 실행

-문장 트리거 : 단 한번 실행

 

트리구 구성

-선언부

-이벤트부

-시작/종료부

-제어부

-SQL

-예외부

 

트리거 인벤트 순서

-BEFORE : DML 수행 전에 트리거 실행

-AFTER  : DML 성공 실행 후 트리거 실행

 

응용 SQL 작성

데이터 분석 함수 : 총합, 평균 등 데이터 분석을 위해서는 복수행 기준 데이터를 모아 처리

특성 

-복수행을 그룹별로 모아놓고 그룹당 단일 계산 결과 반환

-GROUP BY 구문을 활용하여 복수행 그룹핑

-SELECT, HAVING, ORDER BY 구문에 활용

 

데이터 분석 함수의 종류

-집계 함수 : 전체 행으로부터 하나의 결과값 반환

-그룹 함수 : 컬럼 값에 따라 그룹화하여 중간 합계 분석 데이터 산출

-윈도 함수 : 데이터베이스를 사용한 온라인 분석 처리

 

집계 함수

-GROUP BY 구문

-HAVING 구문

 

집계 함수 종류

-COUNT : 줄의 수

-SUM : 컬럼 합계

-AVG : 컬럼 평균

-MAX : 컬럼 최대값

-MIN : 컬럼 최솟값

-STDDEV : 컬럼간 표준편차

-VARIAN : 컬럼간의 분산

 

그룹함수

-ROLLUP 함수 : 지정 컬럼보다 하나 더 큰 레벨만큼 중간 집계, 소계 등 중간 집계 값을 산출

-CUBE 함수 : 결합 가능한 모든 값에 다차원 집계

-GROUPPING SET 함수 : 컬럼 순서와 무관한 결과를 얻을 수 있는 그룹 함수

 

윈도 함수 = OLAP 함수

-순위 함수

-행순서 함수

-그룸 내 비율 함수

 

순위 함수

-RANK : 특정 항목에 대한 순위를 구하는 함수, 동일 순위의 레코드 존재 시 후순위는 넘어감

-DENSE_RANK : 레코드의 순위 계산, 동일 순위의 레코드 존재 시에도 후순위로 넘어가지 않음

-ROW_NUMBER : 레코드의 순위 계산, 동일 순위의 값이 존재해도 이와 무관하게 연속 번호 부여

 

행 순서 함수

-FIRST_VALUE : 파티션별 윈도우에서 가장 먼저 나오는 값을 찾음

-LAST_VALUE : 파티션별 윈도우에서 가장 늦게 나오는 값을 찾음

-LAG : 파티션별 윈도우에서 이전 로우의 값 반환

-LEAD : 파티션별 윈도우에서 이후 로우의 값 반환

 

그룹 내 비율 함수

-RATIO_TO_REPORT :  주어진 그룹에 대해 합을 기준으로 각 로우의 상대적 비율 반환

-PERCENT_RANK :  : 주어진 그룹에 대해 제일 먼저 나오는것을 0, 제일 늦게 나오는 것을 1로 하면 순서별 백분율

 

응용 시스템 DBMS 접속 기술

자바 데이터베이스 연결(JDBC)

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

 

MyBatis 

- SQL MAPPING 기반 오픈 소스 DBMS에 질의하기 위한 SQL 쿼리를 별도의 XML파일로 분리하고 Mapping 을 통해서 SQL을 실행

-MyBatis는 DBMS 의존도가 높고 SQL 질의언어를 사용하기 때문에 국내 실무 개발 환경에 맞아 많이 사용

-#{파라미터명} 으로 사용

장점

- 복잡한 JDBC 코드 단순화

-SQL을 거의 그대로 사용

-Spring 기반 프레임워크와 통합 기능 제공

-우수한 성능

-조건에 따라 SQL 구문 자체 변경 가능

 

데이터 제어어(DCL)

-GRANT : 데이터 베이스 권한 부여

-REVOKE : 데이터 베이스 권한 회수

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

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