[Ⅶ] SQL 응용

박은지·2022년 4월 24일

정보처리기사

정보처리기사 실기 노트

목록 보기

8/11

1. 데이터베이스 기본

🔷 트랜잭션 ( Transaction )

DB 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

◼ 특성 ⭐ #ACID 🔔

원자성 ( Atomicity ) : 분해가 불가능한 작업의 최소단위, 연산 전체가 성공 또는 실패
일관성 ( Consistency ) : 트랜잭션이 실행 성공 후 항상 일관된 DB 상태를 보존
격리성 ( Isolation ) : 트랜잭션 실행 중 생성하는 연산의 중간 결과를 다른 트랜잭션이 접근 불가
영속성 ( Durability ) : 성공이 완료된 트랜잭션의 결과는 영속적으로 DB에 저장

◼ 상태 변화 🔔

활동 상태 ( Active ) : 초기 상태, 트랜잭션이 실행 중
부분 완료 상태 ( Partially Committed ) : 마지막 명령문이 실행된 후
완료 상태 ( Committed ) : 트랜잭션이 성공적으로 완료
실패 상태 ( Failed ) : 정상적인 실행이 더 이상 진행될 수 없음
철회 상태 ( Aborted ) : 트랜잭션이 취소, DB가 트랜잭션 시작 전 상태로 환원

◼ 트랜잭션 제어 언어 ( TCL ; Transaction Control Language ) ⭐ #커롤체 🔔

커밋 ( Commit ) : 트랜잭션을 메모리에 영구적으로 저장
롤백 ( Rollback ) : 트랜잭션 내역을 저장 무효화
체크 포인트 ( Checkpoint ) : Rollback을 위한 시점을 지정

◼ 병행 제어 ( 일관성 주요 기법, Concurrency Control ) ⭐ #로낙타다 🔔

다수 사용자 환경에서 여러 트랜잭션 수행 시, 데이터 베이스 일관성 유지를 위해 상호작용을 제어

로킹 ( Locking ) : 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법
낙관적 검증 : 일단 트랜잭션을 수행하고, 종료 후 검증
타임 스탬프 순서 ( Time Stamp ordering ) : 트랜잭션이 실행을 시작하기 전에 타임 스탬프를 부여해 부여된 시간에 따라 수행
다중 버전 동시성 제어 ( MVCC ; Multi Version Concurrency Control ) : 타임 스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근

◼ 데이터베이스 고립화 수준 ( 격리성 주요 기법 )

다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도

Read Uncommitted : 한 트랜잭션에서 연산 중인 데이터를 다른 트랜잭션이 읽는 것을 허용
Read Committed : 한 트랜잭션의 연산이 완료( Commit )된 후 데이터 읽기 가능
Repeatable Read : 트랜잭션 종료 시까지 해당 데이터에 대한 갱신/삭제 제한
Serializable Read : 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체에 대한 접근 제어

◼ 회복 기법 ( 영속성 주요 기법, Recovery ) ⭐

트랜잭션을 수행하는 도중 장애로 인해 손상된 DB를 손상되기 이전의 정상적인 상태로 복구시키는 작업

로그 기반 회복 기법
- 지연 갱신 회복 기법 : 트랜잭션이 완료된 후 DB에 기록
- 즉각 갱신 회복 기법 : 트랜잭션 수행 중 갱신결과를 바로 DB에 반영
체크 포인트 회복 기법 : 장애 발생 시 검사점(체크포인트) 이후에 처리된 트랜잭션만 복원
그림자 페이징 회복 기법 : 트랜잭션 수행 시 복제본을 생성하여 장애 시 이를 이용해 복구

🔷 데이터 정의어 ( DDL ; Data Definition Language ) 🔔

데이터를 정의하는 언어
특정 구조를 생성, 변셩, 삭제, 이름 변경하는 데이터 구조와 관련된 명령어

◼ DDL 대상 ⭐ #도스테뷰인

도메인 ( Domain ) : 하나의 속성이 가질 수 있는 원자값들의 집합
스키마 ( Schema ) : 데이터베이스의 구조, 제약조건, 정보를 담고 있는 기본적인 구조
- 외부 ( External ) 스키마 : 사용자나 개발자 관점에서 필요로 하는 DB의 논리적 구조
- 개념 ( Conceptual ) 스키마 : DB의 전체적인 논리적 구조
- 내부 ( Internal ) 스키마 : 물리적 저장장치의 관점에서 보는 DB 구조
테이블 ( Table ) : 데이터 저장 공간
뷰 ( View ) : 하나 이상의 물리 테이블에서 유도되는 가상의 테이블
인덱스 ( Index ) : 검색을 빠르게 하기 위한 데이터 구조

◼ DDL 명령어 ⭐ #크리 알 드 크러 ( 크리스마스 계란 두 트럭 ) 🔔

명령어	구분	설명	문법
CREATE	생성	DB 오브젝트 생성	CREATE TABLE 테이블명
ALTER	수정	DB 오브젝트 수정	ALTER TABLE 테이블명
DROP	삭제	DB 오브젝트 삭제	DROP TABLE 테이블명
TRUNCATE	삭제	DB 오브젝트 내용 삭제	TRUNCATE TABLE 테이블명

❐ CREATE TABLE 🔔

✓ 기본 문법

CREATE TABLE 테이블명
(
  컬럼명  데이터타입  [ 제약조건 ],
  . . .
)

✓ 제약조건

PRIMARY KEY : 기본키 지정 ( PK )
FOREIGN KEY : 외래키 지정 ( FK )
UNIQUE : 유일한 값을 갖도록 지정
NOT NULL : NULL 값을 갖지 않도록 지정
CHECK : 개발자가 정의하는 제약 조건
DEFAULT : 데이터를 INSERT할 때 해당 컬럼의 값을 갖지 않는 경우 기본값으로 설정

❐ ALTER TABLE 🔔

✓ 컬럼 추가

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [ 제약조건 ] ;

✓ 컬럼 수정

ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 [ 제약조건 ] ;

✓ 컬럼 삭제

ALTER TABLE 테이블명 DROP 컬럼명 ;

❐ DROP TABLE 🔔

✓ 기본 문법

DROP TABLE 테이블명 [ CASCADE | RESTRICT ] ;

✓ 제약조건

CASCADE : 참조하는 테이블까지 연쇄적으로 제거
RESTRICT : 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않음

❐ TRUNCATE TABLE 🔔

✓ 기본 문법

TRUNCATE TABLE 테이블명 ;

🔷 데이터 조작어 ( DML ; Data Manipulation Language )

DB에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어

◼ DML 명령어 ⭐ #세인 업데 ( 세인이 집에 없대 ) 🔔

명령어	구분	설명	문법
SELECT	조회	테이블 내 컬럼에 저장된 데이터 조회	SELECT 컬럼명 FORM 테이블명
INSERT	삽입	테이블 내 컬럼에 데이터 삽입	INSERT INTP 테이블명 VALUE 데이터
UPDATE	갱신	테이블 내 컬럼에 저장된 데이터 갱신	UPDATE 테이블명 SET 속성 WHERE 조건
DELETE	삭제	테이블 내 컬럼에 저장된 데이터 삭제	DELETE FORM 테이블명 WHERE 조건

❐ SELECT 명령어 🔔

✓ 기본 문법

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

① SELECT 절 : 테이블에서 출력할 컬럼을 명사하는 절

ALL : 모든 튜플 검색 (기본값)
DISTICNT : 중복된 속성이 조회될 경우 그 중 한 개만 검색

② FROM 절 : 검색될 데이터를 포함하는 테이블 명시

③ WHERE 절 : 검색할 조건 기술

비교
- = : 값이 같은 경우
- <> , != : 값이 다른 경우
- < , <= , > , >= : 비교 연산에 해당하는 데이터 조회
범위
값1보다 크거나 같고, 값2보다 작거나 같은 데이터
- 컬럼 BETWEEN 값1 AND 값2
- 컬럼 >= 값1 AND 컬럼 <= 값2
집합
- 컬럼 IN ( 값1, 값2, ..., ) : IN 안에 포함된 경우 조회
- 컬럼 NOT IN ( 값1, 값2, ..., ) : IN 안에 포함되어 있지 않은 경우
패턴
컬럼이 패턴에 포함된 경우 데이터 조회
- 컬럼 LIKE 패턴
  - % : 0개 이상의 문자열과 일치
  - [ ] : 1개의 문자와 일치
  - [^] : 1개의 문자와 불일치
  - _ : 특정 위치의 1개의 문자와 일치
