21.02.01

[INDEX]

- index는 검색을 빠르게 하기 위한 수단이다.
- Primary Key와 Unique Key가 지정되면 따로 index가 생성된다
- 장점
    - 검색속도가 빨라진다.
    - 시스템의 부하를 줄여 성능이 향상된다.
- 단점
    - 인덱스 생성에 시간과 공간이 필요하다.
    - 삽입/갱신/삭제 가 빈번히 일어날 경우 성능 저하
- index가 필요한 경우
    - 데이터가 많을 경우
    - JOIN이 많이 사용된 경우
- index가 불필요한 경우
    - 데이터가 적을경우
    - 삽입/갱신/삭제 가 빈번히 일어날 경우 

-- 1) 비트맵 index
CREATE BITMAP INDEX emp_idx_depno ON employees(email);

-- 2) UNIQUE index
CREATE UNIQUE INDEX emp_idx_ename ON emp(ename);

-- 3) NON-UNIQUE index
CREATE INDEX emp_idx_ename ON emp(ename);

-- 4) 결합 인덱스
CREATE INDEX emp_combi ON emp(ename,job);

-- 5) 인덱스 데이터 사전(인덱스확인)
SELECT * FROM USER_INDEXES;
SELECT index_name, index_type, table_owner, uniqueness FROM USER_INDEXES;

-- 6) 인덱스 삭제
DROP INDEX EMP_IDX_ENAME;

1) 비트맵 index
- CREATE BITMAP INDEX [index 이름] ON [테이블명](컬럼명)
- 각 컬럼에 대해서 적은 종류의 독특한 값이 있을 경우 사용하는 것이 좋다.(남|여, true|false)
- 데이터가 어디있는지 BIT로 표기 해준다. 
- 컬럼이 추가되면 익덱스를 다시 만들어야한다.
- 테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우 사용
- bitmap index는 Standard Edition에서만 동작된다.

2) UNIQUE index
- CREATE UNIQUE INDEX [index 이름] ON [테이블명](컬럼명)
- PK & Unique key가 걸려있으면 자동으로 생성된다.
- 중복되지 않는 데이터를 인덱스 키값으로 지정한다.
- 인덱스를 지정할 컬럼에 중복된 값이 들어있으면 아래와 같은 오류 메세지
- 01452. 00000 - "cannot CREATE UNIQUE INDEX; duplicate keys found"

3) NON-UNIQUE index
- CREATE INDEX [index 이름] ON [테이블명](컬럼명)
- 컬럼에 중복을 허용하는 인덱스

4) 결합 인덱스
- CREATE UNIQUE INDEX [index 이름] ON [테이블명](컬럼명1,컬럼명2,...)
- 복수개의 컬럼을 합쳐서 하나의 인덱스를 만들 수 있다.(최대 16개)
- 중복허용을 하고 싶으면 UNIQUE를 제거한다.

[VIEW]

- 뷰는 기본적으로 읽기전용이다.
- 뷰는 하나의 가상테이블이라 생각하면 된다.
- DB의 특정공간을 사용하기 때문에 많이 사용하지 않으면 안만드는 것이 좋다.
- 특정 컬럼들만 뽑아서 보여지기 때문에 보안성은 좋다.
- 뷰는 복잡한 쿼리를 통해 얻을 수 있는 결과를 간단한 쿼리로 얻을 수 있게 한다.
- 한개의 뷰로 여러 테이블에 대한 데이터를 검색 할 수 있다.

1) 뷰의 제한 조건
- 테이블 데이터에 NULL이 있으면 안된다.(뷰에 포함 될 수 없다.)
- WITH READ ONLY 옵션을 설정한 뷰는 데이터를 갱신 할 수 없다.
- WITH CHECK OPTION을 설정한 뷰는 뷰의 조건에 해당되는 데이터만 수정가능
- 하지만 ROWID, ROWNUM, NEXTVAL, CURRVAL 등과 같은 가상 컬럼에 대한 참조를 포함하는 뷰는 
  어떠한 데이터도 INSERT 불가

-- 2) 뷰 생성
CREATE OR REPLACE VIEW vw_name_lmj AS 
    SELECT e.ename,d.depname FROM emp e, dept d WHERE e.depno=d.depno;

-- 3) WITH CHECK OPTION
CREATE OR REPLACE VIEW vw_checkopt AS 
    SELECT ename,job,depno FROM emp WHERE depno=1 WITH CHECK OPTION;
