1. 절차형 SQL 개요

절차 지향적인 프로그램이 가능하도록 DBMS 벤더별로 PL(Procedural Language)/SQL(Oracle), SQL/PL(DB2), T-SQL(SQL Server) 등의 절차형 SQL을 제공

절차형 SQL을 이용하면 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성

 

2. PL/SQL 개요

 

.  특징

블럭구조로 되어있고, DML문장, QUERY문장, 절차형 언어(IF, LOOP)등이 사용하여 절차적 프로그래밍 기능을 하게 트랜잭션 언어

저장모듈 : PL/SQL 문장을 데이터 베이스 서버에 저장하여 사용자와 어플리케이션 사이에 공유할 있도록 만든 SQL 컴포넌트 프로그램

 독립적으로 또는 프로그램으로부터 실행 있는 완전한 실행 프로그램

 

- PL/SQL은 Block 구조로 되어있어 각 기능별로 모듈화가 가능하다.

- 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.

- IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.

- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.

- PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.

- PL/SQL은 응용 프로그램의 성능을 향상시킨다.

- PL/SQL은 여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

 


프로그램 문장은 PL/SQL 엔진이 처리하고

SQL 문장은 ORACLE 서버의 SQL STATEMENT EXECUTOR 실행하도록 작업 분리하여 처리

 

. 구조


  • DECLARE : BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부
  • BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부
  • EXCEPTION : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부

 

. 기본문법

CREATE [OR REPLACE] Procedure [Procedure_name]

( argument1 [mode] data_type1, argument2 [mode] date_type2, ... ... )

IS [AS] ... ...

BEGIN ... ...

EXCEPTION ... ...

END;

/

  • OR REPLCAE : 같은 이름의 프로시저가 있을 경우 덮어쓰기
  • ARGUMENT : 프로시저 안으로 어떤 값이 들어오거나 혹은 처리한 결과값을 리턴시킬 매개 변수 저장
  • MODE : 매개변수 3가지 유형 IN, OUT, INOUT
  • / : 마지막으로 데이터 베이스에게 프로시저 컴파일하라는 명령어

 

프로시저 삭제

DROP Procedure [Procedure_name];

 

3. T-SQL 개요

 

. 특징

SQL SERVER 제어하기 위한 언어, ANSI표준에 약간의 기능을 추가해 보완적으로 만든

- 변수 선언 기능 @@이라는 전역변수(시스템 함수)와 @이라는 지역변수가 있다.

- 지역변수는 사용자가 자신의 연결 시간 동안만 사용하기 위해 만들어지는 변수이며 전역변수는 이미 SQL서버에 내장된 값이다.

- 데이터 유형(Data Type)을 제공한다. 즉 int, float, varchar 등의 자료형을 의미한다.

- 연산자(Operator) 산술연산자( +, -, *, /)와 비교연산자(=, <, >, <>) 논리연산자(and, or, not) 사용이 가능하다.

- 흐름 제어 기능 IF-ELSE와 WHILE, CASE-THEN 사용이 가능하다.

- 주석 기능한줄 주석 : -- 뒤의 내용은 주석범위 주석 : /* 내용 */ 형태를 사용하며, 여러 줄도 가능함

 

. 구조


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

- BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부이

      T-SQL에서는 BEGIN, END 문을 반드시 사용해야하는 것은 아니지만 블록 단위로 처리하고자 할 때는 반드시 작성

- ERROR 처리 : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부

 

. 기본 문법

CREATE Procedure [schema_name.]Procedure_name

@parameter1 data_type1 [mode],

@parameter2 date_type2 [mode],

... ...

WITH 

AS ... ...

BEGIN ... ...

ERROR 처리 ... ...

END;

 

  • 변경이 필요한 경우는 ALTER 구분으로 변경
  • @PARAMETER 값이 들어오거나 프로시저에서 처리한 값을 리턴 시킬 매개 변수 지정할 사용
  • MODE 부분의 매개변수 4가지 유형

