SqlServer存储过程详解

存储过程：

       存储过程，非常类似于Java语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句进行缓存，这样下次执

行的时候直接使用缓存中的语句，这样就可以提高存储过程的性能。

       一、存储过程的概念：

       存储过程（procedure）是一组为了完成特定功能的SQL语句集合 ，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。

       存储过程中可以包含逻辑控制语句和数据操纵语句(增删改查)，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

       由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。

同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

       1、优点：

       ①通过把处理封装在简单易用的单元中，简化复杂的操作存储过程创建后可以在程序中被多次调用执行，而不必重新编写该

存储过程的SQL语句。

       ②提高性能。使用存储过程比使用单独的SQL语句要快如果某一操作包含大量的SQL语句代码，分别被多次执行，那么存储

过程要比批处理的执行速度快得多。

       ③安全,调用者只需要知道如何调用指定的存储过程即可，而不用关心存储过程的内容，防止SQL注入。

       ④存储过程减轻网络流量对于同一个针对数据库对象的操作，这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当

在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句。从而减轻了网络流量,降低了网络负

载。

       2、缺点：

       ①编写复杂。

       ②如果没有相应的权限,你将无法创建存储过程。

       ③当服务器调用过多存储过程,用户访问量大了,那么压力就丢给数据库来解决,数据库压力会过大。

       ④过多的存储过程，优化过于麻烦。

---

       二、系统存储过程：

       系统存储过程是系统创建的存储过程,目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其

他的系统管理任务。 系统存储过程主要存储在master数据库中,以“sp”下划线开头的存储过程。

       尽管这些系统存储过程在master数据库中,但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建

新的数据库的时候被自动创建在当前数据库中。

       常用系统存储过程有：

存储过程	含义
exec sp_databases;	查看所有数据库
exec sp_helpdb;	查询数据库信息
exec sp_helpdb 数据名;	查询指定数据库信息
exec sp_renamedb ‘旧库名’, ‘新库名’;	更改数据库名称
exec sp_tables;	查询当前数据库的所有表
exec sp_columns 表名;	查看列
exec sp_help 表名;	返回表的所有信息
exec sp_helpIndex 表名;	查看索引
exec sp_helpConstraint 表名;	约束
exec sp_stored_procedures;	当前环境的所有存储
exec sp_helptext ‘存储过程’;	查看存储过程源码
exec sp_rename ‘旧名’, ‘新名’;	修改表、索引、列的名称
exec sp_defaultdb ‘旧库名’, ‘新库名’;	更改登录名的默认数据库

注意：exec 用于调用存储过程

 系统存储过程示例：

 ①表重命名语法：

exec sp_rename 'stu', 'stud';

 ②列重命名语法：

exec sp_rename '表名.旧列名', '新列名','column';

 ③重命名索引语法：

exec sp_rename N'student.idx_cid',N'idx_cidd', N'index';

 ④查询所有存储过程语法：

 补充: P 为 “存储过程”

select * from sys.objects where type = 'P';

---

       三、自定义存储过程：

   1、创建语法：

create proc | procedure 存储名(
  [{@参数 数据类型} [=默认值] [out|output],
   {@参数 数据类型} [=默认值] [out|output],
  ....]
)
as
begin
   SQL_statements
end
go

注意：

默认不写是输入变量；

out输出变量；

output输入输出变量；

参数可以写小括号中，如果没有参数，小括号可以省略不写；

   2、修改语法：

alter proc | procedure 存储过程名
as
beign
   sql语句;
end

   3、删除语法：

drop proc | procedure 存储过程名;

   4、调用语法：

** 不带参数的

exec 存储过程名;

** 带参数的

exec 存储过程名 参数1 out|output,参数2 out|output;