정처기 데이터베이스 구축 - 3.4 SQL 활용, 3.5 데이터 전환

<프로시저(Procedure)> ★★★★


1. 프로시저(Procedure)의 개요 (= 스토어드(Stored) 프로시저)
- 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어
- 호출을 통해 실행되어 미리 저장해 놓은 SQL 작업을 수행함
- 프로시저를 만들어 데이터베이스에 저장하면 여러 프로그램에서 호출하여 사용할 수 있음
- 데이터베이스에 저장되어 수행되기 때문에 스토어드(Stored) 프로시저라고도 불림
- 시스템의 일일 마감 작업, 일괄(Batch) 작업 등에 주로 사용됨
- 프로시저의 구상도  

출처 : https://shlee1990.tistory.com/836

     - DECLARE
          : 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부
     - BDGIN / END
          : 프로시저의 시작과 종료를 의미함
     - CONTROL
          : 조건문 또는 반복문이 삽입되어 순차적으로 처리됨
     - SQL
          : DML, DCL이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행함
     - EXCEPTION
          : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의함
     - TRANSACTION
          : 수행된 데이터 작업들을 DB에 적용할지 취소할지를 결정하는 처리부

2. 프로시저 생성
- 프로시저를 생성하기 위해서는 CREATE PROCEDURE 명령어를 사용함
- 표기 형식
     - CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
       [지역변수 선언]
       [BEGIN]
            프로시저 BODY;
       END;
     - OR REPLACE
          : 선택지인(Optional) 예약어
            이 예약어를 사용하면 동일한 프로시저 이름이 이미 존재하는 경우, 기존 프로시저를 대체가능함
     - 프로시저명
          : 생성하려는 프로시저의 이름을 지정함   
     - 파라미터
          : 프로시저 파라미터로는 다음과 같은 것들이 올 수 있음
          - IN : 호출 프로그램이 프로시저에게 값을 전달할 때 지정함
          - OUT : 프로시저가 호출 프로그램에게 값을 반환할 때 지정함
          - INOUT : 호출 프로그램이 프로시저에게 값을 전달하고,
                       프로시저 실행 후 호출 프로그램에 값을 반환할 때 지정함 
          - 매개변수명 : 호출 프로그램으로부터 전달받은 값을 지정할 변수의 이름을 지정함
          - 자료형 : 변수의 자료형을 지정함
     - 프로시저 BODY
          - 프로시저의 코드를 기록하는 부분
          - BEGIN에서 시작하여 END로 끝나며, BEGIN과 END 사이에는 적어도 하나의 SQL 문이 있어야 함

3. 프로시저 실행
- 프로시저를 실행하기 위해서는 EXECUTE 명령어, CALL 명령어를 사용
- EXECUTE 명령어를 줄여서 EXEC로 사용하기도 함
- 표기 형식
     - EXECUTE 프로시저명;
       EXEC 프로시저명;
       CALL 프로시저명;

4. 프로시저 제거
- 프로시저를 제거하기 위해서는 DROP PROCEDURE 명령어를 사용함
- 표기 형식
     - DROP PROCEDURE 프로시저명;

 

---

<트리거(Trigger)> ☆★★★


1. 트리거(Trigger)의 개요
- 데이터베이스 시스템에서 데이터의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL
- 트리거는 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용됨
- 트리거의 구문에는 DCL(데이터 제어어)을 사용할 수 없으며,
  DCL이 포함된 프로시저나 함수를 호출하는 경우에도 오류가 발생함
- 트리거에 오류가 있는 경어 트리거가 처리하는 데이터에도 영향을 미치므로 트리거를 생성할 떼 세심한 주의가 필요함

2. 트리거의 구성
- 선언, 이벤트, 시작, 종료로 구성되며, 시작과 종료 구문 사이에는 제어(CONTROL), SQL, 예외(EXEPTION)가 포함됨
- 트리거 구상도

출처 : https://shlee1990.tistory.com/836

     - DECLARE 
          : 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부
     - EVENT
          : 트리거가 실행되는 조건을 명시함
     - BEGIN / END
          : 트리거의 시작과 종료를 의미함
     - CONTROL
          : 조건문 또는 반복문이 삽입되어 순차적으로 처리됨
     - SQL
          : DML 문이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행함
     - EXCEPTION
          : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의함

3. 트리거의 생성
- 트리거를 생성하기 위해서는 CREATE TRIGGER 명령어를 사용함
- 표기 형식
     - CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
       REFERENCING [NEW | OLD] AS 테이블명
       FOR EACH ROW
       [WHEN 조건식]
       BEGIN
            트리거 BODY;
       END
     - OR REPLACE
          - 선택적인(Optional) 예약어
          - 이 예약어를 사용하면 동일한 트리거 이름이 이미 존재하는 경우, 기존의 트리거를 대체할 수 있음
     - 동작시기 옵션
          : 트리거가 실행될 때를 지정함. 옵션에는 AFTER와 BEFORE가 있음
          - AFTER : 테이블이 변경된 후에 트리거가 실행됨
          - BEFORE : 테이블이 변경되기 전에 트리거가 실행됨
     - 동작 옵션
          : 트리거가 실행되게 할 작업의 종류를 지정함. 옵션에는 INSERT, DELETE, UPDATE가 있음
          - INSERT : 테이블에 새로운 튜플을 삽입할 때 트리거가 실행됨
          - DELETE : 테이블에 튜플을 삭제할 때 트리거가 실행됨
          - UPDATE : 테이블의 튜플을 수정할 때 트리거가 실행됨
     - NEW | OLD
          : 트리거가 적용될 때 테이블의 별칭을 지정함
          - NEW : 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미함
          - OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미함
     - FOR EACH ROW
          : 각 튜플들마다 트리거를 적용한다는 의미
     - WHEN 조건식
          : 선택적인(Optional) 예약어. 트리거를 적용할 튜플의 조건을 지정함
     - 트리거 BODY
          - 트리거의 본문을 입력하는 부분
          - BEGIN으로 시작해서 END로 끝남
          - 적어도 하나 이상의 SQL문이 있어야 함. 그렇지 않으면 오류가 발생함

4. 트리거의 제거
- 트리거를 제거하기 우해서는 DROP TRIGGER 명령어를 사용함
- 표기 형식
     - DROP TRIGGER 트리거명.   

 

---

<사용자 정의 함수> ☆★★★


1. 사용자 정의 함수의 개요
- 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리
- 종료 시 처리 결과를 단일값으로 반환하는 절차형 SQL
- 사용자 정의 함수는 데이터베이스에 저장되어 SELECT, INSERT, DELETE, UPDATE 등 DML 문의 호출에 의해 실행됨
- 사용자 정의 함수는 예약어 RETURN을 통해 값을 반환하기 때문에 출력 파리미터가 없음
- 사용자 정의 함수는 INSERT, DELETE, UPDATE를 통한 테이블 조작은 할 수 없고 SELECT를 통한 조회만 할 수 있음
- 사용자 정의 하뭇는 SUM(), AVG() 등의 내장 함수처럼 DML 문에서 반환 값을 활용하기 위한 용도로 사용됨

2. 사용자 정의 함수의 구성
- 사용자 정의 함수의 구성은 프로시저와 유사함
- 프로시저의 구성에 RETRUN만 추가하면 됨
- 사용자 정의 함수의 구성도

출처 : https://shlee1990.tistory.com/836

     - DECLARE
          : 사용자 정의 함수의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부
     - BEGIN / END
          : 사용자 정의 함수의 시작과 종료를 의미함
     - CONTROL
          : 조건문 또는 반복문이 삽입되어 순차적으로 처리함
     - SQL
          : SELECT 문이 삽입되어 데이터 조회 작업을 수행함
     - EXCEPTION
          : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의함
     - RETURN
          : 호출 프로그램에 반환할 값이나 변수를 정의함

3. 사용자 정의 함수 생성
- 사용자 정의 함수를 생성하기 위해서는 CREATE FUNCTION 명령어를 사용함
- 표기 형식
     - CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
       [지역변수 선언]
       BEGIN
            사용자 정의 함수 BODY;
            RETURN 반환값;
       END;
     - OR REPLACE
          - 선택적인(Optional) 예약어
          - 이 예약어를 사용하면 동일한 사용자 정의 함수의 이름이 이미 존재하는 경우,
            기존 사용자 정의 함수를 대체할 수 있음
     - 파리미터
          : 사용자 정의 함수의 파라미터로는 다음과 같은 것들이 올 수 있음
          - IN : 호출 프로그램이 사용자 정의 함수에게 값을 전달할 때 지정함
          - 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정함
          - 자료형 : 변수의 자료형을 지정함
     - 사용자 정의 함수 BODY
          - 사용자 정의 함수의 코드를 기록하는 부분
          - BEGIN에서 시작하여 END로 끝나며, BEGIN과 END 사이에는 적어도 하나의 SQL문이 있어야 함
     - RETURN 반환값
          : 반환할 값이나 반환할 값이 저장된 변수를 호출 프로그램으로 돌려줌

4. 사용자 정의 함수 실행
- 사용자 정의 함수는 DML에서 속성명이나 값이 놓일 자리를 대체하여 사용됨
- 표기 형식
     - SELECT 사용자 저의 함수명 FROM 테이블명;
       INSERT INTO 테이블명(속성명) VALUES (사용자 정의 함수명);
       DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;
       UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

5. 사용자 정의 함수 제거
- 사용자 정의 함수를 제거하기 위해서는 DROP FUNTION 명령어를 사용함
- 표기 형식
     - DROP FUNCTION 사용자 정의 함수명;     

