2020년 정보처리기사 실기 정리 #8

프로시저

절차형 SQL을 활용해 특정 기능을 수행할 수 있는 트랜잭션 언어

 

프로시저 구성 (디비컨 SET)

선언부, 시작/종료부, 제어부, SQL, 예외부, 실행부

선언부
프로시저의 명칭, 변수와 인수 그리고 그에 대한 데이터 타입을 정의하는 부분

 

시작/종료부
프로시저의 시작과 종료를 표현하는 곳 BEGIN/END가 쌍을 이룸

 

제어부
기본적으로 순차적으로 처리

 

SQL
DML을 주로 사용

 

예외부
BEGIN~END 절에서 실행되는 SQL 문이 실행될 때 예외 발생시 예외 처리 방법을 정의하는 처리부

 

실행부
트리거에서 수행된 DML 수행 내역의 DBMS의 적용 또는 취소 여부를 결정하는 처리부

 

선언부

CREAT
DBMS 내 객체를 생성

 

OR REPLACE
기존 프로시저 존재 시 현재 컴파일하는 내용으로 덮어씀

 

PROCEDURE
프로시저를 사용한다는 의미

 

프로시저 명
해당 프로시저를 지칭하는 이름

 

파라미터 명
프로시저와 운영체제 간 필요한 값을 전송하기 위한 인자

 

MODE
변수의 입출력을 구분하고 IN, OUT, INOUT 3가지로 구성
IN ; 운영체제에서 프로시저로 값을 전달하는 모드
OUT : 프로시저에서 처리된 결과를 운영체제로 전달하는 모드
INOUT : IN, OUT의 두가지 기능을 동시에 수행하는 모드

 

데이터 타입
해당 파라미터에 대한 데이터 타입 (용도에 따라 다르다)
CHAR : 고정 길이 문자열 데이터 타입
VARCHAR : 가변 길이 문자열 데이터 타입
NUMBER : 숫자를 저장하는 데이터 타입

 

IS [AS]
PL/SQL의 블록을 시작, IS OR AS 키워드를 작성

 

변수의 선언
프로시저 내 변수와 변수에 대한 초기값 설정

 

시작/종료부

BEGIN
프로시저의 시작을 알려주는 명령

 

END
프로시저의 끝을 알려주는 명령

 

제어부

실행흐름을 제어하는 부분으로 크게 조건문과 반복문으로 나뉜다
조건문은 IF문과 CASE문으로 나뉘며, CASE 문은 다시 단순 CASE문과 검색된 CASE문으로 나뉜다.
반복문은 LOOP문과 WHILE문, FOR LOOP문으로 구성된다

 

조건문 – IF

IF~ELSIF~ELSE문으로 사용할 수 있다

 

EX)

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

 

단순 케이스문

명확한 값을 가지는 집합에 대한 표현식의 값을 매칭하는 것만 허용한다

 

EX)

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

 

검색된 케이스문

IF문과 비슷하지만 IF문에 비해 가독성이 좋다

 

EX)

CASE
WHEN 조건 1 THEN
SET 명령어;
WHEN 조건 2 THEN
SET 명령어;
ELSE
SET 명령어;
END CASE;

 

반복문 – LOOP

LOOP문은 특정 조건이 만족할 때까지 반복해서 문장을 실행한다.

 

EX)

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

 

반복문 – WHILE

WHILE문은 조건이 참일 경우 반복하고, 조건이 거짓이거나 EXIT WHEN 조건을 만족하는 경우 반복문을 빠져나간다.

 

EX)

WHILE 반복조건 LOOP
문장;
EXIT WHEN 탈출조건;
END LOOP;

 

반복문 – FOR LOOP

시작과 끝 값을 지정하여 해당 값이 구간내에 있을 때까지 반복한다

 

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

 

프로시저 SQL (DML : 데이터 조작어)

SELECT
데이터 조회
해당 테이블을 구성하는 튜플들 중에서 전체 또는 조건을 만족하는 튜플을 검색하는 명령

 

INSERT
데이터 생성
해당 테이블에 새로운 튜플을 삽일할 때 사용하는 명령

 

UPDATE
데이터 변경
해당 테이블에 있는 튜플들 중에서 특정 튜플의 내용을 변경할 때 사용하는 명령

 

DELETE
데이터 삭제
해당 테이블에 있는 튜플들 중에서 특정 튜플을 삭제할 때 사용하는 명령

 

예외부

실행 중 발생가능한 예외사항을 수행하는 부분이다.

 

EXCEPTION
WHEN 조건 THEN
SET 명령어;

 

실행부

해당 프로시저에서 수행한 DML을 DBMS에 반영할지 복구할지를 결정하는 부분

 

COMMIT
하나의 트랜잭션이 성공적으로 끝나고, DB가 일관성 잇는 상태에 있을 때 트랜잭션이 끝났을 때 사용하는 연산

 

ROLLBACK
하나의 트랜잭션이 비정상적으로 종료되어 트랜잭션 원자성이 깨질 경우 처음부터 다시 시작하거나, 부분적으로 연산을 취소하는 경우

 

프로시저 호출문 작성

