求救:这2个存储过程是在Oracle中用的,现在急需把他转换成Sql server2000中能用的,高手救命啊!

[yandu111]

这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;

[Teng_s2000]

Create proc
alter proc

sqlStr :=
在sql中不支持，改成sqlStr =

[OracleRoob]

1、create or replace procedure

-->

create procedure

2、varchar2

-->

varchar


3、date

-->

datetime

4、number

-->

decimal

5、:=

-->

=

6、||

-->

+

7、定义变量用declare，变量名以@开头

如：

declare @sqlStr varchar(1000);

[hhyyjj0000]

首先你要熟悉sql和oracle的存储过程的写法，也就是sql中的存储过程写法和oracle中的pl/sql语句语法(以上搂主们列了一些转换方法)，然后要看懂你的oracle存储过程的意思，要知道如何转化到sql中去。
试着给你改第一个，没测试，你再改改阿。

create or replace procedure getDelayAccrualMoney
@ctrtId varchar(50),
@postType varchar(50),
@startDate date,
@endDate date
) as

declare @sqlStr varchar2(1000);
-- declare @cr ResultSet;
declare @bt varchar2(100) = 'paymentAccrual'

select sum(latePay_interest) from gl_mpm_payment_delay_interest f, (
+ getPaymentSql + ) e where f.id_salvey = e.id


GO
这两个都用了游标，返回结果用输出参数表示，sql中的存储过程可以直接返回结果集。