关于存储过程/函数/insert select/update select JOIN的sql应用例子
程序员文章站
2022-05-01 11:29:38
...
今天,有两张表需要互相同步数据,1对多的形式,写了一个过程,用到了存储过程/insert select/update select JOIN等复杂SQL
,数据库是sqlserver,其他库写法有微调.先记录如下:
ALTER PROCEDURE [dbo].[proc_export_order_return]
AS
BEGIN
DECLARE @code nvarchar(1000);
DECLARE @return_status nvarchar(10);
DECLARE @return_time nvarchar(17);
DECLARE @return_info nvarchar(1000);
DECLARE @i bigint;
DECLARE @j bigint;
create table #t(
rn int,
code nvarchar(1000),
return_status nvarchar(10),
return_time nvarchar(17),
return_info nvarchar(1000)
);
INSERT INTO #t
SELECT rn=row_number() over(order by code asc),code,return_status,
a.return_time,return_info FROM t_export_order_receipt b
RIGHT JOIN
(SELECT ebc_code + '-' + order_no code, MAX(return_time) return_time FROM t_export_order_receipt WHERE remove = 0
GROUP BY ebc_code + '-' + order_no) a
ON a.code = b.ebc_code + '-' + b.order_no AND a.return_time = b.return_time
SET @i = 1;
SET @j = (SELECT COUNT(*) FROM #t )
WHILE @i< = @j BEGIN
-- 查询业务主键
SET @code = (SELECT code FROM #t a WHERE a.rn = @i);
-- 查询最新回执时间
SET @return_time = (SELECT return_time FROM #t a WHERE a.rn = @i);
-- 查询最新回执状态
SET @return_status = (SELECT return_status FROM #t a WHERE a.rn = @i);
-- 查询最新回执信息
SET @return_info = (SELECT return_info FROM #t a WHERE a.rn = @i);
--更新表头
UPDATE t_export_order_head SET return_info = @return_info, return_time = @return_time, return_status = @return_status
WHERE ebc_code + '-' + order_no = @code AND remove = 0;
SET @i = @i+1;
END-- routine body goes here, e.g.
-- 下面是更新回执表的headId,根据情况看是否进行
UPDATE t_export_order_receipt SET order_head_id = b.id
FROM t_export_order_receipt a LEFT JOIN t_export_order_head b
ON a.ebc_code+a.order_no = b.ebc_code+b.order_no AND a.remove = 0 AND B.remove = 0
END
上面的存储过程不使用临时表还有一种写法,效率较低
ALTER PROCEDURE [dbo].[proc_export_arrival]
AS
BEGIN
DECLARE @code nvarchar(1000);
DECLARE @return_status nvarchar(10);
DECLARE @return_time nvarchar(17);
DECLARE @return_info nvarchar(1000);
DECLARE @i bigint;
DECLARE @j bigint;
SET @i = 1;
SET @j = (SELECT COUNT(*) FROM (SELECT operator_code + '-' + cop_no code, MAX(return_time) return_time FROM t_export_arrival_receipt WHERE remove = 0 GROUP BY operator_code + '-' + cop_no) a);
WHILE @i< = @j BEGIN
-- 查询业务主键
SET @code = (SELECT code FROM(
SELECT rn=row_number() over(order by operator_code + '-' + cop_no asc) , operator_code + '-' + cop_no code, MAX(return_time) return_time FROM t_export_arrival_receipt WHERE remove = 0 GROUP BY operator_code + '-' + cop_no
) a WHERE a.rn = @i);
-- 查询最新回执时间
SET @return_time = (SELECT return_time FROM(
SELECT rn=row_number() over(order by operator_code + '-' + cop_no asc) , operator_code + '-' + cop_no code, MAX(return_time) return_time FROM t_export_arrival_receipt WHERE remove = 0 GROUP BY operator_code + '-' + cop_no
) a WHERE a.rn = @i);
-- 查询最新回执状态
SET @return_status = (SELECT return_status FROM t_export_arrival_receipt WHERE operator_code + '-' + cop_no = @code AND return_time = @return_time AND remove = 0);
-- 查询最新回执信息
SET @return_info = (SELECT return_info FROM t_export_arrival_receipt WHERE operator_code + '-' + cop_no = @code AND return_time = @return_time AND remove = 0);
--更新表头
UPDATE t_export_arrival_head SET return_info = @return_info, return_time = @return_time, return_status = @return_status WHERE operator_code + '-' + cop_no = @code AND remove = 0;
SET @i = @i+1;
END
END
另外可以注意到使用row_number()来遍历临时表的存储过程,完全可以用update select JOIN多重子查询来代替,不再过多写了
上一篇: Oracle 11g AWR 系列二:AWR snapshot 的管理
下一篇: 分析用户ip归属地