/*************************************************************
오라클 SQL & SQL*PLUS & PL/SQL Syntax 정리
**************************************************************/
----------------------------------------------------
- * syntax 기호 -
- -
- [] : 대괄호 안의 내용이 생략가능 -
- | : 또는 -
- {} : 중괄호 안의 내용이 하나 이상 올 수 있다. -
- ... : 앞 표기와 같은 형식으로 더 올 수 있다. -
- , : 구분자 -
----------------------------------------------------
/***********************************************************************
SQL, SQL*PLUS
************************************************************************/
* SELECT
SELECT [DISTINCT] {*, COLUMN [ALIAS], . . .}
FROM TABLE_NAME
[WHERE CONDITION]
[ORDER BY {COLUMN, EXPRESSION} [ASC|DESC]];
* 비교연산자
= : 같다.
> : 보다 크다.
>= : 보다 크거나 같다.
< : 보다 작다.
<= : 보다 작거나 같다.
<>, !=, ^= : 같지 않다.
NOT Column_name = : 같지 않다.
NOT Column_name > : 보다 크지 않다.
* SQL연산자
between a and b : a와 b 사이에 있다. (a, b값 포함)
in (list) : list의 값 중 어느 하나와 일치한다.
like : 문자형태와 일치한다. (%, _ 사용)
%는 값이 없거나 하나이상, _는 하나의 문자
in null : null 값을 가졌다.
not between a and b : a와 b사이에 있지 않다. (a, b값 포함하지 않음)
not in (list) : list의 값과 일치하지 않는다.
not like : 문자 형태와 일치하지 않는다.
not is null : null 값을 갖지 않는다.
* 논리연산자
and : 양쪽 컴포넌트의 조건이 true이면 true를 리턴
or : 한쪽 컴포넌트의 조건만이 true이면 true를 리턴
not : 이후의 조건이 false이면 true를 리턴
* 우선순위 규칙
1) 괄호
2) 수치연산자 > 결합연산자 > 비교연산자
3) is[not] null, like, [not] in > [not] between
4) not > and > or
* 문자형 함수
1) 변환함수
lower : 알파벳값을 소문자로 변환
syntax -> lower(column|expression)
upper : 알파벳 값을 대문자로 변환
syntax -> upper(column|expression)
initcap : 첫번째 글자만 대문자로 변환
syntax -> initcap(column|expression)
2) 문자조작함수
concat : 두 문자열을 연결(합성)
syntax -> concat(column1|expression1, column2|expression2)
substr : 문자열 중 특정 문자 또는 문자열의 일부분을 선택
syntax -> substr(column|expression,m,[,n])
length : 문자열의 길이를 구함
syntax -> length(column|expression)
instr : 명명된 문자의 위치를 구함
syntax -> instr(column|expression,m[,n])
lpad : 왼쪽 문자자리 채움
syntax -> lpad(column|expression,n,'string')
rpad : 오른쪽 문자자리 채움
syntax -> rpad(column|expression,n,'string')
ltrim : 왼쪽 문자를 지움
syntax -> ltrim(column1|expression1, 'string')
rtrim : 오른쪽 문자를 지움
syntax -> rtrim(column1|expression1, 'string')
translate : 특정 문자열을 대체
syntax -> translate(column1|expression1, 'string1', 'string2')
replace : 특정 문자열을 대신
syntax -> replace(column1|expression1, 'string1', 'string2')
* 숫자형 함수
round : 숫자를 반올림
syntax -> round(column1|expression1, n)
trunc : 숫자를 절삭
syntax -> trunc(column1|expression1, n)
mod : 나머지를 구함
syntax -> mod(column1|expression1, n)
power : 거듭제곱
syntax -> power(column1|expression1, n)
sqrt : 제곱근
syntax -> sqrt(column1|expression, n)
sign : 양수, 음수, 0인지를 구분
syntax -> sign(column1|expression1)
chr : ascii값에 해당하는 문자를 구함
syntax -> chr(column1|expression1)
* 날짜연산
date + number : 결과는 date : 일수를 날짜에 더함
date - number : 결과는 date : 날짜에서 일수를 뺌
date - date : 결과는 일수(숫자) : 어떤 날짜에서 다른 날짜를 뺌
date + number/24 : 결과는 date : 시간을 날짜에 더함
* 날짜함수
months_between : 두 날짜사이의 월수를 계산
syntax -> months_between(date1, date2)
add_months : 월을 날짜에 더함
syntax -> add_months(date1, n)
next_day : 명시된 날짜로부터 다음 요일에 대한 날짜를 나타냄
syntax -> next_day(date1, 'string'|n)
last_day : 월의 마지막 날을 계산
syntax -> last_day(date1)
round : 날짜를 반올림
syntax -> round(date1 [,fmt]) -> fmt는 'month', 'year'
trunc : 날짜를 절삭
syntax -> trunc(date1 [,fmt])
* 암시적 형변환 (Oracle서버가 자동형변환)
varchar2 or char -> number
varchar2 or char -> date
number -> varchar2
date -> varchar2
* 변환함수
to_char : 숫자나 문자값을 지정한 형식의 varchar2문자열로 변환
syntax1 -> to_char(date, 'fmt') : 날짜를 문자로
syntax2 -> to_char(number, 'fmt') : 숫자값을 문자로
to_number : 숫자를 포함하는 문자열을 숫자로 변환
syntax -> to_number(char) : 숫자를 포함하는 문자열을 숫자로 변환
to_date : 날짜를 나타내는 문자열을 명시된 날짜로 변환
syntax -> to_date(char [,'fmt']) : 날짜를 나타내는 문자열을 명시된 날짜로 변환
* 날짜 형식
scc or cc : 세기(BC날짜에는 -를 붙임)
years indates yyyy of syyyy : 년(BC날짜에는 -를 붙임)
yyy or yy or y : 년의 마지막 3, 2 또는 1자리 수
y,yyy : 콤마가 있는 년
|yyy,|yy,|y,| : ISO표준에 바탕을 둔 4, 3, 2또는 1자리 수
syser or year : 문자고 표현된 년(BC날짜에는 _S를 붙임)
bc or ad : bc/ad 지시자
b.c or a.d : .이 있는 bc/ad지시자
q : 년의 4분의 1
mm : 두자리 값의 월
month : 9자리를 위해 공백을 추가한 월이름
mon : 세자리의 약어로 된 월이름
rm : 로마숫자 월
ww or w : 년이나 월의 주
ddd or dd or d : 년, 월 또는 주의 일
day : 9자리를 위해 공백을 추가한 요일 이름
dy : 세자리 약어로 된 요일 이름
j : Jilian day (bc4713년 12월 31일 이후의 요일 수)
* 시간형식
am or pm : 정오 지시자
a.m or p.m : .이 있는 정오 지시자
hh or hh12 or hh24 : 하루 중 시간(1-12, 0-23)
mi : 분(0-59)
ss : 초(0-59)
sssss : 자정 이후의 초(0-86399)
/ . , : 사용 문자가 결과에 다시 나타남
"문자" : 인용부호내의 문자(들)가 결과에 출력
* 숫자에 영향을 주는 접미사
th : 서수(ddth -> 4th)
sp : 명시한 수(ddsp -> four)
spth or thsp : 명시한 서수(ddspth -> fourth)
* 숫자형식
9 : 9의 수는 출력폭을 지정
0 : 맨 앞에 0을 출력
$ : $ 기호
L : 지역 화폐기호
. : 명시한 위치에 소수점
, : 명시한 위치에 콤마
mi : 우측에 마이너스 기호(음수 값)
pr : 음수를 ()로 묶음
eeee : 과학적인 부호 표기
v : 10을 n번 곱함
b : 0을 0아닌 공백으로 출력
* 기타함수
case, decode : case나 if-then-else-end if 문장의 조건적 조회
1) case
syntax -> case expr when 조건 then 반환값
[when 조건 then 반환값]
...
else 반환값
end
2) decode
syntax -> decode(col|expr, search1, result1[,search2, result2, ...][,default])
* 중첩함수 : 여러 단일행함수의 중첩
syntax -> f3(f2(f1()))
* 그룹함수
avg(distinct|all|n) : null값을 제외한 n개 행의 평균값
count(distinct|all|expr|*) : null이 아닌 행의 개수
max(distinct|all|expr) : 최대값
min(distinct|all|expr) : 최소값
stddev(distinct|all|n) : null값을 제외한 n의 표준편차
sum(distinct|all|n) : null값을 제외한 n의 합계
variance(distinct|all|n) : null값을 제외한 n의 분산
* 그룹함수
syntax : select group_function(column) [,group_function(column)...]
from table_name
[where condition]
[ order by column]
* 그룹핑
syntax : select group_function(column) [,group_function(column)...]
from table_name
[where condition]
[group by group_by_expression]
[having group_condition]
[ order by column]
* 조인
1) cartesian join : 모든 가능한 행들의 조인 (결과가 의미없는 경우가 대부분)
2) equi join(inner join) : 조인 조건이 정확히 일치하는 경우 사용 (일반적으로 PK, FK사용)
syntax : select table1.column1 [,table2.column2, ...]
from table1, table2
where table1.column1 = table2.column2;
3) non-equi join : 조인 조건이 정확히 일치하지 않는 경우에 사용
4) outer join : 조인 조건이 정확히 일치하지 않는 경우에도 모든 행들을 출력
조인시킬 값이 없는 쪽에 (+)연산자 적용
syntax : select table1.column1 [,table2.column2, ...]
from table1, table2
where table1.column1 = table2.column2(+);
5) self join : 하나의 테이블에서 행들을 조인하고자 할 경우에 사용
* SET연산자
syntax : select *|column1[,column2, column3, ...]
from table1
...
set operator
select *|column1[,column2, column3, ...]
from table2
...
[order by column|expression];
1) union : 각 결과의 합 (합집합:중복되는 값은 한번만 출력)
2) union all : 각 결과의 합 (합집합:중복되는 값 그대로 출력)
3) intersect : 각 결과의 중복되는 부분만 출력 (교집합)
4) minus : 첫번째 결과에서 두번째 결과를 뺌 (차집합)
* 서브쿼리
syntax : select select_list
from table
where expression
operator
(select select_list
from table
where expression)
* 서브쿼리에서의 연산자(operator)
1) in : 2개 이상의 값을 리턴하는 서브쿼리에 대해 비교연산자를
기술하면 에러가 발생. 이런 경우 서브쿼리에서 리턴된 목록의
각각과 비교하여 쿼리를 수행하는 연산자
2) any : 서브쿼리에서 리턴된 목록의 각각의 값과 비교
3) all : 서브쿼리에서 리턴된 목록의 모든 값과 비교
4) exists: 서브쿼리에서 적어도 1개의 행을 리턴하면 논리식은 참
* 다중열 서브쿼리
syntax : select *|column1[,column2, ...]
from table
where (column1, column2 ...)
in
(select column1, column2, ...
from table
where condition);
* SQL*PLUS 명령어
a[ppend] text : 현재 편집라인의 끝에 text를 추가
c[hange]/old/new:현재 편집 라인의 old문자를 new문자로 바꿈
del [n] : n라인을 삭제
i[nput] [text] : 현재 편집 라인 다음에 라인을 추가하여 text를 추가
l[ist] [n] : SQL문장을 보여주고, 편집 라인을 이동
n text : n번재 라인을 text로 바꿈
r[un] : buffer에 있는 명령어를 실행한다.(/와 동일)
edit [filename[.ext]] : 지정된 파일의 내용이나 버퍼의 내용을 운영체제의
문자편집기로 불러온다.
sav[e] [filename[.ext]] [rep[lace] | app[end]]
: SQL버퍼의 내용을 파일에 저장하고 기존 파일에 추가하기 위해서 append를,
기존 파일에 중복해서 쓰려면 replace를 사용한다. 기본적인 파일 확장자는 sql이다.
sta[rt] [filename[.ext]] : 지정된 파일을 수행한다. start라는 명열 대신에 @를
사용할 수 있다. 파일 확장자가 .sql이 아니면 파일 확장자를 명시
get [filename[.ext]] : SQL버퍼에 파일의 내용을 기록한다. 파일명의 기본적인 확장자는
.lis또는 .lst이다
spo[ol] [filename[.ext]] [off | out] : SQL*PLUS의 내용을 파일에 저장
host : sql*plus안에서 호스트 운영체제의 명령어를 실행한다.
! : 운영체제 shell로 나들이
!vi file_name.sql : file_name.sql을 vi편집기로 부름 (unix)
* set : sql*plus의 환경설정
syntax : set 시스템변수 값
- 앞 숫자는 기본값
array[size] {20|n} : 데이터베이스 데이터 패치의 크기를 설정
colsep { | text} : 열 사이에 출력되는 문자를 설정 (디폴트:공백하나)
feed[back] {6|off|on} : 질의가 최소한 n개이어야 row의 수를 출력
hea[ding] {off|on} : 열의 heading을 출력할지의 여부 결정
lin[esize] {80|n} : 라인당 문자의 수
long {80|n} : long값을 출력하기 위해 최대 폭을 설정
pages[ize] {24|n} : page당 line수를 지정
pau[se] {off|on|text} : 화면제어를 함
term[out] {off|on} : 결과를 화면에 출력할지의 여부를 결정
col[umn] [column_option] : 열 포맷을 제어
syntax : col[umn] [{column|alias} [option]]
option : cle[ar] : 어떤 열의 형식을 해제
for[mat] format : 열 데이터의 디스플레이를 변경
hea[ding] text : 열 헤딩을 설정, 수직 바(|)는 헤딩 라인을 한줄 띄움
jus[tify] [align] : 열 heading을 정렬(좌, 우, 중간)
nopri[nt] : 열을 숨김
nul[l] text : null일때 디스플레이할 텍스트 명시
pri[nt] : 열을 보여줌
tru[ncated] : 디스플레이 되는 첫번째 라인의 마지막 문자열을 절삭
wra[pped] : 문자열이 끝나면 다음 라인으로 이동
tti[tle] [text|off|on] : 리포트의 머리말을 명시
bti[tle] [text|off|on] : 리포트의 꼬리말을 명시
bre[ak] [on report_element] : 중복값을 제거하고 라인 피드로 행들을 단락지음
-> 해제시 clear break
syntax : break on column[|alias|row] [skip n|dup|page] on ... [on report]
page : break값이 변경될 대 새로운 page로 skip
skip n : break값이 변경될 때 n만큼 줄을 skip(column, row, page, report)
duplicate : 중복되는 값을 출력
compute : SQL*PLUS명령어를 이용하여 요약된 계산을 한다.
해제시 -> clear compute
syntax : compute function of compute_column on break_column
function : count, num, max, min, sum, avg, std, var중 하나
compute_column : 계산에 사용되는 column이나 식
break_column : break명령으로 기술된 column
* 치환변수
& : 리턴되는 데이터를 동적으로 제한, 변수를 인식
* 사용자 변수 정의
1) define variable = value : char데이터형 사용자 변수를 생성하고 값을 할당
2) define variable : 변수, 변수 값, 변수 데이터형을 출력
3) define : 값과 데이터형을 가진 모든 데이터형을 출력
4) accept : 사용자 입력 라인을 읽고 그것을 변수에 저장
syntax : accept variable [datatype] [for[mat] format] [prompt text] [hide]
variable : 값을 저장하는 변수의 이름
존재하지 않으면 SQL*PLUS가 그것을 생성하여 사용
datatype : number, char 또는 date, char는 최대 길이 240바이트
date는 형식 모델을 다시 검사하고 데이터형은 char
format : 형식 모델을 명시(예:a10, 9,999)
text : 사용자가 값을 입력하기 전에 값을 출력
hide : 사용자 입력을 숨긴다.(예:패스워드)
* 오라클 객체
1) table : 행과 열로 구성된 기본적인 저장 구조
2) view : 하나 이상의 테이블에서 데이터의 부분집합을 논리적으로 표현
3) sequence : 고유한 번호를 자동으로 발생시키는 객체로 주로 PK값 생성에 사용
4) index : 질의(select) 성능을 향상시키기 위하여 사용하는 물리적인 저장 구조
5) synonym : 객체에 대한 이름을 부여
* 테이블 생성
syntax : create table [schema.]table_name
(column datatype [default expr] [column_constraint],
...
[table_constraint]);
* 오라클 data type
varchar2(n) : 가변길이 문자 데이터 (1~4000byte)
char(n) : 고정 길이 문자 데이터 (1~2000byte)
number(p, s) : 전체 p자리 중 소수점 이하 s자리 (p:1~38, s:-84~127)
date : 7byte(bc4712년 1월1일부터 ad9999년 12월 31일)
long :가변길이 문자 데이터 (1~2Gbyte)
clob : 단일 바이트 가변 길이 문자 데이터 (1~4Gbyte)
raw(n) : n byte의 원시 이진 데이터 (1~2000)
long raw : 가변 길이 원시 이진 데이터 (1~2Gbyte)
blob : 가변 길이 이진 데이터 (1~4Gbyte)
bfile : 가변 길이 외부 파일에 저장된 이진 데이터 (1~4Gbyte)
* constraints (제약)
- primary key(pk) : 유일하게 테이블의 각행을 식별 (not null과 unique 동시 만족)
- foreign key(fk) : 열과 참조된 열 사이의 외래키 관계를 적용하고 설정합니다.
- unique key(uk) : 테이블의 모든 행을 유일하게 하는 값을 가진 열 (null을 허용)
- not null(nn) : 열은 null값을 포함할 수 없습니다.
- check(ck) : 참이어야 하는 조건을 지정함(대부분 업무 규칙을 설정)
1) column level
syntax : column datatype [constraint constraint_name] constraint_type
2) table level
syntax : column datatype,
...
[constraint constraint_name] unique(column1[,column2...])
* subquery를 사용한 테이블 생성
syntax : create table table_name [column1[,column2...]] as subquery
* 테이블 수정 (add 열추가, modify 열수정, drop 열삭제)
syntax : alter table table_name
add (column datatype [default expr]
[,column datatype [default expr]...]
* 제약조건 수정 (add 제약추가, drop 제약삭제) : 제약변경은 못함
syntax : alter table table_name
add [constraint constraint_name] constraint_type (column);
- add자리에 disable, enable로 제약조건활성화 결정할 수 있다.
* 객체 이름 변경
syntax : rename old_name to new_name
* truncate table (테이블 비움:롤백불가)
syntax : truncate table table_name
* 테이블에 주석문 추가
syntax : comment on table table_name | column table.column is 'text';
* 테이블 삭제
syntax : drop table table_name
* DML
insert : 테이블에 새로운 행 추가
syntax : insert into table_name [(column1[, column2, ...])]
values (value1[, value2 ...]);
update : 테이블의 행 내용을 변경
syntax : update table_name
set column1 = value1 [,column2 = value2, ...]
[where condition];
delete : 테이블의 행 삭제
syntax : delete [from] table_name
[where condition];
merge : 행이 존재하면 update, 새로운 행이면 insert
merge into table_name as table_alias
using (table/view/sub_query) as alias
on (join condition)
when matched then
update set
col1 = col1_val1, col2 = col2.val2
when not matched then
insert (column_list) values (column_values);
commit : 저장되지 않은 모든 변경 사항을 Database에 저장
syntax : commit;
savepoint : savepoint 설정
syntax : savepoint name;
rollback : 저장되지 않은 모든 변경 사항을 취소
syntax : rollback [to savepoint name]
* sequence (nextval, currval 사용)
syntax : create sequence sequence_name
[increment by n]
[start witn n]
[{maxvalue n | nomaxvalue}]
[{minvalue n | nominvalue}]
[{cycle | nocycle}]
[{cache | nocache}]
- sequence_name : sequence의 이름입니다.
- increment by n : 정수 값인n으로 sequence번호 사시의 간격을 지정.
이 절이 생략되면 sequence는 1씩 증가.
- start with n : 생성하기 위해 첫번째 sequence를 지정.
이 절이 생략되면 sequence는 1로 시작.
- maxvalue n : sequence를 생성할 수 있는 최대 값을 지정.
- nomaxvalue : 오름차순용 10^27 최대값과 내림차순용 -1의 최소값을 지정.
- minvalue n : 최소 sequence를 지정.
- nominvalue : 오름차순용 1과 내림차순용 -(10^26)의 최소값을 지정
- cycle | nocycle : 최대 도는 최소갑에 도달한 후에 계속 값을 생성할지의
여부를 지정, nocycle이 디폴트.
- cache | nocache : 얼마나 많은 값이 메모리에 오라클 서버가 미리 할당하고
유지하는가를 지정. 디폴트로 오라클 서버는 20을 cache
* sequence 수정
syntax : alter sequence sequence_name
... (생성과 같다)
* sequence 제거
syntax : drop sequence sequence_name
* view 생성 (simple view)
syntax : create [or replace] [force|noforce] view view_name [(alias[,alias, ...])]
as subquery
[with check option [constraint constraint_name]]
[with read only]
- or replace : 이미 존재한다면 다시 생성한다.
- force : base table유무에 관계없이 view를 만든다.
- noforce : 기본 테이블이 존재할 경우에만 view를 생성한다.
- view_name : view의 이름
- alias : subquery를 통해 선택된 값에 대한 컬럼명이 된다.
- subquery : select 문장을 기술한다.
- with check option : view에 의해 엑세스 될 수 있는 행만이 입력, 갱신될 수 있다.
- constraint : check option 제약 조건에 대해 지정된 이름이다.
- with read only : 이 view에서 DML이 수행될 수 없도록 한다.
* view 생성 (complex view)
syntax : create [or replace] [force|noforce] view view_name [(alias[,alias, ...])]
as subquery
[with check option [constraint constraint_name]]
[with read only]
- complex view에서는 둘이상의 테이블이 조인되어 view에 나타난다.
* view 제거
syntax : drop view view_name;
* inline view : from절에 subquery를 사용
* top-n analysis
syntax : select [column_list], rownum
from (select [column_list] from table order by top-n_column)
where rownum <= n;
* index 종류
- unique index : 지정된 열의 값이 고유함을 보장
- non-unique index : 데이터를 검색할 때 가장 빠른 결과를 보장
- single column index : 하나의 열만 인덱스에 존재
- composite index : 여러 열을 결합하여 하나의 인덱스를 생성(16개의 열까지)
* index 생성 (사용자정의)
syntax : create index index_name
on table_name (column1[,column2, ...]);
* index 제거
syntax : drop index index_name;
* synonym 생성
syntax : create [public] synonym synonym_name
for object_name;
* synonym 제거
syntax : drop [public] synonym synonym_name;
* user 생성
syntax : create user user_name
idntified by password;
* 권한부여
syntax : grant system_privilege1[,system_privilege2, ...]
to user_name1[,user_name2, ...]
[with admin option]
- with admin option을 주면 부여받은 권한을 다시 부여할 수 있다.
* 권한제거
syntax : revoke system_privilege1[,system_privilege2, ...] | role1[,role2, ...]
from {user1[,user2, ...] | role1[,role2 ...] | public};
* Role 생성 : Role은 권한(privilege의 모임)
syntax : create role role_name;
* Role 부여
syntax : grant role_name to user_name;
/***********************************************************************
PL/SQL
************************************************************************/
* 기본구조
declare : 실행부에서 참조할 모든 변수, 상수, 커서, exception을 선언
variables, cursor, user_defined, exception
begin : 데이터베이스의 데이터를 처리할 sql문과 pl/sql블록을 기술
sql, pl/sql statements;
exception : 실행부에서 에러가 발생했을때 수행될 문장을 기술
actions to perform wher erros occur
end;
* anonymous
syntax : [declare]
begin
statements;
statements;
statements;
[exception]
end;
* procedure
syntax : create procedure procedure_name
is
begin
statements;
statements;
[exception]
end;
* function
syntax : create function function_name
return datatype
is
begin
statements;
statements;
return value;
[exception]
end;
* Data Dictionary 조회
select * from system_privilege_map;
select * from table_privilege_map;
select * from role_sys_privs;
select * from user_free_space;
- 현재 세션을 이루고 있는 사용자가 소유하고 있는 테이블
select table_name, tablespace_name from user_tables;
- 현재 세션을 이루고 있는 사용자가 소유한 모든 객체
select object_name, object_type, timestamp from user_objects;
- 현재 세션을 이루고 있는 사용자가 소유한 테이블, 뷰, 동의어, 시퀀스 조회
select * from user_catalog;
- 현재 세션을 이루고 있는 사용자가 소유하고 있는 시퀀스 조회
select sequence_name, min_value, max_value, increment_by, last_number
from user_sequences;
- 현재 세션을 이루고 있는 사용자가 소유한 view를 조회하시오.
select * from user_views;
- object권한의 종류 확인
select * from table_privilege_map;
