1. 절차형 SQL 작성
2. 트리거
1. 트리거(Trigger)의 개념
- 데이터베이스가 미리 정해 놓은 조건이 충족되거나, 특정 테이블에 삽입, 수정, 삭제 등의 데이터 변경 이벤트가 발생하면 DBMS에서 자동적으로 실행하도록 구현된 프로그램
cf : 이벤트(Event) : 특정 시간에 특정한 쿼리, 함수등을 실행시키는 기능이다.
cf : DBMS(Database Management System): 사용자와 데이터베이스 사이에서 사용자의 요규에 따라 정보를 생성해주고, 데이터베이스를 관리해주는 소프트웨어이다.
2. 트리거의 목적
- 일반적으로 이벤트와 관련된 테이블의 데이터 삽입, 추가, 삭제 작업을 DBMS가 자동적으로 실행시키는 데 활용한다.
- 데이터 무결성 유지 및 로그 메시지 출력 등의 별도 처리를 위해 사용되기도 한다.
cf : 데이터 무결성 : 데이터베이스에 저장된 데이터 값과 그것이 표현하는 실제 값이 일치하는 정확성을 의미한다.
cf : 로그 메시지 : 시스템의 모든 기록을 담고 있는 데이터 이다.
3. 트리거의 구성
- 반환이 없다는 점, 데이터 조작어(DML)을 주된 목적으로 한다는 점에서는 프로시저와 유사하나, EVENT명령어를 통해 트리거 실행을 위한 이벤트를 인지한다는 점과 외부 변수 IN, OUT이 없다는 점이 프로시저나 사용자 정의함수와 다르다.
cf : 프로시저(Procedure) : BD 내부에 저장된 일련의 SQL 명령문들을 하나의 함수처럼 실행하는 쿼리의 집합이다.
cf : 사용자 정의함수(User-Defined Function) : 사용자가 직접 함수를 정의하여 사용할 수 있는 기능이다.
1. DECLAE - 트리거의 명칭, 변수와, 인수 그리고 그에 대한 데이터 타입을 정의하는 선언부
2. BEGIN/END
- 트리거의 시작과 종료를 표현하는 데 필수적이며, BEGIN/END를 쌍을 이루어 추가하므로 Block으로 구성
- 다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스를 구성
3. CONTROL
- 기본적으로는 순차적으로 처리
- 비교 조건에 따라 Block 또는 문장을 실행
- 조건에 따라 반복 실행(Loop)
4. SQL
- DML(SELECT,INSERT,UPDATE,DELTE)를 주로 사용
5. EXCEPTION
- BEGIN ~ END 절에서 실행되는 SQL 문이 실행될 때 예외 발생 시 예외 처리 방법을 정의하는 처리부
6. TRANCATION
- 트리거에서 수행된 DML 수행 내역의 DBMS의 적용 또는 취소 여부를 결정하는 처리부
4. 트리거 구문
CREATE [OR REPLACE] TRIGER 1
{AFTER | BEFOR} 2 ON 3
FOR EACH ROW
BEGIN
[실행할 sql문]
EXCEPTION
END;
/1. 트리거 명 : 해당 트리거를 호출할 때 사용할 이름을 지정
2. {AFTER | BEFOR} :
- BEFORE : 조건 문장이 실행되기 전에 트리거의 내용이 실행되는 타입
- AFTER : 조건 문장이 실행된 후 트리거의 내용이 실행되는 타입
3. 트랜잭션 타입 : DML 중 어떤 상태일 때 트리거가 호출될지 선택(INSERT, UPDATE, DELETE 중 선택)
4. 테이블 명 : 어떤 테이블에서 트랜잭션이 일어날 때 트리거가 되는지 선택
5. 트리거 작성 사례
CREATE TRIGGER T_STUDENT
BEFORE INSERT ON STUDENT
BEGIN
IF(AGE<19) THE RAISE_APPLICATION-ERROR(-20502, '미성년자 삽입 불가');
END IF;
END;
/[설명]
- T_STUDENT라는 트리거 명을 선언
- 조건 문장이 실행되기 전(BEFROE)에 트리거의 내용이 실행되는 타입이고, 삽입(INSERT) 시에 발생
- STUDENT 테이블에 AGE가 19 미만인 값이 삽입 될 경우 20502번 에러가 발생
6. 트리거 작성시 주의사항
- 데이터 제어어(DCL; Data Control Language) 사용이 불가능하다
- 오류 발생하게 되면 트리거 실행의 원인을 제공한 데이터 작업에도 영향을 주는 경우가 있기 때문에 오류에 특히 주의한다.
7. 트리거 구현 및 실행 방법
1. 인식이 가능한 이벤트정의
- 데이터 트랜잭션 확인
- 해당 데이터의 트랜잭션을 인식할 수 있는 이벤트가 가능한지 확인
2. 대상 이벤트와 관련된 테이블 및 데이터 확인
- 관련된 데이터 및 SQL 명령문을 통해 추가, 수정, 삭제하고자 하는 데이터 확인
- 관련 데이터가 포함된 기존 테이블 및 구조 확인
3. 기존 테이블 및 데이터 간의 관계 분석
- 관련 테이블 간의 상호 구조, 기본 키(PK), 외래 키(FK)확인
- 결괏값 반환을 위한 관련 데이터 분석
4. 트리거의 기능 설계
- 트리거의 데이터 흐름도를 작성
- 트리거의 변수 분석
- 예외 처리 설계
5. 트리거 작성
- 트리거의 변수를 선언
- 트리거의 기능 설계를 바탕으로 작성
- 예외 처리 구현
6. 트리거 컴파일
- 트리거를 컴파일 하여 DBMS에 반영
- 에러가 발생하는 경우, 메시지를 참조하여 원인 해결 후 재컴파일
7. 이벤트 발생
- 테이블 내에 데이터 삽입, 수정, 삭제 등 사전에 정의한 이벤트 발생
8. 트리거가 자동 실행
- 이벤트가 정상 완료되는 경우, 별도의 메시지 없음
- 이벤트가 비정상 완료되는 경우, 메시지가 발생하므로 이를 참조
2. 이벤트
1. 이벤트(Event)의 개념
- 이벤트는 특정 시간에 특정한 쿼리, 프로시저, 함수 등을 실행시키는 기능이다.
3. 사용자 정의함수
1. 사용자 정의함수 개념
- 절차형 SQL을 활용하여 일련의 연산 처리 결과를 단일 값으로 반환할 수 있는 함수이다.
cf : 절차형 SQL : 일반적인 개발 언어처럼 절차 지향적인 프로그램이 가능한 SQL로, 절차형 SQL을 이용하면 SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다.
2. 사용자 정의함수 특징
- 사용자 정의함수의 호출을 통해 실행되며, 반환되는 단일 값을 조회 또는, 삽입, 수정 작업에 이용하는 것이 일반적이다.
- 프로시저와의 가장 큰 차이점은 기본적인 개념 및 사용법, 문법 등은 프로시저와 동일하며, 종료 시 단일 값을 반환한다는 점이다.
3. 사용자 정의함수 구문
- 프로시저 문법과 유사하고, 결과를 리턴하는 부분이 차이가 있다.
create or replace function 1(2)
is
begin
...
return[value];
end;1. 사용자 정의함수 명 : 해당 사용자 정의함수를 호출할 때 사용할 이름을 지정
2. 사용자 정의함수 파라미터 : 함수에 필요한 값들을 전달하기 위해 파라미터 사용
3. 지역변수 선언 : 해당 사용자 정의함수에서만 사용하는 변수 생성
cf 지역변수 : 함수 내에서만 존재하고 접근이 가능한 변수
4. 사용자 정의함수 사례
- 생일을 입력받아 나이를 출력하는 사용자 정의함수 사례
create function get_age(v_birth_year in char(4))
is
V_this_year char(4);
begin
select to_char(sysdate, 'yyyy')
into v_this_year
from dual;
return v_this_year - v_birth_year + 1;
end;[설명]
- get_age라는 이름의 함수
- v_this_year라는 char(4) 변수 생성
- sysdate(현재 시간)을 'yyyy'(4자리 연도)로 받아 - V_this_year라는 변수에 삽입
- 현재 연도와 생년을 숫자 형식으로 변환한 후 두수의 차에 1을 더한 값(나이)을 반환
5. 사용자 정의 함수 호출 쿼리 작성
- 사용자 정의함수는 프로시저와 동일하게 외부에서의 호출을 통해 실행한다.
- 비교적 단순한 결괏값을 구하는 것이 보통이며, 또는 타 시스템에 정보 제공 등 은닉을 통해 캡슐화를 제공하는 데에도 많이 사용한다.
cf : 캡슐화(Encapsulation) : 객체의 속성과 행위를 하나로 묶고, 실제 구현 내용 일부를 외부에 감추어 은닉하는 기법이ㅏㄷ.
4. SQL 문법
1. SQL(Structured Query Language) 문법의 개념
- SQL 문법은 데이터베이스를 접근하고 조작하는 데 필요한 표준 언어를 활용 할 수 있게 해주는 규칙이다.
2. SQL 문법의 분류
1. 데이터 정의어(DDL)
- 테이블이나 관계의 구조를 생성하는 데 사용하며 CREATE, ALTER, DROP문 등이 있음
2. 데이터 조작어(DML)
- 테이블에 데이터를 검색, 삽입, 수정, 삭제하는 데 사용하며 SELECT, INSERT, DELETE, UPDATE문 등이 있음
- SELECT문은 특별히 질의어(Query)라고 부름
3. 데이터 제어어(DCL)
- 데이터의 사용 권한을 관리하는 데 사용하며 GRANT, REVOKE문 등이 ㅇㅆ음
3. where 조건
- whre 조건 절에서는 비교, 범위, 집합, 패턴, NULL, 복합 조건 등을 다룸
꾸준함