Oracle 기초 2

오라클- 테이블 무결성 제약조건

 * 데이터 무결성 제약조건(Data Interity Constraint Rule)

- 테이블에 부적절한 자료(데이터)가 입력되는것을 방지하기 위해

테이블을 생성할때 각 컬럼에 대해서 정의하는 여러가지 규칙을 의미.

- not null / null : null을 허용할 지 아니면 반드시 입력받게 할 건지의 조건.

- Unique: 지정된 컬럼에 중복되지 않고 유일한 값만 저장되는 조건.

- Check : 특정한 값만 저장되는 필드 조건.

- Default : 기본값으로 특정값이 저장되도록 설정하는 조건.

- Foreign Key(외래키) : 다른 테이블의 컬럼에 들어있는 값만 저장을 허용.(다른 테이블 참조)

- Primary Key(키본키) : unique + not null

* USER_CONSTRAINTS : 데이터 딕셔너리 뷰 - 제약조건에 관한 정보를 알려 줌.

-owner : 제약조건을 소유한 사용자명을 저장하는 컬럼.

-constraint_name : 제약 조건명을 저장하는 컬럼.

-constraint_type : 제약 조건 유형을 저장하는 컬럼.

.P - primary key

.R - foreign key

.U - unique

.C - check, not null

- table_time : 각 제약조건들이 속한 테이블 이름

- search_condition : 어떤 내용이 조건으로 사용되었는지 설명

- r_constraint_name: 제약조건이 외래키인 경우에 어떤 기본키를 참조했는지에 대한 정보

sql> a18 사이즈로 보여줌

column constraint_type format a18

sql>

select constraint_name, constraint_type, table_name

from user_constraints;

 * USER_CONS_COLUMNS 데이터 딕셔너리 뷰 : 어떤 컬럼에 어떤 제약조건이 지정되었는지 알려주는

데이터 딕셔너리.

폭 줄이기

sql> 

column owner format a10

sql>

column table_name format a15

sql> 

column column_name format a15

sql>

colum  constraint_name format a15

sql>

select * from USER_CONS_COLUMNS;

===================================================

=-=-=컬럼 레벨 제약 조건 지정

-- 1. not null 제약조건을 설정하지않고 테이블 생성

sql>

drop table emp01 purge;

sql>

create table emp01(

empno number(4),

ename varchar2(10),

job varchar2(10),

deptno number(2)

);

-- null값 입력 연습

sql>

insert into emp01 values(null, null, 'SALESMAN', 40);

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- 2. not null 제약조건을 설정 후 테이블 생성

sql>

create table emp02(

empno number(4) not null,

ename varchar2(10) not null,

job varchar2(10),

deptno number(2)

);

sql>

insert into emp02 values(8000, 'SUPERMAN', 'SALESMAN', 40);

insert into emp02 values(8001, 'ACORN', null, 40);

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --- -- -- -- -- -- -- -

-- 3. unique 제약조건 설정

sql>

create table emp03(

empno number(4) unique,

ename varchar2(10),

job varchar2(10),

deptno number(2)

);

sql>

insert into emp03 values(8282, 'SMITH', 'MANAGER', 10);

-- 4. 컬럼 레벨로 제약조건 명시

-사용자가 제약조건명 명시하지않고 제약조건만 명시할 경우 오라클 서버가 제약조건명을 명시

-오라클이 부여하는 제약조건명은 SYS_ 다음에 숫자를 나열한다.

-어떤 제약조건을 위배했는지 알수 없기에 사용자가 의미있게 제약조건명을 명시 할 수있도록

오라클은 제공

sql>

// 컬럼 명 constraint 테이블명_컬럼_제약조건약어

create table emp04(

empno number(4) constraint emp04_empno_uk  unique,

ename varchar2(10) constraint emp04_ename_nn not null,

job varchar2(10),

deptno number(2)

);

sql>

insert into emp04 values(5882, 'ALLEN', 'SALESMAN', 30);

sql>

insert into emp04 values(5882, 'JOHN', 'MANAGER', 20);

->무결성 제약 조건(SCOTT .EMP04_EMPNO_UK)에 위배됩니다. 라고 보여줌.

-- 5. Primary Key 제약 조건 설정하기

sql>

