제3절 데이터 조작 프로시저 작성
1. 프로시저
(1) SQL(Structured Query Language)
- SQL(구조적 질의어) : IBM에서 개발된 데이터베이스에 사용되는 언어.
- 1974년 IBM 연구소에서 발표한 SEQUEL(Structured English Query Language)에 연유.
- IBM뿐만 아니라 ORACLE, INFORMIX, SYBASE, INGRES 등과 같은 다른 회사에서도 채택하게 되었다.
- SQL의 특징
- 관계대수와 관계해석을 기초로한 고급 데이터 언어
- 이해하기 쉬운 형태.
- 대화식 질의어로 사용 가능.
- 데이터 정의, 데이터 조작, 제어 기능 제공.
- COBOL, C, PASCAL 등의 언어에 삽입.
- 레코드 집합 단위로 처리.
- 비절차적 언어.
[1] ⭐️ 데이터 정의어(DDL : Data Definition Language)
- CREATE, DROP, RENAME, ALTER, TRUNCATE 등
[2] ⭐️ 데이터 조작어(DML : Data Manipulation Language)
- INSERT, UPDATE, DELETE, SELECT 등
[3] 데이터 제어어(DCL : Data Control Language)
- ROLE, GRANT, REVOKE 등
[4] 트랜잭션 제어어(TCL : Transaction Control Language)
- COMMIT, ROLLBACK, SAVEPOINT 등
(2) PL/SQL(Procedural Language/SQL)
- 서버에서 절차적인 처리를 위해 표준 sql을 확장한 절차적 언어이다.
- 블록 구조로 여러 SQL문을 한번에 실행할 수 있으며, 모듈화 및 캡슐화, 비교, 반복, 예외처리가 가능하다.
- 서버에 저장되어 빠른 실행이 가능하지만, 문법에 대한 표준이 거의 없고 각 DBMS에 종속적인 것이 단점이다.
- PL/SQL 구조는 선언부, 실행부, 예외처리부로 되어있다. 선언부는 실행부에서 참조할 모든 변수, 상수, CURSOR 등을 선언하는 부분이고 실행부는 BEGIN과 END 사이에 기술되는 영역으로 데이터베이스 데이터를 처리할 SQL문과 PL/SQL 블록들을 기술한다. 예외 처리부에서는 실행부에서 에러가 발생했을 때 수행될 문장을 기술한다.
(3) Stored Fuction
- 특정 작업을 수행할 수 있는 이름이 있는 PL/SQL 블록으로, 구성이 프로시저와 유사하지만 IN 파라미터만 사용할 수 있다.
- 보통 값을 계산하고 결과값을 반환하기 위해서 많이 사용되고 반드시 반환될 값의 데이터 타입을 RETURN문에 선언해야 한다.
(4) Stored Procedure
- 특정 작업을 수행할 수 있는 이름이 있는 PL/SQL 블록으로 매개 변수를 받을 수 있고 반복적으로 사용할 수 있는 Object이다.
(5) Stored Package
- 오라클 데이터 베이스에 저장되어 있는 서로 관련 있는 PL/SQL 프로시저와 함수들의 집합으로 선언부와 본문 두 부분으로 구성된다.
(6) Trigger
- INSERT, UPDATE, DELETE문이 수행될 때 묵시적으로 수행되는 PROCEDURE로 TABLE과는 별도로 DATABASE에 저장된다.
2. 프로그램 디버깅
(1) SQL*Plus의 개념
- Oracle DBMS에서 제공하는 데이터 조작 프로시저에 대한 테스트 도구이다.
- SQL은 데이터를 조작하는 표준 언어이지만, SQL*Plus는 SQL을 DBMS 서버에 전송하여 처리할수 있도록 하는 Oracle에서 제공하는 도구이다.
- SQL*Plus에는 파일처리, 버퍼 편집, SQL 실행, 환경 설정, 포맷 설정, 대화 등을 위한 명령어들이 있다.
(2) SQL*Plus 명령어 유형
파일명령어 : SAVE, GET, SPOOL 등
편집명령어 : A, C, L, I, DEL, n() 등
실행명령어 : START, @, RUN, / 등
환경명령어 : SET HEAD[LINE/PAGE/PAUSE] ON[OFF] 등
대화명령어 : DEFINE, PROMPT, ACCEPT 등
3. 단위테스트 도구
(1) SQL*Plus를 활용한 데이터 조작 프로시저 테스트
- PL/SQL 테스트를 위해서 메시지를 버퍼에 저장하고 버퍼로부터 메시지를 읽어 오기 위한 인터페이스를 제공하는 DBMS_OUTPUT을 코드에 포함해야 한다.
DISABLE : 메시지 버퍼 내용 삭제
ENABLE : 메시지 버퍼 내용 할당
PUT : 메시지 버퍼에 저장되는 메시지의 마지막 라인 끝에 새로운 라인문자(EOL)가 추가되지 않음
PUT_LINE : PUT과 달리 메시지 끝에 새로운 라인문자가 추가됨
GET_LINE : 한 번 호출될 때마다 하나의 라인만을 읽어옴
GET_LINES : 지정된 라인을 읽어옴
(2) 저장 객체 테스트
[1] Stored Function : 반환값을 확인하여 테스트
[2] Stored Procedure : 변경 이전의 값을 확인 후 함수를 실행, 수정된 값을 확인하여 테스트
[3] Stored Package, Trigger : set serveroutput on을 실행 후 확인
(3) SQL 테스트하기
[1] SQLPlus 도구 설치
[2] 운영체제 프롬프트 상태에서 sqlplus uid/pwd를 입력하여 SQLPlus 모드로 전환
[3] 테스트하고자 하는 SQL 문장 입력
[4] SQL*Plus 명령어로서 출력되는 내용을 파악하여 테스트
제4절 데이터 조작 프로시저 최적화
1. 쿼리(Query) 성능 측정
(1) SQL 실행 과정
- User Process : SQL문 작성 프로그램 (SQL*Plus, SQL Developer, Toad ...)
- Syntax check(키워드 검사) : SELECT, FROM, WHERE 같이 미리 정해놓은 키워드 부분을 검사
- Semantic check : 테이블 이름, 컬럼, 이름과 같이 사용자마다 다른 부분을 검사
- 권한검사 : 사용자가 해당 오브젝트에 접근 할 수 있는지 없는지 권한여부를 확인
- soft parse : 문법검사 -> 의미검사 -> 권한검사 -> shared pool에서 Library cache 검사
- hard parse : Library cache 에 실행계획이 없으면(soft parse 실패하면) 옵티마이저 찾아가 실행계획을 만들어 달라고 요청 -> 옵티마이저는 Data Dictionary 등을 참조하여 실행계획을 생성(soft parse 과정에서 실패했을 때에만 hard parse 과정으로 넘어감)
- Row-Source Generator : 옵티마이저가 생성한 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드(또는 프로시저)형태로 포맷팅한다.
(2) 옵티마이저와 실행 계획
- ORACLE 의 경우, 옵티마이저가 실행한 실행계획 그리고 그 실행계획에서 사용된 비용에 대한 정보를 다음과 같은 3가지 방법으로 확인한다.
[1] EXPLAIN PLAN - 실행계획만을 확인할 수 있음
[2] SET AUTOTRACE - 실행계획과 I/O 관련정보를 확인할 수 있음
[3] TKPROF - 실행계획과 I/O 관련정보 뿐만 아니라 CPU 및 실제소요시간과 관련된정보도 상세히 확인 가능
2. 소스코드 인스펙션
(1) 소스코드 인스펙션 진행 순서
Planning(계획) > Overview(사전교육) > Preparation(사전검토) > Meeting(인스펙션 회의) > Re-Work, re-Inspection(수정) > Following(후속조치)
(2) 소스코드 내용 확인
- 앞서 작성된 저장 프로시저와 서브프로그램의 소스 코드 내용을 찾으려면 USER_SOURCE 데이터사전을 이용해야 한다.
