[정보처리기사 실기] 프로시저, 트리거 및 ORM 정리

자격증 준비하면서 내가 이해하기 편하게, 다시 보기 좋게 정리하는 정보처리기사의 내용 (자격증 상세 내용은 아래)

http://www.q-net.or.kr/crf005.do?id=crf00505&gSite=Q&gId=

http://www.q-net.or.kr/crf005.do?gId=&gSite=Q&id=crf00505

프로시저, 트리거 및 ORM 부분을 정리한 내용

---

프로시저

• SQL을 사용하여 작성한 일련의 작업을 저장해 두고 원할 때마다 저장한 작업을 수행하도록 하는 절차형 SQL
• 시스템의 일일 마감 작업, 일괄 작업 등에 주로 사용

-- 프로시저 생성
CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
[지역변수 선언]
BEGIN
	CONTROL
	SQL
	EXCEPTION
	TRANSACTION
END;

-- BEGIN에서 시작하여 END로 끝나고, BEGIN과 END 사이에 적어도 하나의 SQL문이 있어야 함

-- 프로시저 실행
EXECUTE 프로시저며이;
EXEC 프로시저명;
CALL 프로시저명;

-- 프로시저 제거
DROP PROCEDURE 프로시저명;

트리거

• 이벤트가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL
• 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용
• DCL을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우 오류 발생

-- 트리거 생성
CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REFERENCING [NEW|OLD] AS 테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
	CONTROL
	SQL
	EXCEPTION
END;

-- BEGIN에서 시작하여 END로 끝나고, BEGIN과 END 사이에 적어도 하나의 SQL문이 있어야 함

-- 트리거 제거
DROP TRIGGER 트리거명;

사용자 정의 함수

• 종료 시 처리 결과로 단일값만을 반환하는 절차형 SQL
• 데이터베이스에 저장되어 SELECT, INSERT, DELETE, UPDATE 등 DML 문의 호출에 의해 실행

-- 사용자 정의 함수 생성
CREATE [ON REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
[지역변수 선언]
BEGIN
	CONTROL
	SQL
	EXCEPTION
	RETURN 반환값
END

-- BEGIN에서 시작하여 END로 끝나고, BEGIN과 END 사이에 적어도 하나의 SQL문이 있어야 함

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

-- 사용자 정의 함수 제거
DROP FUNCTION 사용자 정의 함수

제어문

• 절차형 SQL의 진행 순서를 변경하기 위해 사용하는 명령문
• IF, LOOP 등

IF문

• 조건에 따라 실행할 문장을 달리하는 제어문

-- 조건이 참일 때만 실행할 경우
IF 조건 THEN
	실행할 문장
	실행할 문장
	...
END IF

-- 조건이 참일 때와 거짓일 떄 분리
IF 조건 THEN
	실행할 문장;
ELSE
	실행할 문장;
END IF;

LOOP문

• 조건에 따라 실행할 문장을 반복 수행하는 제어문

LOOP
	실행할 문장;
	EXIT WHEN 조건;
End LOOP;

커서

• 쿼리문의 처리 결과가 저장되어 메모리 공간을 가리키는 포인터
• 열기, 패치, 닫기의 3단계로 진행

묵시적 커서

• 내부에서 자동으로 생성되어 사용되는 커서
• SQL%FOUND : 쿼리 수행의 결과로 패치된 튜플 수가 1개 이상이면 TRUE
• SQL%NOTFOUND 쿼리 수행의 결과로 패치된 튜플 수가 0개면 TRUE
• SQL%ROWCOUNT 쿼리 수행의 결과로 패치된 튜플 수를 반환
• SQL%ISOPEN 커서가 열린 상태이면 TRUE지만 묵시적 커서는 자동으로 생성된 후 자동으로 닫히기 때문에 항상 FALSE

명시적 커서

• 사용자가 직접 정의해서 사용하는 커서

--선언 형식
CURSOR 커서명(매개변수1, 매개변수2, ...)
IS
SELECT문;

-- 열기 형식
OPEN 커서명(매개변수1, 매개변수2, ...)

-- 패치 형식
FETCH 커서명 INTO 변수1, 변수2, ...

-- 닫기 형식
CLOSE 커서명;

DBMS 접속

• 응용 시스템을 이용하여 DBMS에 접근하는 것
• 웹 응용 프로그램은 웹 응용 시스템을 통해 DBMS에 접근
• 웹 응용 시스템은 웹 서버와 웹 애플리케이션 서버(WAS)로 구성

DBMS 접속 기술

• DBMS에 접근하기 위해 사용하는 API 또는 API의 사용을 편리하게 도와주는 프레임워크
• JDBC : JAVA 언어로 다양한 종류의 데이터베이스에 접속할 때 사용하는 표준 API
• ODBC : 개발 언어에 관계없이 데이터베이스에 접근하기 위한 표준 개방형 API
• MyBatis : JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반의 오픈 소스 접속 프레임워크

동적 SQL

• SQL 구문을 동적으로 변경하여 처리할 수 있는 SQL 처리 방식
• 응용 프로그램 수행 시 SQL이 변형될 수 있으므로 프리컴파일 할 때 구문 분석, 접근 권한 확인 불가

SQL 테스트

• SQL이 작성 의도에 맞게 원하는 기능을 수행하는 검증하는 과정
• 단문 SQL 테스트
  • DDL, DML, DCL이 포함되어 있는 SQL과 TCL을 테스트하는 것으로 직접 실행하여 결과물 확인
  • DDL로 작성된 개체는 DESCRIBE 명령어를 이용하여 속성, 자료형, 옵션들을 확인 가능
  • DML로 변경한 데이터는 SELECT문으로 데이터의 정상적인 변경 여부 확인 가능
  • DCL로 설정된 사용자 권한은 사용자 구너한 정보가 저장된 테이블을 조회하여 확인 가능
• 절차형 SQL 테스트
  • 디버깅을 통해 기능의 적합성 여부를 검증하고 실행을 통해 결과를 확인하는 테스트 수행
  • 데이터베이스에 변화를 줄 수 있는 SQL문은 주석으로 처리하고 출력문을 이용하여 화면에 출력하여 확인

ORM(Object-Relational Mapping)

• 객체와 관계형 데이터베이스의 데이터를 연결하는 기술
• ORM으로 생성된 객체지향 데이터베이스는 프로그래밍 코드 또는 데이터베이스와 독립적이므로 재사용 및 유지보수 용이
• 프레임 워크
  • JAVA : JPA, Hibernate, EclipseLink, DataNucleus, Ebean 둥
  • C++ : ODB, QxOrm 등
  • Python : Django, SQLAlchemy, Storm 등
  • .NET : NHibernate, DatabaseObjects, Dapper 등
  • PHP : Doctrine, Propel, RedBean 등
• 프레임워크가 자동으로 SQL을 작성하기 때문에 의도적으로 SQL이 작성됐는지 확인 필요

쿼리 성능 최적화

• 데이터 입출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것
• 옵티마이저
  • 작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아주는 모듈
  • RBO : 데이터베이스 관리자(DBA)가 사전에 정의해 둔 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저 (실행 계획 예측이 쉬움)
  • CBO : 각 DBMS마다 고유의 알고리즘에 따라 산출되는 비용으로 최적의 경로를 찾는 비용 기반 옵티마이저 (성능 통계치 정보를 활용, 예측이 어려움)

실행 계획

• DBMS의 옵티마이저가 수립한 SQL 코드의 실행 절차와 방법
• 요구사항들을 처리하기 위한 연산 순서가 적혀있으며, 연산에는 조인, 테이블 검색, 필터, 정렬 등

쿼리 서능 최적화 방법

• SQL문이 더 빠르고 효율적으로 작동하도록 SQL 코드와 인덱스를 재구성
• SQL코드 재구성
  • WHERE 절 추가
  • WHERE 절에 연산자 사용 제한
  • IN을 EXISTS로 대체
  • 힌트로 액세스 경로 및 조인 순서 변경
• 인덱스 재구성
  • 조회되는 속성과 조건을 고려하여 인덱스 구성
  • 테이블을 참조하는 다른 SQL문으로의 영향 고려
  • 불필요한 인덱스 제거

---

뒤로 이어지는 내용

https://edder773.tistory.com/204

[정보처리기사 실기] 소프트웨어 보안과 공격 종류 정리