--获取信息列表
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<SYSDATE
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<SYSDATE
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<SYSDATE
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<SYSDATE
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;
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<SYSDATE
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<SYSDATE
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<SYSDATE
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<SYSDATE
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;