SQL Server- 写入数据到oracle数据库(AP发票接口表为例子)

比如sql中审批好的发票数据，直接写入Oracle 的应付发票的接口 表：

1.创建一个 sql server 的函数，专门用来取审批好的数据

CREATE FUNCTION [dbo].[udf_V_GridPOtoEBSApInvoice] 
(
)
RETURNS TABLE    
AS
RETURN 
select * from a where id= 1

go

2.sql server 先建立一个 oracle 的 【链接服务器】，名称为： ORALinkedServer

对应的IP地址 名称为 192.168.0.66 这台oracle 12 数据库

USE [master]
GO

/****** Object:  LinkedServer [ORALINKEDSERVER]    Script Date: 2021/3/14 22:15:10 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ORALINKEDSERVER', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'192.168.0.66:1521/EBS'

GO

EXEC master.dbo.sp_serveroption @server=N'ORALINKEDSERVER', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORALINKEDSERVER', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORALINKEDSERVER', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORALINKEDSERVER', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORALINKEDSERVER', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORALINKEDSERVER', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORALINKEDSERVER', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORALINKEDSERVER', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ORALINKEDSERVER', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'ORALINKEDSERVER', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'ORALINKEDSERVER', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'ORALINKEDSERVER', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'ORALINKEDSERVER', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

3.创建存储过程，这步就是关键，把 sql server 数据 写入到 oracle，oracle 建立一个自定义表： OUR_AP_INVOICES_INF

CREATE proc [dbo].[stp_InsertLSG_AP_INVOICES_INF]
(
 @id varchar(50) = ''  
)
as

declare @sql nvarchar(500)
declare @cstucount int 

set @sql = ' select @a=count(*)
from OPENQUERY(ORALinkedServer, ''
select * from OUR_AP_INVOICES_INF  where INV_SOURCE = ''''E-FLOW''''
and HEADER_ATTRIBUTE1 = ''''' +  @id   + ''''''')'
  
exec sp_executesql @sql,N'@a int output',@cstucount output      
 --print @cstucount  先检查是否有数据，如果oracle 没有数据，就继续写入oracle

if @cstucount =  0

begin 
INSERT OPENQUERY (ORALinkedServer, 'SELECT 
	OU_NAME
	,VENDOR_NUMBER
	,VENDOR_SITE_CODE
	,INVOICE_NUMBER
	,INVOICE_DATE
	,GL_DATE
	,PAYMENT_TERMS
	,DUE_DATE
	,INV_TYPE
	,HEADER_DESCRIPTION
	,CURRENCY_CODE
	,AMOUNT
	,RATE
	,INV_SOURCE
	,HEADER_ATTRIBUTE1
	,HEADER_ATTRIBUTE2
	,HEADER_ATTRIBUTE3
	,HEADER_ATTRIBUTE4
	,HEADER_ATTRIBUTE5
	,LINE_NUMBER
	,LINE_DESCRIPTION
	,LINE_AMOUNT
	,DIST_ACCOUNT
	,LINE_ATTRIBUTE1
	,LINE_ATTRIBUTE2
	,LINE_ATTRIBUTE3
	,LINE_ATTRIBUTE4
	,LINE_ATTRIBUTE5
	,LINE_ATTRIBUTE6
	,LINE_ATTRIBUTE7
	,LINE_ATTRIBUTE8
	,LINE_ATTRIBUTE9
	,LINE_ATTRIBUTE10
FROM OUR_AP_INVOICES_INF' )


select 
    'OU_NAME' = ouname
	,'VENDOR_NUMBER'= VENDORNUMBER
	,'VENDOR_SITE_CODE'= vendorsitecode
	,'INVOICE_NUMBER'= invoicenumber
	,'INVOICE_DATE'=invoicedate   ----改为发票日期，和总账日期区分开
	,'GL_DATE'= CONVERT(varchar(10), getdate(), 23)		----改为财务审批完的日期，去掉秒，防止写入interface不一致，防止跨月区间关闭而无法写入ap
	,'PAYMENT_TERMS'=PAYMENTTERMS
	,'DUE_DATE'=null
	,'INV_TYPE'='INV'
	,'HEADER_DESCRIPTION'=null
	,'CURRENCY_CODE'= rtrim(vendorcurrency)
	,'AMOUNT'= cast(invoicetotalfee AS numeric(18,2))
	,'RATE'=null
	,'INV_SOURCE'='E-FLOW'
	,'HEADER_ATTRIBUTE1'=id
	,'HEADER_ATTRIBUTE2'=null
	,'HEADER_ATTRIBUTE3'=null
	,'HEADER_ATTRIBUTE4'=null
	,'HEADER_ATTRIBUTE5'=null
	,'LINE_NUMBER'=item_line
	,'LINE_DESCRIPTION'=ponumber
	,'LINE_AMOUNT'=cast(fee AS numeric(18,2))
	,'DIST_ACCOUNT'= costcode
	,'LINE_ATTRIBUTE1'=accountid    
	,'LINE_ATTRIBUTE2'=null
	,'LINE_ATTRIBUTE3'=null
	,'LINE_ATTRIBUTE4'=null
	,'LINE_ATTRIBUTE5'=poline
	,'LINE_ATTRIBUTE6'=ordernumber
	,'LINE_ATTRIBUTE7'=null
	,'LINE_ATTRIBUTE8'=null
	,'LINE_ATTRIBUTE9'=null
	,'LINE_ATTRIBUTE10'=null


from udf_V_GridPOtoEBSApInvoice()
WHERE  id=@id

end

GO

以上存储过程可以放在web等系统中，当执行完后，oracle 的自定义表 OUR_AP_INVOICES_INF   就有数据了， 后续就可以继续在oracle 执行其他操作，将此表数据继续抛送到 oracle的AP标准表中去。

注意 insert 语句的用法，sql server 通过 链接服务器 和 oracle 去交互