create table emp05(

empno number(4) constraint emp05_empno_pk  primary key,

ename varchar2(10) constraint emp05_ename_nn not null,

job varchar2(10),

deptno number(2)

);

sql>

insert into emp05 values(7942, 'ALLEN', 'SALESMAN', 30);

// pk -> 중복, null값 입력x

-- 6. 참조 무결성을 위한 Foreign Key(외래키) 제약조건

-부모키가 되기 위한 컬럼은 반드시 부모 테이블의 기본키(PK)이거나, 유일키(unique)로 설정되있어야 함.

sql>

drop table dept01 purge;

sql> 부모 테이블 생성

create table dept01(

deptno number(2) constraint dept01_deptno_pk primary key,

dname varchar2(14),

loc varchar2(14)

);

sql>

insert into dept01 values(10, 'ACCOUNTING', 'NEW YORK');

insert into dept01 values(20, 'RESEARCH', 'DALLAS');

insert into dept01 values(30, 'SALES', 'CHICAGO');

sql> 자식 테이블 생성

create table emp06(

empno number(4) constraint emp06_empno_pk primary key,

ename varchar(10) constraint emp06_ename_nn not null,

job varchar2(10),

deptno number(2) constraint emp06_deptno_fk references dept01(deptno)

);

sql>

insert into emp06 values(8282, 'ALLEN', 'SALESMAN', 30);

insert into emp06 values(7942, 'JOHN', 'MANAGER', 40);  //부모 테이블에 40없음

-- 7. Check 제약조건

-급여 컬럼을 생성하되 값은 500~5000 사이의 값만 저장가능.

-성별 저장 컬럼은 gender를 정의하고, 'M', 'F' 둘 중하나만 저장 가능.

sql>

create table emp07(

empno number(4) constraint emp07_empno_pk primary key,

ename varchar(10) constraint emp07_ename_nn not null,

sal number(7, 2) constraint emp07_sal_ch check(sal between 500 and 5000),

gender varchar2(1) constraint emp07_gender_ch check(gender in ('M', 'F'))

);

sql>

insert into emp07 values(7477, 'ALLEN', 200, 'M'); -> x

insert into emp07 values(7477, 'ALLEN', 1000, 'A'); -> x

insert into emp07 values(7477, 'ALLEN', 1000, 'M');

-- 8. Default 제약 조건

-지역(loc) 컬럼에 아무 값도 입력하지 않을 때 디폴트값인 'SEOUL'이 입력되도록 디폴트 제약조건 지정.

sql>

drop table dept02 purge;

sql>

create table dept02(

deptno number(2) primary key,

dname varchar2(14),

loc varchar2(14) default 'SEOUL'

);

sql>

insert into dept02(deptno,dname) values(10, 'ACCOUNTING');

insert into dept02 values(20, 'RESEARCH', 'NEW YORK');

-- 9. 테이블 레벨 방식으로 제약조건 설정

-@@not null 제약조건은 테이블 레벨방식으로 제약조건을 할수 없다.

sql>

create table emp09(

empno number(4),

ename varchar2(10) constraint emp09_ename_nn not null,

job varchar2(10),

deptno number(2),

primary key(empno),

unique(job),

foreign key(deptno) references dept01(deptno)

);

sql>

create table emp10(

empno number(4),

ename varchar2(10) constraint emp10_ename_nn not null,

job varchar2(10),

deptno number(2),

constraint emp10_empno_pk primary key(empno),

constraint emp10_unique_uk unique(job),

constraint emp10_deptno_fk foreign key(deptno) references dept01(deptno)

);

-- 10. 제약조건 추가 설정

sql>

alter table emp01 add constraint emp01_empno_pk primary key(empno);

sql>

alter table emp01 add constraint emp01_deptno_fk foreign key(deptno) 

references dept01(deptno);

sql>

insert into emp01 values(null, null, 'MANAGER', 40); ->x

insert into emp01 values(7777, null, 'MANAGER', 40); ->x

insert into emp01 values(7777, null, 'MANAGER', 30);  

-- 11. not null 제약조건 추가 설정

sql>

alter table emp01 modify ename constraint emp01_ename_nn not null;

-- 12. 제약 조건 삭제

sql>

alter table emp01

drop primary key;

sql>

alter table emp01

drop constraint emp01_ename_nn;

-- 13. 제약조건 외래키 삭제

sql>

delete from dept01

