3과목 데이터베이스 구축 SQL 활용 114 ~ 120

114. 프로시저

: 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어. > 호출을 통해 실행되어 미리 저장해 놓은 SQL 작업 수행.

- 스토어드 프로시저

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

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

 

1) 구성도

- DECLARE (필수) : 프로시저의 명칭, 변수, 인수, 데이터 타입 정의

- BEGIN (필수) : 시작

• CONTROL : 조건문/반복문 삽입되어 순차적 처리
• SQL : DML, DCL 삽입되어 순차적 처리
• EXCEPTION : BEGIN~END 안의 구문 실행시 예외가 발생하면 이를 처리하는 방법 정의
• TRANSACTION : 수행된 데이터 작업들을 DB에 적용할지 취소할지를 결정하는 처리부

- END (필수) : 종료

 

2) 프로시저 생성

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

- 파라미터 :

• IN(호출 프로그램이 프로시저에세 값을 전달할 때 지정)
• OUT(프로시저가 호출 프로그램에게 값을 반환할 때 지정)
• INOUT (호출 프로그램이 프로시저에게 값을 전달하고, 프로시저 실행 후 호출 프로그램에 값을 반환할 때 지정)
• 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름 지정
• 자료형 : 변수의 자료형 지정.

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

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;

 

3) 프로시저 실행

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

 

4) 프로시저 제거

DROP PROCEDURE 프로시저명;

 

 

115. 트리거

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

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

- DCL 사용 불가.

 

1) 구성

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

- EVENT (필수) : 트리거 실행 조건 명시

- BEGIN (필수) : 시작

• CONTROL : 조건문/반복문 삽입되어 순차적 처리
• SQL : DML, DCL 삽입되어 순차적 처리
• EXCEPTION : BEGIN~END 안의 구문 실행시 예외가 발생하면 이를 처리하는 방법 정의

- END (필수) : 종료

 

2) 트리거 생성

- OR REPLACE : 동일한 트리거 이름이 이미 존재하는 경우, 기존 프로시저 대체

- 동작시기 : 트리거 실행될 때 지정. 

• ALTER : 테이블 변경 후에 트리거 실행
• BEFORE : 테이블 변경 전에 트리거 실행

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

• INSERT : 테이블에 새로운 튜플 삽입할 때 트리거 실행
• DELETE
• UPDATE

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

• NEW : 추가되거나 수정에 참여할 튜플들의 집합
• OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합.

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

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

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

CREATE OR REPLACE TRIGGER 학년정보_tri BEFORE INSERT ON 학생
// 학생 테이블에 튜플을 삽입하기 전에 동작하는 트리거 생성.
REFERENCING NEW AS new_table
// 새로 추가될 튜플들의 집합 NEW의 별칭을 <new_table>로 명명.
FOR EACH ROW
// 모든 튜플들을 대상으로 한다. 
WHEN (new_table.학생 IS NULL)
// new_table에서 학년 속성이 NULL인 튜플에 학년정보_tri 트리거가 적용된다.
	BEGIN
		:new_table.학년 := '신입생';
        // new_table의 학년 속성에 '신입생'을 치환한다.
        // NEW로 지정된 테이블 이름 앞에 콜론(:)이 들어간다. 
    END;

 

3) 트리거 제거

DROP TRIGGER 트리거명;

 

116. 사용자 정의 함수

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

- 예약어 RETURN을 통해 값 반환. 출력 파라미터가 없다.

- INSERT, DELETE, UPDATE를 통한 테이블 조작 불가. /SELECT를 통한 조회만 가능.

- 프로시저 호출 불가!

 

cf) 프로시저와 구분!

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

 

1) 구성

- DECLARE (필수) : 사용자 정의 함수의 명칭, 변수, 인수, 데이터 타입 정의

- BEGIN (필수) : 시작

• CONTROL : 조건문/반복문 삽입되어 순차적 처리
• SQL : DML, DCL 삽입되어 순차적 처리
• EXCEPTION : BEGIN~END 안의 구문 실행시 예외가 발생하면 이를 처리하는 방법 정의
• RETURN : 호출 프로그램에 반환할 값이나 변수 정

- END (필수) : 종료

 

2) 사용자 정의 함수 생성

- OR REPLACE : 동일한 사용자 정의 함수 이름이 이미 존재하는 경우,  대체

- 파라미터 :

