1. view
2. sequnce
3. index
4. trigger
5. role

view

stored view이름을 붙이고 저장한다

• 하나 이상의 테이블에서 원하는 데이터를 선택하여 새로운 가상 테이블을 만들어 주는 것
• 다른 테이블에 있는 데이터를 보여줄 뿐이며 데이터 자체를 포함하고 있는 것은 아님
• 저장장치 내에 물리적으로 존재하지 않고 가상 테이블로 만들어짐
• 물리적인 실체 테이블과의 링크 개념
• view가 어디서?
  • 서브쿼리가 from뒤에 들어가면 inlin view
• view를 만들기 위해서는 권한이 필요한 resource에는 view를 만드는 권한이 없음
• create view라는 권한을 계정에 부여해줘야 한다

  --시스템계정에 선언할것!
  grant create view to 아이디명;
  commit;

create view emp_gender
as select emp_name, emp_no, decode(substr(emp_no,8,1),1,'남',3,'남','여')"성별" from employee;
--가상테이블 생성

select * from emp_gender
where "성별"='여';
--생성한 테이블 조회

• 뷰는 보안을 목적으로 만들어졌다

  • 보여주고싶지 않은 정보를 감출때

    * *DML(INSERT,UPDATE,DELETE) 명령어로 조작이 불가능한 경우

  • 기본적으로 view는 dml이 가능하지만 아래의 경우에는 불가능함

  1. 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우. 가상컬럼에는 데이터를 추가할 수 없다.
  2. INSERT시에 뷰에 포함되지 않은 컬럼 중에 NOT NULL 제약조건이 지정된 경우
  3. 산술 표현식을 정의된 경우. 연봉을 역추적해서 salary와 bonus컬럼값을 구하지 못함.
  4. JOIN을 이용해 여러 테이블을 연결한 경우
  5. DISTINCT를 포함한 경우
  6. 그룹함수나 GROUP BY 절을 포함한 경우

view 옵션

1. or replace

   1. 생성한 뷰가 존재하면 뷰를 재 생성함

   create view emp_join_info
   as select emp_name, dept_title from employee left join department on dept_code = dept_id;
   --생성완료
   
   create or replace view emp_join_info
   as select emp_name, dept_title, phone from employee left join department on dept_code = dept_id;
   
   --phone을 추가하면 테이블을 지우고 phone을 추가해서 다시 생성해줌

2. force/noforce

   1. force옵션은 기본 테이블이 존재하지 않더라도 뷰를 생성함

   2. noforce는 기본테이블이 없으면 생성하지 않음

      create force view emp_view
      as select emp_id, emp_name, salary from nono;
      
      select * from emp_view;
      -- 없는 표를 가지고와서 강제로 생성은 가능하지만 실제로 조회해보면 오류가 뜸

   • 잘 사용하지 않음

3. with check option

   1. where절 조건의 사용한 컬럼의 값을 수정하지 못하게 함

   create or replace view emp_view_D5
   as select emp_id, emp_name, salary, dept_code from employee
   where dept_code = 'D5' **with check option**;
   
   -- dept_code 칼럼 수정불가코드
   
   update emp_view_D5 set dept_code='D2'
   where salary >= 2500000;
   --D2로 수정요청
   -- 오류
   ORA-01402: view WITH CHECK OPTION where-clause violation

4. with read only

   • 특정 컬럼이 아니라 아예 삽입, 수정, 삭제 등을 하지 못하게 함

     create or replace view emp_view_D5
     as select emp_id, emp_name, salary, dept_code from employee
     where dept_code = 'D5' **with read only**;
     --수정 삭제 불가 코드
     
     update emp_view_d5 set salary = salary+500000;
     --salary 추가요청
     --오류
     SQL 오류: ORA-42399: cannot perform a DML operation on a read-only view
     42399.0000 - "cannot perform a DML operation on a read-only view"

data dictionary

1. user_xxxx

   1. 자신이 소유한 객체 등에 관한 정보 조회 가능
   2. 앞으로 배울 객체들의 메타데이터들이 데이터딕셔너리에서 관리될 것임
   3. 사용자가 아닌 DB에서 자동생성/관리해주는 것이며 user_뒤에 객체명을 써서 조회함