① VARYING : 결과 집합이 출력 매개 변수로 사용되도록 지정, CURSOR 매개변수에만 적용

② DEFAULT : 지정된 매개변수가 프로시저를 호출할 당시 지정되지 않을 경우 지정된 기본값으로 처리

  즉, 기본 값이 지정되어 있으면 해당 매개 변수를 지정하지 않아도 프로시저가 지정된 기본 값으로 정상적으로 수행

③ OUT, OUTPUT : 프로시저에서 처리된 결과 값을 EXECUTE 문 호출 시 반환

④ READONLY : 자주 사용되지는 않음 프로시저 본문 내에서 매개 변수를 업데이트하거나 수정할 수 없음

   매개 변수 유형이 사용자 정의 테이블 형식인 경우 READONLY를 지정

  • WITH 부분 3가지 옵션

① RECOMPILE : 데이터베이스 엔진에서 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일 

    엔진에서 저장 프로시저 안에 있는 개별 쿼리에 대한 계획을 삭제하려 할 때 RECOMPILE 쿼리 힌트를 사용

② ENCRYPTION : CREATE PROCEDURE 문의 원본 텍스트가 알아보기 어려운 형식으로 변환

 변조된 출력은 SQL Server의 카탈로그 뷰 어디에서도 직접 표시되지 않음

 원본을 볼 수 있는 방법이 없기 때문에 반드시 원본은 백업 필요

③ EXECUTE AS : 해당 저장 프로시저를 실행할 보안 컨텍스트를 지정

 

 

프로시저 삭제 명령

DROP Procedure [schema_name.]Procedure_name;

 

4. PROCEDURE 생성과 활용

 

주의해야할 문법적 요소

  1. 다양한 변수의 유형
  2. SQL SELECT 문장이 다름, 결과값이 반드시 있어야 하며 하나 있어야함 (T-SQL 결과값이 없어도 에러 미발생)
  3. 대입 연사자 ":="사용 ( 일반적으로 "=" 사용)
  4. 예외처리부에서 WHEN ~ THEN 사용하여 종류별 처리 가능

 

5. USER DEFINED FUNCTION 생성과 활용

절차형 SQL 로직과 함께 데이터베이스 내에 저장해 놓는 명령문의 집합

PROCEDURE 다른점은 RETURN 사용해서 하나의 값을 반드시 되돌려 주어야

SQL 문장에서 특정 작업을 수행하고 반드시 수행결과 값을 리턴해야함

 

6. TRIGGER 생성과 활용

특정한 테이블에 INSERT, UPDATE, DELETE 같은 DML문이 수행되었을 , 자동으로 동작하도록 작성된 프로그램

사용자가 직접호출하는 것이 아니라 자동적으로 수행하게

테이블과 , 데이터베이스 작업을 대상으로 정의할 있음

전체 트랜잭션작업에 대해 발생되는 TRIGGER, 행에 대해서 발생되는 TRIGGER 있음

데이터베이스 보안의 적용, 유효하지 않은 트랜잭션 예방, 업무규칙 자동적용제공 등에 사용가능하다.

 

7. PROCEDURE TRIGGER 차이

PROCEDURE

TRIGGER

CREATE PROCEDURE 문법 사용

CREATE TIGGER 문법사용

EXECUTE 명령어로 실행

생성 자동 실행

COMMIT, ROLLBACK 실행가능

COMMIT, ROLLBACK 실행불가


'자격증 공방 > SQLD' 카테고리의 다른 글

제 5-2절 인덱스 기본  (0) 2018.07.17
제 5-1절 옵티마이저와 실행계획  (0) 2018.07.17
제 4-7절 DCL  (0) 2018.07.11
제 4-6절 윈도우 함수(WINDOW 함수)  (0) 2018.07.11
제 4-5절 그룹함수  (0) 2018.07.11

 

1. DCL 개요

유저를 생성하고 권한을 제어할 있는 DATA CONTROL LANGUAGE

 

2. 유저와 권한

