[211004] SEQUENCE, 트랜젝션, SYNONYM

SEQUENCE

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

(행 데이터에 대해 식별번호를 만들고 싶을 때 시퀀스가 자동으로 번호를 부여해준다.)

 

 

표현식 CREATE SEQUENCE 시퀀스명

옵션          CREATE SEQUENCE 시퀀스명 ① [START WITH 숫자]                       -> 처음 발생시킬 시작 값, 기본값 1 ② [INCREMENT BY 숫자]                   -> 다음 값에 대한 증가치, 기본값 1 ③ [MAXVALUE 숫자 | NOMAXVALUE]  -> 발생시킬 최대값, 10의27승-1까지 가능 ④ [MINVALUE 숫자 | NOMINVALUE]   -> 발생시킬 최소값, -10의 26승 ⑤ [CYCLE | NOCYCLE]                       -> 시퀀스가 최대값까지 증가 완료 시 CYCLE은 START WITH 설정값으로 돌아감 NOCYCLE은 에러 발생 ⑥ [CACHE | NOCACHE]                     -> CACHE는 메모리 상에서 시퀀스 값 관리 기본값 20

예시 CREATE SEQUENCE SEQ_EMPID START WITH 300 INCREMENT BY 5 MAXVALUE 310 NOCYCLE NOCACHE; SELECT SEQ_EMPID.CURRVAL FROM DUAL; SELECT SEQ_EMPID.NEXTVAL FROM DUAL; SELECT SEQ_EMPID.CURRVAL FROM DUAL; SELECT SEQ_EMPID.NEXTVAL FROM DUAL; SELECT SEQ_EMPID.NEXTVAL FROM DUAL; SELECT SEQ_EMPID.NEXTVAL FROM DUAL; 시퀀스를 통해 생성된 번호가 현재 몇번까지 생성되었는지 알고 싶을 때 CURRVAL (CURRENT VALUE) 다음 번호로 생성을 하고 싶다 NEXTVAL  -- 생성된 시퀀스 객체 확인 SELECT * FROM USER_SEQUENCES;

 

- NEXTVAL/CURRVAL 사용 가능 여부

  (서브쿼리 아닌 SELECT, INSERT, UPDATE 에서만 가능하다고 생각)

사용가능	사용불가
서브쿼리가 아닌 SELECT문	VIEW의 SELECT절
INSERT문의 SELECT절	DISTINCT 키워드가 있는 SELECT문
INSERT문의 VALUE절	GROUP BY, HAVING, ORDER BY 절이 있는 SELECT문
UPDATE문의 SET절	SELECT, DELETE, UPDATE의 서브쿼리
	CREATE TABLE, ALTER TABLE 명령의 DEFAULT값

 

- 시퀀스 수정 시 CREATE에 사용한 옵션을 변경 가능

ALTER SEQUENCE SEQ_EMPID INCREMENT BY 10 MAXVALUE 400 NOCYCLE NOCACHE; 단, START WITH 값 변경은 불가하기 때문에 변경하려면 삭제 후 다시 생성

 

---

 

SEQUENCE 실습

 

-- 자동 번호 발생기(SEQUENCE 생성) CREATE SEQUENCE ACCOUNTS_SEQ; -- 조회 SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'ACCOUNTS_SEQ';  SELECT ACCOUNTS_SEQ.NEXTVAL FROM DUAL;  -- 계속 실행하면 계속 증가 하면서 부여 됨 SELECT ACCOUNTS_SEQ.CURRVAL FROM DUAL; -- 시퀀스 만들자마자 바로 시행하면 오류, 부여된 값이 아직 없기 때문 --삭제 DROP SEQUENCE ACCOUNTS_SEQ;

 

 

<번호 INSERT>

INSERT INTO ACCOUNTS VALUES(ACCOUNTS_SEQ.NEXTVAL, 10000, 'O', SYSDATE, 'O01'); -- 조건부 에러 -- ACCOUNTS ID 컬럼에 이미 1 ~ 5 값이 들어가있고 기본키 설정도 되어 있었기 때문이다. -- 기본키는 중복 불가인데 NEXTVAL 한번하면 1번 부여 -> 에러  -- 결과적으로 번호 6이 발생할때까지 계속 실행하면 6번으로 데이터가 INSERT 된다. -- 다른 부분에서 에러가 나도 마찬가지로 번호 발생은 계속 진행 된다. ex) 'O09'로 외래키 제약조건 위반       ORA-02291: integrity constraint (DEVELOP1.ACCOUNTS_ACC_TYPE_FK) violated - parent key not found

 

 

 

---

트랜젝션

하나의 논리적 기능을 수행하기 위한 작업의 단위. 

ex. 상품 구입 기능

        1. 구입 상품의 재고를 감소 

        2. 판매 테이블에 구입 상품의 정보 추가

        3. 세금 계산서 정보 추가

 

 진행 중 사용자가 결제 취소를 한 경우 세금계산서 정보, 구입상품 정보 추가한 것도 삭제, 감소시킨 재고도 원상복구 해야 함, 일련의 과정들을 트랜젝션을 이용하여 처리할 수 있음

 

ROLLBACK : 현재까지의 작업을 취소하고 최근 커밋 상태로 돌아간다.

COMMIT : 현재까지의 작업을 데이터베이스에 반영한다.

[211004 16:35] -- 트랜젝션  SET AUTOCOMMIT OFF;  -- 현재 접속 세션에 대한 것(SET), DB 연결 끊었다가 재연결하면 다시 OFF 해주어야 함  UPDATE ACCOUNTS    SET AMOUNT = 30000  WHERE ID = 10; ROLLBACK;   COMMIT;  SELECT * FROM ACCOUNTS;

 

 

---

SYNONYM

사용자가 다른 사용자의 객체를 참조할 때 [사용자ID].[테이블명]으로 표시하는데 

이처럼 길게 표현되는 것을 동의어(SYNONYM)로 설정 후 간단히 사용 가능한 객체

 

• 비공개 동의어
  객체에 대한 접근 권한을 부여 받은 사용자가 정의한 동의어로 해당 사용자만 사용 가능
• 공개 동의어
  권한을 주는 사용자(DBA)가 정의한 동의어로 모든 사용자가 사용 가능(PUBLIC) ex. DUAL

---

 

SYSNONYM  실습

 

- SYSNONYM 생성 시 권한 부여가 필요

 

SYNONYM 생성 권한 부여

 

-- 일반 사용자에게 동의어를 생성할 권한을 부여 (시스템 계정) GRANT CREATE SYNONYM TO DEVELOP1; -- 비공개 동의어 (유저 계정) CREATE SYNONYM ACCS FOR ACCOUNTS; -- 비공개 동의어이기 때문에 DEVELOP1이 아닌 다른 계정으로 접속하면 조회 불가. SELECT * FROM ACCS;  -- table or view does not exist                      -- 시스템 계정으로 공개 동의어를 생성 CREATE PUBLIC SYNONYM ACTS FOR DEVELOP1.ACCTYPES; -- 어떠한 계정이든 조회 가능 SELECT * FROM ACTS; -- 권한을 부여하기 위해 사용 (does not exist가 아닌 insufficient privileges 에러) GRANT SELECT ANY TABLE TO USER1;