1.7 DATABASE 기타 DB객체 (VIEW, TRIGGER, FUNCTION, PROCEDURE, PACKAGE, SYNONYM, LINK 등)

기타 DB객체 (VIEW, TRIGGER, FUNCTION, PROCEDURE, PACKAGE, SYNONYM, LINK 등)

참고링크: https://gent.tistory.com/361

 

VIEW

테이블과 흡사한 오브젝트

실제로 데이터를 저장하고 있지 않지만 DML 작업이 가능

가상의 테이블

CREATE [OR REPLACE] [FORM|NOFORCE] VIEW 뷰이름
	[(column_aliases)]
AS
	SELECT
    [WITH READ ONLY]
    [WITH CHECK OPTION [CONSTRAINT 제약조건명]]

OR REPLACE	해당 구문을 사용하면 뷰를 수정할 때 DROP 없이 수정 가능
FORCE	뷰를 생성할 때 쿼리문의 테이블, 컬럼, 함수 등이 존재하지 않아도 생성 가능
NORORCE	뷰를 생성할 때 쿼리문의 테이블, 컬럼 함수 등이 존재하지 않으면 생성 불가
column_aliases	SELECT 컬럼의 별칭을 미리 정의
WITH READ ONLY	SELECT만 가능하다. (INSERT, UPDATE, DELETE 불가능)
WITH CHECK OPTION	WHERE 절의 조건에 해당하는 데이터만 저장, 변경이 가능

단순 뷰 생성

• 단일 테이블에 필요한 컬럼을 나열한 것
• 조인, 함수, GROUP BY, UNION 등을 사용하지 않음
• 단순 뷰는 SELECT, INSERT, UPDATE, DELETE 자유롭게 사용 가능

CREATE OR REPLACE VIEW v_emp 
AS
    SELECT empno
         , ename
         , job
         , hiredate
      FROM emp
;

복합 뷰 생성

• 함수 등을 사용할 경우 컬럼 별칭 필수 부여 ( EX) *** AS NEW_NAME)
• 조인, 함수, GROUP BY, UNION 등을 사용하여 뷰를 생성
• SELECT는 자유롭게 가능하고, INSERT, UPDATE, DELETE 상황에 따라서 가능

CREATE OR REPLACE VIEW v_emp 
AS
    SELECT a.empno
         , a.ename
         , a.job
         , TO_CHAR(a.hiredate, 'YYYY-MM-DD') AS hiredate
      FROM emp a
         , dept b
     WHERE a.deptno = b.deptno
;

뷰 컬럼 코멘트 추가

COMMENT ON COLUMN v_emp.empno IS '사원번호';

뷰 삭제

DROP VIEW v_emp;

컬럼 별칭 선언

컬럼 별칭을 상단에 미리 정의하면 쿼리문의 컬럼 순서대로 별칭이 부여

함수를 사용 후 별칭을 부여하지 않아도 가능

SELECT 컬럼의 별칭과 일치하지 않아도 순서와 개수만 맞으면 가능

CREATE OR REPLACE VIEW v_emp 
(
    empno,
    ename,
    job,
    hiredate
)
AS
    SELECT a.empno
         , a.ename
         , a.job
         , TO_CHAR(a.hiredate, 'YYYY-MM-DD')
      FROM emp a
         , dept b
     WHERE a.deptno = b.deptno
;

FORCE 옵션 사용

쿼리문에 테이블, 컬럼, 함수 등이 존재하지 않을 경우 오류가 발생하지만 뷰는 생성됨

INVAILD 상태라 뷰는 동작하지 않음

오류가 없으면 정상적으로 뷰 생성

테이블, 함수 등을 미리 생성하지 않고 뷰를 먼저 생성해 놓는 경우 유용하게 사용 가능한 옵션

CREATE OR REPLACE FORCE VIEW v_emp 
AS
    SELECT empno
         , ename
         , job
         , hiredate
      FROM emp
;

WITH READ ONLY 옵션 사용

SELECT만 가능

CREATE OR REPLACE VIEW v_emp 
AS
    SELECT empno
         , ename
         , job
         , hiredate
      FROM emp
      WITH READ ONLY
;

WHIT CHECK OPTION

해당 옵션을 사용하면 INSERT, UPDATE, DELETE를 사용할 때 WHERE절의 조건에 해당하는 데이터만 접근이 가능

