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

数据库 waitig 397℃ 百度已收录 0评论

今天有个写存储过程的朋友问我一个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


本文由【waitig】发表在等英博客
本文固定链接:oracle 使用with insert插入数据的问题
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)