4. SQL 활용

일반 집합 연산자와 SQL 의 비교

일반 집합 연산자	SQL 문	설명
UNION	UNION	UNION 연산은 수학적 합집합을 제공하기 위해, 공통 교집합의 중복을 없애기 위한 사전 작업으로 시스템에 부하를 주는 정렬 작업이 발생한다
INTERSECTION	INTERSECT	INTERSECTION 은 수학의 교집합으로써 두 집합의 공통 집합을 추출한다
DIFFERENCE	EXCEPT(ORACLE MINUS)	DIFFERENCE 는 수학의 차집합으로써 첫 번째 집합에서 두 번째 집합과의 공통 집합을 제외한 부분이다
PRODUCT	CROSS JOIN	PRODUCT 의 경우는 CROSS(ANIS/ISO 표준) PRODUCT 라고 불리는 곱집합으로 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다

순수 관계 연산자와 SQL 비교

일반 집합 연산자	SQL 문	설명
SELECT 연산	WHERE 절로 구현	SELECT 연산은 SQL 문장에서는 WHERE 절 기능으로 구현이 되었다.
PROJECT 연산	SELECT 절로 구현	PROJECT 연산은 SQL 문장에서는 SELECT 절의 칼럼 선택 기능으로 구현되었다
(NATURAL) JOIN	다양한 JOIN 기능으로 구현	JOIN 연산은 WHERE 절의 INNER JOIN 조건과 함께 FROM 절의 NATURAL JOIN, INNER JOIN, OUTER JOIN, USING 조건절, ON 조건절 등으로 가장 다양하게 발전하였다.
DIVIDE	사용되지 않음

조인의 형태

일반 집합 연산자	설명
INNER JOIN	INNER JOIN 은 OUTER(외부) JOIN 과 대비하여 내부 JOIN 이라고 하며 JOIN 조건에서 동일한 값이 있는 행만 반환
NATURAL JOIN	NATURAL JOIN 은 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN 을 수행
USING 조건절	NATURAL JOIN 에서는 모든 일치되는 칼럼들에 대해 JOIN이 이루어지지만, FROM 절의USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN 을 할 수가 있음
ON 조건절	JOIN 서술부(ON 조건절)와 비 JOIN 서술부(WHERE 조건 절)를 분리하여 이해가 쉬우며, 칼럼 명이 다르더라도 JOIN 조건을 사용할 수 있는 장점이 있음
CROSS JOIN	CROSS JOIN 은 E.F.CODD 박사가 언급한 일반 집합 연산자의 PRODUCT 의 개념으로 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말함
OUTER JOIN	INNER(내부) JOIN 과 대비하여 OUTER(외부) JOIN 이라고 불리며 JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용할 수 있음

OUTER JOIN

• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

집합연산자

집합 연산자의 종류

종류	설명
UNION	여러 개의 SQL 문의 결과에 대한 합집합 중복된 행은 한개의 행으로 출력됨
UNION ALL	여러 개의 SQL 문의 결과에 대한 합집합 중복된 행도 그대로 결과로 표시한다
INTERSECT	여러 개의 SQL 문의 대한 교집합 중복된 행은 하나로 표시한다
EXCEPT	위의 SQL 문의 집합에서 아래의 SQL 문의 집합을 뺀 결과를 표시한다

계층 형 질의와 SELF 조인

• 테이블에 계층 형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층 형
  질의(Hierarchical Query)를 사용
• 계층 형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된
  데이터를 말한다.

오라클의 계층형 SQL

구분	설명
select	조회하고자하는 컬럼을 지정한다
from table	대상 테이블을 지정한다
where	모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다(필터링)
start with 조건	계층 구조 전개의 시작 위치를 지정하는 구문이다 즉, 룰트 데이터를 지정한다
connect by [nocycle] [prior] a and b	connect by 절은 다음에 전개될 자식 데이터를 지정하는 구문이다 prior 자식 = 부모 형태를 사용하면 계층 구조에서 자식 데이터에서 부모 데이터(자식 -> 부모) 방향으로 전개하는 순방향 전개를 한다 prior 부모 = 자식 형태를 사용하면 반대로 부모 데이터에서 자식 데이터(부모 -> 작식) 방향으로 전개하는 역방향 전개를 한다 nocycle을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다
order siblings by 컬럼	형제노드(동일 level) 사이에서 정렬을 수행한다

서브쿼리

서브쿼리란

• 서브 쿼리(Subquery)란 하나의 SQL 문안에 포함되어 있는 또 다른 SQL 문을 말한다.
• 조인은 조인에 참여하는 모든 테이블이 대등한 관계에 있기 때문에 조인에 참여하는 모든 테이블의 칼럼을 어느 위치에서라도 자유롭게 사용할 수 있다. 그러나 서브 쿼리는 메인 쿼리의 칼럼을 모두 사용할 수 있지만 메인 쿼리는 서브 쿼리의 칼럼을 사용할 수 없다.

