본문 바로가기
SQLD/SQL 정처기 이론

SQL(Structured Query Langauge) 응용

red_jh 2025. 5. 27. 07:28

절차형 SQL 작성

[ 절차형 SQL ]

 

1) 절차형 SQL 개념

  • 절차형 SQL 개념은 일반적인 개발 언어처럼 절차 지향적인 프로그램이 가능한 SQL

2) 절차형 SQL 개념

정류 설명
트리거(Ttigger) 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL
사용자 정의 함수(User-Defined Function) 절차형 SQL 을 활용하여 일련의 연산 처리 결과를 단일 값으로 반환할 수 있는 함수
단순 함수 정의
프로시저(Procedure) 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
여러 함수들이나 쿼리들을 통해 하나의 흐름을 구상

 

 

 

[ SQL 문법 ]

 

1) SQL 문법의 개념

  • SQL 문법은 DB 를 접근하고 조작하는 데 필요한 표준 언어를 활용할 수 있게 해주는 규칙

2) SQL 문법의 종류

분류 설명
데이터 정의어(DDL) 데이터를 정의하는 언어
테이블이나 관계의 구조를 생성하는 데 사용
CREATE, ALTER, DROP, TRUNCATE 문이 있음
데이터 조작어(DML) DB 에 저장된 자료들을 입력, 수정, 삭제, 조회 하는 언어
SELECT, INSERT, UPDATE, DELETE 문이 있음
SELECT ANSDMS 특별히 질의어(Query)라고 부름
데이터 제어어(DCL) DB 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 사용하는 제어용 언어
GRANT, REVOKE 문이 있음

 

 

3) WHERE 조건

  • WHERE 조건 절에서는 비교, 범위, 집합, 패턴, NULL, 복합 조건 등을 다룸
구분 연산자 사례
비교 =, <>, <, <=, >, >= PRICE < 50000
PRICE 속성의 값이 50000 미만
범위 BETWEEN PRICE BETWEEN 50000 AND 80000
PRICE 속성 값이 50000 이상 80000 이하
집합 IN, NOT IN PRICE IN (40000, 50000, 60000)
PRICE 속성 값이 40000 또는 50000 또는 60000
패턴 LIKE NAME LIKE '정보%'
NAME 속성값이 '정보'로 시작되는 문자열
NULL IS NULL, IS NOT NULL PRICE IS NULL
PRICE 속성 값이 NULL 인 경우
복합조건 AND, OR, NOT (PRICE < 50000) AND (NAME LIKE '정보%')
PRICE 속성값이 50000 보다 작으면서 NAME 속성값이 '정보'로 시작되는 문자열

(col BETWEEN  A  AND  B) == (col >= A  AND  B <=  B)

 

4) LIKE 와 같이 사용하는 와일드 문자

  • LIKE 와 같이 사용되는 와일드 문자는 +, %, [ ], [ ^ ], _ 가 있음
와일드 문자 설명 사례
+ 문자열을 연결 '축구' + '감독': '축구 감독'
% 0 개 이상의 문자열과 일치 LIKE '키워드%'
= '키워드' 로 시작하는 문자영 검색
[ ] 1 개의 문자와 일치 '[0~8]%' = 0 - 8 사이 숫자로 시작하는 문자열
[ ^ ] 1 개의 문자와 불일치  '[^0~8]%' = 0 - 8 사이 숫자로 시작하니 않는 문자열
_ 특정 위치의 1개의 문자와 일치 '_동%' = 두 번째 위치에 '동'이 들어가는 문자열

응용 SQL 작성

[ 데이터 조작어(DML:Data Manipulation Language) ]

 

1) 데이터 조작어의 개념

  • DML
  • 데이터 조작어는 DB 에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어

2) DML 유형