NULL
- 컬럼 IS NULL : 컬럼이 NULL인 데이터 조회
- 컬럼 IS NOT NULL : 컬럼이 NULL이 아닌 데이터 조회
복합조건
- 조건1 AND 조건2 : 조건1과 조건2 모두를 만족하는 데이터 조회
- 조건1 OR 조건2 : 조건1과 조건2 둘 중 하나을 만족하는 데이터 조회
- 조건1 NOT 조건2 , 조건1 ! 조건2 : 조건에 해당하지 않는 데이터 조회

④ GROUP BY 절 : 속성값을 그룹으로 분류하고자 할 때 사용

⑤ HAVING 절 : GROUP BY에 의해 분류한 후 그룹에 대한 조건을 지정할 때 사용

⑥ ORDER BY : 속성값을 정렬하고자 할 때

ASC : 오름차순
DESC : 내림차순

✓ 조인 : 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법
[1] 내부 조인 ( Inner Join ) : 공통 존재 컬럼의 값이 같은 경우를 추출하는 방법 테이블1 [ INNER ] JOIN 테이블2 ON 조인조건

SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2 ...
FROM 테이블1 A [ INNER ] JOIN 테이블2 B ON 조인조건
[ WHERE 검색조건 ];

[2] 외부 조인 ( Outer Join )

왼쪽 외부 조인 ( Left Outer Join ) : 왼쪽 (all) & 오른쪽 테이블의 (동일 데이터) 테이블1 LEFT [ OUTER ] JOIN 테이블2 ON 조인조건

SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2 ...
FROM 테이블1 A  LEFT [ OUTER ] JOIN 테이블2 B ON 조인조건
[ WHERE 검색조건 ];

오른쪽 외부 조인 ( Right Outer Join ) : 왼쪽 (동일 데이터) & 오른쪽 테이블의 (all) 테이블1 RIGHT [ OUTER ] JOIN 테이블2 ON 조인조건

SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2 ...
FROM 테이블1 A  RIGHT [ OUTER ] JOIN 테이블2 B ON 조인조건
[ WHERE 검색조건 ];

완전 외부 조인 ( Full Outer Join ) : 왼쪽 (all) & 오른쪽 (all) 테이블1 FULL [ OUTER ] JOIN 테이블2 ON 조인조건

SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2 ...
FROM 테이블1 A  FULL [ OUTER ] JOIN 테이블2 B ON 조인조건
[ WHERE 검색조건 ];

[3] 교차 조인 ( Cross Join ) : 조인 조건이 없는 모든 데이터 조합을 추출하는 기법 테이블1 CROSS JOIN 테이블2

// 조인 조건 없음!

SELECT 컬럼1, 컬럼2, ...
FROM 테이블1 CROSS JOIN 테이블2

[4] 셀프 조인 ( Self Join ) : 자기 자신에게 별칭을 지정한 후, 다시 조인하는 기법 테이블1 [ INNER ] JOIN 테이블1 ON 조인조건

// 같은 테이블, 다른 별칭!

SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2 ...
FROM 테이블1 A [ INNER ] JOIN 테이블1 B ON 조인조건
[ WHERE 검색조건 ];

✓ 서브쿼리 : SQL문 안에 포함된 또 다른 SQL문

SELECT 절 서브쿼리
- 스칼라 서브쿼리 ( Scalar Sub-Query )
- 반드시 단일 행 리턴
- SUM, COUNT, MIN, MAX 등의 집계함수 많이 사용
FROM 절 서브쿼리
- 인라인 뷰 ( Inline Views )
- 뷰처럼 결과가 동적으로 생성된 테이블 형태로 사용할 수 있음
WHERE 절 서브쿼리
- 중텁 서브쿼리 ( Nested Sub-Query )

✓ 집합 연산자 : 테이블을 집합 개념으로 보고, 두 테이블 연산에 집합 연산자를 사용

UNION : 중복 행이 제거된 쿼리 결과를 반환하는 합집합
UNION ALL : 중복 행도 포함한 쿼리 결과를 반환하는 합집합
INTERSECT : 공통적으로 존재하는 결과를 반환하는 교집합
MINUS : 첫 쿼리에 있고 두 번째 쿼리에는 없는 결과를 반환하는 차집합

