[정보처리기사 필기] 3-4. SQL 활용

7 분 소요

2020 시나공 정보처리기사 필기책 참고

주의! 중요도가 낮은 항목(C)은 일부 제외

1. 프로시저 – A

- 프로시저의 개요

절차형 SQL을 활용하여 특정 기능을 수행하는 트랜잭션 언어
미리 저장해 놓은 SQL 작업을 수행
여러 프로그램에서 호출하여 사용
스토어드 프로시저라고도 불림
시스템의 일일 마감 작업, 일괄 작업 등에 주로 사용

DECLARE (필수)
BEGIN (필수)
  CONTROL
  SQL
  EXCEPTION
  TRANSACTION
END (필수)

DECLARE : 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의
BEGIN / END : 프로시저의 시작과 종료
CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
SQL : DML, DCL이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업 수행
EXCEPTION : 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 프로시저명;

- 프로시저 제거

DROP PROCEDURE 프로시저명;

2. 트리거 – B

- 트리거의 개요

삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL
DB에 저장되며, 데이터 변경 및 무경성 유지, 로그 메시지 출력 등의 목적으로 사용
DCL을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에도 에러 발생

- 트리거의 구성

DECLARE (필수)
EVENT (필수)
BEGIN (필수)
  CONTROL
  SQL
  EXCEPTION
END (필수)

- 트리거의 생성

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

OR REPLACE : 동일한 트리거 이름이 이미 존재하는 경우, 기존의 트리거를 대체
동작시기 : 트리거가 실행될 때를 지정
- AFTER : 테이블이 변경된 후에 트리거 실행
- BEFORE : 테이블이 변경되기 전에 트리거 실행
동작 : 트리거가 실행되게 할 작업의 종류를 지정
- INSERT : 테이블에 새로운 튜플을 삽입할 때 트리거 실행
- DELETE : 테이블의 튜플을 삭제할 때 트리거 실행
- UPDATE : 테이블의 튜플을 수정할 때 트리거 실행
NEW OLD : 트리거가 적용될 테이브르이 별칭을 지정
- NEW : 추가되거나 수정에 참여할 튜플들의 집합
- OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합
FOR EACH ROW : 튜플마다 트리거를 적용한다는 의미
WHEN 조건식 : 선택적인 예약어. 트리거를 적용할 튜플의 조건을 지정
트리거 BODY
- 트리거의 본문 코드를 입력하는 부분
- BEGIN ~ END 사이에 적어도 하나 이상의 SQL문이 있어야 함

// <학생> 테이블에 새로운 튜플이 삽입될 때, 삽입되는 튜플에 학년 정보가 누락
// 됐으면 '학년'필드에 "신입생"을 치환하는 트리거를 '학년정보_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 트리거명;

3. 사용자 정의 함수 – B

- 사용자 정의 함수의 개요

프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리
종료 시 처리 결과를 단일값으로 반환하는 절차형 SQL
DML문의 호출에 의해 실행됨
예약어 RETURN을 통해 값을 반환하기 때문에 출력 파라미터가 없음
INSERT, DELETE, UPDATE를 통한 테이블 조작은 할 수 없고 SELECT를 통한 조회만 가능
프로시저를 호출하여 사용할 수 없음
DML문에서 반환값을 활용하기 위한 용도로 사용

- 프로시저 vs 사용자 정의 함수

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

- 사용자 정의 함수의 구성

DECLARE (필수)
BEGIN (필수)
  CONTROL
  SQL
  EXCEPTION
  RETURN (필수)
END (필수)

DECLARE : 선언부
BEGIN / END : 시작과 종료
CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
SQL : SELECT문이 삽입되어 데이터 조회 작업 수행
EXCEPTION : 예외 처리
RETURN : 호출 프로그램에 반환할 값이나 변수를 정의

- 사용자 정의 함수 생성

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

