13. 시퀀스 sequence
: 테이블 내의 유일한 숫자(유일한 값)를 자동으로 생성하는 발생기 ---> 주로 primary key로 사용
: 시작값, 증가치, 사이클, 최대값을 지정할 수 있음 +) 기본적으로 증가만 가능
* 시퀀스 생성
create sequence dept_deptno_seq --시퀀스명: 테이블명_컬럼명_seq 형식을 따르는 게 일반적
start with 10 --start with: 시작값 (기본값=1, 지정 가능)
increment by 10; --increment by: 증가치 (기본값=1, 지정 가능)
select *from seq; --시퀀스 목록 확인 1
select *from user_sequences; --시퀀스 목록 확인 2
* 시퀀스 적용
currval: 시퀀스 현재값을 반환
nextval: 시퀀스 다음값을 반환
select dept_deptno_seq.nextval from dual; ---10, 20, 30... 마지막 출력된 그 다음값을 계속 반환
select dept_deptno_seq.currval from dual; ---가장 마지막에 반환된 값=nextval로 찍힌 가장 최근값 반환
01. 사용 가능한 곳
- 서브 쿼리 아닌 select문
- insert문의 select절, value절
- update문의 set절
02. 사용 불가능한 곳
- view의 select절
- distinct 키워드 있는 select문
- group by, having, order by절 있는 select문
- select, delete, update의 서브쿼리
- create table, alter table 명령의 default값
ex1. 시퀀스를 테이블의 기본키(primary key)에 적용
--테이블 생성
create table emp01 ( empno number(4) primary key, ename varchar2(10), hiredate date);
--시퀀스 생성
create sequence emp01_empno_seq; --옵션 모두 생략 ---> 시작값1, 증가치1 (default)
--데이터 입력
insert into emp01 values(emp01_empno_seq.nextval, 'Sirius', sysdate); --empno에 시퀀스 입력
(주의! nextval이어야 다음값이 계속 반환됨, currval은 최근 반환값이 계속 반환되므로 중복이 됨 ---> primary key로 입력 불가)
ex2. seq_ex2
---> seq로 deptno가 10에서 시작, 10씩 증가한 값이 입력되는 것을 확인할 수 있음
* 시퀀스 삭제
drop sequence dept_exp_seq;
--시퀀스 삭제해도 기존에 사용한 곳은 값이 그대로 남아있음 ---> 시퀀스는 숫자를 부여만 하고, 부여된 이후에는 관련이 없기 때문에
* 시퀀스 수정
--시퀀스 생성
create sequence dept_deptno_seq
start with 10
increment by 10
maxvalue 30; --최대값
select dept_deptno_seq.nextval from dual; ---네번 실행하면 오류 발생! 최대 30이라서! cycle도 없고!
---> maxvalue를 30에서 1000000으로 수정해야 함
alter sequence dept_deptno_seq maxvalue 1000000;
select dept_deptno_seq.nextval from dual; ---최대값 수정되었으므로 40부터 다시 반환 시작!
14. 인덱스 index
: 데이터를 더 빠르게 검색할 수 있음 ---> 인덱스가 없으면 각 쿼리의 전체 행을 스캔해야 하기 때문에
인덱스는 키로 빌드됨 ---> 키는 테이블 또는 뷰의 열 등의 값으로 구성
- 장점: 검색속도 빨라짐, 시스템 부하 감소 -> 전체 성능 향상
- 단점: 추가공간 필요, 생성 시간 필요, 데이터변경(insert, update, delete)이 빈번하면 성능이 저하될 수 있음
select *from user_indexes;
-- 기본키primary key가 설정된 컬럼은 자동으로 고유 인덱스가 만들어짐. unique -> 고유 인덱스
* 인덱스 생성
: 자동 생성되는 인덱스 외에 직접 생성도 가능함
create index idx_emp01_ename on emp01(ename); --인덱스명: idx_테이블_컬럼 on 테이블(컬럼)
---> 많은 데이터가 존재할 때 인덱스 생성하면 (처음 한 번) 생성 시간이 꽤 소요됨
※ 인덱스 유무에 따른 검색 속도 비교
없을 때 -> 30.398초 있을 때 -> 0.135초
* 인덱스 삭제
drop index idx_emp01_ename; --인덱스명으로 삭제!
* 인덱스 종류
01. 고유 인덱스 Unique Index: primary key처럼 유일한 값을 갖는 칼럼에 적용할 수 있는 인덱스
---> unique 붙여서 생성해야 함 ex) create unique index index_name~
02. 비교유 인덱스 NonUnique Index: 중복 데이터가 존재하는 칼럼에 적용할 수 있는 인덱스
+) 고유, 비고유 인덱스 비교
----> 만약 고유 인덱스가 존재하는 곳에 중복 데이터를 입력한다면?
insert into dept01 values(10, '인사과','대전')
오류 보고 -
ORA-00001: unique constraint (SCOTT.IDX_DEPT01_DEPTNO) violated
----> 중복 데이터가 존재하는 곳에 고유 인덱스를 생성한다면?
create unique index idx_dept01_loc on dept01(loc)
오류 보고 -
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
01452. 00000 - "cannot CREATE UNIQUE INDEX; duplicate keys found"
03. 단일 인덱스 Single Index : 한 개의 컬럼으로 구성한 인덱스 (지금까지 만든 인덱스들...)
04. 결합 인덱스 Composite Index : 둘 이상의 컬럼으로 구성한 인덱스
create index idx_dept01_com on dept01(deptno, dname);
05. 함수 기반 인덱스 Function Based Index: 컬럼명 자리에 수식이나 함수를 넣어 구성한 인덱스
create index idx_emp01_annsal on emp(sal*12); --괄호 안에 함수(수식) 포함!
15. 사용자 관리
* DB 보안을 위한 권한
: 권한이란, 사용자가 특정 테이블에 접근할 수 있도록 하거나 해당 테이블에 SQL문을 적용할 수 있도록 제한을 두는 것
※ !주의! 권한은 주로 dba에 의해 부여되므로 -> 이 파트는 system계정으로 접속해서 진행해야 함!
01. 시스템 권한: 사용자 생성, 제거, DB 접근 및 각종 객체 생성 권한 등 주로 dba에 의해 부여되는 것
ex) create user(사용자 생성), drop user(사용자 삭제) etc.
참고- 사용자 생성: create user ID identified by PW; -- (create user user01 identified by tiger;)
- 시스템 관리자가 일반 사용자(user)에게 부여하는 권한 (grant 명령어 이용)
- create session: 데이터베이스 접속 권한
- create table: 테이블 생성 권한
- create view: 뷰 생성 권한
- create sequence: 시퀀스 생성 권한
- create procedure: 프로시저(함수) 생성 권한
---> 정리: 사용자 생성하고 권한을 부여(GRANT)해야 비로소 그 계정을 사용할 수 있다!
ex) grant create session to user01; --DB 접속 권한 부여
grant create session, create table, create view to user01; --콤마(,)로 권한 여러 개 부여 가능
(나중에는 롤로 한꺼번에 권한 부여하게 됨)
- with admin option : 시스템 권한을 부여할 때 이 옵션을 추가하면, 권한을 부여받은 사용자는 DBA가 아니지만, 다른 사용자에게 자신이 부여받은 시스템 권한을 부여할 수 있음
(예를 들면 카페지기가 운영자 임명하고 그 운영자가 게시판지기 임명하는 그런 느낌)
ex) grant create session to user02 with admin option;
02. 객체 권한: 특정 객체를 조작할 수 있는 권한. 기본적으로 객체의 소유자는 객체에 대한 모든 권한을 가짐
: 테이블, 뷰, 시퀀스, 함수 등 객체 별로 DML문을 사용할 수 있는 권한을 설정
ex. 새로 생성된 user01 계정에게 scott 계정 소유의 emp테이블 객체에 대한 권한(select)을 부여
conn scott/tiger;
grant select on emp to user01;
ex. user01 계정으로 접속 후 emp 테이블에 대해 select 실행 (단, 다른 계정이니까 scott.emp로 표시)
conn user01/tiger;
select*from scott.emp;
- 권한 취소 (REVOKE)
: 사용자에 부여한 객체 권한을 철회
revoke select on emp from user01;
- with grant option : 객체 권한을 부여할 때 이 옵션을 추가하면, 권한을 부여받은 사용자는 다른 사용자에게 자신이 부여받은 객체 권한을 부여할 수 있음
ex) grant select on emp to user02 with grant option;
16. 롤 role
: 권한 그룹 관리 ---> 사용자가 효율적으로 권한을 부여할 수 있도록 여러 권한을 묶어놓은 것
: create, grant, revoke 기본적으로 system 계정에서 작업하고, 객체 권한 부여 등 특정 상황에만 그 계정에서 작업!
* 시스템 제공 롤
- connect
: 가장 기본적인 시스템 권한 ~8개
alter session, create cluster, create database link, create sequence, create session, create synonym, crate table, create view
- resource
: 객체(table, view, index) 생성할 수 있는 시스템 권한 ~20여 개
create cluster, create procedure, create sequence, create table, create trigger etc.
- dba
: 데이터베이스 관리자(DBA)로서의 권한(시스템 관리에 필요한 모든 권한) ~130여 개
- grant connect, resource to user04;
* 사용자 정의 롤
: 기본 제공되는 롤 외에, 사용자가 직접 묶어 정의한 롤
create role role_name; --롤 생성하고
grant privilege_name to role_name; --생성한 롤에 원하는 권한을 부여
- 시스템 권한
- 객체 권한
---> 객체 권한 부여할 때는 그 객체를 소유한 계정으로 부여해야 함! 딱 이 때만! 주의!!!
* 롤 회수 : 특정 계정한테서 권한을 회수하는 것
- revoke mrole, mrole02 from user05;
* 롤 삭제 : 롤 자체를 삭제
- drop role mrole; --revoke는 여러 개 회수 가능한데, 삭제는 하나씩만 가능하더라 참고!
* 디폴트 롤: 시스템권한+객체권한
---> 디폴트 롤 부여된 여러 계정에 권한이 부여되었음을 확인 (create session+grant select)
'수업 > 정리' 카테고리의 다른 글
| 220207 저장 프로시저, 저장 함수, 커서, 패키지, 트리거 (0) | 2022.02.07 |
|---|---|
| 220204 동의어, PL/SQL-변수,조건,반복, 저장 프로시저 (0) | 2022.02.04 |
| 220128 제약조건, 뷰 (0) | 2022.01.29 |
| 220127 DML, TCL, 무결성 제약조건 (0) | 2022.01.27 |
| 220126 ANSI Join, 서브쿼리, DDL, DML (0) | 2022.01.26 |