UPDATE vw_checkopt SET job='manager' WHERE ename='lee'; -- 업데이트가 된다
-- view를 구성한 조건이 depno=1인데 2로 바꾸려고 하니까 조건에 위배된다.
UPDATE vw_checkopt SET depno=2 WHERE ename='lee';
-- ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

-- 4) WITH READ ONLY : SELECT만 가능한 VIEW
CREATE OR REPLACE VIEW read_only AS
    SELECT ename, job, depno FROM emp WHERE depno=2 WITH READ ONLY;
SELECT * FROM read_only;

UPDATE read_only SET ename='ahn' WHERE job='staff';
-- SQL 오류: ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

-- 5) 생성된 뷰의 정보 확인
SELECT * FROM user_views;
SELECT VIEW_NAME, TEXT FROM user_views;

-- 6) 뷰 삭제
DROP VIEW VW_NAME_LMJ;

2) 뷰 생성
- CREATE [OR REPLACE] [FORCE|NONFORCE] VIEW [VIEW 이름] AS [sub-query] 
  WHERE 조건 [WITH CHECK OPTION | WITH READ ONLY]
- CREATE VIEW : 뷰를 다시만든다거나 뷰의 구조를 변경해야 할 경우 다 지우고 다시 만든다.
- OR REPLACE : 같은 이름의 뷰가 있을 경우 기존 뷰를 갱신한다.
- FORCE : 기본 테이블 유무에 상관없이 VIEW 생성 (안써주면 기존 테이블에서 값 가져옴)
- 뷰 생성시 `ORA-01031: 권한이 불충분합니다` 이러한 에러가 나면 SYS에서 권한부여 해줘야한다. 
  (GRANT CREATE VIEW TO C##web_user;)

3) WITH CHECK OPTION
- 뷰를 생성하는 조건식을 만족하는 컬럼은 UPDATE할 수 없다.(수정할 수 없다)

4) WITH READ ONLY
- SELECT만 가능한 VIEW

[SEQUENCE]

- 유일값(unique)을 생성해주는 오라클 객체이다.
- 시퀀스를 생성하면 절대로 중복되지 않는 증가 값을 얻게 된다.
- 보통 PK 생성을 위해서 사용된다.
- 테이블에 종속되지 않아 독립적으로 생성 저장된다.
- 메모리에 cache되었을때 액세스 효율이 증가된다.

-- 1) SEQUENCE 생성
CREATE SEQUENCE seq_empno;

-- 2) 해당 시퀀스 확인
SELECT seq_empno.NEXTVAL FROM DUAL; -- 현재보다 +1
SELECT seq_empno.CURRVAL FROM DUAL; 

1) SEQUENCE 생성
- CREATE SEQUENCE [시퀀스 name] / 일반적인 시퀀스, 아래내용은 옵션
- [START WITH n] [INCREMENT BY n]
- [MAXVALUE n | NOMAXVALUE] [MINVALUE n | NOMINVALUE]
- [CYCLE | NOCYCLE] [CACHE|NOCACHE]

2) 해당 시퀀스 확인
- NEXTVAL은 현재보다 +1
- CURRVAL은 현재이기 때문에 NEXTVAL실행 없이 최초로 사용할 경우 오류난다.

-- 3) [START WITH n] [INCREMENT BY n]
-- 8000부터 시작하고 1씩 증가하는 시퀀스
CREATE SEQUENCE seq_start START WITH 8000 INCREMENT BY 1 CACHE 20;

-- 4) MAX 옵션
CREATE SEQUENCE seq_max MAXVALUE 10;

-- 5) CYCLE 옵션
CREATE SEQUENCE seq_cycle MAXVALUE 10 CYCLE CACHE 5;

-- 6) MIN 옵션
-- 5로 시작해서 10까지 증가하고 다시 5로 돌아감
CREATE SEQUENCE seq_min START WITH 5 MINVALUE 5 MAXVALUE 10 CYCLE CACHE 5;

3) [START WITH n] [INCREMENT BY n]
- START WITH : 시퀀스의 시작값, n을 8000으로 지정하면 8000부터 순차 증가
- INCREMENT BY : 증가하는 수 (default :1)

4) MAX 옵션
- 시퀀스 최대값 지정
- 최대값 지정시 최대값을 넘어가면 오류 발생

