Transcript PowerPoint 프레젠테이션

데이터베이스 프로그래밍
(소프트웨어 개발 트랙)
2005. 01. 15
SQL (2) - 함수, 뷰
1
제 1부 고급 SQL
오라클 데이터 처리 함수
뷰
SQL (2) - 함수, 뷰
2
오라클 데이터 처리 함수
문자
처리
함수
CHR / ASCII
문자 / ASCII
LOWER / UPPER
소문자 / 대문자
INITCAP
첫 글자만 대문자로 변환
LPAD / RPAD
왼쪽 / 오른쪽에서 문자열
LTRIM / RTRIM
왼쪽 / 오른쪽 빈칸 삭제
REPLACE
문자열 치환
SUBSTR / SUBSTRB
문자열 잘라내기 / byte 기준
LENGTH / LENGTHB
문자열 길이 / byte 기준
INSTR / INSTRB
문자열의 위치를 찾음 / byte 기준
SQL (2) - 함수, 뷰
3
오라클 데이터 처리 함수
숫자
처리
함수
날짜
처리
함수
CEIL
소수점에서 올림
MOD
나머지 값
POWER
승수
ROUND
반올림
TRUNC
버림
SIGN
양수 / 음수 구분
SYSDATE
현재 날짜와 시간
LAST_DAY
해당 월의 마지막 날짜
MONTHS_BETWEEN
두 기간 사이의 개월 수
ADD_MONTHS
월수 더하고 빼기
NEXT_DAY
다음 첫번째 해당 요일의 날짜
SQL (2) - 함수, 뷰
4
오라클 데이터 처리 함수
변환
함수
기타
TO_CHAR
문자로 치환
TO_NUMBER
숫자로 치환
TO_DATE
날짜로 치환
NVL
Null 처리 함수
DECODE
연속 조건문
USERENV
환경 값
GREATEST / LEAST
최대값 / 최소값
UID / USER
User ID / User 명
SQL (2) - 함수, 뷰
5
오라클 데이터 처리 함수

DUAL 테이블



SYS 사용자가 소유하는 것으로 모든 사용자가 질의 가능
dummy 라는 단 하나의 컬럼에 X 라는 단 하나의 레코드만을
갖는 테이블
일시적인 산술연산이나 가상 컬럼등의 값을 보고 싶을 때 많이 사용
SQL (2) - 함수, 뷰
6
문자 처리 함수 – 문자 변환

CHR, ASCII


ASCII 값을 문자로, 문자를 ASCII 값으로 변환
사용법


CHR (n)
ASCII (char)
SQL (2) - 함수, 뷰
7
문자 처리 함수 – 대소문자 변환

LOWER, UPPER, INITCAP 함수

LOWER : 문자열을 모두 소문자로

UPPER : 문자열을 모두 대문자로


INITCAP : 문자열에 속한 각 단어별로 첫 글자를 대문자로,
나머지는 소문자로
사용법

LOWER(컬럼명 | 문자열)

UPPER(컬럼명 | 문자열)

INITCAP(컬럼명 | 문자열)
SQL (2) - 함수, 뷰
8
문자 처리 함수 – 대소문자 변환

예) SCOTT 사용자의 EMP 테이블 사용

SELECT empo, ename FROM emp
WHERE empno < 7600;
SQL (2) - 함수, 뷰
9
Cont.
select empno, UPPER(ename)
from emp
where empno<7600;
select empno, LOWER(ename)
from emp
where empno<7600;
select empno, INITCAP(ename)
from emp
where empno<7600;
SQL (2) - 함수, 뷰
10
Cont.
select empno, ename, job
from emp
where empno<7600;
select empno, ename
from emp
where LOWER(job) = 'manager';
SQL (2) - 함수, 뷰
11
문자 처리 함수- 문자열 조작

문자열 조작 함수

LPAD



RPAD



n 자리만큼의 공간에서 char1을 왼쪽에 붙이고, 남는 자리는
char2 로 채움
RPAD(char1, n, char2)
REPLACE


n 자리만큼의 공간에서 char1을 오른쪽에 붙이고, 남는 자리는
char2 로 채움
LPAD(char1, n, char2)
문자나 문자열을 치환
REPLACE (char1, char2, char3)

char1의 char2를 char3 으로 변환
SQL (2) - 함수, 뷰
12
문자 처리 함수- 문자열 조작
SQL (2) - 함수, 뷰
13
문자 처리 함수- 문자열 조작
SQL (2) - 함수, 뷰
14
문자 처리 함수- 문자열 조작

문자열 조작

SUBSTR, SUBSTRB


SUBSTR(char, n)


char의 n번째 자리부터 문자열 끝까지 돌려줌
SUBSTR(char, n, m)


문자열의 일부를 돌려줌
char의 n번째 자리부터 m번째에 해당하는 문자열을 돌려줌
SUBSTRB(char, n, m)

byte 단위로 char의 n번째 자리부터 m번째에 해당하는
문자열을 돌려줌
SQL (2) - 함수, 뷰
15
문자 처리 함수- 문자열 조작
SQL (2) - 함수, 뷰
16
문자 처리 함수- 문자열 조작
SQL (2) - 함수, 뷰
17
문자 처리 함수- 문자열 조작

LENGTH, LENGTHB : 문자열의 길이

LENGTH(char)


char 길이 반환
LENGTHB(char)

byte 단위로 char 길이 반환
SQL (2) - 함수, 뷰
18
문자 처리 함수- 문자열 조작
SQL (2) - 함수, 뷰
19
문자 처리 함수- 문자열 조작

INSTR, INSTRB : 문자열 속에서 특정 문자나 문자열의
위치를 알려줌

INSTR (char1, char2, n, m)



char1 문자열 중에서 char2 가 포함된 문자열의 위치가
char1 기준으로 앞에서 몇 번째 있는지 알려줌.
단, n번째 위치부터 시작해서 m 번째로 char2와 같은 위치를 돌려줌
INSTRB (char1, char2, n, m)


byte 단위로 char1 문자열 중에서 char2 가 포함된 문자열의 위치가
char1 기준으로 앞에서 몇 번째 있는지 알려줌.
단, n번째 위치부터 시작해서 m 번째로 char2와 같은 위치를 돌려줌
SQL (2) - 함수, 뷰
20
문자 처리 함수- 문자열 조작
SQL (2) - 함수, 뷰
21
문자 처리 함수- 문자열 조작
SQL (2) - 함수, 뷰
22
문자 처리 함수- 공백 제거

LTRIM, RTRIM : 공백문자 (Space) 제거

LTRIM (char)


char의 맨 왼쪽부분에 있는 공백제거
RTRIM (char)

char의 맨 오른쪽부분에 있는 공백제거
SQL (2) - 함수, 뷰
23
문자 처리 함수- 공백 제거
SQL (2) - 함수, 뷰
24
숫자 처리 함수 - CEIL

CEIL – 소수점 이하의 값이 존재하면 무조건 올림

CEIL (n)
SQL (2) - 함수, 뷰
25
숫자 처리 함수 – MOD, POWER

MOD : 나머지 값 구하는 함수


MOD(n, m)
POWER : 승수 값을 구하는 함수

POWER(n, m) : n의 m 승 값
SQL (2) - 함수, 뷰
26
숫자 처리 함수 – MOD, POWER
SQL (2) - 함수, 뷰
27
숫자 처리 함수 – ROUND, TRUNC

ROUND : 반올림값을 구하는 함수

ROUND(n,m) : n은 계산할 숫자, m 은 정수값

m == 0 : n의 소수점 첫째자리에서 반올림,
ROUND(n)과 같음


m > 0 : n의 소수점 (m+1) 자리에서 반올림

m < 0 : n의 10의 (-m) 자리에서 반올림
TRUNC : 버림 값을 구하는 함수

TRUNC(n,m) : n은 계산할 숫자, m 은 정수값
SQL (2) - 함수, 뷰
28
숫자 처리 함수 – ROUND, TRUNC
SQL (2) - 함수, 뷰
29
숫자 처리 함수 – SIGN

SIGN : 해당 값이 양수이면 1, 음수이면 –1, 0이면 0을 돌려줌

SIGN(n) : n 값을 1, 0, -1 로 바꾸어줌
SQL (2) - 함수, 뷰
30
날짜 처리 함수 - SYSDATE

SYSDATE


지금 현재의 날짜와 시간을 의미하고, 기본 값으로 오늘
날짜를 돌려줌
SYSDATE
SQL (2) - 함수, 뷰
31
날짜 처리 함수 – LAST_DAY

LAST_DAY : 해당 날짜의 월의 마지막 날짜

LAST_DAY(date)

date 일자의 해당 월의 마지막 날짜
SQL (2) - 함수, 뷰
32
날짜 처리 함수 – MONTHS_BETWEEN

MONTHS_BETWEEN : 두 날짜사이의 기간을 월 단위로 계산

MONTHS_BETWEEN (date1, date2)

date1과 date2 의 기간을 월 단위로 계산,
date1이 date2보다 클 경우가 양수의 결과
SQL (2) - 함수, 뷰
33
날짜 처리 함수 – MONTHS_BETWEEN
SQL (2) - 함수, 뷰
34
날짜 처리 함수 – ADD_MONTHS

ADD_MONTHS : 해당 일을 월 단위로 해서 이후/이전 날짜로
바꾸어줌

ADD_MONTHS (date, n)

date의 n 개월 후를 계산
SQL (2) - 함수, 뷰
35
날짜 처리 함수 – NEXT_DAY

