CREATE OR REPLACE PROCEDURE usp_refreshTopN
IS
BEGIN
--文档
INSERT INTO topnresource(resourceId,title,type,cover,brief,properUser,tag,clickAmount,createDate,topNCreateDate,organization,topnType)
SELECT A.* FROM (SELECT resourceid,title,type,cover,brief,properuser,tag,clickamount,createdate,sysdate,organization,2 FROM resources WHERE refer = 1 AND type = 1 ORDER BY createdate DESC) A WHERE ROWNUM<=20;
--旧的信息,从第11条到第20条
DELETE FROM topnresource tpr WHERE tpr.topnresourceid IN (SELECT b.topnresourceid FROM(SELECT rownum myno,a.topnresourceid FROM (SELECT * FROM topnresource ORDER BY createdate DESC) a) b WHERE myno>10 AND myno<=20) AND tpr.topntype=2;END;这个存储过程 得写成一个事物 ,高手帮忙。我用的事 pl/sql 在里边运行 begin 语句 end;报一大堆错。
IS
BEGIN
--文档
INSERT INTO topnresource(resourceId,title,type,cover,brief,properUser,tag,clickAmount,createDate,topNCreateDate,organization,topnType)
SELECT A.* FROM (SELECT resourceid,title,type,cover,brief,properuser,tag,clickamount,createdate,sysdate,organization,2 FROM resources WHERE refer = 1 AND type = 1 ORDER BY createdate DESC) A WHERE ROWNUM<=20;
--旧的信息,从第11条到第20条
DELETE FROM topnresource tpr WHERE tpr.topnresourceid IN (SELECT b.topnresourceid FROM(SELECT rownum myno,a.topnresourceid FROM (SELECT * FROM topnresource ORDER BY createdate DESC) a) b WHERE myno>10 AND myno<=20) AND tpr.topntype=2;END;这个存储过程 得写成一个事物 ,高手帮忙。我用的事 pl/sql 在里边运行 begin 语句 end;报一大堆错。
CREATE OR REPLACE PROCEDURE usp_refreshTopN IS
BEGIN
-- 文档
INSERT INTO topnresource
(resourceId,
title,
TYPE,
cover,
brief,
properUser,
tag,
clickAmount,
createDate,
topNCreateDate,
organization,
topnType)
SELECT A.*
FROM (SELECT resourceid,
title,
TYPE,
cover,
brief,
properuser,
tag,
clickamount,
createdate,
SYSDATE,
organization,
2
FROM resources
WHERE refer = 1 AND
TYPE = 1
ORDER BY createdate DESC) A
WHERE ROWNUM <= 20;
--旧的信息,从第11条到第20条
DELETE FROM topnresource tpr
WHERE tpr.topnresourceid IN (SELECT b.topnresourceid
FROM (SELECT rownum myno, a.topnresourceid
FROM (SELECT * FROM topnresource ORDER BY createdate DESC) a) b
WHERE myno > 10 AND
myno <= 20) AND
tpr.topntype = 2;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
IS
BEGIN
--文档
INSERT INTO topnresource(resourceId,title,type,cover,brief,properUser,tag,clickAmount,createDate,topNCreateDate,organization,topnType)
SELECT A.* FROM (SELECT resourceid,title,type,cover,brief,properuser,tag,clickamount,createdate,sysdate,organization,2 FROM resources WHERE refer = 1 AND type = 1 ORDER BY createdate DESC) A WHERE ROWNUM<=20;
--旧的信息,从第11条到第20条
DELETE FROM topnresource tpr WHERE tpr.topnresourceid IN (SELECT b.topnresourceid FROM(SELECT rownum myno,a.topnresourceid FROM (SELECT * FROM topnresource ORDER BY createdate DESC) a) b WHERE myno>10 AND myno<=20) AND tpr.topntype=2;commit;exception when others then
rollback;END;