❐ INSERT 명령어 🔔

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

❐ UPDATE 명령어 🔔

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

❐ DELETE 명령어 🔔

DELETE FROM 테이블명
WHERE 조건 ;

💡 DELETE는 테이블이 남아 있고, DROP은 테이블을 완전히 삭제하는 명령어이다!

🔷 데이터 제어어 ( DCL ; Data Control Language )

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

GRANT : 사용자에게 DB에 대한 권한을 부여
REVOKE : 사용자에게 부여했던 DB에 대한 권한을 회수

◼ GRANT ( 권한 부여 ) 명령어 🔔

DB 관리자( DBA : Database Administrator )가 사용자에게 DB에 대한 권한을 부여하는 명령어

GRANT 권한 ON 테이블 TO 사용자 ;

◼ REVOKE ( 권한 회수 ) 명령어 🔔

DB 관리자( DBA : Database Administrator )가 사용자로부터 DB에 대한 권한을 회수하는 명령어

REVOKE 권한 ON 테이블 FROM 사용자 ;

2. 응용 SQL 작성하기

🔷 데이터 분석 함수

◼ 집계 함수 ( Aggregate Function ) 🔔

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

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

COUNT : 복수 행의 줄 수
SUM : 복수 행의 해당 컬럼 간의 합계
AVG : 복수 행의 해당 컬럼 간의 평균
MAX : 복수 행의 해당 컬럼 중 최댓값 반환
MIN : 복수 행의 해당 컬럼 간의 최솟값 반환
STDDEV : 복수 행의 해당 컬럼 간의 표준편차
VARIAN : 복수 행의 해당 컬럼 간의 분산

◼ 그룹 함수 🔔

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

❐ ROLLUP

저장된 컬럼은 소계 등 중간 집계 값을 산출하기 위한 함수
소계 집계 대상이 되는 컬럼을 ROLLUP 뒤에 기재하고, 소계 집계 대상이 아닌 경우 GROUP BY 뒤에 기재한다.

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

❐ CUBE

결합 가능한 모든 값에 대해 다차원 집계를 생성
연산이 많아 시스템에 부담을 준다.

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

❐ GROUPING SETS

집계 대상 컬럼들에 대한 개별 집계를 구할 수 있다.
ROLLUP이나 CUBE와 달리 컬럼 간 순서와 무관한 결과를 얻을 수 있다.
GROUPING SETS를 이용해 다양한 소계 집합을 만들 수 있다.
ORDER BY를 사용하여 집계 대상 그룹과의 표시 순서를 조정하여 체계적으로 보여줄 수 있다.

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

◼ 윈도 함수 ( OLAP 함수 ) 🔔

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

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

❐ 순위 함수

레코드의 순위를 계산하는 함수

RNAK : 특정 항목(컬럼)에 대한 순위를 구하는 함수 ( 동일 순위 레코드 존재 시, 후순위는 넘어감 → [ 2위가 3개이면 ] 1위 2위 2위 2위 5위 6위... )
DENSE_RNAK : 레코드의 순위 계산 ( 동일 순위 레코드 존재 시, 후순위를 넘어가지 않음 → [ 2위가 3개이면 ] 1위 2위 2위 2위 3위 4위... )
ROW_NUMBER : 레코드의 순위 계산 ( 동일 순위의 값이 존재해도 이와 무관하게 연속 번호 부여 → [ 2위가 3개이면 ] 1위 2위 3위 4위 5위... )

SELECT NAME,
       SALARY,
       RANK( ) OVER ( ORDER BY SALARY DESC ) A,
       DENSE_RANK( ) OVER ( ORDER BY SALARY DESC ) B,
       ROW_NUMBER( ) OVER ( ORDER BY SALARY DESC ) C
FROM EMPLOYEE;

❐ 행 순서 함수

레코드에서 가장 먼저 나오거나 가장 뒤에 나오는 값, 이전/이후의 값들을 출력하는 함수

