第8章：数据库编程

代码是基于SQLServer学习，与MySQL有略微差别！

8.1、存储过程

存储过程的概念：

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
存储过程还是触发器，都是SQL语句和流程控制语句的集合。就本质而言，触发器也是一种存储过程。

存储过程的的优点：

1、存储过程允许标准组件式编程

数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。

2、存储过程能够实现较快的执行速度

存储过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。
SQL 语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。

3、存储过程能够减少网络流量

如果某一数据库操作所涉及到的SQL 语句被组织成一存储过程，那么用户调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL 语句，从而大大增加了网络流量，降低网络负载。

4、存储过程可被作为一种安全机制

系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。

SQL Server中存储过程分为两类：

1）系统提供的存储过程

系统过程以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。

2）用户自定义存储过程

用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。

SQL Server存储过程

系统存储过程	说明
sp_databases	列出服务器上的所有数据库
sp_helpdb	报告有关指定数据库或所有数据库的信息
sp_renamedb	更改数据库的名称
sp_tables	返回当前环境下可查询的对象的列表
sp_columns	返回某个表的列的信息
sp_help	查看某个表的所有信息
sp_helpconstraint	查看某个表的约束
sp_helpindex	查看某个表的索引
sp_stored_procedures	列出当前环境中的所有存储过程
sp_password	添加或修改登录帐户的密码
sp_helptext	显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本

示例：

-- exec 可写可不写
exec sp_databases
exec sp_helpdb 'test' -- 数据库名
exec sp_stored_procedures
sp_helptext 'sp_adduser'

定义存储过程的语法：

create proc[edure] <存储过程名>
		@参数1 数据类型[= 默认值] [output],
	   … … ,
		@参数n 数据类型[= 默认值] [output]
as
		SQL语句

存储过程的参数：

参数可选
参数分为输入参数、输出参数
参数要指定数据类型
输入参数允许有默认值

自定义存储过程示例：

-- 定义存储过程将实现如下功能：从student表中取出所有记录。
create procedure GetS
as
	select * from student
-- 执行存储过程
exec GetS

-- 创建存储过程，查询Java Logic最近一次考试未通过考试的学员信息
create procedure failExamStu
as
	declare @javaCno char(2)
	declare @latestExamdate datetime
	select @javaCno = cno from course 
	where cname = 'Java Logic'
	-- 打印
	print @javaCno
	select @latestExamdate = max(examdate) from sc
	where sc.cno = @javaCno
	-- 打印
	print @latestExamdate
	select * from student s inner join sc on sc.sno = s.sno
	where sc.grade < 60 and sc.cno = @javaCno 
	and sc.examdate = @latestExamdate
-- 执行存储过程
exec failExamStu

-- 修改前面的存储过程failExamStu，根据传入的参数来查询相应的记录。
alter procedure failExamStu
	@coursename nvarchar(20),
	@failGrade decimal(4,1) = 60
as
	declare @javaCno char(2)
	declare @latestExamdate datetime
	select @javaCno = cno from course 
	where cname = @coursename
	-- 打印
	print @javaCno
	select @latestExamdate = max(examdate) from sc
	where sc.cno = @javaCno
	-- 打印
	print @latestExamdate
	select * from student s inner join sc on sc.sno = s.sno
	where sc.grade < @failGrade and sc.cno = @javaCno 
	and sc.examdate = @latestExamdate
-- 执行带输入参数的存储过程
exec failExamStu 'Java Logic', 80

-- 带输入和输出参数的存储过程 
create proc GetAvg
	@courseName nvarchar(20),
	@avgGrade decimal(4,1) output
as
	select @avgGrade = avg(sc.grade)
	from sc inner join course c on sc.cno = c.cno
	where c.cname = @courseName

-- 声明变量，作为引用，指向变量的地址
declare @g1 decimal(4,1)
exec GetAvg '数学', @g1 output
print @g1

-- 创建存储过程，根据课程名返回该门课最近一次考试平均分
-- 以及未通过考试的学员人数，通过线不固定
create proc test1 
	@courseName nvarchar(20),
	@passGrade decimal(4,1) = 60,
	@avgGrade decimal(4,1) output,
	@countPass int output
as
	declare @latestExamDate datetime
	select @latestExamDate = max(sc.examdate) from sc 
	select c.cno from course c where c.cname = @courseName
	select @avgGrade = avg(sc.grade), @countPass = count(s.sno)
	from student s inner join sc on sc.sno = s.sno 
	where sc.examdate = @latestExamDate and sc.grade >= @passGrade

declare @avgGrade decimal(4,1)
declare @countPass int
exec test1 '数据结构', 55, @avgGrade output, @countPass output
print @avgGrade
print @countPass