[2과목] - SQL 활용

순수 관계 연산자와 SQL 문장 비교

순수 관계 연산자에는 SELECT, PROJECT, JOIN, DIVIDE가 있다.

• SELECT 연산은 WHERE 절로 구현
• PROJECT 연산은 SELECT 절로 구현
• (NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현
• DIVIDE 연산은 현재 사용되지 않음

---

ANSI / ISO SQL에서 표시하는 FROM 절의 JOIN 형태

• INNER JOIN
• NATURAL JOIN
• USING 조건절
• ON 조건절
• CROSS JOIN - M*N 건의 데이터 조합이 발생한다.
• OUTER JOIN(LEFT, RIGHT, FULL)

+) ORACLE에서는 OUTER JOIN 구문을 (+) 기호를 사용하여 처리할 수도 있다.

 

---

 

집합 연산자의 종류

집합 연산자	연산자의 의미
UNION	여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만든다.
UNION ALL	여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시된다. 즉, 단순히 결과만 합쳐놓은 것이다. 일반적으로 여러 질의 결과가 상호 배타적인(Exclusive)일 때 많이 사용한다. 개별 SQL문의 결과가 서로 중복되지 않는 경우, UNION과 결과가 동일하다. (결과의 정렬 순서에는 차이가 있을 수 있음)
INTERSECT	여러 개의 SQL문의 결과에 대한 교집합이다. 중복된 행은 하나의 행으로 만든다.
EXCEPT	앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합이다. 중복된 행은 하나의 행으로 만든다. (일부 데이터베이스는 MINUS를 사용함)

+) EXCEPT는 차집합에 대한 연산이므로 NOT IN 또는 NOT EXISTS로 대체하여 처리가 가능

 

일반 집합 연산자를 SQL과 비교

• UNION 연산은 UNION 기능으로,
• INTERSECTION 연산은 INTERSECT 기능으로,
• DIFFERENCE 연산은 EXCEPT(Oracle은 MINUS) 기능으로,
• PRODUCT 연산은 CROSS JOIN 기능으로 구현

 

---

 

PRIOR

• CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정한다.
• PRIOR 자식 = 부모 형태를 사용하면 계층구조의 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개하는 순방향 전개를 한다.
• 그리고 PRIOR 부모 = 자식 형태를 사용하면 반대로 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 역방향 전개를 한다.

+) 오라클 계층형 질의문에서 PRIOR 키워드는 SELECT, WHERE 절에서도 사용할 수 있다.

+) CONNECT BY 절에 작성된 조건절은 WHERE 절에 작성된 조건절과 다르다. START WITH 절에서 필터링된 시작 데이터는 결과목록에 포함되어지며, 이후 CONNECT BY 절에 의해 필터링 된다.

 

---

 

Oracle 계층형 질의

• START WITH절은 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트 데이터를 지정한다. (액세스)
• ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.
• Oracle 계층형 질의에서 루트 노드의 LEVEL 값은 1이다.
• 순방향전개(부모 노드 → 자식 노드) / 역방향전개(자식 노드 → 부모 노드)

 

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용한다.

계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.

예를 들어, 사원 테이블에서는 사원들 사이에 상위 사원(관리자)과 하위 사원 관계가 존재하고 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재한다.

 

---

 

셀프 조인(Self Join)

동일 테이블 사이의 조인을 말한다. 따라서 FROM 절에 동일 테이블이 두 번 이상 나타난다. 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias)를 사용해야 한다.

 

+) SELF JOIN은 하나의 테이블에서 두 개의 칼럼이 연관 관계를 가지고 있는 경우에 사용한다.

셀프 조인(Self Join) 문장

SELECT

  ALIAS명1.칼럼명,

  ALIAS명2.칼럼명, ...

FROM

 테이블 ALIAS명1,

 테이블 ALIAS명2

WHERE

 ALIAS명1.칼럼명2 = ALIAS명2.칼럼명1;

 

---

 

반환되는 데이터의 형태에 따른 서브쿼리 분류

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

 

+) 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multi Row) 비교 연산자와 함께 사용할 수 있다.

+) 서브쿼리는 SELECT 절, FROM 절, HAVING 절, ORDER BY 절 등에서 사용이 가능하다.

• SELECT 절에 사용된 서브쿼리는 단일행 연관 서브쿼리로 JOIN 으로도 변경이 가능
• FROM 절에 사용된 서브쿼리는 Inline View 또는 Dynamic View
• WHERE 절에 사용된 서브쿼리는 다중행 연관 서브쿼리 이다.

+) 서브쿼리의 결과가 복수 행 결과를 반환하는 경우에는 IN, ALL, ANY 등의 복수 행 비교 연산자와 사용하여야 한다.

+) 다중 컬럼 서브쿼리는 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인 쿼리의 조건과 비교되는데, SQL Server에서는 현재 지원하지 않는 기능이다.

+) 연관(Correlated) 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 포함하고 있는 형태의 서브쿼리이다.

+) 단일 행 서브쿼리의 비교연산자는 다중 행 서브쿼리의 비교연산자로 사용할 수 없지만, 반대의 경우는 가능하다.

+) FROM 절에서 사용되는 서브쿼리를 인라인 뷰(Inline View)라고 한다. 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있다. 인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다. 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있다. 인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다.

 

서브쿼리를 사용시 주의사항

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

 

---

 

뷰 사용의 장점

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

+) 뷰는 단지 정의만을 가지고 있으며, 실행 시점에 질의를 재작성하여 수행한다.

+) 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있다.

 

---

ROLLUP은 계층 구조를 가진 SUB TOTAL을 생성하는 함수로 나열된 컬럼의 순서가 변경되면 수행 결과도 변경된다.
CUBE함수는 인수로 나열된 항목의 가능한 모든 조합에 대하여 GROUPING을 수행한다. 따라서 사용하게 되면 결합 가능한 모든 값에 대하여 다차원 집계를 생성한다.