5) CYCLE 옵션
- 최대값 도달시 순환여부
- CYCLE을 줄 경우 돌아갈 수를 미리 기억하도록 캐시설정이 들어가야한다.
- CACHE : 원하는 숫자 만큼 미리 만들어서 캐시에 상주 시킨다.

6) MIN 옵션
- 지정안하면 1, 시작값이 최소값보다 작으면 오류발생
- 단독적으로 쓰이는 것 보다는 MAX가 있고 CYCLE로 다시 돌아갈때의 MINVALUE로 최소값을 정해준다.

-- 7)실제 SEQUENCE 사용 예시
ALTER TABLE emp ADD (emp_no NUMBER(5));
INSERT INTO emp(emp_no,ename,job,depno,hiredate) 
    VALUES(seq_empno.NEXTVAL ,'tiger','manager',3,SYSDATE);
    
-- 8) 모든 시퀀스 확인
SELECT * FROM USER_SEQUENCES;

-- 9) 시퀀스 수정 : START WITH 값을 제외하고 ALTER SEQUENCE 명령어로 수정가능
-- ALTER SEQUENCE [시퀀스이름] ... 수정할 옵션
-- seq_empno 캐시 사이즈를 40으로 바꾸고 증가값을 10으로 변경..
ALTER SEQUENCE seq_empno INCREMENT BY 10 CACHE 40;

-- 10) 시퀀스 삭제
DROP SEQUENCE SEQ_MAX;

7) 실제 SEQUENCE 사용 예시
- 테이블에 데이터 삽입시에 시퀀스를 넣어서 해당 데이터를 유일값으로 생성해준다.

[Function]

1. 집계함수
-- 1) COUNT(COLUMN) : 검색된 행의 수
SELECT COUNT(depno) AS 팀수 FROM dept;

-- 2) MAX(COLUMN) : 컬럼의 값 중 최대값을 추출
SELECT MAX(commission) FROM employees;

-- 3) MIN(COLUMN) : 컬럼의 값 중 최소값을 추출
SELECT MIN(commission) FROM employees;

-- 4) AVG(COLUMN) : 컬럼의 값들의 평균을 추출
-- 전체 급여의 평균
SELECT AVG(salary) FROM employees;

-- 5) SUM(COLUMN) : 컬럼의 값들의 합계
-- 커미션이 10인 사람들의 급여 합계
SELECT SUM(salary) FROM employees WHERE commission=10;

1. 집계함수 : 여러행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수
- GROUP BY 시 노출되는 컬럼 -> 공통으로 묶여지는 컬럼, 집계함수를 사용하여 추출한 내용

2. 문자열(**CHAR) 타입을 다루는 함수
-- 1) CONCAT(char1, char2)
-- 문자열을 합치는 함수
SELECT CONCAT('www.','oracle')||'.com' AS url FROM DUAL;

-- 2) INITCAP(char),LOWER(char),UPPER(char)
-- UNION ALL을 넣으면 한꺼번에 볼 수 있다.
SELECT INITCAP('oracleclub') FROM DUAL UNION ALL -- 앞글자만 대문자로
SELECT LOWER('OracleClub') FROM DUAL UNION ALL -- 전체 소문자
SELECT UPPER('oracleclub') FROM DUAL;  -- 전체 대문자

-- 3) LPAD(char1,n,[char2]), RPAD(char1,n,[char2])
SELECT LPAD('oracleclub',12,'*') AS pass FROM DUAL;
SELECT RPAD('oracleclub',12,'*') AS pass FROM DUAL; 
SELECT RPAD('oracleclub',8,'*') AS pass FROM DUAL;

-- 4) SUBSTR(char,m,n), SUBSTRD(char,m,n)
SELECT SUBSTR('oracleclub',3) AS title FROM DUAL; -- 3번째부터 끝까지
SELECT SUBSTR('oracleclub',3,4) AS title FROM DUAL; -- 3번째부터 4개
SELECT SUBSTR('oracleclub',-3,2) AS title FROM DUAL; -- 뒤에서 3번째 부터 2개
SELECT SUBSTRB('oracleclub',2) AS title FROM DUAL; -- 문자는 기본 2byte
SELECT SUBSTRB('오라클클럽',2) AS title FROM DUAL;

