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

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（不能直接用输出变量来接收）