SQL(끝) / TABLE 변경, View, Sequence, ROLE, user

Cheol·2023년 5월 18일
sql

SQL

목록 보기
7/7
post-thumbnail

edu day 8

테이블 변경

제약조건 삭제

: 기존 테이블의 제약조건을 삭제하기 위해서는 제약조건명을 이용하여 ALTER TABLE DROP문을 자겅해야 된다.

ALTER TABLE 테이블명
DROP PRIMARY KEY|UNIQUE(컬럼)|
CONSTRAINT 제약조건명 [CASCADE];

  • DEPT03의 PRIMARY KEY와 DNAME의 NOT NULL 조건 삭제
ALTER TABLE DEPT03 DROP PRIMARY KEY;

ALTER TABLE DEPT03 DROP CONSTRAINT DEPT03_DNAME_NN;

CASCADE 옵션

: PRIMARY KEY가 FOREIGN KEY로 참조하는 경우에는 기본키를 삭제하면 '참조 무결성'제약 조건에 위배에서 에러가 발생. 이떄 써주는 것이 CASCASDE 옵션이다.

ALTER TABLE DEPT05 DROP PRIMARY KEY;		--에러 발생
ALTER TABLE DEPT05 DROP PRIMARY KEY CASCADE; -- 성공

제약 조건 활성화/비활성화

ALTER TABLE 테이블명
DISABLE|ENABLE CONSTRAINT 제약조건명 [CASCADE];

  • 활성화 / 비활성화
ALTER TABLE EMP05 DISABLE CONSTRAINT EMP05_EMPNO_PK;
ALTER TABLE EMP05 ENABLE CONSTRAINT EMP05_EMPNO_PK;

뷰(View)

CREATE VIEW EMP_VIEW AS SELECT EMPNO,ENAME, D.DNAME, D.DEPTNO FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO WHERE E.DEPTNO = 20;

SELECT * FROM EMP_VIEW;

뷰 수정

: 테이블을 수정할 때는 ALTER TABLE문장을 사용하지만 VIEW를 수정하기 위해서는 CREATE OR REPLACE 문을 사용한다. 뷰가 존재하면 덮어쓰기고 되고 없으면 새로 생성된다.

CREATE OR REPLACE VIEW EMP_VIEW2 AS SELECT EMPNO, ENAME, JOB, MGR, COMM, DEPTNO FROM EMP;

뷰 종류

: 뷰의 종류는 기본 테이블의 개수에 따라서 단순 뷰와 복합 뷰로 구분된다.

  • 단순 뷰(Simple View)
    : 하나의 기본 테이블에대해서 정의한 뷰이다. CRUD(INSERT, UPDATEM DELETE) 같은 DML문 실행이 가능하다.
CREATE VIEW EMP_VIEW3(사원번호, 이름, 월급) 
AS SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 20;

뷰를 생성하면서 함수를 사용하는 경우네느 반드시 컬럼 별칭을 지정해야한다.

CREATE VIEW EMP_VIEW4
AS 
SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO; -- 에러

CREATE VIEW EMP_VIEW4
AS 
SELECT DEPTNO, SUM(SAL) 총합 FROM EMP GROUP BY DEPTNO; -- 정상적으로 생성

단순 뷰로 생성한 경우에는 DML 작업도 가능하다.

CREATE VIEW EMP_VIEW5
AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP; 	-- 정상 생성

DELETE FROM EMP_VIEW5 WHERE DEPTNO = 10; -- 정상 삭제

SELECT * FROM EMP_VIEW5; 
SELECT * FROM EMP;	-- 원본 또한 삭제 

ROLLBACK 	-- DML작업은 트랜잭션으로 인해 당연히 ROLLBACK 가능
  • 단순 뷰의 DML 불가
    모든 상황에서 단순 뷰의 DML 작업이 가능한 것은 아니다. 단순 뷰가 그룹함수,GROUP
    BY, DISTINCT 같은 표현식을 포함한 경우에는 DML 작업이 불가능하다

WITH CHECK OPTION 제약 조건

: 테이블은 데이터의 무결성을 보장하기 위해서 기본 키 및 NOT NULL 제약조건등을 설정한다. 마찬가지로 뷰도 WHERE에 만족하는 데이터만 INSERT 또는 UPDATE가 가능하도록 제약조건을 설정할 수 있다.

  • WITH CHECK OPTION 제약 조건 전
CREATE VIEW EMP_VIEW6
AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP 
WHERE DEPTNO = 30;

SELECT * FROM EMP_VIEW6;

