給一個簡單的例子給你
DROP PROCEDURE IF EXISTS insertStudents;
CREATE PROCEDURE insertStudents(IN P_SNAME VARCHAR(20))
BEGIN
DECLARE V_STUID INT DEFAULT 0;
SET @V_BIRTHDAY ='';
SET @NAME=P_SNAME;
SET @V_BIRTHDAY=date_format(NOW(), '%Y%m%d%H%i%s');
SELECT MAX(STUID) INTO V_STUID FROM Students;
if V_STUID is null then
set V_STUID=1;
else
SET V_STUID=V_STUID+1;
end if;
SET @ID=cast(V_STUID AS CHAR);-- 將數字轉化為字符
-- SET @ID=CONCAT(V_STUID,''); -- 將數字轉化為字符
SET @INSERTSQL=CONCAT('INSERT INTO Students(STUID,SNAME,BIRTHDAY) VALUES(?,?,?)');
start transaction;
PREPARE stmtinsert FROM @INSERTSQL;
EXECUTE stmtinsert USING @ID,@NAME,@V_BIRTHDAY;

savepoint tran_a;
SET V_STUID=V_STUID+1;
SET @ID=cast(V_STUID AS CHAR);-- 將數字轉化為字符
EXECUTE stmtinsert USING @ID,@NAME,@V_BIRTHDAY;
DEALLOCATE PREPARE stmtinsert;
rollback to tran_a;
commit;
END;