• IN(호출 프로그램이 사용자 정의 함수에게 값을 전달할 때 지정)
• 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름 지정
• 자료형 : 변수의 자료형 지정.

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

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

 

3) 사용자 정의 함수 실행

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

SELECT 이름, Get_S_성별(성별코드) FROM 사원;

 

4) 사용자 정의 함수 제거

DROP FUNCTION 사용자정의함수명;

 

 

117. DBMS 접속 기술

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

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

 

- DBMS 접속 기술

1) JDBC (Java DataBase Connectivity)

: Java 언어로 다양한 종류의 데이터베이스에 접속하고 SQL문을 수행할 때 사용되는 표준 API.

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

- Java SE에 포함.

 

2) ODBC (Open DataBase Connectivity) 

: 데이터베이스에 접근하기 위한 표준 개방형 API. (개발 언어 관계없이 사용가능.)

- 접속하려는 DBMS의 인터페이스를 알지 못하더라도 ODBC 문장을 사용하여 SQL을 작성하면 ODBC에 포함된 드라이버 관리자가 해당 DBMS의 인터페이스에 맞게 연결해줌.

 

3) MyBatis

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

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

 

- 동적 SQL

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

- 프리컴파일 할 때 구문분석, 접근 권한 확인 등 불가.

- NVL 함수 사용할 필요가 없다.

 

118. SQL 테스트

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

 

1) 단문 SQL 테스트

: DDL, DML, DCL이 포함되어 있는 SQL과 TCL을 테스트. 직접 실행하여 결과물 확인.

- DESCRIBE 명령어 : DDL로 작성된 테이블이나 뷰의 속성, 자료형, 옵션들을 바로 확인 가능.

DESC 학생; 

// Oracle
SELECT * FROM DBA_SYS PRIVS WHERE GRANTEE='사용자1'; 

// MySQL
SHOW GRANTS FOR 사용자@호스트;

 

2) 절차형 SQL 테스트

: 절차형 SQL은 디버깅을 통해 기능의 적합성 여부를 검증하고, 실행을 통해 결과를 확인하는 테스트를 수행. 

- SHOW ERRORS;

- Oracle

• DBMS_OUTPUT.ENABLE : 화면에 출력하기 위해 DBMS_OUTPUT 패키지를 불러온다.
• DBMS_OUTPUT.PUT_LINE(데이터) : 데이터에 넣은 변수나 값을 화면에 출력한다.

- MySQL

• SELECT 데이터; : 데이터에 넣은 변수나 값을 화면에 출력

 

119. ORM (Object-Relational Mapping)

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

- SQL 코드를 직접 입력하지 않고 선언문이나 할당 같은 부수적인 코드가 생략됨.

- JAVA : JPA, Hibernate, EclipseLink, DataNucleus, Ebean

- C++ : ODB, QxOrm

- Python : Django, SQLAlchemy, Storm

- iOS : DatabaseObjects, Core Data

- .NET : NHibernate, DatabseObjects, Dapper

- PHP : Doctrine, Propel, RedBean

 

-  ORM의 한계 : 의도대로 SQL이 작성되었는지 확인할 필요가 있다. 객체지향적인 사용을 고려하고 설계된 데이터베이스가 아닌 경우 기술 적용 어려움.

 

120. 쿼리 성능 최적화

: 데이터 입,출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것.

- APM(성능 측정 도구)을 사용하여 최적화할 쿼리를 선정.

- 최적화 할 쿼리에 대해 옵티마이저가 수립한 실행 계획을 검토하고 SQL 코드와 인덱스 재구성. 

	RBO(Rule Based Optimizer)	CBO(Cost Based Optimizer)
최적화 기준	규칙에 정의된 우선순위	액세스 비용
성능 기준	개발자의 SQL 숙련도	옵티마이저의 예측 성능
특징	실행 계획 예측이 쉬움	성능 통계치 정보 활용, 예측이 복잡함
고려사항	개발자의 규칙 이해도, 규칙의 효율성	비용 산출 공식의 정확성

 

1) 실행계획 (Execution Plan) : DBMS의 옵티마이저가 수립한 SQL 코드의 실행 절차와 방법. 

- EXPLAIN 명령어로 확인. 

2) 쿼리 성능 최적화 : 연산 순서, 조인 방식, 테이블 조회 방법 등을 참고하여 SQL문이 더 빠르고 효율적으로 작동하도록 재구성. 

- SQL 코드 재구성

- 인덱스 재구성.