2021 정보처리기사 실기 - 8. SQL 응용(2)

2021. 10. 9. 21:01

728x90

프로시저

프로시저의 개요

- 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어

- 호출을 통해 실행되어 미리 저장해놓은 SQL 작업 수행

- 여러 프로그램에서 호출하여 사용 가능

- 데이터베이스에 저장되어 수행되기 때문에 스토어드 프로시저라고도 함

- 시스템의 일일 마감 작업, 일괄 작업 등에 사용

프로시저의 구성

- DECLARE : 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부

- BEGIN / END : 프로시저의 시작과 종료를 의미

- CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리

- SQL : DML, DCL이 삽입되어 데이터 관리를 위한 작업(조회, 추가, 수정, 삭제) 수행

- EXCPETION : BEGIN ~ END 안의 구문 실행 시 예외 처리

- TRANSACTION : 수행된 데이터 작업들을 DB에 저장할지 취소할지 결정

프로시저 생성

CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
[지역번수 선언]
BEGIN
    프로시저 BODY;
END;

- OR REPLACE : 동일한 프로시저 이름이 이미 존재하는 경우 기존의 프로시저를 대체

- 파라미터

-> IN : 호출 프로그램이 프로시저에게 값을 전달할 때 사용

-> OUT : 프로시저가 호출 프로그램에게 값을 전달할 때 사용

-> INOUT : 호출 프로그램이 프로시저에게 값을 전달하고, 프로시저 실행 후 호출 프로그램에게 값을 반환할 때 지정

-> 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정

-> 자료형 : 변수의 자료형을 지정

- 프로시저 BODY

-> 프로시저의 코드를 기록

-> BEGIN과 END 사이에 적어도 하나의 SQL 문이 존재해야 함

- 사원번호를 입력받아 해당 사원의 지급방식을 S로 변경하는 프로시저

CREATE OR REPLACE PROCEDURE emp_change_s(i_사원번호 IN INT)
IS
BEGIN
    UPDATE 급여 SET 지급방식 = 'S' WHERE 사원번호 = i_사원번호;
    EXCEPTION
        WHEN PROGRAM_ERROR THEN
            ROLLBACK;
    COMMIT;
END;

프로시저 실행

EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;

- 위 3가지 명령어 중 하나를 사용

- 위에 정의한 emp_change_s 프로시저를 사원번호 32를 인수로 하여 실행

EXECUTE emp_change_s(32);
EXEC emp_change_s(32);
CALL emp_change_s(32);

프로시저 제거

DROP PROCEDURE 프로시저명;

- 위에 정의한 emp_change_s 프로시저를 제거

DROP PROCEDURE emp_change_s;

트리거

트리거의 개요

- 데이터베이스 시스템에서 데이터의 삽입 갱신 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

- 데이터베이스에 저장

- 데이터 변경 및 무결성 유지 로그 메시지 출력 등의 목적으로 사용

- DCL을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에도 오류 발생

- 트리거에 오류가 있는 경우 트리거가 처리하는 데이터에도 영향을 미침

트리거의 구성

- DECLARE : 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의

- EVENT : 트리거가 실행되는 조건

- BEGIN / END : 트리거의 시작과 끝

- CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리

- SQL : DML문이 삽입되어 데이터 관리를 위한 작업(조회, 추가, 수정, 삭제) 수행

- EXCEPTION : BEGIN ~ END 안의 구문에서 예외가 발생 시 처리

트리거의 생성

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

- 동작 시기 옵션 : 트리거가 실행될 때를 지정

-> AFTER : 테이블이 변경된 후

-> BEFORE : 테이블이 변경되기 전

- 동작 옵션 : 트리거가 실행되게 할 작업의 종류를 지정

-> INSERT : 새로운 튜플을 삽입할 때

-> DELETE : 튜플을 삭제할 때

-> UPDATE : 튜플을 수정할 때

- NEW | OLD : 트리거가 적용될 테이블의 별칭을 지정

