[정처기/실기] 7-2, 3 집계함수, 그룹함수, 윈도 함수, 절차형 SQL/SQL 최적화

오늘 할 분량은 이정도? 사실 월, 화는 탭에 정리를 해둬서 주후에 파일로 블로그에 올려야할지 고민중이다.

 

키보드가 난리라서 이응과 디귿이 연타로 입력된다는 불편함을 빼면 오늘도 할만하다... 식단 구매하느라 거금이 나가서 키보드는 또 다음 월급을 기약해야겠다. (역시 쿠팡을 뛰어야할까)

 

---

절차형 SQL  활용하기

1) 절차형 SQL 개념

- SLQ도 절차 지향적인 프로그래밍 가능하도록 하는 트랜잭션 언어

 

2) 절차형 SQL 종류

1. 프로시저(Procedure) : 일련의 쿼리를 하나의 함수처럼 실행하기위한 쿼리의 집합

2. 사용자 정의 함수(User-Defined Function) : SQL 수행, 수행 결과를 다닐 값으로 반환

3. 트리거(Triggeer) : DB에서 이벤트가 발생 -> 관련 작업이 자동으로 수행

 

3) 출력부

DBMS_OUTPUT 패키지

- 메시지 버퍼에 저장 -> 버퍼로부터 메시지 읽어오는 인터페이스 페키지

- 절차형 SQL이 정상적인 구현이 되었는지 테스트

 

DBMS OUTPUT 패키지 종류

1. DBMS_OUTPUT.PUT(문자열); : 개행 X 문자열 출력

2. DBMS_OUTPUT.LINE(문자열); : 문자열 출력 후 개행

 

4) 제어부

 (1) 조건문(IF, 간단한 CASE 문, 검색된 CASEE 문)

  - IF

IF 조건 THEN
 문장;
ELSIF 조건 THEN
 문장;
...
ELSE
 문장;
ENDD IF;

  - 간단한 케이스 문

CASE 변수
 WHEN 값1 THEN
  SET 명령어;
 WHEN 값2 THEN
  SET 명령어;
...
 ELSE
  SET 명령어;
END CASE;

  - 검색된 케이스문

CASE
 WHEN 값1 THEN
  SET 명령어;
 WHEN 값2 THEN
  SET 명령어;
...
 ELSE
  SET 명령어;
END CASE;

 

 (2) 반복문

  - LOOP : 특정 조건이 만족될 때까지 반복해서 문장 실행

LOOP
 문장;
 EXIT WHEN 탈출조건;
END LOOP

  - WHILE : 참인동안 반복

WHILE 반복 조건 LOOP
 문장;
EXIT WHEN 탈출 조건;
END LOOP;

  - FOR LOOP : 시작과 끝을 지정하여 범위 내라면 반복

FOR 인덱스 IN 시작값 .. 종료값
LOOP
 문장;
END LOOP;

 

5) 예외부

EXPECTION
 WHEN 조건 THEN
  SET 명령어;

 

프로시저

1) 개념

- 일련의 쿼리를 하나의 함수처럼 실행하기 위한 쿼리의 집합

 

2) 구성

DECLARE

BEGIN

 시작, 종료 : 실행 및 서브루틴 생성

 제어 : 단위 블록별 실행흐름 제어

 SQL : 데이터 관리를 위한 조회, 추가, 수정, 삭제

 예외 : 예외 수행

 실행 : DBMS에 반영, 복구

END

구성요소	설명
선언부(DECLARE)	프로시저 명칭, 변수, 인수, 데이터 타입 정의
시작/종료부(BEGIN/END)	프로시저 시작, 종료(한 쌍)
제어부(CONTROL)	순차적 처리, 조건문/반복문 이용
SQL	주로 DML 사용, 간혹 DDL TRUNCATE 사용ㅇ
예외부(EXCEPTION)	BEGIN~END에서 SQL문 실행 -> 옝외 발생시 예외 처리 방법
실행부(TRANSACTION)	DML 수행 내역의 DBMS의 적용, 취소 여부 결정하는 처리부

