9강. 저장 객체

1. 데이터베이스 언어의 특징

• SQL
  • DBMS에 대한 강력한 작업 지시 기능을 제공
  • 인간의 언어와 매우 유사하고 간단, 명료
  • 비절차적(선언형) 언어, 필요한 데이터만 기술
• 비절차적 언어 ( non-procedural language )
  • 목표만 명시하고 수행에 대한 절차는 기술하지 않음
  • 높은 가독성과 동작 순서에 대한 구체적 기술이 없어 오류가 상대적으로 적은 점이 장점
  • 프로그램의 성능 최적화, 디버깅, 오류 추적 및 복잡한 로직 구현이 한계
• 저장 객체를 통해 SQL의 단점을 보완

2. 저장 객체의 이해

• 저장 객체의 정의
  • SQL 문을 확장하여 절차적으로 처리하기 위한 기능을 제공하는 언어
  • SQL / PSM ( Persistent Stored Module ) 기반의 확장 언어
• 저장 객체의 구조

-- 형식 정의 부분
CREATE PROCEDURE do_repeat (param1 INT)

-- 기능 정의 부분
BEGIN
	SET @x = 0;
	REPEAT
		SET @x = @x+1;
	UNTIL @x > param1
	END REPEAT;
END;

3. 저장 객체의 장단점

• 저장 객체의 장점
  • 네트워크 전송 효율 향상
    • 오가는 데이터의 양을 줄일 수 있기에 클라이언트와 DBMS 사이에 트래픽 감소
  • 효율적 실행 속도 향상
    • 저장 객체는 컴파일되어 캐시에 저장
  • 모듈화
    • 대규모 작업의 시간 단축, 프로그램 크기의 축소
  • 보안성 향상
    • 사용자별, 작업 단위별로 권한 부여 가능
• 저장 객체의 단점
  • 처리 성능 저하
    • 서버 메모리 사용량이 증가로 인한 성능 저하
  • 어려운 디버깅
    • 저장 객체를 위한 디버깅 환경 미지원

4. 저장 객체의 종류

• 저장 프로시저
  • 자주 사용되거나 복잡한 과정을 거치는 SQL문을 저장하여 하나의 객체로 관리
• 함수
  • 저장 프로시저의 유사, RETURN문을 통해 결과값을 반환하는 기능 제공
  • SELECT 문에 포함되어 실행
• 트리거
  • 데이터 갱신 시 지정된 애플리케이션이 동작 또는 다른 테이블의 데이터를 변경
  • DBMS가 자동으로 반응하도록 만드는 역할 수행

5. 저장 프로시저의 이해

• 저장 프로시저의 정의
  • 응용 작업 중 자주 사용되거나 다수의 SQL 문으로 구성되는 복잡한 과정이 저장된 객체
  • 데이터를 주고받는 여러 개의 SQL 문을 묶어서 단계별로 실행
• 저장 프로시저의 특징
  • 함수와는 달리 이름을 이용하여 값을 반환하지 않음
  • 매개변수를 받아들이거나 반환
    • 프로시저 내에서 또 다른 프로시저 호출 가능
  • 실행과 관련된 제어값과 메시지를 반환

6. 저장 프로시저의 생성

• 생성 구문형식

CREATE [DEFINER {사용자 | 현재사용자}]
	PROCEDURE 프로시저명 ([매개변수[, ...]])
BEGIN
	SQL문
	...
END

• 매개변수 정의
  • 기본적으로 값이 주어지지 않으면 IN 타입으로 변수 생성

[IN | OUT | INOUT] 매개변수명 데이터 타입

7. 저장 프로시저의 활용 1

• 저장 프로시저 생성

DELIMITER $$
CREATE PROCEDURE GetStudentByGender()
BEGIN
    SELECT 성별, COUNT(*) FROM 학생
    GROUP BY 성별;
END $$
DELIMITER;

• 문장 구분자
  • DELIMITER는 개별 SQL 문 구분하는 구분자 정의
  • BEGIN-END 블럭 내에 SQL 문과 기능 정의 부분의 구분자 구별 필요\

8. 저장 프로시저의 호출

• 호출 구문형식

CALL 저장 프로시저명([매개변수[, ...]]);
또는
CALL 저장 프로시저명([ ]);

• 예시
  • CALL GetStudentByGender;

9. 저장 프로시저의 수정과 삭제