where deptno = 30; -> error

1) 제약조건의 비활성화

-자식 테이블인 사원 테이블(emp06)은 부모 테이블(dept01)의 기본 키인

부서번호를 참조하고 있다.

-부서 테이블의 30번 부서는 사원 테이블에 근무하는 30번 사원이 존재하기 때문에 삭제 불가.

-부모 테이블의 부서 번호 30번이 삭제되면 자식 테이블에서 자신이 참조하는 부모를 잃어버리게 되므로

삭제 할 수 없다.

삭제할려면

(1) 부서 테이블의 30번 부서에서 근무하는 사원들을 삭제 한 후 부서테이블에서 30번 부서를 삭제.

(2) 참조 무결성 때문에 삭제가 불가능 하므로 emp06테이블의 외래키 제약 조건을 제거 한 후 30번 부서 삭제

--제약 조건 비활성화

(1) 테이블에서 제약조건을 삭제하지 않고 일시적으러 적용시키지 않도록하는 방법으로

제약 조건을 비활성화

(2) .DISABLE_CONSTRAINT : 제약 조건의 일시 비활성화 

    .ENABLE_CONSTRAINT : 비활성화된 제약 조건을 해제하여 다시 활성화

sql>

alter table emp06 disable constraint emp06_deptno_fk;

sql>

delete from dept01

where deptno = 30;

sql>

alter table emp06 enable constraint emp06_deptno_fk;

2) cascade 옵션

- 부모 테이블과 자식테이블간의 참조 설정이 되어 있을때 부모테이블의 제약조건을 비활성화하면

이를 첨조하는 저식 테이블의 조약 조건까지 비활성화

sql>

alter table dept01

disable primasry key cascade;

==================================================

==================================================

오라클 시퀀스(sequence)

: 테이블 생성 후 시퀀스를 따로 만들어낸다.

-- 1. 샘플테이블 생성

sql>

create table memos(

num number(4) primary key,

name varchar2(14) not null,

postDate Date default (sysdate)

);

-- 2. 해당 테이블에 시퀀스를 생성

sql>

create sequence memos_seq

start with 1 increment by 1;

-- 3. 데이터 입력 : 시퀀스 포함

--sql>

insert into memos(num, name)

values(memos_seq.nextval, '홍길동');

--sql>

insert into memos(num, name)

values(memos_seq.nextval, '홍길서');

--sql>

insert into memos(num, name)

values(memos_seq.nextval, '홍길남');

--sql>

insert into memos(num, name)

values(memos_seq.nextval, '홍길북');

-- 4. 현재 시퀀스가 어디까지 증가되었는지 확인

sql>

select memos_seq.currval from dual;

-- 5. 시퀀스 수정: 최대 증가값을 6까지로 제한

sql>

alter sequence memos_seq maxvalue 8;

-- 6. 시퀀스 삭제

sql>

drop sequence memos_seq;

cf.) 시퀀스 생성 권한 부여

- sys/ system 으로 접속 후

SQL>

grante create sequence to 사용자명;

====================================================

====================================================

오라클 트랜잭션(transation)

-commit : 명령어 완전 실행

-rollback : 명령어 되돌리기

-- 1. 테이블 생성

sql>

create table trans(

num number(2) primary key,

name varchar2(20) not null,

email varchar2(20),

title varchar2(150),

postDate Date default sysdate,

postIP varchar2(20)

);

-- 2. 시퀀스 생성

sql>

create sequence trans_seq

start with 1 increment by 10;

sql>

insert into trans values(trans_seq.nextval, '홍길동', 'a@a.com', '배고프다', sysdate, '192.168.0.9');

insert into trans values(trans_seq.nextval, '홍길서', 'a@a.com', '나도', sysdate, '192.168.0.19');

insert into trans values(trans_seq.nextval, '홍길남', 'a@a.com', 'ㅇㅇㅇㅇ', sysdate, '192.168.0.29');

insert into trans values(trans_seq.nextval, '홍길북', 'a@a.com', '아ㅏ아아ㅏ', sysdate, '192.168.0.39');

===================================================================

===================================================================

오라클 - 뷰(View)

- 물리적인 테이블에 근거한 논리적인 가상 테이블.

- 가상이란 단어는 실질적으로 데이터를 저장하고 있지 않기 때문에 붙인 것이고,

   테이블이란 단어는 실질적으로 데이터를 저장하고 있지는 않지만 사용자는 마치

   테이블을 사용하는 것과 동일하게 뷰를 사용할 수 있기 때문에 붙인 것.

- 뷰는 기본 테이블에서 파생된 객체로서 기본 테이블에 대한 하나의 쿼리문임.

- 실제 테이블에 저장된 데이터를 뷰를 통해서 볼 수 있도록 함.

- 사용자에게 주어진 뷰를 통해서 기본 테이블을 제한적으로 사용하게 됨.

- 뷰는 이미 존재하고 있는 테이블에 제한적으로 접근하도록 한다.

- 뷰를 생성하기 위해서는 실질적으로 데이터를 저장하고 있는 물리적인 테이블이

   존재해야 하는데 이 테이블을 기본 테이블이다.

//뷰 테이블 생성권한

-시스템계정으로 grant create view to scott;

-- 1. 뷰의 기본 테이블 생성

: DEPT 테이블의 복사본을 dept_copy으로 생성함.

sql>

create table dept_copy

as

select * from dept;

sql> emp 카피 테이블 생성

create table emp_copy

as

select * from emp;

-- 2. 뷰 정의하기

- 뷰를 정의하기 위해서는 create view로 시작함. as 다음엔 서브쿼리문과 유사함.

- 서브 쿼리에는 지금까지 사용했던 select문 기술하면 됨.

// 30번 부서에 소속된 사원들의 사번과 이름과 부서번호를 자주 검색한다.

sql>

select empno, ename, deptno

from emp_copy

where deptno = 30;

sql> 가상 테이블 view 생성

create view emp_view30

as 

select empno, ename, deptno

from emp_copy

where deptno = 30;

sql>

select * from emp_view30;

-- 3. 뷰의 내부구조와 user_views 데이터 딕셔너리

sql>

desc user_views;

sql>

select view_name, text

from user_views;

-- 4. 뷰의 동작 원리

- 1) 사용자가 뷰에 대해 질의하면 user_views에서 뷰에 대한 정의를 조회한다.

- 2) 기본 테이블에 대한 뷰의 접근 권한을 살핀다.

- 3) 뷰에 대한 질의를 기본 테이블에 대한 질의로 변환한다.

- 4) 기본 테이블에 대한 질의를 통해 데이터 검색한다.

- 5) 검색된 결과를 출력한다.

-- 5. 뷰와 기본 테이블 관계 파악

- 1) 뷰를 통한 데이터 저장 가능?

sql>

insert into emp_view30

values(7999, 'ANGEL', 30);

- 2) insert문에 뷰(emp_view30)를 사용하였지만, 뷰는 쿼리문에 대한 이름일뿐

새로운 레코드는 기본 테이블(emp_copy)에 실질적으로 추가되는 것

- 3) 뷰는 실질적인 데이터를 저장한 기본 테이블을 볼 수있도록한 투명한 창이다.

즉, 기본 테이블에 모양이 바뀐 것이고 그 바뀐 내용을 뷰라는 창을 통해서 볼 뿐이다.

뷰에 insert뿐만 아니라, update, delete 모두 사용할 수있는데, 이 명령문 역시 뷰의 텍스트에

저장되어 있는 기본 테이블을 변경하는 것이다.

-- 6. 뷰를 사용하는 이유

- 1) 복잡하고 긴 쿼리문을 뷰로 정의하면 접근을 단순화 시킬수 있다.

- 2) 보안에 유리하다.

 - 사용자마다 특정 객체만 조회할 수있도록 권한을 부여할 수 있기 때문.

sql>

create view emp_view

as

select empno, ename, job, hiredate, deptno

from emp_copy;

-- 7. 뷰의 종류

: 뷰는 뷰를 정의하기 위해서 사용되는 기본 테이블의 수에 따라

 - 단순 뷰(simple view)

 - 복합 뷰(complex view)

특징

-단순 뷰

. 하나의 테이블로 생성

. 그룹 함수의 사용이 불가능

. distinct 사용이 불가능(중복 데이터 버리기)

. insert/update/delete 등이 사용 가능

-복합 뷰

. 여러 개의 테이블로 생성

. 그룹 함수의 사용이 가능

. distinct 사용이 가능

. insert/update/delete 사용이 불가능