FIRST_VALUE : 파티션별 윈도에서 가장 먼저 나오는 값을 찾음
LAST_VALUE : 파티션별 윈도에서 가장 늦게 나오는 값을 찾음
LAG : 파티션별 윈도에서 이전 로우의 값 반환
LEAD : 파티션별 윈도에서 이후 로우의 값 반환

SELECT NAME,
       SALARY,
       FIRST_VALUE( NAME ) OVER ( ORDER BY SALARY DESC ) A,
       LAST_VALUE( NAME ) OVER ( ORDER BY SALARY DESC ) B,
       LAG( NAME ) OVER ( ORDER BY SALARY DESC ) C,
       LEAD( NAME ) OVER ( ORDER BY SALARY DESC ) D
FROM EMPLOYEE;

❐ 그룹 내 비율 함수

백분율을 보여주거나 행의 순서별 백분율 등 비율과 관련된 통계를 보여주는 함수

RATIO_TO_REPORT : 주어진 그룹에 대해 합을 기준으로 각 로우의 상대적 비율을 반환하는 함수 ( 결괏값은 0~1 )
PERCENT_RANK : 주어진 그룹에 대해 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로하여, 값이 아닌 행의 순서별 백분율을 구하는 함수 ( 결괏값은 0~1 )

SELECT NAME,
       SALARY,
       RATIO_TO_REPORT( SALARY ) OVER ( ) A,
       PERCENT_RANK ( ) OVER ( ORDER BY SALARY DESC ) B
FROM EMPLOYEE;

3. 절차형 SQL 활용하기

🔷 절차형 SQL ( Procedural Language SQL )

일반적인 개발 언어처럼 SQL 언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어

프로시저 ( Procedure ) : 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
사용자 정의함수 ( User-Defined Function ) : 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL
트리거 ( Trigger ) : 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 직업이 자동으로 수행되는 절차형 SQL

◼ 출력부

DBMS_OUTPUT.PUT( 문자열 ) : 개행 없이 문자열 출력
DBMS_OUTPUT.PUT_LINE( 문자열 ) : 문자열 출력 후 개행

◼ 제어부 ( CONTROL )

[1] 조건문
① IF 문 : 조건이 참인지 거짓인지에 따라 경로 선택

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

② 간단한 케이스 문 ( Simple Case Expression ) : 명확한 값을 가지는 조건에 따라 여러 개의 선택 경로 중 하나를 취하고자 할 때 사용하는 조건문

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

③ 검색된 케이스 문 ( Searched Case Expression ) : 명확한 값 및 범위를 가지는 조건에 따라 여러 개의 선택 경로 중 하나를 취하고자 할 때 사용하는 조건문

CASE 변수
  WHEN 조건1 THEN
   SET 명령어;
  WHEB 조건2 THEN
   SET 명령어;
  ...
  ELSE
   SET 명령어;
END CASE;

[2] 반복문
① LOOP 문 : 특정 조건이 만족될 때까지 반복해서 문장을 실행하는 반복문

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

② WHILE 문 : 시작과 종료 조건을 지정하여 참인 동안에는 해당 문장을 반복해서 실행하는 명령문

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

③ FOR LOOP 문 : 시작 값과 끝값을 지정하여 해당 값이 그 구간 내에 있을 때 반복하는 반복문

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

◼ 예외부 ( EXCEPTION )

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

EXCEPTION
  WHEN 조건 THEN
  SET 명령어;

🔷 프로시저 ( Procedure )

일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

◼ 구성요소 #DB 컨닝 SET

선언부 ( DECLARE ) : 명칭, 변수, 데이터 타입 정의
시작/종료부 ( BEGIN / END ) : 프로시저의 시작과 종료를 표현
제어부 ( CONTROL ) : 조건문과 반복문을 이용하여 문장 처리
SQL : DML을 주로 사용
예외부 ( EXCEPTION ) : SQL문이 실행될 때 예외 발생 시 예외 처리 방법 정의
실행부 ( TRANSACTION ) : 프로시저에서 수행된 DML 수행 내역의 DBMS 적용 또는 취소 여부 결정

◼ 문법

CREATE [OR REPLACE] PROCEDURE 프로시저명
( 파라미터명 [IN | OUT | INPUT] 데이터타입, ... )
IS
  변수선언
BEGIN
  명령어;
[COMMIT | ROLLBACK]
END;