응용 프로그램에서 호출하거나 내부 스케줄러에 의해 배치 작업을 수행하는 경우 사용된다.
SQL TOOL을 사용해 직접 실행시키는 경우에는 EXECUTE, EXEC 명령어를 이용해 프로시저를 실행시킨다

 

예)

EXECUTE 프로시저명 (파라미터_1, 파라미터_2)

 

사용자 정의 함수

절차형 SQL을 활용해 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL

 

사용자 정의 함수 구성 (디비컨 SER)

기본적으로 프로시저와 동일하고 반환에서의 부분만 프로시저와 다르다.

 

선언부
프로시저의 명칭, 변수와 인수 그리고 그에 대한 데이터 타입을 정의하는 부분

 

시작/종료부
프로시저의 시작과 종료를 표현하는 곳 BEGIN/END가 쌍을 이룸

 

제어부
기본적으로 순차적으로 처리

 

SQL
조회 용도로 SELECT 문을 사용

 

예외부
BEGIN~END 절에서 실행되는 SQL 문이 실행될 때 예외 발생시 예외 처리 방법을 정의하는 처리부

 

반환부
호출문에 대한 함수 값을 반환

 

선언부

CREAT
DBMS 내 객체를 생성

 

OR REPLACE
기존 프로시저 존재 시 현재 컴파일하는 내용으로 덮어씀

 

FUNCTION
FUNCTION은 사용자 정의함수를 사용한다는 의미

 

함수명
해당 사용자 정의함수를 지칭하는 이름

 

RETURN 데이터 타입
함수가 반환할 데이터 타입을 지정

 

파라미터 명
프로시저와 운영체제 간 필요한 값을 전송하기 위한 인자

 

MODE
변수의 입출력을 구분하고 IN, OUT, INOUT 3가지로 구성
IN ; 운영체제에서 프로시저로 값을 전달하는 모드
OUT : 프로시저에서 처리된 결과를 운영체제로 전달하는 모드
INOUT : IN, OUT의 두가지 기능을 동시에 수행하는 모드

 

데이터 타입
해당 파라미터에 대한 데이터 타입 (용도에 따라 다르다)

 

IS [AS]
PL/SQL의 블록을 시작, IS OR AS 키워드를 작성

 

변수의 선언
프로시저 내 변수와 변수에 대한 초기값 설정

 

시작/종료부

사용자 정의 함수의 실행 시작과 종료를 알려주는 부분으로 BEGIN, END 키워드를 사용한다

 

제어부

단위 블록별 실행 흐름을 제어하는 부분으로 크게 IF, CASE문으로 나뉜다

 

SQL

데이터 관리를 위해 조회, 추가, 수정, 삭제를 수행하는 부분

 

예외부

실행 중 발생 가능한 예외사항을 수행하는 부분

 

반환부

RETURN 명령을 통해 사용자 정의함수 종료 시 사용자 정의함수를 호출한 쿼리에 반환하는 단일값을 정의

 

트리거

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

 

트리거의 목적

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

 

트리거의 종류

행 트리거
데이터 변화가 생길 때마다 실행

 

문장 트리거
트리거에 의해 단 한 번 실행

 

트리거 구성 (디이비컨 SE)

선언부
트리거의 명칭을 정의하는 부분

 

이벤트부
트리거가 실행되는 타이밍, 이벤트를 명시하는 부분

 

시작/종료부
트리거의 시작과 종료를 표현하는 데 필수적이며, BEGIN/END가 쌍을 이루어 추가되므로 블록으로 구성

 

제어부
기본적으로 순차적으로 처리, 조건에 따라 반복 실행
비교 조건에 따라 블록 또는 문장을 실행

 

SQL
DML을 주로 사용하고, 자주 사용되지 않지만 DDL을 사용

 

예외부
BEGIN~END 절에서 실행되는 SQL 문이 실행될 때 예외 발생 시 예외처리 방법을 정의하는 처리부

 

선언부

CREAT
DBMS 내 객체를 생성

 

OR REPLACE
기존 트리거 존재 시 현재 컴파일하는 내용으로 덮어씀

 

TRIGGER
TRIGGER는 트리거를 사용한다는 의미

 

트리거 명
해당 트리거를 지칭하는 이름

 

이벤트부

트리거 타이밍, 이벤트, 수행할 테이블을 지정해야 한다

 

트리거 이벤트 순서

BEFORE
이벤트부의 테이블에 대한 INSERT/UPDATE/DELETE를 수행하기 전 트리거가 실행하도록 지정하는 명령

 

AFTER
이벤트부의 테이블에 대한 INSERT/UPDATE/DELETE가 성공적으로 실행되었을 때만 트리거가 실행하도록 지정하는 명령

 

트리거 이벤트 유형

INSERT
새로운 행 삽입

 

UPDATE
기존 행의 변경

 

DELETE
기존 행 삭제

 

시작/종료부

실행 시작과 종료를 알려주는 부분으로 프로시저에 BEGIN/END 키워드는 반드시 포함되어야 한다

 

제어부

