Курсор в процедуре MySQL продолжает отображать некоторые ошибки ⇐ MySql

[Anonymous]

Объявление курсоров, где данные находятся через формат JSON, продолжайте получать ошибку. Сообщение об ошибке-< /p>
Column is not valid at this position, expecting for, lock, select, table, values, with.....

< /code>
-- Procedure to insert Project details
DELIMITER $$

CREATE PROCEDURE InsertProjectDetails
(
IN p_input JSON
)
BEGIN

-- Variable Declarations
DECLARE v_project_id INT;
DECLARE v_user_id INT;
DECLARE v_project_name VARCHAR(255);
DECLARE v_client_name VARCHAR(255);
DECLARE v_project_department VARCHAR(255);
DECLARE v_project_description TEXT;
DECLARE v_project_deadline DATE;
DECLARE v_assigned_date DATE;
DECLARE v_project_report BLOB;
DECLARE v_project_technology TEXT;
DECLARE v_employee_names TEXT;
DECLARE v_employee_roles TEXT;
DECLARE v_manager_names TEXT;
DECLARE v_manager_roles TEXT;

DECLARE employee_name VARCHAR(255);
DECLARE employee_role VARCHAR(255);
DECLARE manager_name VARCHAR(255);
DECLARE manager_role VARCHAR(255);
DECLARE user_emp_id INT;
DECLARE user_mgr_id INT;
DECLARE done INT DEFAULT 0;

-- Cursor Declarations
DECLARE mgr_cursor CURSOR FOR
SELECT TRIM(value)
FROM JSON_TABLE(CONCAT('["', REPLACE(v_manager_names, ',', '","'), '"]')
COLUMNS (value VARCHAR(255) PATH '$'));

DECLARE role_mgr_cursor CURSOR FOR
SELECT TRIM(value)
FROM JSON_TABLE(CONCAT('["', REPLACE(v_manager_roles, ',', '","'), '"]')
COLUMNS (value VARCHAR(255) PATH '$'));

DECLARE emp_cursor CURSOR FOR
SELECT TRIM(value)
FROM JSON_TABLE(CONCAT('["', REPLACE(v_employee_names, ',', '","'), '"]')
COLUMNS (value VARCHAR(255) PATH '$'));

DECLARE role_emp_cursor CURSOR FOR
SELECT TRIM(value)
FROM JSON_TABLE(CONCAT('["', REPLACE(v_employee_roles, ',', '","'), '"]')
COLUMNS (value VARCHAR(255) PATH '$'));

-- Handler Declaration
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

-- Extract fields from JSON input
SET v_user_id = JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.user_id'));
SET v_project_name = JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.project_name'));
SET v_client_name = JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.client_name'));
SET v_project_department = JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.project_department'));
SET v_project_description = JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.project_description'));
SET v_project_deadline = JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.project_deadline'));
SET v_assigned_date = JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.assigned_date'));
SET v_project_report = FROM_BASE64(JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.project_report')));
SET v_project_technology = JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.project_technology'));
SET v_employee_names = JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.employee_names'));
SET v_employee_roles = JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.employee_roles'));
SET v_manager_names = JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.manager_names'));
SET v_manager_roles = JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.manager_roles'));

-- Insert into Projects
INSERT INTO Projects
(
project_name, client_name, project_department, project_description,
project_status, project_deadline, assigned_date, project_report, project_technology
)
VALUES
(
v_project_name, v_client_name, v_project_department, v_project_description,
'Hold', v_project_deadline, v_assigned_date, v_project_report, v_project_technology
);

SET v_project_id = LAST_INSERT_ID();

-- Insert into Project_Reports if project_report exists
IF v_project_report IS NOT NULL THEN
INSERT INTO Project_Reports (project_id, user_id, project_report)
VALUES (v_project_id, v_user_id, v_project_report);
END IF;

-- Process Managers
OPEN mgr_cursor;
OPEN role_mgr_cursor;

SET done = 0; -- Reset done flag for this loop
read_mgr: LOOP
FETCH mgr_cursor INTO manager_name;
FETCH role_mgr_cursor INTO manager_role;
IF done THEN
LEAVE read_mgr;
END IF;

SELECT user_id INTO user_mgr_id
FROM Users
WHERE user_name = manager_name AND user_type = 'manager'
LIMIT 1;

IF user_mgr_id IS NOT NULL THEN
-- Assuming 'manager_id' in Project_Managers links to 'user_id' in Users
INSERT INTO Project_Managers (project_id, manager_id, manager_role_assigned, manager_name, project_accepted)
VALUES (v_project_id, user_mgr_id, manager_role, manager_name, FALSE);
END IF;
END LOOP;

CLOSE mgr_cursor;
CLOSE role_mgr_cursor;

-- Process Employees
SET done = 0; -- Reset done flag for this loop

OPEN emp_cursor;
OPEN role_emp_cursor;

read_emp: LOOP
FETCH emp_cursor INTO employee_name;
FETCH role_emp_cursor INTO employee_role;
IF done THEN
LEAVE read_emp;
END IF;

SELECT user_id INTO user_emp_id
FROM Users
WHERE user_name = employee_name AND user_type = 'employee'
LIMIT 1;

IF user_emp_id IS NOT NULL THEN
-- Assuming 'employee_id' in Project_Employees links to 'user_id' in Users
INSERT INTO Project_Employees (project_id, employee_id, employee_role_assigned, employee_name, project_accepted)
VALUES (v_project_id, user_emp_id, employee_role, employee_name, FALSE);
END IF;
END LOOP;

CLOSE emp_cursor;
CLOSE role_emp_cursor;

-- Return result
SELECT JSON_OBJECT
(
'message', 'Project inserted successfully',
'project_id', v_project_id
) AS result;
END $$

DELIMITER ;
< /code>
I also tried using alias but kept getting the same error. Instead of getting record added through json format of objects to the table this procedure keep getting error. I am unable to identify the correct way to declaring the cursor when data is through JSON format.
DECLARE mgr_cursor CURSOR FOR
SELECT TRIM(jt.value) -- Use the alias here
FROM JSON_TABLE(CONCAT('["', REPLACE(v_manager_names, ',', '","'), '"]')
COLUMNS (value VARCHAR(255) PATH '$')) AS jt; -- Added 'AS jt' alias
< /code>
DECLARE mgr_cursor CURSOR FOR
SELECT TRIM(jt.value)
FROM JSON_TABLE(CONCAT('["', REPLACE(v_manager_names, ',', '","'), '"]')
COLUMNS (value VARCHAR(255) PATH '$[*]')) AS jt;


Подробнее здесь: https://stackoverflow.com/questions/796 ... ome-errors