유형 동작 설명
SELECT 데이터 조회 해당 테이블을 구성하는 튜플 중에서 전체 또는 조선을 만족하는 튜플을 검색하여
주기억장치 상에 임시 테이블로 구성하는 명령문
INSERT 데이터 삽입 해당 테이블에 새로운 튜플을 삽입할 때 사용하는 명령문
UPDATE 데이터 변경 해당 테이블에 있는 튜플 중에서 특정 튜플의 내용을 변경할 때 사용하는 명령문
DELETE 데이터 삭제 해당 테이블에 있는 튜플 중에서 특정 튜플을 삭제할 때 사용하는 명령문

 

3) 데이터 조작어 명령어

  • SELECT : 데이터의 내용을 조회할 때 사용하는 명령어
SELECT [ALL|DISTINCT] 속성명1, 속성명2, ...
    FROM 테이블 명1, ...
[WHERE 조건]
[GROUP BY 속성명1, ... ]
[HAVING 그룹조건]
[ORDER BY 속성 [ASC|DESC]];

 

구분 설명
SELECT 절 검색하고자 하는 속성명, 계산식
2개 이상의 테이블을 대상으로 검샐할 때는 '테이블명, 속성명' 으로 기입
술어 부분은 ALL 이 기본값
ALL
- 모든 튜플을 검색할 때 사용
- SELECT 뒤에 명시하지 않을 경우 ALL 로 인식
DISTINCT
- 중복된 속성이 조회될 경우 그 중 한 개만 검색
(SELECT 뒤에 명시된 속성이 중복될 경우 한 개만 검색)
FROM 절 질의에 의해 검색될 데이터들을 포함하는 테이블명을 기술
GROUP BY 절 속성값을 그룹으로 분류하고자 할 때 사용
HAVING 절 GROUP BY 에 의해 분류한 후 그룹에 대한 조건을 기술
ORDER BY 절 속성값을 정렬하고자 할 때 사용
ASC 와 DESC 키워드 생략시 오름차순(ASC) 정렬
ASC
- 오름차순
DESC
- 내림차순
  • DISTINCT 는 기본적으로 행을 기준으로 중복을 구분한다.
  • 행 한 줄 한 줄 비교하면서 각 컬럼이 전부 동일한 값이 있는 행의 경우 제거를 한다

employees

id name department
1 Alice Sales
2 Bob HR
3 Alice Sales
4 Alice HR 
SELECT DISTINCT name FROM employees # 조회할 행의 구성 column 은 name
Alice
Bob 
SELECT DISTINCT name, department FROM emplyees # 조회할 행의 구성 column 은 name 과 department
Alice Sales
Bob HR
Alice HR

마지막 행은 첫 행과 동일하게 Alice 를 보유하고 있지만, department 값이 다르기 때문에 다른 행으로 간주

 

  • INSERT : 데이터의 내용을 삽입할 때 사용하는 명령어
[학생] 테이블에 학번: 6677, 성명: '장길산', 학년: 3학년, 수강과목: '수학' 인 학생을 삽입

INSERT INTO 학생(학번, 성명, 학년, 수강과목)
VALUES(6677, '장길산', 3, '수학');
구분 설명
INSERT INTO 테이블명(속성명1, ... ) 속성수에 맞춰 데이터 개수, 데이터 타입이 일치 해야함
INTO(데이터1, ... ); 속성명 생략가능

 

 

  • UPDATE : 데이터의 내용을 변경할 때 사용하는 명령어
[학생] 테이블에 장길산의 주소를 '인천'으로 수정

UPDATE 학생
    SET 주소 = '인천'
 WHERE 이름 = '장길산';
구분 설명
UPDATE 테이블명
SET 속성명1 = 데이터1, 속성명2 = 데이터2, ...
WHERE 조건;		 UPDATE 명령문은 WHERE 절을 통해 어떤 조건이 만족할 경우 특정 column 의 값을 수정하는 용도로 자주 사용

 

 

DELETE : 데이터의 내용을 삭데할 때 사용하는 명령어

[학생] 테이블에 '장길산'에 대한 튜플을 삭제

DELETE FROM 학생
WHERE 이름='장길산';
구분 설명
DELETE FROM 학생
WHERE 조건;		 모든 레코드를 삭제할 때는 WHERE 절 없이 DELETE 만 사용
레코드 삭제시에도 테이블 구조는 남아 있어 디스크에서 테이블을 완전히 삭제하는 DROP 명령과는 다름

 

 

 

