SQL Server 使用 MyBatis Plus 创建存储过程并循环插入数据 - 获取插入记录 ID

假设有以下表：\n\nsql\nCREATE TABLE `user` (\n `id` INT NOT NULL AUTO_INCREMENT,\n `name` VARCHAR(50) NOT NULL,\n PRIMARY KEY (`id`)\n);\n\n\n首先，在 SQL Server 中创建一个存储过程，使用 CURSOR 循环插入数据，并使用 OUTPUT 参数返回插入记录的 ID 值：\n\nsql\nCREATE PROCEDURE `insert_users` \n @names VARCHAR(MAX),\n @ids VARCHAR(MAX) OUTPUT\nAS\nBEGIN\n DECLARE @name VARCHAR(50);\n DECLARE @id INT;\n DECLARE @cursor CURSOR;\n \n SET @ids = '';\n \n SET @cursor = CURSOR FOR\n SELECT value FROM STRING_SPLIT(@names, ',');\n \n OPEN @cursor;\n \n FETCH NEXT FROM @cursor INTO @name;\n \n WHILE @@FETCH_STATUS = 0\n BEGIN\n INSERT INTO `user` (`name`) VALUES (@name);\n SET @id = SCOPE_IDENTITY();\n SET @ids = CONCAT(@ids, CAST(@id AS VARCHAR), ',');\n FETCH NEXT FROM @cursor INTO @name;\n END\n \n CLOSE @cursor;\n DEALLOCATE @cursor;\n \n SET @ids = LEFT(@ids, LEN(@ids) - 1);\nEND\n\n\n然后在 MybatisPlus 中调用存储过程，使用 Mapper 接口的 @Select 注解指定调用存储过程的 SQL 语句：\n\njava\npublic interface UserMapper extends BaseMapper<User> {\n @Select("EXEC insert_users @names=#{names}, @ids=#{ids, mode=OUT, jdbcType=VARCHAR}")\n void insertUsers(@Param("names") String names, @Param("ids") String[] ids);\n}\n\n\n最后在代码中调用 insertUsers 方法即可插入数据并获取每条数据的 ID 值：\n\njava\n@Autowired\nprivate UserMapper userMapper;\n\npublic void testInsertUsers() {\n String names = "张三,李四,王五";\n String[] ids = new String[1];\n userMapper.insertUsers(names, ids);\n System.out.println(Arrays.toString(ids[0].split(",")));\n}\n