[Oracle] SQL- DML 객체, DCL 객체

*** 테이블 복사

-- 제약조건은 복사되지 않는다(not null 제외)
-- 형식 : create table table_name(컬럼명,...) as select~;

--컬럼이 조작된 경우, 컬럼명을 부여하여 생성
create table dname_sum(부서명, 급여합계)
as
select dname, sum(sapay) from sawon natural join dept group by dname;

--테이블 구조만 복사하는 경우 조건절을 부정형으로.
create table dept_ex
as
select * from dept where 1=2;  --***

*** DML

-- 데이터 조작 명령, 데이터 변경 명령, 트랜젝션 (처리) 명령, 데이터 흐름 명령, 이벤트 명령
-- 실행 대상 : 행***단위
-- insert(입력), update(변경), delete(삭제)

1. insert
- insert into table_name values(값,...); // 전체 컬럼값을 가진 입력
- insert into table_name(컬럼명,...) values(값,...); // 선택 컬럼값을 가진 입력***
- insert into table_name[(컬럼명,...)] select~; // 다중행 단위의 입력

2. delete
- delete [from] table_name where~

--테이블내의 모든행을 삭제하는 경우(DDL명령)
SQL> truncate table dept_ex;                  --***
테이블이 잘렸습니다.

3. update
-update table_name set 컬럼=값,... where~

* 모든 문제는 sawon_ex, dept_ex, gogek_ex 사본 테이블을 생성하여 작업할 것
--고객의 담당자가 없는 고객은 13번 사원으로 변경
SQL> update gogek_ex set godam=13 where godam is null;

--13번 담당자를 갖는 고객의 담당자를 null로 변경
SQL> update gogek_ex set godam=null where godam=13;

--sawon_ex 테이블에 직책이 사원인 사원들의 sawon 테이블 모든 컬럼 정보를 입력
SQL> insert into sawon_ex(sabun, saname, sapay)
          select sabun, saname, sapay from sawon where sajob='사원';

--17번 사원의 부서와 같은 사원들의 직책을 17번 사원의 직책으로 변경
SQL> update sawon_ex 
          set sajob=(select sajob from sawon_ex where sabun = 17) 
          where deptno=(select deptno from sawon_ex where sabun = 17);

--영업부 사원들의 급여를 10% 인상 변경
SQL> update sawon_ex 
           set sapay = sapay * 1.1
          where deptno = (select deptno from dept_ex where dname='영업부');

--관리부서 사원 중에서 회사의 평균급여보다 낮은 사원들을 삭제
SQL> delete from sawon_ex
           where deptno =(select deptno from dept_exx where dname='관리부' )
           and sapay<( select avg(sapay) from sawon_ex);

--총무부 사원들의 급여를 자신이 속한 직책의 평균급여로 변경
SQL> update sawon_ex s
          set sapay = (select avg(sapay) from sawon_ex e where e.sajob = s.sajob)
          where deptno = (select deptno from dept_ex where dname = '총무부');

*** Merge(병합:9i) ***

--한 번의 조건으로 입력 또는 변경을 수행하는 구문

--형식
merge into 테이블명 using 비교테이블명 on(두 테이블의 비교문-기본키대상)
when matched then
update set ~
when not matched then
insert values ~;

create table sawon_10 as select * from sawon where deptno=10;

(문제)

sawon 테이블과 비교하여 사원정보가
sawon_10 테이블에 존재하면 급여를 10%인상하고,
sawon_10 테이블에 존재하지 않는다면 입력하라

merge into sawon_10 s10 using sawon s on(s.sabun=s10.sabun)
when matched then
update set sapay=sapay*1.1
when not matched then
insert values(s.sabun,s.saname,s.deptno,s.sajob,s.sapay,s.sahire,s.sasex,s.samgr);

 

*** TCL (Tranjection)

-- 트랜젝션 관리 명령, 데이터 흐름 제어문, 이벤트 관리 명령
-- autocommit off(기본값) : 기본적으로 DML명령은 버퍼(메모리)에 저장
-- commit(저장), rollback(취소), savepoint(저장점)(MS-SQL save)

SQL> select * from dept;

 

(연습1)
SQL> insert into dept(deptno, dname, loc) values(50, '기술부', '서울');
1 개의 행이 만들어졌습니다.

SQL> savepoint a;
저장점이 생성되었습니다.

SQL> insert into dept(deptno, dname, loc) values(60, '기술2부', '서울');
1 개의 행이 만들어졌습니다.

