存储过程

来源：互联网发布：csv导入oracle数据库编辑：程序博客网时间：2024/06/04 19:00

1.存储过程的概念

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

2.存储过程的优点

A、存储过程允许标准组件式编程

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

B、存储过程能够实现较快的执行速度

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

C、存储过程减轻网络流量

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

D、存储过程可被作为一种安全机制来充分利用

系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。

3.系统存储过程

定义：系统存储过程是系统创建的存储过程，目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在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 master; --报告有关指定master数据库信息
exce sp_who --查看当前用户、会话、进程情况
exce sp_helpdb --报告有关指定数据库和所有数据库的信息
exce sp_monitor --显示有关SQL SERVER 的统计信息
例1： :--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

4.自定义存储过程

定义：必须具有权限才能创建存储过程，存储过程是架构作用域中的对象，只能在本地数据库中创建存储过程。

语法：create proc | procedure pro_name
[ {@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements
exec pro_name
一、不带参数存储过程及执行
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
二、带参数存储过程及执行
输入参数
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
exec proc_find_stu
输入参数带通配符
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;
exec proc_findStudentByName
输入参数同时输出参数
if (object_id('proc_getStudentRecord', 'P') is not null)
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(@id int=10, @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;
三、执行存储过程
exec proc_get_student ;
exec proc_find_stu 10, 20;

exec proc_findStudentByName '%o%', 't%';

exec proc_getStudentRecord @id, @name out, @temp output;

四、修改存储过程

alter proc proc_get_student

as

select * from student;

go

exec proc_findStudentByName

五、不缓存存储过程

定义：创建存储过程时在其定义中指定 WITH RECOMPILE 选项，表明 SQL Server 将不对该存储过程计划进行高速缓存；该存储过程将在每次执行时都重新编译。当存储过程的参数值在各次执行间都有较大差异，导致每次均需创建不同的执行计划时，可使用 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

六、加密存储过程

定义：给proc加密，之后proc将只能使用，上面有一个小锁头，这样存储过程用普通方法就不能看到源代码。有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

七、带游标参数存储过程

if (object_id('proc_cursor', 'P') is not null)

drop proc proc_cursor

go

create proc proc_cursor

@cur cursor varying output

as

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);

end

close @exec_cur;

deallocate @exec_cur;--删除游标

八、分页存储过程

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;
go
exec proc pro_stu

5.本地与全局存储过程

临时存储过程分为本地存储过程（#）和全局存储过程（##）当SQL SERVER关闭后，这些过程将不复存在。

由于每次tempdb会在每次SQL SERVER启动时重庆创建。

6.嵌套存储过程

定义：嵌套存储过程时指从一个存储过程调用另一个存储过程或执行托管代码。嵌套存储过程和托管代码引用最高可达32级，每当调用的存储过程或托管代码引用开始执行，嵌套级别就增加一级，执行完后，嵌套级别就减少一级。在T-SQL字符串执行@@NESTLEVEL函数返回为“1+当前嵌套级别“。用EXEC来嵌套。

7.管理存储过程

删除存储过程：drop proc pro_基本信息。如果另一个存储过程调用某个被删除的存储过程，数据库将执行调用进程时显示一条错误信息。但是，如果重庆定义了相同名称和参数的存储过程来替代已被删除的存储过程，那么引用该过程的其他过程仍能成功执行。可以先用sp_depends存储过程确定是否有对象依赖于此存储过程。

查看文本信息：sp_helptext pro_基本信息。返回整个存储过程的语句。

查看基本信息：exec sp_help pro_基本信息。返回存储过程的所有者、类型、创建时间，参数名、类型、长度等。

查看详细信息：exec sp_depends pro_基本信息。显示存储过程的名称、类型、更新。

修改存储过程：alter procedure pro_基本信息。

8.函数和存储过程的相同点与不同点

不同点：

1、标识符不同。函数的标识符为FUNCTION，过程为：PROCEDURE。
2、函数中有返回值，且必须返回，而过程没有返回值。
3、过程无返回值类型，不能将结果直接赋值给变量；函数有返回值类型，调用时，除在 select中，必须将返回值赋给变量。
4、函数可以在select语句中直接使用，而过程不能，例如：假设已有函数fun_getAVG() 返回number类型绝对值。那么select fun_getAVG(col_a) from table 这样是可以的。
5.函数分为表值函数跟标量函数。表值函数是经过一些sql语句方法最后返回一张表，标量函数是经过一些sql语句方法最后返回一个值。存储过程是经过一些sql语句方法既可以返回表也可以返回值，且限制相对较少，而函数方法有很多限制。
6.除了语法结构不同之外。存储过程在意的是过程，函数在意的是结果。当然，存储过程也可以用于复杂处理之后的结果处理。

相同点：

二者都可以有出参。

阅读全文

0 0