VIEW(뷰), SYNONYM(시노님)

SYNONYM (시노님)
사용자가(지금 사용중인 user 계정) 다른 사용자의 테이블이나 객체를 참조하는 방법.

 

일반 유저들은 권한이 없으니 먼저 관리자 계정에서 권한을 줘야 한다.

 

윈도우 키 + R > cmd > cmd창 띄우기

 

명령어 입력 : sqlplus / as sysdba

 

이후에

GRANT CREATE SYNONYM TO sky -- 시노님 만들기 위한 권한 부여

GRANT SELECT ON employees TO sky; -- 시노님을 sky계정에서 보기 위한 권한 부여

GRANT  CREATE  VIEW  TO  sky; -- VIEW를 만들기 위한 권한 부여

명령어 입력

 

권한이 잘 들어갔는지 확인 해보기위해 

본인이 사용중인 계정 (저는 sky)가서 입력 

 

SELECT * FROM user_sys_privs;

 

 

sky 계정에서 hr 계정의 employees 테이블 접근

SELECT * FROM hr.employees;

 

sky계정에서 시노님 작성

 CREATE  SYNONYM  employees  FOR  hr.employees;

시노님 확인

SELECT * FROM syn;

employees 라는 시노님으로 SELECT 
SELECT * FROM employees;

hr.employees = 테이블 내용 확인

 

 

시노님 삭제

DROP SYNONYM employees;

 

 

VIEW

* 가상 테이블임

* 사용자에게 접근이 허용된 자료만 제한적으로 보여줌

* SELECT 문의 쿼리가 저장됨

 

먼저 관리자로 권한을 주자.

cmd창 - sqlplus / as sysdba

GRANT  CREATE  VIEW  TO  sky; -- VIEW를 만들기 위한 권한 부여

 

 

SELECT * FROM user_sys_privs;

sky계정으로 돌아가서 권한이 잘 부여 되었는지 확인하자

 

 

JOIN 방식의 쿼리로 VIEW 만들기

        SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, s.cNum, cName, qty
        FROM book b
        JOIN pub p ON b.pNum = p.pNum
        JOIN dsale d ON b.bCode = d.bCode
        JOIN sale s ON d.sNum = s.sNum
        JOIN cus c ON s.cNum = c.cNum;

먼저 JOIN으로 테이블 하나로 합쳐주고

 

      CREATE VIEW panmai
      AS
        SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, s.cNum, cName, qty
        FROM book b
        JOIN pub p ON b.pNum = p.pNum
        JOIN dsale d ON b.bCode = d.bCode
        JOIN sale s ON d.sNum = s.sNum
        JOIN cus c ON s.cNum = c.cNum;

CREATE VIEW (뷰이름) AS 

형태로 VIEW 만들기

 

SELECT * FROM panmai; -- 만든 뷰 확인

SELECT * FROM tab; -- 테이블 목록 확인 코드지만 뷰의 테이블도 나온다.

 

VIEW 수정하기

      CREATE OR REPLACE VIEW panmai
      AS
        SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, s.cNum, cName, qty, qty * bPrice amt
        FROM book b
        JOIN pub p ON b.pNum = p.pNum
        JOIN dsale d ON b.bCode = d.bCode
        JOIN sale s ON d.sNum = s.sNum
        JOIN cus c ON s.cNum = c.cNum;

CREATE 바로 뒤에 OR REPLACE만 붙여주고 고칠거 고치고 다시 실행하면 된다.

(없으면 새로 만들고 있으면 그걸 수정한다.)

 

 

만든 VIEW 컬럼 확인하기

     SELECT * FROM col WHERE tname = 'PANMAI';
     DESC panmai;

VIEW 소스 확인하기

SELECT  *  FROM  user_views;
-- view_name, text

 

 

(예제)

CREATE VIEW empView
AS
SELECT empNo, name, DECODE( MOD(SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자' ) gender,
TO_DATE( CASE
         WHEN SUBSTR(rrn, 8, 1) IN (1,2,5,6) THEN '19'
         WHEN SUBSTR(rrn, 8, 1) IN (3,4,7,8) THEN '20'
         ELSE '18'
         END || SUBSTR(rrn, 1, 6), 'YYYYMMDD') birth,
       hireDate, sal, bonus, sal + bonus totPay,
       TRUNC ( CASE
               WHEN sal+bonus >= 3000000 THEN 0.03
               WHEN sal+bonus >= 2000000 THEN 0.02
              ELSE 0
              END * (sal + bonus), -1) tax
FROM emp;

birth = 주민번호(rrn) 뒷자리 첫번째 숫자 뽑아서 90년대 사람인지(19xx) 20년대 사람인지(20xx) 구분 하기 위해서

CASE문 써주고 구분 끝났으면 문자열 붙이기 ||를 이용해서 뽑아낼 월과 일에 년도를 붙여준다.

 

      SELECT empNo, name, gender, birth, TRUNC( MONTHS_BETWEEN(SYSDATE, birth)/12 ) age,
                 sal, bonus, totPay, tax, totPay-tax spay
      FROM empView;

이 만든 VIEW 테이블을 이용해서 나이도 출력 할 수 있다.

애초에 테이블 처음 만들때 같이 해도 되지만 복잡해서 따로 출력을 하는 것

 

age = TRUNC로 절삭(내림차순) 해주고 현재 시간(SYSDATE)와 위에서 구한 birth 사이의 달을 12로 나누면 나이다

MONTHS_BETWEEN 은 두 날짜 사이의 개월 수를 구하는 함수이다.

 

VIEW 삭제

DROP VIEW 뷰 이름

        DROP VIEW panmai_year;
        DROP VIEW panmai;
        DROP VIEW empView;

 

조건에 맞지 않는 경우 수정하지 못하게 설정하기

(WITH CHECK OPTION)

      CREATE OR REPLACE VIEW testView2
      AS
      SELECT id, name, city
      FROM test1
      WHERE city = '서울'
      WITH CHECK OPTION;