[ 데이터 제어어(DCL:Date Control Langauge) ]

 

1) 데이터 제어어의 개념

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

 

2) DCL 기능

기능 설명
데이터 보안 불법적인 사용자로부터 데이터를 보호
무결성 유지 데이터의 정확성과 일관성을 유지
병행수행 제어 여러 트랜잭션을 수행할 때 트랜잭션들이 DB 의 일관성을 파괴하니 않도록 트랜잭션 간의 상호작용을 제어
회복 DB 장애 발생시 DB 를 장애 발생 이전의 상태로 복원

 

3) DCL 유형

유형 명령어 동작 설명
DCL GRANT 상용 권한 부여 DBA 가 사용자게에 DB 에 대한 권한을 부여
REVOKE 사용 권한 취소 DBA 가 사용자에게 DB 에 대해 부여했던 권한을 회수
DCL/TCL COMMIT 트랜잭션 확정 DB 트랜잭셩의 내용 업데이트 를 영구적으로 확정
REVOKE 트랜잭션 취소 DB 에서 업데이트 오류 발생시, 최근 COMMIT 이전 상태로 되돌림
SAVEPOINT(=CHECKPOINT) 저장 시기 설정 트랜잭션의 특정 지점에 이름을 지정하고, 그 지점 이전에 수행한 작업에 영향을 주지 않고 그 지점 이후에 수행한 작업을 ROLLBACK 을 수행
  • GRANT
    • WITH GRANT OPTION 을 통해 권한을 부여한 사용자에게도 '다른 사용자에게 권한을 부여'할 수 있는 권한을 같이 사용
  • REVOKE 
    • CASCADE 를 통해 WITH GRANT OPTION 으로 권한을 부여받은 다른 사용자들의 권한도 일괄 회수하는 기능을 같이 사용
    • RESTRICT 를  사용하면 권한을 취소하려는 유저가 다른 유저에게 권한을 부여했다면 REVOKE 는 적용되지 않는다
  • SQL 권한 시스템은 정확한 근거(chain of authority)를 기준으로 설계됨
    • 권한의 출처가 사라지면, 그 위에 구축된 권한도 무효화됨
    • 즉, 사용자 A 의 권한만 회수하고, A 로 부터 권한을 부여받은 다른 유저들은 내버려두는 경우는 존재하지 않음

 

4) DCL 명령어

  • GRANT : DBA 가 사용자에게 DB 에 대한 권한을 부여하는 명령어
DBA 가 '장길산'에게 '학생' 테이블에 대해 수정할 수 있는 권한 부여

GRANT UPDATE ON 학생 TO 장길산;

----------------------------------------------------------------------------------------
DBA 가 '장길산'에게 '학생' 테이블에 대해 조회할 수 있는 권한과 그 권한을 다른 사용자에게 부여할 수 있는 권한 부여

GRANT SELECT ON 학생 TO 장길산 WITH GRANT OPTION;
권한 구문 설명
시스템 권한 GRANT 권한 TO 사용자; DBA 가 사용자에게 테이블 | 뷰 | 프로시저 등을 실행하고 삭제할 수 있는 권한 부여
객체 권한 GRANT 권한 ON 테이블 TO 사용자; DBA 가 사용자에게 테이블을 수정, 삽입, 삭제, 조회와 프로시저 실행을 할 수 있는 권한 부여

 

  • REVOKE : DBA 가 사용자에게 부여했던 권한을 회수하는 명령어
DBA 가 사용자 '장길산'에게 '학생' 테이블에 대해 수정할 수 있는 권한을 회수

REVOKE UPDATE ON 학생 FROM 장길산;
--------------------------------------------------------------------------------------------
DBA 가 사용자 '장길산'에게 '학생' 테이블에 대해 조회를 할 수 있는 권한과 WITH GRANT OPTION을 통해 권한을 부여받은 다른 사용자들의 권한까지 회수

