将此SQL存储过程转为ORACLE存储过程
敬请高手赐教啊,CREATE PROCEDURE [dbo].[CLEARCOUNT] AS
SELECT COUNT(*) AS C,POST_DATE,MATERIAL_NO,MATERIAL_ITEM
INTO #T
FROM BMS_STOCK_OUT
GROUP BY POST_DATE,MATERIAL_NO,MATERIAL_ITEM
ORDER BY C DESCDECLARE @POST_DATE DATETIME
DECLARE @MATERIAL_NO NVARCHAR(20)
DECLARE @MATERIAL_ITEM NVARCHAR(20)
DECLARE @COUNT INT
DECLARE ALLCOUNT CURSOR FOR
SELECT * FROM #T WHERE #T.C>1
OPEN ALLCOUNT
FETCH NEXT FROM ALLCOUNT INTO @COUNT,@POST_DATE,@MATERIAL_NO,@MATERIAL_ITEM
WHILE @@FETCH_STATUS = 0 BEGIN
SET ROWCOUNT 1
DELETE FROM BMS_STOCK_OUT WHERE POST_DATE=@POST_DATE AND MATERIAL_NO=@MATERIAL_NO AND MATERIAL_ITEM=@MATERIAL_ITEM
FETCH NEXT FROM ALLCOUNT INTO @COUNT,@POST_DATE,@MATERIAL_NO,@MATERIAL_ITEM
ENDCLOSE ALLCOUNT
敬请高手赐教啊,CREATE PROCEDURE [dbo].[CLEARCOUNT] AS
SELECT COUNT(*) AS C,POST_DATE,MATERIAL_NO,MATERIAL_ITEM
INTO #T
FROM BMS_STOCK_OUT
GROUP BY POST_DATE,MATERIAL_NO,MATERIAL_ITEM
ORDER BY C DESCDECLARE @POST_DATE DATETIME
DECLARE @MATERIAL_NO NVARCHAR(20)
DECLARE @MATERIAL_ITEM NVARCHAR(20)
DECLARE @COUNT INT
DECLARE ALLCOUNT CURSOR FOR
SELECT * FROM #T WHERE #T.C>1
OPEN ALLCOUNT
FETCH NEXT FROM ALLCOUNT INTO @COUNT,@POST_DATE,@MATERIAL_NO,@MATERIAL_ITEM
WHILE @@FETCH_STATUS = 0 BEGIN
SET ROWCOUNT 1
DELETE FROM BMS_STOCK_OUT WHERE POST_DATE=@POST_DATE AND MATERIAL_NO=@MATERIAL_NO AND MATERIAL_ITEM=@MATERIAL_ITEM
FETCH NEXT FROM ALLCOUNT INTO @COUNT,@POST_DATE,@MATERIAL_NO,@MATERIAL_ITEM
ENDCLOSE ALLCOUNT
DELETE FROM BMS_STOCK_OUT WHERE c>0
如果那三个列有空值,那么
DELETE FROM BMS_STOCK_OUT WHERE c>0 and POST_DATE is not null and MATERIAL_NO is not null and MATERIAL_ITEM is not null
as
POST_DATE DATETIME;
MATERIAL_NO NVARCHAR(20);
MATERIAL_ITEM NVARCHAR(20);
COUNT INT;
tname varchar2(20);
cursor ALLCOUNT FOR SELECT * FROM #T WHERE #T.C>1;
begin
Insert into T SELECT COUNT(*) AS C,POST_DATE,MATERIAL_NO,MATERIAL_ITEM
FROM BMS_STOCK_OUT
GROUP BY POST_DATE,MATERIAL_NO,MATERIAL_ITEM
ORDER BY C DESC; open allcount;
loop
fetch allcount into ...;
exit when allcount%notfount;
.......
end loop;
end;类似这样吧,可以找找资料,就是有的写法不同,罗辑关系都一样
IS
CURSOR ALLCOUNT IS
select * from (SELECT COUNT(*) AS C,POST_DATE,MATERIAL_NO,MATERIAL_ITEM
FROM BMS_STOCK_OUT
GROUP BY POST_DATE,MATERIAL_NO,MATERIAL_ITEM
ORDER BY C DESC) t
WHERE T.C>1;
begin
for t_ALLCOUNT in ALLCOUNT loop
DELETE BMS_STOCK_OUT
WHERE POST_DATE = t_ALLCOUNT.POST_DATE
AND MATERIAL_NO = t_ALLCOUNT.MATERIAL_NO
AND MATERIAL_ITEM = t_ALLCOUNT.MATERIAL_ITEM ;
end loop;
END CLEARCOUNT;
as
POST_DATE DATETIME;
MATERIAL_NO NVARCHAR(20);
MATERIAL_ITEM NVARCHAR(20);
COUNT INT;
tname varchar2(20);
cursor ALLCOUNT FOR SELECT * FROM #T WHERE #T.C>1;
begin
Insert into T SELECT COUNT(*) AS C,POST_DATE,MATERIAL_NO,MATERIAL_ITEM
FROM BMS_STOCK_OUT
GROUP BY POST_DATE,MATERIAL_NO,MATERIAL_ITEM
ORDER BY C DESC; open allcount;
loop
fetch allcount into ...;
exit when allcount%notfount;
.......
end loop;
end;
就是有的写法不同,罗辑关系都一样