개발 공부

WITH절

SQL을 작성하다 보면 반복되는 서브 쿼리를 이곳저곳 복붙 해서 사용해야 하는 경우가 있다. 이곳저곳 흩어져 있기 때문에 가독성도 떨어지고, 만약 서브 쿼리를 수정해야 한다면 위치를 찾아 각각 다 고쳐줘야 한다.

WITH 절은 이러한 서브 쿼리의 단점을 해결하기 위해 서브 쿼리를 마치 함수처럼 이름을 가지도록 사용하는 방법이다.

약간 VIEW와 쓰임새가 비슷해보이지만 차이점은 WITH 절은 VIEW처럼 DBMS에 저장하여 사용하는 것이 아닌 실행할 쿼리문 내에 정의되어 있어, 해당 쿼리가 실행될 때마다 파싱 되고 실행계획을 세운다.

WITH절 기본

WITH절의 기본 구조 예제는 아래와 같다.

WITH
WITH_SUB AS (
	... -- 서브쿼리 작성
)
SELECT * FROM WITH_SUB; -- WITH절을 사용한 메인 쿼리 예시

WITH절은 여러 개 선언할 수 있으며, WITH절에서 다른 WITH 절을 사용할 수 있다.

WITH
WITH_SUB1 AS (
	... -- 서브쿼리 작성
),
WITH_SUB2 AS (
	SELECT * FROM WITH_SUB1 -- 다른 WITH절을 사용한 WITH절 
)
SELECT * FROM WITH_SUB2;

Recursive WITH절

WITH절을 가지고 자신이 자신을 호출하는 Recursive 한 방식을 사용할 수 있다. 이를 Recursive WITH절이라고 한다.

Recursive WITH 절을 사용하려면 UNION ALL을 가지고 초기값을 정해주는 초기 서브 쿼리와, 이후에 행위를 작성하는 Recurisive 서브 쿼리로 나뉜다. 단 항상 재귀함수에서 그랬듯이 무한루프에 빠지지 않도록 종료조건을 조건절로 표현을 해야한다.

아래는 1~9까지의 합을 구하는 예제이다.

WITH
SUM(NUM, RESULT) AS(
	-- 초기값을 설정해주는 초기 서브쿼리이다. 즉 NUM=1, RESULT=1 이다.
	SELECT 1,1 FROM DUAL 
	UNION ALL
	-- 이후 행위를 정의한 Recurisive 서브쿼리이다. NUM의 값은 1씩 증가시키며, RESULT 값에는 계속 (NUM + 1)을 더한다.
	SELECT NUM+1, (NUM+1) + RESULT FROM SUM WHERE NUM < 9-- 종료조건으로 NUM < 9를 작성했다.
)
SELECT NUM ,RESULT FROM SUM;

계층형 WITH절

오라클에서는 계층형 쿼리를 위해 CONNECT BY문을 지원한다. 하지만 이 문법은 오라클에서만 사용 가능하다. WITH절은 ANSI SQL에 포함되어 있기 때문에 여러 DBMS에서 지원한다. 따라서 WITH절은 DBMS가 변경되어도 쿼리를 변경할 필요가 없다는 장점이 있기때문에 계층형 쿼리를 작성할 때는 CONNECT BY보다는 WITH절을 사용하자.

아래 예제는 WITH절을 이용한 계층형 쿼리 예제이다.

WITH
WITH_TAB (LVL, NAME, EMP_ID, MGR_ID) AS
(
  -- 초기값을 설정해주는 초기 서브쿼리이다. 즉 LVL=1이고, MGR_ID가 NULL인 값이 최초 값이다.
  SELECT 1, NAME, EMP_ID, MGR_ID
  FROM HR.EMPLOYEES
  WHERE MGR_ID IS NULL -- 오라클의 START_WITH와 동일한 조건
  UNION ALL
  -- 이후 행위를 정의한 Recurisive 서브쿼리이다. 초기값의 EMP_ID의 값이 MGR_ID와 같은 행을 찾아 LVEL + 1을 해준다.
  SELECT B.LVL + 1 AS LVL, A.NAME, A.EMP_ID, A.MGR_ID
  FROM HR.EMPLOYEES A, WITH_TAB B
  WHERE A.MGR_ID = B.EMP_ID -- 오라클의 CONNECT BY PRIOR와 동일한 조건
)
SELECT LVL, NAME, EMP_ID, MGR_ID FROM WITH_TAB;

아래는 위와 같은 결과를 나타내는 오라클의 계층형 쿼리이다.

SELECT LEVEL, NAME, EMP_ID, MGR_ID
FROM HR.EMPLOYEES
START WITH MGR_ID IS NULL
CONNECT BY PRIOR EMP_ID = MGR_ID;

SQLD, SQLP 문제에서 자주 나오는 헷갈리는 문장 정리

CHECK

ALTER TABLE EMP ADD CONSTRAINT EMP_CK CHECK(LENGTH(NAME) > 2);

Oracle의 컬럼 제약조건인 CHECK는 조건을 만족하지 못하는 값이 들어올 경우 에러가 발생한다.

NULL은 CHECK 조건을 무시하여 항상 조건을 통과한다.

DML문

INSERT

• ORACLE은 VARCHAR에 ''을 입력하면 NULL로 입력된다.
• SQL Server는 VARCHAR에 ''을 입력하면 ''로 입력된다.

DELETE

• FROM 키워드는 생략 가능하다. ex) DELETE FROM 주문; = DELETE 주문

SELECT

• 두 개 이상의 테이블을 참조하는 FROM절에 테이블에 대한 ALIAS를 사용했을 때, 테이블들이 중복된 칼럼을 가지고 있을 경우 반드시 ALIAS명을 사용해야 함. 

로깅을 하고 싶지 않다면 NOLOGGING 옵션을 사용한다.

ALTER TABLE EMP NOLOGGING;

ESCAPE

LIKE 연산자에서 '%'와 '_'는 특수한 용도로 사용되는 문자인데, 만약 LIKE을 통해 검색하고자 하는 단어에 '%'와 '_'를 포함할 경우에는 'ESCAPE'를 사용하면 된다. 원하는 특수문자를 앞에 작성하고 '%'와 '_'를 사용하면 된다. 아래 예시는 '#'이라는 특수한 문자를 지정하여 '_'를 포함하는 NAME을 검색한다.

SELECT * FROM EMP WHERE NAME LIKE '%#_%' ESCAPE '#';

IN, NOT IN

IN 절에 NULL이 있을 경우, 무시함

NOT IN 절에 NULL이 있을 경우, 항상 true로 리턴하게 되어 NOT IN 결과가 항상 0건임.

INDEX

인덱스는 내림차순으로 생성 및 정렬된다.

인덱스 구조에 따른 특징

• 클러스터 인덱스
  • 인덱스의 리프 데이터가 곧 데이터 페이지다.
• 비트맵 인덱스
  • 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하는 구조이다.
  • 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해 설계 되었다.
• B-tree 인덱스
  • 브랜치 블록과 리프 블록으로 구성되며 브랜치 블록은 분기를 목적으로 하고 리프 블록은 인덱스를 구성하는 칼럼의 값으로 정렬된다.

ORDER BY

ORDER BY COUNT(*)은 가능하다.

ORACLE 은 NULL 을 가장 큰 값으로 취급하여 ORDER BY 시 맨 뒤로 정렬되고 SQL SERVER 반대로 가장 앞으로 정렬한다.

NULL의 순서를 조절하고 싶으면 ORDEY BY 마지막에 NULLS FIRST 또는 NULLS LAST를 사용한다.

ORDER BY 1, COL1 과 같이 숫자와 컬럼을 혼용하여 사용할 수 있다

Random Access

Netsted Loop는 이중포문이기 때문에 n * m 번만큼 랜덤 엑세스가 가장 많이 발생한다.

SQL Server - IDENTITY

IDENTITY [ ( seed , increment ) ]

• SQL Server에서 사용할 수 있는 데이터타입으로 데이터를 식별할 수 있는 식별자 역할을 한다.
• seed : 첫번째 행이 입력될 때의 시작값
• increment: 증가되는 값
• 해당 컬럼에 값을 넣을 경우 Error 발생 (Error 발생을 막기 위해서는 IDENTITY_INSERT 를 OFF 로 하면 되나 이런 경우 IDENTITY 를 쓴 의미가 없어짐)

Sub Query (서브 쿼리)

서브쿼리는 SELECT, FROM, HAVING, ORDER BY절 등에서 사용 가능

서브쿼리 자체에는 ORDER BY절을 사용할 수 없음

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

비연관 서브쿼리는 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용한다.

인라인 뷰

• FROM 절에 사용한 서브 쿼리.
• Dynamic View(동적 뷰)라고도 함.

중첩된 서브쿼리

• WHERE 절에 사용한 서브 쿼리

스칼라 서브 쿼리

• 한 레코드당 정확히 하나의 값을 반환하는 서브 쿼리.
• JOIN으로 동일한 결과를 추출할 수도 있음.

Single Row(단일 행) 서브쿼리

• 실행 결과가 항상 1건 이하인 서브쿼리
• =, <, <= 등 단일행 비교 연산자와 함께 사용됨

Multi Row(다중 행) 서브쿼리

• 실행 결과가 여러 건인 서브쿼리
• IN, ALL 등 다중행 비교 연산자와 함께 사용됨

Multi Column(다중 컬럼) 서브쿼리

• 여러 컬럼을 반환하는 쿼리
• 메인 쿼리의 조건절에 여러 컬럼을 동시에 비교할 수 있음. 단 컬럼 개수와 컬럼의 위치가 동일해야 함
• Oracle에서만 지원하고, Server SQL에서는 지원하지 않음

Transaction

Dirty Read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것

Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상이다. 트랜잭션 격리성이 낮은 경우 발생한다.

Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상을 말한다. 트랜잭션 격리성이 낮은 경우 발생한다. 

저장점

• 저장점을 정의하면 롤백 할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 저장점까지 트랜잭션의 일부만 롤백할 수 있음
• 동일한 이름의 저장점이 중복된다면, 가장 마지막의 저장점이 적용됨.
• 아래는 SP2로 롤백하고 커밋하므로 SP2 이후에 쿼리문장은 적용되지 않는 예제임

  -- ORACLE 
  SAVEPOINT SP1;
  ...
  SAVEPOINT SP2;
  ...
  ROLLBACK TO SP2;
  COMMIT; 
  
  -- SQL Server
  SAVE TRANSACTION SP1;
  ...
  SAVE TRANSACTION SP2;
  ...
  ROLLBACK TRANSACTION SP1;
  COMMIt;

UNION ALL

중복 로우를 허용하는 합집합

칼럼 ALIAS는 첫번째 SQL 모듈 기준으로 표현 됨

정렬 기준은 마지막 SQL 모듈에 표시하면 됨

VIEW(뷰)

View는 데이터 딕셔너리에 SQL문 형태로 저장하되 실행 시에 참조된다.

참조한 테이블이 변경되면 View도 변경 된다.

View를 통해 입력, 수정, 삭제에는 제약이 있다.

한번 생성된 View는 Alter문을 통해 변경할 수 없고, 삭제 후 재 생성해야 한다.

하나의 테이블에 여러 개의 View를 생성 가능하다.

특정 컬럼만 조회할 수 있도록 할 수 있기에 보안 기능이 있다.

데이터 관리가 수월해지고, SELECT 문장이 간단하다.

View는 인덱스를 만들 수 없다.

계층형

ORACLE의 PRIOR은 CONNECT BY 뿐만 아니라, SELECT, WHERE절에서도 사용 가능함

CONNECT_BY_ISLEAF : leaf 데이터면 1, 아니면 0을 반환한다.

CONNECT_BY_ISCYCLE : 중복참조를 체크하는 함수로 CONNECT BY NOCYCLE일 경우에만 사용 가능하다. 서로가 서로를 부모로 가지는 경우를 찾을 때 사용하고, 중복참조 이면 1을 아니면 0을 반환한다. (루트까지의 경로에 존재하는 데이터를 의미한다라고도 사용함.)

CONNECT_BY_ROOT : 루트 노드의 정보

SYS_CONNECT_BY_PATH : 하위 레벨의 칼럼 표시