SQL> savepoint b;
저장점이 생성되었습니다.

SQL> insert into dept(deptno, dname, loc) values(70, '영업관리부', '제주');
1 개의 행이 만들어졌습니다.

SQL> select * from dept;

SQL> rollback to savepoint a;
롤백이 완료되었습니다.

SQL> commit;
커밋이 완료되었습니다.

SQL> rollback;
롤백이 완료되었습니다.

SQL> select * from dept;

[참고]

MS-SQL은 autocommit on
      begin tran
insert~ ;
update~  ;
delete~   ;
      end tran

 

*** SQL 객체들

1) 시퀀스(sequence: 숫자 자동생성)
-- 규칙적 순서를 갖는 객체(숫자만 가능하고 자동부여 목적) [참고] MS-SQL : 컬럼의 속성(identity)으로 부여
-- 사용 : 데이터 입력시***만 사용!!!

SQL> create sequence s_deptno
  2  increment by 3                      // 증가치
  3  start with 101                        // 초기치
  4  maxvalue 999;                      // 최대치
     nocycle on(기본값)

시퀀스가 생성되었습니다.

SQL> insert into dept(deptno, dname, loc) values(s_deptno.nextval, '생산부', '제주');   // [시퀀스명.nextval] 형태
1 개의 행이 만들어졌습니다.

SQL> select * from dept;

    DEPTNO DNAME      LOC
---------- ---------- ----------
        10 총무부     서울
        20 영업부     대전
        30 전산부     부산
        40 관리부     광주
        50 기술부     서울
       101 생산부     제주

6 개의 행이 선택되었습니다.

SQL> insert into dept(deptno, dname, loc) values(s_deptno.nextval, '생산2부', '제주');
1 개의 행이 만들어졌습니다.

SQL> select * from dept;

    DEPTNO DNAME      LOC
---------- ---------- ----------
        10 총무부     서울
        20 영업부     대전
        30 전산부     부산
        40 관리부     광주
        50 기술부     서울
       101 생산부     제주
       104 생산2부    제주

7 개의 행이 선택되었습니다.

SQL> select s_deptno.currval from dual; // 현재 카운트 정보)

SQL> insert into dept(deptno, dname, loc) values(s_deptno.nextval, '생산부', '제주');
insert into dept(deptno, dname, loc) values(s_deptno.nextval, '생산부', '제주')
*
1행에 오류:
ORA-00001: 무결성 제약 조건(SKY.DEPT_DNAME_UQ)에 위배됩니다


SQL> select s_deptno.currval from dual;
CURRVAL
----------
       107 -- 입력시 실패하더라도 시퀀스를 증가

2) View(뷰) 

* 종류
1. 데이터 사전 뷰(Data Dictionary View) : 오라클에서 원하는 모든 정보를 얻기 위한 객체 
   (예. 현재세션 날짜형식 정보 : nls_session_parameters)

-all_types     :  모든 세션이 조회(요약된 정보)
-user_types  :  현재 세션이 조회(모든 정보)
-dba_types  :  DBA가 조회(모든 세션의 모든 정보)

-v$~ : DBA가 조회(DB의 동적 성능관련 정보)

-- 자신의 제약조건 정보조회(user_constraints / user_cons_columns) :  테이블-컬럼-제약정보
SQL> select a.constraint_name C이름, a.table_name T이름, b.column_name 컬이름, a.constraint_type 타입 
from user_constraints a, user_cons_columns b                           --***
where a.constraint_name=b.constraint_name;

SQL>  select a.owner, a.constraint_name C이름, a.table_name T이름, b.column_name 컬이름, a.constraint_type 타입
from dba_constraints a, dba_cons_columns b  --***
where a.constraint_name=b.constraint_name
    and (a.owner = 'SKY' or a.owner='SCOTT');

예시. 현재 세션의 제약조건 정보
SQL> select d.constraint_name, d.constraint_type, d.table_name, c.column_name from user_cons_columns c, user_constraints d
  2  where c.constraint_name = d.constraint_name;

