oracle ´æ´¢¹ý³Ì¸Ä³É ÊÂÎï Ôõôд°¡ - Oracle / ¿ª·¢

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;±¨Ò»´ó¶Ñ´í¡£{{--