Group Functions

Single functions

문자 함수

LOWER: 전부 소문자로
UPPPER: 전부 대문자로
INITCAP: 맨 앞만 대문자로

CONCAT('Hello', 'World')=> HelloWorld
SUBSTR('HelloWorld',1,5)=> Hello
LENGTH('HelloWorld')=> 10 INSTR('HelloWorld', 'W')=> 6
LPAD(salary,10,'*')=> *****24000 =LPAD(' ',10,'*') 식으로도 가능 RPAD(salary, 10, '*')=> 24000*****
REPLACE('JACK and JUE','J','BL')=> BLACK and BLUE
TRIM('H' FROM 'HelloWorld')=> elloWorld =첫글자나 맨 뒤글자만 됨

숫자 함수
ROUND
TRUNC
MOD: 나머지값

날짜 함수
SYSDATE

※date+number = number를 일수로 계산함, 결과는 date
   date-number = 위와 마찬가지, 결과는 date
   date-date = 둘의 차이를 일수로 계산하여 보여줌, 결과는 일수
   date+number/24 = 일수/24이므로 시간임. 시간을 + 계산하여 결과로 date를 보여줌

MONTHS_BETWEEN('date1', 'date2'): 두 날짜사이의 달수를 계산함
ADD_MONTHS('date1', number): date1에서 number만큼의 달수를 더함
NEXT_DAY('date', 'monday'):date의 다음 월요일을 나타냄
LAST_DAY('date'):해당 달의 마지막날을 나타냄
ROUND(SYSDATE,'MONTH'):16일 이후는 반올림하여 다음달로 표시함.
ROUND(SYSDATE,'YEAR'):7월 1일 이후는 반올림하여 다음 해로 표시함.
TRUNC(SYSDATE,'MONTH'):그 달의 1일로 표시함.
TRUNC(SYSDATE,'YEAR'):해당 년도의 1월 1일로 표시함.

변환 함수
TO_CHAR(date,'[fm]format_model')
TO_CHAR(number, 'format_model')
TO_NUMBER(char[, 'format_model'])
TO_DATE(char[, '[fx]format_model'])

NULL 함수
NVL(expr1, expr2): expr1에 있는 NULL 값을 전부 expr2를 리턴, date, number, char, varchar2 다 가능, 주의! expr1과 expr2는 데이터타입이 같아야함. 틀리면 to_char, to_num으로 바꿔서 해야댐.
NVL2(expr1, expr2, expr3): expr1에 있는 값이 널이 아니면 expr2를, 널이면 expr3를 리턴
NULLIF(expr1, expr2): expr1과 expr2가 같으면 null을 리턴하고, 같지 않으면 expr1을 리턴
COALESCE(expr1, expr2, ...exprn): expr1이 null이 아니면 expr1을 리턴, null이면 expr2를 리턴,  expr1과 expr2가 null이면 expr3을 리턴)

IF-THEN-ELSE 식 함수
CASE expr WHEN comparison_expr1 THEN return1
                  [WHEN comparison_expr2 THEN return_expr2
                   WHEN comparison_exprn THEN return_exprn
                   ELSE else_expr]
END

예시)
SELECT last_name, job_id, salary,
  CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
  WHEN 'ST_CLERK' THEN 1.15*salary
  WHEN 'SA_REP' THEN 1.20*salary
  ELSE salary END AS "REVISED_SALARY"
FROM employees;

DECODE(col|expression, search1, result1
                 [, search2, result2,...,]
                 [, default])

예시)
SELECT last_name, job_id, salary,
  DECODE(job_id, 'IT_PROG', 1.10*salary,
  'ST_CLERK', 1.15*salary,
  'SA_REP', 1.20*salary,
  salary)
  AS REVISED_SALARY
FROM employees;

SQL 종류

더이상의 설명은 생략합니다.

ALTER 사용법(작성중)

1. 컬럼 추가
형식
ALTER TABLE table
ADD (column datatype [DEFAULT expr] [, column datatype]...);

예시
ALTER TABLE dep80
ADD (job_id VARCHAR2(9);

2. 컬럼 변경
형식
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr] [, column datatype]...);