ex) deptno = '10'

--제약조건명을 지정하지 않는 경우
CREATE OR REPLACE VIEW v_emp 
AS
    SELECT empno
         , ename
         , job
         , hiredate
         , deptno
      FROM emp
     WHERE deptno = '10'
      WITH CHECK OPTION
;

INSERT INTO v_emp(empno, ename, deptno) VALUES(9999, 'TEST', 20)
 -- ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배됨
-- deptno = '20'은 WHERE절의 조건에 부합하지 않아서 INSERT시 오류가 발생




--제약조건명을 지정한 경우
CREATE OR REPLACE VIEW v_emp 
AS
    SELECT empno
         , ename
         , job
         , hiredate
         , deptno
      FROM emp
     WHERE deptno = '10'
      WITH CHECK OPTION CONSTRAINT emp_ck
;



 -- WITH CHECK OPTION [CONSTRAINT 제약조건명]
 -- 제약조건명을 지정하지 않아도 기능은 동일하게 작동
 -- 제약조건이 생성될때 원하는 이름으로 생성되느냐 시스템이 자동으로 이름을 부여하느냐의 차이
 -- 지정하지 않으면 SYS_XXXXXXX로 생성

 

DML 사용 시 유의사항

단순 뷰인 경우 INSERT, UPDATE, DELETE가 자유로우며 (NOT NULL 컬럼 주의)
함수, UNION, GROUP BY 등을 사용한 복합 뷰인 경우 INSERT, UPDATE, DELETE가 불가능
(조인만 사용한 복합 뷰인 경우 제한적으로 가능)

CREATE OR REPLACE VIEW v_emp 
AS
    SELECT a.empno
         , a.ename
         , a.job
         , TO_CHAR(a.hiredate, 'YYYY-MM-DD') AS hiredate
         , b.deptno
      FROM emp a
         , dept b
     WHERE a.deptno = b.deptno
;


UPDATE v_emp

      SET deptno = '10'
 WHERE empno = '7369'

-- ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없음
-- deptno 컬럼은 dept 테이블의 컬럼을 사용하였으므로 empno로 접근이 불가

INSERT INTO v_emp(empno, ename, deptno) VALUES(9999, 'TEST', 20)
-- ORA-01776: 조인 뷰에 의하여 하나 이상의 기본 테이블을 수정 불가

INSERT INTO v_emp(empno, ename) VALUES(9999, 'TEST')
-- deptno 컬럼을 제외하면 정상적으로 입력

UPDATE v_emp
   SET hiredate = SYSDATE
 WHERE empno = '7369'

-- ORA-01733: 가상 열은 사용할 수 없음
-- 함수를 사용한 컬럼(hiredaet)은 INSERT, UPDATE를 할 수 없음

 

CREATE OR REPLACE VIEW v_emp 
AS
    SELECT empno
         , ename
         , job
      FROM emp
     WHERE deptno = '10'
     
     UNION ALL
     
    SELECT empno
         , ename
         , job
      FROM emp
     WHERE deptno = '20'
;


UPDATE v_emp
      SET ename = 'TEST'
 WHERE empno = '7369'

-- ORA-01732: 뷰에 대한 데이터 조작이 부적합
-- UNION, GROUP BY 등을 사용한 쿼리는 INSERT, UPDATE, DELETE를 사용할 수 없음

 

TRIGGER

데이터베이스 이벤트에 반응하려 실행되는 프로그램 단위

주 사용 목적은 테이블 데이터틔 무결성 보장, 데이터베이스 관리의 자동화 등이라고 할 수 있음

 

DML 트리거

• DML문 (INSERT,UPDATE,DELETE)이 테이블의 하나 이상의 데이터에 영향을 미칠 때에 자동으로 실행
• 프로시저나 함수는 사용자가 직접 호출해야 하지만 오라클에서 알려주지 않고 자동 호출 처리

 

특징

• 자동으로 실행 가능
• 수동으로는 실행 불가능
• 작동이 일어난 트리거 대상 테이블에 대해서는 트리거 내용이 존재할 수 없음
• BEFORE, AFTER 두 가지 트리거 존재
• 트랜잭션 일부로 처리 (COMMIT, ROLLBACK, SVAEPOINT 문장 포함 안 됨)

 

