[SQLD] 2과목 - 2장 SQL 활용 요약

코소미 2023. 11. 14. 18:37

한국데이터산업진흥원 게시자료에 기반한 요약 정리

2과목 SQL 기본 및 활용

2장 SQL 활용

1절 표준조인

JOIN

두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력 하는 것
일반적으로 행들은 PK나 FK 값의 연관에 의해 JOIN이 성립된다.
어떤 경우에는 PK, FK 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립가능하다.
5가지 테이블을 JOIN 하기 위해서는 최소 4번의 JOIN 과정이 필요하다.

방 법	설 명
중첩 반복 조인 (Nested Loop Join)	- 좁은 범위에 유리 - 유리순차적으로 처리하며, Random Access 위주 - 후행(Driven) 테이블에는 조인을 위한 인덱스가 생성되어 있어야 함 - 실행속도 = 선행 테이블 사이즈 * 후행 테이블 접근횟수
색인된 중첩 반복 조인, 단일 반복 조인 (Single Loop Join)	- 후행(Driven) 테이블의 조인 속성에 인덱스가 존재할 경우 사용 - 선행 테이블의 각 레코드들에 대하여 후행 테이블의 인덱스 접근 구조를 사용하여 직접 검색 후 조인하는 방식
정렬 합병 조인 (Sort Merge Join)	- Sort Merge 조인은 해당 테이블의 인덱스가 없을때 수행이 된다. - 테이블을 정렬(Sort) 한 후에 정렬된 테이블을 병합(Merge) 하면서 조인을 실행한다. - 조인 연결고리의 비교 연산자가 범위 연산( >, < )인 경우 Nested Loop 조인보다 유리 - 두 결과집합의 크기가 차이가 많이 나는 경우에는 비효율적
해시 조인 (Hash Join)	- 해시(Hash)함수를 사용하여 두 테이블의 자료를 결합하는 조인 방식 - Nested Loop 조인과 Sort Merge 조인의 문제점을 해결 - 대용량 데이터 처리는 상당히 큰 hash area를 필요로 함으로, 메모리의 지나친 사용으로 오버헤드 발생 가능성

EQUI JOIN (등가 조인)
2 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용,
대부분 PK, FK의관계를 기반으로 한다.

SELECT PLAYER.PLAYER_NAME
FROM PLAYER
위 SQL처럼 컬럼명 앞에 테이블 명을 기술해줘야 함

NON EQUI JOIN (비등가 조인)
2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용
‘=’ 연산자가 아닌 BETWEEN, >, <= 등 연산자 사용

SELECT E.ENAME, E.JOB, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HSAL;
위는 E의 SAL의 값을 S의 LOSAL과 HSAL 범위에서 찾는 것이다.

ANSI/ISO SQL의 조인 형태
INNER JOIN, NATURAL JOIN, CROSS JOIN, OUTER JOIN, USING조건절, ON조건절

1. NATURAL JOIN

두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUIJOIN 수행,
NATURAL JOIN이 명시되면 추가로 USING, ON, WHERE 조건절에서 JOIN 조건을 정의할 수 없다(사용 불가)
SQLSever는 지원X,
같은 데이터 유형 칼럼만 조인 가능,
앨리어스나 테이블명 사용 불가

SQL>> SELECT 칼럼s FROM 테이블1 NATURAL JOIN 테이블2;

2. INNER JOIN ( =JOIN)

JOIN 조건에서 동일한 값이 있는 행만 반환,
USING이나 ON 조건절을 필수적으로 사용,
행에 동일한 값이 있는 칼럼 조인,
CROSS JOIN이나 OUTER JOIN과 동시 사용 불가,
두 테이블에 동일 이름 칼럼이 있을 경우 SELECT절에 앨리어스 필수

SQL>> SELECT 칼럼s FROM 테이블1 A, 테이블2 B WHERE A.칼럼 = B.칼럼;
SQL>> SELECT 칼럼s FROM 테이블1 A INNER JOIN 테이블2 B ON A.칼럼 = B.칼럼; (ANSI/ISO 표준)

3. USING 조건절

같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있다,
JOIN칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다,
SQL Server 지원X,
조건절에 앨리어스나 테이블명 불가