CONSTRAINT_NAME                C TABLE_NAME                     COLUMN_NAME
------------------------------ - ------------------------------ --------------------
DEPT_DNAME_UQ                  U DEPT                           DNAME
DEPT_DEPTNO_PK                 P DEPT                           DEPTNO
SAWON_SAMGR_FK                 R SAWON                          SAMGR
SAWON_SASEX_CK                 C SAWON                          SASEX
SAWON_DEPTNO_FK                R SAWON                          DEPTNO
SAWON_SANAME_NN                C SAWON                          SANAME
SAWON_SABUN_PK                 P SAWON                          SABUN
GOGEK_GODAM_FK                 R GOGEK                          GODAM
GOGEK_GOJUMIN_UQ               U GOGEK                          GOJUMIN
GOGEK_GOBUN_PK                 P GOGEK                          GOBUN
SYS_C0011294                   C SAWON_EX                       SANAME

11 개의 행이 선택되었습니다.

2. 사용자 정의 뷰(보안 객체 : create view 권한)
--정보 뷰 : user_views
--형식 : create view view_name(컬럼명,...) as select~~;

--목적
- 제한된 엑세스***를 구현(보여주고 싶은 정보만 접근하도록) => 보안
- 복잡한 쿼리를 단순하게 사용(성능과는 무관)
- 컬럼조작이나 그룹화 작업에 의한 뷰가 아닌경우 DML명령 가능!(그러나 할 이유는 없음)

SQL> create view v1(부서명, 급여합계)
as
select d.dname, sum(s.sapay) from sawon s, dept d
where s.deptno = d.deptno
group by d.dname
having sum(sapay) > (select sum(sapay)*0.3 from sawon);

SQL> create view v2
  2  as
  3  select * from sawon where deptno=10
  4  with read only; -- 읽기 전용 뷰

뷰가 생성되었습니다.

SQL> create view v3
  2  as
  3  select * from sawon where deptno=10
  4  with check option [제약명];-- 뷰에 체크제약(조건절에 대한) 설정

뷰가 생성되었습니다.

SQL> update v3 set sapay=3100 where sabun=13;

1 행이 갱신되었습니다.

SQL> update v3 set deptno=20 where sabun=13;
update v3 set deptno=20 where sabun=13
       *
1행에 오류:
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다  --***

3) Index(색인) <-> full table scan(하나의 테이블 저장단위인 1 extent=64K 이하는 권장)
-- where절***에 자주 사용되는 컬럼*에 생성
-- PK, UQ 제약조건은 기본값으로 인덱스 생성
-- 정적* 데이터를 저장하는 컬럼 권장
-- 정보 뷰 : user_indexes, user_ind_columns 

-- 형식 : create index index_name on table_name(column);

-- 종류
1. b*tree index(normal) : PK/UQ, 중복성이 적은 컬럼에 생성
2. bitmap index : 중복성이 많은 컬럼에 생성
3. function-based index : 사용되는 컬럼에 수식을 포함하는 경우
4. reverse-key index(b*tree의 역방향키) : 동적 데이터 컬럼에 생성
5. desending index : 최근 데이터 위주의 검색을 주로 하는 경우
6. bitmap-join index : 조인조건에 생성

-- 인덱스 정보 확인
SQL> select table_name, index_name, index_type from user_indexes;

TABLE_NAME                     INDEX_NAME                     INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
SAWON                          SAWON_SABUN_PK                 NORMAL
GOGEK                          GOGEK_GOBUN_PK                 NORMAL
GOGEK                          GOGEK_GOJUMIN_UQ               NORMAL
DEPT                           DEPT_DEPTNO_PK                 NORMAL
DEPT                           DEPT_DNAME_UQ                  NORMAL

SQL> select table_name, index_name, column_name from user_ind_columns;

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------------
DEPT                           DEPT_DEPTNO_PK                 DEPTNO
DEPT                           DEPT_DNAME_UQ                  DNAME
GOGEK                          GOGEK_GOBUN_PK                 GOBUN
GOGEK                          GOGEK_GOJUMIN_UQ               GOJUMIN
SAWON                          SAWON_SABUN_PK                 SABUN

-- 생성명령
create index i_sawon_sabun on sawon(sabun);
create bitmap index i_sawon_sasex on sawon(sasex);
create index i_sawon_sapay on sawon(sapay*1.1);
create index i_sawon_samgr on sawon(samgr) reverse;
create index i_sawon_sahire on sawon(sahire desc);

SQL> select table_name, index_name, index_type from user_indexes;

