SQL存储过程详解-CSDN博客

本文链接：https://blog.csdn.net/zerolsy/article/details/2123677

存储过程

一、 存储过程概念

存储过程（ procedure ）类似于 C 语言中的函数，它是 SQL 语句和控制流程语句的预编译集合。

存储过程（ procedure ）的优点：

1 允许模块化设计院

2 允许更书报地执行

3 减少网络流量

4 可作为安全机制使用

二、 存储过程的分类

存储过程分为三类 :

1 系统存储过程：以 sp_ 开头，类似于 C 语言中的系统函数。系统存储过程位于 master 数据库中。

2 系统扩展存储过程：以 xp_ 开头，类似于 C 语言中的系统函数。系统扩展存储过程位于 master 数据库中。

3 自定义存储过程：类似于 C 语言中的自定义函数。

三、 常用系统存储过程

1 sp_helpdb: 报告数据库的信息

execute sp_helpdb -- 查看所有数据库信息

execute sp_helpdb ' 数据库名 ' -- 查看当前数据库信息

2 sp_help: 查看某个数据库中表的信息

execute sp_help -- 查看某个数据库中表的所有信息

execute sp_help ' 表名 ' -- 查看某个数据库中单个表的信息

3 sp_helpfile: 查看当前数据库文件的 . mdf 和 . ldf 的位置

execute sp_helpfile

4 sp_helpindex: 查看某个表的索引

execute sp_helpindex ' 表名 '

5 sp_helpconstraint: 查看某个表的约束

execute sp_helpconstraint ' 表名 '

6 sp_helptext: 显示未加密的存储过程触发器或视图的实际文本 .

execute sp_helptext ' 存储过程 | 触发器 | 视图 '

7 sp_stored_procedures: 返回当前数据库中的存储过程的列表 .

execute sp_stored_procedures

8 sp_tables: 查看当前环境下可查询的对象的列表

execute sp_tables

execute sp_tables ' 表名 '

9 sp_columns: 查看表中列的信息

execute sp_columns ' 表名 '

10 sp_databases: 列出服务器上所有的数据库

execute sp_databases

11 sp_renamedb: 更改数据库的名字

execute sp_renamedb ' 原数据库名 ' , ' 新数据库名 '

12 sp_password: 设置登录帐户的密码

execute sp_password ' 旧密码 ' , ' 新密码 ' , ' 登录名 '

alter login ' 登录名 ' enable -- 启用帐户

execute sp_password 'sb','sbsb','sa'

alter login sa enable

四、 常用系统扩展存储过程

execute xp_cmdshell 'dos 命令 ' [,no_output]

execute xp_cmdshell 'mkdir d:/stu',no_output

execute xp_cmdshell 'dir d:/stu'

五、 自定义存储过程

语法 :

create procedure 存储过程名

[{@ 参数数据类型 }[= 默认值 ][output]

......,

{@ 参数 n 数据类型 } [= 默认值 ][output]]

SQL 语句

省略 output 则视为输入参数 .

1 不带参数的存储过程

use stuDB

if exists ( select * from sysobjects where name = ' proc_stu ' )

drop procedure proc_stu

/*--创建存储过程--*/

create procedure proc_stu

declare @writtenAvg float , @labAvg float

select @writtenAvg = avg (WrittenExam), @labAvg = avg (LabExam) from stuMarks

print ' 笔试平均分: ' + convert ( varchar ( 5 ), @writtenAvg )

print ' 机试平均分: ' + cast ( @labAvg as varchar ( 5 ))

if ( @writtenAvg > 70 and @labAvg > 70 )

print ' 本班考试成绩: 优秀 '

else

print ' 本班考试成绩: 差 '

print ' ------------------------------------- '

print ' 参加本次考试没有通过的学生 '

select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo

inner join stuMarks on stuInfo.stuNo = stuMarks.stuNo

where writtenExam < 60 or labExam < 60

-- 调用存储过程--

execute proc_stu

2 带输入参数的存储过程

use stuDB
go
if exists ( select * from sysobjects where name = ' proc_stu ' )
     drop procedure proc_stu
go
/**/ /*--创建存储过程--*/
create procedure proc_stu
@writtenPass int = 60 ,     -- 输入参数,笔试及格线
@labPass int = 60              -- 输入参数,机试及格线
as
     print ' ------------------------------------- '
     print '      参加本次考试没有通过的学生      '
     select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo
         inner join stuMarks on stuInfo.stuNo = stuMarks.stuNo
             where writtenExam < @writtenPass or labExam < @labPass
go

-- 调用存储过程--
execute proc_stu                 -- 笔试和机试都采用默认值
execute proc_stu 50                  -- 笔试及格线,机试采用默认值.
execute proc_stu @labPass = 55      -- 机试及格线,笔试采用默认值.
execute proc_stu 65 , 65              -- 都不采用默认值

3 带输出参数的存储过程

use stuDB
go
if exists ( select * from sysobjects where name = ' proc_stu ' )
     drop procedure proc_stu
go
/**/ /*--创建存储过程--*/
create procedure proc_stu
@notpassSum int output,     -- 指出是输出参数
@writtenPass int = 60 ,     -- 输入参数,笔试及格线,默认参数放后.
@labPass int = 60              -- 输入参数,机试及格线,默认参数放后.
as
     print ' 笔试平均分: ' + convert ( varchar ( 5 ), @writtenPass )
     print ' 机试平均分: ' + cast ( @labPass as varchar ( 5 ))
     print ' ------------------------------------- '
     print '      参加本次考试没有通过的学生      '
     select stuName,stuInfo.stuNo,writtenExam,labExam from stuInfo
         inner join stuMarks on stuInfo.stuNo = stuMarks.stuNo
             where writtenExam < @writtenPass or labExam < @labPass
     /**/ /*------统计不有通过考试的学员人数------*/
     select @notpassSum = count (stuNo) from stuMarks
         where writtenExam < @writtenPass or labExam < @labPass
go

/**/ /**--调用存储过程--**/

-- 定义变量,用于存放调用存储过程时返回的结果--
declare @sum int
-- 调用时也带output,笔试及格线为,机试及格线默认为
execute proc_stu @sum output, 64
select    ' 未通过人数 ' + cast ( @sum as varchar ( 5 )) + ' 人 '