REVOKE SELECT ON 학생 FROM 장길산 [CASCADE | RESTRICT];
권한 구문 설명
시스템 권한 REVOKE 권한 FROM 사용자; DBA 가 사용자에게 테이블 | 뷰 | 프로시저 등을 생성하고 삭제할 수 있는 권한 회수
객체 궎나 REVOKE 권한 ON 테이블 FROM 사용자; DBA 가 사용자에게 테이블을 수정, 삽입, 삭제, 조회와 프로시저 실행할 수 있는 권한을 회수

 

  • TCL
    • 트랜잭션을 제어하는 언어로써 COMMIT, ROLLBACK, SAVEPOINT(=CHECKPOINT), ROLLBACK TO SAVEPOINT 가 있음
# A 테이블에서 COMMIT 과 ROLLBACK 을 같이 사용하는 경우(삽입 1 과 2 두 개가 됐지만, 실행 완료 후 테이블에 1만 남아있음)

INSERT INTO A VALUES(1);
COMMIT; # COMMIT 명령어 동작 시 기존 명령어가 DB에 영구적으로 삽입
INSERT INTO A VALUES(2);
ROLLBACK; # ROLLBACK 명령어 동작 시 COMMIT 이후 명령어는 원상 복구
--------------------------------------------------------------------------------------------------------------------------
# A 테이블에서 SAVEPOINT, COMMIT, ROLLBACK을 같이 사용하는 경우(실행 완료 후 테이블에 1, 3이 남아있음)

INSERT INTO A VALUES(1);
SAVEPOINT SP;              # SAVEPOINT 를 SP 라는 이름으로 설정
INSERT INTO A VALUES(2);
ROLLBACK TO SAVEPOINT SP;   # SP 지정된 부분부터 현재 위치 사이에 실행한 명령어 원상복구
INSERT INTO A VALUES(3);
COMMIT;
  • 이전 SAVEPOINT 와 이후 SAVEPOINT 의 이름을 동일하게 하면 SAVEPOINT 가 이후 지점으로 덮어쓰기 됨
명령문 구분 설명
트랜잭션 확정 COMMIT; DB 트랜잭션 내용 업데이트를 영구적으로확정
트랜잭션 취소 ROLLBACK; DB 업데이트 오류 발생 시 이전 상태로 되돌림
세이브 포인트 지정 SAVEPOINT 이름; 특점 지정을 지정
세이브 포인트 롤백 ROLLBACK TO SAVEPOINT 이름; SAVEPOINT 로 지정한 부분 이후에 발생한 트랜잭션 취소

 

 

[ 윈도 함수(Window Function) ]

 

1) 윈도함수의 개념

  • 행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수
  • DB를 사용한 온라인 분석 처리 용도로 사용하기 위해서 SQL 에 추가된 기능으로 OLAP함수라고도 함
  • OLAP(On-line Analytical Processing) 
    • 의사결정 지원 시스템
    • 사용자가 동일한 데이터를 여러 기준을 이용하는 다양한 방식으로 바라보면서 다차원 데이터 분석을 할 수 있도록 도와주는 기술

 

2) 윈도 함수의 구문

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

 PARTITION BY 는 선택 항목이며, 순위를 정할 대상 범위의 컬럼을 설정 PARTITION BY 구에는 GROUP BY 구가 가진 집약 기능이 없으며, 이로 인해 레코드가 줄어들지 않음

PARTITION BY 를 통해 구분된 레코드 집합을 윈도라고 함

윈도 함수에는  OVER 문구가 필수적으로 포함

ORDER BY 뒤에는 SORT 컬럼을 입력(어떤 열을 어떤 순서로 순위를 정할지를 지정)

 

3) 윈도 함수의 분류

분류 설명
집계 함수 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수
순위 함수 레코드 순위를 계산하는 함수
RANK, DENSE_RANK, ROW_NUMBER 등
행 순서 함수 레코드에서 가장 먼저 나오거나 가장 뒤에 나오는 값 이전/이후 값들을 출력하는 함수
FIRST_VALUE, LAST_VALUE, LAG, LEAD 등
그룹 내 비율 함수 백분율을 보여주거나 행의 순서별 백분율 등 비율과 관련된 통계를 보여주는 함수
PATIO_TO_REPORT, RERCENT_RANK 등

 

  • 집계함수