-- 7-1 단순 뷰에 대한 데이터 조작

sql>

insert into emp_view30 values(8000, 'HONG', 30);

-- 7-2 단순 뷰의 컬럼에다가 별칭 부여

sql>

create view emp_view_copy(사원번호, 사원명, 급여, 부서번호)

as

select empno, ename, sal, deptno

from emp_copy;

sql>

select * from emp_view_copy;

Q) 부서 30번에 근무하는 사원 정보만 출력(별칭)

sql>

select * from emp_view_copy

where 부서번호 = 30;

-- 7-3 그룹함수를 사용한 단순 뷰

@#@# 중요:) 뷰를 작성할 때 select절 다음에 sum이라는 그룹함수를 사용하면 결과를 뷰의

특정 컬럼처럼 사용하게 됨, 따라서 물리적인 칼럼이 존재하지 않는 가상 컬럼이기에 컬럼명도 상속

받을 수 없다. 뷰를 생성할 때 가상 컬럼을 사용하려면 사용자가 반드시 이름을 따로 설정해야 한다.

sql>

create view view_sal

as

select deptno, sum(sal) as "SalSum", avg(sal) as "SalAvg"

from emp_copy

group by deptno;

sql>

select * from view_sal;

-- 주의! 단순 뷰의 insert/update/delete 명령어가 불가능한 경우

1. 뷰 정의에 포함되지 않은 컬럼 중에 기본 테이블의 컬럼이 not null 제약 조건이 지정되어 있는 경우

insert문이 사용 불가능하다. 왜냐하면 뷰에 대한 insert문은 기본 테이블에 null 값을 입력하는 형태이기 때문

2. sal * 12 와 같이 산술 표현식으로 정의된 가상 컬럼이 뷰에 정의 되면 insert/update가 불가능 하다.

3. distinct을 포함한 경우에도 insert/update/delete 명령을 사용할 수 없다.

4. 그룹 함수나 group by절을 포함한 경우에도 insert/update/delete 명령을 사용할 수 없다.

// 기본 테이블이 기준

sql>

create view view_sal_year

as

select ename, sal*12 as "연봉"

from emp_copy;

sql> // sal*12 as "연봉"은 기본 테이블에는 없는 컬럼이므로 저장 안됨

insert into view_sal_year values('홍길동', 12000); -> 안됨

-- 7-4 복합 뷰

sql>

select empno, ename, sal, dept.deptno, dname, loc

from emp, dept

where emp.deptno = dept.deptno

order by empno desc;

sql>

create view emp_view_dept

as

select empno, ename, sal, dept.deptno, dname, loc

from emp, dept

where emp.deptno = dept.deptno

order by empno desc;

sql>

select * from emp_view_dept;

-- 8. 뷰 삭제

sql> 뷰도 객체

select view_name, text from user_views;

sql>

drop view emp_view;

-- 9. 뷰 생성에 사용되는 다양한 옵션(or replace)

-존재하지않은 뷰이면 새로운 뷰 생성하고 기존에 존재하는 뷰이면 그 내용을 변경한다.

sql>

create view emp_view30

as

select empno, ename, comm, deptno

from emp_copy

where deptno = 30;

=> 기존의 객체가 이름을 사용하고 있습니다.

sql> or replace

create or replace view emp_view30

as

select empno, ename, comm, deptno

from emp_copy

where deptno = 30;

sql> 기존 view에 덮어씀

select * from emp_view30;

-- 10. 뷰 생성에 사용되는 다양한 옵션(force / noforce)

force : 기본 테이블이 존재하지 않을 때도 뷰를 생성해야 되는 경우 사용하는 옵션.

noforce : 기본 테이블이 존재하는 경우에만 뷰가 생성(default).

sql> 없는 테이블

desc employee;

sql> 

create or replace force view employee_view

as

select empno, ename, deptno

from employee

where deptno = 30;

=> 컴파일 오류와 함께 뷰가 생성되었습니다.

-- 11. 뷰 생성에 사용되는 다양한 옵션(with check option)

: 뷰를 생성할 때 조건 제시에 사용된 컬럼 값을 변경하지 못하도록 하는 기능 제공

-뷰를 설정할 때 조건으로 설정한 컬럼 이외의 다른 컬럼의 내용은 변경할 수 없음.

