MySQL에서 Create Procedure 설명

1. 생성...

• 구조

    DELIMITER $$
    CREATE PROCEDURE 프로시저명(IN|OUT 변수 변수타입.....)
    BEGIN
       질의어~~~~
    END$$
  

• 예제

    DELIMITER $$
    CREATE PROCEDURE P_TEST(IN in_p INT, OUT out_p VARCHAR(50))
    BEGIN
      SELECT grp_nm
        INTO out_p    
        FROM u_grp_info
       WHERE grp_cd = in_p;
          
    END$$
  

2. 삭제

• 구조

    DROP PROCEDURE 프로시저명 
  

• 예제

    DROP PROCEDURE P_TEST
  

3. 호출

• 구조

    CALL 프로시저명(파라미터);
  

• 예제

    CALL 프로시저명(1, @outParam);
    SELECT @outParam;
  

Stored Procedure 필수 요소들

이런 내장 프로시저를 만드는데는 몇가지 알아야 할 사항이 있는데,

첫번째가 "delimiter"라는 존재입니다. 이 delimiter는 구분자, 즉, 명령러 단락 구역을 만들 수 있게 도와주는 넘입니다.

SQL 구문은 ';' 문자로 끝나게 됩니다. 근데 프로시저 안에서 한줄 끝날때 마다 ;를 다 넣어주면, 어디가 쿼리문이고 어디가 프로시저 문인지를 헛갈리게 됩니다. 따라서 "여기서 여기 까지가 프로시저야" 라는 걸 알려주기 위해서 delimiter를 씁니다.

DELIMITER $$

이렇게 하여서 ';'을 "$$"로 바꾸어 주게 되는데요. 이렇게 하면, 프로시저를 꾸며줄 때 한줄이 끝났을 때 ';'를 써줘도 함수가 끝나지 않았다는 것을 인식 시켜줄 수 있습니다.

그리고 프로시저를 다 꾸미고 나면, 다시 "$$" 를 ';'로 바꾸어줍니다.

두번째로는 파라미터, "인자 값들의 종류" 입니다. 총 3가지(IN, OUT, INOUT)게 있게 되는데요.

1. IN IN 파라미터는 "읽기 전용" 이라는 것입니다. 프로서저를 호출할 때 건네주게 되면, 이를 프로시저 안에서 값을 가져다 쓸뿐 바꿀 수 없다는 것이죠.

2. OUT OUT 파라미터는 "값을 변경 할 수 있는" 속성을 가진 파라미터 입니다. 흔히들 이 파라미터는 프로시저 내부에서 일을 처리한 뒤, 값을 돌려줄때 많이 씁니다.

3. INOUT INOUT 파라미터는 IN의 속성과 OUT의 속성을 다 가지고 있습니다. 프로시저 호출자로 부터 값을 전달 받아 이를 고쳐서 다시 돌려줄때 사용하게 되는 것이죠.

세번째로는 프로시저의 시작과 끝입니다. 프로시저는 BEGIN 으로 시작해서 END로 끝나게 됩니다.

따라서, CREATE PROCEDURE testproc() BEGIN

....

END

이런 형태로 만드셔야 합니다.

MySQL Procedure 만들면서 주의해야 할 사항!

주로 MS-SQL에서 작업을 하다, mysql에서 처음으로 stored procedure(이하 sp)를 만들면서, 5분이면 될 것을 수십분은 걸린 것 같다 --;; 에러가 정확한 위치를 알려주질 않는다. 아래의 정리한 내용은 MS-SQL에 익숙하고 mysql을 사용해 보지 않은 분이 보면 무자게 도움이 될 것 같다.

- IF문을 쓸 때 END IF문이 있어야 하며, END IF문 뒤에는 ;를 붙여야 한다.

   ex> IF (test IS NULL) THEN
            SELSET 'ok';
         END IF;

- auto increment값을 얻어올 때는 LAST_INSERT_ID()을 이용한다.

ex> SET serverId = LAST_INSERT_ID();

- CREATE PROCEDURE문 전에 delimeter문을 이용해 delimeter를 ';'가 아닌 다른 걸로 변경하고 뒤에 다시변경할 것

 ex> delimiter //                  # delimeter를 //로 변경
         CREATE PROCEDURE xxx
                  .....
                  .....
         END;
        //                                # 실제 한 command가 종료됨을 의미
delimeter ;                     # 다시 ;로 변경