SQL Server는 계층형 질의문은 CTR(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개

비교연산자

CHAR형

• 길이가 다르다면 작은 쪽에 SPACE 를 추가하여 길이를 같게 한 후에 비교한다

VARCHAR형

• 서로 다른 문자가 나올 때까지 비교한다
• 길이가 다르다면 짧은 것이 끝날 때까지만 비교한 후에 길이가 긴 것이 크다고 판단한다
• 길이가 같고 다른 것이 없다면 같다고 판단한다

조인

NATURAL JOIN

• 동일한 타입과 이름을 가진 컬럼을 조인 조건으로 이용하는 조인으로 반드시 조인하는 대상의 테이블들은 서로 같은 이름과 타입의 컬럼이 존재해야한다.
• NATURAL JOIN에 사용된 컬럼은 식별자를 사용할 수 없다.

  -- EMP와 DEPT 테이블에 DEPOTNO 컬럼이 각각 존재하므로 DEPTNO로 NATURAL JOIN한다.
  -- 단, DEPNO를 가지고 식별자를 사용하였으므로 (EMP.DEPTNO) 아래 쿼리는 에러가 발생
  SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
  FROM EMP NATURAL JOIN DEPT;

USING JOIN

• 두 개의 테이블의 컬럼명이 같을 경우 조인 조건을 길게 적지 않고 간단하게 적을 수 있도록 하는 역할
• NATURAL JOIN과 동일하게 USING JOIN에 사용된 컬럼은 식별자를 사용할 수 없다.

  SELECT DEPT_NO
  FROM EMP INNER JOIN DEPT USING(DEPT_NO)

FULL OUTER JOIN

• = LEFT OUTER JOIN UNION RIGHT OUTER JOIN (UNION이므로 중복을 제거함)

오라클 (+) 부호

• 해당 테이블에 null을 허용한다는 의미이다.
• 등호의 오른쪽 테이블에 (+)가 붙으면 오른쪽 테이블에 null을 허용하는 것이므로 LEFT OUTER JOIN
• 등호의 왼쪽 테이블에 (+)가 붙으면 왼쪽 테이블에 null을 허용하는 것이므로 RIGHT OUTER JOIN이다.

참조문

DELETE(MODIFY) (액션들) : 참조무결성에서 부모가 삭제될 경우, 행할 액션을 지정

• CASCADE : 부모 삭제시, 자식도 삭제
• SET NULL : 부모 삭제시, 해당 필드 NULL로 설정
• SET DEFAULT : 부모 삭제시, 해당 필드의 기본값 설정
• RESTRICT : 자식 테이블에 PK 값이 없는 경우만 부모의 값을 삭제 가능
• NO ACTION : 참조 무결성을 해치는 삭제가 불가능. 즉 자식에 해당하는 값이 있을 경우, 부모 삭제 불가능

INSERT (액션들) : 참조무결성에서 자식이 삽입될 경우, 행할 액션을 지정

• AUTOMATIC : 자식을 삽입했는데 부모의 값이 없을 경우, 부모값을 생성하고, 자식값을 삽입
• SET NULL : 자식을 삽입했는데 부모의 값이 없을 경우, 해당 필드를 NULL로 설정
• SET DEFAULT : 자식을 삽입했는데 부모의 값이 없을 경우, 해당 필드를 기본값 설정
• DEPENDENT : 자식을 삽입했는데 부모의 값이 없을 경우에는 삽입 불가능. 즉, 부모에 값이 없으면 자식은 삽입 불가능
• NO ACTION : 참조 무결성을 해치는 입력이 불가능. 

ETC

Oracle의 경우 Select시 조회되는 Header Column명은 디폴트로 대문자로 표현한다.  

절차형 SQL

절차형 프로그래밍 언어인 C언어처럼 SQL에도 절차 지향적인 프로그래밍이 가능하도록 벤더별로 프로그래밍 언어와 같은 절차형 SQL을 제공한다.

오라클에서는 PL(Procedural Language)/SQL을 제공하며 SQL Server에서는 T-SQL을 제공한다.

본 포스팅은 오라클의 PL/SQL을 아주 간단하게만 설명한다.

PL/SQL 구조

PL/SQL은 기본적으로 BLOCK 구조로 되어있다. BLOCK 안에는 기본적으로 DECLARE, BEGIN, EXCEPTION으로 구성되어 있으며 마지막에는 END로 BLOCK의 끝을 표현한다.

DECLARE : 선언부라고 하며 BEGINE ~ END에서 사용할 변수(상수)에 대하여 정의하는 부분이다. 정의할 게 없다면 생략 가능하다.

• 변수란 데이터의 임시 저장영역으로 반복해서 재사용하기 위해 사용한다.
• 변수를 사용할 수 있는 범위는 해당 블록 시작(BEGIN)에서 블록의 끝(END)에서만 사용가능하다.
• 변수의 명칭은 반드시 문자로 시작해야 하며, 문자나 숫자, 특수문자를 포함할 수 있다. 단 변수명은 30 bytes 이하로 명명해야 한다.
• 특정 값으로 미리 초기화 가능하다. 
• 데이터를 할당하려면 ':='을 사용한다.
• 상수로 사용하려면 변수명 다음에 CONSTANT 키워드를 사용하면 된다.
• 변수의 데이터 타입은 직접 지정할 수 있지만, 다른 테이블에서 참조해서 지정할 수 있다.
• 아래는 변수 선언의 예시이다.

  DECLARE
  	V_PI CONSTANT NUMBER := 3.14;			-- 상수로 선언하였으며 변경 불가능하다.
  	v_name VARCHAR2(100) := '홍길동';			-- 변수로 선언하였으며 디폴트 값으로 미리 '홍길동'을 할당
  	v_addr VARCHAR2(100); 				-- 변수로 선언하였으며 디폴트 값을 선언하지 않았다.
  	v_you VHARCHAR2(100) := '&your_name'; 		-- 해당 블록을 실행할때마다 사용자한테 입력받는다.​
  	v_deptId DEPT.DEPT_ID%TYPE			-- DEPT 테이블의 DEPT_ID 컬럼의 데이터타입을 참조한다.
  	v_temp	DEPT%ROWTYPE		-- DEPT 테이블의 선언된 컬럼의 모든 데이터타입 아무거나 들어갈 수 있다.

BEGIN : 실행 부라고 하며 처리하고자 하는 SQL문과 필요한 로직을 기술하는 부분이다.

EXCEPTION : 예외처리 부라고 하며 실행 도중에 에러가 발생할 경우에 에러를 처리하는 로직을 기술하는 부분이다. 생략 가능하다.

EXCEPTION
WHEN 예외명1 THEN
	예외처리문장;
WHEN 예외명2 OR 예외명3 THEN
	예외처리문장;
WHEN OTHERS THEN
	나머지_예외처리문장;

PL/SQL 특징

DECLARE, BEGIN, EXCEPTION, END와 같은 예약어는 마지막에 세미콜론(;)을 붙이지 않는다. 이외의 명령어는 세미콜론(;)을 붙인다.

BLOCK안에 새로운 BLOCK을 포함할 수 있다. 이를 Nested Block이라고 표현한다.

IF, LOOP 등의 조건을 사용하여 절차적인 프로그래밍이 가능하다.

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

PL/SQL로 작성된 코드는 PL/SQL 엔진이 처리하고, 일반적인 SQL 문장은 SQL 실행기가 처리한다.

PL/SQL에서는 DML 문장은 직접적으로 쿼리를 작성하여 사용할 수 있다. 하지만 DDL, DCL, TCL은 직접적으로 지원하지 않는다. 동적 SQL(런타임에 문자열로 작성되는 SQL)을 사용하면 가능하긴 하다. 동적 SQL은 execute immediate를 이용한다.

BEGIN
	execute immediate 'TRUNCATE TABLE 사원';
END

PL/SQL을 저장해서 쉽게 사용할 수 있다. 저장하여 사용할 경우 Stored PL/SQL Block이라고 한다. 저장하지 않고 사용할 경우에는 Anonymous PL/SQL Block이라고 한다.

PL/SQL SELECT

• PL/SQL의 SELECT는 SELECT 한 데이터를 INTO 뒤에 변수에 값을 저장한다.
• SELECT 컬럼 항목의 수와 INTO 변수 항목의 수는 동일해야 한다.
• 반드시 로우의 결과는 1건만 나타나야 한다.
• 아래 예시는 사원 ID가 '1'인 사원의 사원명이 v_name에 저장된다.

  DECLARE
  	v_name VARCHAR2(10);
  
  BEGIN
  	SELECT 사원명 INTO v_name FROM 사원 WHERE 사원ID = '1'; 
  
  END​

PL/SQL DML (INSERT, UPDATE, DELETE)

• 기본적인 DML문을 BEGIN 문장에 사용하면 된다.
• 사용자로부터 입력을 받고자 한다면 선언부에 &을 사용하여 입력받는다.
• 아래 예시는 사원 테이블에 INSERT를 하는 예시이다.

  DECLARE
  	v_id	VARCHAR2(4) := '&id';
  	v_name	VARCHAR2(10) := '&name';
      
  BEGIN
  	INSERT INTO 사원 VALUES(v_id, v_name);
  
  END

PL/SQL DML 제어문

IF 조건문

IF (조건) THEN
	실행문장;
ELSE
	실행문장;
END IF;


IF (조건) THEN
	실행문장;
ELSEIF (조건) THEN
	실행문장;
END IF;​

CASE 조건문

CASE 변수
	WHEN 조건1 THEN 결과1
	WHEN 조건1 THEN 결과1
	...
	WHEN 조건N THEN 결과N
ELSE 디폴트결과
END;

LOOP 반복문

LOOP
	문장;
    EXIT 조건;
ENd LOOP;

WHILE 반복문

WHILE 조건 LOOP
	문장;
END LOOP;

FOR 반복문

FOR 변수 IN 시작숫자..끝숫자 LOOP
	문장;
END LOOP;

Stored PL/SQL Block

PL/SQL을 마치 함수(프로그램)처럼 정의해서 사용할 수 있다. 이를 Stored PL/SQL Block이라고 한다. 저장하지 않고 이름 없이 사용하는 것은 Anonymous PL/SQL Block이라고 한다. 아래는 두 개의 차이점을 정리한 내용이다.

Stored PL/SQL Block의 종류는 Procedure, Function, Package, Trigger가 있다.

Procedure(프로시저)

• 가장 기본적인 유형으로 한 번 컴파일된 후 데이터베이스에 저장되어 사용되고, 다른 프로그램에서 호출 가능하다.
• 아래는 데이터베이스에 Procedure를 생성하는 방법이다.

  CREATE PROCEDURE p_test -- 프로시저 명칭을 입력한다.
  (
  	파라미터1 IN 데이터타입,
  	리턴값1 OUT 데이터타입,
  	둘다가능 IN OUT 데이터타입
  )
  IS
  	변수1 데이터타입;
  
  PL/SQL Block​

• 파라미터1은 호출하는 곳에서 전달해줘야 하는 파라미터를 의미하므로 IN을 사용한다. 생략 가능하다.
• 리턴값1은 호출하는 곳에서 리턴 값을 받을 리턴값을 의미하므로 OUT을 사용한다.
• 둘 다 가능은 파라미터와 리턴 값이 된다는 의미로 IN OUT을 사용한다.
• Procedure을 호출하는 방법은 아래와 같다.
  

  EXEC p_test() -- 인자가 없는 Procedure일 경우
  
  EXEC p_test(파라미터1, 리턴값1, 둘다가능) -- 인자가 있는 Procedure일 경우

Function(함수)

• Function은 반드시 리턴 값이 존재하는 Block이다.
• 아래는 데이터베이스에 Function을 생성하는 방법이다.

  CREATE FUNCTION f_test -- 함수 명칭을 입력한다.
  (
  	파라미터1 IN 데이터타입,
  	리턴값1 OUT 데이터타입,
  	둘다가능 IN OUT 데이터타입
  )
  RETURN 데이터타입
  IS
  	변수1 데이터타입;
  
  PL/SQL Block​

• 위에서 설명한 Procedure와 거의 동일하다. 차이점은 반드시 리턴 값이 존재하므로 RETURN을 사용한다. 그리고 리턴 값은 Blobk문에서 RETURN을 사용하여 리턴한다.
• Function은 일반적으로 알고 있는 오라클 함수처럼 사용할 수 있다.
• Procedure와 Function은 자율적으로 트랜잭션을 처리할 수 있다.

Package(패키지)

• Package는 연광성이 있는 변수, 상수, PL/SQL Block 등을 논리적으로 하나의 그룹으로 만드는 특수한 형태이다. Package를 만들고 그 안에 여러 가지 항목을 작성하여 사용하면 된다.
• Package는 인터페이스와 같이 사용방법을 기술한 선언부(스펙)와 실제 구현이 담겨 있는 바디부로 나뉜다.
• 아래는 데이터베이스에 Package를 생성하는 방법이다.
  

  -- 선언부
  CREATE PACKAGE pack_test
  IS
    상수명 CONSTANT 데이터타입;
    예외명 EXCEPTION;
    변수명 데이터타입;
  
    PROCEDURE 프로시저명칭 (변수타입들..);
    FUNCTION 함수명칭 (변수타입들..) RETURN 반환타입;
  END pack_test;
  
  
  -- 바디부
  CREATE PACKAGE BODY pack_test
  IS
    상수명 CONSTANT 데이터타입;
    예외명 EXCEPTION;
    변수명 데이터타입;
  
    PROCEDURE 프로시저명칭 -- 프로시저 생성구문
        ...
    END 프로시저명칭;
  
    FUNCTION 함수명칭 -- 함수 생성구문
        ...
    END 함수명칭;
  END pack_test;

   
• 호출하는 방법은 패키지명. 명칭처럼 사용하면 된다. ex) pack_test.getList;

