在MySQL中使用游标时，从DECLARE语句调用存储过程 [英] call a stored procedure from the DECLARE statement when using cursors in MySQL
问题描述
我试图在MySQL中使用游标多次调用存储过程.我想多次调用它，因为某些临时表中存在my_id的值，并遍历这些id并连接结果.
无论如何，我在过程的这一部分遇到了麻烦:
DECLARE curs CURSOR FOR
SELECT something FROM somewhere;
我不想从某个地方选择某些东西.我想要类似
DECLARE curs CURSOR FOR
CALL storedproc(@an_id);
DECLARE语句可以用于调用存储的proc吗?还是只需要将其与SELECT关联?到处搜寻，恐怕情况就是如此.
使用游标需要一些标准的样板代码来包围它.
使用游标为表中的每组值调用存储过程都需要基本相同的样板. SELECT从任何位置获取要传递的值(可以是临时表，基表或视图，并且可以包括对存储函数的调用)，然后使用这些值调用过程. /p>
我在下面编写了该样板代码的语法上有效的示例，并带有注释以解释每个组件的功能.除了被要求做某件事只是因为"而已，我最不喜欢的事-一切都(希望)得到了解释.
您提到使用多个值调用该过程，因此本示例使用2.
请注意，由于某种原因，发生在她身上的事件按特定顺序排列.变量必须首先声明，游标必须在它们的继续处理程序之前声明，并且循环必须遵循所有这些内容.这给人的印象是，这里有些极端的灵活性，但事实并非如此.您可以通过在过程主体的BEGIN ... END块内嵌套其他代码来重置顺序.例如，如果需要在循环中使用第二个游标，则只需在循环中的另一个BEGIN ... END中声明它.
DELIMITER $$
DROP PROCEDURE IF EXISTS `my_proc` $$
CREATE PROCEDURE `my_proc`(arg1 INT) -- 1 input argument; you might not need one
BEGIN
-- from http://stackoverflow.com/questions/35858541/call-a-stored-procedure-from-the-declare-statement-when-using-cursors-in-mysql
-- declare the program variables where we'll hold the values we're sending into the procedure;
-- declare as many of them as there are input arguments to the second procedure,
-- with appropriate data types.
DECLARE val1 INT DEFAULT NULL;
DECLARE val2 INT DEFAULT NULL;
-- we need a boolean variable to tell us when the cursor is out of data
DECLARE done TINYINT DEFAULT FALSE;
-- declare a cursor to select the desired columns from the desired source table1
-- the input argument (which you might or might not need) is used in this example for row selection
DECLARE cursor1 -- cursor1 is an arbitrary label, an identifier for the cursor
CURSOR FOR
SELECT t1.c1,
t1.c2
FROM table1 t1
WHERE c3 = arg1;
-- this fancy spacing is of course not required; all of this could go on the same line.
-- a cursor that runs out of data throws an exception; we need to catch this.
-- when the NOT FOUND condition fires, "done" -- which defaults to FALSE -- will be set to true,
-- and since this is a CONTINUE handler, execution continues with the next statement.
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- open the cursor
OPEN cursor1;
my_loop: -- loops have to have an arbitrary label; it's used to leave the loop
LOOP
-- read the values from the next row that is available in the cursor
FETCH NEXT FROM cursor1 INTO val1, val2;
IF done THEN -- this will be true when we are out of rows to read, so we go to the statement after END LOOP.
LEAVE my_loop;
ELSE -- val1 and val2 will be the next values from c1 and c2 in table t1,
-- so now we call the procedure with them for this "row"
CALL the_other_procedure(val1,val2);
-- maybe do more stuff here
END IF;
END LOOP;
-- execution continues here when LEAVE my_loop is encountered;
-- you might have more things you want to do here
END $$
DELIMITER ;
I am trying to use a cursor in MySQL to call a stored procedure many times. I want to call it as many times as a value for my_id exists in some temporary table, and iterate through those ids and concatenate the results.
Anyway, I'm having trouble with this part of the process:
DECLARE curs CURSOR FOR
SELECT something FROM somewhere;
I don't want to select something from somewhere. I want something like
DECLARE curs CURSOR FOR
CALL storedproc(@an_id);
Can the DECLARE statement be used to call a stored proc? Or does it have to be associated with a SELECT only? Googling around, I'm afraid that the latter is the case.
Using a cursor requires some standard boilerplate code to surround it.
Using a cursor to call a stored procedure for each set of values from the table requires essentially the same boilerplate. You SELECT the values you want to pass, from wherever you're getting them (which could be a temporary table, base table, or view, and can include calls to stored functions) and then call the procedure with those values.
I've written an syntactically valid example of that boilerplate code, below, with comments to explain what each component is doing. There are few things I dislike more than being asked to just do something "just because" -- so everything is (hopefully) explained.
You mentioned calling the procedure with multiple values, so this example uses 2.
Note that there events that happen her are in a specific order for a reason. Variables have to be declared first, cursors have to be declared before their continue handlers, and loops have to follow all of those things. This gives an impression that there's some fairly extreme inflexibility, here, but that's not really the case. You can reset the ordering by nesting additional code inside BEGIN ... END blocks within the procedure body; for example, if you needed a second cursor inside the loop, you'd just declare it inside the loop, inside another BEGIN ... END.
DELIMITER $$
DROP PROCEDURE IF EXISTS `my_proc` $$
CREATE PROCEDURE `my_proc`(arg1 INT) -- 1 input argument; you might not need one
BEGIN
-- from http://stackoverflow.com/questions/35858541/call-a-stored-procedure-from-the-declare-statement-when-using-cursors-in-mysql
-- declare the program variables where we'll hold the values we're sending into the procedure;
-- declare as many of them as there are input arguments to the second procedure,
-- with appropriate data types.
DECLARE val1 INT DEFAULT NULL;
DECLARE val2 INT DEFAULT NULL;
-- we need a boolean variable to tell us when the cursor is out of data
DECLARE done TINYINT DEFAULT FALSE;
-- declare a cursor to select the desired columns from the desired source table1
-- the input argument (which you might or might not need) is used in this example for row selection
DECLARE cursor1 -- cursor1 is an arbitrary label, an identifier for the cursor
CURSOR FOR
SELECT t1.c1,
t1.c2
FROM table1 t1
WHERE c3 = arg1;
-- this fancy spacing is of course not required; all of this could go on the same line.
-- a cursor that runs out of data throws an exception; we need to catch this.
-- when the NOT FOUND condition fires, "done" -- which defaults to FALSE -- will be set to true,
-- and since this is a CONTINUE handler, execution continues with the next statement.
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- open the cursor
OPEN cursor1;
my_loop: -- loops have to have an arbitrary label; it's used to leave the loop
LOOP
-- read the values from the next row that is available in the cursor
FETCH NEXT FROM cursor1 INTO val1, val2;
IF done THEN -- this will be true when we are out of rows to read, so we go to the statement after END LOOP.
LEAVE my_loop;
ELSE -- val1 and val2 will be the next values from c1 and c2 in table t1,
-- so now we call the procedure with them for this "row"
CALL the_other_procedure(val1,val2);
-- maybe do more stuff here
END IF;
END LOOP;
-- execution continues here when LEAVE my_loop is encountered;
-- you might have more things you want to do here
END $$
DELIMITER ;
这篇关于在MySQL中使用游标时，从DECLARE语句调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
