💡해당 게시글은 방송통신대학교 정재화 교수님의 '데이터베이스 시스템' 강의를 개인 공부 목적으로 메모하였습니다.

학습 개요

SQL은 비절차적 언어로 필요한 정보에 대한 조건만 기술할 뿐 구체적으로 처리 과정을 기술하지 못함
보다 데이터에 대한 절차적 처리가 가능하도록 SQL에 프로그래밍 언어와 유사하게 조건문이나 반복문과 같은 기능을 추가되어 데이터베이스 내에 하나의 객체로 저장되어 관리되는 응용 프로그램과 같은 기능이 제공되는데, 이를 저장 객체라고 함
저장 객체의 종류로는 저장 프로시저, 함수, 트리거 등이 있으며 저장 객체의 기능을 정의하기 위한 Transact-SQL이라는 확장된 언어를 사용하여 세세한 기능을 정의함
저장 프로시저와 함수의 명령문과 변수들을 이용한 여러 가지 사용 예와 트리거의 동작에 대해 학습함

주요 용어

저장 객체
- 데이터베이스 내에 하나의 객체로 저장되어 관리되는 응용 프로그램과 같은 기능을 제공하는 SQL의 확장 기능
저장 프로시저
- 자주 사용되거나 복잡한 SQL 문을 저장하여 하나의 개체로 관리하는 저장 객체의 한 형태
함수
- SELECT문에 포함되어 특정 값을 반환하는 저장 객체의 한 형태
트리거
- 테이블에 INSERT, UPDATE, DELETE등의 이벤트가 발생했을 때 자동으로 실행되는 저장 객체
매개 변수
- 저장 객체에 값을 전달하거나 값을 반환 받기 위한 IN, OUT, INOUT타입의 변수

강의록

저장 프로시저

데이터베이스 언어의 특징

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

저장 객체의 이해

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

저장 객체의 구조

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

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

반복되는 작업 과정을 한데 담아냄으로써 효율성을 높임
- 저장 프로시저
- 함수
- 트리거

저장 객체의 장단점

저장 객체의 장점
- 네트워크 전송 효율
  - 클라이언트와 DBMS 사이에 트래픽 감소
    - 오가는 데이터의 양을 줄일 수 있음
- 효율적 실행 속도
  - 저장 객체는 컴파일되어 캐시에 저장
- 모듈화
  - 대규모 작업의 시간 단축, 프로그램 크기의 축소
    - 여러 단계에 나눠 처리해야 할 SQL 문을 하나의 객체로 처리
- 보안성 향상
  - 사용자 별, 작업 단위별로 권한 부여 가능
저장 객체의 단점
- 처리 성능 저하
  - 서버 메모리 사용량이 증가로 인한 성능 저하
- 어려운 디버깅
  - 저장 객체를 위한 디버깅 환경 미지원
    - 통합 개발 환경(IDE; Integrated Development Environment)
      - 프로그램 개발에 관련된 모든 작업을 하나의 프로그램 안에서 처리하는 환경을 제공하는 소프트웨어

저장 객체의 종류

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

저장 프로시저의 이해

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

저장 프로시저의 생성

생성 구문 형식

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

매개 변수 정의

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

매개 변수 기본 값 IN

저장 프로시저의 활용 1

저장 프로시저 생성

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

프로시저를 정의하는 것도, DELIMITER도 작성하는 것도 중요
만든 프로시저는 바로 실행되지 않고 DBMS 내부 어딘가에 저장됨

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

저장 프로시저의 호출

호출 구문 형식

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

  
  CALL 저장 프로시저명[()];

ex) 호출

  
  CALL GetStudentByGender();

학생번호	학생이름	성별	생년월일	나이	전화번호
201831-331215	김마리아	여	1991-06-18	29	010-0000-0002
201834-021216	유관순	여	1902-12-16	118	010-0000-0001
201926-880215	지청천	여	1988-02-15	32	010-0000-0005

성별	COUNT(*)
여	5
남	7

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

저장 프로시저의 수정
- ALTER PROCEDURE명령어를 사용
  - 보안 및 동작 방식 속성 제외 실제 ALTER문을 통해 변경할 수 있는 것이 거의 없음
- 보안 및 동작 방식에 대한 측면을 변경할 때만 사용
- 저장 프로시저 생성 → 모든 객체 정보 동일
  - 함수, 트리거도 동일

저장 프로시저의 삭제

  
  DROP PROCEDURE 저장 프로시저명;

  
  DROP PROCEDURE GetStudentByGender;

매개 변수의 사용

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

