当前位置：Gxlcms > 数据库问题 > SQL Server存储过程的使用

SQL Server存储过程的使用

时间：2021-07-01 10:21:17 帮助过：13人阅读

　　　　在很多情况下，一些代码会被开发者重复编写多次，如果每次都编写相同功能的代码，不但繁琐，而且容易出错，

　　　　如果不使用的话，SQL Server逐条的执行语句会降低系统的运行效率。

 

  • 存储过程的优点：
  1. 存储过程加快系统运行速度，存储过程只在创建时编译，以后每次执行时不需要重新编译。
  2. 存储过程可以封装复杂的数据库操作，简化操作流程，例如对多个表的更新，删除等。
  3. 可实现模块化的程序设计，存储过程可以多次调用，提供统一的数据库访问接口，改进应用程序的可维护性。
  4. 存储过程可以增加代码的安全性，对于用户不能直接操作存储过程中引用的对象，SQL  Server可以设定用户对指定存储过程的执行权限。
  • 存储过程的缺点：
  1. 数据库移植不方便，存储过程依赖与数据库管理系统， SQL Server 存储过程中封装的操作代码不能直接移植到其他的数据库管理系统中。
  2. 不支持面向对象的设计，
  3. 代码可读性差，不易维护。不支持集群。

  存储过程分类

  • 系统存储过程

　　系统存储过程是 SQL Server系统自身提供的存储过程，可以作为命令执行各种操作。

　　　　系统存储过程主要用来从系统表中获取信息，使用系统存储过程完成数据库服务器的管理工作，为系统管理员提供帮助，

　　　　为用户查看数据库对象提供方便，系统存储过程位于数据库服务器中，并且以sp_开头

　　　　系统存储过程创建并存放在与系统数据库master中，一些系统存储过程只能由系统管理员使用，而有些系统存储过程通过授权可以被其它用户所使用。

 

  • 用户存储过程(自定义存储过程)

　　　　自定义存储过程即用户使用T_SQL语句编写的、为了实现某一特定业务需求，在用户数据库中编写的T_SQL语句集合，

　　　　自定义存储过程可以接受输入参数、向客户端返回结果和信息，返回输出参数等。创建自定义存储过程时，

　　　　存储过程名前加上"##"表示创建了一个全局的临时存储过程;存储过程前面加上"#"时，表示创建的局部临时存储过程。

　　　　局部临时存储过程只能在创建它的回话中使用，会话结束时，将被删除。这两种存储过程都存储在tempdb数据库中。

 

　　　　T_SQL:存储过程是值保存的T_SQL语句集合，可以接受和返回用户提供的参数，存储过程也可能从数据库向客户端应用程序返回数据。

 

   创建存储过程

  1. <span style="color: #008080;"> 1</span> <span style="color: #000000;">use bookdb;
  2. </span><span style="color: #008080;"> 2</span> --<span style="color: #000000;">创建测试books表
  3. </span><span style="color: #008080;"> 3</span> <span style="color: #000000;">create table books (
  4. </span><span style="color: #008080;"> 4</span>     book_id <span style="color: #0000ff;">int</span> identity(1,1<span style="color: #000000;">) primary key,
  5. </span><span style="color: #008080;"> 5</span>     book_name varchar(20<span style="color: #000000;">),
  6. </span><span style="color: #008080;"> 6</span>     book_price <span style="color: #0000ff;">float</span><span style="color: #000000;">,
  7. </span><span style="color: #008080;"> 7</span>     book_author varchar(10<span style="color: #000000;">)
  8. </span><span style="color: #008080;"> 8</span> <span style="color: #000000;">);
  9. </span><span style="color: #008080;"> 9</span> --<span style="color: #000000;">插入测试数据
  10. </span><span style="color: #008080;">10</span> <span style="color: #000000;">insert into books (book_name,book_price,book_author) values
  11. </span><span style="color: #008080;">11</span> (‘论语‘,25.6,‘孔子‘<span style="color: #000000;">),                                   
  12. </span><span style="color: #008080;">12</span>  (‘平凡的世界‘,35.8,‘路遥‘<span style="color: #000000;">);
  13. </span><span style="color: #008080;">13</span>

 

  • 创建无参存储过程

  1. <span style="color: #008080;">1</span> --1<span style="color: #000000;">.创建无参存储过程
  2. </span><span style="color: #008080;">2</span> <span style="color: #0000ff;">if</span> (exists (select * from sys.objects where name = ‘getAllBooks‘<span style="color: #000000;">))
  3. </span><span style="color: #008080;">3</span> <span style="color: #000000;">    drop proc proc_get_student
  4. </span><span style="color: #008080;">4</span> <span style="color: #000000;">go
  5. </span><span style="color: #008080;">5</span> <span style="color: #000000;">create procedure getAllBooks
  6. </span><span style="color: #008080;">6</span> <span style="color: #000000;">as
  7. </span><span style="color: #008080;">7</span> select *<span style="color: #000000;"> from books;
  8. </span><span style="color: #008080;">8</span> --<span style="color: #000000;">调用,执行存储过程
  9. </span><span style="color: #008080;">9</span> exec getAllBooks;

    • 修改存储过程

  1. <span style="color: #008080;">1</span> <span style="color: #000000;">alter procedure dbo.getAllBooks 
  2. </span><span style="color: #008080;">2</span> <span style="color: #000000;">as
  3. </span><span style="color: #008080;">3</span> select book_author  from books;<strong><strong>　　　　　</strong></strong><strong>　　</strong>

    • 删除存储过程

