영어청취와 사업아이디어, 컴퓨터무료강의가 있는 곳

데이터 베이스 무결성(Data Integrity)

관계형 데이터 베이스는 수많은 개체(Entity)와 관계(Relation)들이 저장되어 있는 복합체라고 할 수 있으며 이러한 개체와 관계들을 테이블이라는 형태로 구현한다.

데이터 무결성의 종류로는 크게 컬럼에 대한 무결성을 유지하는 것이 도메인 무결성이고 하나의 행(Row)에 대해서 무결성을 유지하는 것이 개체무결성 이러한 개체와 개체간의 무결성을 유지하는 것이 참조 무결성이라고 할 수 있다.

데이터 무결성은 데이터베이스의 데이터가 업무 규칙을 준수함을 보장하며 데이터 무결성

을 유지 관리할 수 있는 기본적인 방법으로는 다음 세 가지가 있습니다.

• 응용 프로그램 코드

• 데이터베이스 트리거

• 선언적 무결성 제약 조건

설계 시 세 개의 방식 중 하나를 사용하여 업무 규칙을 매핑하며 데이터베이스 관리자는 주

로 설계자가 선택한 방식을 구현하고 무결성 요구 사항에 대한 성능상 요구의 균형을 맞춥

니다.

1. 응용 프로그램 코드를 이용한 방법

응용 프로그램 코드는 데이터베이스 내의 내장 프로시저 또는 클라이언트에서 실행 중인

응용 프로그램으로 구현할 수 있음.

2. 데이터베이스 트리거를 이용한 방법

  데이터베이스 트리거는 열 삽입 또는 열 갱신 같은 특정 이벤트가 테이블에 발생할 때
실행되는 PL/SQL 프로그램으로 활성화하거나 비활성화할 수 있습니다.
즉, 이벤트 발생 시 실행되도록 설정하거나 정의했더라도 실행되지 않도록 설정할 수 있습니다. 데이터베이스 트리거는 대개 하나의 무결성 제약 조건으로 정의할 수 없는 복잡한 업무 규칙을 강제로 시행할 경우에만 작성합니다.

[참고] 트리거에 대해서는 본 사이트의 내용참고
행 트리거 : http://infomercial.tistory.com/173

문장 트리거 : http://infomercial.tistory.com/179

3. 선언적 무결성 제약 조건

업무 규칙을 시행할 때 무결성 제약 조건 방식을 선호하는 이유는 다음과 같습니다.

a. 향상된 성능을 제공합니다.
b. 방대한 코딩 작업이 필요하지 않으므로 선언 및 수정이 용이합니다.

c. 규칙을 집중시킵니다.

d. 유연성(활성화 또는 비활성화 가능)이 있습니다.

e. 데이터 딕셔너리에 완전히 문서화됩니다.

나중에 기회가 되면 1번에서 언급한 응용프로그램 코드에서 데이터무결성에 대한 실제 예를 알아보기로 하고 자 이제부터 선언적 무결성 제약조건에 대해서 알아보자.

제약조건의 유형

트리거(Trigger)

트리거는 프로시저, 함수와 같이 그 블록구조가 유사하지만 프로시저, 함수는 외부적인 실행명령으로 실행되는 반면에 트리거의 실행은 DML문이 발생할 때 시행된다. 즉 INSERT, UPDATE, DELETE문이 실행될 때 정의된 트리거도 실행된다.

트리거의 용도

1. 데이터 베이스 테이블 생성시에 참조 무결성과 데이터 무결성 그 밖의 조약조건으로
 정의할 수 없는 복잡한 요구사항에 대한 조건을 생성

2. 테이블 데이터 작업에 대한 감시, 보안 용도

3. 테이블에 생기는 변화에 따라 필요한 다른 프로그램을 실행시킬 수 있음

트리거 형식

CREATE [OR REPLACE] TRIGGER 트리거 이름 BEFORE|AFTER

트리거 이벤트  ON 테이블이름
[FOR EACH Row]

[WHEN (condition)]

PL/SQL 블록

트리거의 종류

문장트리거(Statement-Level Trigger)와 행트리거(Row-Level Trigger)가 있으며 FOR EACH ROW 옵션을 사용하면 행트리거, 사용하지 않으면 문장트리거가 된다.

문장트리거는 단 한번 실행되며 컬럼의 데이터 값에 상관없이 그 칼럼의 변화가 일어남을 감지하여 실행되는 트리거 반면에 행트리거는 컬럼의 각각의 데이터행에 변화가 생길때마다 실행되며 그 데이터 행의 실제값을 제어할 수 있다.

예제) 업데이트문을 이용한 BEFORE UPDATE 행트리거를 보자.

실행 결과

date_format 함수 만들기

날짜 데이터를 입력받아 TO_CHAR 함수를 이용하여 입력받은 날짜 데이터를

문자열 데이터로 변환하는 함수

이렇게 하여 함수를 생성한 후
SQL>SELECT empno, date_format(HIREDATE) FROM EMP; 하면

[참고] 생성된 함수의 입출력인자 보기 및 스크립보기

이상으로 간단한 함수를 하나 만들어 DATE형 데이터를 문자형으로 변환하는 함수를 만들어 보았다.
응용해서 다른 것도 만들어 보시길..

P/L SQL PACKAGE

오라클 데이터베이스에 저장되어 있는 프로시저, 함수들의 집합체이며 패키지의 구성은

선언부(Specification)과 본문(Body)로 구성됨.

1. 형식

--Specification 부분

