sqlserver的几个函数【get,post请求】,【触发器】
ps: 不能为null,sql函数isnull()无用,用if判断
创建函数【P_GET_HttpRequestData】
CREATE PROCEDURE [dbo].[P_GET_HttpRequestData](
@URL varchar(500),
@status int=0 OUT
)
AS
BEGIN
DECLARE @object int,
@errSrc int
/*初始化对*/
declare @str nvarchar(200)
declare @ResponseText nvarchar(200)
EXEC @status = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0', @object OUT;
print '===================='
print @object
IF @status <> 0
BEGIN
EXEC SP_OAGetErrorInfo @object, @errSrc OUT
RETURN
END
/*创建链接*/
EXEC @status= SP_OAMethod @object,'open',NULL,'GET',@URL
IF @status <> 0
BEGIN
EXEC SP_OAGetErrorInfo @object, @errSrc OUT
RETURN
END
EXEC @status=SP_OAMethod @object,'setRequestHeader','Content-Type','application/x-www-form-urlencoded'
/*发起请求*/
EXEC @status= SP_OAMethod @object,'send',null
print '--------------------------------'
print @str
print '--------------------------------'
print @status
IF @status <> 0
BEGIN
EXEC SP_OAGetErrorInfo @object, @errSrc OUT
RETURN
END
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT --@ResponseText为http返回的内容
Select @ResponseText
print @ResponseText
Exec sp_OADestroy @Object
END;
创建函数【FN_HTTP_POST】
ALTER FUNCTION FN_HTTP_POST(
@URL VARCHAR(256),
@DATA VARCHAR(2000),
@REQ_H_ACCEPT VARCHAR(256),
@REQ_H_CONTENT_TYPE VARCHAR(256)
)
RETURNS VARCHAR(5000)
AS
BEGIN
DECLARE
@object int,
@returnStatus int,
@returnText varchar(5000),
@errMsg varchar(2000),
@httpStatus varchar(20)
;
/* 初始化 */
EXEC @returnStatus = SP_OACreate 'Msxml2.ServerXMLHTTP.3.0',@object OUT;
IF @returnStatus <> 0
BEGIN
EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
RETURN ('初始化对象失败,' + @errMsg + ISNULL(@returnText,''));
END
/*创建链接*/
EXEC @returnStatus= SP_OAMethod @object,'open',NULL,'post',@URL,'false';
IF @returnStatus <> 0
BEGIN
EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
RETURN ('创建连接失败,' + @errMsg + ISNULL(@returnText, ''));
END
EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Accept',@REQ_H_ACCEPT;
EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Type',@REQ_H_CONTENT_TYPE;
EXEC @returnStatus=SP_OAMethod @object,'setRequestHeader',NULL,'Content-Length','1000000';
/*发起请求*/
EXEC @returnStatus= SP_OAMethod @object,'send',NULL,@DATA;
IF @returnStatus <> 0
BEGIN
EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
RETURN ('发起请求失败,' + @errMsg + ISNULL(@returnText, ''));
END
/*获取HTTP状态代码*/
EXEC @returnStatus = SP_OAGetProperty @Object, 'Status', @httpStatus OUT;
IF @returnStatus <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Object, @errMsg OUT, @returnText OUT;
RETURN ('获取HTTP状态代码失败,' + @errMsg + ISNULL(@returnText, ''));
END
IF @httpStatus <> 200
BEGIN
RETURN ('访问错误,HTTP状态代码:' + @httpStatus);
END
/*获取返回信息*/
EXEC @returnStatus= SP_OAGetProperty @object,'responseText',@returnText OUT;
IF @returnStatus <> 0
BEGIN
EXEC SP_OAGetErrorInfo @object, @errMsg OUT, @returnText OUT;
RETURN ('获取返回信息失败,' + @errMsg + ISNULL(@returnText, ''));
END
RETURN @returnText;
END ;
触发器【tri_InsertDlya】
begin
declare @url varchar(256),@param varchar(2000),@money varchar(20),@atypeid varchar(20),@clientPhone varchar(20),@customer varchar(20),@dept varchar(20),@deptPhone varchar(20),@vchCode varchar(20), @arrears varchar(20),@btypeid varchar(20), @etypeid varchar(20) ,@vchType varchar(20) ;
select @vchType = d.vchType , @vchCode = d.vchcode , @money = d.total ,@atypeid = d.atypeid ,@btypeid = d.btypeid ,@etypeid = d.etypeid from Inserted d ;
select @arrears = isnull(b.artotal,'0') ,@clientPhone = isnull(b.area,'') , @customer = isnull(b.FullName,'无') from Btype b where b.typeId = @btypeid ;
if(@customer is null)
set @customer = '';
if(@clientPhone is null)
set @clientPhone = '';
if(@arrears is null)
set @arrears = '0';
set @param = '{"createBy":"101","vchCode":"'+@vchCode+'","remarks":"'+@atypeid+'","payMoney":"'+@money+'","customer":"'+@customer+'","customerPhone":"'+@clientPhone+'","arrearsMoney":"'+@arrears+'"}';
INSERT AA_send_info(create_by,vch_code,send_info) VALUES (0,@vchCode ,@param );
SELECT dbo.FN_HTTP_POST('http://2059039q45.goho.co//wxUser/sns/receiveParamVchRe',@param,'application/json','application/json') ASYNC;
end
触发器【tyi_updateDlya】
begin
declare @url varchar(256),@param varchar(2000),@money varchar(20),@atypeid varchar(20),@clientPhone varchar(20),@customer varchar(20),@dept varchar(20),@deptPhone varchar(20),@vchCode varchar(20), @arrears varchar(20),@btypeid varchar(20), @etypeid varchar(20) ,@vchType varchar(20) ,@realMoney varchar(30);
select @vchType = d.vchType , @vchCode = d.vchcode , @money = d.total ,@atypeid = d.atypeid ,@btypeid = d.btypeid ,@etypeid = d.etypeid ,@realMoney = d.FreeDom14 from Inserted d ;
select @arrears = b.artotal ,@clientPhone = b.area, @customer = b.FullName from Btype b where b.typeId = @btypeid ;
select @dept= dept.fullname, @deptPhone = e2.tel from Employee e left join Department dept on dept.typeid = e.department left join Employee e2 on e2.typeid = dept.EtypeID where e.typeid = @etypeid;
if(@vchType = '11')
select @realMoney = sum(total) from DlyA where vchcode = @vchCode and atypeid not in ('0000500001','0000100006','000060000400001') ;
if(@customer is null)
set @customer = '';
if(@clientPhone is null)
set @clientPhone = '';
if(@arrears is null)
set @arrears = '0';
if(@realMoney is null)
set @realMoney = '0';
if(@dept is null)
set @dept = '';
if(@deptPhone is null)
set @deptPhone = '';
set @param = '{"createBy":"101","payMoney":"'+@money+'","vchType":"'+@vchType+'","vchCode":"'+@vchCode+'","remarks":"'+@atypeid+'","dept":"'+@dept+'","deptPhone":"'+@deptPhone+'","customer":"'+@customer+'","customerPhone":"'+@clientPhone+'","arrearsMoney":"'+@arrears+'","realMoney":"'+@realMoney+'"}';
INSERT AA_send_info(create_by,vch_code,send_info) VALUES (1,@vchCode ,@param );
if (@vchType = '4' and @atypeid = '0000100006' )
SELECT dbo.FN_HTTP_POST( 'http://2059039q45.goho.co//wxUser/sns/receiveParamVchRe',@param,'application/json','application/json') ASYNC;
if (@vchType in ( '4' ,'11' , '45') and @etypeid != '' )
SELECT dbo.FN_HTTP_POST( 'http://2059039q45.goho.co//wxUser/sns/receiveParamUpdate',@param,'application/json','application/json') ASYNC;
end