注意：調(diào)用使用mybatis-plus3形式調(diào)用，可能會(huì)有些區(qū)別

1. 創(chuàng)建存儲(chǔ)過(guò)程

-- -- 生成員工工號(hào)的存儲(chǔ)過(guò)程
DELIMITER $$
CREATE PROCEDURE generate_employee_number(OUT employeeNumber VARCHAR(20)) -- 解釋 out 一個(gè)返回值
BEGIN
  DECLARE prefix VARCHAR(3) DEFAULT '000'; -- 解釋 準(zhǔn)備一個(gè)固定前綴
  DECLARE next_id INT; -- 解釋 后綴值

  SELECT AUTO_INCREMENT -- 解釋 這里的sql就是 拿到staff表中下一個(gè)自增的值 賦值 給 next_id [前提: staff主鍵是自增情況]
  INTO next_id
  FROM information_schema.tables
  WHERE table_schema = DATABASE()
    AND table_name = 'staff'; 

  SET employeeNumber = CONCAT(prefix, next_id); -- 解釋 賦值給 employeeNumber
END$$
DELIMITER ;

-- 刪除存儲(chǔ)過(guò)程
DROP PROCEDURE generate_employee_number;


CALL generate_employee_number(@employeeNumber); -- 測(cè)試
SELECT @employeeNumber;

SHOW CREATE PROCEDURE generate_employee_number; -- 判斷是否存在

SELECT DATABASE(); -- 判斷當(dāng)前正在操作的數(shù)據(jù)庫(kù)

2.xml調(diào)用存儲(chǔ)過(guò)程

<!--調(diào)用存儲(chǔ)過(guò)程-->
<select id="generateEmployeeNumber" statementType="CALLABLE">
  {call generate_employee_number(
  	#{employeeNumber, mode=OUT, jdbcType=VARCHAR, javaType=java.lang.String}
  )}
</select>

3. mapper類(lèi)

void generateEmployeeNumber(Map<String, Object> result);

4. controller 或 測(cè)試

Map<String, Object> result = new HashMap<>();
result.put("employeeNumber", null);

staffMapper.generateEmployeeNumber(result); // 調(diào)用存儲(chǔ)過(guò)程生成員工編號(hào)

System.out.println("result = " + result.get("employeeNumber"));

... ...