存储过程

 一、存储过程的概念

　　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

转载于:https://www.cnblogs.com/tyuanyuan/archive/2013/05/11/3073549.html