SQL存储过程的增删改查 事务 事务异常捕获 触发器 视图存储过程

原创 已于 2025-08-26 22:04:23 修改 · 208 阅读 · 0 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
GEO检测
于 2025-08-26 21:21:39 首次发布

存储过程

存储过程（数据库引擎） - SQL Server | Microsoft Learn

封装一段SQL语句，让SQL语句当成一个整体（编译过），可以直接调用整体，提高性能。

特点:

  • 类似于C#中方法。方法名，方法参数列表，返回值，输入参数in，输出参数out

  • 存储过程也有参数，也有返回值，输入参数，输出参数

存储过程-删除

IF EXISTS(SELECT * FROM sys.procedures WHERE name='p_studentinfo_delete')

drop procedure p_studentinfo_delete
go

create proc p_studentinfo_delete
@StudentId int
as
begin

  begin tran   --开始一个事务
  --从此处开始，后续的所有数据库操作（直到 COMMIT 或 ROLLBACK）都将被包含在一个事务中。事务可以确保这些操作要么全部成功，要么全部失败，从而保证数据的一致性。
  begin try  --开始一个Try-Catch TRY块
  delete from StudentInfo where StuId=@StudentId;  --执行删除操作的 SQL 语句。
  commit tran  --提交事务

  --如果 TRY 块中的所有语句（包括上面的 DELETE）都执行成功，没有发生任何错误，则执行 COMMIT TRAN。这会正式将事务内的所有更改（即删除操作）永久保存到数据库中。
  end try  --TRY块结束
  begin catch  --开始 Try...Catch 结构的 CATCH 块。
  --如果 TRY 块中的任何语句发生了错误，程序执行流程会立即跳转到这里的 CATCH 块中。

  rollback tran  --回滚事务
  --撤销当前事务中已经执行的所有操作，将数据库恢复到 BEGIN TRAN 之前的状态。这确保了在发生错误时，不会只执行一半操作（比如钱扣了但货物库存没减），从而维护了数据的完整性。
  end catch  --CATCH 块的结束标记。

  end  --与最开始的 BEGIN 配对，标志存储过程逻辑主体的结束。
  go  --批处理结束指令。告诉 SQL Server 到此为止，完成存储过程的创建。

存储过程-增

--一个插入的存储过程