-> NEW : 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미

-> OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미

- FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미

- WHEN 조건식 : 트리거를 적용할 튜플의 조건을 지정

- 학생 테이블에 새로운 튜플이 삽입될 때 튜플에 학년 정보가 누락되었으면 학년 필드에 신입생을 치환하는 트리거를 학년 정보_tri라는 이름으로 정의

CREATE TRIGGER 학생정보_tri BEFORE INSERT ON 학생
REFERENCING NEW AS new_table
FOR EACH ROW
WHEN (new_table.학년 IS NULL)
BEGIN
    :new_table.학년 := '신입생';
END;

트리거의 제거

DROP TRIGGER 트리거명;

- 위에 정의한 학생정보_tri를 제거

DROP TRIGGER 학생정보_tri;

사용자 정의 함수

사용자 정의 함수의 개요

- 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하여 종료 시 처리 결과를 단일 값으로 반환하는 절차형 SQL

- 데이터베이스에 저장되어 DML문의 호출에 의해 실행

- 예약어 RETURN을 통해 값이 반환되기 때문에 출력 파라미터가 없음

- 테이블 조작은 할 수 없고 SELECT를 통해 검색만 할 수 있음

- 프로시저를 호출하여 사용할 수 없음

사용자 정의 함수의 구성

- 프로시저의 구성과 유사

- RETURN : 호출 프로그램에 반환할 값이나 변수를 정의

사용자 정의 함수 생성

CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
[지역변수 선언]
BEGIN
    사용자 정의 함수 BODY;
    RETURN 반환;
END;

- 프로시저와 유사하며 파라미터에의 구성요소는 IN, 매개변수명, 자료형이 있음

- RETURN 반환값 : 반환할 값이나 반환할 값이 저장된 변수를 호출 프로그램으로 돌려줌

- i_성별코드를 입력받아 1이면 남자, 2면 여자를 반환하는 사용자 정의 함수를 Get_S_성별로 정의

CREATE FUNCTION Get_S_성별(i_성별코드 IN INT)
RETURN VARCHAR2
IS
BEGIN
    IF (i_성별코드 = 1) THEN
        RETURN '남자';
    ELSE
        RETURN '여자';
    END IF;
END;

사용자 정의 함수 실행

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

사용자 정의 함수 제거

DROP FUNCTION 사용자 정의 함수명;

- 위에 정의한 Get_S_성별을 제거

DROP FUNCTION Get_S_성별;

제어문

제어문

- 절차형 SQL의 진행 순서를 변경하기 위해 사용하는 명령문

IF문

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

IF 조건 THEN
	실행할 문장1;
    실행할 문장2;
ELSE
	실행할 문장3;
    실행할 문장4;
ENDIF

LOOP문

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

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

커서

- 쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가르키는 포인터

- 커서의 수행 : 열기 -> 패치 -> 닫기

묵시적 커서

- DBMS에 의해 내부에서 자동으로 생성되어 사용되는 커서

- 커서의 속성을 조회하여 사용된 쿼리 정보를 열람하는 것이 가능

- 수행된 쿼리문의 수행 여부를 확인하기 위해 사용

- 속성의 종류

-> SQL%FOUND : 쿼리 수행의 결과로 패치된 튜플 수가 1개 이상이면 TRUE

-> SQL%NOTFOUND : 쿼리 수행의 결과로 패치된 튜플 수가 0개면 TRUE

-> SQL%ROWFOUND : 쿼리 수행의 결과로 패치된 튜플 수를 반환

-> SQL%ISOPEN : 커서가 열린 상태면 TRUE. 묵시적 커서는 자동으로 생성된 후 자동으로 닫히기 때문에 항상 FALSE

명시적 커서

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

- 쿼리문의 결과를 저장하여 동일한 쿼리가 반복 수행되어 데이터베이스 자원이 낭비되는 것을 방지

- 선언 -> 열기 -> 패치 -> 닫기 형식으로 사용

