220913 - 오라클 - 시퀀스, 인덱스, 권한, 동의어, PL/SQL

시퀀스

: 자동 순차 증가하는 순번을 반환하는 데이터베이스 객체 (1,2,3,..)

보통 PK값에 중복값을 방지하기위해 사용

 

 

• 시퀀스 생성

CREATE SEQUENCE [시퀀스명]
INCREMENT BY [증감숫자] //디폴트 1
START WITH [시작숫자] //디폴트 1
NOMINVALUE OR MINVALUE [최솟값] 
//NOMINVALUE : 디폴트 1, 최솟값 무한
//MINVALUE : 최솟값 설정
NOMAXVALUE OR MAXVALUE [최댓값] 
//NOMAXVALUE : 디폴트값 1027, 최댓값 무한
//MAXVALUE : 최댓값
CYCLE OR NOCYCLE 
//CYCLE : 최댓값에 도달하면 최솟값부터 다시 시작
//NOCYCLE : 최댓값 생성 시 시퀀스 생성중지
CACHE OR NOCACHE 
//CACHE : 메모리에 시퀀스 값을 미리 할당
//NOCACHE : 시퀀스값을 메모리에 할당하지 않음

 

 

•  

CREATE SEQUENCE sample_seq INCREMENT BY 1 START WITH 1;

select sequence_name, min_value, max_value, increment_by from user_sequences;


SEQUENCE_NAME
--------------------------------------------------
 MIN_VALUE  MAX_VALUE INCREMENT_BY
---------- ---------- ------------
DEPARTMENTS_SEQ
         1       9990           10

EMPLOYEES_SEQ
         1 1.0000E+28            1

LOCATIONS_SEQ
         1       9900          100

SAMPLE_SEQ
         1 1.0000E+28            1

 

 

 

• .nextval : 자동 순번 할당

SQL> select sample_seq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select sample_seq.nextval from dual;

   NEXTVAL
----------
         2

SQL> select sample_seq.nextval from dual;

   NEXTVAL
----------
         3

 

 

 

• .currval : 현재 순번 확인

select sample_seq.currval from dual;

   CURRVAL
----------
         3

 

 

 

• 시퀀스 생성, 테이블에 대입

create table dept_second as select * from department where 0=1; //구조만 복사

create sequence dno_seq increment by 10 start with 10;

insert into dept_second values(dno_seq.nextval, 'ACCOUNTING', 'NEW YORK'); //dno 자리에 시퀀스 대입

insert into dept_second values(dno_seq.nextval, 'RESEARCH', 'DALLAS');


       DNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                        DALLAS

select sequence_name, min_value, max_value, increment_by from user_sequences where sequence_name in('DNO_SEQ');

SEQUENCE_NAME
-------------------------------------------------------------------
 MIN_VALUE  MAX_VALUE INCREMENT_BY
---------- ---------- ------------
DNO_SEQ
         1 1.0000E+28           10

 

 

 

 

• 시퀀스 수정

ALTER SEQUENCE 시퀀스명 
INCREMENT BY a
NOMINVALUE OR MINVALUE a
NOMAXVALUE OR MAXVALUE a
CYCLE OR NOCYCLE
CACHE OR NOCACHE 

 

시작값 수정 불가

 

 

•  

alter sequence dno_seq maxvalue 100;

select sequence_name, min_value, max_value, increment_by from user_sequences where sequence_name in('DNO_SEQ');

SEQUENCE_NAME
-------------------------------------------------------------
 MIN_VALUE  MAX_VALUE INCREMENT_BY
---------- ---------- ------------
DNO_SEQ
         1        100           10

 

 

 

• 시퀀스 삭제

DROP SEQUENCE 시퀀스명;

 

 

 

 

인덱스

: 테이블 내 데이터를 빠르게 찾기 위한 데이터베이스 객체


CREATE INDEX 인덱스명 ON 테이블명(컬럼1,컬럼2,..);

 

테이블의 컬럼 (1개 이상)으로 생성

트리 형식으로 생성됨
과다 시 용량 차지해 성능 저하

인덱스 사용해야 하는 경우
1. 테이블 행이 많을 때
2. where문에 해당 컬럼이 많이 사용될 때
3. join에 자주 사용되는 컬럼

 

