数据库存储过程详解-优快云博客

一.什么是数据库存储过程？
     存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
用来执行管理任务或应用复杂的业务规则。
     存储过程可以带参数，也可以返回结果。
     存储过程可以包含数据操纵语句、变量、逻辑控制语句等。
     存储过程是一组预编译的SQL语句，它可以包含数据操纵语句、变量、逻辑控制语句等
     存储过程允许带参数，参数分为：
     输入参数
     输出参数
     其中，输入参数可以有默认值

二.存储过程的优点：
（1）存储过程的能力大大增强了SQL语言的功能和灵活性。
　　（2）可保证数据的安全性和完整性。
　　（3）通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。
　　（4）通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。
　　（5）在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。
　　（6）可以降低网络的通信量。
　　（7）使体现企业规则的运算程序放入数据库服务器中，以便集中控制。
（8）存储过程可以分为系统存储过程、扩展存储过程和用户自定义的存储过程；
三.存储过程的分类
（1）数据库系统存储过程：数据库自带

我们先来看一下系统存储过程，系统存储过程由系统定义，主要存放在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 --分离数据库

（2）用户自定义数据库存储过程：由用户根据需要自定义

四.使用存储过程实例：

   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