데이터베이스 설계 및 구현 - [제10강] 저장 프로그램 1

데이터베이스 설계 및 구현 - [제10강] 저장 프로그램 1

저장 프로그램 ․저장 프로시저

데이터베이스 용어정리

저장 프로그램

SQL 문을 절차적으로 처리하기 위한 데이터베이스 객체

저장 프로시저

여러 가지 응용작업 중 자주 사용되거나 복잡한 과정을 거치는 SQL 문을 저장하여 관리되는 객체

조건문

특정 그룹의 명령문을 조건에 따라 실행 여부를 결정하는 명령문으로 MySQL은 IF 문과 CASE 문을 제공

반복문

특정 그룹의 명령문을 조건에 따라 특정 횟수만큼 반복하는 명령문으로 MySQL은 WHILE, REPEAT, LOOP 반복문을 제공

요점정리

1. 저장 프로그램은 저장 루틴(stored routine)으로 불리는 저장 프로시저(stored procedure)와 함수(function), 트리거(trigger) 등을 모두 포함하는 명칭이다. 저장 프로그램은 고급 프로그래밍 언어에서 지원하는 작업의 흐름을 제어할 수 있는 제어 명령과 변수사용도 지원한다.
2.  MySQL에서는 저장 프로그램 뿐만 아니라, SQL 문에서도 사용할 수 있는 사용자 정의변수(세션변수)와 저장 프로그램 내에서만 사용할 수 있는 로컬 변수가 있다. MySQL의 사용자 정의변수 이름은 ‘@’으로 시작한다. 로컬변수는 저장 프로시저 내에서 선언되며 반드시 DECLARE 명령으로 선언되어야 하며, 데이터 타입도 함께 명시되어야 한다.
3. 여러 가지 응용작업 중 자주 사용되거나 복잡한 과정을 거치는 SQL 문을 저장하여 하나의 개체로 관리하는 것이 저장 프로시저이다.
4. 저장 프로시저는 실행속도의 효율성, 모듈화 기능, 네트워크 전송효율 그리고 보안설정 면에서 효율적이다.
5. MySQL에서는 IN, OUT, INOUT 세 타입의 매개변수를 제공한다. 저장 프로지서를 생성할 때 매개변수는 기본적으로 IN 타입으로 설정되며, 다른 타입으로 지정하고 싶을 경우 매개변수명 앞에 OUT이나 INOUT을 사용한다.
6. 저장 프로그램에서는 IF 문을 통해 주어진 조건이나 식의 값에 따라 실행되는 SQL 문을 제어할 수 있다. CASE 문은 다중 IF ELSE 문을 간편하게 사용하기 위한 명령어로 조건식이 여러 개일 때 유용하게 사용된다.
7. MySQL은 특정 SQL 문을 주어진 조건이 만족하는 동안 반복하기 위해 WHILE, REPEAT, LOOP 문을 제공한다. WHILE 문은 조건식일 참일 동안 DO와 END WHILE 사이의 SQL 문을 반복한다. REPEAT 문은 UNTIL 조건식이 거짓인 동안 REPEAT와 UNTIL 사이에 있는 SQL 문을 반복하며, LOOP 문은 레이블을 이용한 반복할 블록을 생성 후, LEAVE 문을 통해 LOOP 문이 종료될 때까지 반복한다.

연습문제

• 

  다음 중 저장 프로그램을 사용하는 장점이라고 할 수 없는 것은?

  답을 체크하세요

  1. 네트워크 전송효율 향상
  2. 실행속도의 효율성 향상
  3. 보안 향상
  4. 디버깅 기능 사용

  정답 :

  ④

  저장 프로그램은 통합개발환경을 제공하는 일반적인 프로그래밍 언어에 비해 개발환경이 열악하다. MySQL을 포함한 일반적은 DBMS에서 저장 프로그램에 대한 디버깅 기능을 지원하고 있지 않다.