🔷 사용자 정의 함수 ( User-Defined Function )

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

◼ 구성요소 #DB 컨설

⭐ 기본 개념/ 사용법/ 문법 등은 프로시저와 동일
⭐ 반환 부분만에서 " 종료 시 단일 값을 반환한다 "는 점에서 다르다.

선언부 ( DECLARE ) : 사용자 정의 함수 명칭
시작/종료부 ( BEGIN / END ) : 사용자 정의 함수의 시작과 종료를 표현
제어부 ( CONTROL ) : 조건에 따라 문장 실행
SQL : 조회 용도로 SELECT 사용 / 데이터를 조작하는 INSERT, DELETE, UPDATE 사용 불가
예외부 ( EXCEPTION ) : SQL문이 실행될 때 예외 발생 시 예외 처리 방법 정의
반환부 ( RETURN ) : 호출문에 대한 함숫값 반환

◼ 문법

CREATE [OR REPLACE] FUNCTION 함수명
( 파라미터명 [IN | OUT | INPUT] 데이터타입, ... )
IS
  변수선언
BEGIN
  명령어;
  RETURN 변수;  // ----> 종료 시 단일 값을 반환!!!
END;

🔷 트리거 ( Trigger )

데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 직업이 자동으로 수행되는 절차형 SQL

◼ 종류

행 트리거 : 데이터 변화가 생길 때마다 실행
문장 트리거 : 트리거에 의해 단 한 번 실행

◼ 구성 #DEB 컨닝 SET

⭐ 프로시저/사용자정의함수와 기본 문법은 동일
⭐ 반환 값이 없고, DML을 주된 목적을 한다는 점에서 프로시저와 동일
⭐ EVENT 명령어를 통해 트리거 실행을 위한 이벤트를 인지하고, 외부 변수 IN/ OUT 이 없다는 점이 다르다.

선언부 ( DECLARE ) : 트리거의 명칭 정의
이벤트부 ( EVENT ) : 트리거가 실행되는 타이밍, 이벤트를 명시하는 부분
시작/종료부 ( BEGIN / END ) : 트리거의 시작과 종료를 표현
제어부 ( CONTROL ) : 조건에 따라 문장 실행
SQL : DML을 주로 사용
예외부 ( EXCEPTION ) : SQL문이 실행될 때 예외 발생 시 예외 처리 방법 정의

◼ 문법

CREATE [OR REPLACE] TRIGGER 트리거명
( 파라미터명 [IN | OUT | INPUT] 데이터타입, ... )
IS
  변수선언
BEGIN
  명령어;
  RETURN 변수;  // ----> 종료 시 단일 값을 반환!!!
END;

4. 데이터 조작 프로시저 최적화

🔷 쿼리 성능 개선 ( 튜닝 )

데이터베이스에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시터를 수정하는 작업

절차
1. 문제 있는 SQL 식별
2. 옵티마이저 통계 확인
3. SQL문 재구성
4. 인덱스 재구성
5. 실행계획 유지관리

🔷 옵티마이저 ( Optimizer )

SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심 엔진

◼ 유형

❐ 규칙기반 옵티마이저 ( RBO ; Rule Based Optimizer )

통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 쿼리 실행계획을 선택
규칙 ( 우선 순위 ) 기반
사용자가 원하는 처리 경로로 유도하기 쉬움

❐ 비용기반 옵티마이저 ( CBO ; Cost Based Optimizer )

통계 정보로부터 모든 접근 경로를 고려한 쿼리 실행 계획을 선택
비용 ( 수행 시간 ) 기반
옵티마이저의 이해도가 낮아도 성능보장 가능

◼ 역할

쿼리 변환 ( Query Transformer ) : SQL을 좀 더 일반적이고 표준화된 형태로 변환
비용 산정 ( Estimator ) : 쿼리 명령어 각 단계의 선택도, 카디널리티, 비용 계산 / 실행계획 전체에 대한 총비용 계산
계획 생성 ( Plan Generator ) : 하나의 쿼리를 수행 시 후보군이 될 만한 실행 계획들을 생성 해내는 역할

◼ SQL 힌트

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

