데이터베이스 구축 4. SQL 활용

1. 프로시저

• Procedure
  - 절차향 SQL(C, Java 등 프로그래밍 언어처럼 연속적 실행, 분기, 반복 등 제어 가능)으로 특정 기능 수행하는 트랜잭션 언어
  • 호출로 미리 저장한 SQL 작업 수행
  • DB에 저장되어 수행되는 스토어드(Stored) 프로시저
  • 시스템 일일 마감, 일괄(Batch) 작업 등
• 생성
  - CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터) [지역변수 선언] BEGIN 프로시저 BODY;
  END;
• 실행
  - EXECUTE 프로시저명;
  EXEC 프로시저명;
  CALL 프로시저명;
• 제거
  - DROP PROCEDURE 프로시저명;

2. 트리거

• Trigger
  - DBMS에서 데이터 삽입(Insert), 갱신(Update), 삭제(Delete) 등 이벤트(Event) 발생 시 관련 작업 자동 수행되는 절차형 SQL
  • 트리거 구문은 DCL(데이터 제어어) 사용 못함
  • DCL 포함된 프로시저/함수 호출 시 오류 발생
• 생성
  - CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명 REFERENCE [NEW | OLD] AS 테이블명 FOR EACH ROW [WHEN 조건식] BEGIN 트리거 BODY;
  END;
• 제거
  - DROP TRIGGER 트리거명;

3. 웹 응용 시스템

• 인터넷으로 구동되는 웹 응용 프로그램이 dbms에 접근하는 시스템
  - 구조
  • 사용자가 웹 서버에 접속하여 대용량의 데이터를 요청하면 웹 서버가 직접 처리하지 않고 WAS에 전달
  • 요청을 트랜잭션 언어로 바꿔 DBMS에 전달하여 데이터 받은 후 다시 웹 서버로 전달하고 사용자에게 도달

4. JDBC

• Java DataBase Connectivity
  - DBMS에 접근하기 위한 접속 기술의 하나
  • Java 언어로 DB에 접속하고 SQL문 수행에 사용되는 표준 API(Application Programming Interface)
  • Java SE(Standard Edition)에 포함
  • JDBC 기능 정의한 클래스에 java.sql, javax.sql 있음
  • DBMS에 대한 드라이버 필요

5. ODBC

• Open DataBase Connectivity
  - 표준 개방형 API
  • 개발 언어에 관계 없음
  • 프로그램 내 ODBC 문장으로 MS-Access, DBase, DB2, Excel, Text 등의 DB에 접근
  • DBMS에 대한 드라이버 필요하나 DBMS 인터페이스 몰라도 ODBC 문장으로 SQL 작성하면 ODBC 내 드라이버 관리자가 인터페이스에 맞게 연결

6. 동적 SQL

• Dynamic SQL
  - 개발 언어 내 삽입 SQL 코드를 문자열 변수에 넣어 처리
  - 조건에 따라 SQL 구문을 동적으로 변경 처리 가능
  • 사용자로부터 SQL문 일부/전부 입력받아 실행 가능
  • NVL 함수(값이 입력되지 않을 시) 사용 불요
  • 응용 프로그램 수행 시 SQL 변경 가능성 있으므로 프리컴파일(Precompile) 시 구문 분석 혹은 접근 권한 확인 등 불가
  • 정적 SQL보다 느리나 상황에 따라 다양한 조건 첨가 등 유연한 개발 가능

7. 사용자 정의 함수

• 프로시저와 유사

비교	프로시저	사용자 정의 함수
반환값	없거나 1개 이상	1개
파라미터	입/출력	입력만
사용 명령문	DML/DCL	SELECT
사용법	실행문	DML에 포함

• 생성
  - CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터) [지역변수 선언] BEGIN 사용자 정의 함수 BODY;
  RETURN 반환값;
  END;
• 실행
  - SELECT 사용자 정의 함수명 FROM 테이블명;
  INSERT INTO 테이블명(속성명) VALUES (사용자 정의 함수명);
  DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;
  UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;
• 제거
  - DROP FUNCTION 사용자 정의 함수명;

8. ORM

• Object-Relational Mapping
  - 객체지향 프로그래밍(OOP; Object Oriented Programming)의 객체(Object)와 관계형 데이터베이스(Relational Database)의 데이터를 연결(Mapping)하는 기술
  • OOP에서 사용할 가상의 객체지향 DB 만들어 프로그래밍 코드와 데이터 연결
  • 프로그래밍 코드 혹은 db와 독립적(재사용/유지보수 용이)
  • 직관적이고 간단한 데이터 조작
    • SQL 코드 직접 입력 없음
    • 부수적 코드(선언문/할당문 생략)

9. 쿼리 성능 최적화

• 데이터 입/출력 애플리케이션의 성능 향상 위한 SQL 코드 최적화
  - APM(Application Performance Management/Monitoring)으로 최적화할 쿼리 선정
  - 옵티마이저(Optimizer)가 수립한 실행 계획(Execution Plan) 검토하고 SQL 코드 및 인덱스 재구성

10. 절차형 SQL 테스트

• 디버깅(Debugging)으로 절차형 SQL(프로시저, 사용자 정의 함수, 트리거 등)의 기능 적합성 여부 검증 후 실행으로 결과 확인하는 테스트
  - 많은 코드로 구성되어 있어 오류와 경고 메시지가 상세 출력되지 않음
  • SHOW ERRORS;
• DB에 변화줄 수 있는 SQL문은 주석(Comment) 처리 후 출력문으로 화면에서 확인
  - 디버깅 후 출력문과 주석 기호 삭제
  - 절차형 SQL 실행하여 결과 검토