SQL>> SELECT 칼럼s FROM 테이블1 A JOIN 테이블2 B USING (칼럼명)

4. ON 조건절

ON 조건절과 WHERE 조건절을 분리하여 이해가 쉽다,
칼럼명이 다르더라도 JOIN 조건을 사용할수 있는 장점이 있다,
ALIAS나 테이블명 반드시 사용

SQL>> SELECT 칼럼s FROM 테이블1 A JOIN 테이블2 B ON (A.칼럼 = B.칼럼);

5. CROSS JOIN

가능한 모든 조합으로 조인,
테이블 간 조인조건이 없는 경우 생길 수 있는 모든 데이터의 조합,
양쪽 집합의 M*N건의 데이터 조합이 발생한다 => 곱집합 => 카테시안 곱 발생

SQL>> SELECT 칼럼 FROM 테이블1, 테이블2;

SQL>> SELECT 칼럼 FROM 테이블1 CROSS JOIN 테이블2;
(조인 조건이 없을 때 발생 ↔ NATURAL JOIN은 명시해야 됨)

https://hongong.hanbit.co.kr/sql-%EA%B8%B0%EB%B3%B8-%EB%AC%B8%EB%B2%95-joininner-outer-cross-self-join/

https://yunamom.tistory.com/321#answer15

6. OUTER JOIN

JOIN 조건에서 동일한 값이 없는 행도 반환 가능하다,
USING이나 ON 조건절 반드시 사용해야 함,
조인 조건에서 행에 동일한 값이 없는 칼럼 조인,
USING 조건절이나 ON 조건절 필수

7.LEFT OUTER JOIN ( = LEFT JOIN)

조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 읽은 후,

나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다.
우측 값에서 같은 값이 없는 경우 NULL 값으로 채운다.
좌측 테이블 데이터 조회 후 우측 테이블 조인 대상 데이터 조회

오라클 SQL>> SELECT 칼럼s FROM 테이블1 A, 테이블2 B A.칼럼 = B.칼럼(+);

SQL ServerSQL>> SELECT 칼럼s FROM 테이블1 A LEFT OUTER JOIN 테이블2 B ON (A.칼럼 = B.칼럼);

8. RIGHT OUTER JOIN ( = RIGHT JOIN)

LEFT OUTER JOIN의 반대

9. FULL OUTER JOIN

조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다,
중복 데이터는 삭제한다,
LEFT와 RIGHT OUTER JOIN 포함

2절 집합 연산자

SQL에서의 연산 : 집합연산 VS 관계연산

🔍 집합 연산자

두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회할 때 사용하는 연산자,
SELECT 절의 칼럼 수가 동일하고

SELECT 절의 동일 위치에 존재하는

칼럼의 데이터 타입이 상호 호환할 때 사용 가능.

1. UNION : 합집합

중복 행은 1개로 처리
칼럼 수와 데이터 타입이 모두 동일한 테이블 간 연산만 가능
SQL>> SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 UNION SELECT 테이블명 WHERE 조건절;

2. UNION ALL : 합집합
중복된 행도 전부 출력
정렬 안함 (↔ UNION은 정렬을 유발함)
SQL>> SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 UNION ALL SELECT 테이블명 WHERE 조건절;

3. INTERSECTION : 교집합

연산 INTERSECT 사용
SQL>> SELECT 칼럼명 FROM 테이블명 A WHERE 조건절MS INTERSECT SELECT 테이블명 WHERE 조건절;

4. DIFFERENCE : 차집합

연산 MINUS(Oracle), EXCEP 사용
SQL>> SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 MINUS SELECT 테이블명 WHERE 조건절;

5. PRODUCT : 곱집합

연산 CROSS JOIN 사용

🔍 순수 관계 연산자

관계형 DB를 새롭게 구현

1. SELECT => WHERE
2. PROJECT => SELECT
3. NATRUAL JOIN => 다양한 JOIN
4. DIVIDE => 사용x
{a,x}{a,y}{a,z} divdie {x,z} = {a}

🔍 FROM 절 JOIN 형태
1. INNER JOIN
2. NATURAL JOIN
3. USING 조건절
4. ON 조건절
5. CROSS JOIN
6. OUTER JOIN

