SQLD정리 노트#2

윈도 함수

순위: RANK(중복 순위 포함), DENSE_RANK(중복 순위 무시), ROW NUMBER(단순히 행 번호 표시, 값에 무관하게 고유한 순위 부여)

집계: SUM, MAX, MIN, AVG, COUNT

행 순서: FIRST_VALUE, LAST_VALUE, LAG(이전 행), LEAD(이후 행)

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

 

윈도 함수에는 OVER 문구가 필수 키워드로 포함!

 

SELECT 칼럼명, 칼럼명,.... ,

    WINDOW_FUNCTION (ARGUMENTS)

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

FROM 테이블명

 

PARTITION BY절 조건과 ORDER BY 절 조건이 충돌 시 ORDER BY 절로 정렬

 

- ARGUMENTS(인수): 함수에 따라 0~N개의 인수.

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

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

- WINDOWING 절: 함수의 대상이 되는 행 기준의 범위를 강력히 지정 가능

                      →ROWS 물리적인 결과 행수/ RANGE 논리적인 값에 의한 범위

 

- UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING : 첫 행, 끝 행

- N PRECEDING, N FOLLOWING : N번째 앞 행, N번째 뒤 행

 

ROLE을 이용한 권한 부여

[Oracle] CONN SYSTEM/MANAGER

CREATE ROLE 롤이름;

GRANT 부여권한, 부여 권한,..... TO 롤 이름;

GRANT 롤이름 TO 유저명;

 

ORACLE에서는 기본적으로 몇 가지 ROLE을 제공하고 있다. 그중 가장 많이 사용하는 ROLE은 CONNECT와 RESOURCE이다.

CONNECT: CREATE SESSION과 같은 로그인 권한

RESOURCE: CREATE TABLE과 같은 오브젝트 생성 권한이 포함된 권한들을 가지고 있다. 일반적으로 유저를 생성할 때 이 두 가지를 사용하여 기본 권한을 부여한다.

 

특정 로그인이 멤버로 참여할 수 있는 서버 수준 역할

-PUBLIC: 모든 SQL Server 로그인은 PUBLIC 서버 역할에 속한다.

-bulkadmin: 데이터베이스를 생성, 변경, 삭제 및 복원할 수 있다.

-diskadmin: 디스크 파일을 관리하는데 사용된다.

-dbcreator: 데이터베이스를 생성, 변경, 삭제 및 복원할 수 있다.

-processadmin: SQL Server의 인스턴스에서 실행 중인 프로세스를 종료할 수 있다.

-securityadmin : 로그인 및 해당 속성을 관리한다. 서버 및 데이터베이스 수준의 사용 권한을 부여, 거부, 취소할 수 있다. 로그인 암호도 다시 설정 가능.

-severadmin : 서버 차원의 구성 옵션을 변경하고 서버를 종료할 수 있다.

-setupadmin : 연결된 서버를 추가하거나 제거할 수 있다.

-sysadmin : 서버에서 모든 작업을 수행할 수 있다. 기본적으로, Windows Builtin\Administrators 그룹의 멤버인 로컬 관리자 그룹은 sysadmin 고정 서버 역할의 멤버이다.

 

절차형 SQL 개요

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

- 절차형 SQL을 이용하여 Procedure, User Defined Function, Trigger 저장 모듈 생성 가능.

 

PL/SQL의 특징

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

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

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

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

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

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

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

 

PL/SQL 구조

DECLARE

            선언부 (변수, 상수) → BEGIN~END에서 사용할 변수나 인수 정의 및 타입 선언

BEGIN

            실행부 → 개발자가 처리하고자 하는 SQL문, 필요한 로직 정의

EXCEPTION

            예외 처리부 → BEGIN~END에서 실행되는 SQL문에 발생된 에러 처리

END