ORACLE에서 제공하는 유저

  • SCOTT   : 테스트용 샘플 유저
  • SYS       : DBA ROLE 부여받은 유저
  • SYSTEM : 모든 권한을 부여받은 DBA유저, ORACLE 설치시 패스워드 설정

 

ORACLE 유저를 통해 데이터베이스에 접속, 아이디와 비밀번호 방식으로 인스턴스에 접속하고 스키마에 오브젝트 생성 권한 부여

SQL SERVER 인스턴스를 접근하기 위해 로그인 생성, 인스턴스 존재하는 다수의 데이터베이스를 연결하기 위한 작업을 위해 유저 생성 맵핑

특정유저는 특정데이터베이스 내의 특정 스키마에 대한 권한을 부여 받을 있음

  1. WINDOW 인증하는 방식, 암호를 요청하지 않고 유효성 검사 수행하지 않음, 기본 인증 모드, 신뢰된 인증
  2. 혼합모드, WINDOW인증 또는 SQL 인증, 강력한 암호를 사용해아함,

 

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

유저를 생성하고 데이터 베이스에 접속하지만 오브젝트 생성할 없음, 적절한 권한이 필요

ROLE 통해 여러가지 유형의 유저 관리를 가능하게

 

Oracle) SYSTEM 권한을 주어야 한다.

    GRANT CREATE USER TO SCOTT; 권한이 부여되었다.

    CONN SCOTT/TIGER 연결되었다.

    CREATE USER PJS IDENTIFIED BY KOREA7; 사용자가 생성되었다.

 

SQL Server) SA 로그인 유저 생성

 CREATE LOGIN PJS WITH PASSWORD='KOREA7', DEFAULT_DATABASE=AdventureWorks 로그인 진행

 USE ADVENTUREWORKS;

 GO

 CREATE USER PJS FOR LOGIN PJS WITH DEFAULT_SCHEMA = dbo; 유저 생성

 

. OBJECT 대한 권한 부여

save image


. ROLE 이용한 이용 권한 부여

많은 데이터 베이스에서 유저들과 권한들 사이에 중개하는 역할

관리자는 ROLE 생성하고 각종 권한들을 부여한 롤을 다른 롤이나 유저에게 부여

롤에 포함되어 있는 권한들이 필요한 유저에게는 해당 롤만 부여함을써 빠르고 정확하게 필요한 권한 부여 가능

save image

롤은 CONNECTD(로그인 권한) RESOURCE(오브젝트 생성 권한)

save image

 

Oracle

CONN SYSTEM/MANAGER 연결되었다.

DROP USER JISUNG CASCADE; 사용자가 삭제되었다. ☞ JISUNG 유저가 만든 MENU 테이블도 같이 삭제되었다.

CREATE USER JISUNG IDENTIFIED BY KOREA7; 사용자가 생성되었다.

GRANT CONNECT, RESOURCE TO JISUNG; 권한이 부여되었다.

CONN JISUNG/KOREA7 연결되었다.

CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10)); 테이블이 생성되었다.

REVOKE 권한을 뺏었다.

 

save image

save image



'자격증 공방 > SQLD' 카테고리의 다른 글

제 5-1절 옵티마이저와 실행계획  (0) 2018.07.17
제 4-8장 절차형 SQL  (0) 2018.07.11
제 4-6절 윈도우 함수(WINDOW 함수)  (0) 2018.07.11
제 4-5절 그룹함수  (0) 2018.07.11
제 4-4절 서브쿼리  (0) 2018.07.11

1. WINDOW FUNTION

행과 행간의 관계를 쉽게 정의하기 위해 만든 함수

분석함수(ANALYTIC FUNCTION) 순위 함수(RANK FUNCTION)으로도 불림

중첩해서는 사용이 불가능하나 서브쿼리에서 사용 가능

 