3절 계층형 질의와 셀프 조인 ⭐

계층형 질의

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용,
Oracle에서 지원함

계층형 데이터
엔터티를 순환관계 데이터 모델로 설계할 때 발생함,
동일 테이블에 계층적으로 상위와 하위데이터가 포함된 데이터를 의미

⭐ CONNECT BY ⭐

트리 형태의 구조로 쿼리 수행 => 루트 노드부터 하위 노드의 쿼리를 실행함

상사 이름과 사람 이름을 조인하여 상사 밑에 넣기

- START WITH : 시작 조건 지정

- CONNECT BY PRIOR : 조인 조건 지정

▶ LEVEL : 검색 항목의 깊이, 최상위 계층의 레벨은 1
▶ CONNECT_BY_ISLEAF : 최하위 계층 값 표시

▶ SYS_CONNECT_BY_PATH : 계층 구조의 전개 경로 표시
- CONNECT BY : 절의 루프 알고리즘 키워드 ( 다음에 전개될 자식 데이터 지정)

▶ NOCYCLE : 순환구조의 발생지점까지만 전개
▶ CONNECT_BY_ISCYCLE : 순환구조 발생지점 표시

부모 노드와 자식 노드가 같을 때 1 아니면 0 출력

⭐ LPAD ⭐

계층형 조회 결과를 명확히 하기 위해 사용

LEVEL 값을 이용하여 결과 데이터 정렬

- SATRT WITH : 계층 구조 전개의 시작 위치 지정

계층 구조 전개의 시작 위치를 지정
즉, 루트 데이터를 지정한다.

- CONNECT BY : 다음에 전개될 자식 데이터 지정
- PRIOR :
CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다.
PRIOR 자식 = 부모 형태를 사용
하면 계층구조에서 부모 데이터에서 자식 데이터(부모->자식) 방향으로 전개하는 순방향 전개를 한다.
반대는 역방향 전개
- NOCYCLE : 동일한 데이터가 전개되지 않음
- ORDER SIBLINGS BY : 형제 노드간 사이에서 정렬 수행
- WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)
- LEVEL : 루트 데이터이면 1, 그 하위 데이터면 2, 리프 데이터까지 1씩 증가
- CONNECT_BY_ISLEAF : 해당 데이터가 리프 데이터면1, 그렇지 않으면 0
- CONNECT_BY_ISCYCLE : 해당 데이터가 조상이면 1, 아니면 0
(CYCLE 옵션 사용했을 시만 사용가능)
- SYS_CONNECT_BY_PATH : 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다.
- CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자이다.

SQL Server 계층형 질의

CTE(Common Table Expression)로 재귀 호출

셀프 조인(Self Join)

동일한 테이블에서 발생하는 조인을 의미하며,

FROM절에 동일한 테이블명이 두 번 이상 나타난다

반드시 테이블 별칭(Alias)을 사용해야 함

4절 서브쿼리

서브쿼리

하나의 SQL문 안에 포함되어 있는 또 다른 SQL문
서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만, 메인쿼리는 서브쿼리의 컬럼을 사용할 수 없다.
질의 결과에 서브쿼리 컬럼을 표시해야 한다면 조인 방식으로 변환하거나 함수, 스칼라 서브쿼리 등을 사용해야 한다.

스칼라 서브쿼리 : 한 행, 한 칼럼만을 반환하는 서브쿼리

서브쿼리 사용시 주의사항
1. 서브쿼리를 괄호로 감싸서 사용한다.
2. 서브쿼리는 단일행(Single row) 또는 복수행 (Multiple row) 비교 연산자와 함께 사용 가능하다.
3. 서브쿼리에서 ORDER BY 사용 불가 => ORDER BY절은 메인쿼리 마지막 문장에 위치해야 함
4. SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용 가능

🔍 서브쿼리 종류

1. 동작 방식에 따른 분류

- 비연관 서브쿼리
메인쿼리 칼럼을 가지고 있지 않는 서브쿼리,

메인쿼리에 값을 제공하기 위한 목적으로 주로 사용함
▶ Access Subquery: 제공자 역할
▶ Filter Subquery: 확인자 역할
▶ Early Filter Subquery: 데이터 필터링 역할

