SQL Server存储过程

时间:2022-11-04 22:49:14

创建于2016-12-24 16:12:19


存储过程

    概念:

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

        一种数据对象。

       2.存储过程是SQL语句和控制语句的预编译集合,保存在数据库里可由应用程序调用执行,而且允许用户声明变量、逻辑

          控制语句及其他强大的编程功能。

       3.存储过程可包含逻辑控制语句和数据操作语句,它可以接收参数、输出参数、返回单个或多个结果集及返回值。

       4.存储过程可以只包含一条select语句,也可以包含一系列使用控制流的SQL语句。存储过程可以包含个别或全部的控制

        流语句。

    特点:

       1.模块化程序设计:

          只需创建一次存储过程并将其存储在数据库中,以后即可在程序中反复调用该存储过程。

       2.执行速度快,效率高:

          如果某操作需要大量的T-SQL代码或需要重复执行,则存储过程将比T-SQL批处理代码的执行速度更快。因为存储

          过程只在创建的时候编译一次,而批处理代码每次执行的时候都需要编译。

       3.减少网络流量:

          使用存储过程后,一个需要数百行T-SQL代码的操作,由一条执行过程代码的单独语句即可实现,而不需要再网络

          中发送数百行代码。

       4.具有良好的安全性:

          即使对于没有直接执行存储过程中语句的权限的用户,也可授予他们执行该存储过程的权限。不同的用户使用不同

          的存储过程。

       5.存储过程分为两类:

          系统存储过程用户自定义的存储过程

系统存储过程

    概括:SQL Server提供系统存储过程,它们是一组预编译的T-SQL语句。系统存储过程提供了管理数据库和更新表的机制,并

       充当从系统表中检索信息的快捷方式。

    常用的系统存储过程:

       SQL Server的系统存储过程的名称以“sp_”开头,并存放在Resource数据库中。下面列出一些常用的系统存储过程↓

常用的系统存储过程 
系统存储过程  说明
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]  [返回变量=]  存储过程名

                      [@参数1=]参数值1  [output]  |  [default],

                      ......,

                        [@参数n=]参数值n  [output]  |  [default]

       其中,exec是execute的简写

       如果执行存储过程的语句时批处理中的第一个语句,则可以省略execute关键字。

       如果有返回值的话,可以用一个变量接收。但是在存储过程内return只能返回数值类型

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

       如果不按照参数顺序传递参数值,则要指定参数名。

       一旦某个参数按照"@参数名=参数值"格式传递数据,那么该参数之后的其他参数都必须以同样的格式传递参数

       值。

    常用的扩展存储过程:

       扩展存储过程是SQL Server提供的各类系统存储过程中的一类,允许使用其他编程语言创建外部存储过程,为数据库用

       户提供从SQL Server实例到外部程序的接口,以便进行各种维护活动。通常以"xp_"作为前缀,以DLL形式单独存在。

       语法:    exec  xp_cmdshell  DOS命令  [no_output]

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

用户自定义的存储过程

    组成:

       1.输入参数和输出参数。

       2.在存储过程中执行的T-SQL语句。

        3.存储过程的返回值。

    语法:

       1.创建语法:

             create  proc[edure]  存储过程名

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

                ......,

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

                ]

             as

                SQL语句

       2.删除语法:

             drop  proc[edure]  存储过程名

       3.判断存储过程是否存在,如果存在,则删除

             if  exists(select  *  from sysobjects where name = 存储过程名)

                drop  proc[edure]  存储过程名

             go

    语法释义:

       1.创建:

             proc是procedure的简写,参数的默认值可选,不写则没有默认值,调用执行时必须带有此参数。output

             表示是输出参数,即执行存储过程后把返回值存放在输出参数中。就像C#中的引用传递一样。输出参数也

             可以设置默认值。不写output则为输入参数,就像C#中的值传递一样。

             另外需注意,存储过程中参数的默认值不能是系统函数。比如参数是时间类型,默认值不能是

             GETDATE()

处理错误信息

    概念:在存储过程中,可以使用print语句显式用户定义的错误信息,但这些是临时的,并不保存错误信息。使用raiserror语句

       可以指定严重级别并设置系统变量记录所发生的错误。

    raiserror语句:

       语法:

             raiserror  ({msg_id  |  msg_str}  {,  severity,  state}  [with  option [...n]])

       语法释义:

             msg_id:  在sysmessages系统表中指定的用户定义错误信息。

             msg_str:  用户定义的特定信息,最长为255个字符。

             severity:   与特定信息关联,表示用户定义的严重性级别。用户可使用的级别为0~18级;19~25级是

                     为sysadmin固定角色的成员预留的,并且需要指定with  log选项;20~25级被认为是致命

                     错误。

             state:   表示错误的状态,是1~255中的值。

             option:   错误的自定义选项,可以是下列任一值

                     a. log:    在Microsoft SQL Server数据库引擎实例的错误日志和应用程序日志中记

                            录错误。

                     b. nowait:  将消息立即发送给客户端。

                     c. seterror:  将@@error值和error_nummber值设置为msg_id或50000,不用考虑

                            严重级别。