CREATE [OR REPLACE] PACKAGE package_name

[AUTHID {CURRENT_USER | DEFINER}]

{IS | AS }

[PRAGMA SERIALLY_REUSABLE;]

[collection_type_definition …]

[record_type-definition …]

[subtype_definition …]

[collection_declaration …]

[constant_declaration …]

[exception_declaration …]

[object_declaration …]

[record_declaration …]

[variable_declaration …]

[cursor_spec …]

[function_spec …]

[procedure_spec …]

[call_spec …]

[PRAGMA RESTRICT_REFERENCES (assertions) …]

END [package_name];

--Body 부분

[CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}

[PRAGMA SERIALLY_REUSABLE;]

[collection_type_definition …]

[record_type-definition …]

[subtype_definition …]

[collection_declaration …]

[constant_declaration …]

[exception_declaration …]

[object_declaration …]

[record_declaration …]

[variable_declaration …]

[cursor_body …]

[function_spec …]

[procedure_spec …]

[call_spec …]

[BEGIN

  squence_of_statements]

END [package_name]; ]

여기서 패키지 선언부(Specification)은 패키지에 포함될 PL/SQL 프로시저 및 함수 외에 퍼블릭(Public) 변수 선언과 커서(Cursor) 및 예외절 등을 포함한다.

여기서 퍼블릭 변수는 패키지 전체에 적용되는 변수임을 기억하자.

다음으로 패키지 본문(Body)는 패키지 선언부에서 선언된 변수들, 프로시저, 함수 들을 정의하는 부분이다.

실제 EMP 테이블의 클론인 EMP_BACK 테이블을 가지고 알아보자.

2. 패키지 선언과 본문 스크립

--패키지 선언(Specification)

CREATE OR REPLACE PACKAGE emp_back_pack

IS

  v_counter NUMBER :=0;

  PROCEDURE empno_out;

  PROCEDURE ename_out;

  PROCEDURE job_out;

  PROCEDURE hiredate_out; 

  PROCEDURE deptno_out;

  PROCEDURE salary_out;

END emp_back_pack;

/

--패키지 본문(BODY)

CREATE OR REPLACE PACKAGE BODY emp_back_pack

IS

  -- emp_back_cursor 생성

  CURSOR emp_back_cursor IS

    SELECT * FROM EMP_BACK;

  -- emp_back_procedure

  PROCEDURE empno_out IS

  BEGIN

               DBMS_OUTPUT.ENABLE;

                DBMS_OUTPUT.PUT_LINE('직원번호');

                DBMS_OUTPUT.PUT_LINE('--------------');

                FOR crec IN emp_back_cursor LOOP

                            DBMS_OUTPUT.PUT_LINE('       ' || TO_CHAR(crec.empno));

                END LOOP;

  END empno_out;

  -- emp_back_ename_out 

  PROCEDURE ename_out IS 

  BEGIN

               DBMS_OUTPUT.ENABLE;

                DBMS_OUTPUT.PUT_LINE('직원이름');

                DBMS_OUTPUT.PUT_LINE('--------------');         

                FOR crec IN emp_back_cursor LOOP

                            DBMS_OUTPUT.PUT_LINE('       ' || crec.ename);

                END LOOP;

  END ename_out;  

  -- emp_back_job_out 

  PROCEDURE job_out IS 

  BEGIN

               DBMS_OUTPUT.ENABLE;

                DBMS_OUTPUT.PUT_LINE('업무 직함');

                DBMS_OUTPUT.PUT_LINE('--------------');         

                FOR crec IN emp_back_cursor LOOP

                            DBMS_OUTPUT.PUT_LINE('       ' || crec.job);

                END LOOP;

  END job_out;

  -- emp_back_hiredate_out 

  PROCEDURE hiredate_out IS 

  BEGIN

               DBMS_OUTPUT.ENABLE;

                DBMS_OUTPUT.PUT_LINE('입사일');

                DBMS_OUTPUT.PUT_LINE('--------------');         

                FOR crec IN emp_back_cursor LOOP

                            DBMS_OUTPUT.PUT_LINE('       ' || crec.hiredate);

                END LOOP;

  END hiredate_out;

  -- emp_back_deptno_out 

  PROCEDURE deptno_out IS 

  BEGIN

               DBMS_OUTPUT.ENABLE;

                DBMS_OUTPUT.PUT_LINE('부서번호');

                DBMS_OUTPUT.PUT_LINE('--------------');         

                FOR crec IN emp_back_cursor LOOP

                            DBMS_OUTPUT.PUT_LINE('       ' || TO_CHAR(crec.deptno));

                END LOOP;

  END deptno_out;  

  -- emp_back_salary_out 

  PROCEDURE salary_out IS 

  BEGIN

               DBMS_OUTPUT.ENABLE;

                DBMS_OUTPUT.PUT_LINE('월급');

                DBMS_OUTPUT.PUT_LINE('--------------');         

                FOR crec IN emp_back_cursor LOOP

                            DBMS_OUTPUT.PUT_LINE('       ' || TO_CHAR(crec.sal));

                END LOOP;

  END salary_out;  

END emp_back_pack;

/

3. 실행 결과

패키지 선언부 생성

패키지 본문 생성

실행결과

SQL>SET SERVEROUTPUT ON 을 실행한 후 위와 같이 EXECUTE 문을 써서 실행하면
원하는 결과를 얻을 수 있음.
추가적인 기능들이나 응용을 배우는 것은 독자들에게 남겨둡니다.
그럼 이만.