. WINDOE FUNTION 종류

  1. 순위 관련 함수 : RANK, DENSE_RANK, ROW_NUMBER 대부분의 DBMS에서 지원
  2. 집계 관련 함수 : SUM, MAX, MIN, AVG, COUNT 대부분의 DBMS에서 사용하나 SQL SERVER 경우 OVER 내의 ORDER BY 구문 지원하지 않음
  3. 그룹 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD ORACLE에서만 지원되는 함수
  4. 그룹 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT, 1,2번은 전부, 3번은 표준에는 없지만 지원, 4번은 ORACLE에서만 지원
  5. 선형분석 포함하는 통계 함수

 

. SYNTAX

SELECT WINDOW_FUNCTION (ARGUMENTS)

OVER ( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] )

FROM 테이블 명;

- WINDOW_FUNCTION : 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있다.

- ARGUMENTS (인수) : 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.

- PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.

- ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다.

- WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.

ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다.

다만, WINDOWING 절은 SQL Server에서는 지원하지 않는다.

 

  1. BETWEEN 사용 타입

ROWS | RANGE BETWEEN

UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

AND

UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

 

  1. BETWEEN 미사용 타입

ROWS | RANGE

UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

 

 

2. 그룹 순위 함수

 

. RANK 함수

특정 항목에 대한 순위를 구하는 함수, 특정 범위내에서 순위를 구할수도 있음, 동일값에 대해서는 동일한 순서 부여

예제) RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK : 전체 중에 연봉 순위

 RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK : 그룹별로 나눈 연봉 순위

 

. DENSE_RANK 함수

동일한 건수를 하나의 건수로 취급

동점이 있을 같은 등수 예를 들어 순위는 1, 2, 2, 3, 4, 5, 5, 6

 

. ROW_NUMBER 함수

동일한 값이라도 고유의 순서를 부여함 예를 들어 순위는 1, 2, 3, 4, 5, 6, 7, 8 같은 값이여도 유니크한 순위를 정함

ORACLE 경우는 ROWID 적은 행부터 나옴

 

3. 일반 집계 함수

 

. SUM 함수

누적값 구할 있음

예제) SELECT    MGR, ENAME, SAL,

SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) as MGR_SUM

FROM EMP;

 

RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.

 

. MAX 함수

INLINE VIEW 파티션별 최대값을 가진 추출 가능

예제) SELECT MGR, ENAME, SAL

FROM (SELECT MGR, ENAME, SAL,

MAX(SAL) OVER (PARTITION BY MGR) as IV_MAX_SAL

    FROM EMP)

WHERE SAL = IV_MAX_SAL ;

매니져 별로 연봉이 가장 높은 직원을 추출

 

. MIN 함수

예제) SELECT MGR, ENAME, HIREDATE, SAL,

              MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN

FROM EMP;

 

. AVG 함수

파티션별 ROWS 윈도우를 통해 평균값 구할 있음

예제) SELECT MGR, ENAME, HIREDATE, SAL,

     ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) as MGR_AVG

FROM EMP;

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정한다.

(ROWS는 현재 행의 앞뒤 건수를 말하는 것임)

 

. COUNT 함수

예제) SELECT ENAME, SAL,

     COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT

FROM EMP;

RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING : 현재 행의 급여값을 기준으로 급여가 -50에서 +150의 범위 내에 포함된 모든 행이 대상이 된다.

(RANGE는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시하는 것임)

 

4. 그룹 순서 함수

 

. FIRST_VALUE 함수

파티션별 윈도우에 가장 먼저 나온 , SQL SERVER 미지원(MIN 활용 가능)

공동 함수 인정하지 않고 처음 나온 행만 처리, 의도적으로 나누고 싶다면 INLINE VIEW ORDER BY 절에 칼럼 추가

예제) SELECT DEPTNO, ENAME, SAL,

     FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING) as RICH_EMP

 FROM EMP;

 

. LAST_VALUE 함수

파티션별 윈도우에 가장 나중에 나온 , SQL SERVER 미지원(MAX 활용 가능)

공동 함수 인정하지 않고 처음 나온 행만 처리, 의도적으로 나누고 싶다면 INLINE VIEW ORDER BY 절에 칼럼 추가

