源代码如下: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';
end if;
ELSIF v_PrivateMessageType=2 then
IF v_unreadOnly=1 AND v_unreplyOnly=1 then
v_string1 :='INSERT INTO PageIndex (MessageID)
select MessageID into v_MessageID from forums_PrivateMessages where RecipientUserID=v_UserID AND IsRecipientDelete=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 RecipientUserID=v_UserID AND IsRecipientDelete=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 RecipientUserID=v_UserID AND IsRecipientDelete=0 AND IsReply=0 ORDER BY CreateTime DESC';
ELSE
v_string1 :='INSERT INTO PageIndex (MessageID)
select MessageID into v_MessageID from forums_PrivateMessages where RecipientUserID=v_UserID AND IsRecipientDelete=0 ORDER BY CreateTime DESC';
end if;
end if;
execute immediate(v_string1);
execute immediate 'SELECT * FROM forums_PrivateMessages pm INNER JOIN PageIndex p ON pm.MessageID = p.MessageID
WHERE p.IndexID > v_PageLowerBound AND p.IndexID < v_PageUpperBound
ORDER BY p.IndexID';
execute immediate' SELECT Count(*) FROM PageIndex';
execute immediate 'DROP TABLE PageIndex';end forums_PrivateMessages_Get;错误:Error: Hint: Parameter 'v_UserID' is declared but never used in 'forums_PrivateMessages_Get'
Line: 2
Text: ( v_UserID in integer,Error: Hint: Variable 'v_MessageID' is declared but never used in 'forums_PrivateMessages_Get'
Line: 12
Text: v_MessageID integer;Error: Hint: Value assigned to 'v_PageUpperBound' never used in 'forums_PrivateMessages_Get'
Line: 24
Text: v_PageUpperBound := v_PageLowerBound + v_PageSize + 1;
( 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';
end if;
ELSIF v_PrivateMessageType=2 then
IF v_unreadOnly=1 AND v_unreplyOnly=1 then
v_string1 :='INSERT INTO PageIndex (MessageID)
select MessageID into v_MessageID from forums_PrivateMessages where RecipientUserID=v_UserID AND IsRecipientDelete=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 RecipientUserID=v_UserID AND IsRecipientDelete=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 RecipientUserID=v_UserID AND IsRecipientDelete=0 AND IsReply=0 ORDER BY CreateTime DESC';
ELSE
v_string1 :='INSERT INTO PageIndex (MessageID)
select MessageID into v_MessageID from forums_PrivateMessages where RecipientUserID=v_UserID AND IsRecipientDelete=0 ORDER BY CreateTime DESC';
end if;
end if;
execute immediate(v_string1);
execute immediate 'SELECT * FROM forums_PrivateMessages pm INNER JOIN PageIndex p ON pm.MessageID = p.MessageID
WHERE p.IndexID > v_PageLowerBound AND p.IndexID < v_PageUpperBound
ORDER BY p.IndexID';
execute immediate' SELECT Count(*) FROM PageIndex';
execute immediate 'DROP TABLE PageIndex';end forums_PrivateMessages_Get;错误:Error: Hint: Parameter 'v_UserID' is declared but never used in 'forums_PrivateMessages_Get'
Line: 2
Text: ( v_UserID in integer,Error: Hint: Variable 'v_MessageID' is declared but never used in 'forums_PrivateMessages_Get'
Line: 12
Text: v_MessageID integer;Error: Hint: Value assigned to 'v_PageUpperBound' never used in 'forums_PrivateMessages_Get'
Line: 24
Text: v_PageUpperBound := v_PageLowerBound + v_PageSize + 1;
解决方案 »
- 新建用户无法创建程序包和存储过程,是什么原因?
- oracle通信通道的文件结束
- oracle序列的问题
- oracle自定义排序函数
- 【修正后】有关oracle查询语句性能的问题,附带执行计划。请有经验的朋友帮忙看看,谢谢。
- 求助:emctl start dbconsole启动失败
- 如何查询出A表中不存在于B中的记录
- 如果安装了多个ORACLE,会有多个TNSNAMES.ORA文件,但是应用程序用的是哪个TNSNAMES.ORA文件
- 请大侠提供个Oracle 7.3的下载地址!!!谢啦!!!
- 大家好,请问一下学习ORACLE应从哪里入手,有什么好的办法没有.
- 关于max命令的
- 用exp把服务器上Oracle8i的数据导出来,字符集是US7ASCII,我想在自己电脑上装个Oracle8i,把数据导进去,应该怎样设置字符集?
select MessageID from forums_PrivateMessages where SenderUserID=v_UserID AND IsSenderDelete=0 AND IsRead=0 AND IsReply=0 ORDER BY CreateTime DESC' ;--IsRead --IsReply===================v_UserID should be wrote like this ''||v_UserID||''change all the parameter ...
compilation errors for PROCEDURE JSJL.FORUMS_PRIVATEMESSAGES_GETError: Hint: Parameter 'v_UserID' is declared but never used in 'forums_PrivateMessages_Get'
Line: 2
Text: ( v_UserID in integer,Error: Hint: Variable 'v_MessageID' is declared but never used in 'forums_PrivateMessages_Get'
Line: 12
Text: v_MessageID integer;Error: Hint: Value assigned to 'v_PageUpperBound' never used in 'forums_PrivateMessages_Get'
Line: 24
Text: v_PageUpperBound := v_PageLowerBound + v_PageSize + 1;
问题解决了
不是 ''||v_UserID||''
应该是 '||v_UserID||'
不是 '' ¦ ¦v_UserID ¦ ¦''
应该是 ' ¦ ¦v_UserID ¦ ¦' 那些是''|| ||''