oracle 包中新增存储过程,oracle包中的存储过程

--获取信息列表

PROCEDURE PRO_GET_ARTICLE_LIST

(

sidPara  IN  CMS_CATEGORY.CAT_SID%TYPE,--栏目编号

usernamePara IN VARCHAR2,--当前用户

currPagePara IN  NUMBER,--当前第几页

pageSizePara IN  NUMBER,--页面大小

startIndexPara IN NUMBER,--获取当前页的记录的开始下标,第一条为1

endIndexPara IN NUMBER,--获取当前页的记录的结束下标

timeScopePara IN NUMBER,--获取什么时间以后的数据

userLevelPara IN NUMBER,--用户级别

artCountPara OUT NUMBER,--信息总数量

pageCountPara OUT NUMBER,   --总页数

artListPara OUT refcur

)

IS

--定义当前页展示信息开始的索引值

vStartIndex NUMBER(10,0);

vEndIndex NUMBER(10,0);

--定义信息总数

vArtCount NUMBER(10,0);

--定义是否按照最大权限设置

vExists NUMBER(8);

vPrivilege VARCHAR2(5);

BEGIN

--验证用户是否拥有所有信息的浏览权限

SELECT count(*) INTO vExists

FROM cms_v_user_group

WHERE USERNAME=usernamePara

AND GROUP_SID = LEADER_SID;

IF vExists = 0 THEN

vPrivilege := 'false';

ELSE

vPrivilege := 'true';

END IF;

--验证startIndexPara参数与pageSizePara的关系

IF pageSizePara < startIndexPara THEN

vStartIndex := 0;

ELSE

vStartIndex := startIndexPara - 1;--vStartIndex的正确初始值为0

END IF;

--验证endIndexPara参数与pageSizePara的关系

IF pageSizePara < endIndexPara THEN

vEndIndex := pageSizePara;

ELSE

vEndIndex := endIndexPara;

END IF;

--计算出当前页展示信息开始的索引值

vStartIndex := (currPagePara-1) * pageSizePara + vStartIndex;

--计算出当前页展示信息结束的索引值

vEndIndex := (currPagePara-1) * pageSizePara + vEndIndex;

--获取信息总数

IF vPrivilege = 'false' THEN

SELECT

COUNT(*) INTO vArtCount

FROM CMS_ARTICLE t

WHERE

EXISTS(

SELECT ART_ID FROM CMS_ART_SCOPE t1,cms_v_user_group t2

WHERE t2.GROUP_SID like t1.ART_ROLE||'%'

AND t.ART_ID = t1.art_id

AND t2.USERNAME=usernamePara

)

AND t.ART_DELETED='false'

AND t.ART_AUDITED='true'

AND t.ART_READER_LEVEL>=userLevelPara

AND t.ART_ST_DATE

AND t.ART_ED_DATE>SYSDATE

AND t.CAT_SID LIKE sidPara||'%'

AND t.ART_UPDATED > SYSDATE-timeScopePara;

ELSE

SELECT

COUNT(*) INTO vArtCount

FROM CMS_ARTICLE t

WHERE

t.ART_DELETED='false'

AND t.ART_AUDITED='true'

AND t.ART_ST_DATE

AND t.ART_ED_DATE>SYSDATE

AND t.CAT_SID LIKE sidPara||'%'

AND t.ART_UPDATED > SYSDATE-timeScopePara;

END IF;

IF vArtCount = 0 THEN

artCountPara := 0;

pageCountPara := 0;

OPEN artListPara FOR

SELECT * FROM CMS_ARTICLE

WHERE ART_ID<0;

RETURN;

END IF;

artCountPara := vArtCount;

--计算总页数

IF MOD(vArtCount,pageSizePara) = 0 THEN

pageCountPara := TRUNC(vArtCount / pageSizePara);

ELSE

pageCountPara := TRUNC(vArtCount / pageSizePara) + 1;

END IF;

IF vPrivilege = 'false' THEN

OPEN artListPara FOR

SELECT

*

FROM

(

SELECT

ART_ID,CAT_SID,ART_TITLE,ART_TITLE_STYLE,COMPANY,DEPARTMENT,ART_TITLE_SIGN,TO_CHAR(ART_ST_DATE,'YYYY-MM-DD') AS ART_UPDATED,ART_URL,ROWNUM AS R

FROM--获取按顺序的值

(SELECT

ART_ID,CAT_SID,ART_TITLE,ART_TITLE_STYLE,COMPANY,DEPARTMENT,ART_TITLE_SIGN, ART_ST_DATE,skin.skin_path||'ShowArticle.aspx?art_id='||ART_ID as ART_URL

FROM CMS_ARTICLE t,NEW_CMS_SKIN skin

WHERE

EXISTS(

SELECT ART_ID FROM CMS_ART_SCOPE t1,cms_v_user_group t2

WHERE t2.GROUP_SID LIKE t1.ART_ROLE||'%'

AND t.ART_ID = t1.ART_ID

AND t2.USERNAME=usernamePara

)

AND t.CAT_SID LIKE sidPara||'%'

AND t.ART_SKIN_ID = SKIN.SKIN_ID

AND t.ART_DELETED='false'

AND t.ART_AUDITED='true'

AND t.ART_READER_LEVEL>=userLevelPara

AND t.ART_ST_DATE

AND t.ART_ED_DATE>SYSDATE

AND t.ART_UPDATED>SYSDATE-timeScopePara

ORDER BY

ART_TOPPED DESC,ART_ST_DATE DESC,ART_ID DESC

)

)

WHERE R>vStartIndex AND R < vEndIndex +1;

ELSE

OPEN artListPara FOR

SELECT

*

FROM

(

SELECT

ART_ID,CAT_SID,ART_TITLE,ART_TITLE_STYLE,COMPANY,DEPARTMENT,ART_TITLE_SIGN,TO_CHAR(ART_ST_DATE,'YYYY-MM-DD') AS ART_UPDATED,ART_URL,ROWNUM AS R

FROM--获取按顺序的值

(SELECT

ART_ID,CAT_SID,ART_TITLE,ART_TITLE_STYLE,COMPANY,DEPARTMENT,ART_TITLE_SIGN, ART_ST_DATE,skin.skin_path||'ShowArticle.aspx?art_id='||ART_ID as ART_URL

FROM CMS_ARTICLE t,NEW_CMS_SKIN skin

WHERE

t.CAT_SID LIKE sidPara||'%'

AND t.ART_SKIN_ID = SKIN.SKIN_ID

AND t.ART_DELETED='false'

AND t.ART_AUDITED='true'

AND t.ART_ST_DATE

AND t.ART_ED_DATE>SYSDATE

AND t.ART_UPDATED>SYSDATE-timeScopePara

ORDER BY

ART_TOPPED DESC,ART_ST_DATE DESC,ART_ID DESC

)

)

WHERE R>vStartIndex AND R < vEndIndex +1;

END IF;

END PRO_GET_ARTICLE_LIST;