UPDATE EMP_VIEW6
SET DEPTNO = 40
WHERE EMPNO = 7499;	--성공

--> 사원번호가 7499인 사원의 DEPTNO가 40으로 정상적으로 바뀐다.

CREATE OR REPLACE VIEW EMP_VIEW6
AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 30
WITH CHECK OPTION;

UPDATE EMP_VIEW6
SET DEPTNO = 40
WHERE EMPNO = 7499;	-- 에러 출력

WITH READ ONLY : DML작업은 불가하고 SELECT만 가능하다.

뷰 삭제

: 뷰를 삭제해도 기본 테이블에 영향을 미치지 않는다. Oracle 인터페이스 왼쪽에서 뷰 우클릭으로도 삭제 가능하다.

DROP VIEW 뷰이름;


시퀀스

: 시퀀스 객체는 호출될 떄 마다 자동으로 유일한 숫자를 생성하는 오라클 객체로서 테이블의 특정 컬럼값을 넘버링(numbering)하기 위해서 사용된다.

CREATE SEQUENCE 시퀀스명
[ START WITH n][ INCREMENT BY n]
[ MAXVALUE n | NOMAXVALUE ][ MINVALUE n | NOMINVALUE ]
[ CYCLE | NOCYCLE ][ CACHE n | NOCACHE ]

  • START WITH n
    시퀀스 번호의 시작값을 지정할 때 사용. 생략시 1부터 시작된다.

  • INCREMENT BY n
    연속적인 시퀀스 번호의 증가치를 지정할 때 사용. 음수값 설정도 가능하고 생략시 1씩 증가된다.

  • MAXVALUE n
    시퀀스가 가질 수 있는 최대값을 지정

  • MINVALUE n
    시퀀스가 가질수 있는 최소값 / CYCLE인 경우 새로 시작하는 값 역할을 한다.

  • CYCLE 옵션
    지정된 시퀀스 값이 최대값까지 증가가 완료되면 START WITH값부터 다시 시작하는 것이 아니고 MINVALUE 값부터 다시 시작된다

  • CACHE 옵션
    성능향상을 위해서 메모리상의 시퀀스 값을 미리 만들어서 필요시 바로 제공하는 방법으로 생략시 기본적으로 20개를 생성해서 관리한다. NOCACHE는 필요할 때마다 매번 시퀀스값을 계산해서 반환한다. 성능 면에서는 CACHE 옵션을 사용하는 것이 좋으나 데이터베이스를 종료하고 다시 사용할 경우에는 이전에 생성했던 시퀀스 값을 사용하지 못하게 되어 중간에 비어있는 넘버링이 될 수도 있다. 

CREATE SEQUENCE DEPT_DEPTNO_SEQ
 START WITH 10
 INCREMENT BY 10
 MAXVALUE 100
 MINVALUE 5
 CYCLE
 NOCACHE;

--> 10으로 시작해서 10씩 증가하고 최댓값 100에 도달했을 때 CYCLE이 돌아 최솟값 5로되고 이후 10씩 계속 증가한다.


NEXTVAL 과 CURRVAL

: 시퀀스 객체가 생성되었다고 자동으로 시퀀스 값이 생성되는 것이 아니다. 시퀀스 값을 얻기 위해서는 반드시 시퀀스 객체를 호출해야 되는데, 시퀀스명.NEXTVAL 형식을 사용하면 지정된 시퀀스에서 순차적인 시퀀스 값을 얻어오게 된다. NEXTVAL 값을 호출할 때마다 시퀀스 생성시 지정했던 옵션들에 의해서 다음 시퀀스 값이 결정되어 반환된다.

현재 생성된 시퀀스 값을 조회하기 위해서는 시퀀스명.CURRVAL 형식을 사용하면 된다. 주의할 점은 반드시 NEXTVAL을 먼저 호출하고 나중에 CURRVAL을 호출해야 된다. 

 SELECT DEPT_DEPTNO_SEQ.NEXTVAL, DEPT_DEPTNO_SEQ.CURRVAL FROM DUAL;

시퀀스 수정

: 시퀀스 수정은 ALTER SEQUENCE문을 사용하여 증가치,최대값,최소값,CYCLE여부,캐시값을 변경할 수 있다. 시퀀스가 변경되면 다음 시퀀스 번호값부터 변경사항이 적용되고 START WITH옵션은 변경이 불가능하기 때문에 필요시 시퀀스를 삭제하고 재 생성해야 된다.