서브 쿼리 사용시 주의점

• 서브쿼리를 괄호로 감싸서 사용한다.
• 서브 쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용
  가능하다.
• 단일 행 비교 연산자는 서브 쿼리의 결과가 반드시 1 건 이하이어야 하고 복수 행 비교
  연산자는 서브 쿼리의 결과 건수와 상관 없다.
• 서브쿼리에서는 ORDER BY 를 사용하지 못한다. ORDER BY 절은 SELECT 절에서 오직 한
  개만 올 수 있기 때문에 ORDER BY 절은 메인 쿼리의 마지막 문장에 위치해야 한다.

서브 쿼리가 사용 가능한 위치

• SELECT 절 - FROM 절 - WHERE 절 - HAVING 절 - ORDER BY 절
• INSERT 문의 VALUES 절 - UPDATE 문의 SET 절

동작 방식에 따른 서브쿼리 분류

• 비 연관 서브쿼리
  ◼ 서브 쿼리가 메인 쿼리의 칼럼을 가지고 있지 않은 형태의 서브 쿼리임
  ◼ 메인 쿼리에 값을 제공하기 위한 목적으로 주로 사용
• 연관 서브쿼리
  ◼ 서브 쿼리가 메인 쿼리의 값을 가지고 있는 형태의 서브쿼리이다.
  ◼ 일반적으로 메인 쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이
  맞는지 확인하고자 할 때 주로 사용한다.

반환 형태에 따른 서브 쿼리

• 단일 행 서브 쿼리
  ◼ 서브 쿼리의 실행 결과가 항상 1 건 이하인 서브쿼리를 의미한다.
  ◼ 항상 비교 연산자와 함께 사용된다.
  ◼ (=, <, <=, >, >=, <>)
• 다중 행 서브 쿼리
  ◼ 서브 쿼리의 실행 결과가 여러 건인 서브쿼리를 의미한다.
  ◼ 다중 행 서브 쿼리는 다중 행 비교 연산자와 함께 사용된다.
  ◼ (IN, ALL, ANY, SOME, EXISTS)
• 다중 칼럼 서브 쿼리
  ◼ 서브 쿼리의 실행 결과로 여러 칼럼을 반환한다.
  ◼ 메인 쿼리의 조건 절에 여러 칼럼을 동시에 비교 할 수 있다.
  ◼ 서브 쿼리와 메인 쿼리의 칼럼 수와 칼럼 순서가 동일해야 한다

• EXIST 문 서브쿼리
• 스칼라 서브쿼리
• 인라인 뷰 서브쿼리
• HAVING 절에서 서브 쿼리
• UPDATE 문에 사용되는 서브 쿼리
• INSERT 문에 사용되는 서브 쿼리

뷰 사용의 장점

장점	설명
독립성	테이블 구조가 변경되어도 뷰를 사용하는 응용프로그램은 변경하지 않아도 된다
편리성	복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다. 또한 해당 형태의 SQL 문을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있다
보안성	직원의 급여 정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성 할 때 해당 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다

그룹함수

그룹 함수란

• 그룹 함수를 이용하여 특정 집합의 소계, 중계, 합계, 총 합계를 구할 수 있다
• 즉 이러한 합계를 계산하기 위해서 기존에 들어갔던 다양한 노력들이 그룹 함수를
  이용하여 간단하게 처리 할 수 있게 되었다

그룹 함수의 종류

• ROLLUP
  ◼ 소 그룹간의 소계를 계산하는 기능
  ◼ ROLLUP 함수 내에 인자로 지정된 GRUPING 칼럼은 SUBTOTAL 을 생성하는데
  사용된다.
  ◼ GROUPING 칼럼의 수가 N 이라고 했을 때 N+1 의 SUBTOTAL 이 생성된다.
  ◼ ROLLUP 함수 내의 인자의 순서가 바뀌면 결과도 바뀌게 된다.(ROLLUP 은
  계층 구조임)
• CUBE
  ◼ 다차원적인 소계를 계산하는 기능
  ◼ 결합 가능한 모든 값에 대하여 다차원 집계를 생성
  ◼ CUBE 함수 내에 칼럼이 N 개라면 2 의 N 승만큼의 SUBTOTAL 이 생성됨
  ◼ 시스템에 많은 부담을 주기때문에 사용상 주의가 필요함
• GROUPING SETS
  ◼ 특정 항목에 대한 소계를 계산하는 기능

윈도우 함수

윈도우 함수 개요

• 행과 행간의 관계에서 다양한 연산 처리를 할 수 있는 함수가 윈도우 함수이다.
• 분석함수로도 알려져 있다. (ANSI 표준은 윈도우 함수이다.)
• 윈도우함수는 일반 함수와 달리 중첩하여 호출 될 수는 없다.

윈도우 함수의 종류