sql>

create or replace view emp_view30

as

select empno, ename, sal, deptno

from emp_copy

where deptno = 30;

sql>

update emp_view30

set

deptno = 20

where sal >= 1200;

sql> + with check option

create or replace view view_check30

as

select empno, ename, sal, comm, deptno

from emp_copy

where deptno = 30 with check option;

sql>

update view_check30

set

deptno = 10

where sal >= 900;

=> 뷰의 with check option의 조건에 위배 됩니다.

-- 12.  뷰 생성에 사용되는 다양한 옵션(with read only)

- 기본 테이블의 어떤 컬럼에 대해서도 뷰를 통한 내용수정을 불가능하게 만드는 옵션.

sql>

update view_check30

set

comm = 1000;

=> 가능

sql> + with read only

create or replace view view_read30

as

select empno, ename, sal, comm, deptno

from emp_copy

where deptno = 30 with read only;

sql>

update view_read30

set

comm = 1000;

=>읽기전용 뷰에서는 DML 작업을 수행할 수 없습니다.

===============================================================

===============================================================

오라클 -TOP 쿼리

- Top-N(쿼리) : 상위 n개의 data만을 출력하고 싶을 때 

ex) 사원 중에서 입사일이 가장빠른 5명(Top-5)만을 얻어오는 질의문

- Top-n을 구하기 위해 rownum & 인라인 뷰가 사용.

sql> // rownum(오라클만 지원) : insert시 입력된 순서로 rownum 부여.

select rownum, empno, ename, hiredate

from emp;

sql> 입사일 순으로 정렬

select rownum, empno, ename, hiredate

from emp

order by hiredate;

sql> 입사일 순 뷰 생성

create or replace view view_hire

as

select empno, ename, hiredate

from emp

order by hiredate;

sql> 입사일 순으로 rownum 부여

select rownum, empno, ename, hiredate

from view_hire;

 1) rownum 이용

sql> ex) 사원 중에서 입사일이 가장빠른 5명

select rownum, empno, ename, hiredate

from view_hire

where rownum <= 5;

 2) 인라인 뷰

: sql문에서 사용하는 서브쿼리의 일종으로 보통 from 절에 위치해서 테이블처럼 사용하는 것.

sql> 

select rownum, empno, ename, hiredate

from (select empno, ename, hiredate

from emp

order by hiredate)

where rownum <= 5;

===============================================================

===============================================================

오라클 - 사용자(user) 권한(role)

--[1] 권한의 역할과 종류

        - 권한 : 사용자가 특정 테이블에 접근할 수 있도록 하거나 해당 테이블에 sql(select/insert/update/delete)문을 사용할 수 있도록 제한을 두는 것.

        - 종류 : 데이터베이스 보완

            . 시스템 권한 : 사용자의 생성과 제거, DB 접근 및 각종 객체를 생성할 수 있는 권한 등 DBA에 부여됨.

            . 객체 권한 : 객체를 조작할 수 있는 권한.

--[2] user01 계정 생성

SQL>conn system/admin

SQL>create user user01 identified by tiger;

--[3] 데이터베이스 접속 권한

SQL>grant create session to user01;

SQL>conn user01/tiger

SQL>create table emp01

        (

            empno number(4),

            ename varchar2(14),

            job varchar2(14),

            deptno number(2)

        );

--[4] 테이블 생성 권한

SQL>grant create table to user01;

SQL>create table emp01

        (

            empno number(4),

            ename varchar2(14),

            job varchar2(14),

            deptno number(2)

        );

SQL>insert into emp01

        values(7369, 'SMITH', 'CLERK', 20);

--[5] 테이블스페이스

        : 테이블스페이스(table space)는 디스크 공간을 소비하는 테이블과 뷰 그리고 그 밖의 데이터베이스 객체들이 저장되는 장소.

SQL>alter user user01 quota 2m on users;

SQL>insert into emp01

        values(7369, 'SMITH', 'CLERK', 20);

--[6] 시퀀스/뷰 객체 생성 권한

SQL>grant create sequence to user01;

SQL>grant create view to user01;

--[7] with admin option

        : 사용자에게 시스템 권한을 with admin option과 함께 부여하면 그 사용자는 데이터베이스 관리자가 아닌데도 불구하고 부여받은 시스템 권한을 다른 사용자에게 부여할 수 잇는 권한도 함께 부여 받게 됨.