ALTER SEQUENCE 시퀀스명
[ INCREMENT BY n]
[ MAXVALUE n | NOMAXVALUE ]
[ MINVALUE n | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE n | NOCACHE ]
  • 수정
ALTER SEQUENCE DEPT_DEPTNO_SEQ3
 INCREMENT BY 10
 CYCLE;
 
 ALTER SEQUENCE DEPT_DEPTNO_SEQ3
NOCACHE;

  • START WITH 옵션 변경 불가
    시퀀스의 옵션중에서 START WITH 옵션은 변경이 불가능하기 때문에 dept_deptno_seq3
    시퀀스의 START WITH 옵션을 변경하려고 하면 다음과 같이 에러가 발생되는 것을 확인
    할 수 있다. 
ALTER SEQUENCE DEPT_DEPTNO_SEQ3
 START WITH 5	-- 사용 불가
 INREMENT BY 10
 CYCLE

테이블에 시퀀스 값 저장

: 테이블에 특정 값을 넘버링하기 위한 용도로 사용되기 떄문에 테이블에 데이터를 저장하는 INSERT문과 같이 사용된다.

CREATE TABLE DEPT06
(DEPTNO NUMBER(4) PRIMARY KEY,
DNAME VARCHAR2(15),
LOC VARCHAR2(15)
);	--테이블 생성

CREATE SEQUENCE DEPT_DEPTNO_SEQ4
 START WITH 10
 INCREMENT BY 10
 NOCYCLE
 NOCACHE; 	--시퀀스 생성
 
INSERT INTO DEPT06(DEPTNO, DNAME, LOC)
VALUES (DEPT_DEPTNO_SEQ4.NEXTVAL, '개발','서울');
INSERT INTO DEPT06(DEPTNO, DNAME, LOC)
VALUES (DEPT_DEPTNO_SEQ4.NEXTVAL, '인사','경기');
INSERT INTO DEPT06(DEPTNO, DNAME, LOC)
VALUES (DEPT_DEPTNO_SEQ4.NEXTVAL, '관리','부산');
COMMIT;	-- 테이블에 시퀀스 값 저장 후 커밋

동의어 (synonym)

1.TESTER생성
- SYS에서 생성
2. SCOTT => TESTER DEPT 사용 권한 부여

-- HR계정
GRANT select
ON EMPLOYEES
TO scott; 
--> HR계정에 있는 EMPLOYEES테이블의 SELECT권한을 scott에게 주고있는 것

-- SCOTT계정
GRANT SELECT
ON DEPT
TO TESTER;

select * from scott.dept; -- TESTER에서 확인 가능

CREATE [PUBLIC] SYNONYM 동의어
FOR 스키마.객체;

  • 동의어 만들기
select * from scott.dept; -- TESTER 계정에서 SCOTT.DEPT에 동의어를 넣고싶음 

GRANT CREATE SYNONYM TO TESTER; -- SYS계정에서 SYNONYM를 사용할 권한을 부여한다.

CREATE SYNONYM SDEPT FOR SCOTT.DEPT; 	--TESTER 계정에서 SCOTT.DEPT에 'SDEPT'로 동의어를 부여한다.

SELECT * FROM SDEPT; 	--성공
  • 동의어 삭제
DROP SYNONYM SDEPT;

인덱스

인덱스 생성

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블( 컬럼[,컬럼] );

CREATE INDEX EMP_ENAME_IDX ON EMP(ENAME);   -- 생성 후 검색 속도가 빨라진다.

사용자 관리

  1. SYS => 계정생성
  2. SYS => 권한부여( DB접속권한, 테이블 사용 권한, 시퀀스 권한, 인덱스 권한 등)
  3. 롤(role)의 개념
    사용자정의롤 : 테이블 사용 권한 ,시퀀스 권한, 인덱스 권한 저장
  4. 사용자에게 롤 권한 부여

인증과 권한의 차이 숙지하기.


  • 사용자 생성을 위한 기본 문법

    CREATE USER 계정
    IDENTIFIED BY 비밀번호;

--> 생성 후 권한을 아직 부여하지 않았기 때문에 접속 불가.


일반 사용자에게 할당 가능한 시스템 권한

CREATE SESSION : 데이터베이스에 접속할 수 있는 시스템 권한
CREATE TABLE : 사용자가 테이블을 생성할 수 있는 시스템 권한
CREATE SEQUENCE : 사용자가 시퀀스를 생성할 수 있는 시스템 권한
CREATE VIEW : 사용자가 뷰를 생성할 수 있는 시스템 권한
CREATE PROCEDURE : 사용자가 PL/SQL의 프로시저를 생성할 수 있는 시스템 권한

  • Syntax

    GRANT 시스템권한 [,시스템권한]
    TO 사용자계정 | role | PUBLIC;

  • USER01에게 DB접속과 테이블 생성 권한 부여

