ITPub博客

首页 > 数据库 > 国内数据库 > 胖头鱼的技术专栏-492 SQL Server替换,先把存储过程里的业务跑通(20261117)

胖头鱼的技术专栏-492 SQL Server替换,先把存储过程里的业务跑通(20261117)

原创 国内数据库 作者:yhw1809 时间:2026-11-16 18:27:31 0 删除 编辑

胖头鱼的技术专栏-492 SQL Server替换,先把存储过程里的业务跑通(20261117)

作者:胖头鱼的鱼缸(尹海文)
Oracle ACE Pro: Database
PostgreSQL ACE
10年+数据库行业经验
拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证
墨天轮MVP,ITPUB认证专家
圈内拥有“总监”称号,非著 名社恐(社交恐怖分子)
全网同名:胖头鱼的鱼缸
ITPUB:yhw1809
除授权转载并标明出处外,均为“非法”抄袭

914fcc7ad57defa7868c3be1ca7fb4f5.jpg

数据库替换聊得多了,经常会碰到一个问题:常用的增删改查都支持,是不是应用就差不多能搬过去了?

如果应用只是把数据库当作存储,工作量可能相对容易判断。但不少以前运行在 SQL Server 上的系统把业务逻辑放在存储过程里:批量同步数据、返回变更明细、把另一个过程的结果接过来,再根据参数执行动态查询。只看几条 SELECT,往往看不到这些环节。

本期跟随总监走一遍金仓数据库的 SQL Server 兼容体验,测试场景是: 一批订单进来,已有订单更新,新订单插入,调用方还要知道究竟改了什么。

一、环境介绍

这次使用的安装介质是  KingbaseES_V009R004C019B0004_Lin64_install.iso,数据库版本为 V009R004C019,初始化时选择  sqlserver 模式,并启用大小写不敏感。

完整安装步骤和配置过程略。随包授权有 180 天有效期。

01.png

二、准备一批需要同步的订单

模拟的测试环境是已有订单两笔:SO1001 金额 100,状态 NEW;SO1002 金额 200,状态 PAID。第一批传入的数据里,SO1001 改成 120、PAID,另外增加一笔 SO1003,金额 300。

我把测试对象统一放到  ks_article 模式下。订单号作为业务主键,传入表暂时不加唯一约束,后面专门测试重复源键。结果表用来接收操作类型和新旧金额。

create schema ks_article;
create table ks_article.orders (
  order_no varchar(20) primary key,
  customer_name nvarchar(40) not null,
  amount decimal(12,2) not null,
  order_status varchar(12) not null);
create table ks_article.incoming (
  batch_no int not null,
  order_no varchar(20) not null,
  customer_name nvarchar(40) not null,
  amount decimal(12,2) not null,
  order_status varchar(12) not null);
create table ks_article.sync_result (
  operation varchar(10),
  order_no varchar(20),
  old_amount decimal(12,2),
  new_amount decimal(12,2)
);
insert into ks_article.orders values
  ('SO1001',N'张三',100,'NEW'),
  ('SO1002',N'李四',200,'PAID');
insert into ks_article.incoming values
  (1,'SO1001',N'张三',120,'PAID'),
  (1,'SO1003',N'王五',300,'NEW');

三、把同步和变更结果放进一个过程

MERGE 根据订单号匹配:匹配且内容有变化的执行 UPDATE,没有匹配的执行 INSERT。OUTPUT 则返回操作类型、订单号和金额的新旧值。

在  ksql 中创建过程时,先用  \set SQLTERM GO 设置批次结束标记,避免过程体内部的分号提前结束输入。 这是客户端的输入设置,不是要写进 JDBC 请求的服务器端 SQL。