　　　　 drop procedure getAllBooks; 

技术分享图片

    • 重命名存储过程

 　　　　sp_rename getAllBooks,proc_get_allBooks; 

 

  • 创建带参数的存储过程

　　存储过程的参数分为两种：输入参数和输出参数

　　输入参数：用于向存储过程传入值，类似java语言或则c中的值传递。

　　输出参数：用于调用存储过程后，参会结果，类似java语言的按引用传递。

　　值传递和引用传递区别:

  • 基本数据类型赋值属于值传递；引用类型之间赋值属于引用传递。
  • 值传递传递的是实实在在的变量值；引用传递传递的是对象的引用地址。
  • 值传递后，两个变量改变的是各自的值；引用传递后，两个引用改变的是同一个对象的状态

　　（a）带一个参数存储过程

  1. <span style="color: #008080;">1</span> <span style="color: #0000ff;">if</span> (exists (select * from sys.objects where name = ‘searchBooks‘<span style="color: #000000;">))
  2. </span><span style="color: #008080;">2</span> <span style="color: #000000;">    drop proc searchBooks
  3. </span><span style="color: #008080;">3</span> <span style="color: #000000;">go
  4. </span><span style="color: #008080;">4</span> create proc searchBooks(@bookID <span style="color: #0000ff;">int</span><span style="color: #000000;">)
  5. </span><span style="color: #008080;">5</span> <span style="color: #000000;">as
  6. </span><span style="color: #008080;">6</span>     --<span style="color: #000000;">要求book_id列与输入参数相等
  7. </span><span style="color: #008080;">7</span>     select * from books where book_id=<span style="color: #000000;">@bookID;
  8. </span><span style="color: #008080;">8</span> --<span style="color: #000000;">执行searchBooks
  9. </span><span style="color: #008080;">9</span> exec searchBooks 1;

　　

　　（b）带2个参数存储过程

  1. <span style="color: #008080;"> 1</span> <span style="color: #0000ff;">if</span> (exists (select * from sys.objects where name = ‘searchBooks1‘<span style="color: #000000;">))
  2. </span><span style="color: #008080;"> 2</span> <span style="color: #000000;">    drop proc searchBooks1
  3. </span><span style="color: #008080;"> 3</span> <span style="color: #000000;">go
  4. </span><span style="color: #008080;"> 4</span> <span style="color: #000000;">create proc searchBooks1(
  5. </span><span style="color: #008080;"> 5</span>     @bookID <span style="color: #0000ff;">int</span><span style="color: #000000;">,
  6. </span><span style="color: #008080;"> 6</span>     @bookAuth varchar(20<span style="color: #000000;">)
  7. </span><span style="color: #008080;"> 7</span> <span style="color: #000000;">)
  8. </span><span style="color: #008080;"> 8</span> <span style="color: #000000;">as
  9. </span><span style="color: #008080;"> 9</span>     --<span style="color: #000000;">要求book_id和book_Auth列与输入参数相等
  10. </span><span style="color: #008080;">10</span>     select * from books where book_id=@bookID and book_auth=<span style="color: #000000;">@bookAuth;
  11. </span><span style="color: #008080;">11</span> exec searchBooks1 1,‘金庸‘;