예제) SELECT DEPTNO, ENAME, SAL, LAST_VALUE(ENAME)

    OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as DEPT_POOR

 FROM EMP;

 

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.

 

. LAG 함수

파티션 윈도우에서 이전 몇번째 행의 값을 가져올 있음, SQL SERVER 미지원

NVL, ISNULL 기능과 같음

예제) SELECT ENAME, HIREDATE, SAL,

     LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL

 FROM EMP

 WHERE JOB = 'SALESMAN'

 

LAG(SAL, 2, 0)의 기능은 두 행 앞의 SALARY를 가져오고, 가져올 값이 없는 경우는 0으로 처리한다.

 

[실행 결과] ENAME  HIREDATE   SAL   PREV_SAL

 ---------  ------------- -------- ----------

ALLEN    1981-02-20 1600              0

WARD    1981-02-22 1250              0

TURNER 1981-09-08 1500        1600

MARTIN 1981-09-28 1250        1250

 

. LEAD 함수

파티션 윈도우에서 이후 몇번째 행의 값을 가져올 있음, SQL SERVER 미지원

NVL, ISNULL 기능과 같음

예제) SELECT ENAME, HIREDATE,

     LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) as "NEXTHIRED"

 FROM EMP;

 

[실행 결과] ENAME HIREDATE NEXTHIRED

----------- ------------ ---------------

ALLEN    1981-02-20 1981-02-22

WARD    1981-02-22 1981-04-02

TURNER 1981-09-08 1981-09-28

MARTIN 1981-09-28

 

5. 그룹 비율 함수

 

. RATIO_TO_REPORT 함수

파티션 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구함

결과 값은 > 0 & <= 1이고, 합계는 1, SQL SERVER 미지원

예제 ) ELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R

 FROM EMP

 WHERE JOB = 'SALESMAN';

전체 중에 로우 연봉이 차지하는 비율이 나오게 , 개별 RATIO 전체 합은 1

 

. PERCENT_RANK 함수

윈도우에 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1 하여 순서별 백분율을 구함

결과 값은 > 0 & <= 1이고,  SQL SERVER 미지원

예제) SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R

 FROM EMP;

3개의 로우가 된다면 0, 0.5, 1 순서로 되고, 5개의 로우라면 0, 0.25, 0.5, 0.75, 1 추출됨 ORDER BY역할

 

. CUME_DIST 함수

파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율

결과 값은 > 0 & <= 1이고,  SQL SERVER 미지원

예제) SELECT DEPTNO, ENAME, SAL, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST

 FROM EMP;

5개의 로우가 선택되면 0.2, 0.4, 0.6, 0.8, 1 값이 추출 ORDER BY역할

동일 순서면 뒤의 행의 함수 결과 값을 따름 예를 들면, 0.4, 0.4, 0.6, 0.8, 1

 

. NTILE 함수

파티션별 전체 건수를 ARGUMENT 값으로 N 등분 결과, 조를 나누는 것과 같음

예제) SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE

 FROM EMP;

4개의 파티션으로 나뉘면, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, 4 등으로 로우가 추출되어서 파티션별 조를 정할 있음


'자격증 공방 > SQLD' 카테고리의 다른 글

제 4-8장 절차형 SQL  (0) 2018.07.11
제 4-7절 DCL  (0) 2018.07.11
제 4-5절 그룹함수  (0) 2018.07.11
제 4-4절 서브쿼리  (0) 2018.07.11
제 4-3절 계층형 질의와 셀프 조인  (0) 2018.07.11

 

1. 데이터 분석 개요

 

. AGGREGATE FUNCTION

COUNT, SUM, AVG, MAX, MIN 각종 집계 함수들 포함

 

. GROUP FUNTION

레벨별 집계를 위한 여러 단계의 SQL UNION, UNION ALL 묶은 하나의 테이블을 여러 읽어 다시 재정렬하는 단계를