2. all_xxxx

   1. 자신의 계정이 소유하거나 권한을 부여받은 객체 등에 관한 정보 조회가능

3. DBA_XXXX

   1. 데이터베이스 관리자만 접근이 가능한 객체 동의 정보 조회 가능
   2. (dba는 모든 접근이 가능하므로 결국 DB에 있는 모든 객체에 대한 조회가능)
   3. 일반사용자는 조회 권한 없음

Sequence

• 순차적으로 정수 값을 자동으로 생성하는 객체로, 자동 번호 발생기(채번기)의 역할을 함

create sequence seQ_user_no
start with 1 --생략가능
increment by 1  -- 생략가능
maxvalue 10000 --nomaxvalue, 생략가능
cycle --nochycle --생략가능
nocache;

create sequence seq_user_no2
start with 1
increment by 1
nomaxvalue
nocycle
nocache;

INSERT INTO SHOP_MEMBER
VALUES(seq_user_no.nextval, 'user01', 'pass01',  '01000000000', 'user01@iei.or.kr');
INSERT INTO SHOP_MEMBER
VALUES(SEQ_USER_NO.NEXTVAL, 'user02', 'pass02',  '010000004', 'user02@iei.or.kr');
INSERT INTO SHOP_MEMBER
VALUES(SEQ_USER_NO.NEXTVAL, 'user03', 'pass03', '010000000', 'user03@iei.or.kr');
INSERT INTO SHOP_MEMBER
VALUES(SEQ_USER_NO.NEXTVAL, 'user04', 'pass04', '01000000000', 'user03@iei.or.kr');

values(시퀀스명.nextval,밸류값..)을하면 시퀀스가 1씩 증가한다

하지만 입력에 실패했을대도 시퀀스값은 상승했기때문에 실패시에도 1이 카운팅된다.

select seq_user_no.currval from dual;
--현재 시퀀스 값 확인

• 롤백하거나 표를 삭제한다고 해서 시퀀스 값이 돌아가서 0부터 카운트되지 않는다
• 시퀀스값은 초기화되지 않기때문에 초기화를 원하면 지운뒤 다시 만들어야 한다

-- 해당 시퀀스의 다음값
SELECT testSeq.NEXTVAL FROM DUAL; 
 
-- 해당 시퀀스의 현재값
SELECT testSeq.CURRVAL FROM DUAL; 
 
--INSERT에서의 시퀀스 다음값
INSERT INTO oracleStudy VALUES(testSeq.NEXTVAL, 'studyName' , 'class' , A);

-- 시퀀스 생성
CREATE SEQUENCE SEQ_USER_NO
START WITH 1   -- 생략가능
INCREMENT BY 1  -- 생략가능
NOMAXVALUE      -- 생략가능
NOCYCLE         -- 생략가능
NOCACHE;        -- 생략가능
 
CREATE SEQUENCE SEQ_USER_NO; -- 기본값으로 생성됨
-- 시퀀스 확인
SELECT * FROM USER_SEQUENCES;
-- 시퀀스 이용 데이터 삽입
INSERT INTO SHOP_MEMBERVALUES
(SEQ_USER_NO.NEXTVAL, 'khuser01', 'pass01', '01092928383', 'khuser01@iei.or.kr'
);
INSERT INTO SHOP_MEMBERVALUES(SEQ_USER_NO.NEXTVAL, 
'khuser02', 'pass02', '01082830494', 'khuser02@iei.or.kr');
INSERT INTO SHOP_MEMBERVALUES(SEQ_USER_NO.NEXTVAL, 'khuser03', 'pass03', 
'01092983939', 'khuser03@iei.or.kr');
INSERT INTO SHOP_MEMBERVALUES(SEQ_USER_NO.NEXTVAL, 'khuser04', 
'pass04', '01092444939', 'khuser04@iei.or.kr');

-- 시퀀스 현재값 조회
SELECT SEQ_USER_NO.CURRVAL FROM DUAL;

-- INSERT 오류가 나더라도 시퀀스의 값은 증가함
-- 해당 시퀀스의 현재값을 조회하기 위해 CURRVAL을 사용해야함
-- 시퀀스 수정ALTER SEQUENCE SEQ_USER_NOINCREMENT BY 10;           
-- 시퀀스 증가폭 수정-- 시퀀스 삭제DROP SEQUENCE SEQ_USER_NO; 
-- 초기값은 수정이 안되기 때문에 지웠다가 다시 만들어야함

