SQL调用webApi

　　　　有些时候对一些接口的调用放在数据库比放在程序里更好控制，我这边用到的场景就是，更具状态变化去调用，我处理的方法就是放在

触发器里面，只要状态数据变化就触发，这样，我就不需要去考虑很多复杂的业务逻辑场景。具体怎么操作，看下面

　　　　数据库版本 Microsoft SQL Server 2012

　　　　1、基础调用方法，放在一个函数里面：

USE [数据库名]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_webapi]
(@url varchar(1000),--请求地址
 @data varchar(1000)--请求参数
)
returns varchar(1000)
as
begin
set @url=@url+'?'+@data
Declare @Object as Int
Declare @ResponseText AS  varchar(1000)   ;     
Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'POST',@url,'false'
Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/x-www-form-urlencoded'
Exec sp_OAMethod @Object, 'send', NULL, null --发送数据
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
EXEC sp_OAGetErrorInfo @Object --异常输出
Exec sp_OADestroy @Object
return @ResponseText
end

 　　　　2、辅助函数：（去空格）

USE [数据库名]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--去除字符串中的空格和换行
ALTER function  [dbo].[fn_strWithoutBlankAndWrap](@str varchar(1000))
returns varchar(1000)
as
begin
if LEN(@str)>0
	begin
		set @str= REPLACE(@str,' ','')
		set @str=REPLACE(@str,'
','')
	end
return @str
end

 　　　　3、具体函数调用

USE [数据库名]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--GPS2.0系统占车数据推送
ALTER procedure [dbo].[GPS2_ChangeVehicleShiKongState]
as
begin
--初始化一些默认值
declare  @tenantId int =1--默认值字段
declare @url varchar(1000)='http://123.126.34.10:2499/api/yingzhang/ChangeVehicleShiKongState'--测试地址
declare @data varchar(1000)--请求的参数
declare @responseResult varchar(1000)--请求结果
declare @IsSuccess bit --是否请求成功

declare @LicNo varchar(100)--车牌号
declare @OrganizeCode varchar(100)
declare @ZhanCheState int=1 --占车状态
declare cus_zhanche cursor for(
    select  distinct LicNo ,THEIRSHOP from Car_CarInfo
    where ServiceStatus3=70
)
open cus_zhanche
fetch next from cus_zhanche into @LicNo,@OrganizeCode
while @@fetch_status=0
begin
set @data=
'&tenantId='+cast(@tenantId as varchar(50))
+'&licenceNumber='+dbo.UrlEncode(@LicNo)
+'&organizeCode='+@OrganizeCode
+'&isShiKong='+cast(@ZhanCheState as varchar(8));
set @responseResult= dbo.[fn_webapi](@url,@data)--请求webapi
set @responseResult=dbo.[fn_strWithoutBlankAndWrap](@responseResult)--结果去空格和换行
if substring(@responseResult,12,1)='t'
set @IsSuccess=1
else set @IsSuccess=0
declare @Parameter varchar(1000)
=@LicNo+','+@OrganizeCode+','
+cast(@ZhanCheState as varchar(8));
insert into Log_GPS2webapi
(CreateTime,FunctionName,IsSuccess,AskUrl,Parameter,ResponseResult)values
(getdate(),'ChangeVehicleShiKongState',@IsSuccess,@url,@Parameter,@responseResult)
fetch next from cus_zhanche into @LicNo,@OrganizeCode
end
end

　　　　4、再来一条具体调用的例子：

USE [数据库]
GO
/****** Object:  StoredProcedure [dbo].[GPS2_ChangeVehicleFenceMonitorState]    Script Date: 2017/7/11 14:25:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--2016-12-08-zl
--GPS2.0系统车辆是否启用电子围栏报警
ALTER procedure [dbo].[GPS2_ChangeVehicleFenceMonitorState]
 @licenceNumber varchar(100),--车牌号
 @railOutIsEnable varchar(10),--是否启动电子围栏 true/false 
 @railOrganizeName varchar(100)--可空:'',门店变化的时候，要带上变化了的门店
as
begin
--初始化一些默认值
declare  @tenantId int =1--默认值字段
declare @url varchar(1000)='http://123.126.34.10:2499/api/yingzhang/ChangeVehicleFenceMonitorState'--测试地址
declare @data varchar(1000)--请求的参数
declare @responseResult varchar(1000)--请求结果
declare @IsSuccess bit --是否请求成功
set @data
='licenceNumber='+dbo.UrlEncode(@licenceNumber)
+'&railOrganizeName='+dbo.UrlEncode(@railOrganizeName)
+'&railOutIsEnable='+@railOutIsEnable
+'&tenantId='+cast(@tenantId as varchar(50))

set @responseResult= dbo.[fn_webapi](@url,@data)--请求webapi
set @responseResult=dbo.[fn_strWithoutBlankAndWrap](@responseResult)--结果去空格和换行
if substring(@responseResult,12,1)='t'
set @IsSuccess=1
else set @IsSuccess=0
declare @Parameter varchar(1000)
=@licenceNumber+','+@railOrganizeName+','
+@railOutIsEnable
insert into Log_GPS2webapi
(CreateTime,FunctionName,IsSuccess,AskUrl,Parameter,ResponseResult)values
(getdate(),'ChangeVehicleFenceMonitorState',@IsSuccess,@url,@Parameter,@responseResult)
end