sql存储过程

SQL存储过程详解：创建、执行与优化

最新推荐文章于 2025-09-08 09:00:27 发布

原创最新推荐文章于 2025-09-08 09:00:27 发布 · 316 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

其他专栏收录该内容

15 篇文章

订阅专栏

本文详细介绍了SQL存储过程的类型、特点、创建、执行、修改和删除等核心概念。存储过程提供了代码重用性、高速执行和安全性，通过减少网络通信和授权机制增强数据库安全。示例展示了不同类型的存储过程创建，包括带参数、默认值和返回参数的情况，并解释了参数定义顺序的重要性。此外，还讨论了如何执行和修改存储过程以及相关数据库管理操作。

如果在看之前，你有一定的编程基础，这个东西就相当于一个函数

类型：

系统存储过程，存储在master数据库中，可以作为命令执行各种操作，以sp_开头。
用户自定义的存储过程：用户创建，具有一定功能。

所以说，千万不能在数据库中定义一个已sp_开头的存储过程

特点

代码重用性
高速性
第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。
减少网络通信量
一条语句就能执行一个存储过程。
安全性
通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。

创建

CREATE  PROC[EDURE]   procedure_name[;number]
[@parameter data_type [=default] ][,…n]
AS sql_statement[,…n]

Procedure_name:指定存储过程的名称；
Number:对同名的存储过程指定一个序号；
@parameter date_type [=default] ：指定存储过程的参数。 Parameter为参数名称， date_type参数的数据类型， [=default] 用于指定参数的默认值。默认值必须是常数或空值。
AS sql_statement：过程中要包含的T-SQL语句

示例：显示学生平均分

create procedure student_avg
as
	select id,avg(grade)
	from score
	group by id

带参存储过程
示例：创建一个存储过程SCORE，求某个学生（按学号）某门课（课程号）的分数

create proc score
@SNO varchar(4)
@CNO varchar(10)
as
	select id,score
	from S_C
	where SNO=@SNO and CNO=@CNO

当用户调用存储过程，但是没有给全参数时，需要以默认值补全，默认值是一个常量
示例：创建一个存储过程SCORE2，求某个学生（按学号）某门课（课程号）的分数，其中默认课程为‘001’

create proc score2
@SNO varchar(4)
@CNO varchar(10)='001'
as
	select SNO,SCORE
	from S_C
	where
		SNO=@SNO and CNO=@CNO

这里我们需要注意的是定义具有默认值的参数的位置。
可以考虑如下情况，如果我们把具有默认值的参数定义在前

create proc score2
@CNO varchar(10)='001'
@SNO varchar(4)
as
	select SNO,SCORE
	from S_C
	where
		SNO=@SNO and CNO=@CNO

那么在调用的时候想要省略默认值调用，则无法判断是要重新指定默认值，还是给其他参数赋值，所以若要在一个存储过程中使用带默认值的参数，应将这些参数放到参数列表的最后

带返回参数存储过程
对传出参数用OUTPUT关键字修饰
示例：创建一个存储过程，完成除法功能，并可以将商传出

CREATE PROC DIVIDE
@DIVIDEND INT,
@DIVISOR INT,
@QUOTIENT INT OUTPUT
AS
SELECT @QUOTIENT=@DIVIDEND/@DIVISOR

执行

[EXEC[UTE]]
[@return_status=]procedure_name
[[@parameter=]{vale|@variable}…]

示例：

EXEC student_avg

执行带参存储过程
不指定参数名时，按照存储过程参数定义顺序依次传递

exec score '0001','math'

也可以任意给定参数顺序，但要说明参数名(按名传递)

exec score @CNO='math',@SNO='0001'

一旦一个参数用了按名传递，则其后所有的参数也必须都按名传递

对于有默认值的存储过程，默认值可以被重新指定新值，也可以被省略以使用其默认值

exec score2 '0001'
exec score2 '0001','math'

带返回参数存储过程

exec DIVIDE 20,4 @res output

其中output参数不可省略。如果省略，那么程序将会调用一个无返回参数的存储过程
使用output参数可以实现类似于传址调用和传参调用的功能。如果某参数被定义为OUTPUT，一旦该参数在存储过程中的值发生变化，与之相对应的调用者的变量也会发生变化，并在存储过程执行结束后，在调用者中保持改变后的值，这种调用被称作：“地址调用”。如果该参数没有被定义为OUTPUT ，在存储过程中该参数的改变不会影响到存储过程执行后的变量值，称为：“传值调用”。

修改

ALTER PROC[EDURE]   procedure_name[;number]
[@parameter data_type [=default] ][,…n]
AS sql_statement[,…n]

删除

DROP RPOCEDURE proc_name[,n]

其他

数据库中定义了一些系统存储过程，用于处理存储过程

查看存储过程的定义
```
EXEC  sp_helptext   procedure_name
```
查看存储过程的其它（所有者、类型、创建日期和参数）等属性
```
EXEC  sp_help  procedure_name
```
重命名
```
EXEC sp_rename old_name,new_name
```