问题如下:有没有办法令insert语句不返回结果集?
存储过程:
DELIMITER $$
CREATE PROCEDURE `ztedu`.`getResource`(IN id INT)
BEGIN
DECLARE _done INT DEFAULT 0;
DECLARE tmp_resId BIGINT; /*资源ID*/
DECLARE tmp_resTypeName VARCHAR(20); /*资源类型1(视频,动漫)*/
DECLARE tmp_resName VARCHAR(100);/*资源名称*/
DECLARE tmp_resUrl VARCHAR(200); /*资源类型URL*/
DECLARE tmp_resCType INT; /*资源类型2(资源库,习题库,自主资源库)*/
DECLARE tmp_fileType VARCHAR(20); /*文件类型*/
DECLARE num INT DEFAULT 0;
DECLARE upfileRes CURSOR FOR SELECT tur_resType,tur_res_id FROM tch_c_upfile_res WHERE tur_cu_id = id ORDER BY tur_order DESC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_res_table(resId BIGINT,
resTypeName VARCHAR(20),
resName VARCHAR(100),
resUrl VARCHAR(200),
resCType INT,
fileType VARCHAR(20));
TRUNCATE TABLE tmp_res_table;
OPEN upfileRes;
/* 循环执行 */
REPEAT
FETCH upfileRes INTO tmp_resCType,tmp_resId;
IF NOT _done THEN
IF tmp_resCType=0 THEN
SELECT r.`er_name`,r.`er_fileUrl`,t.`sgrt_name`,f.`sgft_name`
INTO tmp_resName,tmp_resUrl,tmp_resTypeName,tmp_fileType
FROM `sys_edures` r
LEFT JOIN `sys_g_restype` t ON r.`sgrt_id`=t.`sgrt_id`
LEFT JOIN `sys_g_filetype` f ON r.`er_fileType`=f.`sgft_id`
WHERE r.`er_id`=tmp_resId;
INSERT INTO tmp_res_table (resId,resTypeName,resName,resUrl,resCType,fileType)
VALUES(tmp_resId,tmp_resTypeName,tmp_resName,tmp_resUrl,tmp_resCType,tmp_fileType);
ELSEIF tmp_resCType=1 THEN
SELECT p.tp_name INTO tmp_resName
FROM tch_p_papers p
WHERE p.tp_id = tmp_resId;
INSERT INTO tmp_res_table (resId,resTypeName,resName,resUrl,resCType,fileType)
VALUES(tmp_resId,"习题",tmp_resName,NULL,tmp_resCType,NULL);
ELSEIF tmp_resCType=2 THEN
SELECT r.`smr_name`,r.`smr_fileUrl`,t.`sgrt_name`,f.`sgft_name`
INTO tmp_resName,tmp_resUrl,tmp_resTypeName,tmp_fileType
FROM `stu_myres` r
LEFT JOIN `sys_g_restype` t ON r.`sgrt_id`=t.`sgrt_id`
LEFT JOIN `sys_g_filetype` f ON r.`smr_fileType`=f.`sgft_id`
WHERE r.`smr_id`=tmp_resId;
INSERT INTO tmp_res_table (resId,resTypeName,resName,resUrl,resCType,fileType)
VALUES(tmp_resId,tmp_resTypeName,tmp_resName,tmp_resUrl,tmp_resCType,tmp_fileType);
END IF;
END IF;
SELECT * FROM tmp_res_table;
UNTIL _done END REPEAT;
CLOSE upfileRes;
END$$
DELIMITER ;
执行结果:
insert存储过程结果集
存储过程:
DELIMITER $$
CREATE PROCEDURE `ztedu`.`getResource`(IN id INT)
BEGIN
DECLARE _done INT DEFAULT 0;
DECLARE tmp_resId BIGINT; /*资源ID*/
DECLARE tmp_resTypeName VARCHAR(20); /*资源类型1(视频,动漫)*/
DECLARE tmp_resName VARCHAR(100);/*资源名称*/
DECLARE tmp_resUrl VARCHAR(200); /*资源类型URL*/
DECLARE tmp_resCType INT; /*资源类型2(资源库,习题库,自主资源库)*/
DECLARE tmp_fileType VARCHAR(20); /*文件类型*/
DECLARE num INT DEFAULT 0;
DECLARE upfileRes CURSOR FOR SELECT tur_resType,tur_res_id FROM tch_c_upfile_res WHERE tur_cu_id = id ORDER BY tur_order DESC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_res_table(resId BIGINT,
resTypeName VARCHAR(20),
resName VARCHAR(100),
resUrl VARCHAR(200),
resCType INT,
fileType VARCHAR(20));
TRUNCATE TABLE tmp_res_table;
OPEN upfileRes;
/* 循环执行 */
REPEAT
FETCH upfileRes INTO tmp_resCType,tmp_resId;
IF NOT _done THEN
IF tmp_resCType=0 THEN
SELECT r.`er_name`,r.`er_fileUrl`,t.`sgrt_name`,f.`sgft_name`
INTO tmp_resName,tmp_resUrl,tmp_resTypeName,tmp_fileType
FROM `sys_edures` r
LEFT JOIN `sys_g_restype` t ON r.`sgrt_id`=t.`sgrt_id`
LEFT JOIN `sys_g_filetype` f ON r.`er_fileType`=f.`sgft_id`
WHERE r.`er_id`=tmp_resId;
INSERT INTO tmp_res_table (resId,resTypeName,resName,resUrl,resCType,fileType)
VALUES(tmp_resId,tmp_resTypeName,tmp_resName,tmp_resUrl,tmp_resCType,tmp_fileType);
ELSEIF tmp_resCType=1 THEN
SELECT p.tp_name INTO tmp_resName
FROM tch_p_papers p
WHERE p.tp_id = tmp_resId;
INSERT INTO tmp_res_table (resId,resTypeName,resName,resUrl,resCType,fileType)
VALUES(tmp_resId,"习题",tmp_resName,NULL,tmp_resCType,NULL);
ELSEIF tmp_resCType=2 THEN
SELECT r.`smr_name`,r.`smr_fileUrl`,t.`sgrt_name`,f.`sgft_name`
INTO tmp_resName,tmp_resUrl,tmp_resTypeName,tmp_fileType
FROM `stu_myres` r
LEFT JOIN `sys_g_restype` t ON r.`sgrt_id`=t.`sgrt_id`
LEFT JOIN `sys_g_filetype` f ON r.`smr_fileType`=f.`sgft_id`
WHERE r.`smr_id`=tmp_resId;
INSERT INTO tmp_res_table (resId,resTypeName,resName,resUrl,resCType,fileType)
VALUES(tmp_resId,tmp_resTypeName,tmp_resName,tmp_resUrl,tmp_resCType,tmp_fileType);
END IF;
END IF;
SELECT * FROM tmp_res_table;
UNTIL _done END REPEAT;
CLOSE upfileRes;
END$$
DELIMITER ;
执行结果:
insert存储过程结果集
...
SELECT * FROM tmp_res_table;
UNTIL _done END REPEAT;
这个select在循环里面,所以返回多个结果集,不是insert返回的
INSERT本身不会返回结果集的。