用SQL创建一个存储过程,当输入一个学生的姓名的时候,将从三个表中返回该学生的学号,选修的课程名称和课程成绩。
student表下有sno(主键),sname,sage,ssex,sdept
course表下有cno(主键),cname,ccredit,cpno
sc表下有sno,cno,grade(sno和cno与上面的关联)
my
SQL下的procedure:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc a --创建存储过程名字为a,再次运行时将create改为alter
@sname nvarchar(100)='' --传递的参数
as
select a.sno,b.cname,c.grade from student a,course b,sc c --分别给表命名为a,b,c
where a.sname=@sname and a.sno = c.sno and b.cno=c.cno --查询条件
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
exec a '名字' --执行存储过程语句,可在存储过程外SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc a --创建存储过程名字为a,再次运行时将create改为alter
@sname nvarchar(100)='' --传递的参数
as
select a.sno,b.cname,c.grade from student a,course b,sc c --分别给表命名为a,b,c
where a.sname=@sname and a.sno = c.sno and b.cno=c.cno --查询条件
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
exec a '名字' --执行存储过程语句,可在存储过程外
oracle下的procedure:
create or replace procedure get_imfor(name varchar2,no out number,
v_cname out varchar2,v_grade out number)
--name 默认为输入参数,no、v_canme、v_grade为输出参数
is
begin
select a.sno,b.cname,c.grade into no,v_cname,v_grade
from student a,course b,sc c
where a.sno=c.sno and
b.cno=c.cno and
upper(s.name)=upper(name);
--upper()函数将字符串全部小写,也就是忽略大小写的意思
end get_imfor;