단위 블록별 실행흐름을 제어하는 부분으로 크게 IF/CASE문이 나뉜다
SQL
데이터 관리를 위한 조회/추가/삭제/수정을 수행하는 부분이다

 

예외부

반드시 포함될 필요는 없으며 실행 중 발생 가능한 예외 상황을 수행하는 부분

 

데이터 분석 함수

총합, 평균 등 데이터 분석을 위해 복수 행 기준 데이터를 모아 처리하는 것을 목적으로 하는 다중 행 함수

 

데이터 분석 함수의 종류 (집그윈)

집계 함수 : 여러 행 또는 테이블 전테 행으로부터 하나의 결괏값을 반환하는 함수
그룹 함수 : 소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 함수
윈도 함수 : 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가 된 기능

 

집계 함수

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

 

집계 함수 구문

SELECT 컬럼1, 컬럼 2, ..., 집계함수
FROM 테이블명
[WHERE 조건]
GROUP BY 컬럼1, 컬럼2, ...
[HAVING 조건식(집계함수 포함)]

 

GROUP BY 구문

SQL에서 WHERE 구문을 활용 해 조건별 대상 ROW를 선택한다

 

HAVING 구문

WHERE 구문 내에는 사용할 수 없는 집계 함수 구문을 적용해 복수 행의 계산 결과를 조건별로 적용하는 데 사용된다.

 

집계 함수의 종류

COUNT
복수 행의 줄 수

 

SUM
복수 행의 해당 컬럼 간의 합계

 

AVG
복수 행의 해당 컬럼 간의 평균

 

MAX
복수 행의 해당 컬럼 중 최댓값

 

MIN
복수 행의 해당 컬럼 중 최솟값

 

그룹 함수

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

 

그룹 함수의 유형 – ROLLUP

ROLLUP에 의해 지정된 컬럼은 소계 등 중간 집계 값을 산출하기 위한 그룹함수이다.

 

그룹 함수 구문(ROLLUP)
SELECT 컬럼1, 컬럼 2, ..., 그룹함수
FROM 테이블명
[WHERE ...]
GROUP BY [컬럼 ... ] ROLLUP 컬럼
[HAVING ...][ORDER BY ...]
*소계 집계 대상이 되는 컬럼을 ROLLUP 뒤에 기재하고, 소계 집계 대상이 아닌 경우 GROUP BY 뒤에 기재한다.

 

그룹 함수의 유형 – CUBE

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

 

그룹 함수 구문(CUBE)
SELECT 컬럼1, 컬럼 2, ..., 그룹함수
FROM 테이블명
[WHERE ...]
GROUP BY [컬럼명1, ... ] CUBE 컬럼(컬럼명A, ...)
[HAVING ...][ORDER BY ...]
*ROLLUP은 소계, 중간 집계를 나타내 주지만, CUBE는 결합 가능한 모든 값에 대해 다차원 집계 생성

 

그룹 함수의 유형 – GROUPING SETS

집계 대상 컬럼들에 대한 개별 집계를 구할 수 있으며, ROLLUP과 CUBE와 같이 컬럼 간 순서와 무관한 결과를 얻을 수 있는 그룹 함수

 

그룹 함수 구문(GROUPING SETS)

SELECT 컬럼1, 컬럼 2, ..., 그룹 함수
FROM 테이블명
[WHERE ...]
GROUP BY [컬럼명1, ... ] GROUPING SETS (컬럼명A, ...)
[HAVING ...][ORDER BY ...]

 

윈도 함수

DB를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 함수이다.

 

윈도 함수의 구문

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

 

윈도 함수의 종류

순위 함수 : 레코드의 순위를 계산하는 함수 (RANK, DENSE_RANK, ROW_NUMBER)
행순서 함수 : 레코드에서 가장 먼저 나오거나 뒤에 나오는 값, 이전/이후의 값들을 출력하는 함수 (FIRST_VALUE, LAST_VALUE, LAST, LAG, LEAD)
그룹 내 비율 함수 : 백분율을 보여주거나 행의 순서별 백분율 등 비율과 관련된 통계를 보여주는 함수 (RATIO_TO_REPORT, PERCENT_RANK)

 

순위 함수

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

MyBatis는 SQL Mapping 기반 오픈 소스 엑세스 프레임워크로 DBMS에 질의하기 위한 SQL 쿼리를 별도의 XML 파일로 분리하고 매핑을 통해 SQL을 실행한다.

 

데이터 제어어(DCL)

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

 

데이터 제어어의 유형 (GRANK, REVOKE)

GRANT 사용 권한 부여 : 관리자가 사용자에게 DB에 대한 권한을 부여하는 명령어
REVOKE 사용 권한 취소 : 관리자가 사용자에게 DBD에 대한 권한을 회수하기 위한 명령어

 

GRANT (그온투위)

DB 관리자가 사용자에게 DB에 대한 권한을 부여하는 명령어
GRANT 권한 ON 테이블 TO 사용자 [WITH 권한 옵션];

 

REVOKE (리온프캐)

DB 관리자가 사용자에게 부여했던 권한을 회수하기 위한 명령어
REVOKE 권한 ON 테이블 FROM 사용자 [CASCADE CONSTIRAINT]