Mysql - 저장 프로시저 생성 및 호출 방법

My SQL 프로시저 생성 방법

DELIMITER //

DROP PROCEDURE IF EXISTS sp_UserInfo_Select_All //

CREATE PROCEDURE sp_UserInfo_Select_All( )

BEGIN

    SELECT *

      FROM UserInfo;

END//

DELIMITER ;

DELIMITER //

DROP PROCEDURE IF EXISTS sp_UserInfo_Select //

CREATE PROCEDURE sp_UserInfo_Select

(

    pi_UserID INT

)

BEGIN

    SELECT *

      FROM UserInfo

     WHERE UserID = pi_UserID;      

END//

DELIMITER ;

DELIMITER //

DROP PROCEDURE IF EXISTS sp_UserInfo_Insert //

CREATE PROCEDURE sp_UserInfo_Insert

(

    pi_UserName VARCHAR(20)

    ,OUT po_UserID INT

)

BEGIN

    INSERT INTO UserInfo (UserName)

    VALUES (pi_UserName);

    -- Auto Increment 값 조회

    SELECT LAST_INSERT_ID()

      INTO po_UserID; -- OUT 파라메터 담기

END//

DELIMITER ;

DELIMITER //

DROP PROCEDURE IF EXISTS sp_UserInfo_Update //

CREATE PROCEDURE sp_UserInfo_Update

(

    pi_UserID INT

    , pi_UserName VARCHAR(20)

)

BEGIN

    UPDATE UserInfo

       SET UserName = pi_UserName

     WHERE UserID = pi_UserID;

END//

DELIMITER ;

DELIMITER //

DROP PROCEDURE IF EXISTS sp_UserInfo_Delete //

CREATE PROCEDURE sp_UserInfo_Delete

(

    pi_UserID INT

)

BEGIN

    DELETE FROM UserInfo

     WHERE UserID = pi_UserID;

END//

DELIMITER ;

프로시저 호출

CALL sp_UserInfo_Select_All();

CALL sp_UserInfo_Select(1);

CALL sp_UserInfo_Insert('Park', @UserID);

SELECT @UserID

CALL sp_UserInfo_Insert('Choi', @UserID);

SELECT @UserID

CALL sp_UserInfo_Insert('bahk', @UserID);

SELECT @UserID

CALL sp_UserInfo_Select(2);

CALL sp_UserInfo_Update(2, 'Choi-Update');

CALL sp_UserInfo_Select(2);

CALL sp_UserInfo_Delete(2);

CALL sp_UserInfo_Select_All();