GROUPING SETS은 사용자가 원하는 다양한 조합을 인수로 사용할 수 있다.

 

+) Grouping Columns이 가질 수 있는 모든 경우에 대하여 Subtotal을 생성해야 하는 경우에는 CUBE를 사용하는 것이 바람직하나, ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 한다.

+) ROLLUP 함수는 함수의 인자로 주어진 컬럼의 순서에 따라 다른 결과를 추출하지만 CUBE와 GROUPING SETS는 순서와 상관없이 결과가 동일하다.

+) CUBE, GROUPING SETS, ROLLUP 세가지 그룹 함수 모두 일반 그룹 함수로 동일한 결과를 추출할 수 있다.

+) CUBE, GROUPING SETS, ROLLUP 함수들에 의해 집계된 레코드에서 집계 대상 컬럼 이외의 GROUP 대상 컬럼의 값은 NULL을 반환한다.

---

 

윈도우 함수(Window Function, Analytic Function)

윈도우 함수는 결과에 대한 함수처리이기 때문에 결과 건수는 줄지 않는다.

Partition과 Group By 구문은 의미적으로 유사하다.

Partition 구문이 없으면 전체 집합을 하나의 Partition으로 정의한 것과 동일하다

윈도우 함수 적용 범위는 Partition을 넘을 수 없다.

 

---

RANK 함수

RANK 함수는 ORDER BY를 포함한 QUERY문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이며 동일한 값에 대해서는 동일한 순위를 부여하게 된다.

 

순위를 구하는 함수로는 RANK, DENSE_RANK, ROW_NUMBER 함수가 있다. RANK WINDOW FUNCTION은 동일 값에 대해서는 동일 순위를 부여하고 중간 순위는 비워 두지만, DENSE_RANK 함수는 동일 순위를 부여하되 중간 순위를 비우지 않는다. ROW_NUMBER 함수는 동일 값에 대해서도 유일한 순위를 부여한다.

 

---

LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.

이후 몇 번째 행의 값을 가져오는 것은 LEAD 함수이며, SQL Server에서는 지원하지 않는 함수이다.

• LAG(expr [,offset] [,default]) OVER([partition_by_clause] order_by_clause)
• LEAD(expr [,offset] [,default]) OVER([partition_by_clause] order_by_clause)

 

---

 

ROLE

ROLE은 많은 DBMS 사용자에게 개별적으로 많은 권한을 부여하는 번거로움과 어려움을 해소하기 위해 다양한 권한을 하나의 그룹으로 묶어놓은 논리적인 권한의 그룹이다.

 

+) DBMS 사용자를 생성하면 기본적으로 많은 권한을 부여해야 한다. 많은 DBMS에서는 DBMS 관리자가 사용자별로 권한을 관리해야 하는 부담과 복잡함을 줄이기 위하여 다양한 권한을 그룹으로 묶어 관리할 수 있도록 사용자와 권한 사이에서 중개 역할을 수행하는 ROLE을 제공한다.

 

---

 

PL / SQL의 특징

• PL/SQL은 Block 구조로 되어있어 각 기능별로 모듈화가 가능하다.
• 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.
• IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
• PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
• PL/SQL은 응용 프로그램의 성능을 향상시킨다.
• PL/SQL은 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

+) PL/SQL로 작성된 Procedure, User Defined Function은 작성자의 기준으로 트랜잭션을 분할할 수 있으며, 또한 프로시저 내에서 다른 프로시저를 호출할 경우에 호출 프로시저의 트랜잭션과는 별도로 PRAGMA AUTONOMOUS_TRANSACTION을 선언하여 자율 트랜잭션 처리를 할 수 있다.

+) 변수와 상수 등을 사용하여 일반 SQL 문장을 실행할 때 WHERE절의 조건 등으로 대입할 수 있다.

+) Procedure, User Defined Function, Trigger 객체를 PL/SQL로 작성할 수 있다.

+) Procedure 내부에 작성된 절차적 코드는 PL/SQL엔진이 처리하고 일반적인 SQL 문장은 SQL실행기가 처리한다.

 

+) PL/SQL 에서는 동적 SQL 또는 DDL 문장을 실행할 때 EXECUTE IMMEDIATE를 사용하여야 한다.

 

---

 

저장 모듈(Stored Module)

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

Oracle의 저장 모듈에는 Procedure, User Defined Function, Trigger가 있다.

 

+) 저장형 프로시져는 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미한다.

+) 저장형 함수(사용자 정의 함수)는 단독적으로 실행되기 보다는 다른 SQL문을 통하여 호출하고 그 결과를 리턴하는 SQL의 보조적인 역할을 한다.

+) 트리거는 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때 데이터베이스에서 자동으로 동작하도록 작성된 프로그램이다.

 

cf) 데이터의 무결성과 일관성을 위해서 사용자 정의 함수를 사용하는 것은 트리거의 용도이다.

 

---

 

트리거(Trigger)

Trigger는 Procedure와 달리 Commit 및 Rollback 과 같은 TCL을 사용할 수 없다.

Trigger는 데이터베이스에 의해서 자동으로 호출되고 수행한다.

Trigger는 특정 테이블에 대해서 INSERT, UPDATE, DELETE문이 수행되었을 때 호출되도록 정의할 수 있다.

Trigger는 데이터베이스에 로그인하는 작업에도 정의할 수 있다.

 

---

 

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

프로시저	트리거
CREATE Procedure 문법사용	CREATE Trigger 문법사용
EXECUTE 명령어로 실행	생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능	COMMIT, ROLLBACK 실행 안됨