그룹함수를 이용하면 한번에 해결 가능, CASE 함수를 이용하면 쉽게 원하는 포맷 보고서 작성 가능

  • ROLLUP 함수 : 소그룹 간의 소계를 계산, 확장된 형태, 병렬로 수행가능하기에 효과적임, 계층적 분류를 포함하고 있는 데이터 집계에 적합
  • CUBE 함수 : GROUP BY 항목들 다차원적인 소계를 계산, 모든값에 대한 다차원적인 집계 생성, 시스템 부하를 줄수 있음
  • GROUPING SET 함수 : 특정 항목에 대한 소계를 계산, 원하는 부분만 추출가능
  • Rollup 이나 Cube에 의한 소계가 계산된 결과에는 Grouping = 1 이 표시되고 그 외의 결과에는 Grouping = 0 으로 표시

 

. WINDOW FUNTION

분석함수, 순위함수로 불림, 데이터웨어하우스에서 발전한 기능

 

2. ROLLUP 함수


지정된 그룹 칼럼의 리스트는 SUBTOTAL 생성하기 위해 사용되어짐

생성되는 SUBTOTAL : 칼럼수 + 1

인수는 계층구조라 인수 순서가 바뀌면 수행결과 바뀌니 인수의 순서에도 주의해야함

STEP 1. 일반적인 GROUP BY 사용 + ORDER BY 사용

STEP 2. ROLLUP 함수 사용 + ORDER BY 사용

그룹 함수 수행시 생성되는 표준집계 + 기준칼럼에 대한 소계 + 전체의 마지막 소계

STEP 3. GROUPING 함수 사용 + CASE 함수 사용(ORACLE경우는 DECODE함수 사용)

GROUPING(EXPR) = 1, 그외의 결과는 GROUPINT(EXPR) = 0 CASE함수로 소계에 나타내는 필드에 문자열 지정 가능 보고서 작성시 유용함

예제) SELECT    CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,

CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB,

COUNT(*) "Total Empl",

SUM(SAL) "Total Sal"

FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

GROUP BY ROLLUP (DNAME, JOB);

Oracle의 경우는 DECODE 함수를 사용해서 좀더 짧게 표현할 수 있다.

SELECT     DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME, DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB,

COUNT(*) "Total Empl", SUM(SAL) "Total Sal"

FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB);

 

STEP 4-1. 일부 칼럼에 ROLLUP 함수 사용

예제) GROUP BY DNAME, ROLLUP(JOB) 최종 소계 결과는 나오지 않음

STEP 4-2. 칼럼을 결합하여 ROLLUP 함수 사용

예제) GROUP BY ROLLUP (DNAME, (JOB, MGR)) 하나의 집합으로 간주하여 칼럼별 집계를 구하지 않음

 

3. CUBE 함수


결합 가능한 모든 값에 대하여 다차원 집계를 생성

그룹핑 칼럼의 순서를 바꿔서 한번 쿼리를 추가 수행하고 양쪽 쿼리에서 총계를 모두 생성함으로 연산 대상이 많아 시스템 부하의 단점이 있음

계층구조가 아니라 평등 관계임으로 인수 순서, 정렬순서는 바뀔 수있어도 데이터 결과는 같음

STEP 1. CUBE 함수 이용

칼럼이 가질 있는 모든 경우의 수예 대하여 SUBTATAL 생성됨으로 2 컬럼수에 대한 레벨의 SUBTATAL 발생

ROLLUP함수에 비해 업무별 집계를 표시한 추가적인 데이터가 생성

STEP 2. UNION ALL 사용

CUBE함수를 사용하면 반복 엑세스 하는 부분을 한번으로 줄일 있음, 수행속도 자원 사용율을 개선하고 가독성을 높힘

 

4. GROUPING SET 함수


다양한 소계 집합을 만들 있음, 여러 반복하지 않아도 원하는 결과 얻을 있음

인수별 개별 집계 가능하고 평등관계라 인수 순서 중요하지 않음

컬럼별의 총계들을 구함

 

