oracle 使用with insert插入数据的问题

最新推荐文章于 2024-11-30 08:14:53 发布

原创最新推荐文章于 2024-11-30 08:14:53 发布 · 5k 阅读

4 ·

CC 4.0 BY-SA版权

本文解决了一个关于WITH子句后跟INSERT操作导致ORA-00928错误的问题。通过调整SQL语句结构，将INSERT语句移至WITH子句之前，成功避免了错误并实现了预期的数据处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天有个写存储过程的朋友问我一个with a as 。。。。insert 的存储过程为什么总是报错：ORA-00928: 缺失 SELECT 关键字

最后发现with后面只能直接跟select 不能跟insert，修改后解决问题

修改前：

with
a as(SELECT T.StationId,U.Id AS UserId
FROM Sys_Station_Info T
JOIN Sys_Organize O ON T.StationId=O.OrgCode
JOIN Sys_UsersRelation R ON O.Id=R.Orgid
JOIN Sys_Users U ON U.Id=R.UserId
WHERE T.Use_Flag=1)

INSERT INTO USEROFTENIP(StationCode,UserId,Ipaddress,Times,CreateTime)
SELECT ba.StationId,a.UserId,L.IpAddress,COUNT(L.IpAddress) AS Times,Sysdate from .
a JOIN Sys_Log L ON a.UserId=L.UserId
where L.WriteTime>= sysdate-30
GROUP BY a.StationId,a.UserId,L.IpAddress；

修改后：

INSERT INTO USEROFTENIP(StationCode,UserId,Ipaddress,Times,CreateTime)

with
a as(SELECT T.StationId,U.Id AS UserId
FROM Sys_Station_Info T
JOIN Sys_Organize O ON T.StationId=O.OrgCode
JOIN Sys_UsersRelation R ON O.Id=R.Orgid
JOIN Sys_Users U ON U.Id=R.UserId
WHERE T.Use_Flag=1)

SELECT ba.StationId,a.UserId,L.IpAddress,COUNT(L.IpAddress) AS Times,Sysdate from .
a JOIN Sys_Log L ON a.UserId=L.UserId
where L.WriteTime>= sysdate-30
GROUP BY a.StationId,a.UserId,L.IpAddress