데이터베이스 프로그래밍 (소프트웨어 개발 트랙) 2005. 01. 15                               SQL (2) - 함수, 뷰

제 1부 고급 SQL 오라클 데이터 처리 함수 뷰 SQL (2) - 함수, 뷰

오라클 데이터 처리 함수 문자처리 함수 CHR / ASCII 문자 / ASCII LOWER / UPPER 소문자 / 대문자 소문자 / 대문자 INITCAP 첫 글자만 대문자로 변환 LPAD / RPAD 왼쪽 / 오른쪽에서 문자열 LTRIM / RTRIM 왼쪽 / 오른쪽 빈칸 삭제 REPLACE 문자열 치환 SUBSTR / SUBSTRB 문자열 잘라내기 / byte 기준 LENGTH / LENGTHB 문자열 길이 / byte 기준 INSTR / INSTRB 문자열의 위치를 찾음 / byte 기준 SQL (2) - 함수, 뷰

오라클 데이터 처리 함수 숫자 처리 함수 날짜 처리 함수 CEIL 소수점에서 올림 MOD 나머지 값 POWER 승수 ROUND 반올림 TRUNC 버림 SIGN 양수 / 음수 구분 날짜 처리 함수 SYSDATE 현재 날짜와 시간 LAST_DAY 해당 월의 마지막 날짜 MONTHS_BETWEEN 두 기간 사이의 개월 수 ADD_MONTHS 월수 더하고 빼기 NEXT_DAY 다음 첫번째 해당 요일의 날짜 SQL (2) - 함수, 뷰

오라클 데이터 처리 함수 변환 함수 TO_CHAR 문자로 치환 TO_NUMBER 숫자로 치환 TO_DATE 날짜로 치환 기타 NVL Null 처리 함수 DECODE 연속 조건문 USERENV 환경 값 GREATEST / LEAST 최대값 / 최소값 UID / USER User ID / User 명 SQL (2) - 함수, 뷰

오라클 데이터 처리 함수 DUAL 테이블 SYS 사용자가 소유하는 것으로 모든 사용자가 질의 가능 dummy 라는 단 하나의 컬럼에 X 라는 단 하나의 레코드만을 갖는 테이블 일시적인 산술연산이나 가상 컬럼등의 값을 보고 싶을 때 많이 사용 SQL (2) - 함수, 뷰

문자 처리 함수 – 문자 변환 CHR, ASCII ASCII 값을 문자로, 문자를 ASCII 값으로 변환 사용법 CHR (n) ASCII (char) SQL (2) - 함수, 뷰

문자 처리 함수 – 대소문자 변환 LOWER, UPPER, INITCAP 함수 사용법 LOWER : 문자열을 모두 소문자로 나머지는 소문자로 사용법 LOWER(컬럼명 | 문자열) UPPER(컬럼명 | 문자열) INITCAP(컬럼명 | 문자열) SQL (2) - 함수, 뷰

문자 처리 함수 – 대소문자 변환 예) SCOTT 사용자의 EMP 테이블 사용 SELECT empo, ename FROM emp WHERE empno < 7600; SQL (2) - 함수, 뷰

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) - 함수, 뷰

Cont. select empno, ename, job from emp where empno<7600; where LOWER(job) = 'manager'; SQL (2) - 함수, 뷰

문자 처리 함수- 문자열 조작 문자열 조작 함수 LPAD RPAD REPLACE n 자리만큼의 공간에서 char1을 오른쪽에 붙이고, 남는 자리는 char2 로 채움 LPAD(char1, n, char2) RPAD n 자리만큼의 공간에서 char1을 왼쪽에 붙이고, 남는 자리는 RPAD(char1, n, char2) REPLACE 문자나 문자열을 치환 REPLACE (char1, char2, char3) char1의 char2를 char3 으로 변환 SQL (2) - 함수, 뷰

문자 처리 함수- 문자열 조작 SQL (2) - 함수, 뷰

문자 처리 함수- 문자열 조작 SQL (2) - 함수, 뷰

문자 처리 함수- 문자열 조작 문자열 조작 SUBSTR, SUBSTRB SUBSTR(char, n) 문자열의 일부를 돌려줌 SUBSTR(char, n) char의 n번째 자리부터 문자열 끝까지 돌려줌 SUBSTR(char, n, m) char의 n번째 자리부터 m번째에 해당하는 문자열을 돌려줌 SUBSTRB(char, n, m) byte 단위로 char의 n번째 자리부터 m번째에 해당하는 문자열을 돌려줌 SQL (2) - 함수, 뷰

문자 처리 함수- 문자열 조작 SQL (2) - 함수, 뷰

문자 처리 함수- 문자열 조작 SQL (2) - 함수, 뷰

문자 처리 함수- 문자열 조작 LENGTH, LENGTHB : 문자열의 길이 LENGTH(char) LENGTHB(char) byte 단위로 char 길이 반환 SQL (2) - 함수, 뷰

문자 처리 함수- 문자열 조작 SQL (2) - 함수, 뷰

문자 처리 함수- 문자열 조작 INSTR, INSTRB : 문자열 속에서 특정 문자나 문자열의 위치를 알려줌 INSTR (char1, char2, n, m) char1 문자열 중에서 char2 가 포함된 문자열의 위치가 char1 기준으로 앞에서 몇 번째 있는지 알려줌. 단, n번째 위치부터 시작해서 m 번째로 char2와 같은 위치를 돌려줌 INSTRB (char1, char2, n, m) byte 단위로 char1 문자열 중에서 char2 가 포함된 문자열의 위치가 char1 기준으로 앞에서 몇 번째 있는지 알려줌. SQL (2) - 함수, 뷰

문자 처리 함수- 문자열 조작 SQL (2) - 함수, 뷰

문자 처리 함수- 문자열 조작 SQL (2) - 함수, 뷰

문자 처리 함수- 공백 제거 LTRIM, RTRIM : 공백문자 (Space) 제거 LTRIM (char) RTRIM (char) char의 맨 오른쪽부분에 있는 공백제거 SQL (2) - 함수, 뷰

문자 처리 함수- 공백 제거 SQL (2) - 함수, 뷰

숫자 처리 함수 - CEIL CEIL – 소수점 이하의 값이 존재하면 무조건 올림 CEIL (n) SQL (2) - 함수, 뷰

숫자 처리 함수 – MOD, POWER MOD : 나머지 값 구하는 함수 POWER : 승수 값을 구하는 함수 MOD(n, m) POWER : 승수 값을 구하는 함수 POWER(n, m) : n의 m 승 값 SQL (2) - 함수, 뷰

숫자 처리 함수 – MOD, POWER SQL (2) - 함수, 뷰

숫자 처리 함수 – ROUND, TRUNC ROUND : 반올림값을 구하는 함수 TRUNC : 버림 값을 구하는 함수 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) - 함수, 뷰

숫자 처리 함수 – ROUND, TRUNC SQL (2) - 함수, 뷰

숫자 처리 함수 – SIGN SIGN : 해당 값이 양수이면 1, 음수이면 –1, 0이면 0을 돌려줌 SIGN(n) : n 값을 1, 0, -1 로 바꾸어줌 SQL (2) - 함수, 뷰

날짜 처리 함수 - SYSDATE SYSDATE 지금 현재의 날짜와 시간을 의미하고, 기본 값으로 오늘 날짜를 돌려줌 SQL (2) - 함수, 뷰

날짜 처리 함수 – LAST_DAY LAST_DAY : 해당 날짜의 월의 마지막 날짜 LAST_DAY(date) SQL (2) - 함수, 뷰

날짜 처리 함수 – MONTHS_BETWEEN MONTHS_BETWEEN (date1, date2) date1과 date2 의 기간을 월 단위로 계산, date1이 date2보다 클 경우가 양수의 결과 SQL (2) - 함수, 뷰

날짜 처리 함수 – MONTHS_BETWEEN SQL (2) - 함수, 뷰

날짜 처리 함수 – ADD_MONTHS ADD_MONTHS : 해당 일을 월 단위로 해서 이후/이전 날짜로 바꾸어줌 ADD_MONTHS (date, n) date의 n 개월 후를 계산 SQL (2) - 함수, 뷰

날짜 처리 함수 – NEXT_DAY NEXT_DAY : 해당 일을 기준으로 주어진 요일이 처음 오는 날짜를 돌려줌 NEXT_DAY(date, 요일) date를 기준으로 [요일]에 해당하는 바로 다음 날짜를 구함 [요일]의 표현방법은 숫자로도 가능 일 : 1, 월 : 2, 화 : 3, 수 : 4, 목 : 5, 금 : 6, 토 : 7 SQL (2) - 함수, 뷰

날짜 처리 함수 – NEXT_DAY SQL (2) - 함수, 뷰

변환 함수 – TO_CHAR TO_CHAR : 문자로 변환 TO_CHAR(n) TO_CHAR(n, format) n 1000 ‘9,999.99’ ‘9G999D99’ 1234567.890 ‘9,999,999.999’ ‘9G999G999D999’ 3.5 1234 ‘999,99’ ‘999G999’ SQL (2) - 함수, 뷰

변환 함수 – TO_CHAR TO_CHAR(date, format) date를 format 형식의 문자로 변환 date 예제 sysdate ‘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) - 함수, 뷰

변환 함수 – TO_CHAR SQL (2) - 함수, 뷰

변환 함수 – TO_CHAR SQL (2) - 함수, 뷰

