[Database] 오라클 실습 II

LOB data types

• LOB 타입은 대용량 데이터를 저장/관리하기 위해 사용됨
• BLOB, CLOB, NCLOB, BFILE이 있으며 BFILE을 제외한 다른 데이터 타입은 트랜잭션 성질이 지원됨
• BFILE은 운영체제의 파일 시스템을 이용하여 저장/관리하는 데이터 타입으로 데이터 변경이 불가능, 읽기 연산만 지원

 

Set operators

• Oracle 시스템은 'union all'만 지원하며 'intersect all', 'minis all'은 지원하지 않음 > 최근에 생김
• Except
• Except all == Minus all
• Intersect all

dual table

• 속성이 하나(dummy varchar2(1))이며 튜플이 하나인 sys 소유의 테이블
• SELECT 문장엔 반드시 from절이 있어야 하는데, date 같이 빈 테이블을 사용해야 할 때 사용됨

 

Functions for characters

Function	Instruction	Result
Lower('SQL Course')	모두 소문자로	sql course
Upper('SQL Course')	모두 대문자로	SQL COURSE
Initcap('SQL Course')	첫 알파벳만 대문자로	Sql Course
SELECT length('this lab is cool') from dual;	문자열 길이	returns 16
SELECT concat('Oracle', 'SQL') from dual;	문자열 붙이기	returns 'OracleSQL'
SELECT instr('MILLER', 'L', 1, 2), instr('MILLER', 'M', 2, 1) from dual	(1) 문자열의 (3)부터 검색해서 (2)가 (4)번째 나오는 index (index 1부터 시작)	returns 4, 0
SELECT replace('jack and jue', 'j', 'bl') from dual;	(1) 문자열의 (2) 문자열들을 (3)으로 대체	returns 'black and blue'
SELECT substr('900303-1234567', 8, 2) from dual;	(1) 문자열의 (2)번째 문자부터 (3)개 추출	returns '12'

 

Functions for numeric types

Function	Instruction	Result
SELECT round(456.789, 2), round(456.789, -1), round(456.789) from dual;	(2) 자릿수부터 반올림 (-1부턴 정수 부분)	returns 456.79, 460, 457
SELECT trunc(456.789, 2), trunc(456.789, -1), trunc(456.789) from dual;	(2) 자릿수 미만 절삭(내림)	returns 456.78, 450, 456
SELECT sign(100), sign(-100), sign(0) from dual;	양수이면 1 반환, 음수 -1, 0은 0	returns 1, -1, 0

 

Functions for date types

• sysdate는 현재 날짜를 반환하며, 기본 형식은 'YY/MM/DD'로 더하기, 빼기 연산으로 날짜값에 가감을 할 수 있음

ex)

SELECT sysdate, sysdate+1, sysdate-1 from dual; 
# returns today, tomorrow, yesterday in 'YY/MM/DD' format

 

• round 함수를 date형 값에 적용하면 주어진 날짜를 16일부터 올리는 기준으로 반올림한 날짜를 반환

ex)

SELECT hiredate, round(hiredate, 'month') from emp;

# 98/12/17 >> 99/01/01

# 98/02/20 >> 98/03/01

# 98/04/02 >> 98/04/01

 

• months_between 날짜와 날짜 사이의 날수를 달(month) 수 기준으로 반환, 소수점 이하 값은 한 달이 되지 않은 달을 의미함

ex)

 SELECT sysdate, hiredate, months_between(sysdate, hiredate) from emp;
# 09/01/25, 80/12/27, 337.281158 - 2009년 1월 25일과 1980년 12월 27일 사이의 개월 수는 337개월 + 0.2881158개월

 

ROWNUM

• 1로 시작하는 쿼리 pseudo-column임
• where 절을 통과해야지만 값이 나옴

ex)

SELECT * from student where rownum <= 5;
# returns 5 tuples
# 저장된 순서대로 검색 (자기 맘대로 저장하긴 함)

SELECT * from student where rownum <= 5;
order by gpa desc;
# rownum이 정렬 전에 먼저 부여돼서 top 5 안 나옴
# 이렇게 해도 아무렇게나 출력된다는 뜻

SELECT * from (select * from student order by gpa desc)
where rownum <= 5;
# 이렇게 해야 top 5 나옴

SELECT * from student
where rownum > 1;
# 1부터 해야해서 아무것도 return 안 됨
# rownum >= 1은 됨

With mytemp as
(SELECT sID, name, gpa, rank() over (order by GPA desc) as myRank
from student)
SELECT *
from mytemp
where myRank <= 3;
# 2등 3등 1등 이렇게 3등까진 나오지만 순서는 막 나옴

 

Functions for data conversion

• to_char 함수는 날짜나 숫자를 문자형으로 반환하기 위해 사용

ex)

SELECT sysdate, to_char(sysdate, 'YYYY-MM-DD'), to_char(sysdate, 'YY-MM-DD DAY') from dual;
# returns 19/04/29, 2019-04-29, 19-04-29 월요일 - 'YYYY-MM-DD'는 형식을 표현, DAY는 요일

SELECT to_char(123456), to_char(123456, '000000000'), to_char(123456, '999,999,999') from dual;
# returns 123456, 000123456, 123,456 - 0은 자릿수가 맞지 않으면 0으로 채우고, 9는 채우지 않음

 

• to_number는 숫자형으로 데이터를 변환

ex)

SELECT to_number('20000', '99999') - to_number('10000', '99999') from dual;
# returns 10000

 

View