GRANT CREATE SESSION, CREATE TABLE TO USER01;
--> 이것만으로 USER01에서 테이블 생성이 불가하다. USER01은 테이블 공간의 권한이 없기 떄문이다.

GRANT UNLIMITED TABLESPACE TO USER01; 
--> SYS 계정에서 TABLESPACE의 권한을 USER01에 부여해야한다.
  • 권한 부여 목록 확인 
SELECT * FROM SESSION_PRIVS;

  • 권한 회수

    REVOKE 시스템권한[,시스템권한]
    FROM 사용자계정|role|PUBLIC;

  • CREATE SESSION 권한 회수;

-- SYS계정에서 실행
GRANT CREATE SESSION TO USER01;

  • WITH ADMIN OPTION
    : 권한을 부여한 계정이 다른 계정에 부여한 권한을 취소할 때 연쇄적 취소가 안되기 떄문에 사용에 주의해야 한다.

객체 권한 종류

ALTER: 테이블,시퀀스 객체를 수정할 수 있는 객체 권한
DELETE: 테이블,뷰 객체에서 데이터를 삭제할 수 있는 객체 권한
INSERT: 테이블,뷰 객체에서 데이터를 삽입할 수 있는 객체 권한
UPDATE: 테이블,뷰 객체에서 데이터를 수정할 수 있는 객체 권한
SELECT: 테이블,뷰,시퀀스 객체에서 데이터를 조회할 수 있는 객체 권한
REFERENCES: 테이블의 참조 제약조건을 설정할 수 있는 객체 권한
EXECUTE: PL/SQL의 프로시저를 실행할 수 있는 객체 권한

GRANT 객체권한[(컬럼)]
ON 객체명
TO 사용자계정|role|PUBLIC
[WITH GRANT OPTION];

  • USER01계정에 SCOTT의 SELECT, INSERT 권한 부여
GRANT SELECT,INSERT ON DEPT TO USER01;

--> USER01계정에서 SCOTT계정에 있는 테이블 정보 SELECT, INSERT 가능

  • WITH GRANT OPTION
    : 시스템 권한 계정이 회수하면 그 하위 계정까지 연쇄적 취소 가능

권한 관련 데이터 사전

  • 부여한 권한 정보 확인 SQL (부여한 계정에서 실행)
SELECT * FROM USER_TAB_PRIVS_MADE;
  • 부여받은 권한 정보 확인 SQL (부여받은 계정에서 실행)
SELECT * FROM USER_TAB_PRIVS_RECD;



롤(ROLE)

: 사용자에게 일일이 권한을 부여하거나 회수하는 작업은 매우 번거롭기 때문에 ROLE을 사용한다.

롤의 종류

  • Built-in 롤
    롤의 종류
    CONNECT: CREATE SESSION
    RESOURCE: CREATE TABLE,CREATE PROCEDURE, CREATE SEQUENCE,CREATE TRIGGER,CREATE TYPE, CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR
    DBA: 대부분의 시스템 권한과 일부 롤을 포함하며 일반 사용자에게 롤을 부여해서는 안된다.

    DBA_ROLES 데이터 사전을 이용하여 사전 정의된 롤을 확인할 수 있다.

select role from dba_roles;

  • 사용자가 필요에 의해서 정의한 롤
    사용자 롤 사용 순서
CREATE USER user03 IDENTIFIED by user03;	--사용자 계정 생성
CREATE ROLE clerk;	--clerk 롤 생성

GRANT CREATE SESSION, CREATE TABLE TO CLERK;	--롤에 권한 부여

GRANT SELECT ON SCOTT.DEPT TO CLERK;	--롤에 SCOTT.DEPT에 접근할수있는 권한 부여

GRANT CLERK TO user03;	--계정에 롤 권한 부여

GRANT UNLIMITED TABLESPACE TO USER03;	--테이블 공간 권한이 없으므로 권한 부여

여기까지 SQL이 벌써 끝났는데 생각보다 할만한 듯 하다.

배운 것만 까먹지 않게 복습한다면 좋을텐데,
새로운 지식을 배우는 것 보다 복습이 더 힘들다...

찡찡대지말고 복습하자~

이대로 꾸준히 기록하겠다.

profile
Cheol

0개의 댓글