I created one procedure called Sample1
我创建了一个名为Sample1的过程
delimiter ;;
drop procedure if exists Sample1;;
CREATE PROCEDURE Sample1(IN lft1 INT,IN rgt1 INT)
BEGIN
declare emp1 VARCHAR(20);
SELECT emp into emp1 FROM personnell WHERE lft>lft1 and rgt < rgt1;
insert into emp_val values (emp1);
END;;
call Sample1(1,12);;
My Table Structure is like the following:
我的表结构如下:
---------------------
emp lft rgt
---------------------
Albert 1 12
Bert 2 3
Chuck 4 11
Donna 5 6
Eddie 7 8
Fred 9 10
--------------------
It is executing well but i can get the result
它执行得很好,但我可以得到结果
IF the executed query having only one row means its coming and inserted that value into the table called emp_val.
如果执行的查询只有一行意味着它的到来并将该值插入名为emp_val的表中。
but The executed query having more than one row means its showing the following error
但执行的查询有多行意味着它显示以下错误
Error
SQL query:
call Sample1( 1, 12 )
MySQL said: Documentation
#1172 - Result consisted of more than one row
MY SUGGESTION
I have one suggestion to implementing array on it but how to use it i dont know any one help me..
1 个解决方案
#1
3
If your select query can return multiple rows, then you can't use an intermediate variable to store the query's results. You can, however, use the insert ... select ... query format:
如果您的选择查询可以返回多行,则您不能使用中间变量来存储查询的结果。但是,您可以使用insert ... select ...查询格式:
INSERT INTO emp_val SELECT emp FROM personnell WHERE lft>lft1 and rgt < rgt1;
which does it all in a single statement.
这一切都在一个声明中完成。
#1
3
If your select query can return multiple rows, then you can't use an intermediate variable to store the query's results. You can, however, use the insert ... select ... query format:
如果您的选择查询可以返回多行,则您不能使用中间变量来存储查询的结果。但是,您可以使用insert ... select ...查询格式:
INSERT INTO emp_val SELECT emp FROM personnell WHERE lft>lft1 and rgt < rgt1;
which does it all in a single statement.
这一切都在一个声明中完成。