제 7장. 오라클 실습 II

 * 목차
 7.1 오라클 SQLs
 7.2 실습하기


 * LOB 데이터 타입
대용량 데이터를 저장, 관리하기 위해서 사용

LOB 데이터 타입 종류
 1. BLOB
 2. CLOB
 3. NCLOB
 4. BFILE

BFILE을 제외한 다른 데이터 타입은 
트랜잭션 성질이 지원됨  (ACID 성질)
so, 트랜잭션 커밋, 롤백에 의한 undo, redo 기능을
DBMS가 자체적으로 수행

 BFILE
운영체제의 파일 시스템을 이용하여
저장, 관리하는 데이터 타입
트랜잭션 성질이 지원되지 x
읽기 연산만 지원
데이터 변경이 ㅂㄱㄴ


 * 집합 연산
오라클은 union all만 지원

intersect all, minis all은 다른 방식으로 지원


 * dual 테이블
속성이 하나이고  (dummy varchar2(1))
튜퓰이 하나인  (튜플 값이 'x')
sys 소유의 테이블


 * DBMS 데이터 처리 함수
 1. 숫자 처리 함수 
 2. 문자 처리 함수
 3. 날짜 처리 함수
 4. 데이터 변환 처리 함수
 5. 일반 함수


 * 문자 처리 함수

 Lower('문자')
소문자로 변환

 Upper('문자')
대문자로 변환

 Initcap('문자')
첫 글자 빼고
나머지는 대소문자 반대로 변환

 Instr('문자열', '찾고 싶은 문자', 시작위치, 순서)
문자열에서 특정 문자가 있는 위치를 반환

ex)
Instr('MILLER', 'L', 1, 2)
ㄴ 'MILLER'에서 'L'을 찾는데
ㄴ 1번째 위치부터 검색하고
ㄴ 2번째 나오는 'L'의 위치를 반환
ㄴ 찾고자 하는 문자가 없는 경우 0을 반환

 Replace('문자열', '문자1', '문자2')
문자열에서 문자1을 찾아서 문자2로 치환

ex)
replace('jack and jue', 'j' ,' bl')은
'black and blue'을 리턴

 Substr('문자열', 인덱스, 반환 개수)
문자열에서 인덱스에 있는 위치부터 반환 개수만큼 문자를 반환

ex)
substr('900303-1234567', 8, 1)은
1을 리턴

 length('문자열') from dual
문자열 길이를 리턴

 concat('문자', '문자') form dual
두 문자를 연결
이 때 띄어쓰기는 x

ex)
concat('Oracle', 'Sql')은
'OracleSql'을 리턴

이 함수들은 Select 문에서 사용하면
그 결과를 볼 수 있다


 * 숫자형 처리 함수

 Round(숫자, 자리수)
지정한 자릿수 이하에서 반올림

ex)
Round(456.789, 2)는 456.79

 Trunc(숫자, 자리수)
지정한 자리수 이하에서 절삭

 Sign(숫자)
주어진 값이 양수, 음수, 0인지를 판단
 1. 양수이면 1
 2. 음수이면 -1
 3. 0이면 0
을 리턴


 * 날짜형 처리 함수

 sysdate()
현재 날짜를 반환
기본 형식은 'YY/MM/DD'  (date 데이터 형)

 date 데이터 형
더하기, 빼기 연산을 하면
날짜 값 (day)에 가감을 한다
date 형끼리도 더하기, 빼기 연산 ㄱㄴ

round() 함수에 date 데이터 형을 넣으면
주어진 날짜를 특정 기준으로 반올림한 날짜를 반환
보통 16일을 기준으로 반올림한 달을 반환

 month_between(date 형, date 형)
날짜와 날짜 사이의 날수를
달 수 기준으로 반환 (month)
소수점 이하 값도 나오는데
이는 한달이 되지 않은 달을 의미


 * 형 변환 함수

 TO_CHAR()
날짜나 숫자를 문자형으로 변환

ex)
to_char(sysdate, 'YY-MM-DD')은
19/04/29 2019-04-29 19-04-29 월요일
을 리턴

 TO_NUMBER()
데이터를 숫자형으로 변환