매개 변수의 활용 - `IN`

  
DELIMITER $$
CREATE PROCEDURE GetBalancebyNameBirth(
    IN name VARCHAR(20), IN birth DATE) -- **두 매개 변수를 프로시저 외부**로부터 받아들임
BEGIN
    SELECT 잔액 FROM 학생, 계좌
        WHERE 학생.학생번호 = 계좌.학생번호 AND
            학생.학생이름 = name AND 학생.생년월일 = birth;
END $$ -- 위에서 받은 학생 이름과 생년월일을 토대로 해당 학생의 계좌에서 잔액을 조회
DELIMITER;

  
CALL GetBalancebyNameBirth('안중근', '1979-09-02'); -- 매개 변수화 시켜서 프로시저에 전달

잔액
100000

매개 변수의 활용 - `OUT`

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

  
CALL GetPhoneByStudentID('202034-596541', @phone);
SELECT @phone; -- 화면에 출력

@phone
010-0000-0008

변수의 사용

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

변수 값 할당

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

SET명령

  
  DECLARE total_count INT DEFAULT 0;
  SET total_count = 10;

SELECT INTO문

숫자 타입만 가능

  
  DECLARE total_student INT DEFAULT 0;
  SELECT COUNT(*) INTO total_student FROM 학생; -- COUNT(*) 값이 total_student에 저장

SQL 문의 실행 결과를 특정 변수에 넣어준다는 의미

선택 구조 - `IF`

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

구문 형식

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

`IF`문의 활용

학생 번호에 해당하는 학생의 이수 학점을 가져 온 후, 이수 학점에 따라 학년(nGrade)을 추출하고자 하는 프로시저

  
  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 ;

입력(IN) 및 출력(OUT) 매개 변수
- sid
- nGrade
로컬 변수 선언
- nTotalCredit
  - 특정 학생의 총 이수 학점을 저장하는 변수
학년 구분 조건
1. 4학년: 총 이수 학점(nTotalCredit)이 120 이상인 경우
2. 3학년: 이수 학점이 81 이상 119 이하인 경우
3. 2학년: 이수 학점이 41 이상 80 이하인 경우
4. 1학년: 이수 학점이 40 이하인 경우

  
  CALL GetGradeByCredit('202036-590930', @grade);
  SELECT @grade

위 호출에서 학생 202036-590930의 학년 결과가 계산되어 @grade라는 변수로 반환
@grade
4

@grade
4

