SQL Server 数据库存储过程

目录

1.什么是存储过程

2.存储过程的分类

3.存储过程的创建和执行

3.1一般格式

3.2具体例子

4.存储过程的查看和修改

4.1一般格式

5.存储过程的重命名和删除

5.1一般格式

---

1.什么是存储过程

存储过程是一组为了完成特定功能的SQL语句的集合，它经编译后存储在数据库中，用户通过指定的调用方法执行之。存储过程具有名称，参数及返回值，并且可以嵌套调用。

2.存储过程的分类

（1）系统存储过程

系统存储过程主要存储在master数据库中，一般以“sp_”为前缀。

（2）用户自定义的存储过程

用户自定义存储过程是由用户创建并能够完成某些特定功能而编写的存储过程，它可以输入参数、向客户端返回表格或结果、消息等，也可以返回输出参数。

（3）扩展存储过程

扩展存储过程通常以“xp_”为前缀。

3.存储过程的创建和执行

3.1一般格式

create  procedure  存储过程名
[ @参数名 数据类型 [(长度)] [ ,……, ]]  @参数名 数据类型 [(长度)] output [ ,…… ]
[ with encryption ]     //可以为存储过程的创建文本加密
[ with recompile ]     //在每次执行时重新编译
as
T-SQL语句

execute 存储过程名[参数1,参数2 or 参数1=xx，参数2=xx]  [,接收参数 output]

说明：

（1）参数分为输入参数和输出参数，前者可以在定义参数时给其赋值作为默认值，后者要在后面加上output表示是输出参数

（2）execute命令表示执行存储过程，可以简写成exec，在执行时，若要接收存储过程执行后得到的参数，则需先定义一个参数作为接收参数，详见下面3.2中具体例子的例3

3.2具体例子

例1（不带参数）：

create  procedure  selStu
as
select * 
from student

例2（带输入参数）：

create procedure test2
@N1 varchar(10)='张乐' 
as
select * from 教师
where 姓名=@N1

exec test2 张三     //张三是输入参数

例3（带输出参数）：

create procedure test3 
@name nvarchar(4),@课程号_最好成绩 nvarchar(10) output
as
select @课程号_最好成绩=(
  select top 1 课程号 from Score 
       where 学号=(
         select 学号 from 学生 where 姓名=@name)
       order by 成绩 desc)

declare @best_grade nvarchar(10)   //先声明一个参数用来接收执行结果
exec test3 李四,@best_grade output  //在接收参数后面加output标识

4.存储过程的查看和修改

4.1一般格式

1. 查看存储过程

可以使用系统存储过程sp_helptext、sp_help和sp_depends查看存储过程的创建信息和创建文本。

如下：

Exec sp_depends 表        //显示存储过程所关联的数据表和字段信息。

Exec sp_help  表          //存储过程的所有者、类型、创建时间以及包含哪些参数等信息

Exec sp_helptext 表       //创建文本信息。创建时被加密的存储过程的创建文本不能被查看。

说实话，我还没太懂这个查看是什么意思，等用到了再深入了解吧，如果聪明的读者朋友知道的话，欢迎在评论区赐教^_^

        2.修改存储过程

修改和创建很像，就是把关键字 create 改成 alter 即可，如下：

alter  procedure  存储过程名
    [ @参数名 数据类型 [(长度)] [ ,……, ]]  @参数名 数据类型 [(长度)] output [ ,…… ]
    [ with encryption ]
    [ with recompile ]
    as
        T-SQL语句

5.存储过程的重命名和删除

5.1一般格式

1. 重命名存储过程

sp_rename [@objname=] ‘object_name’,
	[@newname=]’new_name’
	[,[@objtype=] ‘object_type’]

    2. 删除存储过程

	DROP PROCEDURE {procedure}	[,...n]

例子：

USE [jiaoxuedb]
GO
DROP PROCEDURE [dbo].[findScore],[dbo].[findSome]
GO