　　（c）创建有返回值的存储过程

  1. <span style="color: #008080;"> 1</span> <span style="color: #0000ff;">if</span> (exists (select * from sys.objects where name = ‘getBookId‘<span style="color: #000000;">))
  2. </span><span style="color: #008080;"> 2</span> <span style="color: #000000;">    drop proc getBookId
  3. </span><span style="color: #008080;"> 3</span> <span style="color: #000000;">go
  4. </span><span style="color: #008080;"> 4</span> <span style="color: #000000;">create proc getBookId(
  5. </span><span style="color: #008080;"> 5</span>     @bookAuth varchar(20),--<span style="color: #000000;">输入参数,无默认值
  6. </span><span style="color: #008080;"> 6</span>     @bookId <span style="color: #0000ff;">int</span> output --输入/<span style="color: #000000;">输出参数 无默认值
  7. </span><span style="color: #008080;"> 7</span> <span style="color: #000000;">)
  8. </span><span style="color: #008080;"> 8</span> <span style="color: #000000;">as
  9. </span><span style="color: #008080;"> 9</span>     select @bookId=book_id from books where book_auth=<span style="color: #000000;">@bookAuth
  10. </span><span style="color: #008080;">10</span> --<span style="color: #000000;">执行getBookId这个带返回值的存储过程
  11. </span><span style="color: #008080;">11</span> declare @id <span style="color: #0000ff;">int</span> --<span style="color: #000000;">声明一个变量用来接收执行存储过程后的返回值
  12. </span><span style="color: #008080;">12</span> exec getBookId ‘孔子‘<span style="color: #000000;">,@id output
  13. </span><span style="color: #008080;">13</span> select @id as bookId;--as是给返回的列值起一个名字

　　（d）创建带通配符的存储过程

  1. <span style="color: #008080;"> 1</span> <span style="color: #0000ff;">if</span> (exists (select * from sys.objects where name = ‘charBooks‘<span style="color: #000000;">))
  2. </span><span style="color: #008080;"> 2</span> <span style="color: #000000;">    drop proc charBooks
  3. </span><span style="color: #008080;"> 3</span> <span style="color: #000000;">go
  4. </span><span style="color: #008080;"> 4</span> <span style="color: #000000;">create proc charBooks(
  5. </span><span style="color: #008080;"> 5</span>     @bookAuthor varchar(20)=‘金%‘<span style="color: #000000;">,
  6. </span><span style="color: #008080;"> 6</span>     @bookName varchar(20)=‘%‘
  7. <span style="color: #008080;"> 7</span> <span style="color: #000000;">)
  8. </span><span style="color: #008080;"> 8</span> <span style="color: #000000;">as 
  9. </span><span style="color: #008080;"> 9</span>     select *<span style="color: #000000;"> from books where book_author like @bookAuth and book_name like @bookName;
  10. </span><span style="color: #008080;">10</span> --<span style="color: #000000;">执行存储过程charBooks
  11. </span><span style="color: #008080;">11</span> exec  charBooks    ‘孔%‘,‘论%‘;

 

　　　　