TABLE_NAME                     INDEX_NAME                     INDEX_TYPE
------------------------------ ------------------------------ ----------------------
SAWON                          SAWON_SABUN_PK                 NORMAL
SAWON                          I_SAWON_SASEX                  BITMAP --***
SAWON                          I_SAWON_SAPAY                  FUNCTION-BASED NORMAL
SAWON                          I_SAWON_SAMGR                  NORMAL/REV
SAWON                          I_SAWON_SAHIRE                 FUNCTION-BASED NORMAL
GOGEK                          GOGEK_GOBUN_PK                 NORMAL
GOGEK                          GOGEK_GOJUMIN_UQ               NORMAL
DEPT                           DEPT_DEPTNO_PK                 NORMAL
DEPT                           DEPT_DNAME_UQ                  NORMAL

9 개의 행이 선택되었습니다.

SQL> select table_name, index_name, column_name from user_ind_columns;

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------------
DEPT                           DEPT_DEPTNO_PK                 DEPTNO
DEPT                           DEPT_DNAME_UQ                  DNAME
GOGEK                          GOGEK_GOBUN_PK                 GOBUN
GOGEK                          GOGEK_GOJUMIN_UQ               GOJUMIN
SAWON                          I_SAWON_SAHIRE                 SYS_NC00010$ --***
SAWON                          I_SAWON_SAMGR                  SAMGR
SAWON                          I_SAWON_SAPAY                  SYS_NC00009$
SAWON                          I_SAWON_SASEX                  SASEX
SAWON                          SAWON_SABUN_PK                 SABUN

9 개의 행이 선택되었습니다.

* 생성된 인덱스가 사용되지 못하는 경우***
- 부정 연산자 / is null / like 연산자 사용
- 컬럼 조작된 경우(인덱스가 생성된 컬럼 그대로 사용되지 않은 경우)
- 생성된 인덱스 컬럼의 순서*대로 사용하지 않은 경우

 

*** DCL

 

1. 권한 관련 명령, 데이터 관리(제어) 명령

--grant(부여), revoke(취소) [deny(거부) : MS-SQL]

2. 권한(privilege) 종류
- system privilege(사용권한) : 
                               사용가능한 명령 권한 create table, create view,... 상속절 : with admin option
- object privilege(객체 액세스 권한) :
                               대상* 객체가 존재하고, 객체의 소유자가 권한 부여 : select, update on sawon, insert on gogoek,...
- 상속절 : with grant option

 

3. 형식
grant  권한,...  [on 객체명]    to [세션|롤],... [with admin|grant option];  // 상속가능
revoke 권한,... [on 객체명] from [세션|롤];

 

4. Role (보안객체)
--관련 privilege들의 집합
--기본 시스템 롤 : connect, resource, dba,...

SQL> select grantee, privilege from dba_sys_privs
  2  where grantee='CONNECT' or grantee='RESOURCE'
  3  order by 1;

GRANTEE                        PRIVILEGE
------------------------------ ------------------------
CONNECT                        CREATE SESSION
RESOURCE                       CREATE CLUSTER
RESOURCE                       CREATE INDEXTYPE
RESOURCE                       CREATE OPERATOR
RESOURCE                       CREATE PROCEDURE
RESOURCE                       CREATE SEQUENCE
RESOURCE                       CREATE TABLE
RESOURCE                       CREATE TRIGGER
RESOURCE                       CREATE TYPE

5. 롤관리 (create role권한 필요)
1) 롤을 생성
2) 롤에 관련 권한 부여
3) 세션에게 롤 권한을 부여

--예시
SQL> create role sawon_admin; --***
롤이 생성되었습니다.

SQL> grant select on sawon to sawon_admin;
권한이 부여되었습니다.

SQL> grant select on dept to sawon_admin;
권한이 부여되었습니다.

SQL> grant select on gogek to sawon_admin;
권한이 부여되었습니다.

SQL> grant sawon_admin to scott;                                 --***
권한이 부여되었습니다.

--권한 관련 정보 뷰
- user_sys_privs : 현재 세션의 시스템 권한 정보(롤을 통한 privilege 제외)
- user_tab_privs : 현재 세션의 객체 권한 정보(롤을 통한 privilege 제외)
- role_sys_privs : 현재 세션의 시스템 롤 권한 정보
- role_tab_privs : 현재 세션의 객체 롤 권한 정보

## Synonym(동의어)

1. create synonym 권한 필요(보안객체)

--형식 : create [public] synonym sy_name for table_name;
                  ---------
   관리자만 가능***

2. garden
SQL> create synonym s for sawon; // [s=sawon]
-- scott
SQL> select * from sky.s;

SQL> create public synonym s for garden.sawon; // [s=sky.sawon] ***