sqlserver存储过程

发布时间：2020-12-12 12:48:09 所属栏目：MsSql教程来源：网络整理

导读：本文转自：http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html 对文章做了部分补充、注释与勘误 1.存储过程的概念 ? ? ?A.存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参

本文转自：http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html

对文章做了部分补充、注释与勘误

1.存储过程的概念

? ? ?A.存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。
? ? ?B.存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
? ? ?C.由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。
? ? D.同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、减轻网络负担。

2.存储过程的优点
? ? ?A.存储过程允许标准组件式编程
? ? ? ? 存储过程创建后可以在程序中被多次调用执行，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大的提高了程序的可移植性。

? ? ?B.存储过程能够实现较快的执行速度
? ? ? ? 如果某一操作包含大量的T-SQL语句代码，分别被多次执行，那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划。
而批处理的T-SQL语句每次运行都需要预编译和优化，所以速度就要慢一些。

? ? ?C.存储过程减轻网络流量
? ? ? ? 对于同一个针对数据库对象的操作，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句。从而减轻了网络流量，降低了网络负载。

? ? ?D.存储过程可被作为一种安全机制来充分利用
? ? ? ? 系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。

3.存储过程的缺点
? ? A.特别是在组件式编程中，由于大部分逻辑操作放在了存储过程中，组件服务器的计算压力转移到了数据库服务器
B.由于不同的数据库，语法格式有所不同，有的不支持存储过程，势必在数据库迁移时会有较大改动工作量

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

常用系统存储过程有：
exec sp_databases; ? ? --查看数据库
exec sp_tables; ? ? ? ?--查看表
exec sp_columns table_name;--查看列
exec sp_helpindex table_name;--查看索引
exec sp_helpconstraint student;--约束
exec sp_help [table_name] ? ?--查看表信息
exec sp_stored_procedures; ? ? --查看数据库存储过程
exec sp_helptext 'sp_stored_procedures|view_name';--查看存储过程(视图)创建、定义语句
exec sp_rename student,stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB,myDB;--更改数据库名称
exec sp_defaultdb 'master','myDB';--更改登录名的默认数据库
exec sp_helpdb; ? ? ? ? ? ? ?--数据库帮助，查询数据库信息
exec sp_helpdb database_name;--查看某个数据库信息
exec sp_refreshview 视图名?? --刷新单个视图

5.系统存储过程示例
--表重命名
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%';

6.用户自定义存储过程
? ? 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_student
go
create proc proc_get_student
as
select * from student;
--调用存储过程
exec proc_get_student;

3、创建带参存储过程
if (object_id('proc_find_stu','P') is not null)
? ? drop proc proc_find_stu
go
create proc proc_find_stu(@startId int,@endId int)
as
? ? select * from student where id between @startId and @endId
go
--调用存储过程
exec proc_find_stu 2,4;--2和4为参数

4.修改存储过程
alter proc proc_get_student
as
? ? select * from student;

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

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

? ? 6、带输出参数存储过程
if (object_id('proc_getStudentRecord','P') is not null)
drop proc proc_getStudentRecord
go
create 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 age = @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_temp
go
create proc proc_temp
with recompile
as
select * from student;
go
exec proc_temp;

? ?8、加密存储过程
--加密WITH ENCRYPTION?
if (object_id('proc_temp_encryption','P') is not null)
drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
select * from student;
go

exec proc_temp_encryption;--执行存储过程
exec sp_helptext 'proc_temp_encryption';--显示存储过程语句(加密后会提示存储过程已加密)

? ?9、带游标参数存储过程
if (object_id('proc_cursor','P') is not null)
drop proc proc_cursor
go
create proc proc_cursor
@cur cursor varying output --varying 目的待明确
as
set @cur = cursor forward_only static for
select Number,Name,[Type] from DeviceInfo;
open @cur;
go

declare @exec_cur cursor;
declare @Number varchar(20),
@Name varchar(20),
@Type varchar(20),
@mun int;
SET @mun =0;
exec proc_cursor @exec_cur output;--调用存储过程
fetch next from @exec_cur into @Number,@Name,@Type;
while (@@fetch_status = 0)--fetch into 取到数据则@@fetch_status = 0，否则为0
begin
print 'Number: ' + @Number+ ',Name: ' + @Name + ',TYPE: ' + @Type+' ?cursor'+ convert(char,@@fetch_status);
set @mun=@mun+1;
fetch next from @exec_cur into @Number,@Type;
end
print @mun
close @exec_cur; ? ? --关闭游标
deallocate @exec_cur;--释放游标

? ?10、分页存储过程
---存储过程、row_number完成分页
if (object_id('pro_page','P') is not null)
drop proc proc_cursor
go
create proc pro_page
@startIndex int,
@endIndex int
as
select count(*) from product; ? ?
select * from?
(
select row_number() over(order by pid) as rowId,* from product?
) temp
where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1,4
--
--分页存储过程
if (object_id('pro_page','P') is not null)
drop proc pro_stu
go
create 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;--between and 取到的数值包含两头的

exec pro_stu 2,2;

? ? 11、Raiserror 用户自定义错误消息 Raiserror返回用户定义的错误信息，可以指定严重级别，设置系统变量记录所发生的错误。语法如下： Raiserror({msg_id | msg_str | @local_variable} ?{,severity,state} ?[,argument[,…n]] ?[with option[,…n]] ) ? # msg_id:在sysmessages系统表中指定的用户定义错误信息用户定义错误消息的错误号应当大于 50000 ? # msg_str:用户定义的信息，信息最大长度在2047个字符。 ? # severity：用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时， ? ?raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。 19-25之间的安全级别需要使用with log选项。 [0,10]的闭区间内，不会跳到catch；如果是[11,19],则跳到catch；如果[20,无穷)，则直接终止数据库连接 ? # state： ? ? ? ?如果在多个位置引发相同的用户定义错误， ? ? ? ? ? ? 则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。 ? ? ? ? ? ? 介于 1 至 127 之间的任意整数。（state 默认值为1） ? ? ? ? ? ? 当state 值为 0 或大于 127 时会生成错误！ ?? ? #option ? 错误的自定义选项，可以是下表中的任一值： ? LOG ：在错误日志和应用程序日志中记录错误； ? NOWAIT：将消息立即发送给客户端； ? SETERROR：将 @@ERROR 值和 ERROR_NUMBER 值设置为 msg_id 或 50000； ? ? ? --范例 ? raiserror('is error',16,1); ? select * from sys.messages; ? --使用sysmessages中定义的消息 ? raiserror(33003,1); ? raiserror(33006,1);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!