Trigger(트리거)

• Trigger는 특정 이벤트가 발생할 때 자동으로 수행되는 Block을 의미한다. 따라서 트리거는 직접적으로 실행할 수는 없다.
• 자동으로 수행하게 하여 데이터의 무결성과 일관성을 유지하도록 한다.
• 트리거는 TCL을 이용하여 트랜잭션을 제어할 수 없다.
• 특정 이벤트가 발생하기 전(BEFORE EVENT), 특정 이벤트가 발생 후(AFTER EVENT) 등을 설정할 수 있다.
• 이벤트에는 INSERT, UPDATE, DELETE, DDL, DROP, TRUNCATE 등을 사용할 수 있다.
• DELETE ON TRIGGER 의 경우,  :OLD 는 삭제 전 데이터를 , :NEW 는 삭제 후 데이터를 나타낸다.
• UPDATE TRIGGER 에서, :OLD 에는 수정 전, :NEW 에는 수정 후 값이 들어간다.
• 아래는 Trigger를 생성하는 방법이다.

  CREATE [OR REPLACE] TRIGGER trigger_name
  BEFORE | AFTER
       trigger_event ON table_name
       [FOR EACH ROW]
       [WHEN (condition)]
       PL/SQL block
       
       
  CREATE TRIGGER 인서트제약트리거
  BEFORE UPDATE ON 사원연봉
  BEGIN
  	IF (:NEW.입력자) NOT IN ('대표') THEN
  		RAISE_APPLICATION_ERROR(-9999,'대표만 수정할 수 있습니다.');
  	END IF;
  END

CURSOR(커서)

커서를 사용하면 처리된 SQL문장의 결과 집합에 접근할 수 있다. 

묵시적 커서는 바로 이전에 실행된 SQL문에 대하여 커서를 가지게 되어 특별한 키워드를 이용해 결과 집합에 대한 정보를 얻을 수 있다.

SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT

명시적 커서는 직접 작성한 SQL 문장을 커서로 만들어 사용하는 방법이다. 순서는 다음과 같다.

1. 커서 선언 : 사용할 커서에 이름을 부여하고 SQL문을 작성한다.

   CURSOR 커서명[(매개변수들)]
   IS
   SQL문장;

2. 커서 열기 : 선언한 커서를 OPEN한다. 매개변수가 존재할 경우 매개변수를 함께 전달한다.

   OPEN 커서명[(매개변수들)];​