집계 함수 설명
COUNT 복수 행의 줄 수를 구하는 함수
SUM 복수 행의 해당 컬럼에 대한 합계를 구하는 함수
AVG 복수 행의 해당 컬럼 간의 평균에 대한 평균을 구하는 함수
MAX 복수 행의 해당 컬럼 중 최댓값을 구하는 함수
MIN 복수 행의 해당 컬럼 중 최솟값을 구하는 함수
STDDEV 복수 행의 해당 컬럼에 대한 표준편차를 구하는 함수
VARIANCE 복수 행의 해당 컬럼에 대한 분산을 구하는 함수

 

  • 순위함수
순위 함수 설명
RANK 특정 항목(컬럼)에 대한 순위를 구하는 함수
동일 순위의 레코드 존재시 후순위는 넘어감
ex(1위, 1위, 3위, 4위, 4위, 4위 ,7위 ... )
DENSE_RANK 레코드 순위를 계산하는 함수
동일 순위의 레코드 존재시 후순위를 넘어가지 않음
ex(1위, 1위, 2위, 3위, 3위, 3위, 4위 ... )
ROW_NUMBER 레코드의 순위를 계산하는 함수
동일 순위의 값이 존재해도 이와 무관하게 연속 번호를 부여
ex(1위, 2위, 3위, 4위, 5위, 6위, 7위 ... ) 공동 순위 인정 X
- OVER( ORDER BY ...) 설정하면 해당  기준으로 공동 순위에서 차등 순위를 배분
- OVER() 로 설정시 DBMS 내부 처리 순서에 의존함

 

  • 행 순서 함수
행 순서 함수 설명
FIRST_VALUE 파티션별 윈도에서 가장 먼저 나오는 값을 반환하는 함수
오름차순(내림차순) 시 집계 함수의 MIN(MAX) 과 동일한 결과를 출력
LAST_VALUE 파티션별 윈도에서 가장 늦게 나오는 값을 반환하는 함수
오름차훈(내림차순) 시 집계 함수의 MAX(MIN) 와 동일한 결과 출력
LAG 파티션별 윈도에서 이전 로우의 값을 반환하는 함수
LEAD 파티션별 윈도에서 이후 로우의 값을 반환하는 함수

 

  • LAG 예시
SELECT 이름, 점수,
       LAG(점수) OVER (ORDER BY 점수 DESC) AS 이전_점수
FROM 학생;

 

 

학생

이름 점수 이전_점수
민수 95 NULL
철수 90 95
영희 85 90
민지 70 85

 

  •  LEAD 예시
SELECT 이름, 점수,
       LEAD(점수) OVER (ORDER BY 점수 DESC) AS 다음_점수
FROM 학생;

 

학생

이름 점수 다음_점수
민수 95 90
철수 90 85
영희 85 70
민지 70 NULL

 

 

  • 그룹 내 비율 함수
그룹 내 비율 함수 설명
RATIO_TO_PERPORT 주어진 그룹에 대해 합을 기준으로 각 로우의 상대적 비율을 반환하는 함수 (합계 대비 비율 측정)
결괐값은 0 ~ 1 의 범위 값을 가짐
PERCENT_RANK 주어진 구룹에 대해 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1 로 하여, 
값이 아닌 행의 순서별 백분율을 구하는 함수 (정렬 기준 상대적 위치 보기)
결과값은 0 ~ 1 범위 값을 가짐

 

  • RATIO_TO_PERPORT 예시

매출데이터

부서 매출
A 100
A 300
A 600

 

SELECT 부서, 매출,
  RATIO_TO_REPORT(매출) OVER (PARTITION BY 부서) AS 매출비율
FROM sales;

 

 

