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();