　　（e）创建分页存储过程

  1. <span style="color: #008080;"> 1</span> <span style="color: #0000ff;">if</span> (object_id(‘book_page‘, ‘P‘) is not <span style="color: #0000ff;">null</span><span style="color: #000000;">)
  2. </span><span style="color: #008080;"> 2</span> <span style="color: #000000;">    drop proc book_page
  3. </span><span style="color: #008080;"> 3</span> <span style="color: #000000;">go
  4. </span><span style="color: #008080;"> 4</span> <span style="color: #000000;">create proc book_page(
  5. </span><span style="color: #008080;"> 5</span>     @TableName varchar(50),            --<span style="color: #000000;">表名
  6. </span><span style="color: #008080;"> 6</span>     @ReFieldsStr varchar(200) = ‘*‘,   --字段名(全部字段为*<span style="color: #000000;">)
  7. </span><span style="color: #008080;"> 7</span>     @OrderString varchar(200),         --排序字段(必须!<span style="color: #000000;">支持多字段不用加order by)
  8. </span><span style="color: #008080;"> 8</span>     @WhereString varchar(500) =N‘‘,  --<span style="color: #000000;">条件语句(不用加where)
  9. </span><span style="color: #008080;"> 9</span>     @PageSize <span style="color: #0000ff;">int</span>,                     --<span style="color: #000000;">每页多少条记录
  10. </span><span style="color: #008080;">10</span>     @PageIndex <span style="color: #0000ff;">int</span> = 1 ,               --<span style="color: #000000;">指定当前为第几页
  11. </span><span style="color: #008080;">11</span>     @TotalRecord <span style="color: #0000ff;">int</span> output            --<span style="color: #000000;">返回总记录数
  12. </span><span style="color: #008080;">12</span> <span style="color: #000000;">)
  13. </span><span style="color: #008080;">13</span> <span style="color: #000000;">as
  14. </span><span style="color: #008080;">14</span> <span style="color: #000000;">begin
  15. </span><span style="color: #008080;">15</span>      --<span style="color: #000000;">处理开始点和结束点
  16. </span><span style="color: #008080;">16</span>     Declare @StartRecord <span style="color: #0000ff;">int</span><span style="color: #000000;">;
  17. </span><span style="color: #008080;">17</span>     Declare @EndRecord <span style="color: #0000ff;">int</span><span style="color: #000000;">; 
  18. </span><span style="color: #008080;">18</span>     Declare @TotalCountSql nvarchar(500<span style="color: #000000;">); 
  19. </span><span style="color: #008080;">19</span>     Declare @SqlString nvarchar(2000<span style="color: #000000;">);    
  20. </span><span style="color: #008080;">20</span>     set @StartRecord = (@PageIndex-1)*@PageSize + 1
  21. <span style="color: #008080;">21</span>     set @EndRecord = @StartRecord + @PageSize - 1 
  22. <span style="color: #008080;">22</span>     SET @TotalCountSql= N‘select @TotalRecord = count(*) from ‘ + @TableName;--<span style="color: #000000;">总记录数语句
  23. </span><span style="color: #008080;">23</span>     SET @SqlString = N‘(select row_number() over (order by ‘+ @OrderString +‘) as rowId,‘+@ReFieldsStr+‘ from ‘+ @TableName;--<span style="color: #000000;">查询语句
  24. </span><span style="color: #008080;">24</span>     --
  25. <span style="color: #008080;">25</span>     IF (@WhereString! = ‘‘ or @WhereString!=<span style="color: #0000ff;">null</span><span style="color: #000000;">)
  26. </span><span style="color: #008080;">26</span> <span style="color: #000000;">        BEGIN
  27. </span><span style="color: #008080;">27</span>             SET @TotalCountSql=@TotalCountSql + ‘  where ‘+<span style="color: #000000;"> @WhereString;
  28. </span><span style="color: #008080;">28</span>             SET @SqlString =@SqlString+ ‘  where ‘+<span style="color: #000000;"> @WhereString;            
  29. </span><span style="color: #008080;">29</span> <span style="color: #000000;">        END
  30. </span><span style="color: #008080;">30</span>     --<span style="color: #000000;">第一次执行得到
  31. </span><span style="color: #008080;">31</span>     --IF(@TotalRecord is <span style="color: #0000ff;">null</span><span style="color: #000000;">)
  32. </span><span style="color: #008080;">32</span>     --<span style="color: #000000;">   BEGIN
  33. </span><span style="color: #008080;">33</span>            EXEC sp_executesql @totalCountSql,N‘@TotalRecord int out‘,@TotalRecord output;--<span style="color: #000000;">返回总记录数
  34. </span><span style="color: #008080;">34</span>     --<span style="color: #000000;">  END
  35. </span><span style="color: #008080;">35</span>     ----<span style="color: #000000;">执行主语句
  36. </span><span style="color: #008080;">36</span>     set @SqlString =‘select * from ‘ + @SqlString + ‘) as t where rowId between ‘ + ltrim(str(@StartRecord)) + ‘ and ‘ +<span style="color: #000000;">  ltrim(str(@EndRecord));
  37. </span><span style="color: #008080;">37</span> <span style="color: #000000;">    Exec(@SqlString)    
  38. </span><span style="color: #008080;">38</span> <span style="color: #000000;">END
  39. </span><span style="color: #008080;">39</span> --<span style="color: #000000;">调用分页存储过程book_page
  40. </span><span style="color: #008080;">40</span> exec book_page ‘books‘,‘*‘,‘book_id‘,‘‘,3,1,0<span style="color: #000000;">;
  41. </span><span style="color: #008080;">41</span> 
  42. <span style="color: #008080;">42</span> --
  43. <span style="color: #008080;">43</span> declare @totalCount <span style="color: #0000ff;">int</span>
  44. <span style="color: #008080;">44</span> exec book_page ‘books‘,‘*‘,‘book_id‘,‘‘,3,1<span style="color: #000000;">,@totalCount output; 
  45. </span><span style="color: #008080;">45</span> select @totalCount as totalCount;--总记录数。

 

SQL Server存储过程的使用

标签：set   查看   begin   src   sel   更新   tsql   全局   不能   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！