[정보처리기사 필기] SQL 활용(Procedure, Trigger, ORM 등)

프로시저(Procedure)

---

절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어로, 호출을 통해 실행되어 미리 저장해 놓은 SQL작업을 수행한다.

• 만들어 저장해놓으면 여러 프로그램에서 호출하여 사용가능
• 데이터베이스에 저장되어 수행되기 때문에 스토어드(Stored) 프로시저라고도 불린다.
• 시스템 일일 마감작업, 일괄(Batch) 작업 등에 사용된다.

 

1. 프로시저 구성

 

프로시저 구성도 [1]

• DECLARE : 프로시저의 명칭, 변수, 인수, 데이터 타입 정의하는 선언부
• BEGIN / END : 프로시저의 시작과 종료 의미
• CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리된다.
• SQL : DML, DCL이 삽입되어 데이터 관리
• EXEPTION : 예외처리
• TRANSACTION : 수정 데이터 작업을 DB에 적용할지 취소할지 결정

 

2. 프로시저 생성

CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
[지역변수 선언]
BEGIN
  프로시저 BODY;
END;

• OR REPLACE : 선택적 예약어. 동일 프로시저명 이미 존재하는 경우, 기존 프로시저 대체 가능
• 파라미터
  • IN : 호출 프로그램이 프로시저에게 값을 전달할 때 지정
  • OUT : 프로시저가 호출 프로그램에게 값을 반환할 때 지정
  • INOUT : 호출 프로그램이 프로시저에게 값 전달, 실행 후 호출 프로그램에 값 반환할때 
  • 매개변수명 
  • 자료형
• 프로시저 BODY : 프로시저 코드 기록부. BEGIN ~ END 사이에는 적어도 하나의 SQL문 있어야 함

 

3. 프로시저 실행

EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;

• 3가지 중 하나 사용해도 실행결과 같음

 

4. 프로시저 제거

DROP PROCEDURE 프로시저명;

 

 

트리거(Trigger)

---

데이터베이스 시스템에서 데이터의 삽입(Insert), 갱신(Update), 삭제(Delete) 등의 이벤트(Event)가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

• 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적
• 트리거 구문에는 DCL(데이터제어어) 사용 불가, DCL 포함된 함수 로드등에도 오류

1. 트리거 구성

트리거 구성도[2]

• DECLARE : 트리거 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부
• EVENT : 트리거 실행되는 조건
• BEGIN / END : 시작과 종료
• CONTROL : 조건문 또는 반복문 삽입되어 순차적으로 처리
• SQL : DML 문 삽입되어 데이터관리
• EXEPTION : 예외처리

 

2. 트리거 생성

CREATE [OR REPLACE] TRIGGER 트리거명 동작시기 동작 ON 테이블명
[REFERENCING NEW | OLD AS 테이블명]
[FOR EACH ROW [WHEN 조건식]]
BEGIN
  트리거 BODY;
END;

• OR REPLACE : 선택적 예약어. 동일한 트리거 이름 존재하는 경우 기존 트리거 대체 불가
• 동작시기 : 트리거가 실행될 때를 지정.
  • AFTER : 테이블이 변경된 후에 트리거 실행
  • BEFORE : 테이블 변경되기 전에 트리거 실행
• 동작 : 트리거가 실행되게 할 작업의 종류 지정
  • INSERT : 테이블에 새로운 튜플 삽입할 때 트리거 실행
  • DELETE : 테이블의 튜플 삭제할 때 트리거 실행
  • UPDATE : 테이블의 튜플을 수정할 대 트리거 실행
• NEW | OLD : 트리거 적용될 테이블의 별칭 지정
  • NEW : 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미
  • OLD : 수정되거나 삭제전 대상이 되는 튜플들의 집합(테이블) 의미
• FOR EACH ROW : 각 튜플마다 트리거 적용
• WHEN 조건식 : 선택적 예약어. 트리거 적용할 튜플의 조건지정
• 트리거 BODY : 본문 코드 입력부분. BEGIN 으로 시작해서 END 로 끝남. 적어도 하나이상의 SQL 문 있어야함

 

3. 트리거 제거

DROP TRIGGER 트리거명;

 

 

 

사용자 정의 함수

---

사용자 정의함수는 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하며, 종료 시 처리 결과를 단일값으로 반환하는 절차형 SQL

 