SQL>create user user02 identified by tiger;

SQL>grant create session to user02 with admin option;

SQL>create user user03 identified by tiger;

SQL>conn user02/tiger;

SQL>grant create session to user03;

--[8] 테이블 객체에 대한 select 권한 부여(scott/emp -> user01)

SQL>conn scott/tiger

SQL>grant select on emp to user01;

SQL>conn user01/tiger

SQL>select * from emp;

--[9] 스키마(SCHEMA) : 객체를 소유한 사용자명을 의미.

SQL>select * from scott.emp;

--[10] 사용자에게 부여된 권한 조회

         . user_tab_privs_made : 현재 사용자가 다른 사용자에게 부여한 권한 정보를 알려줌.

         . user_tab_privs_recd : 자신에게 부여된 사용자 권한을 알고 싶을 때.

SQL>select * from user_tab_privs_made;

SQL>select * from user_tab_privs_recd;

--[한번에 권한 부여하기]

SQL>grant create session, create table, create view to user01;

--[11] 객체 권한 제거하기

SQL>conn scott/tiger

SQL>select * from user_tab_privs_made;

SQL>revoke select on emp from user01;

SQL>conn user01/tiger

SQL>select * from scott.emp;

--[12] with grant option

         : 사용자에게 객체 권한을 with grant option과 함께 부여하면 사용자는 객체를 접근할 권한을 부여 받으면서 그 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여받게 됨.

SQL>conn scott/tiger

SQL>grant select on scott.emp to user02 with grant option;

SQL>conn user02/tiger

SQL>grant select on scott.emp to user01;

--[13] 사용자 계정 삭제

SQL>conn system/admin

SQL>drop user user03;

--[14] 롤(Role)

          - 사용자에게 보다 효율적으로 권한을 부여할 수 있도록 여러 개의 권한을 묶어 놓은 것.

          - 사용자를 생성했으면 그 사용자에게 권한을 부여해야만 생성된 사용자가 데이터베이스를 사용할 수 있음.

        1) connection role

             . 사용자가 데이터베이스에 접속 가능하도록 하기 위해서 다음과 같이 가장 기본적인 시스템 권한 8가지 묶어 놓은 권한.

             . create session, create table, create view, create sequence, create synonym, create cluster, create database link, alter session

        2) resource role

              . 사용자가 객체(테이블, 시퀀스)를 생성할 수 있도록 시스템 권한을 묶어 놓은 것.

              . cteate cluster, create procedure, create sequence, create table, create trigger

        3) DBA role

              . 사용자들이 소유한 데이터베이스 객체를 관리하고 사용자들이 작성하고 변경하고 제거 할 수 있도록하는 모든 권한을 가짐.

SQL>conn system/admin

SQL>create user user03 identified by tiger;

SQL>grant connect, resource to user03;

SQL>conn user03/tiger

SQL>select * from dict

        where table_name like '%ROLE%';

==================================================================

==================================================================

==================================================================

==================================================================

오라클 - 인덱스(index)

    : 조회를 빠르게(빠른 검색) 하도록 도와줌.

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

    : 장점

       - 검색 속도가 빨라진다.

       - 시스템에 걸리는 부하를 줄여서 시스템 전체 성능을 향상시킨다.

    : 단점

       - 인덱스를 위한 추가적인 공간이 필요하다.

       - 인덱스 생성하는데 시간이 걸린다.

       - 데이터의 변경 작업(insert/update/delete)이 자주 일어날 경우에는 오히려 성능이 떨어진다.

--[1] 인덱스 정보 조회

SQL>select index_name, table_name, column_name

        from user_ind_columns

        where table_name in ('EMP', 'DEPT');

--[2] 조회 속도 비교하기

SQL>drop table emp01 purge;

SQL>create table emp01

        as

        select * from emp;

SQL>select index_name, table_name, column_name

        from user_ind_columns

        where table_name in('EMP', 'EMP01');

        // emp 테이블에는 empno 컬럼에 인덱스(pk_emp)가 

        // 존재하지만 emp 서브쿼리로 복사한 emp01 테이블에는 

        // 인덱스가 존재하지 않음.

        //    -> 서브쿼리문으로 복사한 테이블은 구조와 내용만 복사될 뿐        //         , 제약조건은 복사되지 않기 때문.

