10. ORACLE :: oracle 내 객체 활용 정리(object)

 

오라클에서도 객체가 있다.

자세히 알아보자.

1. VIEW

select 쿼리의 실행 결과를 화면에 저장한 논리적 가상 테이블

실제 테이블과 다르게 데이터가 저장되어있진 않지만, 실제 테이블하고 링크되는 것이기 때문에 사용자는 테이블과 동일하게 이용할 수 있다.

💡 with와의 차이점
with로 생성된 내용은 해당 쿼리문 내에서만 쓸 수 있다.
view는 drop할 때까지 사용할 수 있다.

 

 

1) 생성 방법

기본 create, resource 권한에 view(object) 생성은 포함되어있지 않다. → 권한 부여 필수

• GRANT CREAT VIEW TO 사용자명

CREATE VIEW 이름(컬럼명, ...) AS SELECT문

• 뒤 select문에서 생성한 view의 이름으로 호출해서 사용가능

CREATE VIEW V_AVG_SALARY
AS SELECT '부서' AS DIV, DEPT_CODE AS CDOE, DEPT_TITLE AS TITLE, FLOOR(AVG(SALARY)) AS AVG_SAL
FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_ID = DEPT_CODE
GROUP BY DEPT_CODE, DEPT_TITLE
UNION
SELECT '직책' AS DIV, JOB_CODE, JOB_NAME, FLOOR(AVG(SALARY)) AS AVG
FROM EMPLOYEE JOIN JOB USING(JOB_CODE)
GROUP BY JOB_CODE, JOB_NAME;
--drop view v_avg_salary;
--위와 같이 긴 코드를 저장해놓고 아래 한줄로 간단하게 조회할 수 있다.

SELECT * FROM V_AVG_SALARY;

생성하면서 옵션 적용이 가능하다

OR REPLACE : 이미 있으면 덮어쓰기, 아니면 새로 생성하는 구문

create or replace view v_emp
as select * from department;

FORCE / NOFORCE

: 실제 테이블이 없어도 view를 생성할 수 있게 해주는 옵션

기본적으로는 noforce 상태인데, force 명시해주면 나중에 create할 수 있게 된다.

create force view v_tt
as select * from tt;
select * from v_tt; -- BS.V_TT에 오류가 있습니다
select * from user_views; -- 여기엔 추가됨
create table TT(
    name varchar2(20),
    count number
);
select * from v_tt; -- 조회 잘됨

WITH CHECK OPTION → where 절 마지막에 작성

select문의 where절에 사용한 컬럼값을 수정하지 못하게 하는 옵션

create or replace view v_check
as select emp_id, emp_name, salary, dept_code
from employee
where dept_code= 'D5';
update v_check set dept_code = 'D6' where emp_id = 215; --적용 잘됨

create or replace view v_check
as select emp_id, emp_name, salary, dept_code
from employee
where dept_code= 'D5' with check option;
update v_check set dept_code = 'D6' where emp_id = 215; --에러 발생

WITH READ ONLY : 수정 못하게 만드는 옵션 → where절 마지막 작성

create or replace view v_check
as select emp_id, emp_name, salary, dept_code
from employee
where dept_code= 'D5' with read only;
-- 수정 시도시 읽기 전용 뷰라는 에러 발생

2) 생성된 view들 조회

오라클이 제공하는 테이블 이용

select * from user_views;

3) insert 문

보통 한번 생성하기 복잡한 테이블의 조회 용도로 쓰기 때문에 DML(insert, update, delete) 구문과는 함께 사용하지 않는 편이다. ex) 집합연산을 가지는 select문, 통계쿼리문, 가상 컬럼이 포함된 select문

단일 테이블로 생성한 view 테이블에는 insert 사용이 가능하다.

단, 해당 단일 테이블 컬럼 중 not null 제약조건이 설정되어있으면 불가능

또는 JOIN, UNION 등 사용시 불가능

• view table에 insert 사용시 실제 컬럼에 데이터가 추가되는데, 세팅하지 않은 값들은 null로 입력되기 때문에 지양하는 편이 좋다. 다르게 보면 실제 데이터가 추가되면 해당 view 값도 바뀐다.