생성 구문

CREATE [OR REPLACE] TRIGGER <트리거 이름>
{BEFORE|AFTER}<이벤트>ON<테이블>
[FOR EACH ROW]
[WHEN (<조건>)]
[DECLARE <변수선언>]
BEGIN
         <실행코드>
[EXCEPTION <예외사항>]
END <트리거 이름>;

비활성화/활성화 구문

alter trigger tri_name [disable|enable];
alter table table_name [disable|enable] all triggers;

 

FUNCTION

참고 링크: https://exmemory.tistory.com/77

 

오라클에서는 TO_DATE, CONCAT 과 같은 내장 함수가 있지만 사용자가 생성할 수 있음

 

생성 문법

CREATE OR REPLACE FUNCTION function_name (agr1 NUMBER)
    RETURN VARCHAR2
IS
    return_value VARCHAR(14);
BEGIN
    SELECT 
    	data
    INTO
    	return_value
    FROM 
    	TABLE
    WHERE 
    	sequence = arg1
        and ROWNUM =1;
    RETURN return_value;
END;

-- 실행하는 법
SELECT function_name(1000413) FROM dual;

• 위의 방법은 Function 반환 결과가 1 Row 1 Column 일때 사용 가능
• 여러개의 Row와 여러개의 Column을 반환하려면 Oracle Table Function 또는 Pipelined Table Function을 사용해야 함
• Oracle Table Function은 Result Set(Multi column + Multi Row)의 형태를 인자 값으로 받아들여 값을 Return할 수 있는 PL/SQL Function
• Pipelined Table Function은 Oracle Table Function과 비슷하지만 전체 집합을 한 번에 처리하지 않고 Row 단위로 한 건씩 처리 가능

Pipelined Table Function

-- 1. Return 받을 Object Type 생성
CREATE OR REPLACE TYPE test_type AS OBJECT
(
    sn NUMBER,
    yyyymmdd VARCHAR2(16)
);

-- 2. Object Type을 가지는 Collection Type 생성
CREATE OR REPLACE TYPE test_table AS TABLE OF test_type;

-- 3. Pipelined Table Function 생성
CREATE OR REPLACE FUNCTION test_func_2 (sn NUMBER)
    RETURN test_table
    PIPELINED
IS
    t_type test_type;
BEGIN
    FOR rec IN (
         SELECT sn
        		, yyyymmdd
         FROM data_table
         WHERE sequence = sn
    ) LOOP
        t_type := test_type(rec.sn, rec.yyyymmdd);
        PIPE ROW(t_type);
    END LOOP;
    
    RETURN;
END;

-- Pipelined Table Function를 실행하는 방법
SELECT * FROM TABLE(test_func_2(1000413));

 

 

 

 

PROCEDURE

• 오라클에서의 프로시저는 PL/SQL을 통해 생성
• 자주 사용하는 SQL을 프로시저로 만든 뒤 필요 할때마다 호출,사용하여 작업 효율 향상
• 함수는 특정 연산을 수행한 뒤 결과 값을 반환하지만 프로시저는 특정한 로직을 처리하기만 하고 결과 값은 반환하지 않는 서브 프로그램

프로시저 생성

CREATE OR REPLACE PROCEDURE EX_PROC
(
   P_DEPARTMENT IN VARCHAR2,
   P_STUDENT_CNT IN NUMBER
)
IS
P_UNIVERSITY VARCHAR2(100)  := '서울대학교';

BEGIN

INSERT INTO UNIVERSITY1 (UNIVERSITY, DEPARTMENT, STUDENT_CNT)
VALUES (P_UNIVERSITY, P_DEPARTMENT, P_STUDENT_CNT);
COMMIT;

END EX_PROC;

-- 실행
EXEC EX_PROC('물리학과',500);

-- 결과

UNIVERSITY || DEPARTMENT || STUDENT_CNT
서울대학교 || 물리학과 || 500

PACKAGE

참고 링크: https://coding-factory.tistory.com/456

• 변수, 상수, 서브프로그램 등의 항목을 묶어놓은 객체
• 컴파일 과정을 거쳐 데이터베이스에 저장되며 다른 프로그램에서 패키지의 항목을 참조하고 실행 가능

사용하는 이유

