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