Home  >  Article  >  Database  >  MSSQL存储过程学习笔记一 关于存储过程

MSSQL存储过程学习笔记一 关于存储过程

WBOY
WBOYOriginal
2016-06-07 18:01:44911browse

在写笔记之前,首先需要整理好这些概念性的东西,否则的话,就会在概念上产生陌生或者是混淆的感觉。

一、 存储过程的概念,优点,语法
在写笔记之前,首先需要整理好这些概念性的东西,否则的话,就会在概念上产生陌生或者是混淆的感觉。
概念:将常用的或者是很复杂的工作,预先利用SQL语句写好并用一个指定的名称存储起来,那么以后要是调用这些SQL语句的时候,只需要利用Execute/Exec执行以下,即可。
优点:当然了,使用存储过程的优点是很多的,下面来一一说明。
1、 存储过程只是在创造的时候进行编译,以后每次执行的时候,就不需要编译了,但是直接利用SQL的话,需要每次运行的时候都重新编译一次,所以使用存储过程可以提高数据库的执行速度。
2、 当对数据库进行复杂操作的时候,利用存储过程进行封装,可以减少代码出错的几率,并且MSSQL本身具有代码调试能力,可以很容易的定位到出错的语句。
3、 存储过程可以重复使用,可以提高开发人员的开发效率。
4、 安全性高,可以设定只有特定权限的用户对存储过程进行操作;也可以在一定的程度上预防SQL注入操作。
种类:存储过程分为三类,分别为系统存储过程、扩展存储过程、用户自定义存储过程。
1、 系统存储过程:就是以SP_开头的存储过程,用来进行系统的各种设定,取得信息,进行相关的管理工作等等;如:sp_help就是取得指定对象的相关信息。
2、 扩展存储过程:就是以XP_开头的,用来调用操作系统提供的功能。以下为引用的内容:exec master..xp_cmdshell ‘ping 127.0.0.1'
3、 用户自定义的存储过程:
常用格式如下:
代码如下:
Create procedure procedue_name
  [@parameter data_type][output]
  [with]{recompile|encryption}
  as
  sql_statement

需要说明的就是:
output:表明此参数是可以回传的。
[with]{recompile|encryption}中的recompile:表明每次执行此存储过程的时候,都重新编译一次(默认情况下只有在创建的时候才进行编译)。
encryption:所创建的存储过程的内容会被加密。
小技巧:在这里需要说明的是,如果我们有时候要在数据库中查找所有包含A关键字的表的列的名称,那么该如何寻找呢?可以利用下面的语句:
代码如下:
select table_name,column_name from INFORMATION_SCHEMA.COLUMNSwhere COLUMN_NAME like '%A%'; --查看那些表含有包含A的列

但是如果想在存储过程找存在表“B”的存储过程的名称,该如何做呢,可以利用下面的语句来进行:
代码如下:
select routine_name, routine_definition from information_schema.routines
where routine_definition like '%B%'
and routine_type='procedure'

当然了,我们其实还可以利用SQL中的syscomments,sysobjects,sysdepends来查看具体的数据信息,这个和oracle中的dba_objects等很像:
代码如下:
select * from syscomments; --查看标注
select * from sysobjects; --查看数据库对象
select * from sysdepends; --查看依赖关系

二、存储过程进阶
当然了,说先来说明下存储过程的格式语法规则:
代码如下:
Create Procedure Procedure-name ( Input parameters , Output Parameters (If required))AsBegin Sql statement used in the stored procedureEnd

在这里我们利用一个普通的例子来说明:
代码如下:
/* Getstudentname is the name of the stored procedure*/
Create PROCEDURE Getstudentname(
@studentid INT --Input parameter , Studentid of the student
)
AS
BEGIN
SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END

当然了,这里的@studentid参数只是一个传入的参数,但是如果想回传一个值,那么就需要利用到out参数来实现,具体的实现代码如下:
代码如下:
/*
GetstudentnameInOutputVariable is the name of the stored procedure which
uses output variable @Studentname to collect the student name returns by the
stored procedure
*/
Create PROCEDURE GetstudentnameInOutputVariable
(
@studentid INT, --Input parameter , Studentid of the student
@studentname VARCHAR(200) OUT -- Out parameter declared with the help of OUT keyword
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END

从上面的代码,可以看出out参数的具体用法,但是如果想在SQL服务器端执行这段代码,那该如何进行呢?
其实,一说到这,稍微麻烦一点,如果是只有in参数,那么只需要利用execute/exec 后面加上存储过程的名称,里面给参数赋值即可;但是如果不仅有in参数,而且有out参数,这个该怎么来弄呢?
下面通过一个具体的实例来详细的描述用法:
代码如下:
Alter PROCEDURE GetstudentnameInOutputVariable
(
@studentid INT, --Input parameter , Studentid of the student
@studentname VARCHAR (200) OUT, -- Output parameter to collect the student name
@StudentEmail VARCHAR (200)OUT -- Output Parameter to collect the student email
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname,
@StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
END

可以看出,上面的存储过程有三个参数,其中第一个是IN参数,而后两个是OUT参数,从过程主体可以看出,第一个out参数就是得到学生全名,第二个则是得到email的。
那么如何在服务器端查看执行后得到的结果呢?
代码如下:
Declare @Studentname as nvarchar(200) -- 申明第一个输出参数
Declare @Studentemail as nvarchar(50) -- 申明第二个输出参数
Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output
Select @Studentname,@Studentemail --“select”语句可以查看结果

好了,上面就是存储过程方面的一些知识,后续文章将会将游标,自定义错误,事物运行给包括进来,希望SQL在一步一步的学习中,坚实的成长起来。
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn