如何使用select查询中的列导致SQL中存储过程的“if condition”

时间:2022-09-20 23:47:53

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查询:使用子查询。