[오라클/SQL] SYNONYM (동의어), MERGE(머지), Data Dictionary(딕셔너리)

SYNONYM 이란?

    SYNONYM(시노님)은 테이블 이름을 별칭으로 다르게 만들어 주는 것으로 생각하면 된다. 테이블 이름을 변경하는 rename과 다르다. 

    아래에서와 같이 paul 사용자에게 hr 사용자의 employees 테이블에 접근할 수 있는 권한을 주고, paul 사용자가 hr.employees로 접근할 때 이름이      길고, 불편하다면 Synonym을 사용해서 이 긴 테이블 이름 hr.employees을 HR_EMP식으로 변경해줄 수 있다.

 

SYNONYM 을 사용하는 이유 

    오브젝트명을 짧게하여 SQL문을 단순화 시킬 수 있다.

    스키마, 테이블 명이 변경 되어도 시노님만 재생성하면 SQL문은 변경하지 않아도 된다.

    오브젝트를 외부에 제공할 경우 스키마, 오브젝트명을 숨길 수 있어 보안에 좋다.

create [public/private] synonym HR_EMP for hr.employees;

select last_name, salary from HR_EMP;

drop [public/private] sysnonym HR_EMP

 

MERGE

    MERGE 는 하나 혹은 그 이상의 테이블에 대한 INSERT나 UPDATE 작업을 수행할 때, 여러 명령어들을 묶어서 질의하게 해주는 기능이다.
    여러개의 동장을 조합해서 실행하는 작업이다.
    INSERT나 UPDATE , DELETE 까지 한번에 수행이 가능하다.
    단일 테이블에 MERGE 자주 사용된다.

    다른 테이블이 아니라 직접 내용을 넣는다면 USING 절에 dual 테이블을 사용한다. 

ON 조건에 일치하면 UPDATE하고, 불일치하면 INSERT를 하는 쿼리문

'MERGE INTO 테이블_명 USING SELECT ~ ON 조건 
  WHEN MATCHED THEN UPDATE ~나 DELETE ~ 구문
  WHEN NOT MATCHED THEN
    INSERT 구문 
  WHERE ~ 조건'식

Q.  merge를 사용해서 사번 100번이 있으면 부서를 20으로 변경하고, 없으면 사번 101번 Neena 사용자를 20번 부서에 넣어보시오.

  MERGE INTO employees USING dual ON (employee_id = 100)
    WHEN MATCHED THEN
      UPDATE 
        SET deptment_id = 20
    WHEN NOT MATCHED THEN
      INSERT (employee_id, last_name, department_id) VALUES(101, 'Neena', 20); 
      -- ‘1행이 병합되었습니다’ 메시지가 나온다.

Q. Sales 부서의 사원들에게 급여의 10%와 $100을 보너스로 주고, 그 외의 부서에 대해서는 급여의 10%만 보너스로 주되, 
   급여가 $8,000 이하의 직원에 대해서만 적용시키시오.

create table bonus(employee_id number, bonus number default 100);
					// 보너스 항목에 별도의 항목이 안들어가면 100을 보너스로 준다는 default식 
insert into bonus(employee_id)
select employee_id from employees e JOIN departments d
		ON e.department_id = b.department_id
where department_name = 'Sales' ;

commit;

select * from bonus;

merge into bonus b using(select employee_id, salary, department_id from employees) e
on (e.employee_id = b. employee_id)
when matched then 
update 
set bonus = salary * 1.1
delete where salary > 8000
when not matched then 
insert values(e.employee_id, salary * 0.1)
where salary <= 8000;

 

 

Date Dictionary 란?

    데이터 딕셔너리는 Oracle 데이터베이스에 내장된 모든 객체의 정보를 가지고 있는 파일이다. 
    사용자가 관리하지 않고 Oracle DBMS가 직접 관리해서 저장하고 있다. 어느 객체의 정보를 얻으려면 이 데이터 딕셔너리를 이용하면 된다. 
    데이터 딕셔너리는 데이베이스 생성 시 함께 만들어 지며 SYSTEM 테이블 스페이스에 Table이나 View 형태로 저장되어 있다.  
    데이터 딕셔너리에는 현재 데이터베이스에 접속한 사용자가 소유한 객체 정보를 가지고 있는 USER, 
    사용자가 접근 가능한 한 모든 스키마 정보를 가지고 있는 ALL, 
    데이터베이스 관리를 위한 정보를 가지고 있는 DBA 세 가지 종류가 있다. 
    딕셔너리에 저장된 모든 데이터는 대문자이다. 

종류	내용
DBA_	DB 전체에 포함되는 모든 객체에 대한 자세한 정보
ALL_	자신이 생성한 객체와 다른 사용자가 만든 객체중에서 자신이 볼 수 있는 정보
USER_	자신이 생성한 모든 객체에 대한 정보

 

select * from tabs; 

select username from dba_users

select table_name from all_tables;
	// Oracle + hr 이 만든 것을 모두 볼 수 있음.
    
 select count(table_name) from dictionary;       

 select comments from dictionary
 where table_name = 'USER_TABLES';