Create [or replace] [force | noforce] view view_name
as subquery
[with check option]
[with read only];

• 'force' 옵션을 사용하면 base table의 존재 여부와 상관없이 뷰를 생성할 수 있음 / 기본값은 noforce
• 'with check option'을 사용하면 view로 볼 수 있는 value만 넣을 수 있음 / update도 마찬가지
• 'with read only' 옵션을 사용하면, 뷰에 대하여 검색만 가능하고 뷰를 통한 base table 변경은 불가능

 

Materialized View

• 단순한 가상 뷰가 아닌, 실제로 결과를 저장하는 객체, 즉 실제 테이블처럼 동작하는 뷰

구분	일반 View	Materialized View
저장 방식	실시간 계산 (데이터 저장 안 함)	결과를 저장 (테이블처럼 저장됨)
속도	느릴 수 있음 (매번 조회 계산)	빠름 (조회 시 계산 안 함)
갱신 방식	원본 데이터 바뀌면 자동 반영	직접 Refresh 필요
사용 예	단순 조인, 필터링	복잡한 조인, 집계, 오프라인 리포트 등

 

ex) 실제로 mv_sales_summary라는 테이블이 생긴 것처럼 사용 가능함

CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE
REFRESH ON DEMAND
AS
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

• 단, sales 테이블에 데이터가 바뀌어도 자동 반영 안 됨

 

Recursive queries

• Oracle 시스템은 순환 질의를 지원함

c(course) - 과목, p(prerequisite) - 선수과목

With recu(x,y) as (
(SELECT c, p from course) # 기본적으로 모든 course 테이블을 x, y로 넣음
union all
(select x, р
from recu, course c
where lower(y)=lower(c))) # recu.y와 course.c가 같으면 recu.x와 course.p를 연결
SELECT * from recu
order by х;

SELECT y from recu
where x='DB II';
# returns 'DB I', 'file processing', 'data structure', 'C language'

 

System privilege vs Object privilege

시스템 권한 (System Privilege)

• 정의: 특정 행위 자체를 수행할 수 있는 권한
• 모든 객체나 스키마에 대해 전역적으로 허용됨
• ex) CREATE TABLE, DROP VIEW, ALTER ANY TABLE, SELECT ANY TABLE, CREATE ANY SEQUENCE, EXECUTE ANY PROCEDURE 등
• 대상 객체를 지정하지 않아도 됨
• DB 관리자(DBA)들이 주로 가짐
• 'with admin option'으로 권한 부여

With admin option

객체 권한 (Object Privilege)

• 정의: 특정 **객체(테이블, 뷰 등)**에 대해 수행할 수 있는 권한
• 대상 객체가 반드시 명시되어야 함
• ex) 특정 테이블에 대한 SELECT, INSERT, UPDATE, DELETE / 특정 프로시저에 대한 EXECUTE
• 권한 + 객체가 함께 지정됨 (예: GRANT SELECT ON emp TO user1;)
• 'with grant option'으로 권한 부여

With grant option

 

Synonym (동의어)

항목	설명
목적	다른 사용자 객체에 접근 시 스키마명을 생략하고 간편하게 사용하기 위해
종류	- Private synonym: 생성자만 사용 가능 - Public synonym: 모든 사용자 사용 가능
사용 예시	CREATE [PUBLIC] SYNONYM myStudent FOR user1.student; SELECT * FROM myStudent;

 

• 동의어(Synonym)는 특정 객체(테이블 등)에 대한 별칭(alias)을 지정하는 기능
• 객체 접근 시 "스키마.객체" 형식을 생략 가능하게 함
• Public synonym은 SYS 스키마에 생성되며 모든 사용자가 사용 가능
• 권한이 있는 객체에 대해만 synonym을 만들어도 접근 가능
• ex) dual은 public synonym이라서 sys.dual이 아닌 그냥 dual로 사용 가능

 

 

Trigger (트리거)

Create [or replace] trigger <trigger name>
[before | after | instead of]
[insert | update | delete ...] on <object name>
[for each row]
[when <condition for trigger to get execute>]
declare
	<declaration part>
begin
	<execution part>
exception
	<exception handling part>
end;

항목	설명
트리거 시점	BEFORE / AFTER / INSTEAD OF
작동 조건	INSERT / UPDATE / DELETE 발생 시 자동 실행
범위	FOR EACH ROW (행 단위), 또는 생략하면 문장 단위
언어	PL/SQL로 작성, DECLARE, BEGIN ... END, EXCEPTION 블록 구성
실행 주의	트리거 실행 결과 확인하려면 SET SERVEROUTPUT ON 먼저 실행 ( DBMS_OUTPUT.PUT_LINE )
수정 방법	CREATE OR REPLACE 사용하여 기존 트리거 수정 가능

ex)

Create or replace trigger trig1 after update of salary on
myprofessor
for each row
when (new.salary is not null)
begin
	DBMS_OUTPUT.PUT_LINE('Value before update: ' || :old.salary);
	DBMS_OUTPUT.PUT_LINE('Value after update: ' || :new.salary);
end;



Create or replace trigger trig2 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;

• ':' 바인드 변수(콜론)는 PL/SQL 트리거나 프로시저 안에서 전/후 행의 값을 참조하는 특별한 식별자
• :NEW, :OLD, :n, :o는 "이건 데이터행에서 가져온 값이다" 라고 Oracle에 알려주는 역할을 함
• 트리거, 프로시저, PL/SQL 블록 내부에서는 :를 붙여야 Oracle이 트리거 전후 값을 올바르게 인식함