3. 커서 패치 : 해당 SQL문을 실행하여 원하는 작업을 한다.

   -- 일반적으로 패치한 데이터를 모두 접근하기 위해 루프를 사용한다.
   LOOP
   FETCH 커서명 INTO 변수들; -- 수행한 SQL문에 데이터를 변수에 저장한다.
   EXIT WHEN 커서명%NOTFOUND; -- 더 이상 데이터가 없을 경우 루프를 종료한다.
   END LOOP;​

4. 커서 닫기 : 커서 사용을 완료하면 닫아준다.

   CLOSE 커서명;​

윈도우 함수

SELECT WINDOW_FUNCTION([컬럼]) OVER 
( [PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절] )
FROM 테이블명 ;

윈도우 함수는 로우 간의 관계를 구할 수 있도록 만들어 놓은 함수이다. 로우 간의 순위, 집계, 순서, 비율 등을 구할 수 있는 함수를 제공한다.  인자가 있는 함수도 있고 없는 함수도 있다

ORDER BY 절을 사용하여 어떤 컬럼 기준으로 순위를 정할지 지정할 수 있다. 순위 함수가 아닐 경우에는 정렬로 사용한다.  아래는 ORDER BY를 사용하여 모든 로우에 대해서 매출액 기준으로 순위를 구하는 예시이다.

SELECT 부서번호, 사원번호, 사원명, 매출액
	,RANK() OVER (ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 순위;

모든 로우간의 관계를 구할 수도 있으며, PARTITION BY 절을 사용하여 특정 그룹 조건을 기준으로도 관계를 구할 수 있다. 아래는 PARTITION BY를 사용하여 같은 부서 안에서 매출액 기준으로 순위를 구하는 예시이다.

SELECT 부서번호, 사원번호, 사원명, 매출액
	,RANK() OVER (PARTITION BY 부서번호 ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 부서번호, 순위;

WINDOWING 절을 사용하여 윈도우 함수에 포함시킬 로우의 조건(범위)을 지정할 수 있다. 크게 ROWS와 RANGE를 사용할 수 있다.

ROWS : 현재 ROW 위치에서 물리적인 범위

RANGE : 현재 ROW 값을 기준으로 논리적인 범위

예를 들어 설명하기 위해 먼저 UNBOUNDED PRECEDING이라는 것을 가지고 ROWS와 RANGE를 비교하여 설명한다.

• ROWS UNBOUNDED PRECEDING : ROWS UNBOUNDED PRECEDING은 현재 행과 현재 행 위에 있는 모든 행들을 함수에 사용하라는 의미이다. 아래 예시는 부서 번호 기준으로 행이 출력되며 아래로 내려가면서 매출액을 계속 SUM 하여 매출액 합계를 구하는 쿼리이다. 즉, 첫 번째 행은 자신의 매출액만 표현되고, 맨 아래 행은 모든 매출액의 합이 출력된다.

  SELECT 부서번호, 사원명, 매출액
  	,SUM(매출액) OVER (ORDER BY 부서번호 ROWS UNBOUNDED PRECEDING) AS 매출액합계
  FROM 사원;

  
  

  부서번호	사원명	매출액	매출액 합계
  1	A	500	500
  1	B	1000	1500
  2	C	700	2200
  3	D	300	2500
  3	E	600	3100
  3	F	900	4000

• RANGE UNBOUNDED PRECEDING : RANGE UNBOUNDED PRECEDING은 현재 행의 값을 기준으로 이하의 값을 함수에 사용하라는 의미이다. 아래 예시는 부서 그룹내에서 자신의 매출액보다 작은 매출액을 모두 SUM하여 매출액 합계를 구하는 쿼리이다. 즉, 자신의 값 + 자신보다 작은 모든값의 합이 출력된다.

  SELECT 부서번호, 사원명, 매출액
  	,SUM(매출액) OVER (PARTITION BY 부서번호 ORDER BY 매출액 RANGE UNBOUNDED PRECEDING) AS 매출액합계
  FROM 사원;

  부서 번호	사원명	매출액	매출액 합계
  1	A	500	500
  1	B	1000	1500
  2	C	700	700
  3	D	300	600
  3	E	300	600
  3	F	900	900

UNBOUNDED PRECEDING 이외에 WINDOWING 절은 아래와 같이 다양하게 존재한다.

• ROWS 숫자(N) PRECEDING : 현재 행과 바로 위의 행 N 개의 값을 함수에 사용하라는 의미이다. 아래 예시는 부서 번호 기준으로 행이 출력되며 첫 번째 행은 자신의 매출액만 표현되고, 두 번째 행부터는 자신 행의 바로 위의 행의 매출액과 자신의 매출액을 더한 매출액이 표현된다.

  SELECT 부서번호, 사원명, 매출액
  	,SUM(매출액) OVER (ORDER BY 부서번호 ROWS 1 PRECEDING) AS 매출액합계
  FROM 사원;

  부서 번호	사원명	매출액	매출액 합계
  1	A	500	500 (0 + 500)
  1	B	1000	1500 (500 + 100)
  2	C	700	1700 (1000 + 700)
  3	D	300	1000 (700 + 300)
  3	E	600	900 (300 + 600)
  3	F	900	1500 (600 + 900)

위에서 설명한 WINDOWING 절은 사실 BETWEEN이라는 구문이 생략되었다. 위에서 설명한 절을 BETWEEN으로 표현하면 다음과 같다.

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
• ROWS BETWEEN 숫자(N) PRECEDING AND CURRENT ROW

BETWEEN은 윈도우의 시작과 끝 위치를 지정하는 것으로 생략 가능하다. 생략한다면 위와 같이 CURRENT ROW, 즉 현재 행을 끝 위치로 지정하는 것이다. 따라서 위에 결과를 보면 항상 현재 행에서 멈춘 것을 확인할 수 있다.

현재 행이 아닌 맨 마지막 행을 끝 위치로 표현하고 싶다면 AND 뒤에 UNBOUNDED FOLLOWING을 사용하면 된다.

자신보다 위에 있는 N개만큼을 함수에 포함하는 숫자(N) PRECEDING 절처럼 FOLLOWING도 숫자(N) FOLLOWING 과 같이 사용하여 현재 행을 기준으로 끝 위치를 N개만큼 지정할 수 있다.

윈도우 순위 함수

RANK

SELECT 사원번호, 사원명, 매출액
	,RANK() OVER (ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 순위

• ORDER BY에 기술한 컬럼 기준으로 순위를 구하는 함수이다.
• 동일한 값에 대해서는 동일한 순위를 부여하고, 다음 순위는 비워둔다.

DENSE_RANK

SELECT 사원번호, 사원명, 매출액
	,DENSE_RANK() OVER (ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 순위

• ORDER BY에 기술한 컬럼 기준으로 순위를 구하는 함수이다.
• 동일한 값에 대해서는 동일한 순위를 부여하고, 다음 순위는 RANK 함수와 다르게 비워두지 않는다.

ROW_NUMBER

SELECT 사원번호, 사원명, 매출액
	,ROW_NUMBER() OVER (ORDER BY 매출액 DESC) AS 순위
FROM 사원
ORDER BY 순위

• ORDER BY에 기술한 컬럼 기준으로 순위를 구하는 함수이다.
• 동일한 값이라도 고유한 순위를 부여한다. 오라클의 경우 ROWID가 작은 행이 우선순위가 높다.
• 만약 동일한 값에 대해서 순서를 관리하고 싶다면 ORDER BY절에 다른 조건의 컬럼을 추가해야 한다.

윈도우 집계 함수

윈도우 집계 함수에는 SUM, MAX, MIN, AVG, COUNT가 있으며 특별하게 주의해야 할 점이 없으므로 설명은 생략한다.

윈도우 순서 함수

윈도우 순서 함수에는 FIRST_VALUE, LAST_VALUE, LAG, LEAD가 있다. FIRST_VALUE와 LAST_VALUE는 각각 집계 함수의 MAX와 MIN 함수와 동일한 결과를 출력한다.

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

LAG

SELECT 사원번호, 사원명, 매출액
	,LAG(매출액, 1, 9999) OVER (ORDER BY 매출액 DESC) AS 내_바로_위_매출액
FROM 사원;

• LAG의 두 번째 인자는 이전 몇 번째 행의 값을 가져올지를 정하는 인수이며, 세 번째 인자는 이전 몇 번째 행의 값을 가져올 수 없거나 NULL일 경우에 세팅할 값을 설정한다.

LEAD

• LAG와는 반대로 이후 몇 번째 행의 값을 가져올 수 있다. 함수 사용 방법은 LAG와 동일하다.

윈도우 비율 함수

CUME_DIST

SELECT 사원번호, 사원명, 매출액
	,CUME_DIST() OVER (ORDER BY 매출액 DESC) AS 누적분포율
FROM 사원;

• CUME_DIST는 누적 분포를 계산하는 것이다.
• 즉 윈도우 중에 자신의 위치가 어느 정도인지 0~1 사이로 표현한다.
• NULL 값도 계산에 포함한다. 그리고 가능한 가장 낮은 값으로 NULL을 취급한다.

PERCENT_RANK

SELECT 사원번호, 사원명, 매출액
	,PERCENT_RANK() OVER (ORDER BY 매출액 DESC) AS 백분율순위
FROM 사원;

• 백분율의 순위를 계산한다.

NTILE

SELECT 사원번호, 사원명, 매출액
	,NTILE(2) OVER (ORDER BY 매출액 DESC) AS 타일번호
FROM 사원;

• 값을 N개의 타일(등분)하여 그룹핑 번호를 반환한다.

AWS 문서 참고 : https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/c_Window_functions.html

다중행 함수

• 다중행 함수는 여러 행(로우)를 입력으로 하여 처리하는 함수이며, 단일 값을 반환한다.
• 다중행 함수에는 그룹함수, 집계함수, 윈도우 함수 등이 있다.

그룹 함수

• 여러 로우의 데이터를 가지고 한 번에 처리하여 결과 값 1건을 반환하는 함수이다.
• 1건으로 만들기 때문에 복수행 함수 또는 집계 함수라고도 표현한다.
• 그룹 함수는 일반적으로 NULL 값을 제외하여 처리한다.
• 그룹 함수는 WHERE절에 사용할 수 없다.

기본 함수

COUNT(*)

• NULL 값을 포함한 행의 수를 출력한다.

COUNT(표현식)

• NULL 값을 제외한 행의 수를 출력한다.

SUM(표현식)

• 표현식의 NULL 값을 제외한 합계를 출력한다.

AVG(표현식)

• 표현식의 NULL 값을 제외한 평균을 출력한다.
• NULL값을 0으로 하여 전체 평균을 구하고 싶다면 NVL 함수를 사용한다. 예시는 다음과 같다.
• AVG(NVL(score, 0))

MAX(표현식) / MIN(표현식)

• 표현식의 최대값, 최솟값을 출력한다.
• 문자, 날짜 타입도 사용 가능하다.
• 날짜의 최대값은 현재랑 가장 가까운 날이다.

STDDEV(표현식) / VARIAN(표현식)

• 표현식의 표준편차, 분산을 출력한다.

GROUP BY

SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] GROUP BY 칼럼

• GROUP BY는 SELECT에 그룹 함수(= 복수행 함수 = 집계 함수)을 사용할 때, 특정 조건으로 그룹을 만들어 그룹별로 함수를 사용하여 결과를 출력한다.
• GROUP BY을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
• GROUP BY는 SELECT와 달리 ALIAS를 사용할 수 없다.

HAVING

SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] [GROUP BY 칼럼] HAVING 그룹조건식

• 그룹 함수는 WHERE절에 사용할 수 없다. WHERE절 대신 HAVING 절을 사용한다.
• HAVING은 일반적으로 GROUP BY 뒤에 표현하지만, 앞에 표현해도 된다.
• GROUP BY가 없어도 HAVING은 사용할 수 있다.

집계함수 : ROLLUP, CUBE, GROUPING SETS

SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] GROUP BY ROLLUP(칼럼1, 칼럼2)

-- ROLLUP을 UNION ALL로 표현하면 아래와 같음
GROUP BY (칼럼1, 칼럼2)
UNION ALL
GROUP BY (칼럼1)
UNIN ALL
모든 집합 그룹 결과


SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] GROUP BY CUBE(칼럼1, 칼럼2)