SQL>insert into emp01

        select * from emp01;

SQL>insert into emp01(empno, ename)

        values(8000, 'ANGEL');

SQL>set timing on

SQL>select empno, ename

        from emp01

        where ename='ANGEL';

--[3] 인덱스 생성

       - 기본키(primary key)나 유일키(unique)가 아닌 컬럼에 대해서 인덱스를 지정하려면 create index 명령어 사용.

SQL>create index idx_emp01_ename

        on emp01(ename);

SQL>select empno, ename

        from emp01

        where ename='ANGEL';

--[4] 인덱스 제거(삭제)

SQL>drop index idx_emp01_ename;

--[5] 인덱스를 사용해야 하는 경우 판단하기

       - 인덱스를 사용해야 하는 경우

         . 테이블에 행(레코드)의 수가 많을 때

         . where문에 해당 컬럼이 많이 사용될 때

         . 검색 결과가 전체 데이터의 2~4% 정도일 때

         . join에 자주 사용되는 컬럼이나 null을 포함하는 컬럼이 많은 경우 

       - 인덱스를 사용하지 말아야 하는 경우

         . 테이블에 행(레코드)의 수가 적을 때

         . where문에 해당 컬럼이 자주 사용되지 않을 때

         . 검색 결과가 전체 데이터의 10~15% 이상일 때

         . 테이블에 (insert/update/delete) 작업이 많은 경우, 즉 입력/수정/삭제 등이 자주 일어날 때

--[6] 인덱스의 종류

        1. 고유 인덱스(Unique Index)

            : 기본키나 유일키처럼 유일한 값을 갖는 컬럼에 대해서 생성하는 인덱스.

SQL>create unique index idx_dept01_deptno

        on dept01(deptno);

        

        2. 비고유 인덱스(Nonunique Index)

            : 중복된 데이터를 갖는 컬럼에 대해서 인덱스를 생성하는 경우.

SQL>drop table dept01;

SQL>create table dept01

        as

        select * from dept

        where 1=0;

SQL>insert into dept01 values(10, '개발부', '서울');

SQL>insert into dept01 values(20, '관리부', '서울');

SQL>insert into dept01 values(30, '영업부', '경기');

SQL>create index idx_dept01_loc

        on dept01(loc);

SQL>create unique index idx_dept01_deptno

        on dept01(deptno);

        3. 단일 인덱스(Single Index)

            : 한 개의 컬럼으로 구성한 인덱스.

        4. 결합 인덱스(Composite Index)

            : 두 개 이상의 컬럼으로 인덱스를 구성하는 것.

SQL>create index idx_dept01_com

        on dept01(deptno, dname);

        5. 함수 기반 인덱스(Function Based Index)

            : 연산자 항목의 컬럼으로 인덱스를 구성하는 것.

SQL>create index idx_emp01_annsal

        on emp01(sal*12);

SQL>select index_name, column_name

        from user_ind_columns

        where table_name = 'EMP01';

==================================================================

==================================================================

==================================================================

==================================================================

오라클 - 저장 프로시저(procedure)

      - 복잡한 쿼리문(DML : insert/update/delete)들을 필요할 때 마다 다시 입력할 필요없이 간단하게 호출만 해서 복잡한 쿼리문의 실행 결과를 얻을 수 있음.

      - 성능도 향상되고, 호환성 문제도 해결.

      - 여러 번 반복 호출해서 사용할 수 있는 장점.

SQL>drop table emp01;

SQL>create table emp01

        as

        select * from emp;

SQL>ed proc01;

SQL>@proc01(.sql);

SQL>execute del_all;

SQL>show error;

--[1] 저장프로시저 조회하기

SQL>desc user_source

SQL>select name, text

        from user_source;

--[2] 저장 프로시저의 매개 변수

SQL>ed proc02;

SQL>@proc02;

SQL>execute del_name('SMITH');

--[3] IN, OUT, INOUT 매개변수

SQL>ed proc03;

SQL>@proc03;

SQL>variable var_ename varchar2(15);

SQL>variable var_sal number;

SQL>variable var_job varchar2(9);

SQL>execute sel_empno(7876, :var_ename, :var_sal, :var_job);

SQL>print var_ename;

SQL>print var_sal;

SQL>print var_job;