我试图使用MySQL中的游标多次调用存储过程。我想将它调用的次数与某个临时表中存在的my_id值相同,并遍历这些it并将结果连接起来。
总之,我在这个过程中遇到了麻烦:
DECLARE curs CURSOR FOR
SELECT something FROM somewhere;我不想从某个地方选什么东西。我想要像这样的
DECLARE curs CURSOR FOR
CALL storedproc(@an_id);DECLARE语句可以用来调用存储过程吗?还是必须只与SELECT相关联?在谷歌上搜索,恐怕是后者的情况。
发布于 2016-03-10 21:27:37
使用游标需要一些标准的样板代码来包围它。
使用游标为表中的每一组值调用存储过程基本上需要相同的样板。您可以将要传递的值从任何地方传递(可以是临时表、基表或视图,也可以包括对存储函数的调用),然后使用这些值调用过程。
下面,我编写了一个语法上有效的样板代码示例,并给出了说明每个组件正在做什么的注释。我最不喜欢的事情莫过于“仅仅因为”被要求做一些事情--所以一切都(希望)得到了解释。
您提到了用多个值调用该过程,因此本例使用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 ;发布于 2016-03-08 04:10:00
可以使用声明语句来调用存储的proc吗?
不可能,这方面的文档也很清楚。
游标声明语法 此语句声明游标并将其与关联,这是一个SELECT语句,用于检索游标要遍历的行。若要稍后获取行,请使用fetch语句。SELECT语句检索的列数必须与FETCH语句中指定的输出变量数相匹配。
https://stackoverflow.com/questions/35858541
复制相似问题