ex)
to_number('20000', '99999') - to_number('10000', '99999)는
10000
을 리턴
즉, 이어서 변환 후에 인자끼리 계산하고 이어붙인 것


 * Create or replace view 뷰 형식
기존에 존재하는 뷰 이름 정의를 수정하기가 수월

 force 옵션
베이스 테이블 존재 여부와 상관없이 뷰 생성 가능
기본 옵션은 noforce다

 with read only 옵션
뷰에 대하여 검색만 가능하고
뷰를 통한 베이스 테이블 변경을 불가능

사용자에게 뷰 생성 권한이 없으면
DBA가 다음 문장을 통해 필요한 권한을 받는다
Grant create view to C##hodori

Create or replace force view 뷰 as
Select 문
with check option
with read only


 * 뷰 with check option
뷰 질의 정의에 부합하지 않은
튜플 입력 및 변경을 방지


 * 실체화된 뷰
튜플을 가지는 뷰

빠른 질의문 응답을 위해서 사용
응답시간이 기존 뷰보다 빠름

but 시간이 흐름에 따라
뷰가 최신 정보를 반영하지 못하는 경우가 있음

주기적으로
or 베이스 테이블에 변화가 있을 때
or 사용자로부터 명시적인 요구가 있을 때
DBS가 실체화된 뷰 갱신 옵션을 제공

SQL 표준은
실체화된 뷰에 대한 표준 사양을 제공하지 x


 * 실체화된 뷰 구현
Create materialized view 뷰이름
build immediate 혹은 deferred
refresh fast 혹은 complete 혹은 force
on commint 혹은 demand
enable 혹은 disable query rewrite
on prebuilt table
as select문

refresh 방식
 1. complete 
 2. fast 
 3. force

 complete
테이블을 초기화하고 다시 인스턴스를 구현

 fast
미리 정의된 정보를 이영하여
변경된 부분만 이용해서 refresh

 force
우선 fast를 시작하고
수행하지 못하면 complete를 수행

refresh를 하는 시점
 1. on commit
 2. on demand

 on commit
과녈ㄴ 테이블에 대한 commit 연산이 있으면 refresh

 on demand
사용자 요청 or 주기적인 task에 의하여 refresh

 query rewrite 절
query optimizer에게 질의어 재생성을 할 때
실체화된 뷰가 고려되어야 하는지를 결정

 on prebuilt table
이미 존재하는 테이블 세그먼트를 사용


 * 순환 질의
구문에서 with 키워드 다음에 recursive가 없어야하고
union은 반드시 union all로 사용

ex)
with recu(x, y) as (
  (select c, p from course)
  union
  (select x, p from recu, course c
  where lower(y)=lower(c)))
  select * from recu
  order by x;


 * 시스템 권한 및 객체 권한
오라클 시스템의 권한은 크게 2가지
 1. 시스템 권한
 2. 객체 권한

객체 권한은
반드시 권한 및 적용되는 타입이 함꼐 명시되어야 한다

권한은
 1. Delete
 2. Execute
 3. Index
 4. Insert
 5. References
 6. Select
 7. Update

 Delete 객체 타입은
Table

 Execute 객체 타입은
 1. PL/Sql package,
 2. procedure
 3. function

 Index 객체 타입은
Table or materialized view

 Insert 객체 타입은
Table or synonym

 References 객체 타입은
Table or materialized view

 Select 객체 타입은
 1. Talbe
 2. sequence
 3. view
 4. materialized view
 5. synonym

 Update 객체 타입은
Table


 * With admin option
시스템 권한 부여 시 사용
이 옵션이 있으면 다른 사용자에게 권한을 부여할 수 있다

 구문은
Grant create session to 유저, 유저 with admin option;


 * With grant option
객체 권한 부여 시 사용

 구문은
Grant select on 테이블.속성 to 유저 with grant option;


 * 동의어  Synonym
타 사용자의 객체에 접근할 때는
스키마.객체 형식으로 지정

스키마가 지정되지 않으면
자신 스키마에서 객체를 찾음

동의어는
스키마.객체 형식 명시가 불편한 것을 방지

동의어를 사용하면
 1. 객체 접근이 단순하고
 2. 보안 문제도 해결

 dual은
Public 동의어의 대표적인 사례
sys.dual이 아니라 dual만으로도 사용 ㄱㄴ


 * 트리거 구문
Create or replace trigger 트리거  // or replace는 선택사항
before 혹은 after 혹은 instead of
insert 혹은 update 혹은 delete on 객체
for each row
when 조건
declare 명시 파트  // 선택사항
begin 실행
exception  // 선택사항
end;

트리거 본문은 PL/SQL 형식으로 작성

트리거를 실행하기 위해서
마지막 문장은 반드시 /가 되어야 함

트리거를 수행한 결과를 출력하기 위해서는
반드시 Set serveroutput on을 먼저 실행해야 함

 Create or replace
기존 트리거 수정이 용이