• 자동 인덱스 (기본키)

SELECT index_name, table_name, column_name from user_ind_columns where table_name in('EMPLOYEE','DEPARTMENT');

INDEX_NAME
-------------------
TABLE_NAME
-------------------
COLUMN_NAME
-------------------
PK_DEPT
DEPARTMENT
DNO

PK_EMP
EMPLOYEE
ENO

 

 

 

 

• 인덱스 생성

create index idx_emp_ename on employee(ename);

SELECT index_name, table_name, column_name from user_ind_columns where table_name in('EMPLOYEE','DEPARTMENT');

INDEX_NAME
----------------------
TABLE_NAME
----------------------
COLUMN_NAME
----------------------
PK_DEPT
DEPARTMENT
DNO

IDX_EMP_ENAME
EMPLOYEE
ENAME

PK_EMP
EMPLOYEE
ENO

 

 

 

 

• 인덱스 삭제

drop index 인덱스명;

 

 

 

 

• 고유 인덱스 : 중복값 비허용

CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (컬럼1,..) 

 

cf. 비고유 인덱스

 

 

 

 

• 결합 인덱스 : 두개 이상의 컬럼으로 구성된 인덱스

CREATE INDEX 인덱스명 ON 테이블명 (컬럼1,컬럼2,...);

 

cf. 단일 인덱스

 

 

•  

create index idx_dept_com on dept_second(dname, loc);

 

 

 

 

• 함수 기반 인덱스 : 수식/함수 적용해 만든 인덱스 

•  

create index idx_emp_totasal on employee(salary*12);

select ename, salary, salary*12 연봉 from employee where salary*12>16000;

ENAME                    SALARY       연봉
-------------------- ---------- ----------
TURNER                     1500      18000
ALLEN                      1600      19200
CLARK                      2450      29400
BLAKE                      2850      34200
JONES                      2975      35700
SCOTT                      3000      36000
FORD                       3000      36000
KING                       5000      60000

 

 

 

 

권한

권한 관련 작업 => 시스템 계정에서 실행 (관리자 계정 - dba 권한 모두 갖고 있음)

 

 

 

• 권한 부여

GRANT 권한(SELECT~DELETE) ON 계정이름.테이블 TO (권한부여할)계정이름;

 

 

•  

grant select, insert, update, delete on hr.employee to mydb2; 
//mydb2 계정에 hr 계정의 employee 테이블 SIUD할 권한 부여

=> mydb2 계정

select * from hr.employee;  //employee 테이블 조회 가능

 

 

 

 

• 권한 취소

•  

revoke insert, update, delete on hr.employee from mydb2; 
//mydb2 계정의 hr계정 employee 테이블에 대한 IUD 권한 취소

 

 

 

• 세션 권한 : 데이터베이스 연결 권한

• 사용자 생성, db 연결 권한 부여

alter session set "_ORACLE_SCRIPT"=true;
create user userdb identified by 1234;
grant create session to userdb; //세션 연결 권한 부여
grant create table to userdb; //테이블 생성 권한 부여

 

※ show user;  //연결된 계정 확인
※ alter user mydb2 identified by newPass;  //비밀번호 변경

 

 

 

 

• 모든 사용자 계정에 권한 부여

•  

grant select on hr.employee to public;

 

 

 

 

동의어

: 데이터베이스 객체 별칭

CREATE SYNONYM 시노님명 FOR 객체명;

 

 

• 동의어 생성 권한 부여

conn system/9511

CREATE SYNONYM syn_emp FOR employee;

 

 

 

• 동의어 생성

conn userdb/1234

create synonym syn_emp for hr.employee;

select * from syn_emp; //=>employee 테이블 출력됨

 

 

 

• 동의어 삭제

drop synonym syn_emp;

 

 

 

 

PL/SQL (Procedural Language extension to SQL)

: 프로그래밍 언어의 특성을 수용하여 SQL을 확장한 절차적 언어
 (SQL에서는 사용 불가한 절차적 프로그래밍 기능 가짐)

=> 관계형 데이터베이스에서 사용되는 Oracle의 표준 데이터 엑세스 언어
=> 프로시저 생성자를 SQL과 완벽하게 통합

 

 

※ 네트워크 연결 오류 시 호스트 이름 PC 이름으로 대체