프로시저 구성(영어 암기 필수)
선언부, 시장/종료부, 제어부, SQL, 예외부, 실행부

수제비에서는.. DB 컨닝 세트..라고 외우라고..
DE / BE / CON / S / E / T
DECLARE, BEGIN/END, CONTROL, SQL, EXCEPTION, TRANSACTION

출처 : https://shlee1990.tistory.com/836

3) 프로시저 문법

CREATE [OR REPLACE] PROCEDURE 프로시저-명
(파라미터-명, [IN | OUT | INOUT] 데이터-타입, ...)
IS
 변수 선언
BEGIN
 명령어;
[COMMIT | ROLLBACK]
END;

구성	설명
OR REPLACE	프로시저로 값 전달
IN	운영체제 -> 프로시저로 값 전달
OUT	프로시저 -> 운영체제로 값 전달
INOUT	IN, OUT 둘 다 해
BEGIN	프로시저 시작 키워드
COMMIT	트랜잭션 성공저그로 끝났을 때 사용하는 연산
ROLLBACK	드랜잭션의 비정상적인 종료 -> 전체 OR 일부 연산 취소
END	프로시저의 끝

 

4) 프로시저 호출문 작성

프로시저 실행 : EXECUTE, EXEC

SQL> EXECUTE 프로시저-명 (파라미터1, 파라미터2,,,);

 

사용자 정의 함수

1) 개념

일련의 SQL 처리. 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL

 

2) 구성

구성요소	설명
선언부(DECLARE)	사용자 정의 함수의 명칭, 변수, 인수, 데이터 타입 정의
시작/종료부(BEGIN/END)	시작, 종료(한 쌍)
제어부(CONTROL)	순차적 처리, 조건문/반복문 이용
SQL	조회 용도로 SELECT. 조작어는 사용 X
예외부(EXCEPTION)	BEGIN~END에서 SQL문 실행 -> 옝외 발생시 예외 처리 방법
반환부(RETURN)	호출문에 대한 함숫값 반환

3) 문법

CREATE [OR REPLACE] FUNCTION 함수명
(파라미터-명 IN 데이터 타입, 데이터 타입,,,)
RETRUN 데이터-타입
IS
 변수 선언
BEGIN
 명령어;
 RETURN 변수;
END;

 

트리거

1) 개념

이벤트 발생 -> 관련 작업이 자동으로 수행되는 절차형 SQL

 

2) 목적

특정 테이블의 데이터 변경을 시작점. 그와 관련된 작업을 자동으로 수행 

 

3) 종류

행 트리거 : 데이터가 변할 때 마다 실행

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

 

4) 구성

구성요소	설명
선언부(DECLARE)	트리거 명칭 정의
시작/종료부(BEGIN/END)	시작, 종료(한 쌍)
제어부(CONTROL)	순차적 처리, 조건문/반복문 이용
SQL	주로 DML, 가끔 DDL
예외부(EXCEPTION)	BEGIN~END에서 SQL문 실행 -> 옝외 발생시 예외 처리 방법
이벤트부(EVENT)	트리거가 실행되는 타이밍

5) 문법

CREATE [OR REPLACE] TRIGGER 트리거명
[BEFORE | AFTER] 유형 ON 테이블명
[FOR EACH ROW]
BEGIN
END;

BEFORE : INSERT, UPDATE, DELETE 수행하기 전 실행

AFTER : INSERT, UPDATE, DELETE 성공적으로 수행 후 실행

유형 : DML에 해당하는 INSERT, UPDATE, DELETE 중 트리거를 수행 할 명령어 유형 선택(중복 가능. 예) INSERT OR DELETE)

FOR EACH ROW : 매번 변겨외는 데이터 행의 수만큼 실행

BEGIN : 트리거 시작

END : 트리거 끝

 

6) 주의

1. TCL 안됨 : COMMIT, ROLLBACK 은 ㄴㄴ

2. 오류에 주의