09. 데이터 조작 프로시저
데이터 조작 프로시저 작성
프로시저
개념
일련의 쿼리를 하나의 함수처럼 실행하기 위한 쿼리의 집합
DB에 대한 일련의 작업을 정리한 절차를 RDBMS에 저장한 모듈
DB 내부에 저장되고, 해당 프로시저를 호출하여 실행시키거나 특정 이벤트가 발생했을 때, 혹은 특정 시간에 실행
역할
데이터 무결성의 유지
복잡한 비즈니스 규칙과 제약의 강화
유지 보수의 용이
저장형 객체
Stored Function
값을 계산하고 결과값을 반환하기 위해서 많이 사용
대부분 구성이 프로시저와 유사하지만 IN 파라미터만 사용할 수 있음
반드시 반환될 값을 데이터 타입을 RETURN문에 선언
CREATE OR REPLACE FUNCTION function_name 형태로 생성
Stored Procedure
특정 작업을 수행할 수 있는, 이름이 있는 PL/SQL 블록
저장된 SQL 명령들은 최적화되어 있기 때문에 빠르게 동작
액세스 권한을 별로도 지정할 수 있기 때문에 높은 보안성 제공
매개 변수를 받을 수 있고 반복적으로 사용할 수 있는 Object
보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL 블록을 DB에 저장하기 위해 생성
- 정의 단계에서 필요에 따라 입력 매개변수, 출력 매개변수 및 지역변수 정의 가능
- IN, OUT, INPUT으로 입력 받을 매개변수인지 전달할 매개변수인지 선언
- CREATE OR REPLACE PROCEDURE procedure_name 형태로 생성
Stored Package
패키지는 DB에 저장되어 있는 서로 관련있는 PL/SQL 프로시저와 함수들의 집합
패키지는 선언부와 본문 두 부분으로 나누어짐
Trigger
INSERT, UPDATE, DELETE문이 TABLE에 대해 행해질 때 묵시적으로 수행되는 PROCEDURE
Trigger는 TABLE과는 별도로 DATABASE에 저장
Trigger는 VIEW에 대해서가 아니라 TABLE에 관해서만 정의될 수 있음
조건이 만족될 때(이벤트의 발생) 자동으로 지정된 작업을 수행하게 만드는 일종의 프로시저
테이블 정의 시 표현할 수 없는 기업의 비즈니스 규칙이나 복잡한 보안 요건들을 시행하는 역할 수행(데이터의 무결성 유지)
트리거의 종류
행 트리거
- FOR EACH ROW 옵션을 사용
- 각각의 행의 데이터 변화가 생길 때마다 실행
문장 트리거 | Statement Trigger
- 트리거 사건에 의해 단 한번 실행되며, 컬럼의 각 데이터 행을 제어할 수 없음
ORM | Object-Relational Mapping Framework
개념
객체와 관계형 DB의 데이터를 자동으로 매핑(연결)해주는 것
객체지향 프로그램에서 클래스를 생성하고, 관계형 DB의 테이블의 내용을 매핑
객체지향 프로그램을 통해 DB의 데이터를 다룸
장/단점
장점
비즈니스 로직에 더 집중할 수 있음
재사용 및 유지보수의 편리성이 증가
DBMS에 대한 종속성이 줄어듬
단점
완벽한 ORM으로만 서비스를 구현하기 어려움
프로시저가 많은 시스템에선 ORM의 객체지향 장점을 활용하기 어려움
매핑 기술 비교
SQL Mapper
SQL을 명시하여 단순히 필드를 매핑시키는 것이 목적
SQL 문장으로 직접 DB DATA를 다룸
iBatis, Mybatis, jdbc Templetes 등
OR Mapping (=ORM)
객체를 통해 간접적으로 DB를 다룸
객체와 관계형 DB의 데이터를 자동으로 매핑
ORM을 이용하면 SQL Query 가 아닌 직관적인 코드로 데이터를 조작할 수 있음
JPA(Java Persistent API), Hibernate
프로그램 디버깅
개념
개발 단계 중에 발생하는 시스템의 논리적인 오류나 비정상적 연산을 찾아내고, 그 원인을 밝혀 수정하는 작업과정
프로그램 개발 단계의 마지막에 주로 이루어짐
주로 디버깅은 작업에 의미를 둔 용어이고, 오류를 수정하는 소프트웨어는 디버거라는 용어 사용
프로시저는 DB에서 사용하는 절차적인 프로그램이기 때문에, 디버깅을 통하여 오류를 발견하여 수정
ORACLE 에서 사용하는 디버깅 도구
프로시저의 디버깅을 위해 SQL*Plus 도구를 이용
SQL*Plus는 오라클에서 제공하는 SQL명령문을 저장하거나 편집할 수 있는 기능을 제공
SQL 문을 실행시키고 그 결과를 볼 수 있는 도구
단위 테스트 도구
개념
프로그래밍에서 특정 기능의 소스코드가 의도한 대로 정확히 동작하는지 검증하는 절차
테스트가 가능한 최소 단위로 나눠서 테스트를 수행하는 테스트
ORACLE 에서는 데이터 조작 프로시저에 대한 테스트 환경으로 SQL*Plus라는 도구를 제공
PL/SQL 테스트
DBMS_OUTPUT 패키지 기능
DISABLE- 메시지 버퍼 내용 삭제
ENABLE- 메시지 버퍼 내용 할당
PUT- 메시지 버퍼에 내용을 저장
- 메시지 마지막 라인에 라인문자(EOL)가 추가되지 않음
PUT_LINE- PUT과 달리 메시지 끝에 새로운 라인문자가 추가됨
GET_LINE- 한 번 호출될 때마다 하나의 라인만을 읽어옴
GET_LINES- 지정된 라인을 읽어옴
데이터 조작 프로시저 최적화
쿼리(Query) 성능 측정
개념
쿼리문의 응답시간, 해당 쿼리가 실행될 때 사용된 CPU, 메모리 자원 사용 등을 측정
쿼리문의 성능에 문제가 있으면 전체적인 시스템 성능이 저하되기 때문에, 쿼리의 성능을 측정하여 최적의 실행 쿼리를 만듬
SQL 처리 흐름
구문분석 단계 | Parsing
사용자가 요청한 SQL문이 DB에서 사용된 적이 있는지 공유 풀 영역을 검색하여 확인
이미 사용했다면 구문분석 작업을 하지 않고, 처음 사용되었다면 구문분석 작업을 수행
SQL문이 문법에 따라 정상적으로 작성되었는지 분석하고, SQL 내에 포함된 테이블, 뷰 등이 DB에 존재하는지 확인
실행단계 | Execution
SQL 문에서 사용된 데이터가 버퍼캐시 영역에 존재하는지 검색
데이터버퍼 캐시영역에 존재한다면, 테이블의 해당 데이터 파일로부터 테이블을 읽지 않고 캐시 영역의 데이터를 그대로 추출
존재하지 않는다면 정의된 테이블의 해당 데이터 파일로부터 테이블을 읽어서 데이터버퍼 캐시영역에 저장
인출(추출) 단계 | Fetch
실행단계가 끝나면 서버 프로세스는 데이터버퍼 캐시영역에서 관련 테이블 데이터를 읽어서 사용자가 요청한 클라이언트로 전송
SELECT문을 실행하는 경우에만 인출단계가 실행되고, UPDATE, INSERT, DELETE문 실행시에는 인출단계는 실행되지 않음
SQL 작성 시 고려사항
개발자는 SQL 특성을 충분히 이해하여 SQL문을 적절히 구사할 수 있는 능력을 기본적으로 갖추어야 함
사용자가 SQL 작성 시, 옵티마이저가 실행계획을 수립한 후 실행되는 일련의 과정을 이해하고 작성
구문분석 단계 시, 옵티마이저가 수립한 실행계획에 따라 엄청난 수행속도 차이가 발생
특정 SQL이 실행될 때 옵티마이저에 의해 수립된 실행 계획은 제어하기가 어렵지만 옵티마이저가 비정상적으로 동작한다면, 이를 추적하여 개발자가 원하는 실행계획으로 동작될 수 있도록 조정하는 과정이 필요
개발자는 옵티마이저가 정상적인 실행계획을 수립할 수 있도록 종합적이고 전락적인 포인트를 SQL에 부여하여 작성
좋은 SQL은 추출되는 결과를 추론하여 SQL을 집약적으로 접근하여 작성
SQL 성능 최적화를 위한 유틸리티
SQL Trace
TKPROF Trace Kernel PROFile - EXPLAIN PLAN
SQL 성능 개선 절차
문제 있는 SQL 식별
APM(Application Performance Management)등을 활용하여 문제있는 SQL 식별
문제가 있는 SQL을 수행할 경우, 자원을 많이 사용하게 되고, 시간이 오래 걸림
옵티마이저 통계 확인
- 옵티마이저에 지원하는 통계정보를 확인
SQL문 재구성
여러가지 조건을 활용하여 인출되는 범위를 줄임
힌트를 이용하여 옵티마이저의 경로 및 조인 순서를 제어
인덱스 재구성
성능에 중요한 엑세스 경로를 고려하여 인덱스를 재구성
불필요한 인덱스들은 제거
실행계획 유지 관리
- 실행계획이 유지되고 있는지 모니터링하고 관리
소스코드 인스펙션
개념
프로그램 소스를 실행하지 않고, 코드 상에 존재하는 잠재적인 오류/표준 등을 찾아내고 이를 개선하는 일련의 과정
실제 프로그램을 실행하기 전에 결함을 조기에 찾을 수 있음
동적 테스트로 발견할 수 없는 종류의 결함들을 발견할 수 있음
관련 전문가로 구성되며 검사목록(checklist)제공, 품질 보증 활동으로 인식
목적
소스코드 이해도 향상
유지보수성 향상
코드 품질 향상
코드 보안성 향상
외주 관리 지원
대상
소스코드 보안 취약점
코딩 표준 위반
사용되지 않은 변수, 코드
일관되지 않은 인터페이스
과거의 데이터 타입 사용
절차
계획 : 인스펙션 대상과 검토 항목을 정하고, 일정을 수립
개관(사전교육) : 주최자가 모든 인스펙션 참가자에게 계획 단계에서 정리한 내용을 공유
준비 : 개별적으로 작업 산출물과 관련 자료들을 검토
검사(인스펙션 회의) : 공식적인 SQL 인스펙션 수행, 문제점 토의
재작업(수정) : 검사 후 발견된 모든 오류를 수정
추적(후속조치) : 수정한 결과를 체크하고, 산출물 관리