\set SQLTERM GOcreate procedure ks_article.sync_orders @batch_no intasbegin
  set nocount on;  merge into ks_article.orders as t  using (
      select order_no,customer_name,amount,order_status
      from ks_article.incoming where batch_no=@batch_no
  ) as s  on t.order_no=s.order_no  when matched and (
    t.amount<>s.amount
        or t.order_status<>s.order_status
        or t.customer_name<>s.customer_name
  ) then update set
    customer_name=s.customer_name,
    amount=s.amount,
    order_status=s.order_status  when not matched then
    insert(order_no,customer_name,amount,order_status)
        values(s.order_no,s.customer_name,s.amount,s.order_status)
         output $action as operation,inserted.order_no,
         deleted.amount as old_amount,
         inserted.amount as new_amount;end;
GO
\set SQLTERM ;

$action 用来区分操作, deleted 和  inserted 分别取对应的新旧行信息。这里仅返回金额变化,但状态、姓名也参与是否更新的判断,所以可能出现新旧金额相同、实际改了其他字段的记录。

接着调用过程,把返回的四列按顺序放入结果表:

insert into ks_article.sync_result
exec ks_article.sync_orders @batch_no=1;
select * from ks_article.sync_result order by order_no;
select * from ks_article.orders order by order_no;

02.png

结果里一条 UPDATE、一条 INSERT,底表也符合预期。这样,MERGE、OUTPUT、INSERT…EXEC 就通过同一个存储过程连起来了。

INSERT…EXEC 接收的是结果集,接收表的列数、顺序和类型都要对得上。这里把过程输出固定成四列,没有把任意查询结果直接往表里塞,也没有测试嵌套 INSERT…EXEC 或多个结果集。

四、原样再执行一次

批量接口遇到重试很正常。我更关心第二次执行会发生什么,而不是第一次执行完就算结束。

truncate table ks_article.sync_result;
insert into ks_article.sync_result
exec ks_article.sync_orders @batch_no=1;
select count(*) as repeated_changes from ks_article.sync_result;
select count(*) as order_rows,sum(amount) as total_amount
from ks_article.orders;

03.png

这次没有变更行,订单仍为三笔,总额 620。原因是过程里明确写了“匹配且字段发生变化才更新”,而不是 MERGE 自动识别了所有重复请求。

这里验证的是同一份数据顺序重放的结果,不等于已经解决业务上的幂等。并发请求、旧批次晚到、数据版本冲突,都不在这个小测试的覆盖范围里。结果表也只是演示用的接收表,实际系统需要批次隔离,不能多个任务共用一张表再随手 TRUNCATE。

五、动态查询保留参数

再看一个常见环节:调用方按最低金额和订单状态查询。SQL 文本不变,条件值单独传入  SP_EXECUTESQL,不用把值拼进字符串。

exec sys.sp_executesql
  N'SELECT order_no,amount FROM ks_article.orders 
    WHERE amount>=@min_amount AND order_status=@status
    ORDER BY order_no;',
  N'@min_amount decimal(12,2), @status varchar(12)',
  @min_amount=200,@status='NEW';
exec sys.sp_executesql
  N'SELECT order_no,amount FROM ks_article.orders
    WHERE amount>=@min_amount AND order_status=@status
    ORDER BY order_no;',
  N'@min_amount decimal(12,2), @status varchar(12)',
  @min_amount=100,@status='PAID';

04.png

两次调用分别取得 SO1003,以及 SO1001、SO1002,参数定义与传值按预期生效。

参数化并不意味着可以放任其他字符串拼接。表名、排序列等结构部分,仍要做白名单控制。本次也没有测试执行计划复用,所以不把同一条文本调用两次写成“已验证计划缓存命中”。

六、回滚以后再查一遍

第二批数据准备修改 SO1002,并新增 SO1004。我把调用和结果接收放在显式事务中,先看当前事务中的数据,再主动回滚。

insert into ks_article.incoming values
  (2,'SO1002',N'李四',250,'PAID'),
  (2,'SO1004',N'赵六',400,'NEW');begin transaction;insert into ks_article.sync_result