선택 구조 - `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;

`CASE`문의 활용

수강 신청 결과(신청자 수)에 따라 강의실을 배정하는 프로시저

  
  DELIMITER $$
  CREATE PROCEDURE GetRoomSize(
      IN sClassCode CHAR(5), -- 과목코드
      OUT sClassSize VARCHAR(20)) -- 강의실 규모
  BEGIN
      DECLARE nClassVolumn INT; -- 신청자수
      SELECT COUNT(*) INTO nClassVolumn FROM 수강
      WHERE 과목코드 = sClassCode;
    
      CASE
          WHEN nClassVolumn > 4 THEN
              SET sClassSize = '대 강의실';
          WHEN (nClassVolumn > 2 AND nClassVolumn < 4) THEN
              SET sClassSize = '중 강의실';
          ELSE
              SET sClassSize = '소 강의실';
      END CASE;
  END $$
  DELIMITER ;

특정 과목 코드(sClassCode)를 입력받아 해당 과목 신청자 수(nClassVolumn)를 기반으로 강의실 규모(sClassSize)를 판단하는 프로시저
입력(IN) 및 출력(OUT) 매개 변수
- sClassCode
- sClassSize
로컬 변수 선언
- nClassVolumn
  - 특정 과목의 전체 신청자 수를 저장하는 변수
강의실 규모 구분 조건
1. 대 강의실: 신청자 수가 4명 초과인 경우
2. 중 강의실: 신청자 수가 3명인 경우
3. 소 강의실: 신청자 수가 2명 이하인 경우

  
  CALL GetRoomSize('COM12',@size);
  SELECT @size;

COM12 과목의 신청자 수를 확인하여 강의실 규모 결과(소 강의실, 중 강의실, 또는 대 강의실)를 반환
반환 값은 @size 변수에 저장
@size
대 강의실

@size
대 강의실

반복 구조

MySQL은 특정 SQL문을 주어진 조건이 만족하는 동안 특정 구간을 반복 시킬 수 있는 세가지 구문을 제공
- WHILE
- REPEAT
- LOOP
반복문 안에서 반복 작업을 제어하기 위해 LEAVE와 ITERATE문을 사용
- LEAVE는 반복 종료
  - break;와 동일
- ITERATE는 현재 반복 구간을 중단하고 조건을 재검사하여 반복 여부를 판별
  - continue;와 동일

`WHILE`문의 활용

특정 과목(sCourseCode)이 가진 선수 과목을 재귀적으로 탐색하여 최종적으로 학생이 반드시 들어야 할 “최초의 선수 과목”(sPrerequisite)을 반환하는 프로시저

  
  DELIMITER $$
  CREATE PROCEDURE GetFirstCourse(
      IN sCourseCode CHAR(5), OUT sPrerequisite CHAR(5))
  BEGIN
      DECLARE sInputCourse CHAR(5);
      SET sInputCourse = sCourseCode;
      pre: WHILE true DO -- 시작 특정 지점을 나타내는 구간
          SELECT 선수과목 INTO sPrerequisite FROM 과목
          WHERE 과목코드 = sCourseCode;
          IF sPrerequisite IS NOT NULL THEN
              SET sCourseCode = sPrerequisite;
          ELSE
              LEAVE pre;
          END IF;
      END WHILE;
      IF sCourseCode = sInputCourse THEN
          SET sPrerequisite = NULL;
      ELSE
          SET sPrerequisite = sCourseCode;
      END IF;
  END$$
  DELIMITER ;

입력(IN) 및 출력(OUT) 매개 변수
- sCourseCode
- sPrerequisite
로컬 변수 선언
- sInputCourse
  - 최초 입력된 과목 코드를 저장
WHILE 루프 구조
- 조건
  - 선수 과목(sPrerequisite)이 있을 경우, 해당 과목으로 재설정(sCourseCode = sPrerequisite)하고 다시 탐색
- 종료 조건
  - 더 이상 선수 과목이 없으면 루프를 빠져 나감
결과 확인
- 만약 입력 과목 코드(sInputCourse)와 최종적으로 도달한 과목 코드(sCourseCode)가 동일하면 선수 과목이 없음을 의미하므로, NULL로 반환

  
  CALL GetFirstCourse('COM31', @code);
  SELECT @code;

최종적인 선수 과목은 @code 변수에 저장
만약 선수 과목이 없다면 결과는 NULL로 반환

@code
COM12

함수

함수의 이해

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

함수의 생성

구문 형식
1 2 3 4 5 6 7 8 CREATE [DEFINER {사용자 | 현재사용자}] FUNCTION 함수명([매개 변수[, ...]]) RETURNS 데이터 타입 [Characteristic] BEGIN SQL문 END
- 프로시저의 구문 형식과 매우 유사
- 매개 변수
  - IN타입의 매개 변수만 사용 가능
    - OUT매개 변수가 없음
- Characteristic
  - SQL 문의 특성을 기술
  - NO SQL
    - 실행되는 SQL문이 없을 경우
  - READ SQL DATA
    - SELECT만 하는 경우
  - MODIFIES SQL DATA
    - INSERT, UPDATE, DELETE사용할 경우

함수의 생성

계좌 잔액에 따라 다르게 적용되는 이율(fIR)을 반환해 주는 함수

  
  DELIMITER $$
  CREATE FUNCTION GetInterestRate(nBalance INT)
      RETURNS DECIMAL(4, 2)
      READS SQL DATA
  BEGIN
      DECLARE fIR DECIMAL(4, 2); -- DECIMAL(전체 숫자의 자릿 수, 정수의 자릿 수)
      IF nBalance >= 500000 THEN
          SET fIR = 0.03;
      ELSEIF nBalance >= 100000 AND nBalance < 500000 THEN
          SET fIR = 0.02;
      ELSEIF nBalance < 100000 THEN
          SET fIR = 0.01;
      ELSE
          SET fIR = 0.0;
      END IF;
      RETURN (fIR);
  END$$
  DELIMITER;

함수의 활용

  
SELECT 학생번호, 계좌번호, 잔액, GetInterestRate(잔액) AS 이율 -- 앞에서 정의한 함수
    FROM 계좌
    WHERE 잔액 >= 300000;

학생번호	계좌번호	잔액
201831-331215	123434-222222	800000
201834-021216	123434-111111	600000
201926-880215	123434-555555	300000
201931-781109	123434-444444	400000
201934-080621	123434-777777	300000
201934-790902	123434-666666	100000
202034-596541	123434-888888	1200000
202031-816515	123434-999999	150000

학생번호	계좌번호	잔액	이율
201831-331215	123434-222222	800000	0.03
201834-021216	123434-111111	600000	0.03
201978-610408	123434-333333	400000	0.02
201931-781109	123434-444444	400000	0.02
201926-880215	123434-555555	300000	0.02
201934-080621	123434-777777	300000	0.02
202034-596541	123434-888888	1200000	0.03

함수는 별도 라인 별로 적용, 결괏 값은 컬럼으로 나타남

트리거

트리거의 이해

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

트리거의 종류

트리거 이벤트와 트리거 시점으로 구분
| 트리거 이벤트 | 실행 시점 | 기능 | | — | — | — | | INSERT | BEFORE | 테이블에 데이터가 입력되기 전에 실행 | | | AFTER | 테이블에 데이터가 입력된 후에 실행 | | UPDATE | BEFORE | 테이블의 데이터가 수정되기 전에 실행 | | | AFTER | 테이블의 데이터가 수정된 후에 실행 | | DELETE | BEFORE | 테이블의 데이터가 삭제되기 전에 실행 | | | AFTER | 테이블의 데이터가 삭제된 후에 실행 |
- 트리거 이벤트
  - 자동으로 반응하는 SQL 문
- 트리거 시점
  - 트리거가 실행될 시점을 명시

트리거의 생성

구문 형식

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

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

트리거의 활용

과목테이블의 학점이 업데이트될 때, 학점 값이 1 미만이거나 3 초과인 경우 각각 1 또는 3으로 강제 조정되도록 하는 트리거

  
  DELIMITER $$
  CREATE TRIGGER before_과목_update
  	BEFORE UPDATE ON 과목 -- 과목 테이블이 업데이트 하기 전
  	FOR EACH ROW
  BEGIN
    IF NEW.학점 < 1 THEN -- 새롭게 바뀔 학점(NEW 학점)
      SET NEW.학점 = 1; -- NEW 레코드가 과목 테이블에 반영
    ELSEIF NEW.학점 > 3 THEN
      SET NEW.학점 = 3;
    END IF;
  END $$
  DELIMITER ;

  
  -- 과목 테이블에서 과목코드가 'COM34'인 행의 학점을 5로 업데이트하려고 시도
  UPDATE 과목
  	SET 학점 = 5
  	WHERE 과목코드 = 'COM34';
    
  -- 학생 테이블의 모든 데이터를 조회
  SELECT * FROM 학생;

트리거 조건에 의해 NEW.학점의 값은 3으로 변경되고, 실제 과목테이블에는 학점이 5가 아닌 3으로 업데이트

과목코드	과목명	학점	선수과목	이수구분	교수번호
COM11	컴퓨터의 이해	3		교양	…
COM12	파이썬 프로그래밍 기초	3		교양	…
COM24	자료구조	3	COM12	전공필수	…
COM31	데이터베이스 시스템	3	COM24	전공필수	…
COM34	알고리즘	3	COM24	일반선택	…
COM44	클라우드 컴퓨팅	3		전공필수	…
ECE24	놀이지도	3		전공필수	…
ECE31	유아언어교육	3	ECE31	전공필수	…

연습 문제

다음 중 저장 객체에 포함되지 않는 것은?
a. 인덱스
- 저장 객체는 저장 프로시저, 함수, 트리거 등을 포함하지만, 인덱스는 테이블의 검색 성능을 향상 시키기 위한 데이터베이스 객체임
다음 중 매개 변수 타입이 아닌 것은?
a. RETURN
- 매개 변수 타입은 IN, OUT, INOUT세 가지가 있고, RETURN은 매개 변수 타입이 아닌 함수에서 값을 반환하는 명령어임
저장 프로시저와 함수의 차이점으로 올바른 것은?
a. 함수는 RETURN문으로 값을 반환하지만, 저장 프로시저는 RETURN문으로 값을 반환하지 않는다
- 함수는 RETURN문을 통해 특정 값을 반환하는 반면, 저장 프로시저는 값을 반환하지 않거나 OUT/INOUT매개 변수를 통해 값을 반환함
- 함수는 SELECT문에 포함되어 사용되지만, 저장 프로시저는 CALL명령으로 독립적으로 실행 됨

정리 하기

저장 객체는 비절차적 SQL 언어의 한계를 보완하여 데이터에 대한 절차적 처리가 가능하도록 개발된 데이터베이스 내 저장되는 프로그래밍 요소로, 저장 프로시저, 함수, 트리거 등이 있음
저장 객체는 네트워크 전송 효율, 실행 속도의 효율성, 모듈화 기능, 보안 향상 등의 장점이 있으나, 낮은 처리 성능과 디버깅의 어려움이라는 단점도 있음
MySQL에서 변수는 세션 전체에서 사용 가능한 사용자 정의 변수(@로 시작)와 저장 객체 내에서만 사용 가능한 로컬 변수(DECLARE로 선언)로 구분 됨
저장 프로시저는 CREATE PROCEDURE로 생성되며, 매개 변수를 통해 값을 전달 받거나 반환할 수 있음
- 매개 변수는 IN, OUT, INOUT세 가지 타입이 있으며, CALL명령으로 호출 됨
저장 객체에서는 IF-THEN, CASE WHEN같은 조건문과 WHILE, REPEAT, LOOP같은 반복문을 사용할 수 있으며, 이를 통해 복잡한 업무 로직 구현이 가능함
함수는 저장 프로시저와 유사하지만 RETURN문을 통해 특정 값을 반환하며, SELECT문에 포함되어 실행된다는 점에서 차이가 있음
트리거는 테이블에 INSERT, UPDATE, DELETE이벤트 발생 시 자동으로 실행되는 특별한 저장 객체로, 데이터베이스의 무결성과 일관성을 유지하는 데 활용 됨
MySQL에서는 BEFORE/AFTER와 INSERT/UPDATE/DELETE조합으로 총 여섯 가지 트리거 유형을 제공하며, OLD와 NEW키워드를 통해 변경 전후의 데이터 값을 참조할 수 있음
저장 객체를 수정할 때는 일반적으로 DROP명령으로 객체를 삭제한 후 CREATE명령으로 다시 생성해야 하며, ALTER명령은 보안 및 동작 방식 속성 변경에만 사용 됨
MySQL 워크 벤치에서는 GUI 방식으로 저장 객체를 편리하게 생성, 수정, 삭제할 수 있어 개발 생산성을 높일 수 있음

체크 포인트

저장 객체에 대한 설명으로 가장 적절하지 않은 것은?

a. 저장, 프로시저 함수, 트리거는 비절차적 언어로 작성된다.

 - 비절차적 언어는 목표만 있고 중간 과정이 없는 언어  - 저장 객체에 대한 설명으로 적절한 것
 - 데이터베이스 시스템의 성능을 개선한다.
 - 트리거에서 `COMMIT`, `ROLLBACK`을 사용할 수 없다.
 - 함수는 반드시 리턴이 있다.
 - 저장, 프로시저 함수, 트리거는 절차적 언어로 작성된다.

테이블 R(A, B, C, D, E)와 S(E, F, G)에 대해 두 릴레이션을 요약하는 별도의 테이블 RS(A, B, E, G)를 생성하였다. 테이블의 R의 B 속성에 대한 갱신 연산이 발생할 경우 RS에 전파하는 트리거를 작성하려고 할 때 빈 칸에 알맞은 것이 순서대로 나열 된 것은?
1 2 3 4 5 6 7 8 CREATE TRIGGER After_Update_On_R_B ALTER UPDATE B ON R FOR EACH ROW BEGIN UPDATE RS SET B = _____ WHERE RS.A = _____ AND RS.E = _____**; END;**
a. NEW.B, OLD.A, OLD.E
- R에 업데이트가 일어났을 때 B의 값을 R의 Key인 A와 E 값이 일치하는 경우만 변경

[데이터베이스 시스템] 9강 - 저장 객체

학습 개요

주요 용어

강의록

저장 프로시저

데이터베이스 언어의 특징

저장 객체의 이해

저장 객체의 장단점

저장 객체의 종류

저장 프로시저의 이해

저장 프로시저의 생성

저장 프로시저의 활용 1

저장 프로시저의 호출

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

매개 변수의 사용

매개 변수의 활용 - IN

매개 변수의 활용 - OUT

변수의 사용

변수 값 할당

선택 구조 - IF

IF문의 활용

선택 구조 - CASE

CASE문의 활용

반복 구조

WHILE문의 활용

함수

함수의 이해

함수의 생성

함수의 생성

함수의 활용

트리거

트리거의 이해

트리거의 종류

트리거의 생성

트리거의 활용

연습 문제

정리 하기

체크 포인트

Further Reading

[데이터베이스 시스템] 2강 - 데이터베이스 모델링

[데이터베이스 시스템] 3강 - 관계형 모델

[데이터베이스 시스템] 4강 - SQL

매개 변수의 활용 - `IN`

매개 변수의 활용 - `OUT`

선택 구조 - `IF`

`IF`문의 활용

선택 구조 - `CASE`

`CASE`문의 활용

`WHILE`문의 활용