-- CUBE를 UNION ALL로 표현하면 아래와 같음
GROUP BY (칼럼1, 칼럼2)
UNION ALL
GROUP BY (칼럼1)
UNION ALL
GROUP BY (칼럼2)
UNIN ALL
모든 집합 그룹 결과


SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] GROUP BY GROUPING SETS(칼럼1, 칼럼2)

-- GROUPING SETS을 UNION ALL로 표현하면 아래와 같음
GROUP BY (칼럼1)
UNION ALL
GROUP BY (칼럼2)

• ROLLUP은 주어진 그룹핑의 소계를 표현한다.
• CUBE는 소계 + 총계를 표현한다. 즉 ROLLUP 결과에 총계가 추가된다.
• 예제는 아래와 같다.

SELECT 부서, 직급, COUNT(*) FROM 직원 GROUP BY CUBE(부서, 직급)

GROUPING

GROUPING은 해당 로우 데이터가 ROLLUP, CUBE 또는 GROUPING SETS을 이용할 때 만들어진 로우인지, 아니면 일반 로우의 데이터인지를 판별할 수 있는 함수이다.

즉, 해당 컬럼의 데이터가 Null인 경우에는 특수하게 생성된 로우이므로 1을 반환하고, 해당 컬럼의 데이터가 Null이 아닌 경우에는 기존 로우의 데이터를 이용한 것이므로 0을 반환한다.

SELECT CASE WHEN GROUPING(부서) = 0 THEN 부서 ELSE '합계' END AS 부서
, 직급, COUNT(*) FROM 직원 GROUP BY CUBE(부서, 직급)

단일행 함수

• 한 번에 한 로우씩 처리하여  한 로우에 결과를 변경하는 함수이다.
• 단일행 함수는 SELECT, WHERE, ORDER BY, UPDATE의 SET 절에서 사용 가능하다.
• 단일행 함수는 1:M 조인이어도 M쪽에서 출력된 행이 하나씩 단일행 함수의 입력값으로 사용되므로 사용가 가능하다.
• 단일행 함수는 입력되는 값에 따라 문자 함수, 숫자 함수, 날짜 함수, 변환 함수, 일반 함수로 구분된다.

