[데이터베이스] #10 오라클 실습 II

목차

1. LOB 데이터 타임
2. 집합 연산
3. dual 테이블
4. 문자열 처리 함수
5. 숫자형 처리 함수
6. 날짜형 처리 함수
7. 형 변환 함수
8. 뷰
9. 순환 질의
10. 시스템 및 객체 권한
11. 동의어
12. 트리거 구문

 

---

1. LOB 데이터 타입

LOB 타입은 대용량 데이터를 저장/관리하기 위하여 사용

• BLOB
• CLOB
• NCLOB
• BFILE

BFILE을 제외한 다른 데이터 타입은 트랜잭션 성질 지원

트랜잭션 commit/rollback에 의한 UNDO/REDO 기능을 DBMS가 자체적으로 수행

2. 집합 연산

오라클 시스템은 union all만 지원

intersect all, minis all 지원 x

3. dual 테이블

속성이 하나이며 터플이 하나인 sys 소유의 테이블

4. 문자열 처리 함수

Select sID, name, deptName
from student
where lower(name) = 'kim';

name 속성을 소문자로 계산하여 ‘kim’과 연산

Select instr('MILLER', 'L', 1, 2), instr('MILLER', 'x', 1, 2)

세 번째 인자는 시작 위치, 네 번째 인자는 몇 번째로 나오는지

returns: 4, 0

5. 숫자형 처리 함수

• round - 반올림
• Trunc - 내림

6. 날짜형 처리 함수

기본 형식: ‘YY/MM/DD’

• round - day으로 반올림한 month
• months_between - 날짜와 날짜 사이의 날수를 month 기준으로 출력, 소수점 포함

7. 형 변환 함수

TO_CHAR

Select sysdate, to_char(sysdate, 'YYYY-MM-DD DAY') from dual

returns: 24/05/18 2024-05-18 토요일

날짜 → ‘YYYY-MM-DD’

Select to_char(123456), to_char(123456, '000000000'), to_char(123456, '999,999,999') from dual;

returns: 123456 000123456 123,456

형식에서 0, 9는 자릿수를 나타냄

• 0은 자리수가 맞지 않으면 0으로 채움
• 9는 채우지 않음

TO_NUMBER

숫자형으로 데이터를 변환

8. 뷰

Create or replace view view_name as subquery

실체화된 뷰

터플을 가지는 뷰

속도 빠름

뷰의 터플 업데이트 필요

Create materialized view view_name
build [immediate | deferred]
refresh [fast | complete | force]
on [commit | demand]
[[enable | disable] query rewrite]
[on prebuilt table]
as select ...;

9. 순환 질의

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

10. 시스템 및 객체 권한

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

• with admin option - 시스템 권한 부여
• with grant option - 객체 권한 부여

11. 동의어

타 사용자의 객체를 접근하기 위해 ‘스키마.객체’ 형식으로 지정

ex) sys.dual → dual

(user1 프롬프트)
Grant select on student to user2;
(sys 프롬프트)
Grant create synonym to user2;
(user2 프롬프트)
Create synonym myStudent for user1.student;

• myStudent를 사용하여 user1의 스키마임을 숨김(보안)
• ‘스키마.객체’ 형식에서 단순한 형식으로 변경

12. 트리거 구문

마지막 문장이 반드시 \\임

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

Create or replace로 기존 트리거 수정 가능

Create pr replace trigger trig after update of salary on employee1
referencing new as n old as o
for each row
when (n.dnumber is not null)
begin
	update department1
	set totalsalary = totalsalary + :n.salary - :o.salary
	where dno = :n.dnumber;
end;
\