• nextval, currval를 사용할수 있는 경우
  • 서브쿼리가 아닌 select절
  • insert문의 select절
  • insert문의 values절
  • update문의 set절
• nextval, currval을 사용할 수 없는 경우
  • view의 select절
  • distinct 키워드가 있는 select절
  • group by, having order by절이 있는 select문
  • select delete update의 서브쿼리
  • create table, alter talbe명령의 default값

INDEX

• 색인

• sql명령문의 처리 속도를 향상 시키기 위해서 컬럼에 대해 생성하는 오라클 객체!!!!

• key-value형태로 생성이 되며 key에는 인덱스로 만들 컬럼 값 value에는 행이 저장된 주소 값이 저장됨

• 장점: 검색 속도가 빨라지고 시스템에 걸리는 부하를 줄여서 시스템 전체 성능을 향상 시킬 수 있음

• 단점 :

  • 인덱스를 위한 추가 저장 공간이 필요하고 인덱스를 생성하는 시간이 걸림
  • 데이터의 변경 작업(insert/update/delete)이 자주 일어나는 테이블에 index생성시 오히려 성능 저하가 발생할 수 있음

• 어떤 컬럼에 인덱스를 만들면 좋을까?

  • 데이터 값이 중복된 것이 없는 고유한 데이터 값을 가지는 컬럼에 만드는 것이 제일 좋다
  • 선택도(selectivity)가 좋은 걸림의 예
  • 좋은 예 : 사원 번호, 주민 번호, 사원 명(중복이 있을 수 있으니 주의)
  • 나쁜 예 : 성별, 부서코드, 직급, 나이

• 효율적인 인덱스 사용 예

  • where절에 자주 사용되는 컬럼에 인덱스 생성
    • 전체 데이터 중에서 10~15% 이내의 데이터를 검색하는 경우, 중복이 많지 않은 컬럼 이어야 함
    • 두 개 이상의 컬럼 where절이나 join조건으로 자주 사용되는 경우
    • 한번에 입력된 데이터의 변경이 자주 일어나지 않는 경우
    • 한 테이블에 저장된 데이터 용량이 상당히 큰 경우

• 비 효율적인 인덱스 사용 예

  • 중복 값이 많은 칼럼에 사용된 인덱스
  • null값이 많은 컬럼
    • 인덱스의 수는 제한이 없으나 너무 많으면 오히려 성능저하

• 인덱스 조회

select * from user_indexes 
where table_name = 'employee';

• 인덱스 생성

create index index_1 on employee(emp_id, emp_no);
--인덱스는 컬럼에 건다

• 이렇게 인덱스를 걸어주면 검색 시 더 빨라진다
• 인덱스 삭제

drop index inx_emp_name;

Role

• 사용자에게 여러 개의 권한을 한번에 부여 할 수 있는 데이터 베이스 객체
• 사용자에게 권한을 부여할 때 한개 씩 부여하게 된다면 권한 부여 및 회수에 따른 관리가 불편함
  • GRANT CONNECT RESOURCE TO 아이디
    • 지금까지 했던 권한 부여
• 권한과 관련된 명령어는 반드시 SYSTEM에서 수행!

GRANT CONNECT, RESOURCE TO KH; -- 지금 부여했던 것은 ROLE 객체를 통해 권한을 부여했던것

CREATE ROLE EMP_ROLE;
-- 나만의 ROLE객체를 만들어서
--EMP_ROLE이라는 ROLE객체 생성

GRANT SELECT ON KH.EMPLOYEE TO EMP_ROLE;
-- 해당 ROLE객체에 권한을 부여하고
--EMP_ROLE에 KH.EMPLOYEE를 SELECT할수있는 권한을 부여

GRANT EMP_ROLE TO KHUSER;
-- 나만의 ROLE을 사용자 계정에 권한부여를 해봄.
-- EMP_ROLE을 KHUSER에 부여

REVOKE EMP_ROLE FROM KHUSER;
--KHUSER에게 주었던 EMP_ROLE의 권한을 삭제