• 순위 관련함수
  ◼ RANK
  ◼ DENSE_RANK
  ◼ ROW_NUMBER
• 집계관련함수
  ◼ SUM
  ◼ MAX
  ◼ MIN
  ◼ AVG
  ◼ COUNT
• 행순서관련함수
  ◼ FIRST_VALUE
  ◼ LAST_VALUE
  ◼ LAG
  ◼ LEAD
• 그룹내 비율관련 함수
  ◼ CUME_DIST
  ◼ PERCENT_RANK
  ◼ NTILE
  ◼ RATIO_TO_REPORT

DCL

DCL 이란

• 유저를 생성하고 권한을 제어할 수 있는 명령어
• 데이터의 보호와 보안을 위해서 유저와 권한을 관리 해야함

> 오라클에서 제공하는 유저들

• SCOTT
  ◼ 테스트용 샘플 유저
• SYS
  ◼ 테스트용 샘플 유저
• SYSTEM
  ◼ SYSTEM 데이터베이스의 모든 시스템 권한을 부여 받은 유저(SYS 바로 밑)

> 유저 생성과 시스템 권한 부여

• 유저 생성후, 접속을 위한 권한 부여 필요,
• 모든 DDL 문장 수행을 위한 권한 부여 필요
• 개별로 부여 하기 보다는 ROLE 을 통한 권한 부여

> OBJECT 에 대한 권한 부여

• 오브젝트 권한과 오브젝트와의 관계
• 개별 오브젝트에 대한 작업을 위해서는 오브젝트 권한 부여 필요

> ROLE 을 이용한 권한 부여

• 유저를 생성하면 다양한 많은 권한들을 부여 해야함
• DBA 는 ROLE 을 생성하고 ROLE 에 각종 권한을 부여한 후 해당 ROLE 을 다른 유저에게
  부여
• ROLE 에 포함된 권한들이 필요한 유저에게 빠르게 권한을 부여할 수 있음

절차형 SQL

절차형 SQL 이란

• 일반적인 개발언어처럼 SQL 문도 절차지향적인 프로그램 작성이 가능하도록 절차 형 SQL 을 제공한다.
• 절차 형 SQL 을 사용하면 SQL 문의 연속적인 실행이나 조건에 따른 분기 처리를 수행하는 모듈을 생성할 수 있다.
• 오라클 기준 이러한 절차 형 모듈의 종류는 사용자정의함수, 프로시저, 트리거가 있다.
• 오라클 기준 이러한 절차 형 모듈을 PL/SQL 이라고 부른다.

PL / SQL 개요

• PL/SQL 은 Block 구조로 되어 있고 Block 내에는 SQL 문, IF, LOOP 등이 존재함
• PL/SQL 을 이용해서 다양한 모듈을 개발 가능
• Block 구조로 되어있으며 각 기능별로 모듈화가 가능
• 변수/상수 선언 및 IF/LOOP 문 등의 사용이 가능
• DBMS 에러나 사용자 에러 정의를 할 수 있음
• PL/SQL 은 오라클에 내장 시킬수 있으므로 어떠한 오라클 서버로도 이식이 가능
• PL/SQL 은 여러 SQL 문장을 Block 으로 묶고 한번에 Block 전부를 서버로 보내기때문에 네트워크 패킷 수를 감소 시킴

구조명	필수/선택	설명
declare(선언부)	필수	begin - end 에서 사용할 변수나 인수에 대한 정의 및 데이터 타입 선언
begin(실행부)	필수	개발자가 처리하고자 하는 sql문과 필요한 logic(비교문,제어문 등)이 정의도는 실행 부
exception(예외처리부)	선택	begin - end 에서 실행되는 sql 문에 발생된 에러를 처리하는 에러 처리 부
end	필수

사용자 정의 함수란

• 사용자 정의 함수는 프로시저처럼 SQL 문을 IF/LOOP 등의 LOGIC 와 함께 데이터베이스에
  저장해 놓은 명령문의 집합이다.
• SUM, AVG, NVL 의 함수처럼 호출해서 사용할 수 있다.
• 프로시저와 차이점은 반드시 한 건을 되돌려 줘야 한다는 것이다.

트리거란

• 특정한 테이블에 INSERT, UPDATE, DELETE 를 수행할 때 DBMS 내에서 자동으로
  동작하도록 작성된 프로그램이다.
• 즉 사용자가 직접 호출하는 것이 아니고 DBMS 가 자동적으로 수행한다.
• 트리거는 테이블과 뷰, DB 작업을 대상으로 정의 할수 있으며 전체 트랜잭션 작업에 대해
  발생되는 트리거와 각행에 대해 발생되는 트리거가 있다.

프로시저와 트리거의 차이점

프로시저	트리거
create procedure 문법사용	create trigger 문법사용
exectute/exec 명형어로 실행	생성 후 자동으로 실행
내부에서 commit,rollback 실행가능	내부에서 commit,rollback 실행불가