부서 매출 매출비율
A 100 0.1
A 300 0.3
A 600 0.6

 

  • PERCENT_RANK 예시

시험점수

이름 점수
철수 70
영희 80
민수 90
희수 100 
SELECT 이름, 점수,
  PERCENT_RANK() OVER (ORDER BY 점수) AS 순위비율
FROM students;
이름 점수 순위비율
철수 70 0.0
영희 80 0.333
민수 90 0.667
희수 100 1.0

 

 

4) 윈도 함수 활용

  • OLAP 개념
    • OLAP 은 사용자가 동일한 데이터를 여러 기준을 이용하는 다양한 방식으로 바라보면서 다차원 데이터 분석을 할 수 있도록 도와주는 의사결정 지원 시스템
  • OLAP 연산
연산 설명
Roll-up 분석할 항목에 대해 구체적인 데이터로부터 요약된 형태의 테이터로 접근하는 연산
Drill-down 분석할 항목에 대해 요약된 형태의 데이터로부터 구체적인 데이터로 접근하는 연산
Slicing 온라인 분석처리를 위한 자료 구조인 데이터 큐브의 한 조각을 볼 수 있게 해주는 연산
Dicing 고정된 다차원 값에 대한 연산
Rivoting 다차원 분석 테이블인 크로스 체이블에서 차원 변경을 위해 사용되는 연산

 

 

[ 그룹 함수(Group Function) ]

 

1) 그룹 합수 개념

  • 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수
  • 소계 침 총계 등을 구하기 위해서 그룹 함수를 사용한다면 단일 DML 만으로도 원하는 작업을 할 수 있음

2) 그룹 함수 유형

  • ROLLUP
    • 소그룹 간 소계 출력
    • 지정된 컬럼의 소계 및 총계를 구하기 위해 하용하는 그룹 함수
    • 지정 컬럼의 수보다 하나 더 큰 레벨만큼의 집계 값이 생성
    • ROLLUP 의 지정 컬럼은 계층별로 구성되기 때문에 순서가 바뀌면 수행 결과가 바뀌기 때문에 유의해서 사용해야 함
구문 설명
SELECT 컬럼명1, ..., 집계함수
   FROM 테이블명
[ WHERE ... ]
GROUP BY [ 컬럼명1, ... ] ROLLUP (그룹화할 열)
[ HAVING ... ]
[ ORDER BY ... ]		 소계 집계 대상이 되는 컬럼을 ROLLUP 키워드 뒤에 기재
소계 집계 대상이 아닌 경우 GROUP BY 키워드 뒤에 기재
SELECT 뒤에 포함되는 컬럼이 GROUP BY 또는 ROLLUP 키워드 뒤에 기재 되어야 함
ORDER BY 구문을 활용해 계층 내 정렬에 사용 가능
  •  ROLLUP 예제

sales

부서 연도 매출
A 2023 100
A 2024 120
B 2023 200

 

SELECT 부서, 연도, SUM(매출)
FROM sales
GROUP BY ROLLUP(부서, 연도);

 

부서 연도 SUM(매출)
A 2023 100
A 2024 120
A NULL 220  ←  A 부서 소계
B 2023 200
B NULL 200  ←  B 부서 소계
NULL NULL 420  ←  전체 총계(== 소계 총계)

 

  • CUBE
    • GROUP BY 항목들과 다차원 소계 출력
    • 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수
    • 내부적으로 대상 컬럼의 순서를 변경하여 또 한 번의 쿼리를 수행함
    • 연산이 많아 시스템에 부담을 준다
    • 기준 컬럼부터 우측으로 계층적으로 소계 값을 도출함
구문 설명
SELECT 컬럼명, ..., 집계 함수
   FROM 테이블명
[ WEHRE ... ]
GROUP BY [ 컬럼명1, ... ] CUBE(컬럼명A, ... )
[ HAVING ]
[ ORDER BY ... ]		 소계 집계 대상이 되는 컬럼을 CUBE 키워드 뒤에 기재
소계 집계 대상이 아닌 경우 GROUP BY 키워드 뒤에 기재
SELECT 뒤에 포함되는 컬럼이 GROUP BY 또는 CUBE 키워드 뒤에 기재되어야 함
ORDRE BY 구문을 활용해 계층 내 정렬에 사용 가능

 

 

