源代码如下:
create or replace procedure forums_PrivateMessages_Get
( v_UserID in integer,
v_PrivateMessageType in integer, -- 1 已经发送, 2 收到的私人消息
v_unreadOnly in number,
v_unreplyOnly in number,
v_PageSize in integer,
v_PageIndex in integer)
is
v_PageLowerBound integer;
v_PageUpperBound integer;
v_string1 varchar2(500);
v_MessageID integer;
begin
--DECLARE v_RowsToReturn int
-- First set the rowcount
--SET v_RowsToReturn = v_PageSize * (v_PageIndex + 1)
--SET ROWCOUNT v_RowsToReturn
-- Set the page bounds
v_PageLowerBound := v_PageSize * v_PageIndex;
v_PageUpperBound := v_PageLowerBound + v_PageSize + 1;
-- Create a temp table to store the select results
execute immediate 'CREATE GLOBAL TEMPORARY TABLE PageIndex
(
IndexID int IDENTITY (1, 1) NOT NULL,
MessageID int
)ON COMMIT delete ROWS ';
IF v_PrivateMessageType=1 then
IF v_unreadOnly=1 AND v_unreplyOnly=1 then
v_string1 := 'INSERT INTO PageIndex (MessageID)
select MessageID from forums_PrivateMessages where SenderUserID=v_UserID AND IsSenderDelete=0 AND IsRead=0 AND IsReply=0 ORDER BY CreateTime DESC ' ;--IsRead --IsReply
ELSIF v_unreadOnly=1 then
v_string1 := 'INSERT INTO PageIndex (MessageID)
select MessageID into v_MessageID from forums_PrivateMessages where SenderUserID=v_UserID AND IsSenderDelete=0 AND IsRead=0 ORDER BY CreateTime DESC ';
ELSIF v_unreplyOnly=1 then
v_string1 := 'INSERT INTO PageIndex (MessageID)
select MessageID into v_MessageID from forums_PrivateMessages where SenderUserID=v_UserID AND IsSenderDelete=0 AND IsReply=0 ORDER BY CreateTime DESC ';
ELSE
v_string1 := 'INSERT INTO PageIndex (MessageID)
select MessageID into v_MessageID from forums_PrivateMessages where SenderUserID=v_UserID AND IsSenderDelete=0 ORDER BY CreateTime DESC ';