- 연관 서브쿼리(Associative Subquery)
메인쿼리의 결과를 조건이 맞는지 확인하기 위한 목적으로 주로 사용함

2. 반환 데이터 형태에 따른 분류

- 단일 행 서브쿼리 (Single Row)
서브쿼리 실행 결과가 1건 이하인 서브쿼리,
단일 행 비교 연산자와 함께 사용 한다.

=, <, <=, >, >=, <>

- 다중 행 서브쿼리 (Multi Row)
서브쿼리 실행 결과가 여러 건인 서브쿼리,
다중 행 비교 연산자와 함께 사용한다.

IN : 서브쿼리의 결과 중 하나의 값이라도 동일하다는 조건
ANY : 서브쿼리의 결과 중 하나의 값이라도 만족한다는 조건
ALL : 서브쿼리의 모든 결과값을 만족한다는 조건
EXISTS : 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건,
‘WHERE EXISTS (SELECT ~)’ (항상 연관 서브쿼리로 사용)

- 다중 칼럼 서브쿼리 (Multi Column)
서브쿼리 실행 결과로 여러 칼럼 반환,
주로 메인쿼리의 조건과 비교하기 위해 사용
(비교하고자 하는 칼럼의 개수와 위치가 동일해야 함)

- 스칼라 서브쿼리
값 하나를 반환하는 서브쿼리

한 행, 한 칼럼만을 반환하는 서브쿼리

SELECT절에 사용하는 서브쿼리

인라인 뷰

FROM절에 사용하는 뷰는 인라인 뷰(Inline View)라고 함,

테이블 명이 올 수 있는 곳에 사용,

ORDER BY 사용 가능,

서즈쿼리의 결과가 마치 실행시 동적으러 생성된 테이블처럼 사용 가능,
임시적으로 생성되는 동적 뷰,
데이터베이스에 해당 정보 저장 X

뷰

테이블은 실제로 데이터를 가지고 있는 반면,

뷰는 실제 데이터를 가지고 있지 않다.

가상 테이블이라고도 함

뷰 장점
독립성: 테이블 구조 변경 자동 반영
편리성: 쿼리를 단순하게 작성할 수 있음, 자주 사용하는 SQL문의 형태를 뷰로 생성하여 사용할 수 있음
보안성: 뷰를 생성할 때 칼럼을 제외할 수 있음

SQL>> CREATE VIEW V_PLAYER_TEAM AS
DROP VIEW V_PLAYER_TEAM;

- WITH
서브쿼리를 이용하여 뷰로 사용할 수 있는 구문
SQL>> WITH 뷰명 AS (SELECT ~)

5절 그룹 함수

ANSI/ISO 표준 데이터 분석 함수: 집계 함수, 그룹 함수, 윈도우 함수

- 그룹 함수(Group Function) : ROLLUP, CUBE, GROUPING SETS

합계 계산 함수

NULL을 빼고 집계함 (~ 집계 함수)

결과값 없는 행은 출력 안함

ROLLUP

GROUP BY로 묶인 칼럼의 소계 계산,

계층 구조로 GROUP BY의 칼럼 순서가 바뀌면 결과 값 바뀜,

Subtotal을 생성하기 위해 사용,
Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성 됨,
인수 순서에 주의

CUBE

조합 가능한 모든 값에 대해 다차원 집계 생성,
ROLLUP에 비해 시스템에 부하 심함

GROUPING SETS

특정 항목에 대한 소계 계산,

인수들에 대한 개별 집계를 구할 수 있다,

다양한 소계 집합 생성 가능,

GROUP BY의 칼럼 순서와 무관하게 개별적으로 처리함

표현식	출력값
GROUP BY ROLLUP (E1,E2)	E1과 E2별 소계 / E1별 소계 / 총합계
GROUP BY CUBE (E1,E2)	E1과 E2별 소계 / E1별 소계 / E2별 소계 / 총합계
GROUP BY GROUPING SETS (E1,E2)	E1별 소계 / E2별 소계

※ ‘GROUP BY CUBE (E1,E2)’와 ‘GROUP BY GROUPING SETS (E1,E2,(E1,E2),())’는 동일한 결과 출력

- GROUPING

그룹 함수에서 생성되는 합계를 구분해주는 함수,

