第六章:存储过程

什么是存储过程

  • 存储过程(procedure)类似于写EXCEL中的函数,JAVA中的方法
  • 用来执行管理任务或应用复杂的业务规则
  • 存储过程可以带参数,也可以返回结果
  • 存储过程可以包含数据操纵语句、变量、逻辑 控制语句等

存储过程的优点

  • 执行速度更快
  • 允许模块化程序设计
  • 提高系统安全性
  • 减少网络流通量

存储过程的分类

  1. 系统存储过程
    • 由系统定义,存放在master数据库中
    • 类似C语言中的系统函数
    • 系统存储过程的名称都以“sp_”开头或”xp_”开头
  2. 用户自定义存储过程
    • 由用户在自己的数据库中创建的存储过程
    • 类似C语言中的用户自定义函数

常用的扩展存储过程:xp_cmdshell

可以执行DOS命令下的一些的操作以文本行方式返回任何输出调用语法:EXEC xp_cmdshell DOS命令 [NO_OUTPUT]

--开启高级配置               
EXEC sp_configure 'show advanced options',1  
GO
--重新配置(使上一条系统配置生效)
RECONFIGURE
GO
--开启允许xp_cmdshell的权限
EXEC sp_configure 'xp_cmdshell',1   
go 
--重新配置(使上一条系统配置生效) 
RECONFIGURE
GO
--已经有执行系统DOS命令的权限了.....
EXEC xp_cmdshell ''    
--做完之后记得关闭,出于安全考滤
EXEC sp_configure 'xp_cmdshell',0  
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options',0 
GO
RECONFIGURE

常用存储过程

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

创建存储过程的基本语法

create proc 存储过程名
@参数1 类型
,@参数2类型 OUTPUT
,@参数3类型 = 默认值
AS
    --功能的实现
GO

1.简单存储过程 无参数

CREATE PROC PR_KP1
AS
    PRINT '这是一个简单无参存储过程'
GO
--调用
EXEC PR_KP1

2.带普通参数的存储过程

CREATE PROC PR_KP2
@param varchar(50)
AS
    PRINT @param    
GO
EXEC PR_KP2 '这是参数@param的值'

3.带默认值的参数(可选参数)

CREATE PROC PR_KP3
@param varchar(50) = '这是我的默认值.'
AS
    PRINT @param    
GO
EXEC PR_KP3 '这是参数@param的值'

4.普通能数与默认值的共用,默认值的必须放最后.

CREATE PROC PR_KP4
@param0 varchar(50)
,@param varchar(50) = '这是我的默认值.'
AS
    PRINT @param    
GO
EXEC PR_KP4 '这是参数@param的值'
--实例2:如果有多个可选参数,则如下
alter PROC PR_KP4
@param0 varchar(50)
,@param varchar(50) = '这是我的默认值11111.'
,@param2 varchar(50) = '这是我的默认值22222.'
AS
    PRINT @param0 
    PRINT @param 
    PRINT @param2  
GO
EXEC PR_KP4 '这是参数@param的值',default,'aaaaaaa'

5.带输出参数的存储过程

CREATE PROC PR_KP5
@param varchar(200) OUTPUT
AS
    SET @param = '这是输出参数的值!'
GO
--调用
DECLARE @temp varchar(200)
EXEC PR_KP5 @temp OUTPUT
PRINT @temp

6.带RETURN(return后不跟变量名或值,则它是跳出存储过程,如果跟值或变量,则返回值并跳出存储过程),如果用它返回值,只能返回int类型,一般放在条件中或存储过程最后.

ALTER PROC PR_KP6
AS
    RETURN 123
GO
--调用
declare @temp int
EXEC @temp = PR_KP6
print convert(varchar(50),@temp)

作业

--1.写一个存储过程,创建一个数据库,要求:
    --参数1 数据库名
    --参数2 路径 (带默认值)
    --在创建前判断这个库是否存在,存在则删作
    alter PROC PR_创建数据库
@dbname varchar(50) --数据库名
,@path VARCHAR(200) = 'E:\数据库勿删' --数据库存放的路径
AS

exec('IF EXISTS(SELECT NAME FROM sys.sysdatabases WHERE NAME='''+@dbname+''')
    DROP DATABASE ['+@dbname+']')
-----------------------------------------
--在创建数据库之前要创建目录:学员自己完成
--开启高级配置               
EXEC sp_configure 'show advanced options',1  
--重新配置(使上一条系统配置生效)
RECONFIGURE
--开启允许xp_cmdshell的权限
EXEC sp_configure 'xp_cmdshell',1   
--重新配置(使上一条系统配置生效) 
RECONFIGURE
--已经有执行系统DOS命令的权限了.....


DECLARE @temp VARCHAR(200)
SET @temp = 'md '+@path
EXEC xp_cmdshell @temp,'NO_OUTPUT'

--做完之后记得关闭,出于安全考滤
EXEC sp_configure 'xp_cmdshell',0  
RECONFIGURE
EXEC sp_configure 'show advanced options',0 
RECONFIGURE
-----------------------------------------
--解决路径中\问题(读取路径最后一个字符,看它是不是\)
if(RIGHT(@path,1) <> '\')
    set @path = @path+'\'

EXEC('CREATE DATABASE '+@dbname+'
ON
(
    NAME = '+@dbname+'_data
    ,FILENAME = ['+@path+@dbname+'_data.mdf]
    ,FILEGROWTH = 10%
    ,SIZE = 5MB
)
LOG ON
(
    NAME = '+@dbname+'_log
    ,FILENAME = ['+@path+@dbname+'_log.ldf]
    ,FILEGROWTH = 10%
    ,SIZE = 5MB
    ,MAXSIZE = 50MB
)')
PRINT '创建成功'
GO
--调用
EXEC PR_创建数据库 '五班测试数据库BBB','D:\测试路径1'
EXEC PR_创建数据库 '五班测试数据库BBB'
    

加强练习作业2:

--写一个存储过程完成转帐(由于学习难度,暂时取消,只写了部分代码,转帐与错误处理未写完)
--1.三个输入参数,转出人ID,转入人ID,钱,@输出参数 显示成功或不失败 
--2.要求有事务,转帐一旦失败则回滚,并给输出参数赋值'失败',
--  成功能给输出参数赋值'成功' 并提交事务
ALTER PROC 转帐
@flag VARCHAR(50) OUTPUT --用于输出成功还是失败
,@outusrid INT  --转出人ID
,@inusrid INT   --转入人ID
,@money MONEY   --钱
AS
--1.开始事务
BEGIN TRAN

--2.转帐(思考可分几步?
    --1.判断两用户ID是否存在,
DECLARE @tempid INT
SELECT @tempid = COUNT(id) FROM st_money WHERE id = @outusrid
IF(@tempid <> 1) 
BEGIN
    set @flag = '转帐失败:转出用户不存在!'
    ROLLBACK
    RETURN
END
DECLARE @tempid1 INT
SELECT @tempid1 = COUNT(id) FROM st_money WHERE id = @inusrid
IF(@tempid1 <> 1) 
BEGIN
    set @flag = '转帐失败:转入用户不存在!'
    ROLLBACK
    RETURN
END
    --2.判断转出帐户余额是否充足
DECLARE @tempmoney MONEY
SELECT @tempmoney = MONEY FROM st_money WHERE id = @outusrid
IF(@tempmoney < @money)
BEGIN
    set @flag = '转帐失败:转出人余额不足!'
    ROLLBACK
    RETURN
END
    --3.防止参数@money为负数或0,转0无意义
IF(@money <= 0)
BEGIN
    set @flag = '转帐失败:你想干什么???转帐金额必须大于0'
    ROLLBACK
    RETURN
END
--开始转帐....由于学生接收能力关系,暂停了
    
--最后要对语句异常进行处理.暂停了未写)
--3.要判断是否成功成功则提交事务给输出变量赋值,不成功则回滚事务并给输出变量赋值
COMMIT TRAN
set @flag = '转帐成功!'
GO
--调用代码
DECLARE @flag VARCHAR(50)
EXEC 转帐 @flag output,1,2,100
select @flag