create view v_emp_test
as select emp_id, emp_no, emp_name, email, phone, job_code, sal_level
from employee;

-- 값을 insert 하면?
insert into v_emp_test values(999, '010810-123456', '박댕댕', '박@점컴', '1234', 'J4', 'S3');
select * from employee; -- 실제 테이블에 추가됨, 지정안한 컬럼은 null값으로 추가.
select * from v_emp_test; -- 결과 바뀜

4) update문

실제 데이터와 링크 되어있는 컬럼값은 수정 가능

5) delete문

실제 데이터와 링크 되어있는 컬럼값은 수정 가능

 

2. SEQUENCE

순차적으로 정수 값을 자동으로 생성하는 객체, 즉 자동 번호 발생기와 동일

순번 발급 및 출력시 유용

보통 pk값으로 많이 이용한다.(, 2024_001…)

*@ P_001형*
select 'P_'||lpad(seq_pknumber.nextval,3, '0') from dual;
@ 2024_001형
select extract(year from sysdate) || '_' || seq_pknumber.nextval from dual;

1) 기본 시퀀스 발생

CREATE SEQUENCE 명칭;

→ 1부터 1씩 증가하는 번호를 발생시킴 (1~9999999999999999999999999999, 캐시 20)

2) 예약어

NEXTVAL와 CURRVAL

• 시퀀스명.NEXTVAL → 번호 1개 발생

select seq_basic.nextval from dual;

• 시퀀스명.CURRVAL → 현재 번호 확인
  • 단, nextval이 선행되어야만 실행할 수 있음

  select seq_basic.currval from dual;
  

사용가능 상황 사용불가 상황

사용가능 상황	사용 불가 상황
서브쿼리 아닌 select문	view의 select절
insert의 select절	distinct 키워드가 있는 select문
insert의 value절	group by, having, order by가 있는 select문
update문의 set절	select, delete, upadate 서브쿼리
	create table, alter table 명령의 디폴트값

반응형

3) 시퀀스 옵션 활용하기

CREATE SEQUENCE + 아래 옵션들을 같이 써주면 된다.

start with를 제외하면 옵션 변경이 가능하다 → 수정해서 다시 실행하면됨

(1) START WITH

번호 시작값 설정, 디폴트 : 1

단, 한번 설정 후 변경 불가하므로 변경하려면 삭제 후 다시 생성해야함

create sequence seq_basic2
start with 100;

(2) INCREAMNET BY

증가되는 숫자값(간격), 음수도 가능함. 디폴트: 1

create sequence seq_basic3
start with 100
increment by 10;

(3) MAXVALUE & MINVALUE

둘은 항상 같이 써야함

• MAX 증가하는 번호의 최대값 양수, 디폴트: 9999999999999999999999999999(오라클에서 표현가능한 최대정수값)
• MIN : 번호의 최소값, 음수도 가능함
• create sequence seq_basic4 start with 100 increment by 10 maxvalue 200 minvalue 0;

(4) CYCLE | NOCYCLE

번호가 최대값이 됬을 때 순환 여부를 결정함

디폴트: nocycle → 다되면 에러 발생

create sequence seq_basic4
start with 100
increment by 10
maxvalue 200
minvalue 0
cycle;

(5) CACHE | NOCACHE

미리 번호를 생성해놓는 기능 → 속도가 빨라짐

디폴트: 20

캐시 : 특정 수치에 달했을 때 한번에 제공 특정 row를 구분할 때 많이 사용

4) 시퀀스로 계층형 쿼리문 만들기

• 댓글과 대댓글 등 작성시 사용

select * from employee;
SELECT LEVEL, EMP_ID, EMP_NAME, MANAGER_ID
FROM EMPLOYEE
    START WITH EMP_ID = 200 
    CONNECT BY PRIOR EMP_ID = MANAGER_ID;
    
select level || '' || lpad('', (level-1) * 5, '') || emp_name || nvl2(manager_id, '('||manager_id||')', '') 조직도
from employee
    start with emp_id = 200
    connect by prior emp_id = manager_id
    order by level;

5) 시퀀스 수정

drop후 다시 생성해주는 것이 가장 정확하다.

시작번호는 최종 진행한 번호의 다음 번호로 세팅한다. → start with