SQL 성능 개선의 핵심 부분으로 옵티마이저의 실행 계획을 원하는대로 변경 가능
옵티마이저가 항상 최선의 실행 계획을 수립할 수 없어 명시적인 힌트를 통해 실행계획 변경

❐ 주요 옵티마이저 힌트

/*+ RULE */ : 규칙 기반 접근 방식을 사용하도록 지정
/*+ CHOOSE */ : 오라클 옵티마이저 디폴트 값에 따름
/*+ INDEX( 테이블명 인덱스명 ) */ : 지정된 인덱스를 강제적으로 사용하도록 지정
/*+ USE_HASH( 테이블명 ) */ : 지정된 테이블의 조인이 Hash Join 형식으로 일어나도록 유도
/*+ USE_MERGE( 테이블명 ) */ : 지정된 테이블의 조인이 Sort Merge 형식으로 일어나도록 유도
/*+ USE_NL( 테이블명 ) */ : 지정된 테이블의 조인이 Nested Loop 형식으로 일어나도록 유도

박은지

이전 포스트

[Ⅵ] 프로그래밍 언어 활용 (2)

다음 포스트

[Ⅶ] SQL 응용

정보처리기사 실기 노트

1. 데이터베이스 기본

🔷 트랜잭션 ( Transaction )

◼ 특성 ⭐ #ACID 🔔

◼ 상태 변화 🔔

◼ 트랜잭션 제어 언어 ( TCL ; Transaction Control Language ) ⭐ #커롤체 🔔

◼ 병행 제어 ( 일관성 주요 기법, Concurrency Control ) ⭐ #로낙타다 🔔

◼ 데이터베이스 고립화 수준 ( 격리성 주요 기법 )

◼ 회복 기법 ( 영속성 주요 기법, Recovery ) ⭐

🔷 데이터 정의어 ( DDL ; Data Definition Language ) 🔔

◼ DDL 대상 ⭐ #도스테뷰인

◼ DDL 명령어 ⭐ #크리 알 드 크러 ( 크리스마스 계란 두 트럭 ) 🔔

❐ CREATE TABLE 🔔

❐ ALTER TABLE 🔔

❐ DROP TABLE 🔔

❐ TRUNCATE TABLE 🔔

🔷 데이터 조작어 ( DML ; Data Manipulation Language )

◼ DML 명령어 ⭐ #세인 업데 ( 세인이 집에 없대 ) 🔔

❐ SELECT 명령어 🔔

❐ INSERT 명령어 🔔

❐ UPDATE 명령어 🔔

❐ DELETE 명령어 🔔

🔷 데이터 제어어 ( DCL ; Data Control Language )

◼ GRANT ( 권한 부여 ) 명령어 🔔

◼ REVOKE ( 권한 회수 ) 명령어 🔔

2. 응용 SQL 작성하기

🔷 데이터 분석 함수

◼ 집계 함수 ( Aggregate Function ) 🔔

◼ 그룹 함수 🔔

❐ ROLLUP

❐ CUBE

❐ GROUPING SETS

◼ 윈도 함수 ( OLAP 함수 ) 🔔

❐ 순위 함수

❐ 행 순서 함수

❐ 그룹 내 비율 함수

3. 절차형 SQL 활용하기

🔷 절차형 SQL ( Procedural Language SQL )

◼ 출력부

◼ 제어부 ( CONTROL )

◼ 예외부 ( EXCEPTION )

🔷 프로시저 ( Procedure )

◼ 구성요소 #DB 컨닝 SET

◼ 문법

🔷 사용자 정의 함수 ( User-Defined Function )

◼ 구성요소 #DB 컨설

◼ 문법

🔷 트리거 ( Trigger )

◼ 종류

◼ 구성 #DEB 컨닝 SET

◼ 문법

4. 데이터 조작 프로시저 최적화

🔷 쿼리 성능 개선 ( 튜닝 )

🔷 옵티마이저 ( Optimizer )

◼ 유형

❐ 규칙기반 옵티마이저 ( RBO ; Rule Based Optimizer )

❐ 비용기반 옵티마이저 ( CBO ; Cost Based Optimizer )

◼ 역할

◼ SQL 힌트

❐ 주요 옵티마이저 힌트

[Ⅵ] 프로그래밍 언어 활용 (2)

[Ⅷ] 서버 프로그램 구현

0개의 댓글