oracle存储过程是事务,oracle 存储过程中应用事务的问题 - Oracle / 开发

SQL code:

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;

--课程

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,1 from resources WHERE refer = 1 and type = 4 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=1;

--社区热门文档

INSERT INTO topnresource(resour