[MySQL Stored Procedure]Language Fundamentals

Variables
 - Syntax
   DECLARE variable_name[,variable_name..] datatype [DEFAULT value];
 - 값 할당시에는 SET사용
 - 모든 데이터 타입 사용 가능

Literals
 - Numeric literals 가능
   10진수(200), e지수(2e5), 16진수(OxA) 사용 가능
 - Date literals
   SET datetime = '1999-12-31 23:59:59';
 - String literals
   'String', "String"로 표현
   상수안에 '," 사용시 백슬래쉬 사용
   escape문자 사용가능 \t, \n, \\
   ANSI_QUOTES모드일때에는 single quotes만 가능

Parameters - IN
 - Syntax
   CREATE PROCEDURE name ([IN|OUT|INOUT] parameter_name data_type...)
 - IN 파라미터 값을 프로시져 안으로 전달
 - 변경은 가능하지만 리턴은 불가(Call by Value 형태)

Parameters - OUT
 - 프로시져 호출시 return value 지정
 - 값이 반환 될 수는 있지만 프로시져 안으로는 전달이 안됨

Parameters - INOUT
 - 값이 입력, 출력이 가능하다

User Variables
 - Syntax
   SET @name = 'YourName';
 - 세션단위의 사용자 지정 광역변수
 - 동일세션내에서 다른 프로시져에서 호출 가능
 - 관리가 힘들다

Comment
 - '--' 또는 '/* */'으로 사용 가능

Data Types - ENUM
 - Syntax
   CREATE PROCEDURE name (in_option ENUM('Yes','No','Maybe'))
   CALL name('Yes')
  
   CREATE PROCEDURE name (in_option SET('YES','No','Maybe'))
   CALL name('Yes,No')  
 - 다른 DB와는 호환이 되지 않음

Strict Mode
 - 잘못된 값이 컬럼에 삽입되거나 업데이트 될때 Error을 반환
 - STRICT_TRANS_TABLES, STRICT_ALL_TABLES의 차이점
   STRICT_TRANS_TABLES은 트랜잭션 지원하지 않는 테이블에서 두번째 줄에서 오류시 첫번째 줄은 처리됨
   STRICT_ALL_TABLES의은 전체가 무시됨
 - 프로시저 생성시 지정된 sql_mode환경에 따라 동작함(생성후 Mode를 변경해도 프로시져는 반영안됨)
 - 프로시저 작성전에는 Strict모드로 지정하여 잘못된 데이터의 삽입,수정을 막아야함

 

 

[MySQL Stored Procedure]Stored Procedure 개요

Stored Program이란?
 - MySQL5 버전부터 지원
 - ANSI SQL 2003을 기준으로 채택(블럭구조기반)
 - 3가지 타입
   Stored procedure : 가장 일반적인 타임
   Stored functions : 프로시져와 비슷하지만 단일값만 return
   Triggers : 데이터베이스 activity발생시 반응되는 action정의

 

Stored Program 사용이유
 - 데이터베이스 보안 강화
 - 코드의 유지보수를 더욱 쉽게
 - 네트웍 트래픽 감소
 - 어플리케이션의 이동성을 강화
 
Procedure의 기초

 - 기본 샘플

 DELIMITER $$
 DROP PROCEDURE IF EXISTS HellloWorld$$
 CREATE PROCEDURE HelloWorld()
 BEGIN
   SELECT 'Hello World';
 END$$
 DELIMITER ;

 

 - 변수

 BEGIN
   DECLARE my_integer INT;
   DECLARE my_dob     DATE DEFAULT '1960-06-21'
   SET my_integer=20;
 END;

 
 - 파라미터의 모드

 DELIMITER $$
 CREATE PROCEDURE my_sqrt(input_number INT, OUT out_number FLOAT)
 BEGIN
   SET out_number=SQRT(input_number);
 END$$
 DELIMITER ;

 IN - 프로시저의 기본모드, 값을 전달받음
 OUT - 값을 할당할 수 있고 호출할 프로그램에 반환
 INOUT - 값을 전달할 수 있고 변경된 값 역시 확인 가능

 

 - 조건절(IF, CASE)

   IF(price>500) THEN
     SET discount=10
   ELSEIF (price>100) THEN
     SET discount=5
   ELSE
     SET discount=3
   END IF

   
 - LOOP의 사용(WHILE/END WHILE, REPEAT/UNTIL절 가능)

   my_simple_loop : LOOP
     SET counter = counter+1;
   END LOOP my_simple_loop;

 - SELECT INTO(변수에 값을 SELECT로 넣을 경우 사용)

   DECLARE total NUMERIC(8,2);
   SELECT SUM(sale) INTO total
   FROM sales

 

 - Store Function : IN파라미터만 사용가능, OUT, INOUT는 사용 불가, SQL내에서도 호출 가능

 - Trigger : DML문에 의하여 테이블이 변경되었을때만 실행됨