• 저장 프로시저의 수정
  • ALTER PROCEDURE 명령을 사용
  • 보안 및 동작 방식에 대한 속성을 변경할 때만 사용
  • 다른 것을 수정하길 원하실때는 삭제 후 재생성
    • 모든 저장 객체 동일 ( 함수, 트리거도 동일 )
• 저장 프로시저의 삭제
  • DROP PROCEDURE 프로시저명;
• 예시
  • DROP PROCEDURE GetStudentByGender;

10. 매개변수의 사용

• 매개변수의 역할
  • 외부로부터 주어지는 값이나 기본값을 매개변수를 통해 저장 프로시저로 전달
  • 저장 프로시저를 실행한 결과값을 반환
• IN, OUT, INOUT 세 타입의 매개변수를 제공
  • IN : 기본 타입, 외부로부터 값을 전달
  • OUT : 반환값을 저장
  • INOUT : IN과 OUT의 목적으로 모두 사용

11. 매개변수의 활용

DELIMITER $$
CREATE PROCEDURE GetPhoneByStudentID(
    IN sid CHAR(13),
    OUT phone CHAR(15)) -- 전화번호를 저장한 phone OUT
BEGIN
    SELECT 전화번호 INTO phone FROM 학생 -- 전화번호를 phone에 저장
        WHERE 학생번호 = sid;
END $$
DELIMITER ;

CALL GetPhoneByStudentID('202034-596541', @phone); -- 매개변수 사용하여 전달
SELECT @phone;

12. 변수의 사용

• 저장 객체에서 처리하는 응용 과정에서 발생하는 임시적인 값을 저장하고 재사용하는 기능 제공
  • 사용자 정의 변수
    • @ 시작
    • 별도의 선언 없이 사용
  • 로컬 변수
    • 저장 객체 내에서만 사용
    • DECLARE로 선언
• 구문형식
  • DECLARE 변수명[, ...] 데이터 타입[(크기)] [DEFAULT 기본값];
  • 데이터 타입( 크기 ) : 변수가 가질 수 있는 데이터 타입과 크기를 지정
  • DEFAULT 기본값 : 변수가 선언될 때 초기값을 지정

13. 변수값 할당

• 선언된 변수에 값을 저장( 할당 )하는 방법
  • SET 명령

DECLARE total_count INT DEFAULT 0;
SET total_count = 10;

• SELECT INTO 문
  • 숫자 타입만 가능
  • SQL 문의 실행 결과를 특정 변수에 넣어준다는 의미

DECLARE total_student INT DEFAULT 0;
SELECT COUNT(*) INTO total_student FROM 학생;

14. 선택 구조 - IF

• 주어진 조건이나 식의 값에 따라 SQL문의 실행 여부를 제어
• 구문형식

IF 조건식 THEN
    SQL문
[ELSEIF 조건식 THEN
    SQL문]
[ELSE
    SQL문]
END IF;

• 예시

DELIMITER $$
CREATE PROCEDURE GetGradeByCredit(
     IN sID CHAR(13), -- 학생번호
     OUT nGrade TINYINT) -- 학년
 BEGIN
     DECLARE nTotalCredit SMALLINT; -- 총 이수학점
     SELECT SUM(이수학점) INTO nTotalCredit FROM 전공
         WHERE 학생번호 = sID;
     IF nTotalCredit > 120 THEN
         SET nGrade = 4;
     ELSEIF (nTotalCredit > 80 AND nTotalCredit < 120) THEN
         SET nGrade = 3;
     ELSEIF (nTotalCredit > 40 AND nTotalCredit < 80) THEN
         SET nGrade = 2;
     ELSE
         SET nGrade = 1;
     END IF;
 END $$
 DELIMITER;

15. 선택 구조 - CASE

• 다중 IF ELSE 문을 간편하게 사용하기 위한 명령어

CASE 변수
    WHEN 비교변숫값1 THEN SQL문
    [WHEN 비교변숫값2 THEN SQL문]
    ...
    [ELSE SQL문]
END CASE;

CASE
    WHEN 조건식1 THEN SQL문
    [WHEN 조건식2 THEN SQL문]
    ...
    [ELSE SQL문]
END CASE;

16. 반복 구조

• MySQL은 특정 SQL문을 주어진 조건이 만족하는 동안 특정 구간을 반복시킬 수 있는 세 가지 구문을 제공
  • REPEAT
  • LOOP
  • WHILE

CREATE PROCEDURE GetFirstCourse(
    IN sCourseCode CHAR(5), OUT sPrerequisite CHAR(5))
