一、存储过程介绍。
1.1 什么是存储过程?
存储过程是一种为了完成特定功能的一个或一组SQL语句集合。经编译后存储在服务器端的数据库中,
可以利用存储过程来加速SQL语句的执行效率。
1.2 存储过程的分类。
分为“系统存储过程”和“自定义存储过程”。
系统存储过程:
存在于master数据库中,其他数据库可以直接调用,不必在前面加上数据库名。
在创建其他新的数据库时,这些系统存储过程便会在新数据库中自动创建。
自定义存储过程:
为完成特定功能由用户自己创建的存储过程。
存储过程可以传参,也可以不传参。
存储过程可以有返回值,也可以没有返回值。
存储过程可以只有一个操作,也可以有多个操作。
1.3 存储过程的优点。
1.3.1 提高应用程序的通用性和可移植性。
允许多次调用,不必每次都去编写SQL语句,方便维护人员随时修改。
1.3.2 可以有效管理数据库权限,提高数据库安全性。
1.3.3 提高执行SQL语句的效率。
1.3.4 减轻服务器的负担。
1.4 存储过程的缺点。
1.4.1 存储过程占用数据库物理空间。
1.4.2 存储过程需要人员定期维护。
二、用脚本“创建/修改/删除/调用”存储过程。
假如有下面2张表,以它们举例说明:
产品信息表(ProductInfo):
标识列 Id int
产品编号 ProductNo varchar(50)
产品名称 ProductName nvarchar(100)
产品类型编号 ProductTypeId int
产品价格 ProductPrice decimal(18, 2)
产品数量 ProductCount int
产品备注 ProductRemark nvarchar(150)
产品类型表(ProductTypeInfo):
产品类型编号 ProductTypeId int
产品类型名称 ProductTypeName nvarchar(50)
use ProductManagementDB
go
-- 先创建好主表,指定主键
create table ProductTypeInfo
(
ProductTypeId int identity(1, 1) primary key not null,
ProductTypeName nvarchar(50) not null
)
go
-- 再创建好从表,指定外键
create table ProductInfo
(
Id int identity(10001, 1) primary key not null, -- 标识种子,自增量
ProductNo varchar(50) unique not null, -- 指定unique约束
ProductName nvarchar(100) not null,
ProductTypeId int not null foreign key references ProductTypeInfo(ProductTypeId), -- 指定ProductTypeInfo表中的ProductTypeId为外键
ProductPrice decimal(18, 2) default(0.00) check(ProductPrice > 9.99 and ProductPrice < 499.99) not null, -- 指定default约束和check约束
ProductCount int default(0) not null,
ProductRemark nvarchar(150) null
)
go
2.1 创建存储过程。
关键字:
"create" 和 "procedure / proc"。
语法:
create proc/procedure 存储过程名称 参数列表
as
begin
T-SQL语句
end
go
2.1.1 创建一个无参的存储过程。
例如:
create proc proc_productInfo
as
begin
select
pi.Id,
pi.ProductNo,
pi.ProductName,
pi.ProductTypeId,
pti.ProductTypeName,
pi.ProductPrice,
pi.ProductCount
from
ProductInfo pi
inner join
ProductTypeInfo pti
on
pi.ProductTypeId = pti.ProductTypeId
end
go
2.1.2 创建一个有参的存储过程。
存储过程内部:
定义局部变量,关键字"declare"。
设置单个变量,关键字"set"。
设置多个变量,关键字"select"。
declare @变量1 数据类型
declare @变量1 数据类型, @变量2 数据类型, @变量3 数据类型......
set @变量1 = 值1
select @变量1 = 值1, @变量2 = 值2, @变量3 = 值3......
例如:
declare @DefaultProductNo varchar(50)
declare @DefaultProductName nvarchar(100)
declare @DefaultProductTypeId int, @DefaultProductPrice decimal(18, 2), @DefaultProductCount int, @DefaultProductRemark nvarchar(150)
set DefaultProductNo = 'isbn123456'
set DefaultProductName = '毛泽东选集'
select @DefaultProductTypeId = 12, @DefaultProductPrice = 59.99, @DefaultProductCount = 288, @DefaultProductRemark = '一代伟人的气势磅礴作品!'
例如:
create proc proc_addProductInfo
@ProductNo varchar(50),
@ProductName nvarchar(100),
@ProductTypeId int,
@ProductPrice decimal(18, 2),
@ProductCount int,
@ProductRemark nvarchar(150)
as
begin
insert into ProductInfo(ProductNo, ProductName, ProductTypeId, ProductPrice, ProductCount, ProductRemark)
values(@ProductNo, @ProductName, @ProductTypeId, @ProductPrice, @ProductCount, @ProductRemark);
delete from ProductInfo where Id = 10025;
select
*
from
ProductInfo
end
go
2.2 调用存储过程。
关键字:
"execute / exec"。
语法:
exec 存储过程名 参数列表
例如:
2.2.1 调用一个无参的存储过程。
exec proc_productInfo
2.2.2 调用一个有参的存储过程。
exec proc_addProductInfo 'isbn123456', '毛泽东选集', 12, 59.99, 288, '一代伟人的气势磅礴作品!'
2.3 修改存储过程。
关键字:
"alter" 和 "procedure / proc"。
语法:
alter proc/procedure 存储过程名称 参数列表
as
begin
T-SQL语句
end
go
例如:
alter proc proc_productInfo
as
begin
select
pi.*,
pti.ProductTypeName
from
ProductInfo pi
inner join
ProductTypeInfo pti
on
pi.ProductTypeId = pti.ProductTypeId
end
go
2.4 删除存储过程。
关键字:
"drop" 和 "procedure / proc"。
语法:
drop proc/procedure 存储过程名称
go
例如:
drop proc proc_productInfo
go
SQL Server数据库基础:存储过程介绍、用脚本“创建/修改/删除/调用”存储过程。
于 2022-08-04 10:42:07 首次发布