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

MsSQL 前端之家
前端之家收集整理的这篇文章主要介绍了MSSQL存储过程学习笔记一 关于存储过程，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

一、 存储过程的概念，优点，语法
在写笔记之前，首先需要整理好这些概念性的东西，否则的话，就会在概念上产生陌生或者是混淆的感觉。
概念：将常用的或者是很复杂的工作，预先利用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、 用户自定义的存储过程：
常用格式如下：
<div class="codetitle"><a style="CURSOR: pointer" data="72501" class="copybut" id="copybut72501" onclick="doCopy('code72501')"> 代码如下:

<div class="codebody" id="code72501">
Create procedure procedue_name
　　[@parameter data_type][output]
　　[with]{recompile|encryption}
　　as
　　sql_statement

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

但是如果想在存储过程找存在表“B”的存储过程的名称，该如何做呢，可以利用下面的语句来进行：
<div class="codetitle"><a style="CURSOR: pointer" data="42189" class="copybut" id="copybut42189" onclick="doCopy('code42189')"> 代码如下:
<div class="codebody" id="code42189">
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等很像：
<div class="codetitle"><a style="CURSOR: pointer" data="81710" class="copybut" id="copybut81710" onclick="doCopy('code81710')"> 代码如下:
<div class="codebody" id="code81710">
select from syscomments; --查看标注
select from sysobjects; --查看数据库对象
select from sysdepends; --查看依赖关系

二、存储过程进阶
当然了，说先来说明下存储过程的格式语法规则：
<div class="codetitle"><a style="CURSOR: pointer" data="63147" class="copybut" id="copybut63147" onclick="doCopy('code63147')"> 代码如下:<div class="codebody" id="code63147">
Create Procedure Procedure-name ( Input parameters,Output Parameters (If required))AsBegin sql statement used in the stored procedureEnd

在这里我们利用一个普通的例子来说明:
<div class="codetitle"><a style="CURSOR: pointer" data="45393" class="copybut" id="copybut45393" onclick="doCopy('code45393')"> 代码如下:<div class="codebody" id="code45393">
/ 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参数来实现，具体的实现代码如下：
<div class="codetitle"><a style="CURSOR: pointer" data="72097" class="copybut" id="copybut72097" onclick="doCopy('code72097')"> 代码如下:<div class="codebody" id="code72097">
/
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参数，这个该怎么来弄呢？
下面通过一个具体的实例来详细的描述用法：
<div class="codetitle"><a style="CURSOR: pointer" data="91987" class="copybut" id="copybut91987" onclick="doCopy('code91987')"> 代码如下:<div class="codebody" id="code91987">
Alter PROCEDURE GetstudentnameInOutputVariable
(
@studentid INT,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的。
那么如何在服务器端查看执行后得到的结果呢？
<div class="codetitle"><a style="CURSOR: pointer" data="58875" class="copybut" id="copybut58875" onclick="doCopy('code58875')"> 代码如下:<div class="codebody" id="code58875">
Declare @Studentname as nvarchar(200) -- 申明第一个输出参数
Declare @Studentemail as nvarchar(50) -- 申明第二个输出参数
Execute GetstudentnameInOutputVariable 1,@Studentname output,@Studentemail output
Select @Studentname,@Studentemail --“select”语句可以查看结果

好了，上面就是存储过程方面的一些知识，后续文章将会将游标，自定义错误，事物运行给包括进来，希望sql在一步一步的学习中，坚实的成长起来。

存储过程

猜你在找的MsSQL相关文章

SQL Server2012高可用之日志传送测试
（一）日志传送架构 （1.1）相关服务器 主服务器 ：用于生产的服务器，上面运行这生产SQL ...
SQL server事物复制报错：要复制的 LOB 数据的长度(xxxxx)超出了配置的最大值 65536
(一)事故背景 最近在SQL Server 2012生产数据库上配置完事物复制（发布订阅）后，生产数据...
SQL Server2012高可用之事物复制(发布订阅)测试
（一）测试目的 目前公司使用的SQL SERVER 2012高可用环境为主备模式，其中主库可执行读写...
使用zabbix监控sql server的发布订阅
（一）背景个人在使用sql server时，用到了sql server的发布订阅来做主从同步，类似MySQL的...
SQL学习（四）集合运算
UNION和OR谓词 找出 product 和 product2 中售价高于 500 的商品的基本信息. select * fro...
SQL学习（五） 高级处理
5.1 请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。 SEL...
SQL学习(一）初识数据库
datawhale组队学习SQL task01
SQL学习(二）基础查询与排序
datawhale数据库组队学习第二天打卡
SQL Server 2005 返回修改后的数据
今天在公司看到同事写了个SQL2005的新特性的文章，觉得很实用，在这里和大家分享下。原著作...
SQL Server 数据库最小宕机迁移方案
一、目的 在做SQL Server数据库维护的时候，当上司要求我们把几十G的数据文件搬动到其它服...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问