数据库存储过程

【什么是存储过程】

 

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集。经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程由 流控制 和 SQL语句书写，它可以接收参数、输出参数、返回单个或多个结果集以及返回值。

 

【存储过程的种类】

 

① 系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。

 

② 本地存储过程：用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。

 

③ 临时存储过程：分为两种存储过程：

一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它;

二是全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。

 

④ 远程存储过程：在SQL Server2005中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。

 

⑤ 扩展存储过程：扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。

 

【系统存储过程】

    系统存储过程是系统创建的存储过程，目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中，以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中，但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。

Ø 常用系统存储过程有：

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;

Ø系统存储过程示例：

 

--表重命名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%';

 

Ø 用户自定义存储过程

   1、 创建语法

create proc | procedure pro_name    [{@参数数据类型} [=默认值] [output],     {@参数数据类型} [=默认值] [output],     ....    ]as    SQL_statements

 

   2、 创建不带参数存储过程

--创建存储过程if (exists (select * from sys.objects where name = 'proc_get_student'))    drop proc proc_get_studentgocreate proc proc_get_studentas    select * from student;--调用、执行存储过程exec proc_get_student;

   3、 修改存储过程

--修改存储过程alter proc proc_get_studentasselect * from student;

   4、 带参存储过程

--带参存储过程if (object_id('proc_find_stu', 'P') is not null)    drop proc proc_find_stugocreate proc proc_find_stu(@startId int, @endId int)as    select * from student where id between @startId and @endIdgoexec proc_find_stu 2, 4;

   5、 带通配符参数存储过程

--带通配符参数存储过程if (object_id('proc_findStudentByName', 'P') is not null)    drop proc proc_findStudentByNamegocreate proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')as    select * from student where name like @name and name like @nextName;goexec proc_findStudentByName;exec proc_findStudentByName '%o%', 't%';

   6、 带输出参数存储过程

if (object_id('proc_getStudentRecord', 'P') is not null)    drop proc proc_getStudentRecordgocreate proc proc_getStudentRecord(    @id int, --默认输入参数    @name varchar(20) out, --输出参数    @age varchar(20) output--输入输出参数)as    select @name = name, @age = age  from student where id = @id and sex = @age;go-- declare @id int,        @name varchar(20),        @temp varchar(20);set @id = 7; set @temp = 1;exec proc_getStudentRecord @id, @name out, @temp output;select @name, @temp;print @name + '#' + @temp;

   7、 不缓存存储过程

--WITH RECOMPILE 不缓存if (object_id('proc_temp', 'P') is not null)    drop proc proc_tempgocreate proc proc_tempwith recompileas    select * from student;goexec proc_temp;

   8、 加密存储过程

--加密WITH ENCRYPTION if (object_id('proc_temp_encryption', 'P') is not null)    drop proc proc_temp_encryptiongocreate proc proc_temp_encryptionwith encryptionas    select * from student;goexec proc_temp_encryption;exec sp_helptext 'proc_temp';exec sp_helptext 'proc_temp_encryption';

   9、 带游标参数存储过程

if (object_id('proc_cursor', 'P') is not null)    drop proc proc_cursorgocreate proc proc_cursor    @cur cursor varying outputas    set @cur = cursor forward_only static for    select id, name, age from student;    open @cur;go--调用declare @exec_cur cursor;declare @id int,        @name varchar(20),        @age int;exec proc_cursor @cur = @exec_cur output;--调用存储过程fetch next from @exec_cur into @id, @name, @age;while (@@fetch_status = 0)begin    fetch next from @exec_cur into @id, @name, @age;    print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);endclose @exec_cur;deallocate @exec_cur;--删除游标

   10、 分页存储过程

---存储过程、row_number完成分页if (object_id('pro_page', 'P') is not null)    drop proc proc_cursorgocreate proc pro_page    @startIndex int,    @endIndex intas    select count(*) from product;        select * from (        select row_number() over(order by pid) as rowId, * from product     ) temp    where temp.rowId between @startIndex and @endIndexgo--drop proc pro_pageexec pro_page 1, 4----分页存储过程if (object_id('pro_page', 'P') is not null)    drop proc pro_stugocreate procedure pro_stu(    @pageIndex int,    @pageSize int)as    declare @startRow int, @endRow int    set @startRow = (@pageIndex - 1) * @pageSize +1    set @endRow = @startRow + @pageSize -1    select * from (        select *, row_number() over (order by id asc) as number from student     ) t    where t.number between @startRow and @endRow;exec pro_stu 2, 2;

Ø Raiserror

Raiserror返回用户定义的错误信息，可以指定严重级别，设置系统变量记录所发生的错误。

   语法如下：

Raiserror({msg_id | msg_str | @local_variable}  {, severity, state}  [,argument[,…n]]  [with option[,…n]])

   # msg_id:在sysmessages系统表中指定的用户定义错误信息

   # msg_str:用户定义的信息，信息最大长度在2047个字符。

   # severity：用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时，raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。

    任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用with log选项。

   # state：介于1至127直接的任何整数。State默认值是1。

raiserror('is error', 16, 1);
select * from sys.messages;--使用sysmessages中定义的消息raiserror(33003, 16, 1);raiserror(33006, 16, 1);

 

 

来源http://blog.csdn.net/yang3wei/article/details/6281255  

        http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html