OR REPLACE : 동일한 사용자 정의 함수의 이름이 존재하는 경우, 기존의 함수 대체
파라미터
- IN : 호출 프로그램이 사용자 정의 함수에게 값을 전달할 때 지정
- 매개변수명 : 전달받은 값을 저장할 변수의 이름 지정
- 자료형 : 변수의 자료형 지정
사용자 정의 함수 BODY
- 코드를 기록하는 부분
- BEGIN ~ END 사이에는 적어도 하나의 SQL문이 있어야 함
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 사용자 정의 함수명;

4. DBMS 접속 기술 – B

- DBMS 접속의 개요

사용자가 데이터를 사용하기 위해 응용 시스템을 이용하여 DBMS에 접근하는 것
응용시스템은 사용자로부터 매개 변수를 전달받아 SQL을 실행하고 DBMS로부터 전달받은 결과를 사용자에게 전달하는 매개체 역할 수행
웹 응용 프로그램은 웹 응용 시스템을 통해 DBMS에 접근
웹 응용 시스템은 웹 서버와 웹 애플리케이션 서버(WAS)로 구성
규모가 작은 경우 웹 서버와 WAS를 통합하여 하나의 서버만으로 운용 가능

- 웹 응용 시스템의 구조

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

- DBMS 접속 기술

DBMS에 접근하기 위해 사용하는 API 또는 API의 사용을 편리하게 도와주는 프레임워크
JDBC(Java DataBase Connectivity)
- Java 언어로 다양한 DB에 접속하고 SQL문을 수행할 때 사용되는 표준 API
- Java SE에 포함. JDBC 클래스는 java.sql, javax.sql에 포함
- 접속하려는 DBMS에 대한 드라이버 필요
ODBC(Open DataBase Connectivity)
- DB에 접근하기 위한 표준 개방형 API. 개발 언어에 관계없이 사용 가능
- MS에서 출시
- ODBC 문장을 사용하여 SQL을 작성하면 해당 DBMS의 인터페이스에 맞게 연결해 줌
MyBatis
- JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 접속 프레임워크
- 간소화, 접속 기능 강화
- SQL 문장을 분리하여 XML파일 만들고, Mapping을 통해 SQL 실행
- SQL을 거의 그대로 사용할 수 있음

- 동적 SQL(Dynamic SQL)

개발 언어에 삽입되는 SQL코드를 문자열 변수에 넣어 처리
SQL문의 일부 또는 전부를 입력받아 실행할 수 있음
값이 입력되지 않을 경우 사용하는 NVL 함수를 사용할 필요가 없음
프리컴파일 할 때 구문 분석, 접근 권한 확인 등을 할 수 없음
정적 SQL에 비해 속도가 느리지만, 유연한 개발이 가능함

- 정적 SQL vs 동적 SQL

구분	정적 SQL	동적 SQL
SQL 구성	커서를 통한 정적 처리	문자열 변수에 담아 동적 처리
개발 패턴	반복문 활용하여 SQL작성	NVL 없이 로직을 통해 SQL 작성
실행 속도	빠름	느림
사전 검사	가능	불가능

5. SQL 테스트 – B

- SQL 테스트의 개요

SQL이 작성 의도에 맞게 원하는 기능을 수행하는지 검증
단문 SQL은 코드를 직접 실행한 후 결과를 확인
절차형 SQL은 테스트 전에 생성을 통해 Syntax Error나 참조 오류의 존재 확인
정상적으로 생성된 절차형 SQL은 디버깅을 통해 로직을 검증하고, 결과를 통해 최종 확인

- 단문 SQL 테스트

직접 실행하여 결과물 확인
오류 발생 시 메시지를 참조하여 문제를 해결
DESCRIBE 명령어로 DDL로 작성된 테이블이나 뷰의 속성, 자료형, 옵션들을 확인 가능
DML로 변경한 데이터는 SELECT문으로 데이터의 정상적인 변경 여부를 확인
DCL로 설정된 사용자 권한은 해당 테이블을 SELECT로 조회하거나, SHOW 명령어로 확인
- Oracle : SELECT * FROM ~~~
- MySQL : SHOW GRANTS FOR ~~~

