Я ценю ваш ответ. Я изменил процедуру с вашими изменениями, но я сделал что-то не так. Не могли бы вы рассказать мне, что не так с кодом? Поэтому мне нужно будет включить код в конце основного вопроса. Попытка № 2 находится внизу.
Может ли кто-нибудь сказать мне, что не так с этой процедурой? Система сообщает мне, что есть ошибка:
DECLARE myemail_cursor CURSOR FOR
USE v_db; SELECT DISTINCT u.mail FROM users u INNER JOIN users_roles ur ON u.uid=ur.uid INNER JOIN role r ON ur.rid=r.rid WHERE r.name = 'court administrator' AND from_unixtime(u.access) >= NOW() - INTERVAL 1 YEAR;
Если я запускаю USE mydb; SELECT... в запросе он отлично работает. Могу ли я использовать переменную USE; select... 'в процедуре?
BEGIN
DECLARE v_finished, v_finished1 BOOLEAN DEFAULT FALSE;
DECLARE v_email varchar(4000) DEFAULT "";
DECLARE v_db varchar(400) DEFAULT "";
DECLARE mydb_cursor CURSOR FOR
SELECT TABLE_SCHEMA AS 'database' FROM information_schema.TABLES WHERE TABLE_TYPE = 'BASE TABLE' and table_name='users';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = TRUE;
open mydb_cursor;
get_mydb: LOOP
FETCH FROM mydb_cursor INTO v_db;
IF v_finished THEN
CLOSE mydb_cursor;
LEAVE get_mydb;
END IF;
DECLARE myemail_cursor CURSOR FOR
USE v_db; SELECT DISTINCT u.mail FROM users u INNER JOIN users_roles ur ON u.uid=ur.uid INNER JOIN role r ON ur.rid=r.rid WHERE r.name = 'court administrator' AND from_unixtime(u.access) >= NOW() - INTERVAL 1 YEAR;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished1 = TRUE;
OPEN myemail_cursor;
get_myemail: LOOP
FETCH FROM myemail_cursor INTO v_email';
IF v_finished1 THEN
CLOSE myemail_cursor;
SET v_finished1 = FALSE;
LEAVE get_myemail;
END IF;
-- build email list
SET email_list = CONCAT(v_email," ",email_list);
END LOOP get_myemail;
CLOSE myemail_cursor;
END LOOP get_email;
CLOSE email_cursor;
END LOOP get_mydb;
END
Попытка №2. Я получаю эту ошибку: table 'v_db.users' не существует. Первый курсор выбирает только базы данных, в которых пользователи таблицы существуют, поэтому я не могу понять, почему я получаю ошибку.
BEGIN
DECLARE v_finished, v_finished1 BOOLEAN DEFAULT FALSE;
DECLARE v_email varchar(4000) DEFAULT "";
DECLARE v_db varchar(400) DEFAULT "";
DECLARE mydb_cursor CURSOR FOR
SELECT TABLE_SCHEMA AS 'database' FROM information_schema.TABLES WHERE TABLE_TYPE = 'BASE TABLE' and table_name='users';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = TRUE;
open mydb_cursor;
get_mydb: LOOP
FETCH FROM mydb_cursor INTO v_db;
IF v_finished THEN CLOSE mydb_cursor;
LEAVE get_mydb;
END IF;
BLOCK2: BEGIN
DECLARE myemail_cursor CURSOR FOR
SELECT DISTINCT u.mail FROM v_db.users u INNER JOIN v_db.users_roles ur ON u.uid=ur.uid INNER JOIN v_db.role r ON ur.rid=r.rid WHERE r.name = 'court administrator' AND from_unixtime(u.access) >= NOW() - INTERVAL 1 YEAR;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished1 = TRUE;
OPEN myemail_cursor;
get_myemail: LOOP
FETCH FROM myemail_cursor INTO v_email;
IF v_finished1 THEN
CLOSE myemail_cursor;
LEAVE get_myemail;
END IF;
SET email_list = CONCAT(v_email," ",email_list);
END LOOP get_myemail;
END BLOCK2;
END LOOP get_mydb;
END
23.2.1 Сохраненный рутинный синтаксис
...
- Когда вызывается подпрограмма, выполняется неявное USE db_name (и отменяется, когда процедура завершается). Операции USE в хранимых процедурах запрещены.
...
Пытаться:
...
DECLARE mydb_cursor CURSOR FOR
SELECT
DISTINCT u.mail
FROM
v_db.users u
INNER JOIN v_db.users_roles ur ON u.uid=ur.uid
INNER JOIN v_db.role r ON ur.rid=r.rid
WHERE
r.name = 'court administrator' AND
from_unixtime(u.access) >= NOW() - INTERVAL 1 YEAR;
...
Помните:
...
DECLARE разрешено только внутри инструкции BEGIN... END и должно быть в начале, перед любыми другими заявлениями.
...
v_db
является динамическим, поэтому вам потребуется динамический запрос ( 13.5 Синтаксис подготовленного оператора SQL ), однако объявление динамического курсора не так просто, см. MariaDB / MySQL: Курсоры для динамического SQL .