---

<DBMS 접속 기술> ☆★★★


1. DBMS 접속의 개요
- 사용자가 데이터를 사용하기 위해 응용 시스템을 이용하여 DBMS에 접근하는 것을 의미함
- 응용 시스템은 사용자로부터 매개변수를 전달받아 SQL을 실행하고
  DBMS로부터 전달받은 결과를 사용자에게 전다랗는 매게체 역할을 수행함
- 인터넷을 통해 구동되는 웹 응용 프로그램은 웹 응용 시스템을 통해 DBMS에 접근함
- 웹 응용 시스템은 웹 서버와 웹 애플리케이션 서버(WAS)로 구성되며,
  서비스 규모가 작은 경우에는 웹 서버와 웹 애플리케이션 서브를 통합하여 하나의 서버만으로 운용할 수 있음

2. DBMS 접속 기술
- DBMS에 접근하기 위해 사용하는 API 또는 API의 사용을 편리하게 도와주는 프레임워크 등을 의미함
- JDBC(Java DataBase Connectivity)
     - Java 언어로 다양한 종류의 데이터베이스에 접속하고 SQL 문을 수행할 때 사용되는 표준 API
     - Java SE(Standard Edition)에 포함되어 있으며, JDBC 클래스는 java, sql, javax.sql에 포함되어 있음
     - 접속하려는 DBMS에 대한 드라이버가 필요함
- ODBC(Open DataBase Connectivity)
     - 데이터베이스에 접근하기 위한 표준 개방형 API로, 개발 언어에 상관업이 사용 가능
     - 프로그램 내 ODBC 문장을 사용하여 MS-Access, DBase, DB2, Excel, Text 등 다양한 데이터베이스에 접근 가능
     - ODBC도 접속하려는 DBMS에 맞는 드라이버가 필요하지만,
       접속하려는 DBMS의 인터페이스를 알지 못하더라도 ODBC 문장을 사용하여 SQL을 작성하면
       ODBC에 포함된 드라이버 관리자가 해당 DBMS의 인터페이스에 맞게 연결해 주므로 DBMS의 종류를 몰라도 됨
- MyBatis
     - JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 접속 프레임워크
     - JDBC로 데이터베이스에 접속하려면 다양한 메소드를 호출하고 해제해야 하는데,
       MyBatis는 이를 간소화했고 접속 기능을 더욱 강화하였음
     - MyBatis는 SQL 문장을 분리하여 XML 파일을 만들고, Mapping을 통해 SQL을 실행함
     - MyBatis는 SQL을 거의 그대로 사용할 수 있어 SQL 친화적인 국내 환경에 적합하여 사용됨

3. 동적 SQL(Dynamic SQL)
- 개발 언어에 삽입되는 SQL 코드를 문자열 변수에 넣에 처리하는 것으로,
  조건에 따라 SQL 구문을 동적으로 변경하여 처리 가능
- 사용자로부터 SQL문의 일부, 전부를 입력받아 실행할 수 있음
- 값이 입력되지 않을 경우 사용하는 NVL 함수를 사용할 필요가 없다
- 응용 프로그램 수행 시 SQL이 변형될 수 잇으므로 프리컴파일 할 때 구문 분석, 접근 권한 확인 등을 할 수 없음
- 정적 SQL에 비해 속도가 느리지만, 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발이 가능함

---

<SQL 테스트> ☆★★★


1. SQL 테스트의 개요
- SQL이 작성 의도에 맞게 원하는 기능을 수행하는지 검증하는 과정
- 단문 SQL은 SQL 코드를 직접 실행한 후 결과를 확인하는 것으로 간단히 테스트가 가능함
- 절차형 SQL은 테스트 전에 생성을 통해 구문 오류(Syntac Error)나 참조 오류의 존재 여부를 확인함
- 정상적으로 생성된 절차형 SQL은 디버깅을 통해 로직을 검증하고, 결과를 통해 최종적으로 확인함

2. 단문 SQL 테스트
- DDL, DML, DCL이 포함되어 있는 SQL과 TCL을 테스트하는 것으로, 직접 실행하여 결과물을 확인함
- 실행 시 오류나 경고가 발생할 경우 메시지를 참조하여 문제를 해결함
- DESCRIBE 명령어를 이용하면 DDL로 작성된 테이블이나 뷰의 속성, 자료형, 옵션들을 바로 확인 가능
     - DESC [개체명];
- DML로 변경한 데이터는 SELECT 문으로 데이터의 정상적인 변경 여부를 확인할 수 있음
- DCL로 설정된 사용자 권한은 사용자 권한 정보가 저장된 테이블을 SELECT로 조회하거나, SHOW 명령어로 확인 가능
     - Oracle : SELECT * FROM DBA_ROLE_PRIVES WHERE GRANTEE = 사용자;
     - MySQL : SHOW GRANTS FOR 사용자@호스트;

