游标选择的 MySQL 存储过程语法错误

Question

DROP PROCEDURE CREATE_DUPLICATE_PRODUCT;
DELIMITER $$
CREATE PROCEDURE `CREATE_DUPLICATE_PRODUCT`(IN `old_course_id` INT(25), IN `new_course_id` 
INT(25))
BEGIN
DECLARE db_cursor CURSOR FOR SELECT lic.org_id, lic.course_id, lic.license_duration_typeid, lic.insert_date, lic.insert_by, lic.quantity FROM  cdp_organization_licenses as lic JOIN cdp_organization_license_settings as sett ON sett.org_license_id = lic.id JOIN cdp_organization_purchases as pur ON pur.org_id = lic.org_id AND pur.course_id = lic.course_id JOIN cdp_organizations as org ON org.org_id = lic.org_id WHERE  lic.status_id = 1 AND org.status_id = 1;
DECLARE @org_id INT;
DECLARE @course_id INT;
DECLARE @license_typeid INT;
DECLARE @insert_date INT;
DECLARE @insert_by INT;
DECLARE @quantity INT;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @org_id, @course_id, 
@license_duration_typeid, @insert_date, @insert_by, @quantity;
WHILE @@FETCH_STATUS = 0  
BEGIN  
--Do stuff with scalar values
FETCH NEXT FROM db_cursor INTO @org_id, @course_id,
@license_duration_typeid, @insert_date, @insert_by, @quantity;
INSERT INTO cdp_organization_licenses_test
SET
org_id = @org_id,
course_id = @course_id,
license_duration_typeid = @license_duration_typeid,
insert_date = @insert_date,
insert_by = @insert_by,
quantity = @quantity;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
END$$
DELIMITER ;

我收到此错误：

#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 '@org_id INT;
DECLARE @course_id INT;
DECLARE @license_typeid INT; ' at line 5

我该怎么做才能解决这个问题？

Answer 1

你正在混合sql serv和mysql，这不能工作

DROP PROCEDURE CREATE_DUPLICATE_PRODUCT;
DELIMITER $$
CREATE PROCEDURE `CREATE_DUPLICATE_PRODUCT`(IN `old_course_id` INT(25), IN `new_course_id` 
INT(25))
BEGIN

DECLARE _org_id INT;
DECLARE _course_id INT;
DECLARE _license_typeid INT;
DECLARE _insert_date INT;
DECLARE _insert_by INT;
DECLARE _quantity INT;
DECLARE finished INTEGER DEFAULT 0;
DECLARE db_cursor CURSOR FOR 
SELECT lic.org_id, lic.course_id, lic.license_duration_typeid, lic.insert_date, lic.insert_by, lic.quantity 
FROM  cdp_organization_licenses as lic 
JOIN cdp_organization_license_settings as sett ON sett.org_license_id = lic.id 
JOIN cdp_organization_purchases as pur ON pur.org_id = lic.org_id AND pur.course_id = lic.course_id 
JOIN cdp_organizations as org ON org.org_id = lic.org_id 
WHERE  lic.status_id = 1 AND org.status_id = 1;
DECLARE CONTINUE HANDLER 
FOR NOT FOUND SET finished = 1;

OPEN db_cursor;
getinfo: LOOP
BEGIN  
FETCH NEXT FROM db_cursor INTO _org_id, _course_id,
_license_duration_typeid, _insert_date, _insert_by, _quantity;
IF finished = 1 THEN 
LEAVE getEmail;
END IF;
INSERT INTO cdp_organization_licenses_test
SET
org_id = _org_id,
course_id = _course_id,
license_duration_typeid = _license_duration_typeid,
insert_date = _insert_date,
insert_by = _insert_by,
quantity = _quantity;
END;
END LOOP getinfo;
CLOSE db_cursor;
END$$
DELIMITER ;

但是你可以简单地做一个

INSERT INTO cdp_organization_licenses_test     
SELECT lic.org_id, lic.course_id, lic.license_duration_typeid, lic.insert_date, lic.insert_by, lic.quantity 
FROM  cdp_organization_licenses as lic 
JOIN cdp_organization_license_settings as sett ON sett.org_license_id = lic.id 
JOIN cdp_organization_purchases as pur ON pur.org_id = lic.org_id AND pur.course_id = lic.course_id 
JOIN cdp_organizations as org ON org.org_id = lic.org_id 
WHERE  lic.status_id = 1 AND org.status_id = 1;

这使得一切都没有循环

Answer 2

不要DECLARE名称开头带有@的变量。这些是会话变量，不需要声明，并且具有会话范围，