BEGIN
    DECLARE sInputCourse CHAR(5);
    SET sInputCourse = sCourseCode;
pre: WHILE true DO -- while 시작
    SELECT 선수과목 INTO sPrerequisite FROM 과목
        WHERE 과목코드 = sCourseCode;
    IF sPrerequisite IS NOT NULL THEN
        SET sCourseCode = sPrerequisite;
        SET sPrerequisite = NULL;
    ELSE
        LEAVE pre; -- break;
    END IF;
END WHILE; -- while 끝
IF sCourseCode = sInputCourse THEN
    SET sPrerequisite NULL;
ELSE
    SET sPrerequisite = sCourseCode;
END IF;
END$$

• 반복문 안에서 반복 작업을 제어하기 위해 LEAVE와 ITERATE 문을 사용
  • LEAVE는 반복 종료 ( == break; )
  • ITERATE는 현재 반복 구간을 중단하고 조건을 재검사하여 반복 여부는 판별 ( == continue; )

17. 함수의 이해

• DBMS는 문자열 함수, 숫자 함수, 날짜 함수, 집계 함수 등 많은 편리한 함수를 제공
• 사용자가 프로그래밍 언어의 기능을 사용하여 직접 함수를 정의하는 사용자 정의 함수 기능을 제공
• 별도 라인별로 적용되며, 결과값은 컬럼으로 나타난다.
• 저장 프로시저와 함수의 차이
  • 저장 프로시저는 OUT 매개변수를 통해 결과를 반환, 함수는 RETURN문에 의해 특정 값을 반환
  • 저장 프로시저는 CALL 명령에 의해 실행되나, 함수는 SELECT문에 포함되어 실행(호출)

18. 함수의 생성

• 구문형식

CREATE
    [DEFINER {사용자|현재사용자}]
    FUNCTION 함수명([매개변수[, ...]])
	    RETURNS 데이터 타입
	    [Characteristic]
BEGIN
    SQL문
END

• 매개변수 : IN 타입의 매개변수만 사용 가능
• Characteristic : SQL 문의 특성을 기술
  • NO SQL / READ SQL DATA / MODIFIES SQL DATA

19. 트리거의 이해

• 데이터 변경 시 자동으로 지정된 애플리케이션이 동작하거나 다른 테이블의 데이터를 변경하도록 설계된 저장 프로시저의 특별한 형태
• 트리거의 역할
  • 전체적인 데이터베이스의 무결성과 일관성을 유지하도록 동작
  • 조건에 따른 데이터 적합성 검사 가능
  • 연속적 트리거 실행 시 다른 데이터베이스나 서버에 대한 광범위한 참조 무결성 검사도 가능
• 주의점
  • 실제 DBMS 내부에서 동작했을 때 어떤 트리거가 발동되었는지 유추하기 힘듦
  • 연쇄적인 트리거 발생은 때때로 DBMS의 실패상황을 야기할 수도 있음

20. 트리거의 종류

• 트리거 이벤트와 트리거 시점으로 구분
  • 트리거 이벤트 : 자동으로 반응하는 SQL 문
  • 트리거 시점 : 트리거가 실행될 시점을 명시

트리거 이벤트	실행시점	기능
INSERT	BEFORE	테이블에 데이터가 입력되기 전에 실행
INSERT	AFTER	테이블에 데이터가 입력된 후에 실행
UPDATE	BEFORE	테이블의 데이터가 수정되기 전에 실행
UPDATE	AFTER	테이블의 데이터가 수정된 후에 실행
DELETE	BEFORE	테이블의 데이터가 삭제되기 전에 실행
DELETE	AFTER	테이블의 데이터가 삭제된 후에 실행

21. 트리거의 생성

• 구문형식

CREATE TRIGGER 트리거이름
    트리거시점 트리거이벤트 ON 테이블이름
    FOR EACH ROW
BEGIN
    SQL문
END

• 사용 예시

DELIMITER $$
CREATE TRIGGER before_과목_update
    BEFORE UPDATE ON 과목
    FOR EACH ROW
BEGIN
    IF NEW.학점 < 1 THEN
        SET NEW.학점 = 1;
    ELSEIF NEW.학점 > 3 THEN
        SET NEW.학점 = 3;
    END IF;
END $$
DELIMITER ;

• OLD와 NEW 키워드
  • OLD : 해당 테이블에 변경이 가해지기 전의 레코드
  • NEW : 해당 테이블에 변경이 가해진 후의 레코드