예시
ALTER TABLE dep80
MODIFY (job_id VARCHAR2(20));

3. 컬럼 제거
형식
ALTER TABLE table
DROP COLUMN column;

예시
ALTER TABLE dep80
DROP COLUMN job_id;

TABLE 생성 및 관리(작성중)

테이블/열 이름 규칙
-문자로 시작
-1~30자까지 가능
-특수문자는 _, $, #만 가능

* CREATE TABLE
1. CREATE 형식: CREATE TABLE table (column datatype)
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr])

다른 유저의 테이블을 보고 싶을때 schema를 사용
schema란=특정 유저가 소유하고 있는 모든 객체들의 집합을 스키마라 하고 이름을 유저이름으로 함
남의 테이블을 참조할때는 반드시 스키마를 명시해야함
예) user hr이 scott의 emp테이블을 보고 싶을때
select * from scott.emp
다른 유저의 것을 볼때는 스키마 생략 불가

DEFAULT 옵션은 값을 입력하지 않을때 자동으로 들어가는 값
다른 컬럼의 이름, nextval, currval은 못쓴다.
예)
CREATE TABLE hire_dates
(id NUMBER(8),
hire_date DATE DEFAULT SYSDATE);

2. CREATE
CREATE TABLE emp
( emp_id number(6) constraint emp_empid_pk primary key,
f_name varchar2(20),
l_name varchar2(25) constraint emp_lname_nn not null,
email varchar2(25) constraint emp_email_nn not null
constraint emp_email_uk unique,
phone_number varchar2(20),
hire_date date constraint emp_hiredate_nn not null,
job_id varchar2(10) constraint emp_jobid_nn not null,
salary number(8,2) constraint emp_salary_ck check (salary>0),
commission_pct number(2,2),
manager_id number(6),
department_id number(4) constraint emp_depid_fk references departments (department_id));

TCL

Transaction Control Language

DML은 마지막에 TCL이 필요함
트랜잭션 길이가 짧고 갯수가 많은 상황: 은행=OLTP
트랜잭선 길이가 길고 갯수가 적은 상황: dw or batch

※ iSQLPLUS에서 autocommit on을 하면 절대 안된다.

아래 한장으로 대략 거의 모든 설명이 된다.

1. 명령어들
COMMIT
SAVE POINT name
ROLLBACK
ROLLBACK TO SAVEPOINT name

2. COMMIT, ROLLBACK이 자동으로 되는 상황
exit를 하면 명시적으로 commit이 발생됨(iSQLPLUS가 정상적으로 종료되어도)
장애 발생하면 자동 rollback 처리됨(iSQLPLUS 창을 닫으면 비정상 종료로 처리함)
DDL = auto commit
DCL = auto commit

3. PENDING STATE: DML을 치고 TCL을 아직 안친 상태
데이터를 이전으로 ROLLBACK 가능
SELECT로 DML 결과를 검토 가능
다른 사용자는 DML 전의 상태만 볼수 있음
DML 작업중인 행은 LOCK 상태이기에 다른 사용자는 변경 불가

4. Read Consistency
-데이터는 항상 commit된 데이터만 보여줘야 한다.
-데이터를 읽는 중(select)에는 다른 사용자가 쓸(insert,update,delete) 수 없다.
-데이터를 쓰는 중에는 다른 사용자가 쓰는 내용을 읽을 수 없다.

5. Read Consistency 구현
-쓰기: select, insert, delete 할 경우 이전 데이터의 복사본을 undo segment에 넣어두고 다른 사용자가 select를 실행시 undo segment 내용을 보여줌.
-커밋되면: undo segment가 지워짐.
-롤백되면: undo segment 내용을 data block으로 다시 이전됨.

DDL(작성중)

* TRUNCATE
구조를 남기고 모든 데이터를 다 삭제함.
DDL이기에 rollback 불가
=행이 많을 경우 delete보다 truncate를 쓰는게 속도적인 측면에서 월등히 좋다

1. 형식: TRUNCATE TABLE
TRUNCATE TABLE table_name;