编写 T-SQL 存储过程
介绍
存储过程是存储在数据库中的 Transact-SQL 语句的集合。它们用于封装经常使用的查询，例如条件语句、循环和其他强大的编程功能。
存储过程类似于高级编程语言中的函数。它们支持输入和输出参数以及返回值。存储过程可以返回数据行或单个值。
存储过程的类型
存储过程有两种类型：
- 系统存储过程
- 扩展存储过程
这两种类型都是在 SQL Server 中预先构建的。两者之间的主要区别在于，系统存储过程的代码是用 T-SQL 编写的，并在每个 SQL Server 安装中包含的主数据库中找到。然而，系统扩展存储过程的代码是用本机代码（通常是 C++）编写的，并通过动态链接库提供。
创建存储过程
要创建存储过程，请使用T-SQL 语句CREATE PROCEDURE 。
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
CREATE PROCEDURE语句必须是 T-SQL 批处理中的唯一语句。从AS关键字到脚本结尾或批处理结尾（使用批处理分隔符，如GO）的所有语句都将成为存储过程的主体。
替换存储过程
您不能使用CREATE PROC语句替换过程。您需要使用ALTER PROCEDURE语句明确更改它，或者删除它然后重新创建它。
ALTER PROCEDURE procedure_name
AS
sql_statement
GO;
执行存储过程
EXECUTE语句用于执行存储过程。建议在执行存储过程时使用架构名称限定过程名称。这有助于数据库引擎提高其性能，因为它不必搜索多个架构来查找所需的过程。此外，如果数据库在不同的架构中具有同名的过程，它还可以防止您执行错误的过程。
EXECUTE schemaname.procedurename;
删除存储过程
要从数据库中删除存储过程，请使用DROP PROCEDURE语句。要删除系统扩展存储过程，请使用sp_dropextendedproc过程。
DROP PROCEDURE schemaname.procedurename;
参数化存储过程
参数化存储过程允许您将值传入和传出存储过程。这使得存储过程可重复使用。
输入参数
输入参数用于在存储过程和调用存储过程的代码之间交换数据。存储过程接受输入参数，就像高级语言中将参数传递给函数一样。
create procedure getEmployeeDetails
@empid int
as
select name, address, phone
from employee
where emplyeeid = @empid;
Go
注意empid如何在存储过程内部传递以及如何通过过程的参数在查询中使用。
输出参数
输出参数用于存储过程将值传回给调用者。要在 T-SQL 中使用输出参数，必须在CREATE PROCEDURE语句和EXECUTE语句中指定OUTPUT关键字。
create procedure getEmployeeName
@empid INT
@empname varchar(50)
as
begin
select @empname = name
from employee
where emplyeeid = @empid;
end
Go
示例
考虑下面的员工表：
| ID | 姓名 | 地址 | 年龄 | 电话 |
|---|---|---|---|---|
| 1 | 主 | 美国 | 三十五 | 11111111 |
| 2 | 布赖恩 | 美国 | 三十 | 22222222 |
| 3 | 莱蒂 | 美国 | 三十二 | 33333333 |
您可以使用以下脚本来设置测试数据：
Create table employee (
id int,
name varchar(50),
age int,
address varchar(100),
phone int
);
GO
insert into employee values (1, 'dom', 35, 'USA', 1111111), (2, 'brian', 30, 'USA', 222222), (3, 'letty', 32, 'USA', 3333333)
select * from employee
示例 1：返回年龄超过 31 岁的人员列表的存储过程。
CREATE PROCEDURE getmployeeByage
AS
select * from employee
where age > 31;
GO
执行以下命令来调用该过程
execute dbo.getmployeeByage
| ID | 姓名 | 地址 | 年龄 | 电话 |
|---|---|---|---|---|
| 1 | 主 | 美国 | 三十五 | 11111111 |
| 3 | 莱蒂 | 美国 | 三十二 | 33333333 |
示例 2：修改过程 getmployeeByage 以将年龄作为参数，而不是在 T-SQL 代码中对其进行硬编码。
ALTER PROCEDURE getmployeeByAge
@age int
AS
select * from employee
where age > @age;
GO
注意参数@age在select语句的where条件中的使用方式。
例3：使用过程getmployeeByage，显示所有年龄大于30岁的员工。
execute dbo.getmployeeByAge 30
执行命令的结果如下：
| ID | 姓名 | 地址 | 年龄 | 电话 |
|---|---|---|---|---|
| 1 | 主 | 美国 | 三十五 | 11111111 |
| 3 | 莱蒂 | 美国 | 三十二 | 33333333 |
例 4：编写一个返回员工平均年龄的过程。
CREATE PROCEDURE getAvgAge
AS
select avg(age) from employee
GO
当您执行该过程时，您应该得到结果 32。
例 5：修改过程 getAvgAge 以使用输出参数返回结果。
<span class="hljs-keyword"免责声明：本内容来源于第三方作者授权、网友推荐或互联网整理，旨在为广大用户提供学习与参考之用。所有文本和图片版权归原创网站或作者本人所有，其观点并不代表本站立场。如有任何版权侵犯或转载不当之情况，请与我们取得联系，我们将尽快进行相关处理与修改。感谢您的理解与支持！
请先 登录后发表评论 ~