Question

oracle student表，编写一个存储过程，输出指定部门的学生的学号、姓名、分数、部门号

create or replace procedure sel_stu(
var_dep in varchar,
num_sno out student.sno%type,
var_sna out student.snamev%type,
var_sco out student.score%type,
var_depid out student.department_id%type) is
begin
select sno,sname,score,department_id
into num_sno,var_sna,var_sco,var_depid
from student
where department = var_dep;
end sel_stu;
/
出现编译 错误，求大神指导

Answer 1

create or replace procedure sel_stu(var_dep in varchar,
num_sno out student.sno%type,
var_sna out student.sname%type,
var_sco out student.score%type,
var_depid out student.department_id%type) is
begin
select sno, sname, score, department_id
into num_sno, var_sna, var_sco, var_depid
from student
where department = var_dep;
end sel_stu;

Answer 2

if (exists (select * from sys.objects where name = 'proc_stu'))
drop proc proc_stu
go
create proc proc_stu(@sname varchar(8) ='张%')
as
select Student.sno,sname,isnull(lname,'') as lname,isnull(grade,0) as grade
from Student left join SC on Student.sno=SC.sno
left join Lesson on SC.lno=Lesson.lno and
where SC.sname=@sname

go