문자 함수

UPPER(컬럼)

• 모든 문자를 대문자로 변경(UPPER)

LOWER(컬럼)

• 모든 문자를 소문자로 변경(LOWER)

INITCAP(컬럼)

• 첫(INIT) 글자는 대문자(CAP), 나머지는 소문자로 변경

LPAD(컬럼, 자릿수, '빈자리를 채울 문자')

• 두 번째 인자인 자리 수만큼 문자열의 부족한 자리를 왼쪽(L)에서부터 세 번째 인자로 채운다(PAD).

RPAD(컬럼, 자릿수, '빈자리를 채울 문자')

• 두 번째 인자인 자리 수만큼 문자열의 부족한 자리를 오른쪽(R)에서부터 세 번째 인자로 채운다(PAD).

LTRIM(컬럼, '제거할 문자열')

• 문자열의 왼쪽(L)에 두 번째 인자인 제거할 문자열이 존재하면 제거(TRIM)한다. '제거할 문자'는 옵션으로 디폴트 값은 공백이다.
• '제거할 문자'를 동일한 값으로 여러 개 입력하여도 한 문자로 판단한다.
• 예를 들어 LTRIM(컬럼, '0') 이나 LTRIM(컬럼 '0000000')이나 동일하다.

RTRIM(컬럼, '제거할 문자열')

• 문자열의 오른쪽(R)에 두 번째 인자인 제거할 문자열이 존재하면 제거(TRIM)한다. 나머지 특징은 LTRIM과 동일하다.

TRIM( [BOTH | LEADING | TRAILING] '제거할 문자' FROM '문자열')

• 문자열의 양쪽(BOTH), 앞쪽(LEADING) 또는 뒤쪽(TRAILING)에 제거할 문자가 존재하면 제거(TRIM)한다.
• 문자열만 입력한다면 양쪽의 공백을 제거한다.
• BOTH, LEADING, TRAILING 셋 중 아무것도 입력하지 않는다면, 디폴트 값은 BOTH이다.
• '제거할 문자'는 '문자열'이 아니고 '문자'이다. 즉 char 하나만 입력받는다. 문자열을 입력하게 되면 에러가 발생한다.

SUBSTR(컬럼, 시작 위치, 길이)

• 문자열에서 두 번째 인자인 시작 위치에서 세 번째 인자인 길이만큼 문자열을 잘라낼 때(SUBSTR) 사용하는 함수이다.
• 시작 위치의 인덱스는 1부터 시작한다.
• 시작 위치는 음수도 될 수 있다. 시작 위치가 -1이라면 맨 끝 문자가 시작 위치이다. 그리고 스캔하는 방향을 항상 오른쪽으로 한다. 아래 설명할 INSTR 함수랑 다르다.

INSTR(컬럼, 찾는 글자, 시작 위치, 횟수)

• 문자열에서 특정 문자의 위치를 찾을 때(INSTR) 사용하는 함수이다. 찾을 때의 시작 위치를 입력받으며, 몇 번째로 해당 글자를 만났을 때 위치를 반환할지도 입력받는다.
• 시작 위치와 횟수는 입력을 받지 않을 경우, 각각 디폴트 값은 1, 1이다.
• 찾는 문자가 없으면 0을 반환한다.
• 시작 위치는 음수도 될 수 있다. 시작 위치가 -1이라면 맨 끝 문자가 시작 위치이다. 그리고 스캔하는 방향은 항상 왼쪽이다. 위에 설명한 SUBSTR 함수랑 다르다.

REPLACE(컬럼, '찾을 문자' '변환 문자')

• 문자열에서 특정 문자를 찾아 변환한다(REPLACE).

숫자 함수

ABS(숫자)

• 절댓값을 구한다.

MOD(숫자, 나눌숫자)

• 나머지를 구한다. 숫자 % 나눌숫자

SIGN(숫자)

• 양수이면 1, 음수이면 -1, 0이면 0을 반환하는 함수이다.

ROUND(숫자, 자릿수)

• 반올림한다.
• 자릿수는 음수도 입력할 수 있다. 음수를 입력하면 소수점이 아닌 정수형 자릿수를 나타낸다.

TRUNC(숫자, 자릿수)

• 내림한다.
• 자릿수는 음수도 입력할 수 있다. 음수를 입력하면 소수점이 아닌 정수형 자릿수를 나타낸다.

CEIL(숫자)

• 주어진 숫자와 가장 가까우면서 큰 정수를 구한다.
• 소수점을 다 지우고 +1 하면 된다.

FLOOR(숫자)

• 주어진 숫자와 가장 가까우면서 작은 정수를 구한다.
• 소수점을 다 지우면 된다.

POWER(숫자, 승)

• 숫자^승 값을 반환한다.

EXP(숫자)

• e^숫자 값을 반환한다.

LOG(밑, 진수)

• 로그를 구한다.

LN(진수)

• 밑이 e인 로그를 구한다.

SIN(라디안), COS(라디안), TAN라디안)

• 삼각함수를 구한다.

CHR(숫자)

• 숫자와 대응되는 아스키코드의 문자를 반환한다.

날짜 함수

SYSDATE

• 현재의 날짜와 시간을 출력하는 함수이다.

EXTRACT( [YEAR | MONTH | DAY | HOUR | MINUTE | SECOND] FROM 날짜 )

• 입력된 날짜에서 추출하고자 하는 정보를 반환하는 함수
• 변환 함수에서 설명할 TO_CHAR와 비슷하다.

ADD_MONTHS(날짜, 더할 숫자)

• 입력된 날짜에 숫자만큼의 달을 더하는 함수이다.

MONTHS_BETWEEN(날짜1, 날짜2)

• 두 날짜 사이의 개월 수를 출력하는 함수이다.
• 날짜 1이 더 커야지 결과 값은 양수로 표현된다.

변환 함수

TO_CHAR(숫자 또는 날짜, 형식)

• 숫자 또는 날짜의 값을 원하는 형식의 문자열로 변환하는 함수이다.
• 9는 자리를 차지한다는 의미이다.

  TO_CHAR(1234,'99999') -- ' 1234'
  TO_CHAR(1234,'9999.99') -- '1234.00'​

• 0은 해당 자리가 비어있다면 0으로 표현하라는 의미이다.

• TO_CHAR(1234,'09999') -- '01234'​​

• 날짜 함수인 EXTRACT(YEAR FROM SYSDATE)는 아래와 동일하다.

  TO_CHAR(SYSDATE, YYYY)​

TO_NUMBER(문자)

• 문자를 숫자로 변환하는 함수이다.
• 숫자로 변환할 수 없을 경우 에러가 발생한다.

TO_DATE(문자, 날짜 형식)

• 문자를 날짜로 변환하는 함수이다.
• 날짜 형식을 입력하지 않으면 입력받은 날짜 문자 형식대로 변환된다.

CAST(변환 대상 AS 데이터 타입)

• 변환 대상을 특정한 데이터 타입으로 변환하는 함수이다.

일반 함수

NVL(컬럼, 변환 값)

• 첫 번째 인자의 값이 NULL일 경우, 두 번째 값으로 표현한다.