소계나 합계가 계산되면 1 아니면 0 반환,

Subtotal의 total을 생성

6절 윈도우 함수

순위 함수

- RANK : 중복 순위 포함
- DENSE_RANK : 중복 순위 무시 (중간 순위를 비우지 않음)
- ROW_NUMBER : 단순히 행 번호 표시, 값에 무관하게 고유한 순위 부여

일반집계 함수

SUM, MAX, MIN, AVG, COUNT

행 순서 함수

- FIRST_VALUE, LAST_VALUE : 첫 값, 끝 값
- LAG, LEAD : 이전 행, 이후 행 (Oracle) 랙릿
※ ‘LEAD(E,A)’는 E에서 A번째 행의 값을 호출하는 형태로도 쓰임 (A의 기본값은 1)

비율 관련 함수

- PERCENT_RANK() : 백분율 순서
- CUME_DIST() : 현재 행 이하 값을 포함한 누적 백분율
- NTILE(A) : 전체 데이터 A등분
- RATIO_TO_REPORT : 총합계에 대한 값의 백분율

윈도우 함수

행과 행간의 관계를 정의하거나 행과 행간을 비교&연산하는 함수

여러 행 간의 관계 정의 함수

중첩 불가

SQL>> SELECT 윈도우함수(A) OVER (PARTITION BY 칼럼 ORDER BY 칼럼 윈도잉절) FROM 테이블명;

- PARTITION BY : 그룹핑 기준

- ORDER BY : 순위 지정 기준

- 윈도잉절 : 함수의 대상이 되는 행 범위 지정

1. BETWEEN A AND B : 구간 지정
▶ N PRECEDING, N FOLLOWING : N번째 앞 행, N번째 뒤 행
▶ UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING : 첫 행, 끝 행
▶ CURRENT ROW : 현재 행

2. ROWS, RANGE : 행 지정, 값의 범위 지정

RANK 함수
특정 항목에 대한 순위를 구하는 함수,
동일한 값에 대해서는 동일한 순위를 부여(1,2,2,4)

DENSE_RANK 함수
동일한 순위를 하나의 등수로 간주(1,2,2,3)

ROW_NUMBER 함수
동일한 값이라도 고유한 순위 부여

SUM
파티션별 윈도우의 합 구할 수 있다.
ex)같은 매니저를 두고 있는 사원들의 월급 합

MAX,MIN
파티션별 윈도우의 최대,최소 값을 구할 수 있다.
ex)같은 매니저를 두고 있는 사원들 중 최대 값

AVG
원하는 조건에 맞는 데이터에 대한 통계 값
ex)같은 매니저 내에서 앞의 사번과 뒤의 사번의 평균

COUNT
조건에 맞는 데이터에 대한 통계 값
ex)본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수

FIRST_VALUE
파티션별 윈도우에서 가장 먼저 나온 값을 구한다.(SQL Server는 지원x)

LAST_VALUE
파티션별 윈도우에서 가장 나중에 나온 값을 구한다.(SQL Server 지원x)

LAG
파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.(SQL Server 지원x)

LEAD
파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.(SQL Server 지원x)

RATIO_TO_REPORT
파티션 내 전체 SUM값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다.
결과 값은 0보다 크고 1보다 작거나 같다.

PERCENT_RANK
파티션별 윈도우에서 제일 먼저 나오는 것을 0,
제일 늦게 나오는 것을 1로 하여 행의 순서별 백분율을 구한다.
0>=,<=1

CUME_DIST
현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다.
>0, <=1

NTILE
파티션별 전체 건수를 인수 값으로 N등분한 결과를 구할 수 있다.

7절 DCL

DCL

유저를 생성하거나 권한을 제어하는 명령어, 보안을 위해 필요함
유저 생성하고 권한을 제어할 수 있는 명령어

Oracle과 SQL Server의 사용자 아키텍처 차이
- Oracle
유저를 통해 DB에 접속을 하는 형태,

ID와 PW 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 됨

- SQL Server
인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며,
인스턴스 내에 존재하는 다수의 DB에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑해 주어야 한다.
Windows 인증 방식과 혼합 모드 방식이 존재함

