절차형SQL
오류처리의 개념
- 프로그램 코드 상의 구문 오류 또는 프로그램 실행시 상황에 따라 발생하는 오류를 처리하는 과정
오류 처리 방법
- 오류 복구
- 예외상황을 파악하고 문제를 해결하여 정상상태로 돌려 놓는것
- 오류 회피
- 예외 처리를 자신을 호출한 쪽으로 던져 버림
- 오류 전환
- 예외를 메소드 밖으로 던지지만 예외 회피와 달리 적절한 예외로 전환해서 던짐
절차형SQL - 집계성DCL작성
절차형SQL
데이터 분석 함수의 정의
- 관계형 데이터베이스에서는 단일행 기준의 처리가 주로 이루어지나 총합, 평균 등의 데이터 분석은 복수 행 기준의 데이터를 모아서 처리
- 다중행 처리를 목적으로 하는 다중 행 함수가 존재
데이터 분석 함수의 특성
- 단일행을 기반으로 산출하지 않고 복수 행을 그룹별로 모아 놓고 그룹당 단일 계산 결과를 반환
- GROUP BY 구문을 활용하여 복수행을 그룹핑
데이터 분석 함수의 유형
- 데이터 튜플 간의 상호 연관 및 계산 분석을 위한 세 가지
- 집계 함수
- 그룹 함수
- 윈도우 함수
- 집계 함수는 명령어도 상대적으로 단순하며 이를 기본으로 하여 그룹 함수나 윈도우 함수에도 적용
- 집계 함수는 그룹 함수의 한 부분
집계 함수의 구문
- GROUP BY 구문 뒤에 테이블을 구분하는 컬럼을 기재하여 그룹화
- HAVING 구문은 WHERE 조건으로 지정된 데이터 집합으로 부터 그룹화 된 집합에 대한 조건 선택 시에 사용
집계함수의 분류
- COUNT(입력값)
- 복수 행의 줄 수
- SUM(입력값)
- 복수 행의 해당 컬럼 간의 합계
- AVG(입력값)
- 복수 행의 해당 컬럼 간의 평균
- MAX(입력값)
- 복수 행의 해당 컬럼 간의 최댓값
- MIN(입력값)
- 복수 행의 해당 컬럼 간의 최솟값
- STDDEV(입력값)
- 복수 행의 해당 컬럼 간의 표준편차
- VARIAN(입력값)
- 복수 행의 해당 컬럼 간의 분산
그룹 함수의 개념
- 소계 및 총계등을 구하기 위해서 집계 함수만 사용한다면 레벨별 집계를 위한 각 단계별 데이터 질의어를 UNION ALL 등으로 결합하고 표시하는 단계 필요
- 단일 DQL만으로도 원하는 작업 가능
- 정렬이 필요한 경우 ORDER BY절에 정렬 컬럼 명시
그룹 함수의 종류
- ROLLUP
- 소계 등 중간 집계 값을 산출 위한 칼럼을 ROLLUP 뒤 기재
- 지정 칼럼의 수 보다 하나 더 큰 레벨의 중간 집계 값이 생성
- SELECT 뒤에 포함되는 칼럼이 GROUP BY 또는 ROLLUP 뒤에 기재되어야 한다는 점 숙지
- CUBE
- 결합 가능한 모든 값에 대해 다차원 집계를 생성하며, 가능한 한 소집계만을 생성하는 ROLLUP과 구별
- 내부적으로 대상 컬럼의 순서를 변경하여 또 한 번의 쿼리를 수행
- 지정 컬럼의 순서가 바뀌어도 결과는 같음
- GROUPING SETS
- 원하는 특정 항목에 대한 다양한 소계 집합 계산 가능
- 집계 대상 칼럼 들에 대한 개별 집계를 구할 수 있고 칼럼 간 순서가 바뀌어도 정렬 순서는 바뀔 수 있으나 결과는 같음
윈도우 함수의 개념
- 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 기능
- 온라인 분석 처리는 시장 분석, 통계 작성, 경영 계획 분석 및 수립 등 비즈니스 현장에서 자주 사용되는 분석이 포함
- 데이터 기반 의사 결정의 증가에 따라 그 중요성이 더욱 증가하는 실정
윈도우 함수의 구분
- PARTITION BY는 선택 항목이며 순위를 정할 대상 범위의 컬럼을 설정
- OVER문구가 필수적으로 포함되며, ORDER BY 뒤에는 SORT컬럼을 입력
윈도우 함수의 종류
- 집계함수
- SUM
- MAX
- MIN
- AVG
- COUNT
- 순위함수
- RANK : 레코드의 순위를 계산하며, 동일 순위 레코드 존재 시 후 순위는 넘어감
- DENSE_RANK : “”, 동일 순위의 레코드 존재 시에도 후순위를 넘어가지 않음
- ROW_NUMBER : “”, 동일 순위의 값이 존재해도 이와 무관하게 연속 번호 부여
- 그룹 내 비율 함수
- RATIO_TO_REPORT
- PERCENT_RANK
- CUME_DIST
- NTILE
- 행 순서 함수
- FIRST_VALUE
- LAST_VALUE
- LAG
- LEAD
절차형SQL - DCL
절차형SQL
DCL; Data Control Language 정의
- 무결성, 보안 및 권한 제어, 회복을 위해 데이터베이스에 접근을 제어하는 SQL 명령어
DCL 조작 대상
| 오브젝트 | 목적 | 내용 |
|---|---|---|
| 사용자 권한 | 접근 통제 | 사용자를 등록하고, 사용자에게 특정 데이터베이스를 사용할 수 있는 권리를 부여하는 작업 |
| 트랜잭션 | 안전한 거래보장 | 동시에 다수의 작업을 독립적으로 안전하게 처리하기 위한 상호 작용 단위 |
DCL 유형과 명령어
- 트랜잭션 제어를 위한 명령어 TCL
- TCL; Transaction Control Language
- TCL과 DCL은 대상이 달라 서로 별개의 개념으로 분류할 수 있으나, 제어 기능의 공통점으로 DCL의 일부료 분류함
- 명령어
- DCL
- GRANT : 사용자 권한 부여
- REVOKE : 사용자 권한 회수
- TCL
- COMMIT : 트랜잭션 확정
- ROLLBACK : 트랜잭션 취소
- SAVEPOTINT : 복귀지점 설정
- DCL
사용자 권한 부여
- 권한은 시스템 권한과 객체 권한으로 분류
- 시스템 권한
1 | GRNAT 권한1, 권한2 TO 사용자 계정 |
- 객체 권한
1 | GRNAT 권한1, 권한2 ON 객체명 TO 사용자 계정 |
시스템 권한과 객체 권한의 종류
- 시스템 권한
- CREATE USER : 계정 생성 권한
- DROP USER : 계정 삭제 권한
- DROP ANY TABLE : 테이블 삭제 권한
- CREATE SESSION : 데이터베이스 접속 권한
- CREATE TABLE : 테이블 생성 권한
- CREATE VIEW : 뷰 생성 권한
- CREATE SEQUENCE : 시퀀스 생성 권한
- CREATE PROCEDURE : 프로시저 생성 권한
- 객체 권한
- ALTER : 테이블 변경 권한
- INSERT : 데이터 조작 권한
- DELETE : 데이터 조작 권한
- SELECT : 데이터 조작 권한
- UPDATE : 데이터 조작 권한
- EXECUTE : PROCEDURE 실행 권한
사용자 권한 회수
- GRANT에 대응하는 권한 회수 명령은 REVOKE
- 시스템 권한
1 | REVOKE 권한1, 권한2 FROM 사용자계정 |
- 객체 권한
1 | REVOKE 권한1, 권한2 ON 객체명 FROM 사용자계정 |