1) CONCAT(char1, char2) :문자열을 합치는 함수
3) LPAD(char1,n,[char2]), RPAD(char1,n,[char2])
- LPAD : 왼쪽에 문자를 끼워 넣는 역할
- RPAD : 오른쪽에 문자를 끼워 넣는 역할
- n은 반환되는 문자열의 전체 길이, 
- char1의 문자열이 n보다 클 경우 char1을 n개의 문자열만큼만 반환(뒤부터 잘림)
- 특정한 규칙에 의해서 자릿수를 맞춰야 할 경우에도 사용
4) SUBSTR(char,m,n), SUBSTRD(char,m,n)
- SUBSTR : m번째 자리부터 길이가 n개인 문자열을 반환한다. n을 안쓰면 끝까지 보여줌
- m이 음수일 경우 뒤에서 m번째 문자열부터 n개의 문자열을 반환.
- SUBSTRB : B는 Byte를 의미한다. Byte단위로 처리하겠다.
- DB는 INDEX가 1부터 시작

-- 5) LENGTH(char), LENGTHB(char)
SELECT LENGTH('오라클클럽') AS title FROM DUAL UNION ALL
SELECT LENGTHB('오라클클럽') AS title FROM DUAL; -- 한글을 3바이트로 잡는다.

-- 6) REPLACE(char1, src,dst)
SELECT REPLACE('oracleclub','oracle','DB') AS title FROM DUAL;
SELECT REPLACE('OracleClub','oracle','DB') AS title FROM DUAL;
SELECT REPLACE('OracleClub','Oracle','DB') AS title FROM DUAL;

-- 7) INSTR(char1,str1,m,n)
SELECT INSTR('CORPORATE FLOOR','OK') AS idx FROM DUAL; -- 없으니까 0을반환
SELECT INSTR('CORPORATE FLOOR','OR') AS idx FROM DUAL; -- 2를 반환 (2번에서 찾아서)
-- 3번인덱스부터 찾아서 5반환
SELECT INSTR('CORPORATE FLOOR','OR',3) AS idx FROM DUAL; 
 -- 3번 인덱스부터 찾기 시작해서 두번째 발견 되는놈
SELECT INSTR('CORPORATE FLOOR','OR',3,2) AS idx FROM DUAL;

-- 8) TRIM(char1 FROM char2)
SELECT TRIM('o' FROM 'oracleclub') AS title FROM DUAL; -- o 지움
SELECT TRIM(' oracleclub ') AS title FROM DUAL; -- 양쪽 공백 지움

-- 9) LTRIM(char1,char2)
SELECT LTRIM('oracleclub','oracle') AS title FROM DUAL;
SELECT LTRIM(' oracleclub ') AS title FROM DUAL; --왼쪽에 있는 공백만 지움

-- 10) RTRIM(char1,char2)
SELECT RTRIM('oracleclub','club') AS title FROM DUAL;
SELECT RTRIM(' oracleclub ') AS title FROM DUAL; --오른쪽에 있는 공백만 지움

5) LENGTH(char), LENGTHB(char) : 길이 반환

6) REPLACE(char1, src,dst)
- 문자열 char1에서 src를 dst로 바꿈
- 대소문자 구분한다.

7) INSTR(char1,str1,m,n)
- 문자열이 포함되어 잇는지 조사하여 위치를 반환
- 지정된 문자열이 없으면 0을 반환
- char1: 대상 문자열 , str1 : 찾을 문자열
- m : 시작위치 , n : 시작위치에서 n번째 발견되는 놈

8) TRIM(char1 FROM char2)
- 특정한 문자열을 제거 하는데 사용한다.
- 만약에 제거할 문자를 명시하지 않으면 공백이 제거된다.

9) LTRIM(char1,char2) : 지정한 문자열이 대상문자열을 왼쪽부터 제거 (반복되는 문자도 제거)

10) RTRIM(char1,char2) : 지정한 문자열이 대상문자열을 오른쪽부터 제거 (반복되는 문자도 제거)

3. DateTime Functions(시간과 날짜를 다루는 함수)
-- 날짜 함수는 날짜를 지정하거나 계산할 때 사용

