Sql 存储过程小入门

注：最近要接手一个工作任务，存储方面的，恶补下存储的知识先。

1、什么是存储过程

存储过程，简单说就是将执行常用的或者复杂的工作的Sql语句预先写好，并用一个制定的名称存储起来，需要时调用excel即可

2、为什么要用存储过程

不仅是在面试的时候，还有工作中接触的系统中都有很多关于存储的方面，所以简单查了下。(以下引用)

1）、优点：
1.由于应用程序随着时间推移会不断更改，增删功能，T－SQL过程代码会变得更复杂，StoredProcedure为封装此代码提供了一个替换位置。

2.执行计划（存储过程在首次运行时将被编译，这将产生一个执行计划－－ 实际上是 Microsoft SQL Server为在存储过程中获取由 T-SQL 指定的结果而必须采取的步骤的记录。）缓存改善性能。 ……..但sql server新版本，执行计划已针对所有
T-SQL 批处理进行了缓存，而不管它们是否在存储过程中，所以没比较优势了。

3.存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量。

4.使用存储过程使您能够增强对执行计划的重复使用，由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能。RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。

5.可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。

6.代码精简一致，一个存储过程可以用于应用程序代码的不同位置。

7.更好的版本控制，通过使用 Microsoft Visual SourceSafe 或某个其他源代码控制工具，您可以轻松地恢复到或引用旧版本的存储过程。

8.增强安全性： a、通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问； b、提高代码安全，防止 SQL注入（但未彻底解决，例如，将数据操作语言－－DML，附加到输入参数）； c、SqlParameter
类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证）。
2)缺点：
1.如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。
2.可移植性差 由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于
RDBMS 的中间层中可能是一个更佳的选择。
这个博客总结了很多，引用下，感谢博主

3、ADO.Net中如何执行存储过程

//设置cmd的CommandType属性（命令类型）为存储过程
cmd.ComemandType=CommandType.StoredProcedur;
//设置cmd的CommandText属性，值为数据库中存储过程的名称
cmd.CommandText="存储过程名称";
//创建传入存储的参数对象,参数名@IdPm，参数类型SqlDbType.Int
SqlParameter IdPm=new SqlParameter("@IdPm",SqlDbType.Int);
//给参数赋值
IdPm.Value=2;
cmd.Parameters.Add(IdPm);

4、附上可用的实例

/*1.在webshop数据库中创建查询指定图书信息的存储过程up_JavaBo okInfo.
use WebShop 
go

create procedure up_SQLBookInfo
@type char(10)
as
select * from [BookInfo    图书信息表] 
where B_name=@type
go*/

/*2.执行存储过程up_JavaBookInfo
USE [WebShop]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[up_SQLBookInfo]
        @type=N'oo1'

SELECT  'Return Value' = @return_value

GO
*/

/*3.在Library数据库中查看存储过程up_JavaBookInfo的属性。*/
/*4.编写一个存储过程up_BookInfoByPub,实现在BookInfo表查询出版社“001”的图书信息，
然后执行存储过程完成指定的查询。
use WebShop 
go

create procedure up_BookInfoByPub
@type char(3)
as
select * from BookInfo 
where P_ID =@type 
go
--执行存储过程

exec up_BookInfoByPub '001'
*/

/*5.编写一个存储过程，将指定图书号（TP3/2752）价格通过输出参数返回，然后通过执行存储过程验证其功能。

use WebShop 
go
--创建存储过程
create procedure up_BookPrice
@abc varchar(16),@bcd float output
as
select @bcd = BookInfo .B_price from BookInfo 
where B_ID =@abc 
go
--执行储存过程
declare @asd as float 
exec up_BookPrice 'TP3/2752' ,@asd  output
print ('返回的价格为：' @asd)
go

*/

/*6.编写一个存储过程，将存储过程是否执行成功的结果返回，然后通过执行存储过程验证其返回值。
use WebShop 
go

--创建存储过程
create procedure up_Return
@zxc varchar(20)
as
declare @asd float
select @asd=BookInfo .B_price 
from BookInfo 
where B_ID =@zxc 
return @asd
--执行存储过程
use WebShop 
go
declare @qwe int 
exec @qwe=up_return 'TP3/2752'
print('返回的价格为：' + convert(char(5),@qwe))  --convert(数据类型，变量)--

*/
/*7.查看存储过程up_RerurnPrice的信息。
use WebShop 
go
sp_help up_Return
*/
/*8.查看存储过程up_RerurnPrice的文本内容。
use WebShop 
go
sp_helptext up_Return
*/
/*9.删除存储过程up_BookInfoByPub.
drop procedure up_BookInfoByPub
*/

... prompt'''

5、网查常用系统存储

常用系统存储过程：

exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;
... prompt'''

2). 系统存储过程示例：

``` python
--表重命名
exec sp_rename 'stu', 'stud';
select * from stud;
--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd',
N'index';
exec sp_help 'student';
--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%'
and name like 'sp%';
... prompt'''
```