您的位置:首页 - 教程 - 数据库 - 正文
第七章:存储过程

一  存储过程的概念

     存储过程是在数据库管理系统中保存的,预先编译的,能实现某种功能的SQL程序,它是数据库应用中比较广泛的一种数据对象

1.为什么需要存储过程

   增加数据库的安全性

2.什么是存储过程

   是SQL语句和控制语句的预编译集合,保存在数据库里,可由应用程序调用执行,而且允许用户声明变量,逻辑控制语句及其他强大的编程功能。它可以接收参数,输出参数,返回单个或多个结果集及返回值。

使用存储过程的优点:

   ①模块化程序设计。

   ②执行速度快,效率高。

   ③减少网络流量

   ④具有良好的安全性

二  系统存储过程

1.常用的系统存储过程

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

 

语法:

exec[ute] 存储过程名[参数值]

2.常用的扩展存储过程

语法:

exec xp_cmdshell dos 命令[no_output]

  其中,exec表示调用存储过程,no_output为可选参数,设置执行dos命令后是否输出返回信息。

三  用户自定义的存储过程

创建不带参数的存储过程:

   1.创建存储过程

语法:

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

 

其中,参数部分可选,这里先讨论不带参数的存储过程方法。

   2.删除存储过程

语法:

drop proc[edure] 存储过程名

   3.一个栗子

--最简单的创建存储过程
create procedure usp_Student
as
select * from Student

创建带参数的存储过程:

   1.创建带参数的存储过程

     如果存储过程的参数后面有“output”关键字,则表示此参数为输出参数;否则视为输入参数,输入参数还可以设置为默认值。

   2.执行带参数的存储过程

语法:

exec[ute] [返回变量值=]存储过程名 [@参数1=] 参数值1 [output]|[default],
                                ……
                               [@参数1=] 参数值n [output]|[default]

其中,output表明参数是输出参数,default表示参数的默认值

   3.一个栗子

create procedure usp_unpass
   @subName varchar(50),
   @score int
as
    declare @subjectNo int --课程编号
    declare @date datetime  --最近考试时间
   select @subjectNo=subjectNo from Subject where SubjectName=@subName
    select @date=max(ExamDate) from Result 
    inner join  Subject on Result.SubjectNo=Subject.SubjectNo
    where Result.SubjectNo=@subjectNo
print '--------------------------------------------------------'
print '参加最近一次'+@subName+'考试没有达到分数线的学员:'
select StudentName.Student,StudentNo,StudentResult from Student
inner join Result on Student.StudentNo=Result.StudentNo
where StudentResult<@score 
and ExamDate=@date 
and subjectNo=@subjectNo   

评论: