求一个存储过程,该存储过程应该实现以下功能:
有一个数据表:emplyee存有员工信息,该数据表中有二个字段name,group,其中name是姓名,group存有每条纪录之间的组属关系,现要求:给定两个姓名name1,name2,判断name2是否是name1的组员。数据表纪录如下:
ref name group
1 张三 null
2 李四 1
3 王二 2
4 刘京 2组属关系为:“李四”属于“张三”这个组,“王二”和“刘京”属于“李四”这个组。希望有一个存储过程,如IsChild(IN fathername CHAR(20), IN childname CHAR(20), OUT ret INT)。如果输入参数调用:IsChild("张三", "王二", @ret), 则ret返回1。
有一个数据表:emplyee存有员工信息,该数据表中有二个字段name,group,其中name是姓名,group存有每条纪录之间的组属关系,现要求:给定两个姓名name1,name2,判断name2是否是name1的组员。数据表纪录如下:
ref name group
1 张三 null
2 李四 1
3 王二 2
4 刘京 2组属关系为:“李四”属于“张三”这个组,“王二”和“刘京”属于“李四”这个组。希望有一个存储过程,如IsChild(IN fathername CHAR(20), IN childname CHAR(20), OUT ret INT)。如果输入参数调用:IsChild("张三", "王二", @ret), 则ret返回1。
张三
/
/
李四
/ \
王二 刘京要求返回王二是否是张三的成员。
先谢谢了!!
set names gbk;
create table lk5 (
ref int not null auto_increment primary key,
`name` char(20),
`group` int
);
insert into lk5(`name`,`group`) values
('张三',null),
('李四',1),
('王二',2),
('刘京',2);
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`sp_isChild`$$CREATE PROCEDURE `test`.`sp_isChild`(IN fathername CHAR(20),IN childname CHAR(20),
OUT ret INT)
BEGIN
set ret = 0;
select `group` from lk5 where `name` = fathername into @group1;
select `group` from lk5 where `name` = childname into @group2;
if @group2 + 1 = @group1 or @group1 is null then
set ret = 1;
end if;
END$$DELIMITER ;
call sp_ischild('张三','王二',@ret);
select @ret;query result(1 records)
@ret
1 call sp_ischild('张三','李四',@ret);
select @ret;
query result(1 records)
@ret
1 call sp_ischild('李四','张三',@ret);
select @ret;
query result(1 records)
@ret
0
BEGIN
DECLARE fatherref INT;
DECLARE tempref INT;
select ref into fatherref from emplyee where name = fathername;
select grp into tempref from emplyee where name = childname;
WHILE tempref is not null and tempref != fatherref DO
select grp into tempref from emplyee where ref = tempref;
END WHILE;
IF tempref is not null and tempref = fatherref THEN
set ret = 1;
ELSE
set ret = 0;
END IF;
END
这样好看些:CREATE PROCEDURE IsChild(IN fathername CHAR(20),IN childname CHAR(20),OUT ret INT)
BEGIN
DECLARE fatherref INT;
DECLARE tempref INT;
select ref into fatherref from emplyee where name = fathername;
select grp into tempref from emplyee where name = childname;
WHILE tempref is not null and tempref != fatherref DO
select grp into tempref from emplyee where ref = tempref;
END WHILE;
IF tempref is not null and tempref = fatherref THEN
set ret = 1;
ELSE
set ret = 0;
END IF;
END
再贴一次:DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`IsChild` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `IsChild`(IN fathername CHAR(20),IN childname CHAR(20),OUT ret INT)
BEGIN
select ref from emplyee where name = fathername into @fatherref;
select grp from emplyee where name = childname into @tempref;
WHILE @tempref is not null and @tempref != @fatherref DO
select grp into @tempref from emplyee where ref = @tempref;
END WHILE;
IF @tempref is not null and @tempref = @fatherref THEN
set ret = 1;
ELSE
set ret = 0;
END IF;
END $$DELIMITER ;
if @group2 + 1 = @group1 or @group1 is null then应该是这个意思(伪代码):
while (ret = 0) && (@childgroup<>null)
select `ref` from lk5 where `name` = fathername into @fatherref;
select `group` from lk5 where `name` = childname into @childgroup;
if (@childgroup = @fatherref) then
set ret = 1;
else
select group from lk5 where ref = @childgroup into @childgroup;
end if
end while
请 骑着上帝看戏 再帮忙看看。这些代码用Mysql的存储过程该如何写?再次感谢!!
我的意思应该和你吻合。
可以帮我去看看,谢谢。
DELIMITER $$
DROP PROCEDURE IF EXISTS `iris`.`IsChild` $$ CREATE PROCEDURE `IsChild`(IN fathername CHAR(20), IN childname CHAR(20), OUT ret INT)
BEGIN
SET @fatherref = 0;
SET @tempref = 0;
select ref from lk5 where name = fathername into @fatherref;
select grp from lk5 where name = childname into @tempref; WHILE @tempref <> 0 and @tempref != @fatherref DO
SET @tempref = 0;
select grp into @tempref from lk5 where ref = @tempref;
END WHILE; IF @tempref is not null and @tempref = @fatherref THEN
set ret = 1;
ELSE
set ret = 0;
END IF; select @ret;
END $$ DELIMITER ;
谢谢