mysql输入输出参数存储过程简单使用

kaywang

　　DROP TABLE IF EXISTS `stuinfo`;
CREATE TABLE `stuinfo` (
`id` int(11) NOT NULL,
`stuName` varchar(255) NOT NULL,
`stuNo` varchar(255) NOT NULL,
`stuSex` varchar(4) NOT NULL,
`stuAge` int(11) NOT NULL,
`stuSeat` int(11) NOT NULL,
`stuAddress` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `stuinfo` VALUES ('1', 'zhangqiuli', 's25301', 'nan', '18', '1', null);
INSERT INTO `stuinfo` VALUES ('2', 'liwencai', 's25302', 'nan', '31', '2', null);
INSERT INTO `stuinfo` VALUES ('3', 'lisiwen', 's25303', 'nv', '22', '3', null);
INSERT INTO `stuinfo` VALUES ('4', 'ouyangjunxiong', 's25304', 'nan', '28', '4', null);
INSERT INTO `stuinfo` VALUES ('5', 'meichaomeng', 's25318', 'nv', '23', '5', null);


DROP TABLE IF EXISTS `stumarks`;
CREATE TABLE `stumarks` (
`id` int(11) NOT NULL,
`ExamNo` varchar(255) NOT NULL,
`stuNo` varchar(255) NOT NULL,
`writtenExam` double NOT NULL,
`LabExam` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `stumarks` VALUES ('1', 's1', 's25303', '93', '59');
INSERT INTO `stumarks` VALUES ('2', 's2', 's25302', '63', '91');
INSERT INTO `stumarks` VALUES ('3', 's3', 's25301', '90', '83');
INSERT INTO `stumarks` VALUES ('4', 's4', 's25318', '63', '53');

存储过程
use test;
drop procedure if exists `pro_use`;
create procedure pro_use (writtenPass int,labPass int,out outputParam varchar(16))//注意这里的变量不需要@，区分于SqlServer
begin

select @aa:=stuName ,stuInfo.stuNo,writtenExam,labExam from stuInfo
inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo
where writtenExam<writtenPass or labExam<labPass;
set outputParam=@aa;//给蔬菜变量赋值
end;


call pro_use(60,55,@out);//调用存储过程
select @out;//打印出输入变量的值

这里很简单，参数为输入和输出参数，输出参数的值为查询一个字段的值，先用用户变量@aa接收，然后再复制给输出变量outputParam（不能直接用输出变量来接收）