exec ks_article.sync_orders @batch_no=2;
select * from ks_article.sync_result order by order_no;
select count(*) as inside_rows,sum(amount) as inside_total
from ks_article.orders;
rollback transaction;
select count(*) as after_rows,sum(amount) as after_total
from ks_article.orders;
select count(*) as result_rows from ks_article.sync_result;

05.png

订单修改和结果表插入都跟着事务回滚了。第二批传入记录是在事务开始前插入的,所以仍保留在 incoming 表里,后续需要重试时还能重新调用。

对业务而言, 查到变更结果和事务最终提交,是两个不同的时点。数据库事务也不会帮应用撤销已经发出去的短信、消息或远程调用。如果后续要对外发布订单事件,还需要设计提交后的发送与失败恢复。

七、源数据重复时,返回行不能当成功

最后故意制造一个问题:同一批次有两条 SO1001,分别要求改成 130 和 140。目标表虽然有主键,但源表存在重复匹配。

insert into ks_article.incoming values
  (3,'SO1001',N'张三',130,'PAID'),
  (3,'SO1001',N'张三',140,'PAID');
\set SQLTERM GObegin try
  exec ks_article.sync_orders @batch_no=3;
  end try
  begin catch
    select error_number() as error_no,
         error_message() as error_message;
  end catch;
GO
\set SQLTERM ;
select amount from ks_article.orders where order_no='SO1001';

06.png

这个结果值得多看一眼:本次客户端先显示了一条金额改为 140 的 OUTPUT,接着捕获错误  -10132,消息是  unable to get a stable set of rows in the source tables。再查底表,金额仍是 120。

如果只看到 OUTPUT 的那一行就触发后续业务,很可能把未生效的修改当成成功。SQL Server 官方 OUTPUT 文档也提醒,发生错误并回滚时,仍可能向客户端返回行,出错时不应使用这些结果。本次在金仓上的现象说明,这个调用规则值得一起保留(不据此声称两端所有错误码、错误时机完全相同)。

实际过程可以先做一次源键检查,再决定是否进入修改:

\set SQLTERM GOcreate procedure ks_article.sync_orders_checked @batch_no intasbegin
  set nocount on;
  if exists (
      select order_no from ks_article.incoming
      where batch_no=@batch_no
      group by order_no having count(*)>1
  )
  begin
    throw 50001,'Duplicate order_no in source batch',1;
  end;
  exec ks_article.sync_orders @batch_no=@batch_no;
  end;
GO
\set SQLTERM ;

再用同一批重复数据调用:

\set SQLTERM GObegin try
  exec ks_article.sync_orders_checked @batch_no=3;
  end trybegin catch  select error_number() as error_no,
         error_message() as error_message;end catch;
GO
\set SQLTERM ;select amount from ks_article.orders where order_no='SO1001';

07.png

这次在修改前就返回了自定义错误,底表金额仍为 120。应用迁移时,如果此前按 SQL Server 固定错误码判断分支,也应该检查这里的实际返回,不要只验证 TRY…CATCH 能不能执行。

这个前置检查只是演示改进, 不能替代并发下的约束与批次控制。如果业务规定同一批次每个订单只能出现一次,可以考虑对  (batch_no, order_no) 建唯一约束;如果允许多次变化,则先明确取最新、合并还是拒绝,再设计去重逻辑。

总结

本期在金仓数据库 V009R004C019 的 SQL Server 模式下,存储过程中的 MERGE、OUTPUT、INSERT…EXEC 已经串成了一段可以运行的订单同步流程,参数化动态查询也按预期返回结果。顺序重放、显式回滚、重复源键和前置检查,让这次体验不只停在创建过程成功。

数据库兼容,我比较在意的是原有业务逻辑能保留多少,以及出问题时应用还能不能作出正确判断。 语法可以执行是第一步,返回结果、事务边界和错误处理能接得住,才更接近一次可用的替换。这也是这段小测试比单独罗列几个支持项更有意思的地方。

老规矩,知道写了些啥。

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/31466763/viewspace-3189119/ ,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2017-06-07

  • 博文量
    396
  • 访问量
    422504