这个存储过程要实现的是先查出tur_resType类型,然后根据不同的类型查询不同的表,再将结果加入临时表tmp_res_table中,但是创建存储过程的时候总是报错,求指导如何修改。
完整代码:
DELIMITER $$
CREATE PROCEDURE `ztedu`.`getResource`(IN id INT)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_res_table(resTypeName VARCHAR(20),
resName VARCHAR(100),
resUrl VARCHAR(200),
resCType SMALLINT);
DECLARE _done INT DEFAULT 0;
DECLARE tmp_resTypeName VARCHAR(20); /*资源类型1*/
DECLARE tmp_resName VARCHAR(100);/*资源名称类型*/
DECLARE tmp_resUrl VARCHAR(200); /*资源类型URL*/
DECLARE tmp_resCType INT; /*资源类型2*/
DECLARE num INT DEFAULT 0;
DECLARE upfileRes CURSOR FOR SELECT tur_resType FROM tch_c_upfile_res WHERE tur_cu_id = id ORDER BY tur_order DESC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
OPEN upfileRes;
/* 循环执行 */
REPEAT
FETCH upfileRes INTO tmp_resCType;
IF NOT _done THEN
SELECT("1");
END IF;
UNTIL _done END REPEAT;
CLOSE upfileRes;
END$$
DELIMITER ;错误信息:
查询:CREATE PROCEDURE `ztedu`.`getResource`(IN id INT) BEGIN CREATE TEMPORARY TABLE IF NOT EXISTS tmp_res_table(resTypeName VARCHAR(2...错误代码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE _done INT DEFAULT 0;
DECLARE tmp_resTypeName VARCHAR(20); /*资源类' at line 7执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0.001 sec临时表MySQL存储
完整代码:
DELIMITER $$
CREATE PROCEDURE `ztedu`.`getResource`(IN id INT)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_res_table(resTypeName VARCHAR(20),
resName VARCHAR(100),
resUrl VARCHAR(200),
resCType SMALLINT);
DECLARE _done INT DEFAULT 0;
DECLARE tmp_resTypeName VARCHAR(20); /*资源类型1*/
DECLARE tmp_resName VARCHAR(100);/*资源名称类型*/
DECLARE tmp_resUrl VARCHAR(200); /*资源类型URL*/
DECLARE tmp_resCType INT; /*资源类型2*/
DECLARE num INT DEFAULT 0;
DECLARE upfileRes CURSOR FOR SELECT tur_resType FROM tch_c_upfile_res WHERE tur_cu_id = id ORDER BY tur_order DESC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
OPEN upfileRes;
/* 循环执行 */
REPEAT
FETCH upfileRes INTO tmp_resCType;
IF NOT _done THEN
SELECT("1");
END IF;
UNTIL _done END REPEAT;
CLOSE upfileRes;
END$$
DELIMITER ;错误信息:
查询:CREATE PROCEDURE `ztedu`.`getResource`(IN id INT) BEGIN CREATE TEMPORARY TABLE IF NOT EXISTS tmp_res_table(resTypeName VARCHAR(2...错误代码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE _done INT DEFAULT 0;
DECLARE tmp_resTypeName VARCHAR(20); /*资源类' at line 7执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0.001 sec临时表MySQL存储
解决方案 »
- 【求助】关于mysql执行存储过程显示结果集的问题!
- 查询表中不相等的记录
- Show master Status在master和Slava机器上结果不一样
- mysql在那里可以管理trigger呢?insert的trigger在mysql中应该如何写呢?
- 关于时间戳的一个问题
- mysql 的默认用户名称,密码?
- mysqld: File '/home/mysql/share/mysql/charsets/gb2312.xml' not found
- 用SQLyog链接mysql,创建表的时候报错。
- MySql安装问题
- 往表里插入数据的时候,遇到个错误~~求解答
- sql 优化
- 注入爆表失败,求解释
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 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);
OPEN upfileRes;
/* 循环执行 */
REPEAT
FETCH upfileRes INTO tmp_resCType,tmp_resId;
IF NOT _done THEN
IF tmp_resCType=0 THEN
DECLARE allRes CURSOR FOR SELECT er_id,er_name FROM `sys_edures` WHERE er_id = tmp_resId;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET allRes_done = 1;
ELSEIF tmp_resCType=1 THEN
DECLARE examRes CURSOR FOR SELECT smr_id,smr_name FROM `tch_p_papers` WHERE smr_id = tmp_resId;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET examRes_done = 1;
ELSEIF tmp_resCType=2 THEN
DECLARE myRes CURSOR FOR SELECT tp_id,tp_name FROM `stu_myres` WHERE tp_id = tmp_resId;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET myRes_done = 1;
END IF;
END IF;
UNTIL _done END REPEAT;
CLOSE upfileRes;
END$$
DELIMITER ;