第10章 存储过程

--创建存储过程，接收性别输入和输出游标变量
create proc studentinfo
@sex char(2),
@student_cursor cursor varying output
as
set @student_cursor = cursor
for
select s.sno,sname,cname,grade
end
deallocate @stcursor
上机练习：
使用student、s_c、course表
1、创建查询表student的所有内容的存储过程query_student ，加密，并执行。1、create procedure query_student
with encryption
as
select * from student
1. 查看SQL Server中所有数据库的信息
2. 查看teaching数据库的信息
sp_helpfile
sp_helpfile:返回与当前数据库关联的文件的物理名称及属性。
语法格式：Sp_helpfile [name]
注意：’name’是当前数据库中任意文件的逻辑名称。
1. 查看teaching数据库的所有数据库文件信息
2. 查看teaching数据库的数据文件的信息
sp_help
sp_help:报告有关数据库对象、用户自定义数据类型或SQL Server 2000提供的数据类型的信息。
数据库对象：在系统表sysobjects中列出的所有对象。包括系统表，用户表，表的各种约束，视图，默认值，规则，存储过程，触发器。（这些对象在sysobjects表中都可以看到）
若平均年龄小于30，则显示‘年龄结构偏小’；
若介于30到40之间，则显示‘年龄结构合理’。
将该系教师的平均年龄和最大年龄传递给用户。
4、创建有返回值的存储过程
【实例】利用存储过程的返回值，创建一个求学生各科总成绩的存储过程。（书例10-5）
5、创建加密保存的存储过程
【例】创建存储过程usp_show_teacher1,要求返回所有教师的所有信息，并加密。
查看存储过程源代码：（用图形界面和系统存储过程分别查看）
1．从系统表syscomments中查看存储过程源代码。
2．利用系统存储过程sp_helptext 。
6、重新编译存储过程
1. 使用系统存储过程sp_recompile。
go
exec query_student
2、创建存储过程select_student，查询指定姓名，性别的学生学号、姓名、性别、课程名和成绩。
2、create procedure select_student1
@sname char(10),@sex char(2)
as
if @sno is NULL or @sname is NULL
--如果在非空列输入空值，返回提示信息
begin
print '必须输入学号，姓名和系部！'
print '请重新输入！'
return
end
else
--使用事务
1.查看sysobjects表中的对象，然后试着给某个对象更改名称，之后在查看sysobjects表。
sp_tables
sp_tables：返回可在当前环境中查询的对象列表。这代表可在FROM子句中出现的任何对象。
语法格式如下：
Sp_tables [[ ‘name’][, ‘owner’] [, ‘qualifier’]
declare @sname char(10),@sex char(2)
exec select_student1 '郭靖','m'
4、创建存储过程insert_student，可以通过该存储过程将学生的信息插入到表中，并能将所有学生的平均年龄返回给用户。（参照书上例10-2）
4、以下黑色部分可以不写
存储过程
一、 存储过程的基本知识
存储过程是由SQL语句和控制语句构成的语句串，经编译和优化后存储在数据库服务器中，以供使用时调用。
二、 存储过程的优点
三、 存储过程的类型
? 用户定义的存储过程
? 扩展存储过程
? 系统存储过程
四、系统存储过程
sp_helpdb
sp_helpdb:报告有关指定数据库或所有数据库的信息
【实例】为teaching数据库创建存储过程usp_insert_student，通过执行该存储过程将数据添加到student1表中。（多个输入参数）（书例10-2）
【练习】创建存储过程usp_select_teacher,查询指定系，指定性别的教师的编号、姓名。
【练习】执行存储过程usp_select_teacher,查询数学系、男教师的编号、姓名。
1. 返回可在master数据库中查询的对象列表。
2. 返回有关teaching数据库中dbo所拥有的表的信息。
sp_columns
sp_columns：返回当前环境中可查询的指定表或视图的列信息。
语法格式如下：
Sp_columns object [, owner]
1. 返回指定表TEACHER的列信息。
select @avg_age=avg(age) from student
select @avg_age as 平均年龄
5、执行存储过程insert_student，将学生的信息（s8，李红，f，24）插入到表student中, 并将所有学生的平均年龄返回给用户。
declare @avgage int
rollback tran
print '插入数据失败，请重新输入！'
return
end
else
print '插入数据成功，学生姓名：'+@sname
end
commit tran
select s.sno,sname,sex,cname,grade
from student as s,s_c as sc,course as c
where s.sno=sc.sno and o=o
and sname=@sname and sex=@sex
go
3、执行存储过程select_student，查询郭靖的信息。
2. 重新编译创建存储过程时在定义中指定WITH RECOMPILE选项。
3. 执行时指定WITH RECOMPILE选项，强制执行重新编译。
（二）执行存储过程
（三）修改、删除存储过程
1、修改：
语法格式如下：
ALTER PROCEDURE procedure_name
[{@parameter data_type}[=default] [OUTPUT] ][,…..]
exec insert_student 's8','李红','f',24,@avgage
（注：@avgage后不写output再此也可以，因为下面没有语句引用，加上：select @avgage，会发现为null；若在下面语句要使用的话，必须加上output）
6、创建存储过程studentinfo，能够在客户端从数据库服务器检索出指定性别学生的信息（学号，姓名，课程名称，成绩），使用游标变量完成。6、
| RECOMPILE,ENCRYPTபைடு நூலகம்ON}]
AS sql_statement
1、创建没有参数的存储过程
1.创建存储过程usp_show_teacher,要求返回所有教师的所有信息。
2. 调用1题中创建的存储过程。
2、创建有输入参数的存储过程
【例】创建存储过程usp_select_student，查询指定姓名的学生学号、姓名以及性别。（单个输入参数）
create procedure insert_student
@sno char(10)=NULL, @sname char(20)=NULL,@sex char(2)=NULL,@age int=NULL, @avg_age int output
AS
--对输入的参数进行检测，防止非空列输入空值
3、创建有输入输出参数的存储过程
【实例】在teaching数据库里基于student1表，创建一个计算学生平均分数的存储过程，并将学生的姓名和平均成绩作为输出参数。
（书例10-3）
【练习】创建存储过程usp_teacher_age，查询特定系的教师的信息，并要判定该系教师的年龄结构:
若教师的平均年龄大于40，则显示‘年龄结构偏大’；
五、用户自定义存储过程
（一）创建存储过程
使用SQL语句
CREATE PROCEDURE procedure_name
[{@parameter data_type} [=default] [OUTPUT]][,…..]
[WITH
{RECOMPILE|ENCRYPTION
begin tran
begin
insert into student(sno,sname,sex,age)
values(@sno,@sname,@sex,@age)
--检测插入是否成功
if @@error!=0
begin
语法格式：sp_help [name]
1.列出teaching数据库中每个对象的信息
2.列出teaching数据库中smallint数据类型的信息