프로시저 같은 서브 프로그램의 수가 많아지고 관리가 어려운 상황을 방지하기 위하여 사용

 

사용법

선언부, 본문, 실행부로 구성

 

선언부

CREATE OR REPLACE PACKAGE EX_PKG IS --패키지 선언

FUNCTION FUNC_1(P_PRODUCT_ID IN NUMBER) --패키지로 묶을 함수
RETURN VARCHAR2;

PROCEDURE PROC_1; --패키지로 묶을 프로시저1

PROCEDURE PROC_2(P_PRODUCT_ID IN NUMBER); --패키지로 묶을 프로시저2

END EX_PKG; --패키지 종료

본문

CREATE OR REPLACE PACKAGE BODY  EX_PKG  IS --패키지 본문

    ----------패키지 선언부 함수1----------
    FUNCTION FUNC_1(P_PRODUCT_ID IN NUMBER)
    RETURN VARCHAR2
    IS V_PRODUCT_NAME VARCHAR(100);
    BEGIN
    SELECT PRODUCT_NAME INTO V_PRODUCT_NAME
    FROM PRODUCTS
    WHERE PRODUCT_ID = P_PRODUCT_ID;
    RETURN NVL(V_PRODUCT_NAME, '해당제품없음');

    END FUNC_1;

   ----------패키지 선언부 프로시저1----------
    PROCEDURE PROC_1
    IS
    CURSOR EX_CUR IS
    SELECT 
    PRODUCT_ID,
    PRODUCT_NAME
    FROM PRODUCTS;
    
    BEGIN
    
    FOR I  IN EX_CUR LOOP
    
    DBMS_OUTPUT.PUT_LINE(' 제품ID : ' || I.PRODUCT_ID);
    DBMS_OUTPUT.PUT_LINE(' 제품명 : ' || I.PRODUCT_NAME);
    
    END LOOP;
    
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
    
    END PROC_1;

    ----------패키지 선언부 프로시저2----------
    PROCEDURE PROC_2(P_PRODUCT_ID IN NUMBER)
    IS
    CURSOR EX_CUR IS
    SELECT 
    PRODUCT_ID,
    PRODUCT_NAME
    FROM PRODUCTS
    WHERE PRODUCT_ID = P_PRODUCT_ID;
    
    BEGIN
    
    FOR I  IN EX_CUR LOOP
    
    DBMS_OUTPUT.PUT_LINE(' 제품ID : ' || I.PRODUCT_ID);
    DBMS_OUTPUT.PUT_LINE(' 제품명 : ' || I.PRODUCT_NAME);

    END LOOP;
    
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM||'에러 발생 ');
    
    END PROC_2;

END EX_PKG; --패키지 종료

실행부

-- DBMS_OUTPUT.PUT_LINE을 출력하기 위해 사용
SET SERVEROUTPUT ON; 

-- 패키지 실행
VARIABLE RESULT VARCHAR2;
EXEC :RESULT := EX_PKG.FUNC_1(10);
PRINT :RESULT;

EXEC EX_PKG.PROC_1;

EXEC EX_PKG.PROC_2(10);

 

SYNONYM

• ALIAS처럼 이름을 줄여주는 역할 수행
• 시노님은 테이블의 이름을 설정
• 보통 다른 유저의 객체(테이블, 뷰, 프로시저, 함수, 패키지, 시퀀스 등)를 참조할 때 많이 사용
• 실제로 SYNONYM을 이용하는 이유는 다른 유저의 객체를 사용할때 유저의 이름과 객체의  실제이름을 사용하는데 그 두개를 감춤으로써 데이터베이스의 보안을 개선하기위해 사용

시노님 생성

• PUBLIC은 모든 사용자가 접근이 가능하도록 설정
• PUBLIC을 선언해주지 않으면 기본값으로 PRIVATE가 선언

CREATE [ PUBLIC ] SYNONYM [ 시노님 이름 ]
FOR [ 객체 이름 ]

1. 권한 부여

GRANT ALL ON oracleStury TO user2;
-- user2에게 테이블 조작 권한 부여

GRANT SELECT ON oracleStury TO user2;
-- user2에게 테이블 SELECT 권한 부여

2. USER 테이블 접근

[유저명].[테이블명]을 적어줘야 조회 가능