시스템 권한 : 사용자가 SQL 문을 실행하기 위해 필요한 적절한 권한

모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면

해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 한다.

GRANT : 권한 부여
SQL>> GRANT 권한 ON 오브젝트 TO 유저명;

REVOKE : 권한 제거
SQL>> REVOKE 권한 ON 오브젝트 TO 유저명;

GRANT CREATE USER TO SCOTT;
CONN SCOTT/TIGER(ID/PW)
CREATE USER PJS IDENTIFIED BY KOREA7;
GRANT CREATE SESSION TO PJS;
GRANT CREATE TABLE TO PJS;
REVOKE CREATE TABLE FROM PJS;

권한(Privileges)

SELECT, INSERT, UPDATE, DELETE, ALTER, ALL => DML 관련 권한

REFERENCES : 지정된 테이블을 참조하는 제약조건을 생성하는 권한
INDEX : 지정된 테이블에서 인덱스를 생성하는 권한

Oracle의 유저

SCOTT: 테스트용 샘플 유저
SYS: DBA 권한이 부여된 최상위 유저
SYSTEM: DB의 모든 시스템 권한이 부여된 DBA

ROLE

권한의 집합

권한을 일일이 부여하지 않고 ROLE로 편리하게 여러 권한을 부여할 수 있음

유저에게 알맞은 권한들을 한 번에 부여하기 위해 사용하는 것

CREATE ROLE LOGIN_TABLE;
GRANT CREATE TABLE TO LOGIN_TABLE;

Oracle의 ROLE

ROLE	권한
CONNECT	CREATE SESSION
RESOURCE	CREATE CLUSTER CREATE PROCEDURE CREATE TYPE CREATE SEQUENCE	CREATE TRIGGER CREATE OPERATOR CREATE TABLE CREATE INDEXTYPE

CASCADE : 하위 오브젝트까지 삭제

DROP USER PJS CASCADE;

8절 절차형 SQL ⭐

절차형 SQL

SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는저장 모듈을 생성할 수 있다.

Procedure, User Defined Function, Trigger 등이 있음

저장 모듈? PL/SQL 문장을 DB 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록
만든 일종의 SQL 컴포넌트 프로그램, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는
완전한 실행 프로그램

1. PL/SQL (Oracle)

블록 구조: 블록 내에 1) DML 2) 쿼리 3) IF나 LOOP 등을 사용할 수 있음

▶  Declare(선언부): 블록에서 사용할 변수나 인수에 대한 정의
▶  Begin(실행부): 처리할 SQL문 정의
▶  Exception(예외 처리부): 블록에서 발생한 에러 처리 로직 정의, 유일한 선택 항목

1. Block 구조로 되어있어 각 기능별로 모듈화 가능
2. 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환
3. IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
4. DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
5. PL/SQL은 Oracle에 내장되어 있으므로 호환성 굳
6. 응용 프로그램의 성능을 향상시킨다.
7. Block 단위로 처리 -> 통신량을 줄일 수 있다.

- DECLARE : BEGIN~END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입 선언부

- BEGIN~END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문,

제어문을 이용 필요한 로직 처리

- EXCEPTION : BEGIN~END 절에서 실행되는 SQL문이 실행될 때

에러가 발생하면 그 에러를 어떻게 처리할지 정의하는 예외 처리부

2. T-SQL (SQL Server)

근본적으로 SQL Server를 제어하는 언어
CREATE Procedure schema_NAME.Procedure_name

프로시저(Procedure)

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

절차형 SQL을 로직과 함께 DB 내에 저장해 놓은 명령문 집합,
RETURN을 통해 반드시 하나의 값 반환 (↔ 프로시저)

트리거(Trigger)

DML문이 수행되었을 때 자동으로 동작하는 프로그램 (↔ 프로시저는 EXECUTE로 실행함),
DCL와 TCL 실행 불가 (↔ 프로시저는 사용 가능함)
특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때,

DB에서 자동으로 동작하도록 작성된 프로그램,

사용자 호출이 아닌 DB 자동 수행

CREATE Trigger Trigger_name

프로시저와 트리거의 차이점
프로시저는 BEGIN~END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용가능,

DB트리거는 BEGIN~END 절 내에 사용 불가

저작자표시