how to use a column from select query result in "if condition" of Stored Procedure in SQL.
如何使用select查询中的列导致SQL中存储过程的“if condition”。
1 个解决方案
#1
-1
for stored procedure : use cursor. get column values in variable and then use if condition.
对于存储过程:使用游标。获取变量中的列值,然后使用if条件。
example from mysql:
来自mysql的例子:
DELIMITER $$
CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_email varchar(100) DEFAULT "";
-- declare cursor for employee email
DEClARE email_cursor CURSOR FOR
SELECT email FROM employees;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
OPEN email_cursor;
get_email: LOOP
FETCH email_cursor INTO v_email;
IF v_finished = 1 THEN
LEAVE get_email;
END IF;
-- build email list
SET email_list = CONCAT(v_email,";",email_list);
END LOOP get_email;
CLOSE email_cursor;
END$$
DELIMITER ;
for sql query : use subquery.
对于SQL查询:使用子查询。
#1
-1
for stored procedure : use cursor. get column values in variable and then use if condition.
对于存储过程:使用游标。获取变量中的列值,然后使用if条件。
example from mysql:
来自mysql的例子:
DELIMITER $$
CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_email varchar(100) DEFAULT "";
-- declare cursor for employee email
DEClARE email_cursor CURSOR FOR
SELECT email FROM employees;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
OPEN email_cursor;
get_email: LOOP
FETCH email_cursor INTO v_email;
IF v_finished = 1 THEN
LEAVE get_email;
END IF;
-- build email list
SET email_list = CONCAT(v_email,";",email_list);
END LOOP get_email;
CLOSE email_cursor;
END$$
DELIMITER ;
for sql query : use subquery.
对于SQL查询:使用子查询。