--判断某个存储过程是否存在 如果已经存在 先删除 在创建
IF EXISTS (
    SELECT *
    FROM sys.procedures WHERE name = 'p_studentsuinfo_insert')
    
    drop procedure p_studentsuinfo_insert
	go

	--定义存储过程  Procedure 缩写proc

	create proc p_studentsuinfo_insert

	--参数列表 定义格式 参数名称 参数类型 每个参数以英文逗号隔开,最后一个参数不加逗号
	--输入参数(类似C#中形式参数)
	@StudentName varchar(50),  --输入参数:参数1:学生姓名
	@Score int,    --输入参数:参数2:分数
	--输出参数:必须赋值 新插入学生的ID
	@ReturnValue int output
	as

	begin
	--begin 和 end 之间存储的是业务逻辑 ,将来业务逻辑可以是复杂的业务逻辑
	insert into StudentInfo(StuName,Score)values(@StudentName,@Score);

	--@@rowcount;sql执行时影响的行数
	set @ReturnValue=@@ROWCOUNT;
	return 100;
	end

	go

	--在sql server中调用存储过程
	--execute 简写 exec

	--declare 声明一个变量  print 打印

	declare @rv int  --接受输出参数
	declare @return int --接收输出参数
	--调用存储过程
	--@rv 用于接收输出参数，@return 用于接收返回值

	exec @return=p_studentsuinfo_insert '吴亦凡666',82,@rv output
	print @rv
	print @return

存储过程-改

--有返回值的时候需要定义输出参数  而这个没有输出参数 是因为没有返回值

IF EXISTS(SELECT * FROM sys.procedures WHERE name='p_studentinfo_update')
 drop procedure p_studentinfo_update
 go


 create proc p_studentinfo_update
    @StuName varchar(50),
	@Score int,
	@StudentId int

	as
	begin
	 update StudentInfo set StuName=@StuName,Score=@Score where StuId=@StudentId;
	 end
	 go

	 --调用存储过程
	 exec p_studentinfo_update '吴亦凡777',123,1

 分页存储过程-查询

SQLServer存储过程分页 - JoyBin - 博客园 https://zhuanlan.zhihu.com/p/694238427 SqlServer 分页存储过程 - 石shi - 博客园 SQL存储过程[2] - 分页（SQL Server 、MySQL） - 滔Roy - 博客园

--分页存储过程
if exists(select * from sysobjects where id=object_id('p_studentinfo_select'))
	drop proc p_studentinfo_select
go

create proc p_studentinfo_select
	@currentPage int,	--当前页码
	@pageSize int,		--每页显示条数
	@where varchar(max), --查询条件
	@totalPage int output --输出总页数
as
begin
	declare @sql1 nvarchar(max); --求查询结果的sql
	declare @sql2 nvarchar(max); --求总页码的sql
	declare @total int;          --保存查询的总记录数，目的为了求总页码
	declare @offset int;         --分页的偏移量

	--偏移量公式
	set @offset = (@currentPage - 1) * @pageSize

	--拼接求结果的sql语句、拼接求总页码的sql语句
	--提醒：求总页码的sql和求结果的sql共用一个where条件
	set @sql1 = N'select * from StudentInfo where 1=1 ';
	set @sql2 = N'select @count = count(*) from StudentInfo where 1=1 '
	if len(trim(@where)) <> 0
	begin
		set @sql1 += @where;  -- @where = and StudentName like '%张三%'
		set @sql2 += @where;
	end
	
	set @sql1 += N' order by StuId offset   ' + 
	cast(@offset as nvarchar)
	+ N'   rows fetch next   '+ 
	cast(@pageSize as nvarchar) + 
	N'   rows only'

	exec(@sql1);--执行sql  exec 只能使用拼接字符串的方式，不支持使用输入参数
	--把@count变量的结果输出到@total上。
	exec sp_executesql @sql2, N'@count int output',@total output;

	if(@total % @pageSize = 0)
		set @totalPage =  @total / @pageSize;
	else
		set @totalPage =  @total / @pageSize + 1;
end
go


declare @tp int
exec p_studentinfo_select 1,10,'',@tp output
print @tp

事务

Transactions (Transact-SQL) - SQL Server | Microsoft Learn

是一个整体，多个SQL语句在一个整体内完成，比如：一个整体中包含一个查询，一个更新， 一个删除语句，事务可以保证这三个语句要么同时成功，要么同时失败。不会出现一部分成功，一部分失败的情况。事务保证的是数据的一致性。

事务定义:

语法如下： ‌开始事务‌：使用BEGIN TRANSACTION或BEGIN TRANSACTION。 ‌提交事务‌：使用COMMIT TRANSACTION。 ‌回滚事务‌：使用ROLLBACK TRANSACTION‌。

事务的隔离级别(了解) SQL Server提供了多种事务隔离级别，用于控制并发事务之间的可见性和锁行为。常见的隔离级别包括： ‌READ COMMITTED‌：默认级别，只能读取已提交的数据 ‌READ UNCOMMITTED‌：允许读取未提交的数据（脏读） ‌REPEATABLE READ‌：保证在事务中多次读取同一数据时结果一致 ‌SERIALIZABLE‌：最高隔离级别，事务完全隔离，避免并发问题‌

--事务可以保证存储过程中的业务逻辑要么同时完成 要么同时失败


--1.开始事务 事务把很多的业务逻辑当成一个整体来执行
begin transaction
begin
  insert into StudentInfo(StuName,Score)values('吴亦凡123',97);
  insert into StudentInfo(StuName,Score)values('吴亦凡456',99);
  update StudentInfo set StuName='吴亦凡666',Score=12 where StuId=2;

  ---- 手动触发一个错误（模拟操作过程中出现异常）
  raiserror('发生一个错误',1,16);
  end
  

  -- !=  <> 不等于
  --@@ errer 语句的错误编号 若语句执行成功 返回0
if @@error  <> 0   --@@error 是系统函数 返回上一条语句的错误编号 非0表示失败  0表示成功

begin
 rollback tran  --2.回滚事务(失败)  撤销所有已执行的操作（插入和更新全部无效）
 print'执行事务失败'  --输出失败提示
 end
 else  --如果没有错误(错误编号=0)
 begin
 commit tran  --3.提交事务  (成功) :所有操作永久生效(插入和更新写入数据库)
 print '执行事务成功' --输出事务成功
 end

事务捕获异常

SqlServer捕获异常

--SQLServer 捕获异常

begin try  --开始一个 try 块。 标记一段可能包含错误代码的开始。SQL Server 会尝试执行这个块内的所有语句。
declare @i int;  --声明一个名为 @i 的局部变量，类型为 int（整数）。

set @i=cast('abc' as int);  --为@i赋值
--尝试将字符串 'abc' 转换（CAST） 为 int 类型。这行代码是故意会失败的，因为 'abc' 无法转换为一个整数。这将引发一个错误。

print @i  --打印变量 @i 的值。  这行代码永远不会被执行。因为上一行发生了错误，程序流程会立即跳出 try 块，直接进入 catch 块
end try  --try 块的结束标记。	
begin catch   --开始一个catch块  作用：当 try 块中的任何语句发生错误时，程序会立刻跳转到这里开始执行。这是处理错误的地方。
print '转换失败'  --打印一条消息。
end catch  --catch 块的结束标记。


--事务中捕获异常

begin tran  --开始一个事务 从此处开始，后续的所有数据库操作都将被作为一个原子单元（Atomic Unit）。这些操作要么全部成功，要么全部失败
begin try   --开始 try 块（将可能出错的事务操作包裹起来）
 delete from StudentInfo where StuId=10;  --执行一条删除语句，删除 StuId 为 10 的学生记录。  这是事务内的第一个操作。

 raiserror('一个错误',12,1)  --主动抛出一个错误 模拟一个业务逻辑错误  这行代码会使执行流程立即跳转到 catch 块。
 print '全部逻辑成功,就提交'  --打印成功信息  这行代码永远不会被执行，因为上一行抛出了错误。
 commit tran  --提交事务 
 --这行代码也永远不会被执行。只有当 try 块中所有语句都成功执行时，才会运行到这里。它会将事务内所有的更改（上面的 delete）永久保存到数据库。

 end try   --try 块的结束标记。
 begin catch  --开始try块  因为try块中raiserror抛出错误

 print '任意一个逻辑失败,就会滚'   --打印一条信息 说明进入了错误处理流程
 
 rollback tran  --回滚事务
 --这是 catch 块中最关键的一步。它表示撤销自 begin tran 以来的所有操作。在这里，它会撤销那条删除了 StuId=10 记录的 delete 语句，使数据库恢复到事务开始前的状态，从而保证了数据的一致性。
 end catch  --catch 块的结束标记。

视图

Views - SQL Server | Microsoft Learn

相当于时一个 '临时表' 就是虚拟表 它时由多个表组成的结果集

平时建议只对视图进行查询操作,增 删 改 应该应用真实的表结构

--视图 view  
iF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'v_studentinfo') 
    DROP View v_studentinfo