NULLIF(표현식1, 표현식2)

• 첫 번째 인자와 두 번째 인자의 값이 같다면 NULL을 반환한다. 만약 다르다면 첫 번째 인자를 반환한다.

COALESCE(표현식1, 표현식2, ... , 표현식N)

• 입력받은 인자를 앞에서부터 확인하면서 NULL이 아니라면 해당 인자를 반환한다. 만약 모두 NULL이라면 NULL을 반환한다.

DECODE(표현식, 비교대상1, 반환값1, 비교대상2, 반환값2, ... , 비교대상N, 반환값N, 디폴트 값)

• 첫 번째 인자를 앞에서부터 비교대상과 비교하고 같다면 해당 반환 값을 반환한다. 만약 모든 비교대상과 다르다면 맨 마지막 인자인 디폴트 값을 반환한다.
• 디폴트 값은 입력하지 않으면 NULL이다.

CASE 표현식

      WHEN 비교대상(조건문)1 THEN 반환값1

      WHEN 비교대상(조건문)2 THEN 반환값2

                    ....

      WHEN 비교대상(조건문)N THEN 반환값N

      ELSE 디폴트값

END

• 표현식을 앞에서부터 비교대상과 비교하고 같다면 해당 반환 값을 반환한다. 만약 모든 비교대상과 다르다면 맨 마지막 인자인 디폴트 값을 반환한다.
• 비교 값만 입력할 경우 DECODE와 동일하게 동작한다.
• BETWEEN 10 AND 20과 같이 조건문을 입력할 수도 있다.

DDL

-- 테이블 생성 1 제약조건까지 한번에
CREATE TABLE EMP (
	NO VARCHAR2(100) NOT NULL
	,NAME VARCHAR2(100) DEFAULT '홍길동' NOT NULL
	,ADDR VARCHAR2(100) NULL
	,CONSTRAINT EMP_PK PRIMARY KEY (NO)
);

-- 테이블 생성 2 제약조건은 ALTER문으로 
CREATE TABLE EMP (
	NO VARCHAR2(100) NOT NULL
	,NAME VARCHAR2(100) DEFAULT '홍길동' NOT NULL
	,ADDR VARCHAR2(100) NULL
);
ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (NO);

-- 테이블 생성 3 PK를 칼럼 선언할때 한번에
CREATE TABLE EMP (
	NO VARCHAR2(100) PRIMARY KEY
	,NAME VARCHAR2(100) DEFAULT '홍길동' NOT NULL
	,ADDR VARCHAR2(100) NULL)
);

-- 다른 테이블에서 데이터까지 복사해서 만들기
CREATE TABLE EMP AS SELECT * FROM EMP2; -- 모든 컬럼
CREATE TABLE EMP AS SELECT NAME FROM EMP2; -- 특정 컬럼
CREATE TABLE EMP AS SELECT * FROM EMP2 WHERE 1=2; -- 데이터를 제외한 모든 컬럼

-- 참조관계 정의
-- 1. 부모
CREATE TABLE 부서 (부서번호 CHAR(10), 부서명 CHAR(10), PRIMARY KEY(부서번호));
-- 2. 자식 (CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION)
CREATE TABLE 직원 (직원번호, CHAR(10), 부서명 CHAR(10), PRIMARY KEY(직원번호),
FOREIGN KEY(부서명) REFERENCES 부서(부서명) ON DELETE CASCADE);

-- 인덱스 생성
CREATE INDEX IDX_EMP ON EMP (NAME);

-- 제약조건 생성
ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (NO);
ALTER TABLE EMP ADD CONSTRAINT EMP_CK CHECK(LENGTH(NAME) > 2);

-- 인덱스 생성

-- 칼럼 추가
ALTER TABLE EMP ADD (PHONE VARCHAR2(100));

-- 칼럼 명 변경
ALTER TABLE EMP RENAME COLUMN PHONE TO PHONE_NUM;

-- 칼럼 타입 변경
ALTER TABLE EMP MODIFY (PHONE_NUM VARCHAR2(200));

-- 칼럼 삭제
ALTER TABLE EMP DROP COLUMN PHONE_NUM; 


-- 테이블 전체 삭제
DROP TABLE EMP;

-- 테이블은 삭제하지 않음. 데이터 전체 삭제. 롤백 불가능, 자동 커밋, 데이터 저장공간 모두 Release
TRUNCATE FROM EMP;

-- 테이블 이름 변경
RENAME EMP TO EMP_AFTER;

DML

-- 데이터 삽입 : 전체 컬럼 데이터 입력시
INSERT INTO EMP VALUES ('1', '홍길동', '영등포');

-- 데이터 삽입 : 특정 컬럼 데이터 입력시
INSERT INTO EMP (NO, NAME) VALUES ('1', '홍길동');

-- 여러 행을 한번에 삽입
INSERT INTO EMP
	SELECT NO, NAME, ADDR FROM EMP2 WHERE ADDR = '영등포';

-- 데이터 수정 
UPDATE EMP SET ADDR = '강남' WHERE NAME = '홍길동';

-- 데이터 일부 삭제
DELETE FROM EMP WHERE NO = '1';

-- 데이터 전체 삭제. 롤백가능, 직접 커밋, 데이터 저장공간은 그대로 살아있음
DELETE FROM EMP;

-- 여러 테이블의 데이터를 한 테이블에 모으기
MERGE INTO 타겟테이블
	USING 소스테이블
	ON (타겟테이블.컬럼1 = 소스테이블.컬럼1)
	WHEN MATCHED THEN
		... -- ON절 조건이 성립한다면 해당 쿼리 수행
	WHEN NOT MATCHED THEN
		... -- ON절 조건이 성립하지 않는다면 해당 쿼리 수행

DCL

-- 계정 생성
CREATE USER 사용자 IDENTIFIED BY 패스워드;

-- 사용자 생성 권한 부여
GRANT CREATE USER TO 사용자;

-- 로그인 권한 부여
GRANT CREATE SESSION TO 사용자;

-- 테이블 생성 권한 부여
GRANT CREATE TABLE TO 사용자;

-- 다른 사용자에게 테이블 SELECT 권한 부여
GRANT SELECT ON 나의테이블 TO 상대방

-- 여러 개의 권한을 뭉친 하나의 ROLE 생성
CREATE ROLE NEW_ROLE;
GRANT CREATE SESSION, CREATE TABLE TO NEW_ROLE;

-- 생성한 ROLE을 사용자에게 부여
GRANT NEW_ROLE TO 사용자;


-- 릴레이션 권한
-- 1. 사용자1이 다른 유저에게 테이블에 대해서 권한을 줄 수 있도록 설정
GRANT SELECT, INSERT, DELETE ON 테이블명 TO 사용자1 WITH GRANT OPTION;
-- 2. 사용자1이 사용자2에게 권한 부여
GRANT SELECT, INSERT, DELETE ON R TO 사용자2;
-- 3. 사용자1의 테이블명의 DELETE 권한을 제거
REVOKE DELETE ON 테이블명 FROM 사용자1;
-- 4. 사용자1의 테이블명의 INSERT 권한 제거 및 사용자2도 자동으로 제거됨
REVOKE INSERT ON 테이블명 FROM 사용자1 CASCADE;