DECLARE
	...
    CURSOR 커서명(매개변수, ...)
    IS
    SELECT ...;
    
BEGIN
	OPEN 커서명(매개변수, ...)
    
    FETCH 커서명 INTO 변수1, ...;
    
    CLOSE 커서명;
END;

DBMS 접속 기술

DBMS 접속 기술의 개요

- 사용자가 데이터를 접속하기 위해 응용 시스템을 이용하여 DBMS에 접근하는 것

- 응용 시스템은 사용자로부터 매개 변수를 전달받아 SQL을 실행하고 DBMS로부터 전달받은 결과를 사용자에게 전달

- 웹 응용프로그램은 웹 응용 시스템을 통해 DBMS에 접근

- 웹 응용 시스템은 웹 서버와 웹 애플리케이션 서버(WAS)로 구성

-> 사용자 ↔ 웹 서버 ↔ WAS ↔ DBMS

DBMS 접속 기술

- DBMS에 접근하기 위해 사용하는 API 또는 프레임워크를 의미

-> API(Application Programming Interface) : 응용 프로그램 개발 시 운영 체제나 DBMS 등을 이용할 수 있도록 규칙 등에 대해 정의해 놓은 인터페이스

-> 프레임워크 : 소프트웨어에서는 특정 기능을 수행하기 위해 필요한 클래스나 인터페이스 등을 모아둔 집합체

- JDBC(Java DataBase Connectivity)

-> Java 언어

-> 썬 마이크로시스템에서 출시

-> Java SE에 포함되어 있고 JDBC 클래스는 java.sql, javax.sql에 포함

-> 접속하려는 DBMS에 대한 드라이버 필요

- ODBC(Open DataBase Connectivity)

-> 개발 언어와 상관 없음

-> 마이크로소프트에서 출시

-> MS-Access, DBase, DB2, Excel, Text 등 다양한 데이터베이스에 접근 가능

- MyBatis

-> JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈소스 접속 프레임워크

-> SQL 문장을 분리하여 XML 파일을 만들고 Mapping을 통해 SQL을 실행

-> SQL을 거의 그대로 사용할 수 있어 국내 환경에 적합

동적 SQL

- 개발 언어에 삽입되는 SQL 코드를 문자열 변수에 넣어 처리하는 것

- 조건에 따라 SQL 구문을 동적으로 변경하여 처리 가능

- NVL 함수를 사용할 필요가 없음

- 응용 프로그램 수행 시 SQL이 변형될 수 있어 프리컴파일할 때 구문 분석, 접근 권한 확인 등을 할 수 없음

SQL 테스트

SQL 테스트의 개요

- SQL이 작성 의도에 맞게 원하는 기능을 수행하는지 검증하는 과정

- 단문 SQL은 코드를 직접 실행한 후 결과를 확인

- 절차형 SQL은 테스트 전에 생성을 통해 구문 오류나 참조 오류의 존재 여부 확인

- 정상적으로 생성된 절차형 SQL은 디버깅을 통해 로직을 검증하고 결과를 통해 최종 확인

단문 SQL 테스트

- DDL, DML, DCL이 포함되어 있는 SQL과 TCL을 직접 실행하여 테스트

- DESCRIBE 명령어를 이용하면 DDL로 작성된 테이블이나 뷰의 속성, 자료형, 옵션들을 확인할 수 있음

- DCL로 설정된 사용자 권한은 사용자 권한 정보가 저장된 테이블을 SELECT문으로 조회하거나 SHOW 명령어로 확인할 수 있음

-> Oracle : SELECT * FROM DBA_ROLE_PRIVES WHERE GRANTEE = 사용자;

-> MySQL : SHOW GRANTS FOR 사용자@호스트;

절차형 SQL 테스트

- 프로시저, 사용자 정의 함수, 트리거 등의 절차형 SQL은 디버깅을 통해 기능의 적합성 여부 검증, 실행을 통해 결과를 확인하는 테스트를 수행

