MySQL创建多表查询的存储过程的两种方法

ocp114

已于 2022-11-13 22:24:16 修改

阅读量425

点赞数

文章标签： mysql 存储过程

于 2016-06-06 17:00:37 首次发布

本文链接：https://blog.csdn.net/ocp114/article/details/51596751

版权

成绩表：

科目表：

学生表：

/*<span style="color:#ff0000;">方法一</span>*/
/*如果该存储过程存在，先删除*/
drop PROCEDURE if exists s_stuinfo;


/*创建查询学员信息的有参存储过程*/
create procedure s_stuinfo(in stu varchar(10))
  select stuNo 学号,stuName 姓名,subjectName 科目,studentResult 成绩,examDate 考试时间 from result,student,subject 
    where (result.subjectNo = subject.subjectNo and result.studentNo = student.stuNo and student.stuNo = stu);
                                                                                       
drop PROCEDURE if exists s_stuinfo;
create procedure s_stuinfo(in stu varchar(10))
  select stuNo 学号,stuName 姓名,subjectName 科目,studentResult 成绩,examDate 考试时间 from result,student,subject 
    where (result.studentNo = student.stuNo and result.subjectNo = subject.subjectNo and stuNo in (stu));

/*调用存储过程*/
call s_stuinfo('0305');

/*<span style="color:#ff0000;">方法二</span>*/
drop procedure if exists ss_stuinfo;
create procedure ss_stuinfo(in stu varchar(10))
   select stuName 姓名 ,stuNo 学号 ,subjectName 科目,studentResult 成绩,examDate 考试时间 from result 
     inner join student on result.studentNo = student.stuNo
     inner join subject on result.subjectNo = subject.subjectNo 
       where student.stuNo = stu;

call ss_stuinfo('0303');