NEXT_DAY : 해당 일을 기준으로 주어진 요일이 처음 오는
날짜를 돌려줌

NEXT_DAY(date, 요일)


date를 기준으로 [요일]에 해당하는 바로 다음 날짜를 구함
[요일]의 표현방법은 숫자로도 가능

일 : 1, 월 : 2, 화 : 3, 수 : 4, 목 : 5, 금 : 6, 토 : 7
SQL (2) - 함수, 뷰
36
날짜 처리 함수 – NEXT_DAY
SQL (2) - 함수, 뷰
37
변환 함수 – TO_CHAR

TO_CHAR : 문자로 변환

TO_CHAR(n)


n 을 문자로 변환
TO_CHAR(n, format)

n 을 format 형식의 문자로 변환
n
format
format
1000
‘9,999.99’
‘9G999D99’
1234567.890
‘9,999,999.999’
‘9G999G999D999’
3.5
‘9,999.99’
‘9G999D99’
1234
‘999,99’
‘999G999’
SQL (2) - 함수, 뷰
38
변환 함수 – TO_CHAR

TO_CHAR(date, format)

date를 format 형식의 문자로 변환
date
sysdate
예제
format
‘YYYYMMDD’
‘20040201’
‘YYYY/MM/DD’
‘2004/02/01’
‘YYYY-MM-DD’
‘2004-02-01’
‘YYYY/MM/DD HH24MISS’
‘20040201 132910’
‘YYYY/MM/DD HHMISS AM’
‘20040201 012910 PM’
‘YYYY/MM/DD HH24:MI:SS’
‘2004/02/01 13:29:10’
‘YYYY/MM/DD HH24:MI:SS:SSSSS’
‘2004/02/01 13:29:10:7880’
SQL (2) - 함수, 뷰
39
변환 함수 – TO_CHAR
SQL (2) - 함수, 뷰
40
변환 함수 – TO_CHAR
SQL (2) - 함수, 뷰
41
변환 함수 – TO_NUMBER

TO_NUMBER : 형태만 숫자인 문자를 숫자로 변환


TO_NUMBER(char)

숫자로 이루어진 char를 문자로 변환.

char 가 숫자 모양이 아닌 a, b, 가, 나 처럼 일반 문자이면 에러 발생
TO_NUMBER(char, format)

format 에 맞는 형태의 숫자로 이루어진 char를 숫자로 변환.

단, format의 크기가 최소한 char보다는 커야 한다
SQL (2) - 함수, 뷰
42
변환 함수 – TO_NUMBER
SQL (2) - 함수, 뷰
43
변환 함수 – TO_DATE

TO_DATE : 형태가 날짜인 문자를 DATE 로 변환

TO_DATE (char)


날짜로 이루어진 char 를 날짜로 변환
TO_DATE (char, format)

format 에 맞는 형태의 날짜로 이루어진 char 를 날짜로 변환
SQL (2) - 함수, 뷰
44
변환 함수 – TO_DATE
SQL (2) - 함수, 뷰
45
변환 함수 – TO_DATE
SQL (2) - 함수, 뷰
46
기타 - NVL

NVL : 해당 컬럼이 null 일 경우에 대신할 문자나 숫자 등을 대신
사용할 수 있게 하는 함수

NVL(컬럼명, 대신할 데이터)

해당 컬럼의 데이터가 null 일 경우 대신한 데이터가 대신 사용되고,
아닌 경우에는 원래 데이터를 사용
SQL (2) - 함수, 뷰
47
Cont.
SQL (2) - 함수, 뷰
48
기타 - DECODE

DECODE : 연속적인 조건문

DECODE (a,b,c,d)


a가 b이면 c이고, 아니면 d
DECODE (a,b,c,d,e,f)

a가 b이면 c이고, d이면 e이고 아니면 f
SQL (2) - 함수, 뷰
49
기타 - USERENV

USERENV : 지금 사용 중인 오라클의 몇 가지 환경변수 값을
알수 있음

USERENV(‘LANGUAGE’)


USERENV(‘TERMINAL’)


현재 설치되어있는 오라클의 언어
컴퓨터 이름
USERENV(‘SESSIONID’)

세션 ID
SQL (2) - 함수, 뷰
50
기타 - USERENV
SQL> SELECT USERENV('LANGUAGE') "LANGUAGE",
USERENV('TERMINAL') "TERMINAL",
USERENV('SESSIONID') "SESSIONID"
FROM DUAL;
LANGUAGE
---------------------------------------------------TERMINAL
SESSIONID
------------------------------ ---------AMERICAN_AMERICA.KO16KSC5601
pts/0
127744
SQL (2) - 함수, 뷰
51
기타 – GREATEST, LEAST

GREAST, LEAST : 나열한 데이터 중에서 가장 큰 값과
가장 작은 값을 찾음.