STEP 1. 3개의 인수를 이용

괄호로 묶은 집합 별로 괄호 내는 계층 구조가 아닌 하나의 데이터로 간주하여 집계를 구할 있음

 

- Rollup(a,b,c) -

{a,b,c} 그룹의 합계

{a.b} 그룹의 합계

{a} 그룹의 합계

{} 전체 테이블에 대한 합계

 - Cube(a,b.c) -

Cube(a,b,c)

{a,b,c} 그룹의 합계 

{a,b}, {a,c}, {b,c} 그룹의 합계

{a}, {b}, {c} 그룹의 합계

{} 전체 테이블에 대한 합계


'자격증 공방 > SQLD' 카테고리의 다른 글

제 4-7절 DCL  (0) 2018.07.11
제 4-6절 윈도우 함수(WINDOW 함수)  (0) 2018.07.11
제 4-4절 서브쿼리  (0) 2018.07.11
제 4-3절 계층형 질의와 셀프 조인  (0) 2018.07.11
제 4-2절 집합 연산자 (SET OPERATOR)  (0) 2018.07.11

하나의 SQL문안에 포함되어 있는 다른 SQL

알려지지 않은 기준을 이용해 검색하기 위해 사용

save image


서브쿼리는 메인쿼리의 칼럼을 모두 사용 가능

메인쿼리는 서브쿼리의 칼럼을 사용할 없음 (필요하다면 JOIN으로 변경하거나 스칼라 서브쿼리를 사용해야함)

서브쿼리 레벨과 상관없이 항상 메인쿼리 레벨로 결과로 생성되어 1:M 관계에서도 결과는 1

 

주의사항

  1. 서브쿼리를 괄호로 감싸서 사용해야함
  2. 단일행 또는 복수행 비교 연산자와 함께 사용가능

단일 비교연산자는 서브쿼리 결과가 반드시 1 이하여야 하고

복수 비교연산자는 결과 건수와 상관없음

  1. 서브쿼리 ORDER BY 사용하지 못함, 메인쿼리의 마지막 문장에 위치해야함
  2. 사용 가능한 - SELELC, FROM, WHERE, HAVING, ORDER BY, INSERT VALUES, UADATE SET

 

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

  • UN-CORRELATED (비연관) 서브쿼리 : 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않은 형태의 서브쿼리

메인쿼리에 (서브쿼리가 실행된결과) 제공하기 위한 목적으로 사용

  • CORREGATED(연관) 서브쿼리 : 서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태의 서브쿼리

      메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리 조건에 맞는지 확인하고자 주로 사용

 

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


save image

 

 

1. 단일 서브쿼리

단일 비교 연산자 ( =, >, >=, <, <=, <>) 함께 사용할 때는 서브쿼리 결과가 반드시 1 이하여야

2 이상을 반환하면 SQL문은 실행시간 오류가 발생, 컴파일할 알수 없는 오류

예제) SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버

 FROM PLAYER

 WHERE HEIGHT <= (SELECT AVG(HEIGHT) FROM PLAYER)

 ORDER BY PLAYER_NAME;

 

2. 다중 서브쿼리

결과가 2 이상이면 다중 비교 연산자 ( IN, ALL, ANY, SOME) 사용해야

다중행 서브쿼리 비교 연산자는 단일 서브쿼리의 비교 연산자로도 사용가능

다중 연산자

설명

IN(서브쿼리)

서브쿼리 결과에 존재하는 임의의 값과 동일한 조건 (MULTIPLE OR 조건)

비교연산자 ALL (서브쿼리)

서브쿼리 결과에 만족하는 모든 값을 만족하는 조건 > 사용하면 최대값보다 모든 조건 만족

비교연산자 ANY(서브쿼리)

서브쿼리 결과에 존재하는 어느 하나의 값이라도 만족하는 조건

> 사용하면 어떤 값이라도 만족하면 됨으로 결과의 최소값보다 모든 조건 만족 (SOME 동일)

EXISTS (서브쿼리)

