求一存储过程（sqlserver转oracle）
CREATE PROCEDURE BCP_transaction_BillList
(
@Page_Nonvarchar(10) = '1 ',
@Page_Sizenvarchar(10) = '10 ',
@Record_Countbigint = 0 output,
@TIDnvarchar(60) = ' ',
@NOnvarchar(30) = ' ',
@CPID nvarchar(30) = ' ',
@CPNamenvarchar(50) = ' ',
@TransTypenvarchar(10) = ' ',
@SItemPriceint ,
@EItemPriceint ,
@SDatenvarchar(8) = ' ',
@EDatenvarchar(8) = ' ',
@OrderIDnvarchar(80) = ' ',
@UserIDnvarchar(50) = ' ',
@totalAmt money = 0 output
)
AS
BEGIN
set nocount on
declare @sSQL nvarchar(4000), @sSQLList nvarchar(4000), @sSQLCount nvarchar(4000), @sSQLAmt nvarchar(4000), @sSub nvarchar(4000)
IF @SItemPrice IS NULL or @SItemPrice = ' '
SET @SItemPrice = 0
IF @EItemPrice IS NULL or @SItemPrice = ' '
SET @EItemPrice = 9999
declare @subPage_Size nvarchar(10)
set @subPage_Size = Convert(nvarchar(10),((Convert(int,@Page_No) - 1) * Convert(int,@Page_Size)))
set @sSQLList = '
select top ' + @Page_Size + ' tid, T_ORDER.no, name, provider_id, TransType, bank_transAmt, bank_sysDate, bank_sysSeqid, user_id '
set @sSQLCount = '
select count(*) '
set @sSQLAmt = '
select sum(bank_transAmt) '
set @sSQL = '
from T_ORDER , T_PROVIDER
where T_ORDER.provider_id = T_PROVIDER.no and bank_transAmt between ' + convert(nvarchar(10), @SItemPrice) + ' and ' + convert(nvarchar(10), @EItemPrice)
IF @SDate <> ' ' AND @EDate <> ' '
SET @sSQL = @sSQL + '
and bank_sysDate between ' ' ' + @SDate + ' ' ' and ' ' ' + @EDate + ' ' ' '
if isnull(@CPID, ' ') <> ' '
set @sSQL = @sSQL + '
and cprovider_id = ' ' ' + @CPID + ' ' ' '
if isnull(@CPName, ' ') <> ' '
set @sSQL = @sSQL + '
and name like ' '% ' + @CPName + '% ' ' '
if isnull(@TID, ' ') <> ' '
set @sSQL = @sSQL + '
and tid = ' ' ' + @TID + ' ' ' '
if isnull(@NO, ' ') <> ' '
set @sSQL = @sSQL + '
and no = ' ' ' + @NO + ' ' ' '
if isnull(@OrderID, ' ') <> ' '
set @sSQL = @sSQL + '
and bank_sysSeqid like ' '% ' + @OrderID + '% ' ' '
IF isnull(@UserID, ' ') <> ' '
SET @sSQL = @sSQL + '
and User_ID like ' '% ' + @UserID + '% ' ' '
set @sSub = N ' and TID not in (select top ' + @subPage_Size + ' TID '
set @sSub = @sSub + @sSQL + ' order by TID desc) '
set @sSQLCount = @sSQLCount + @sSQL
print @sSQLCount
EXEC (@sSQLCount)
set @sSQLAmt = @sSQLAmt + @sSQL
print @sSQLAmt
EXEC (@sSQLAmt)
set @sSQLList = @sSQLList + @sSQL + @sSub + ' order by tid desc '
print @sSQLList
EXEC (@sSQLList)
set nocount off
END
GO
[解决办法]
--太长了
CREATE or replace PROCEDURE BCP_transaction_BillList
(
Page_Non in varchar2,
Page_Sizen in varchar2,
Record_Count out int,
TIDn in varchar2,
NOn in varchar2,
CPID in varchar2,
CPNamen in varchar2,
TransTypen in varchar2,
SItemPrice in int,
EItemPrice in int,
SDaten in varchar2,
EDaten in varchar2,
OrderIDn in varchar2,
UserIDn in varchar2,
totalAmt out number
)
is
sSQL varchar2(4000);
sSQLList varchar2(4000);
sSQLCount varchar2(4000);
sSQLAmt varchar2(4000);
sSub varchar2(4000);
subPage_Size varchar2(10);
BEGIN
IF SItemPrice IS NULL or SItemPrice = ' ' then
SItemPrice:= 0;
end if;
IF EItemPrice IS NULL or SItemPrice = ' ' then
EItemPrice:= 9999
end if;
......
end;
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html