oracle本地执行sql,Sql调用Oracle的函数更新本地数据

USE [CollectionSystem]

GO

/****** 对象:  StoredProcedure [dbo].[OverDueForfeit]    脚本日期: 05/31/2011 09:38:05 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[OverDueForfeit] as

delete from dbo.contract_id_over_due

delete from Tb_RentalOverDue

insert into dbo.contract_id_over_due

select distinct contract_id from dbo.OverDueInfo

DECLARE @TIME VARCHAR(10),@ID varchar(10)

DECLARE @TSQL varchar(8000),@constant1 varchar(10),@constant2 varchar(10)--,@id varchar(10)

--SELECT  @ID='142'

declare cursor1 cursor for         --定义游标cursor1

select contract_id from contract_id_over_due   --使用游标的对象(跟据需要填入select)

open cursor1                       --打开游标

fetch next from cursor1 into @ID  --将游标向下移行，获取的数据放入之前定义的变量@id,@name中

while @@fetch_status=0           --判断是否成功获取数据

begin

select @TIME=CONVERT(VARCHAR(10),processing_dte,120)

from openquery

(

FIATPRODUCT,

'select processing_dte from cms_fiat.processing_date'

)

select  @constant1='C'

select  @constant2='yyyy-mm-dd'

select  @TSQL='select contract_id,rental_id,ACCRUED_OD_AMT from OPENQUERY(FIATPRODUCT,''select * from Table(CMS_FIAT.FN_CTR_RENTAL_CALC_OD('''''+@ID+''''','''''+@constant1+''''',to_date('''''+@TIME+''''','''''+@constant2+'''''))) '')'

insert into Tb_RentalOverDue   --进行相应处理(跟据需要填入SQL文)

exec (@TSQL)

fetch next from cursor1 into @id  --将游标向下移行

end

close cursor1                   --关闭游标

deallocate cursor1