절차형 SQL 작성

절차형 SQL

- 절차지향적인 프로그램 개발을 위한 트랜잭션 언어

- 종류

ㆍ프로시저

  : 일련의 쿼리들을 하나의 함수처럼 실행하기 위한 쿼리의 집합

 

ㆍ사용자 정의 함수

  : 일련의 SQL 처리를 수행하고 단일 값으로 반환

 

ㆍ트리거

  : CRUD 이벤트 발생시 관련 작업을 자동으로 수행

 

 

 

출력부 (DBMS_OUTPUT)

- 메시지를 버퍼에 저장하고 버퍼로 부터 메시지를 읽어오기 위한 인터페이스 패키지

 : 절차형 SQL이 정상적으로 구현 되었는지 테스트

- 종류

ㆍDBMS_OUTPUT.PUT(문자열);

  : 개행 없이 문자열 출력 

ㆍDBMS_OUTPUT.PUT_LINE(문자열);

  : 문자열 출력 후 개행

 

 

 

제어부 (CONTROL)

- if문

if 조건 then 
	문장;
elsif 조건 then
	문장;
else
	문장;
end if;

- 간단한 case문

case 변수
	when 값1 then
		set 명령어;
	when 값2 then
		set 명령어;
	else
		set 명령어;
end case;

- 검색된 case문 

case 변수
	when 조건1 then
		set 명령어;
	when 조건2 then
		set 명령어;
	else
		set 명령어;
end case;

- loop문

loop	
	문장;
    exit when 탈출조건;
end loop;

- while문

while 반복조건 loop
	문장;
exit when 탈출조건;
end loop;

- for loop문

for 인덱스 in 시작값 .. 종료값
loop
	문장;
end loop;

 

 

 

예외부 (exception)

exception
	when 조건 then
    	set 명령어;

 

 

 

프로시저 (Procdure)

- 선언부 (declare)

 : 프로시저의 명칭, 변수와 인수, 데이터 타입 정의

 

- 시작 / 종료부 (begin / end)

 : 다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스 구성

 

- 제어부 (control)

 : 조건문과 반복문을 이용한 문장처리

 

- SQL

 : DML을 주로 사용 / DDL중 TRUNCATE 사용

 

- 예외부 (exception)

 : begin~end 내의 SQL문 실행시 발생하는 예외에 대한 처리

 

- 실행부 (transaction)

 : 프로시저에서 수행된 dml 수행 내역의 DBMS 적용 또는 취소 여부 결정

 

 

 

프로시저 문법

create [or replace] procedure 프로시저명
(파라미터명 [in | out | inout] 데이터타입,..)
is
	변수선언
begin
	명령어;
[commit | rollback]
end;

- or replace : 기존 프로시저 존재시 덮어쓰기

- in : 운영체제에서 프로시저로 값 전달

- out : 프로시저의 처리결과를 운영체제로 전달

- inout : in과 out을 동시에 수행

 

 

 

사용자 정의 함수

- 프로시저 구조 + 반환부

- 반환부 : 호출문에 대한 함숫 값 반환

=> 프로시저에 return이 있는 형태

=> 모드에는 in만 존재 (파라미터명 in 데이터타입,...)

 

 

 

트리거

- 프로시저 구조 + 이벤트 / 순서 / 유형 / for each now

- 종류

ㆍ행 트리거 : 데이터 변화가 생길 때마다 실행

ㆍ문장 트리거 : 트리거에 의해 단 한 번 실행

- 주의사항

 : 트랜잭션 제어어(TCL) 사용불가

 

 

 

트리거 구조

create [or replace] trigger 트리거명
[before | after] 유형 on 테이블명
[for each now]
begin
end;

- before

 : 테이블에 대한 insert, update, delete 수행전 트리거 실행

 

- after

 : 테이블에 대한 insert, update, delete가 성공했을때 트리거 실행

 

- 유형

 : DML 명령어 중 선택  <여러개 선택 가능>

 : ex) insert or delete on employee

 

- for each now

 : 매번 변경되는 데이터 행의 수만큼 실행

 

 

 

 프로시저 성능개선

- 규칙 기반 옵티마이저 (RBO)

ㆍ개념

  : 사전 등록된 규칙에 따라 질의 실행 계획 선택

ㆍ평가기준

  : 인덱스 구조, 연산자, 조건절 형태 등

ㆍ장점

  : 사용자가 원하는 처리경로로 유도하기 쉬움 

 

- 비용 기반 옵티마이저 (CBO)

ㆍ개념

  : 통계정보로 부터 모든 접근 경로를 고려한 질의 실행 계획 선택

ㆍ평가기준

  : 레코드 및 블록 개수, 평균 행 길이, 컬럼 값 수 등

ㆍ장점

  : 옵티마이저에 대한 이해도가 낮아도 성능 보장

 

 

 

옵티마이저의 역할

- 쿼리변환

 : SQL을 표준화된 형태로 변환

- 비용산정

 : 쿼리 명령어 각 단계의 선택도, 카디널리티, 비용 계산

- 계획생성

 : 하나의 쿼리 수행시 후보군이 될만한 실행 계획 생성

 

 

힌트

- SQL 성능개선의 핵심부분으로,옵티마이저의 실행 계획을 원하는대로 변경

- 종류

/*+RULE*/
/*+CHOOSE*/
/*+INDEX(테이블명 인덱스명)*/
/*+USE_HASH(테이블명 인덱스명)*/
/*+USE_MERGE(테이블명)*/
/*+USE_NL(테이블명)*/