• 데이터베이스에 저장되어 SELECT, INSERT, DELETE, UPDATE 등 DML문의 호출에 의해 실행된다.
• 예약어 RETURN을 통해 반환되기 때문에 출력 파라미터 없음
• 테이블 조작은 할 수 없고 SELECT 만 사용가능
• 프로시저 호출불가
• DML문에서 반환값 활용 위한 용도

 

1. 사용자 정의 함수 구성

사용자 정의 함수[3]

• RETURN : 호출 프로그램에 의해 반환할 값이나 변수 정의

 

2. 사용자 정의 함수 생성

CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
[지역변수 선언]
BEGIN
  사용자 정의함수 BODY;
  RETURN 반환값;
END;

• 파라미터
  • IN : 호출 프로그램에 사용자 정의 함수에게 값 전달할 때 사용
  • 매개변수명 : 호출 프로그램으로 부터 전달 받은 값을 지정할 변수 이름 지정
  • 자료형 : 변수 자료형 지정
• 사용자 정의 함수 BODY
  • 사용자 정의함수 코드 기록부
  • BEGIN~END 사이에 적어도 하나의 SQL문
• RETURN 반환값 : 반환할 값이나 변수를 호출 프로그램으로 돌려준다.

 

3. 사용자 정의 함수 실행

SELECT 사용자 정의 함수명 FROM 테이블명;
INSERT INTO 테이블명(속성명) VALUES(사용자 정의 함수명);
DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;
UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

 

4. 사용자 정의 함수 제거

DROP FUNCTION Get_S_성별;

 

 

 

DBMS 접속기술

---

사용자가 데이터를 사용하기 위해 응용프로그램을 이용하여 DBMS에 접근 하는 것

 

1. 접속 기술 종류

JDBC(Java Database Connectivity)

• Java 언어로 다양한 종류의 데이터베이스에 접속하고 SQL문을 수행할 때 사용되는 표준 API
• Java SE(Standard Edition)에 포함되어 있으며, 클래스는 java.sql, javax.sql 에 포함
• 접속하려는 DBMS에 대한 드라이버 필요

ODBC(Open DataBase Connectivity)

• 데이터베이스에 접근하기 위한 표준 개방형 API로, 개발 언어에 관계없이 사용가능
• 1992년 마이크로소프트 출시
• 역시 드라이버 필요하지만, 접속하려는 DBMS 인터페이스 알지 못해도 알아서 맞게 연결해준다.

MyBatis

• JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈소스 프레임워크
• SQL 문장을 분리하여 XML파일 만들고, Mapping 통해 SQL 실행

 

2. 동적 SQL(Dynamic SQL)

개발 언어에 사용되는 SQL 코드를 문자열 변수에 넣어서 처리하는 것으로, 조건에 따라 SQL 구문을 동적으로 변경하여 처리 가능

• 값이 입력되지 않을 경우 사용하는 NVL 함수를 사용할 필요가 없다.
• 정적 SQL 에 비해 속도 느리지만 상황에 따른 유연한 개발 가능

 

 

 

ORM(Object-Relational Mapping)

---

ORM은 객체지향 프로그래밍의 객체(Object)와 관계형 데이터베이스(Relational Database)의 데이터를 연결(Mapping)하는 기술을 의미

• 객체지향 프로그래밍에서 사용할 수 있는 가상의 객체지향 데이터베이스를 만들어 프로그래밍 코드와 데이터 연결
• 재사용 및 유지보수 쉽다
• SQL 코드 직접 입력하지 않아 직관적이고 간단하게 데이터 입력할 수 있다.

ORM 프레임워크

JAVA	JPA, Hibernate, EclipseLink, DataNucleus, Ebean
C++	ODB, QxOrm
Python	Django, SQLAlchemy, Storm
IOS	DatabaseObjects, Core Data
.NET	NHibernate, DatabaseObjects, Dapper
PHP	Doctrine, Propel, RedBean

 

 

---

 

출처

자료 참고: 

시나공 정보처리기사 필기 2022를 참고하여 작성되었습니다

표지

[1] https://lipcoder.tistory.com/361

[2] https://lipcoder.tistory.com/362?category=887720 

[3] https://lipcoder.tistory.com/363?category=887720