• 

  저장 프로시저 생성 시 문장 구분자를 변경하기 위해 사용되는 명령어는?

  답을 체크하세요

  1. DELIMITER
  2. BEGIN WITH
  3. END WITH
  4. DECLARE

  정답 :

  ①

  저장 프로지서 생성 시 CREATE PROCEDURE 구분의 끝과 CREATE PROCEDURE 내부의 SQL 문의 끝을 구분하기 위해서 문장 구분자를 변경해야한다. 문장 구분자는 ‘DELIMITER 구분자’ 형식을 사용하여 변경한다.

• 

  저장 프로시저를 호출 할 때 사용하는 구문은?

  답을 체크하세요

  1. SET 프로시저명
  2. SELECT 프로시저명
  3. CALL 프로시저명
  4. EXEC 프로시저명

  정답 :

  ③

  저장 프로시저는 독립적으로 실행 가능한 데이터베이스 객체로 CALL 명령어를 사용한다. 저장 프로시저와 유사한 함수는 독립적으로 실행이 불가능한 객체이기 때문에 SELECT 명령어에 포함시켜 실행한다.

• 

  다음은 count 변수의 값이 1이면 result 변수에 '홀수'를 1이 아니면 '짝수'를 대입하는 저장 프로시저 정의 구문이다.
  빈칸에 알맞은 명령어로 짝지어 진 것은?

  (     가     ) COUNT = 1 THEN
  SET result = ‘홀수’;
  (     나     )
  SET result = ‘짝수’;
  END IF;

  답을 체크하세요

  1. IF - ELSEIF
  2. CASE - WHEN
  3. IF - CASE
  4. IF - ELSE

  정답 :

  ④

  MySQL에서 제공되는 조건문은 IF 문과 CASE 문이다. IF 문은 한 개의 조건은 IF-ELSE 문으로 여러 개의 조건은 IF-ELSEIF-ELSE 문으로 기술할 수 있으며, CASE 문은 CASE-WHEN 문으로 조건을 기술할 수 있다. 문제에서는 조건(COUNT = 1)이 한 개이므로 IF-ELSE가 사용되어야 한다.

• 

  다음과 같이 정의된 저장 프로시저에 매개변수에 3, @result를 사용하여 실행 시 @result에 저장되는 값은?

  DELIMITER $$
  CREATE PROCEDURE Test5Proc(
  IN input INT(4),
  OUT result INT(4) )
  BEGIN
  DECLARE x INT;
  SET x = 1;
  SET result = 0;
  WHILE x <= input DO
  SET result = result + 3 * x;
  SET x = x + 1;
  END WHILE;
  END $$
  DELIMITER ;

  답을 체크하세요

  1. 9
  2. 18
  3. 27
  4. 64

  정답 :

  ②

  Test5Proc는 두 매개변수를 입력받는다. 두 번째 매개변수 result에는 WHILE 문에서 x의 값에 3이 계속 곱해져 result에 더해지고 반복할 때마다 x의 값을 1씩 증가된다. 따라서 result에는 3*1, 3*2, 3*3의 값이 누적된다.

• 

  다음은 LOOP 문을 사용하여 저장 프로시저를 정의하는 구문이다. 빈칸 가, 나에 알맞은 명령어로 짝지어 진 것은?

  DELIMITER $$
  CREATE PROCEDURE Test6Proc(
  IN p_num INT(4),
  OUT p_sum INT(4) )
  BEGIN
  DECLARE x INT;
  SET x=1;
  SET p_sum=0;
  loop_label：(     가     )
  IF x > p_num THEN
  (     나     ) loop_label;
  END IF;
  SET p_sum=p_sum+x;
  SET x=x+1;
  END LOOP;
  END$$
  DELIMITER ;

  답을 체크하세요

  1. LOOP - LEAVE 
  2. REPEAT - UNTIL
  3. WHILE – DO
  4. WHILE – LEAVE

  정답 :

  ①

  제시된 저장 프로시저는 LOOP 반복문을 사용한다. LOOP 문은 레이블을 이용한 반복할 블록을 생성 후, LEAVE 문을 통해 LOOP 문이 종료될 때까지 반복한다.