3. 절차형 SQL 테스트
- 프로시저, 사용자 정의 함수, 트리거 등의 절차형 SQL은 디버깅을 통해 기능의 적합성 여부를 검증하고,
  실행을 통해 결과를 확인하는 테스트를 수행함
- 많은 코드로 구성된 절차형 SQL의 특성상 오류 및 경고 메시지가 상세히 출력되지 않으므로
  SHOW 명령어를 통해 오류 내용을 확인하고 문제를 수정함
     - 형식 : SHOW ERRORS;
- 데이터베이스에 변화를 줄 수 있는 SQL문은 주석으로 처리하고, 출력문을 이용하여 화면에 출력하여 확인함
     - Oracle 출력 형식
          - DBMS_OUTPUT.ENABLE; : 화면에 출력하기 위해 DBMS_OUTPUT 패키지를 불러옴
          - DBMS_OUTPUT.LINE(데이터); : '데이터'에 넣은 변수나 값을 화면에 출력함
     - MySQL 출력 형식
          - SELECT 데이터; : '데이터'에 넣은 변수나 값을 화며에 출력함
- 디버깅이 완료되면 출력문을 삭제하고, 주석 기호를 삭제한 후 절차형 SQL을 실해하여 결과를 검토함 

 

---

<ORM(Object - Relational Mappin)> ☆★★★


1. ORM(Object - Relational Mappin)의 개요
- 객체지향 프로그래밍의 객체(Object)와 관계형 데이터베이스(Relational Database)의
  데이터 연결(Mapping)하는 기술을 의미함
- ORM은 객체지향 프로그래밍에서 사용할 수 있는 가상의 객체지향 데이터베이스를 만들어
  프로르개밍 코드와 데이터를 연결함
- ORM으로 생성된 가상의 객체지향 데이터베이스는 프로그래밍 코드 또는
  데이터베이스와 독립적이므로 재사용 및 유지보수가 용이함
- ORM은 SQL 코드를 직접 입력하지 않고 선언문이나 할당 같은 부수적인 코드가 생략되기 때문에
  직관적이고 간단하게 데이터를 조작할 수 있음

2. ORM 프레임워크
- ORM을 구현하기 우한 구조와 구현을 위해 필요한 여러 기능을 제공하는 소프트웨어를 의미함
- ORFM 프레임워크의 종류
     - 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 등

3. ORM의 한계
- 프레임워크가 자동으로 SQL을 작성하기 때문에 의도대로 SQL이 작성되었는지를 확인할 필요가 있음
- 객체지향적인 사용을 고려하고 설계된 데이터베이스가 아닌 경우
  프로젝트가 크고 복잡해질소록 ORM 기술을 적용하기 어려워짐
- 기존 작업들은 ORM을 고려하지 않은 데이터베이스를 사용하고 있기 때문에
  ORM에 적합하게 변환하려면 많은 시간과 노력이 필요함

---

<데이터 전환> ☆★★★


1. 데이터 전환의 정의
- 운영 중인 기존 정보 시스템에 축적되어있는 데이터를 추출(Extraction)하여
  새로 개발할 정보 시스템에서 운영 가능하도록 변환(Transformation)한 후, 적재(Loading)하는 일련의 과정을 말함
- 데이터 전환을 ETL(Extraction, Transformation, Load), 즉 추출, 변환, 적재 과정이라고 함
- 데이터 전환을 데이터 이행(Data Migration) 또는 데이터 이관이라고도 함

2. 데이터 전환 계획서
- 데이터 전환이 필요한 대상을 분석하여 데이터 전환 작업에 필요한 모든 계획을 기록하는 문서
- 데이터 전환 개요
     - 데이터 전환 목표
     - 주요 성공 요인
     - 전제조건 및 제약조건
- 데이터 전환 대상 및 범위
- 데이터 전환 환경 구성
     - 원천 시스템 구성도
     - 목적 시스템 구성도
     - 전환 단계별 DISK 사용량
- 데이터 전환 일정
- 데이터 전환 방안
     - 데이터 전환 규칙
     - 데이터 전환 절차
     - 데이터 전환 방법
     - 데이터 전환 설계
     - 전환 프로그램 개발 및 테스트 계획
     - 데이터 전환 계획
     - 데이터 검증 방안
- 데이터 정비 방안
     - 데이터 정비 대상 및 방법
     - 데이어 정비 일정 및 조직
- 비상 계획
     - 종합상황실 및 의사소통 체계
- 데이터 복구 대책

 

www.yes24.com/Product/Goods/82838724?OzSrank=6

2020 시나공 정보처리기사 필기

* 2020 시나공 정보처리기사 필기 요약한 내용입니다.