在MySQL Workbench中执行此过程时显示错误 [英] Showing me error while executing this procedure in MySQL Workbench
本文介绍了在MySQL Workbench中执行此过程时显示错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
USE `DMS_db`;
DELIMITER $$
CREATE PROCEDURE usp_BrowseFiles
(
p_CmpId int / * = NULL * /
)
BEGIN
DROP TEMPORARY 表 IF EXISTS temp;
CREATE TEMPORARY TABLE temp(ID VARCHAR ( 50 ),NodeName LONGTEXT,NodeParentId VARCHAR ( 50 ),NodeType VARCHAR ( 50 ),ValuePath LONGTEXT);
INSERT INTO temp(ID,NodeName,NodeParentId,NodeType,ValuePath)( SELECT CONCAT(' CMP',CompanyId),名称, NULL ,' CMP',< span class =code-keyword> NULL FROM 公司 WHERE CompanyId = IFNULL(p_CmpId ,CompanyId));
DECLARE v_ID VARCHAR ( 50 跨度>);
DECLARE v_Name LONGTEXT;
DECLARE v_PID VARCHAR ( 50 );
DECLARE v_Path LONGTEXT;
DECLARE ProjectCur CURSOR FOR
SELECT ProjectId,Name,CompanyId FROM 项目
WHERE CompanyId IN ( SELECT CompanyId FROM 公司 WHERE CompanyId = IFNULL(p_CmpId,CompanyId));
OPEN ProjectCur;
FETCH ProjectCur INTO v_ID,v_Name,v_PID;
LOOP
INSERT INTO temp VALUES (CONCAT(' PRJ',v_ID),v_Name,CONCAT(' CMP',v_PID),' PRJ',CONCAT(' / CMP',v_PID ))
INSERT INTO temp SELECT CONCAT(' CAT',DATE_FORMAT(FileCategoryId)),Name,CONCAT(' PRJ',v_ID),' CAT',CONCAT(' / CMP',v_PID, ' / PRJ',v_ID) FROM FileCategory
FETCH ProjectCur INTO v_ID,v_Name,v_PID
END LOOP;
关闭 ProjectCur;
DECLARE v_CatgId bigint ;
DECLARE v_FileCompanyId int ;
DECLARE FileCur CURSOR FOR
SELECT FileId,Name,ProjectId,FileCategoryId,CompanyId FROM 文件
WHERE CompanyId = IFNULL(p_CmpId,CompanyId);
OPEN FileCur
FETCH FileCur INTO v_ID,v_Name,v_PID,v_CatgId,v_FileCompanyId;
LOOP
INSERT INTO temp VALUES (CONCAT(' FILE',v_ID),v_Name,CONCAT(' CAT',v_PID),' FILE',CONCAT(' / CMP',DATE_FORMAT (v_FileCompanyId),' / PRJ',v_PID,' / CAT',DATE_FORMAT(v_CatgId)));
FETCH FileCur INTO v_ID,v_Name,v_PID,v_CatgId,v_FileCompanyId;
END LOOP;
关闭 FileCur;
SELECT * FROM temp;
END ;
错误详情:
错误代码: 1064 。您的SQL语法中有错误; 检查与 对应的手册您的MySQL服务器版本 正确语法 使用 near ' DECLARE v_ID VARCHAR(50); DECLARE v_Name LONGTEXT; DECLARE v_PID VARCHAR(50); '在 10
请help
解决方案
创建 程序 usp_BrowseFiles
(
p_CmpId int / * = NULL * /
)
BEGIN
DROP TEMPORARY 表 IF EXISTS temp;
CREATE TEMPORARY TABLE temp(ID VARCHAR ( 50 ),NodeName LONGTEXT,NodeParentId VARCHAR ( 50 ),NodeType VARCHAR ( 50 ),ValuePath LONGTEXT);
INSERT INTO temp(ID,NodeName,NodeParentId,NodeType,ValuePath)( SELECT CONCAT(' CMP',CompanyId),名称, NULL ,' CMP',< span class =code-keyword> NULL FROM 公司 WHERE CompanyId = IFNULL(p_CmpId ,CompanyId));
DECLARE v_ID VARCHAR ( 50 跨度>);
DECLARE v_Name LONGTEXT;
DECLARE v_PID VARCHAR ( 50 );
DECLARE v_Path LONGTEXT;
DECLARE ProjectCur CURSOR FOR
SELECT ProjectId,Name,CompanyId FROM 项目
WHERE CompanyId IN ( SELECT CompanyId FROM 公司 WHERE CompanyId = IFNULL(p_CmpId,CompanyId));
OPEN ProjectCur;
FETCH ProjectCur INTO v_ID,v_Name,v_PID;
LOOP
INSERT INTO temp VALUES (CONCAT(' PRJ',v_ID),v_Name,CONCAT(' CMP',v_PID),' PRJ',CONCAT(' / CMP',v_PID ))
INSERT INTO temp SELECT CONCAT(' CAT',DATE_FORMAT(FileCategoryId)),Name,CONCAT(' PRJ',v_ID),' CAT',CONCAT(' / CMP',v_PID, ' / PRJ',v_ID) FROM FileCategory
FETCH ProjectCur INTO v_ID,v_Name,v_PID
END LOOP;
关闭 ProjectCur;
DECLARE v_CatgId bigint ;
DECLARE v_FileCompanyId int ;
DECLARE FileCur CURSOR FOR
SELECT FileId,Name,ProjectId,FileCategoryId,CompanyId FROM 文件
WHERE CompanyId = IFNULL(p_CmpId,CompanyId);
OPEN FileCur
FETCH FileCur INTO v_ID,v_Name,v_PID,v_CatgId,v_FileCompanyId;
LOOP
INSERT INTO temp VALUES (CONCAT(' FILE',v_ID),v_Name,CONCAT(' CAT',v_PID),' FILE',CONCAT(' / CMP',DATE_FORMAT (v_FileCompanyId),' / PRJ',v_PID,' / CAT',DATE_FORMAT(v_CatgId)));
FETCH FileCur INTO v_ID,v_Name,v_PID,v_CatgId,v_FileCompanyId;
END LOOP;
关闭 FileCur;
SELECT * FROM temp;
END ;
错误详情:错误代码: 1064 。您的SQL语法中有错误; 检查与 对应的手册您的MySQL服务器版本 正确语法 使用 near ' DECLARE v_ID VARCHAR(50); DECLARE v_Name LONGTEXT; DECLARE v_PID VARCHAR(50); '在 10
请帮助
USE `DMS_db`;
DELIMITER $$
CREATE PROCEDURE usp_BrowseFiles
(
p_CmpId int/* =NULL */
)
BEGIN
DROP TEMPORARY TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp (ID VARCHAR(50),NodeName LONGTEXT,NodeParentId VARCHAR(50),NodeType VARCHAR(50),ValuePath LONGTEXT);
INSERT INTO temp (ID,NodeName,NodeParentId,NodeType,ValuePath) (SELECT CONCAT('CMP',CompanyId),Name,NULL,'CMP',NULL FROM Company WHERE CompanyId=IFNULL(p_CmpId,CompanyId));
DECLARE v_ID VARCHAR(50);
DECLARE v_Name LONGTEXT;
DECLARE v_PID VARCHAR(50);
DECLARE v_Path LONGTEXT;
DECLARE ProjectCur CURSOR FOR
SELECT ProjectId,Name,CompanyId FROM Project
WHERE CompanyId IN ( SELECT CompanyId FROM Company WHERE CompanyId=IFNULL(p_CmpId,CompanyId));
OPEN ProjectCur;
FETCH ProjectCur INTO v_ID,v_Name,v_PID;
LOOP
INSERT INTO temp VALUES (CONCAT('PRJ',v_ID),v_Name,CONCAT('CMP',v_PID),'PRJ',CONCAT('/CMP',v_PID))
INSERT INTO temp SELECT CONCAT('CAT',DATE_FORMAT(FileCategoryId)),Name,CONCAT('PRJ',v_ID),'CAT',CONCAT('/CMP',v_PID,'/PRJ',v_ID) FROM FileCategory
FETCH ProjectCur INTO v_ID,v_Name,v_PID
END LOOP;
CLOSE ProjectCur;
DECLARE v_CatgId bigint;
DECLARE v_FileCompanyId int;
DECLARE FileCur CURSOR FOR
SELECT FileId,Name,ProjectId,FileCategoryId,CompanyId FROM File
WHERE CompanyId=IFNULL(p_CmpId,CompanyId);
OPEN FileCur
FETCH FileCur INTO v_ID,v_Name,v_PID,v_CatgId,v_FileCompanyId;
LOOP
INSERT INTO temp VALUES (CONCAT('FILE',v_ID),v_Name,CONCAT('CAT',v_PID),'FILE',CONCAT('/CMP',DATE_FORMAT(v_FileCompanyId),'/PRJ',v_PID,'/CAT',DATE_FORMAT(v_CatgId)));
FETCH FileCur INTO v_ID,v_Name,v_PID,v_CatgId,v_FileCompanyId;
END LOOP;
CLOSE FileCur;
SELECT * FROM temp;
END;
Error Details:
Error Code: 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 v_ID VARCHAR(50); DECLARE v_Name LONGTEXT; DECLARE v_PID VARCHAR(50); ' at line 10
Please help
解决方案
CREATE PROCEDURE usp_BrowseFiles ( p_CmpId int/* =NULL */ ) BEGIN DROP TEMPORARY TABLE IF EXISTS temp; CREATE TEMPORARY TABLE temp (ID VARCHAR(50),NodeName LONGTEXT,NodeParentId VARCHAR(50),NodeType VARCHAR(50),ValuePath LONGTEXT); INSERT INTO temp (ID,NodeName,NodeParentId,NodeType,ValuePath) (SELECT CONCAT('CMP',CompanyId),Name,NULL,'CMP',NULL FROM Company WHERE CompanyId=IFNULL(p_CmpId,CompanyId)); DECLARE v_ID VARCHAR(50); DECLARE v_Name LONGTEXT; DECLARE v_PID VARCHAR(50); DECLARE v_Path LONGTEXT; DECLARE ProjectCur CURSOR FOR SELECT ProjectId,Name,CompanyId FROM Project WHERE CompanyId IN ( SELECT CompanyId FROM Company WHERE CompanyId=IFNULL(p_CmpId,CompanyId)); OPEN ProjectCur; FETCH ProjectCur INTO v_ID,v_Name,v_PID; LOOP INSERT INTO temp VALUES (CONCAT('PRJ',v_ID),v_Name,CONCAT('CMP',v_PID),'PRJ',CONCAT('/CMP',v_PID)) INSERT INTO temp SELECT CONCAT('CAT',DATE_FORMAT(FileCategoryId)),Name,CONCAT('PRJ',v_ID),'CAT',CONCAT('/CMP',v_PID,'/PRJ',v_ID) FROM FileCategory FETCH ProjectCur INTO v_ID,v_Name,v_PID END LOOP; CLOSE ProjectCur; DECLARE v_CatgId bigint; DECLARE v_FileCompanyId int; DECLARE FileCur CURSOR FOR SELECT FileId,Name,ProjectId,FileCategoryId,CompanyId FROM File WHERE CompanyId=IFNULL(p_CmpId,CompanyId); OPEN FileCur FETCH FileCur INTO v_ID,v_Name,v_PID,v_CatgId,v_FileCompanyId; LOOP INSERT INTO temp VALUES (CONCAT('FILE',v_ID),v_Name,CONCAT('CAT',v_PID),'FILE',CONCAT('/CMP',DATE_FORMAT(v_FileCompanyId),'/PRJ',v_PID,'/CAT',DATE_FORMAT(v_CatgId))); FETCH FileCur INTO v_ID,v_Name,v_PID,v_CatgId,v_FileCompanyId; END LOOP; CLOSE FileCur; SELECT * FROM temp; END;
Error Details:Error Code: 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 v_ID VARCHAR(50); DECLARE v_Name LONGTEXT; DECLARE v_PID VARCHAR(50); ' at line 10
Please help
这篇关于在MySQL Workbench中执行此过程时显示错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文