存储过程
一、存储过程的概念
T-SQl和C语言一样 ，是一门结构化的语言。
什么是存储过程？
存储过程是SQL查询语句与控制流程语句的预编译集合，并以特定的名称保存在数据库中。存储过程也是数据库对象
分类：
系统存储过程： 以sp_或xp_打头
用户自定义 ：以proc_打头
存储过程的优点：
执行速度快 效率高
模块式编程
减少网络流量
提高安全性
二、系统存储过程
SQl server 的系统存储过程保存在master数据库中，且所有命名的系统存储过程命名以“Sp_”开头。在master数据库中，
系统存储过程数量如下：
|
select count([name])as '系统存储数量' from sysobjects where [name] like 'sp_%' |
EXECUTE 用来表示调用存储过程，也可以缩写为EXEC，
调用存储的语法如下：
| EXECUTE ‘存储过程名’ ‘参数’ ---如果没有参数则省略参数 |
常用的系统存储过程
| EXEC sp_databases | 列出当前系统中的数据库 |
| EXEC sp_renamedb 'Northwind','Northwind1' | 修改数据库的名称(单用户访问) |
| USE stuDB GO EXEC sp_tables | 返回某个表列的信息 |
| EXEC sp_columns | 查看指定列的信息 |
| EXEC sp_help | 查看某个表的所有信息 |
| EXEC sp_helpconstraint '表名' | 查看某个表的约束 |
| EXEC sp_helpdb '数据库名' 或 EXEC sp_helpdb | 查看指定数据库或所有数据库信息 |
| EXEC sp_helptext '对象名称' | 显示数据库对象（存储过程、触发器、试图）的定义文本 |
| EXEC sp_helpindex '表名' | 查看指定表的索引信息 |
| EXEC sp_renamedb '原名称','新名称' | 更改数据库名称 |
| EXEC sp_stored_procedures | 列出当前环境可用的所有存储过程 |
除了系统存储过程，SQL Server 还提供以Xp_开头的扩展存储过程，如可以调用DOS命名的，XP_cmdshell 存储过程
用法如下：
|
EXEC Xp_cmdshell DOS 命名 [NO_OUTPUT]
|
NO_OUTPUT 为可选参数，表示是否输入存储过程返回的信息
三、用户自定义存储过程
1、语法
|
create procedure 存储过程名 @参数1名 数据类型 [=默认值] [参数类型(输入/输出)] ... ... @参数n名 数据类型 [=默认值] [参数类型(输入/输出)] as begin sql语句 end; go |
参数类型分为输入参数和输出参数，默认为输入参数，使用OUTPUT表示输出参数。创建存储过程最好以proc开头
2、创建不带参数的存储过程
| --判断存储过程是否存在
if object_id('proc_student','procedure') is not null drop procedure proc_student go create procedure proc_student as begin select pcid as '电脑编号', case pcuse when 0 then '空闲' when 1 then '忙碌' end as '使用状态' from pc end; --调用存储过程 execute proc_student select * from pc go |
3、创建带输入参数的存储过程
语法：
|
create procedure 存储过程名 @参数1名 数据类型 [=默认值] .... @参数2名 数据类型[=默认值] as SQl与语句 ... go |
--例如
|
--创建带输入参数的存储过程 if object_id('proc_stu','procedure') is not null drop procedure proc_stu go create procedure proc_stu @pcuse int as begin select pcid as '电脑编号', case pcuse when 0 then '空闲' when 1 then '忙碌' end as '使用状态' from pc where pcuse=@pcuse end; --调用存储过程 execute proc_stu @pcuse=1 |
4、创建带输出参数的存储过程
|
--创建带输出参数的存储过程 if OBJECT_ID('proc_s','procedure') is not null drop procedure proc_s go create procedure proc_s @pcid int, @pcus int output as begin select @pcus=pcuse from pc where pcid=@pcid end; --调用存储过程 declare @pcus int execute proc_s 5,@pcus output |
四、处理错误信息
当存储过程的语句十分复杂时，可以在存储过程中加入错误语言。SQL Server中可以使用RAISERROR 返回用户自定义的错误信息。
RAISERROR 语法如下：
| RAISERROR (自定义的错误信息，错误的严重级别，错误状态) |
自定义错误信息：表示输出信息：表示输出的错误提示文本
错误的严重级别：表示用户自定义错误的严重性级别。（0-18极）
错误的状态：表示自定义错误的状态，值的范围在1-127