go

create view  v_studentinfo
as
   select * from  StudentInfo;
go

--查询视图
select  * from  v_studentinfo;


--视图:可以把多个表的结果集合合并到一个视图中
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME=N'v_customerInfo')
 DROP VIEW v_customerInfo
 GO

 create view v_customerInfo
 as

 select
 C.CustomerId,    -- 从CustomerInfo表选择客户ID
 C.CustomerName,  -- 从CustomerInfo表选择客户姓名

 --使用 CASE 语句将数字表示的性别转换为中文
 case C.Sex 
       when 1 then '男'  ---- 如果Sex字段值为1，则显示'男'
	   else '女'    ---- 否则（即不是1），显示'女'
 end as SexName1,   ---- 将这个计算后的列命名为 SexName1

  -- 另一个更严谨的 CASE 语句处理性别转换
 case C.Sex 
     when 1 then '男'   -- 值为1 -> 男
	 when 0 then '女'   --值为0 -> 女
     else '未知' --值为其他任何值（如NULL, 2等） -> 未知
 end as SexName2,  -- -- 将这个计算后的列命名为 SexName2

 C.Age,    -- 从CustomerInfo表选择年龄
 C.Phone,  -- 从CustomerInfo表选择电话  

  -- 拼接地址信息：省份+城市+区域+详细地址，组成一个完整的地址字符串
