SQL——存储过程

存储过程：存储在数据库服务器端执行的一组T-SQL语句的集合。（相当于函数）
1.①在stuinfo数据库中创建一个存储过程p1，查询指定学号的学生的姓名和平均分，要求姓名和平均分使用返回参数。
②执行该存储过程查询学号为101的学生的姓名和平均分。

		create procedure select_name_avg 
		@stu_no   char(5),
		@stu_name char(10) output,
		@stu_greed float output
		as 
			begin
				select @stu_name = sname, @stu_greed = AVG(degree)
				from score, student
				where student.sno = @stu_no and student.sno = score.sno
				group by student.sno, student.sname
			end
		declare @stu_n char(10);
		declare  @stu_d float;
		execute select_name_avg '101', @stu_n output, @stu_d output;
		select @stu_n, @stu_d

2.①在stuinfo数据库中创建存储过程P2，根据指定的学号和课程号判断成绩等级（A、B、C、D、E） (如果degree>=90则为A，依次类推，不及格为E)。
②执行该存储过程查看学号为101、课程号为3-105的成绩等级。

	create procedure p2
	@stu_no char(5),
	@stu_co char(6),
	--@stu_de float,
	@stu_re char(5) output
	as
		begin 
			declare @stu_de float
			select  @stu_de = degree
			from score
			where sno = @stu_no and cno = @stu_co
		end
		begin
			if(@stu_de < 60)			set @stu_re = 'E';
			if(@stu_de between 60 and 70)set @stu_re = 'D';
			if(@stu_de between 70 and 80) set @stu_re = 'C';
			if(@stu_de between 80 and 90) set @stu_re = 'B';
			if(@stu_de >= 90)				set @stu_re = 'A';
		end	 
		declare @x  char(5)
		execute p2 '101', '3-105', @x output
		select @x

3.①在stuinfo数据库中创建存储过程P3，检查指定学号的学生是否有选课。有就输出其姓名、课程名和成绩，没有就输出“该生无选课”。
②执行该存储过程分别查看学号为101和888学生的选课情况。

create procedure p3
		@stu_no char(5)
		as 
			begin
				declare @c char(6) 
				select @c = cno
				from score
				where sno = @stu_no
			end
			begin 
				if(@c is null) print '该学生未选课'
				else 
					select sname, cname, degree
					from student, score, course
					where student.sno = @stu_no and student.sno = score.sno and score.cno = course.cno
			end
		execute p3 '101';
		execute p3 '888'

4.①在OrderManagement数据库中创建存储过程P5，查询指定年份的销售总额。
②执行该存储过程查看2001年的销售总额。要求执行完存储过程后按如下格式输出数据：
2001年的销售总额：
----------------------------
******元

	create procedure p5
	@y char(5)
	as 
		begin
			declare @tot_m int
			select @tot_m = SUM(单价 * 数量)
			from order_detail, order_list
			where order_detail.订单号 = order_list.订单号 and year(order_list.订购日期) = @y
		end
		begin
			print @y + '年的销售总额:';
			print '-------------------';
			print @tot_m ;
		end
	execute p5 '2001'

5.①在OrderManagement数据库中创建存储过程P6，修改指定订单号和器件号的单价。
②执行该存储过程将订单号为OR-01C、器件号为P1001的单价修改为1000。

create procedure p6
	@dno char(6),
	@qno char(5),
	@mon int
	as
		begin
		    update order_detail
		    set 单价 = @mon
		    where  订单号 = @dno and 器件号 = @qno
		end
	execute p6 'OR-01C', 'P1001', 1000;

6.①在OrderManagement数据库中创建存储过程P7，查询订单中至少订购了“CPU P4 1.4G”和“CPU P4 1.4G”这两种器件的订单号。
②执行该存储过程。

	create procedure p7
	as
	begin
		select distinct 订单号
		from order_detail
		where  订单号 in(
			select 订单号
			from order_detail
			where 器件名 = 'CPU P4 1.4G'
		) 
		and 订单号 in(
			select 订单号
			from order_detail
			where 器件名 = '内存'
		)
	end
	execute p7