求救:这2个存储过程是在Oracle中用的,现在急需把他转换成Sql server2000中能用的,高手救命啊!
这2个存储过程是在Oracle中用的,现在急需把他转换成Sql server2000中能用的,高手救命啊!
1,create or replace procedure getDelayAccrualMoney(ctrtId varchar2,
postType varchar2,
startDate date,
endDate date,
money out number) is
type ResultSet is ref cursor;
sqlStr varchar2(1000);
cr ResultSet;
bt varchar2(100) := 'paymentAccrual';
begin
sqlStr := 'select sum(latePay_interest) from gl_mpm_payment_delay_interest f, ('
|| getPaymentSql || ') e where f.id_salvey = e.id';
open cr for sqlStr using startDate, endDate, bt, ctrtId, postType;
fetch cr into money;
close cr;
end;
2,create or replace procedure getFinishBillMoney(ctrtId varchar2,
billChpt varchar2,
postType varchar2,
startDate date,
endDate date,
money out number) is
type ResultSet is ref cursor;
sqlStr varchar2(1000);
cr ResultSet;
bt varchar2(100) := 'middlePay';
begin
sqlStr := 'select sum(k.money) from gl_ccm_middle_pay_bill k,
(select x.id from gl_ccm_contract_billData x, gl_ccm_contract_bill y
where x.bill_id = y.id and y.id_contract=:ctrtId and y.bill_code like '''|| billChpt ||'%'') m,
(select f.id from gl_ccm_middle_pay f, (' || getPaymentSql || ') e where f.salvey_id = e.id) n
where k.id_middle = n.id and k.id_billprice = m.id';
open cr for sqlStr using ctrtId, startDate, endDate, bt, ctrtId, postType;
fetch cr
into money;
close cr;
end;