변환 함수 – TO_NUMBER TO_NUMBER : 형태만 숫자인 문자를 숫자로 변환 TO_NUMBER(char) char 가 숫자 모양이 아닌 a, b, 가, 나 처럼 일반 문자이면 에러 발생 TO_NUMBER(char, format) format 에 맞는 형태의 숫자로 이루어진 char를 숫자로 변환. 단, format의 크기가 최소한 char보다는 커야 한다 SQL (2) - 함수, 뷰

변환 함수 – TO_NUMBER SQL (2) - 함수, 뷰

변환 함수 – TO_DATE TO_DATE : 형태가 날짜인 문자를 DATE 로 변환 TO_DATE (char) TO_DATE (char, format) format 에 맞는 형태의 날짜로 이루어진 char 를 날짜로 변환 SQL (2) - 함수, 뷰

변환 함수 – TO_DATE SQL (2) - 함수, 뷰

변환 함수 – TO_DATE SQL (2) - 함수, 뷰

기타 - NVL NVL : 해당 컬럼이 null 일 경우에 대신할 문자나 숫자 등을 대신 사용할 수 있게 하는 함수 아닌 경우에는 원래 데이터를 사용 SQL (2) - 함수, 뷰

Cont. SQL (2) - 함수, 뷰

기타 - DECODE DECODE : 연속적인 조건문 DECODE (a,b,c,d) DECODE (a,b,c,d,e,f) SQL (2) - 함수, 뷰

기타 - USERENV USERENV : 지금 사용 중인 오라클의 몇 가지 환경변수 값을 알수 있음 USERENV(‘LANGUAGE’) 현재 설치되어있는 오라클의 언어 USERENV(‘TERMINAL’) 컴퓨터 이름 USERENV(‘SESSIONID’) 세션 ID SQL (2) - 함수, 뷰

기타 - 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) - 함수, 뷰

기타 – GREATEST, LEAST GREAST, LEAST : 나열한 데이터 중에서 가장 큰 값과 가장 작은 값을 찾음. GREAST(n1, n2, n3,…,char1, char2, char3,..) LEAST (n1, n2, n3,…,char1, char2, char3,..) SQL (2) - 함수, 뷰

기타 – GREATEST, LEAST SQL (2) - 함수, 뷰

기타 – UID, USER UID, USER : 사용자 ID와 사용자의 OWNER 를 알려줌 SQL (2) - 함수, 뷰

뷰 뷰 개요 뷰 생성 뷰 구조 확인 뷰 수정 뷰 를 통한 데이터 변경 SQL (2) - 함수, 뷰

뷰 뷰 (View) 하나이상의 테이블로부터 유도된 이름을 가진 가상 테이블 (virtual table) 구문 CREATE [OR REPLACE] VIEW view_name [column1, column2, ….] AS Select statement SQL (2) - 함수, 뷰

뷰 예 1: 학생(STUDENT) 테이블의 컴퓨터학과 학생들로 컴퓨터과 학생(CSTUDENT) 라는 뷰 생성 SQL (2) - 함수, 뷰

뷰 SQL (2) - 함수, 뷰

뷰 예 2 : SQL (2) - 함수, 뷰

뷰 예 3 : 두 개 이상의 테이블을 연관하여 정의 SQL (2) - 함수, 뷰

뷰의 구조 및 이름 확인 USER_VIEWS 데이터 사전 테이블 SQL (2) - 함수, 뷰

뷰의 수정 OR REPLACE 옵션 사용 SQL (2) - 함수, 뷰

뷰를 통한 데이터 변경 뷰가 하나의 테이블을 사용하고, 적절한 권한이 있다면 INSERT, UPDATE, DELETE 문을 사용하여 뷰가 참조하는 테이블의 데이터 변경 가능 WITH CHECK OPTION 절 사용 뷰를 통해 수행되는 INSERT 와 UPDATE는 WITH CHECK OPTION 절이 있으면 삽입되거나 갱신되는 데이터에 대해 무결성 제약조건과 데이터 검증 체크를 한다. READ ONLY 옵션 사용 뷰에서 삽입, 갱신, 삭제가 불가능해진다. SQL (2) - 함수, 뷰

뷰를 통한 데이터 변경 예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) - 함수, 뷰

뷰를 통한 데이터 변경 예 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; 예 2 SQL (2) - 함수, 뷰

뷰를 통한 데이터 변경 예 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) - 함수, 뷰

뷰를 통한 데이터 변경 예 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) - 함수, 뷰

뷰를 통한 데이터 변경 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) - 함수, 뷰

뷰 삭제 뷰는 데이터베이스에서 기본 테이블을 기반으로 하기 때문에 데이터 손실없이 뷰 삭제 가능 구문 예 DROP VIEW view_name; SQL> DROP VIEW MSTUDENT; View dropped. SQL (2) - 함수, 뷰