GREAST(n1, n2, n3,…,char1, char2, char3,..)

LEAST (n1, n2, n3,…,char1, char2, char3,..)
SQL (2) - 함수, 뷰
52
기타 – GREATEST, LEAST
SQL (2) - 함수, 뷰
53
기타 – UID, USER

UID, USER : 사용자 ID와 사용자의 OWNER 를 알려줌
SQL (2) - 함수, 뷰
54
뷰

뷰 개요

뷰 생성

뷰 구조 확인

뷰 수정

뷰 를 통한 데이터 변경
SQL (2) - 함수, 뷰
55
뷰

뷰 (View)


하나이상의 테이블로부터 유도된 이름을 가진 가상 테이블
(virtual table)
구문
CREATE [OR REPLACE] VIEW view_name
[column1, column2, ….]
AS
Select statement
SQL (2) - 함수, 뷰
56
뷰

예 1: 학생(STUDENT) 테이블의 컴퓨터학과 학생들로
컴퓨터과 학생(CSTUDENT) 라는 뷰 생성
SQL (2) - 함수, 뷰
57
뷰
SQL (2) - 함수, 뷰
58
뷰

예2:
SQL (2) - 함수, 뷰
59
뷰

예 3 : 두 개 이상의 테이블을 연관하여 정의
SQL (2) - 함수, 뷰
60
뷰의 구조 및 이름 확인

USER_VIEWS 데이터 사전 테이블
SQL (2) - 함수, 뷰
61
뷰의 수정

OR REPLACE 옵션 사용
SQL (2) - 함수, 뷰
62
뷰를 통한 데이터 변경


뷰가 하나의 테이블을 사용하고, 적절한 권한이 있다면 INSERT,
UPDATE, DELETE 문을 사용하여 뷰가 참조하는 테이블의 데이터
변경 가능
WITH CHECK OPTION 절 사용

뷰를 통해 수행되는 INSERT 와 UPDATE는 WITH CHECK
OPTION 절이 있으면 삽입되거나 갱신되는 데이터에 대해
무결성 제약조건과 데이터 검증 체크를 한다.

READ ONLY 옵션 사용

뷰에서 삽입, 갱신, 삭제가 불가능해진다.
SQL (2) - 함수, 뷰
63
뷰를 통한 데이터 변경

예1
SQL> UPDATE CSTUDENT
2 SET
S_YEAR = 3
3 WHERE S_ID = '20011234';
1 row updated.
SQL> select s_id, s_name, s_year
2 from cstudent;
S_ID
S_NAME
S_YEAR
---------- ---------- ---------20011234 신경화
3
20011235 서용만
4
20011236 정주영
4
SQL (2) - 함수, 뷰
64
뷰를 통한 데이터 변경

예2
SQL> CREATE OR REPLACE VIEW
2 CSTUDENT_YEAR4 (S_ID, S_NAME, S_YEAR)
3 AS
4 SELECT S_ID, S_NAME, S_YEAR
5 FROM
STUDENT
6 WHERE S_MAJOR= '컴퓨터학과' AND S_YEAR=4
7 WITH CHECK OPTION CONSTRAINT
8 CSTUDENT_YEAR4_CK;
SQL (2) - 함수, 뷰
65
뷰를 통한 데이터 변경

예2
SQL> UPDATE CSTUDENT_YEAR4
2 SET
S_YEAR = 3
3 WHERE S_ID = '20011234';
UPDATE CSTUDENT_YEAR4
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL (2) - 함수, 뷰
66
뷰를 통한 데이터 변경

예 3 : READ ONLY 옵션 사용
SQL> CREATE OR REPLACE VIEW
2 MSTUDENT (S_ID, S_NAME, S_YEAR)
3 AS
4 SELECT S_ID, S_NAME, S_YEAR
5 FROM
STUDENT
6 WHERE S_MAJOR= '멀티미디어학과'
7 WITH READ ONLY;
View created.
SQL (2) - 함수, 뷰
67
뷰를 통한 데이터 변경
SQL> SELECT * FROM MSTUDENT;
S_ID
S_NAME
S_YEAR
---------- ---------- ---------20023451 김영철
3
20012454 정태웅
4
20012456 정진영
4
SQL> DELETE FROM MSTUDENT
2 WHERE S_ID='20023451';
DELETE FROM MSTUDENT
*
ERROR at line 1:
ORA-01752: cannot delete from view
without exactly one key-preserved table
SQL (2) - 함수, 뷰
68
뷰 삭제


뷰는 데이터베이스에서 기본 테이블을 기반으로 하기 때문에
데이터 손실없이 뷰 삭제 가능
구문
DROP VIEW view_name;

예
SQL> DROP VIEW MSTUDENT;
View dropped.
SQL (2) - 함수, 뷰
69