// MySQL에서 <학생> 테이블의 정보를 확인하는 SQL문 작성
DESC 학생;

// Oracle에서 'SINAGONG' 사용자의 시스템 권한을 확인하는 SQL문 작성
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'SINAGONG';

// MySQL에서 localhost 서버의 'SINAGONG' 사용자에 대한 권한을 확인하는 SQL문 작성
SHOW GRANTS FOR 'SINAGONG'@'localhost';

- 절차형 SQL 테스트

디버깅을 통해 기능의 적합성 여부를 검증하고, 실행을 통해 결과를 확인
SHOW 명령어를 통해 오류 내용을 확인하고 문제를 수정
DB에 변화를 줄 수 있는 SQL문은 주석으로 처리하고, 출력문을 이용해 확인
- Oracle 출력 형식
  - DBMS_OUTPUT.ENABLE; : DBMS_OUTPUT 패키지를 불러옴
  - DBMS_OUTPUT.PUT_LINE(데이터); : ‘데이터’에 넣은 변수나 값을 출력
- MySQL 출력 형식
  - SELECT 데이터; : ‘데이터’에 넣은 변수나 값을 출력
디버깅이 완료되면 출력문을 삭제하고 주석을 제거

// Oracle에서 변수 RESULT를 출력하는 SQL문 작성
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE(RESULT);

// MySQL에서 변수 RESULT를 출력하는 SQL문 작성
SELECT RESULT;

6. ORM(Object-Relational Mapping) – B

- ORM의 개요

객체지향 프로그래밍의 객체와 RDB의 데이터를 Mapping하는 기술
가상의 객체지향 DB를 만들어 프로그래밍 코드와 데이터를 연결
재사용 및 유지보수 용이
직관적이고 간단하게 데이터 조작 가능

- ORM 프레임워크

JAVA : JPA, Hibernate, EclipseLink, DataNucleus, Ebean 등
C++ : ODB, QxOrm 등
Python : Django, SQLAlchemy, Strom 등
iOS : DatabaseObjects, Core Data 등
.NET : NHibernate, DatabaseObjects, Dapper 등
PHP : Doctrine, Propel, RedBean 등

- ORM의 한계

의도대로 SQL이 작성되었는지 확인할 필요가 있음
프로젝트가 크고 복잡해질수록 ORM 기술을 적용하기 어려움
ORM에 적합하게 변환하려면 많은 시간과 노력 필요

Twitter Facebook LinkedIn

LWW

[정보처리기사 필기] 3-4. SQL 활용

1. 프로시저 – A

- 프로시저의 개요

- 프로시저 생성

- 프로시저 실행

- 프로시저 제거

2. 트리거 – B

- 트리거의 개요

- 트리거의 구성

- 트리거의 생성

- 트리거의 제거

3. 사용자 정의 함수 – B

- 사용자 정의 함수의 개요

- 프로시저 vs 사용자 정의 함수

- 사용자 정의 함수의 구성

- 사용자 정의 함수 생성

- 사용자 정의 함수 실행

- 사용자 정의 함수 제거

4. DBMS 접속 기술 – B

- DBMS 접속의 개요

- 웹 응용 시스템의 구조

- DBMS 접속 기술

- 동적 SQL(Dynamic SQL)

- 정적 SQL vs 동적 SQL

5. SQL 테스트 – B

- SQL 테스트의 개요

- 단문 SQL 테스트

- 절차형 SQL 테스트

6. ORM(Object-Relational Mapping) – B

- ORM의 개요

- ORM 프레임워크

- ORM의 한계

공유하기

댓글남기기

참고

[Baekjoon] 9076번 : 점수 집계

[Baekjoon] 5545번 : 최고의 피자

[Baekjoon] 20540번 : 연길이의 이상형

[Baekjoon] 4811번 : 알약