数据库之存储过程

简介

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

 

优点

1.存储过程的能力大大增强了SQL语言的功能和灵活性。

2.可保证数据的安全性和完整性。

• 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。

• 通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。

3.创建时已经检查了语法；第一次执行的时候执行计划被创建，被编译,再次执行时不需要重检查语法、不需要重编译、根据已经缓存的计划来决定是否需要重创建执行计划

4.减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。

5.重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。

 

存储过程种类

存储过程可以分为系统存储过程、扩展存储过程和用户自定义的存储过程

系统存储过程

我们先来看一下系统存储过程，系统存储过程由系统定义，主要存放在MASTER数据库中，名称以"SP"开头或以"XP"开头。尽管这些系统存储过程在MASTER数据库中，

　 但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。

 

常用系统存储过程有：

exec sp_databases; --查看数据库

exec sp_tables; --查看表

exec sp_columns student;--查看列

exec sp_helpIndex student;--查看索引

exec sp_helpConstraint student;--约束

exec sp_helptext 'sp_stored_procedures';--查看存储过程创建定义的语句

exec 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_attach_db --附加数据库

exec sp_detach_db --分离数据库

 

存储过程语法

CREATE PROC[EDURE] 存储过程名 
@参数1 [数据类型]=[默认值] [OUTPUT] 
@参数2 [数据类型]=[默认值] [OUTPUT]
AS 
SQL语句
EXEC 过程名[参数]

存储过程案例

1.不带参数

create procedure proc_select_officeinfo--(存储过程名)
as select Id,Name from Office_Info--(sql语句)

exec proc_select_officeinfo--(调用存储过程)

2.带输入参数

create procedure procedure_proc_GetoffinfoById --(存储过程名)
@Id int--(参数名 参数类型)
as select Name from dbo.Office_Info where Id=@Id--(sql语句)

exec procedure_proc_GetoffinfoById 2--(存储过程名称之后,空格加上参数,多个参数中间以逗号分隔)

注:参数赋值是,第一个参数可以不写参数名称,后面传入参数,需要明确传入的是哪个参数名称

3.带输入输出参数

create procedure proc_office_info--（存储过程名）
@Id int,@Name varchar(20) output--(参数名 参数类型)传出参数要加上output
as 
begin
select @Name=Name from dbo.Office_Info where Id=@Id --(sql语句)
end

declare @houseName varchar(20) --声明一个变量,获取存储过程传出来的值
exec proc_office_info--(存储过程名)
4,@houseName output--(传说参数要加output 这边如果用@变量 = OUTPUT会报错，所以换一种写法)

select @houseName--(显示值)

4.带返回值的

create procedure proc_office_info--（存储过程名）
@Id int--(参数名 参数类型)
as 
begin
if(select Name from dbo.Office_Info where Id=@Id)=null --(sql语句)
begin
return -1
end
else
begin
return 1
end
end

declare @house varchar(20) --声明一个变量,获取存储过程传出来的值
exec @house=proc_office_info 2 --(调用存储过程,用变量接收返回值)
--注：带返回值的存储过程只能为int类型的返回值
print @house

 

参考资料：

《Oracle9i数据库基础》

浅谈数据库之存储过程 https://www.cnblogs.com/ToNi/p/4234370.html