SQL Server存储过程简介

存储过程是由一个或多个T-SQL语句组成的语句组,它可以接受输入参数和输出参数且能向调用程序返回多个值;可以调用其他过程;可以向调用程序返回结果状态值以标记执行成功或失败;

使用存储过程的好处

  1. 减少服务器/客户端网络流量:过程多条命令进行单个批处理执行,只一次网络传输;
  2. 更强的安全性:防止SQL注入,也可以进行加密处理;
  3. 代码重复使用:可封装重复的数据库操作;
  4. 更易维护:更新存储过程脚本可不更新调用的服务端程序;
  5. 更好的性能:首次执行时会生成执行计划,供后续重复使用;

创建存储过程

存储过程创建语法如下:

USE Database;
GO
CREATE PROCEDURE getStudents
    @num int,
    @name  varchar(32)
AS
    DECLARE @condi varchar(32);
    SET @condi = @name + '%';
    select top (@num) * from student
        where name like @condi;
GO

上述存储过程有两个参数,表示查询以name以@name开头的前@num条记录,如传入@num=10,
@name='李',则会返回前10条学生姓名以‘李’开头的学生记录。

执行存储过程

如果存储过程不带参数: exec proc_name;

如果存储过程带参数,则按方法签名传入参数即可,如上述例子:
exec getStudents 10, '李';

删除存储过程

删除存储过程跟删除数据库、表一样,使用DROP关键字。
如: DROP PROCEDURE getStudents;

修改存储过程

修改存储过程既可以使用alter关键字,也可现将原存储过程删除再新建。
如:

(1)使用Alter:

ALTER PROCEDURE getStudents
AS 
    select * from student;
GO

(2)先删除再创建:

IF OBJECT_ID('getStudents', 'P') IS NOT NULL
    DROP PROCEDURE getStudents;
GO
CREATE PROCEDURE getStudents
AS
    select * from student;
GO
 

以上两种方式效果一样。

从存储过程中返回数据

存储过程中使用OUTPUT关键字来返回数据。看例子:

IF OBJECT_ID('getStudentName', 'P') IS NOT NULL
    DROP PROCEDURE getStudentName;
GO
CREATE PROCEDURE getStudentName
    @name nvarchar(32) OUTPUT
AS
    select @name=name from student where name='ray';
RETURN
GO

改过程将student的name返回给@name变量,调用如下:

DECLARE @RES nvarchar(32);
EXEC getStudentName @name=@res OUTPUT;
PRINT @res;

有时,存储过程OUTPUT参数经常与数据库游标(cursor)结合使用,有关用法请参考.

常用系统存储过程

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;--数据库帮助,查询数据库信息
exec sp_helpdb master;

--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

参考资料

标签: sql server, procedure, 存储过程

添加新评论