GROUP BY vs ROLLUP vs CUBE 결과 비교

 

sales

부서 연도 매출
A 2023 100
A 2024 120
B 2023 90
B 2024 80

 

GROUP BY

SELECT 부서, 연도, SUM(매출)
FROM sales
GROUP BY 부서, 연도;

 

부서 연도 SUM(매출)
A 2023 100
A 2024 120
B 2023 90
B 2024 80

 

ROLLUP

SELECT 부서, 연도, SUM(매출)
FROM sales
GROUP BY ROLLUP(부서, 연도);

 

부서 연도 SUM(매출)
A 2023 100
A 2024 120
A NULL 220  <-  A 부서의 소계
B 2023 90
B 2024 80
B NULL 170  <-  B 부서의 소계
NULL NULL 390  <- 전체 총계

 

CUBE

SELECT 부서, 연도, SUM(매출)
FROM sales
GROUP BY CUBE(부서, 연도);

 

부서 연도 SUM(매출)
A 2023 100
A 2024 120
A NULL 220  <-  A 부서 소계
B 2023 90
B 2024 80
B NULL 170  <-  B 부서 소계
NULL 2023 190   <-  2023 연도 소계
NULL 2024 200  <-  2024 연도 소계
NULL NULL 390  <-  전체 총계

 

 

 

[ 오류 처리(Error Handling) ]

 

1) 오류 처리의 개념

  • 코드상의 오류나 프로시저 실행 시 예외나 에러가 발생했을 때, 문제를 해결하고 의미 있는 에러 메시지를 부여하는 과정

2) 핸들러 선언 구문

DECLARE (1) HANDLER
         FOR (2)  (3);
구문 요소 설명
(1) 액션 해당 오류 발생 시 핸들러 처리 방법
( 특정 쿼리에서 오류 발생시 다음 쿼리를 계속 할 것인지, 종료 할 것인지에 대한 동작 설정)
COUNINUE
- (3) 명령어 부분을 계속 실행
EXIT
- (3) 명령어 부분을 한 번 실행 ( 이 한 번의 쿼리에서 오류 발생시 로직 종료 )
(2) 상태값 핸들러가 수행되기 위한 에러코드
SQLWARNING
- 에러가 아닌 경고가 발생
NOTFOUND
- 다음 레코드를 가져오지 못할 때
SQLEXCEPTION
- 에러가 발생

(2) 상태값이 NOTFOUND 인 경우,
쿼리 실행시 NOTFOUND 에러가 발생하면 해당 핸들러 실행
(3) 명령문 핸들러가 호출될 때 실행되는 명령

 

  • 핸들러 선언을 통한 오류처리 사례
사례 설명
DECLARE CONTINUE HANLER
         FOR SQLEXCEPTION
         SET TEST_ERROR = 5;		 에러가 발생시 TEST_ERROR 변수의 값을 5 로 설정하는 핸들러
DECLARE EXIT HANDLER
          FOR SQLEXCEPTION
BEGIN
     ROLLBACK;
     SELECT '에러 발생, ROLLBACK 수행';
END;		 어떤 에러가 발생하면, 이전 연산자로 되돌아 간 후 에러 메시지가 발생하는 핸들러
에러 핸들러 구문(BEGIN ~ END) 이후 시작된 트랜잭션 과정에서 쿼리 성공시
SAVEPOINT 를 설정했다면 해당 SP 지점으로 트랜잭션들을 취소하고, SP 가 없다면 쿼리 시작 부분까지 전부 취소한다
DECLARE CONTINUE HANDLER
         FOR NOTFOUND
         SET NO_ROW_TEST = 5;		 만약 더이산 가져올 행이 없다면 NO_ROW_TEST 변수의 값을 5 로 설정하고 계속 실행하는 의미를 가지를 핸들러

 

 

티스토리툴바