- SHOW 명령어를 통해 오류 내용을 확인

-> SHOW ERRORS;

- 데이터베이스에 변화를 줄 수 있는 SQL문은 주석 처리 후 출력문을 이용하여 결과 확인

-> Oracle : DBMS_OUTPUT.ENABLE; / DBMS_OUTPUT.PUT_LINE(데이터);

-> MySQL : SELECT 데이터;

ORM

ORM(Object-Relational Mapping)의 개요

- 객체지향 프로그래밍의 객체와 관계형 데이터베이스의 데이터를 연결하는 기술

- 객체지향 프로그래밍에서 사용할 수 있는 가상의 객체지향 데이터베이스를 만들어 프로그래밍 코드와 데이터 연결

- 프로그래밍 코드 또는 데이터베이스와 독립적이므로 재사용 및 유지보수가 용이

- SQL 코드를 직접 사용하지 않기 때문에 직관적이고 간단하게 데이터 조작 가능

ORM 프레임워크

- ORM을 구현하기 위한 구조와 구현을 위해 필요한 여러 기능들을 제공하는 소프트웨어

- 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 등

ORM의 한계

- 프레임워크가 자동으로 작성하기 때문에 의도대로 작성되었는지 확인할 필요가 있음

- 객체지향적 사용을 고려, 설계한 데이터베이스가 아닌 경우 프로젝트가 크고 복잡할수록 ORM 기술을 적용하기 어려움

- 기존의 기업들은 ORM 고려하지 않은 데이터베이스를 사용하고 있어 ORM에 적합하게 변환하려면 많은 시간과 노력이 필요

쿼리 성능 최적화

쿼리 성능 치적화의 개요

- 데이터 입출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화

- 최적화 전 APM을 사용하여 최적화할 쿼리 선정

-> APM(Application Performance Management/Monitoring) : 애플리케이션의 성능 관리를 위해 다양한 모니터링 기능을 제공하는 도구

- RBO(Rule Based Optimizer) : 규칙 기반 옵티마이저

- CBO(Cost Based Optimizer) : 비용 기반 옵티마이저

실행 계획

- DBMS의 옵티마이저가 수립한 SQL 코드의 실행 절차와 방법을 의미

- EXPLAIN 명령어를 통해 확인

- 그래픽이나 텍스트로 표현

- 요구사항을 처리하기 위한 연산 순서가 적혀있고 연산에는 조인, 테이블 검색, 필터, 정렬 등이 있음

쿼리 성능 최적화

- 실행 계획에 표시된 연산 순서, 조인 방식, 테이블 조회 방법 등을 참고하여 SQL문이 더 빠르고 효율적으로 작동하도록 코드와 인덱스를 재구성

- SQL 코드 재구성

-> WHERE 절을 추가하여 일부 레코드만 조회

-> WHERE 절에 연산자 사용 자제

-> 특정 데이터 확인 시 IN보다 EXISTS 사용

-> 힌트를 활용하여 실행 계획의 액세스 경로 및 조인 순서 변경

- 인덱스 재구성

-> SQL 코드에서 조회되는 속성과 조건들을 고려하여 인덱스 구성

-> 인덱스를 추가하거나 기존 인덱스의 열 순서 변경

-> 단일 인덱스로 쓰기나 수정 없이 읽기로만 사용되는 경우 IOT(Index-Organized Table)로 구성

실기 정리

2021 정보처리기사 실기 정리

본 정리 글은 시나공 정보처리기사 실기책과 2020년 기출문제 등을 참고하여 작성하였습니다. -> 책 정보 확인하기 시나공 정보처리기사 실기 수험생들의 궁금증을 100% 반영시험에 나올만한 내

1d1cblog.tistory.com

728x90

저작자표시

'2020(개정) 이후 정보처리기사 > 8장 : SQL 응용' 카테고리의 다른 글

2021 정보처리기사 실기 - 8. SQL 응용(1) (0)	2021.10.09

1D1C