A.ProvinceName+A.City+A.Area+A.DetailAddress as AddressDetail  

-- 指定数据来源的表，并使用 LEFT JOIN 连接
from  CustomerInfo as C left join AddressInfo as A on 

C.AddressId=A.AddressId

go


select * from   v_customerInfo where SexName1='男'

自定义函数

-- 自定义函数
IF OBJECT_ID('dbo.f_add', 'FN') IS NOT NULL
--检查名为 f_add 的函数是否存在。
--dbo 是 schema（架构名），通常是数据库所有者。
--'FN' 指定对象类型为标量函数 (Scalar Function)。

 drop  function f_add
go

create function f_add(@x int , @y int)
--创建一个名为 f_add 的函数，它接受两个 int 类型的参数 @x 和 @y。
returns int--返回值类型
as 
begin
		return @x+@y;
end
go


--调用自定义函数
declare  @r int;
--声明一个名为 @r 的整数型变量，用于存储函数返回的结果

-- database owner   数据库拥有折
select @r  =   dbo.f_add(10,10)
--调用自定义函数 f_add，传入参数 10 和 10，并将返回值赋给变量 @r。注意：调用自定义函数时必须加上 schema 名前缀（这里是 dbo.）。

print @r;

--系统内置函数示例

select convert(varchar(10),getdate(),121)  ---- 将当前日期时间转换为 'YYYY-MM-DD' 格式的字符串
select dateadd(month, 1, '20240830');  ---- 给日期 '2024-08-30' 增加 1 个月
select dateadd(year, 1, getdate());  ---- 给当前日期增加 1 年
select power(2,3)  ---- 计算 2 的 3 次方，返回 8
select ceiling(2.1)  --- 向上取整，返回 3
select floor(2.9)  -- 向下取整，返回 2
select round(4.6,0,0)  -- 将 4.6 四舍五入到 0 位小数，返回 5.0
SELECT ROUND(123.4545, 2);  -- 将 123.4545 四舍五入到 2 位小数，返回 123.4500
SELECT ROUND(163.45, -2); -- 在十位上进行四舍五入，返回 200.00
SELECT ROUND(150.75, 0);   -- 四舍五入到整数，返回 151.00
 
SELECT ROUND(150.75, 0, 2);  -- 直接截断到整数，返回 150.00 (第三个参数非0表示截断)

select left('hello',3)  -- 从左边开始截取 3 个字符，返回 'hel'
select right('hello',3)  -- 从右边开始截取 3 个字符，返回 'llo'

--截取，查索引，合并（串联），转换大写，转换小写，去空白，格式化，替换。
select substring('hello',1,3) -- 从第1位开始截取3个字符，返回 'hel'
select upper('hello'); -- 转换为大写，返回 'HELLO'
select lower('HELLO'); -- 转换为小写，返回 'hello'
select ltrim('   hello') -- 去除字符串左侧的空格，返回 'hello'
select rtrim('hello   ') -- 去除字符串右侧的空格，返回 'hello'
select trim('   hello   ') -- 去除字符串两端的空格，返回 'hello' (SQL Server 2017及以上版本支持)
select reverse('hello'); -- 反转字符串，返回 'olleh'
select char(1) -- 返回 ASCII 码 1 对应的字符（一个不可见的控制字符）
select char( ascii('a') ) -- 嵌套函数：先获取 'a' 的 ASCII 码（97），再返回 ASCII 码 97 对应的字符（'a'）
select 'abc'+'def' -- 字符串连接（串联），返回 'abcdef'
select concat('hello',' world','how',' are',' you')  -- 更强大的连接函数，返回 'hello worldhow are you'

触发器

https://download.csdn.net/blog/column/12681594/139170021

insert触发器

delete 触发器

update触发器

另外一种分类：before触发器，after触发器，控制触发器执行的时机，是在insert,update,delete语句前或后

触发器不需要exec来调用，会自动执行。在插入，删除，更新数据时会执行触发器。

​
IF EXISTS (SELECT *   FROM sys.triggers WHERE name = 'tri_studentinfo_insert')
drop trigger tri_studentinfo_insert;

create trigger   tri_studentinfo_insert on StudentInfo for insert 
as
print '我是向StudenInfo中插入数据的时候执行的触发器'
go

update StudentInfo set StuName = 'ABC' where  StuName='吴亦凡69'
insert into StudentInfo (StuName,Score) values ('abc',90);

关键字:

N

在 SQL Server 中，N 前缀用于表示其后跟的字符串是一个 Unicode 字符串字面量（NVARCHAR 类型），而不是普通的非 Unicode 字符串字面量（VARCHAR 类型）。

  • 'Some text' - 普通字符串，类型为 VARCHAR

  • N'Some text' - Unicode 字符串，类型为 NVARCHAR

为什么需要 N 前缀？关键区别

VARCHAR 和 NVARCHAR 的根本区别在于字符编码和存储方式：

特性VARCHAR (非 Unicode)NVARCHAR (Unicode)
编码方式使用数据库的代码页（如 CP1252, GB2312）使用 Unicode 标准（UCS-2/UTF-16）
存储空间每个字符占 1-2 字节（取决于代码页）每个字符占 2 字节（基本多语言平面）
字符支持仅限于特定代码页定义的字符集支持全球几乎所有语言的字符
最大长度VARCHAR(n)：最多 8,000 字符NVARCHAR(n)：最多 4,000 字符

何时必须使用 N 前缀？

当您需要处理非英文字符（如中文、日文、阿拉伯文等）或需要确保字符跨系统兼容时，必须使用 N 前缀。

示例对比

假设我们有一个存储用户名的表：

sql

CREATE TABLE Users (
    Id INT IDENTITY PRIMARY KEY,
    UserName NVARCHAR(50) -- 定义为 Unicode 列
);

情况一：错误写法（不使用 N）

sql

INSERT INTO Users (UserName) VALUES ('张三');

这里 '张三' 作为 VARCHAR 字符串处理，如果数据库代码页不支持中文，会导致乱码（如 ??）。

情况二：正确写法（使用 N）

sql

INSERT INTO Users (UserName) VALUES (N'张三');
-- 正确插入中文字符
INSERT INTO Users (UserName) VALUES (N'すばらしい');
-- 正确插入日文字符
INSERT INTO Users (UserName) VALUES (N'محمد');
-- 正确插入阿拉伯文字符

情况三：查询时的比较

sql

-- 可能无法正确匹配
SELECT * FROM Users WHERE UserName = '张三';
​
-- 正确的 Unicode 字符串比较
SELECT * FROM Users WHERE UserName = N'张三';

其他使用场景

  1. 存储过程参数

sql

CREATE PROCEDURE AddUser
    @Name NVARCHAR(50)
AS
BEGIN
    INSERT INTO Users (UserName) VALUES (@Name);
END
​
-- 调用时也需要使用 N 前缀
EXEC AddUser @Name = N'李四';

  1. 函数参数

sql

SELECT * FROM Users 
WHERE UserName LIKE N'张%'; -- 使用 Unicode 模式匹配

  1. 变量声明

sql

DECLARE @MyName NVARCHAR(50) = N'王五';

最佳实践

  1. 表设计时：如果应用需要国际化支持，优先选择 NVARCHAR 而不是 VARCHAR

  2. 编程时：处理可能包含非ASCII字符的字符串时，始终使用 N 前缀

  3. 性能考量：虽然 NVARCHAR 占用更多空间，但现代硬件条件下这种开销通常可以接受

  4. 一致性：即使当前只使用英文，也建议养成使用 N 前缀的习惯，为未来国际化做准备

标签
#sql #数据库 #oracle
D1021_
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值