-- 1) SYSDATE : 현재 일자와 시간(시스템기준)을 얻을 수 있다.
-- to_char() : 괄호안의 내용을 문자열로 보여줌
SELECT to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS 현재시간 FROM DUAL;
-- 연산을 통해서 날짜를 지정 할 수 있다.
-- -[뺄숫자(일)]/[시간(24시간기준)]/[분]/[초]
-- 1일전
SELECT to_char(SYSDATE-1,'YYYY-MM-DD HH24:MI:SS') AS 하루전시간 FROM DUAL;
-- 1시간 전(24시간중 1일 빼라)
-- 가상 컬럼명 맨 앞에 숫자가 들어간 경우 더블쿼터로 감싸줘야 한다.
SELECT to_char(SYSDATE-1/24,'YYYY-MM-DD HH24:MI:SS') AS "1시간전" FROM DUAL;
-- 1분전(24시간중에서 60분중에 1을 빼라)
SELECT to_char(SYSDATE-1/24/60,'YYYY-MM-DD HH24:MI:SS') AS "1분전" FROM DUAL;
-- 5시간30분10초전
SELECT to_char(SYSDATE-(5/24 + 30/24/60 + 10/24/60/60),'YYYY-MM-DD HH24:MI:SS') 
	AS "5시간30분10초전" FROM DUAL;

-- 2) SYSTIMESTAMP : 현재 일자와 시간(시스템기준)을 얻을 수 있다.(10억분의 1초 단위)
-- 초단위보다 빠른 시간을 표기한다면 날짜 계산이 안된다.
SELECT to_char(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF9') AS 현재시간 FROM DUAL;
-- 1시간전
SELECT to_char(SYSTIMESTAMP-1/24,'YYYY-MM-DD HH24:MI:SS') AS "1시간전" FROM DUAL;
-- 1분전
SELECT to_char(SYSTIMESTAMP-1/24/60,'YYYY-MM-DD HH24:MI:SS') AS "1분전" FROM DUAL;

-- 3) ADD_MONTHS(a,b) : a의 날짜에 b의 달을 더한 값을 반환한다.
-- 오늘 날짜에 세달을 추가(키워드를 컬럼명으로 사용할 시 더블쿼터로 감싸준다)
SELECT ADD_MONTHS(SYSDATE,3) AS "3달후" FROM DUAL;
SELECT ADD_MONTHS(SYSTIMESTAMP,3) AS "3달후" FROM DUAL;

-- 4) MONTHS_BETWEEN(m1,m2) : m1과 m2의 사이의 달의 수를 NUMBER형으로 반환
SELECT MONTHS_BETWEEN(TO_DATE('2021-05-10', 'YYYY-MM-DD'), 
	TO_DATE('2021-02-03','YYYY-MM-DD')) AS "date" FROM DUAL;
-- 두 날짜 사이의 일의 수
SELECT TO_DATE('2021-05-10', 'YYYY-MM-DD') - TO_DATE('2021-02-03','YYYY-MM-DD')
	AS "DAY" FROM DUAL;

-- 5) LAST_DAY(m) : 해당 달의 마지막 날짜를 반환
SELECT LAST_DAY(SYSDATE) AS lasyday FROM DUAL;

-- 6) NEXT_DAY(d1,d2) : 돌아오는 명시된 요일의 일자를 반환
-- 요일은 1(일요일) ~ 7(토요일)
SELECT NEXT_DAY(SYSDATE,1) "NEXT WENDSEDAY" FROM DUAL;
-- 3월 둘째주 월요일
SELECT NEXT_DAY(TO_DATE('2021/03/01','YYYY/MM/DD'),2) "3월 둘째주 월요일" FROM DUAL;

4. numberic 함수
-- 1)ABS(n) : 절대값
SELECT ABS(-1.234) AS absolute FROM DUAL;
-- 소수점
-- 2) CEIL(n) : 올림
SELECT CEIL(10.1234) FROM DUAL; -- 11
SELECT CEIL(-10.1234) FROM DUAL; -- -10

-- 3) FLOOR(n) : 내림
SELECT FLOOR(10.1234) FROM DUAL; -- 10
SELECT FLOOR(-10.1234) FROM DUAL; -- -11

-- 4) ROUND(n,[m]) : 반올림(m : 반올림의 자릿수)
SELECT ROUND(15.193,1) FROM DUAL;
SELECT ROUND(15.193) FROM DUAL; -- 소수점 없애기, m을 0으로 처리해줘도 된다.

-- 5) TRUNC(n,m) : n의 값을 m의 소수점자리까지 잘라낸다
SELECT TRUNC(789.5597,2) FROM DUAL; -- 789.55
SELECT TRUNC(789.5597,-2) FROM DUAL; -- 700

-- 6) MOD(m,n) : m을 n으로 나눈 나머지의 값
SELECT MOD(9,4) FROM DUAL;