SELECT * FROM user1.oracleStudy;

CREATE SYNONYM u1_oralStudy FOR user1.oracleStudy;
-- synonym을 사용하여 테이블 이름을 변경

SELECT * FROM u1_oralStudy;
-- 시노님으로 만든 이름을 사용할 수 있음
-- user1.oracleStudy 와 u1_oralStudy 의 값이 동일하게 출력 예정

시노님 삭제

DROP SYNONYM u1_oralStudy;

 

LINK

참고 링크: https://kkh0977.tistory.com/377, https://bebeya1.tistory.com/3

 

설명

• 디비 링크 란 물리적으로 데이터베이스가 분리되어 있을 경우 오라클 서버의 ALIAS(별명)을 이용하여 서로 다른 DB에 접근하는 Oracle DBMS만의 기술
• 데이터베이스 링크는 오라클(Oracle) 서버들끼리 데이터베이스 링크를 만들어서 하나의 데이터베이스처럼 사용하기 위한 방법
• 디비 링크를 사용하면 다른 컴퓨터에 있는 테이블 및 함수 등을 조회 가능
• ​디비 링크를 사용하면 서로 다른 DB간에 조인도 가능
• 다른컴퓨터 테이블 및 함수 등을 사용하기 위해서는 사용 컴퓨터에 컴퓨터 디비 링크 정보를 등록 필수
• PUBLIC/PRIVATE 종류의 링크가 존재

PUBLIC

• 모든 사용자가 사용 가능
• 보안에는 취약하지만 편리함

 

PRIVATE

• 특정 사용자가 사용
• 보안 우수
• 각각의 사용자마다 DBLINK를 생성해줘야 생성 시에도 작업이 필요

등록 방법

• 만약 원격 DB에 접속해서 무언가를 하실려면 어떤 계정에 대한 Link가 필요하며, tnsnames.ora 파일에 있는 원격 디비에 대한 Alias를 등록 필요
• 같은 DB 서버 상의 디비 링크 등록은 오라클 DBMS를 사용해서 간편하게 등록 후 테이블 및 함수 등을 조회 및 사용 가능

LINK 생성

 

1. 생성 권한 부여

데이터베이스 링크를 생성하기 위해서는 "DATABASE LINK"라는 권한이 필요

PRIVATE 데이터베이스 링크 생성 권한을 부여합니다.
GRANT CREATE DATABASE LINK TO TESTUSER;

PUBLIC 데이터베이스 링크 생성 권한을 부여합니다.
GRANT CREATE PUBLIC DATABASE LINK TO TESTUSER;

PRIVATE 데이터베이스 링크 생성 권한을 회수합니다.
Revoke CREATE DATABASE LINK FROM TESTUSER;

PUBLIC 데이터베이스 링크 생성 권한을 회수합니다.
GRANT DROP PUBLIC DATABASE LINK TO TESTUSER;

 

2. 데이터베이스 링크 생성

데이터베이스 링크 생성 권한을 부여 받은 TESTUSER로 접속 후 명령어를 실행하여 데이터베이스 링크를 생성

보안을 위해서 PRIVATE 링크를 생성하기를 원한다면 PUBLIC 빼고 명령어를 실행

CREATE  PUBLIC DATABASE LINK TESTUSER_LINK
CONNECT TO R_USER IDENTIFIED BY "RPassword" USING "TestUser_TNS";

3. 데이터베이스 링크 삭제

삭제 시 주의 할 점

• 기존에 존재하던 DB 링크를 삭제하고 같은 이름으로 새로 DB 링크를 생성하였을 경우, 기존 DB 링크를 사용하던 프로시저나 뷰는 다시 컴파일 필요 

DROP  PUBLIC  DATABASE LINK TESTUSER_LINK;

4. 생성된 데이터베이스 링크로 쿼리문을 실행

만든 링크 데이터베이스 명인 TESTUSER_LINK 를 이용해서 원격지 테이블을 조회

SELECT * FROM REMOTE_TABLE@TESTUSER_LINK;

REMOTE_TABLE은 원격지 오라클 데이터베이스의 테이블 명

데이터베이스 TESTUSER_LINK 링크를 생성할 때 유저를 넣는데 해당 유저의 권한으로 조회가 가능한 테이블만 컨트롤 가능