MySQL хранимая процедура, возвращающая несколько наборов записей
Я создал несколько хранимых процедур в моей базе данных (MySQL) следующим образом.
Хранимая процедура 1
CREATE PROCEDURE sp_Name1(
param1,
param2,
......
)
BEGIN
.....
some code
IF cond THEN
call sp_Name2 //Calling 2nd procedure from here.
Update SomeTable .....
SELECT '1' As Result;
END IF
END
Хранимая процедура 2
CREATE PROCEDURE sp_Name2(
param1,
param2,
......
)
BEGIN
.....
some code
IF cond THEN
SELECT '2' As Result;
SELECT '3' As Result;
END IF
END
Теперь я вызываю свою первую хранимую процедуру следующим образом:
Call sp_Name1(param1, param2, ... );
Здесь я получаю 4 набора результатов в MySQL Workbench. 2 результата от sp_Name2, 3-го для оператора обновления в sp_Name1 и 4-го от оператора выбора, также в sp_Name1. Здесь я ищу только последний набор результатов. Иногда последовательность результатов появляется в ожидаемом порядке, что означает, что результаты могут быть такими, как Результат 1, Результат 2, Результат 4, Результат 3 (В этом случае я не могу судить, какой набор результатов мне полезен, поскольку последний набор результатов может быть изменен).
Как подавить нежелательные наборы результатов?
РЕДАКТИРОВАТЬ: У меня есть вариант использования для вашего лучшего понимания.
CREATE PROCEDURE sp_LoginUser( IN Username varchar(50) , IN password varchar(50) )
BEGIN
IF EXISTS( SELECT 1 FROM Users where name = UserName and Pwd = password)
SET userid = 0;
SET loginid = 0;
SELECT userid INTO userid
FROM users
WHERE name = UserName and Pwd = password;
IF userid > 0 THEN
CALL sp_Login(userid);
SET loginid = LAST_INSERT_ID();
END IF;
//only this result i am expecting.
IF loginid > 0 THEN
SELECT userid as userid, loginid AS loginid;
ELSE
SELECT 0 userid, 0 loginid;
END IF;
END IF;
END
CREATE PROCEDURE sp_Login( IN Userid int )
BEGIN
INSERT Logins ( userid, datetime )
VALUES ( Userid, now() );
SELECT LAST_INSERT_ID() AS loginid;
END
Итак, теперь, когда мой пользователь запрашивает логин и вводит его / ее имя пользователя с паролем на моей странице входа в систему, тогда у меня есть вызов sp_LoginUser() на моем сервере. Во многих случаях мне нужно вызывать sp_Login() отдельно.
В приведенном выше случае я могу установить один параметр (например, loginid) AS INOUT в процедуре sp_Login(), назначить ему LAST_INSERT_ID(), удалить оператор SELECT и получить в sp_LoginUser(). Но когда мне нужно вызвать sp_Login() отдельно, мне нужно объявить некоторую переменную в моем коде для получения значения.
5 ответов
Когда вы выполняете выбор внутри хранимой процедуры, набор результатов возвращается клиенту. http://dev.mysql.com/doc/refman/5.5/en/faqs-stored-procs.html
CREATE PROCEDURE sp_LoginUser( IN Username varchar(50) , IN password varchar(50) )
BEGIN
--put the resultset into a variable so it don't return back
DECLARE doesUserExist BOOL;
SELECT EXISTS( SELECT 1 FROM Users where name = UserName and Pwd = password ) INTO doesUserExist;
IF doesUserExist
SET userid = 0;
SET loginid = 0;
SELECT userid INTO userid
FROM users
WHERE name = UserName and Pwd = password;
IF userid > 0 THEN
-- call a function instead of a procedure so you don't need to call last_insert_id again
SET loginid = sp_Login(userid);
END IF;
//only this result i am expecting.
IF loginid > 0 THEN
SELECT userid as userid, loginid AS loginid;
ELSE
SELECT 0 userid, 0 loginid;
END IF;
END IF;
END
-- this is now a function so it can return what you need
CREATE FUNCTION sp_Login(Userid int)
RETURNS INTEGER
BEGIN
INSERT Logins ( userid, datetime )
VALUES ( Userid, now() );
SET loginid = LAST_INSERT_ID();
RETURN loginid;
END
Использование DO SELECT.. если вы не хотите возвращать набор результатов для выбора ( http://dev.mysql.com/doc/refman/5.6/en/do.html). Тем не менее, я не понимаю, почему вы запускаете селекты, если вам не нужны результаты.
Я не уверен, почему вы выбираете LAST_INSERT_ID() в sp_Login и снова в sp_LoginUser?
Если вам нужно вернуть LAST_INSERT_ID() из sp_Login, вам нужно либо присвоить ему выходную переменную, либо вместо этого использовать скалярную функцию.
Почему хранятся процы? Вы можете сделать это с обычным SQL:
SET @previous := LAST_INSERT_ID();
INSERT INTO Logins ( userid, datetime )
SELECT Userid, now()
FROM users
WHERE name = UserName
and Pwd = password;
SELECT *
FROM Logins
WHERE ID = LAST_INSERT_ID()
AND ID != @previous;
Если имя пользователя / пароль были правильными, в вашем наборе строк будет строка для входа в систему - вы можете присоединить ее к пользовательской таблице, чтобы также получить все данные пользователя.
Если имя пользователя / пароль были неверны, у вас будет пустой набор строк.
FYI, LAST_INSERT_ID() возвращается 0 если нет предыдущих вставок.
Хранимые процедуры являются наименее предпочтительным способом реализации SQL, особенно потому, что они наименее переносимы (есть и другие веские причины); если вы можете реализовать в простом SQL, это лучший вариант. Хотя этот SQL не является полностью переносимым, его можно довольно легко преобразовать, так как большинство баз данных имеют функции и функции, аналогичные mysql.
Вернуть несколько значений, например
CREATE PROCEDURE `sp_ReturnValue`(
p_Id int(11), -- Input param
OUT r_status1 INT(11) -- output param
OUT r_status2 VARCHAR(11) -- output param
OUT r_status3 INT(11) -- output param
)
BEGIN
SELECT Status FROM tblUsers WHERE tblUsers_ID = p_Id; // use of input param
SET r_status1 = 2; // use of output param
SET r_status2 = "ABCD"; // use of string output param
SET r_status3 = 2; // use of output param
END
И ЧИТАЙТЕ как
CREATE PROCEDURE `sp_ReadReturnValue`()
BEGIN
SELECT @ret_value1 AS ret_value1,
@ret_value2 AS ret_value2,
@ret_value3 AS ret_value3;
END
Параметр в узле для параметра Out
con.query("CALL sp_ReturnValue(?, @ret_value1, @ret_value2, @ret_value3); CALL sp_ReadReturnValue;", [id], (err, rows) => {
console.log("Print return value \n ");
console.log(rows[1][0].ret_value1 "\n");
console.log(rows[1][0].ret_value2 "\n");
console.log(rows[1][0].ret_value3 );
})