서브쿼리 결과를 만족하는 값이 존재하는지 여부를 확인, 여러건이여도 1건만 찾으면 이상 검색 하지 않음

 

3. 다중 칼럼 서브쿼리

여러 개의 칼럼이 반환된어 메인쿼리의 조건과 동일하게 비교되는 것을 의미, SQL SERVER에서는 지원되지 않음

예제)  SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

  FROM PLAYER

  WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT) FROM PLAYER GROUP BY TEAM_ID)

  ORDER BY TEAM_ID, PLAYER_NAME;

 

4. 연관 서브 쿼리

서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리

EXISTS 서브쿼리 : 만족하는 1건만 찾음 추가검색 진행하지 않음

예제) SELECT STADIUM_ID ID, STADIUM_NAME 경기장명

FROM STADIUM A

WHERE EXISTS (SELECT 1 FROM SCHEDULE X WHERE X.STADIUM_ID = A.STADIUM_ID AND X.SCHE_DATE BETWEEN '20120501' AND '20120502');

 

5. 그 밖에 위치에서 사용 하는 서브쿼리

 

. SELECT

스칼라 서브쿼리란 ? , 컬럼만을 반환하는 서브쿼리, 단일 서브쿼리 (2건이상이면 오류)

예제) SELECT PLAYER_NAME 선수명, HEIGHT 키, (SELECT AVG(HEIGHT) FROM PLAYER X WHERE X.TEAM_ID = P.TEAM_ID) 팀평균키

 FROM PLAYER P;

 

. FROM

인라인뷰(INLINE VIEW), 동적뷰 (DYNAMIC VIEW)

서브쿼리 결과가 동적으로 생성된 테이블인 처럼 사용 가능, 데이터 베이스에는 해당 정보가 저장되지 않음

조인방식을 사용하는 것과 같아서 인라인 뷰의 칼럼은 SQL 자유롭게 참조 가능

ORDER BY 사용가능,

TOP-N 쿼리 : 인사인 뷰에서 먼저 정렬을 수행하고 결과 중에 일부 데이터만 추출하는 , ORACLE ROWNUM으로 사용가능

예제) SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM (SELECT PLAYER_NAME, POSITION, BACK_NO, HEIGHT FROM PLAYER WHERE HEIGHT IS NOT NULL ORDER BY HEIGHT DESC)

WHERE ROWNUM <= 5;

  • 데이터까지 전부 추출하고싶으면 RANK 함수 사용

 

. HAVING

결과에 대한 부가적인 조건 주기 위해서

 

. UPDATE 문의 SET

새로운 테이블에 이전 테이블의 값을 옮기고 싶을

NULL값을 반환할 경우 칼럼 결과가 NULL 될수 있음으로 주의해야함

 

. INSERT 문의 VALUES

새로운 테이블에 이전 테이블의 , 또는 수정값을 옮길

 

6. 뷰(VIEW)

가상 테이블(VIRTUAL TABLE) 실제 데이터를 가지고 있지 않음, 정의(VIEW DEFINITION) 가지고 있음

정의를 참조해서 질의를 작성하여 질의를 수행

이미 존재하는 뷰를 참조해서도 생성 가능

실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 가진 DBMS 있음

 

장점

  1. 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 프로그램 변경 없음
  2. 편리성 : 복잡한 질의를 뷰로 생성하여 관련 질의를 단순하게 작업할 있음, 해당 SQL 자주 사용 할때 뷰를 이용하면 편리하게 사용
  3. 보안성 : 감추고 싶은 정보를 뷰를 생성할때 제외하고 생성 가능

 

예제) CREATE VIEW V_PLAYER_TEAM

AS SELECT P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.TEAM_ID, T.TEAM_NAME

FROM PLAYER P, TEAM T

WHERE P.TEAM_ID = T.TEAM_ID;

 

제거하기 : DROP VIEW V_PLAYER_TEAM;

        DROP VIEW V_PLAYER_TEAM_FILTER;

+ Recent posts