首页 >  SQL  > HTTP发送接收存储过程_POST

HTTP发送接收存储过程_POST

本文由 猛 于 2021-12-22 15:05 发布在  SQL    

 

 

/*

 SELECT dbo.FN_HTTP_POST('https://oapi.dingtalk.com/gettoken?corpid=ding9834123f9d36af30&corpsecret=zw7Rf-JRfj5vxhlXhfz4F5t4X000aH2gbBaQq6MuLcdV7Zv8uDOevvOSKtYoIDNn',
                         '',
                         'application/json', 'application/json');
*/
 
 ALTER FUNCTION [dbo].[FN_HTTP_POST](
@URL VARCHAR(256),
@DATA VARCHAR(8000),
@REQ_H_ACCEPT VARCHAR(256),
@REQ_H_CONTENT_TYPE VARCHAR(256)
 )
 RETURNS VARCHAR(max)
 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
 ;

 

 

 

 
 
 /*
  SELECT dbo.FN_HTTP_GET('https://oapi.dingtalk.com/gettoken?corpid=ding9834123f9d36af30&corpsecret=zw7Rf-JRfj5vxhlXhfz4F5t4X000aH2gbBaQq6MuLcdV7Zv8uDOevvOSKtYoIDNn',
                         '',
                         'application/json', 'application/json');
 
 */
-- HTTP GET 请求

ALTER FUNCTION [dbo].[FN_HTTP_GET](
@URL VARCHAR(256),
@DATA VARCHAR(8000),
@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,'get',@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 

 

 

 
 

-- HTTP PUT 请求

ALTER FUNCTION [dbo].[FN_HTTP_PUT](
@URL VARCHAR(256),
@DATA VARCHAR(8000),
@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,'put',@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;

 

 

ALTER FUNCTION [dbo].[fn_UrlDecode] 
( 
 @Str VARCHAR(8000)--已经编码的字符串 
) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 
  DECLARE @Position  INT;          --'%'字符所在位置 
  DECLARE @Chr       CHAR(16);     --字符常量 
  DECLARE @Pattern   CHAR(21); 
  DECLARE @ParseStr  VARCHAR(8000);--解码后的字符串 
  DECLARE @Hex       UNIQUEIDENTIFIER;--定义16进制模板,因为GUID方便转为BYTE 
  DECLARE @CurrWord  INT        ;--当前字 
  DECLARE @BitsCount INT        ;--当前解码位数 
  DECLARE @HightByte TINYINT;--高位字节 
  DECLARE @LowByte   TINYINT;--低位字节 
   
  /****************变量初始化***********************/ 
  SET     @Chr = '0123456789abcdef'; 
  SET     @Pattern = '%[%][a-f0-9][a-f0-9]%'; 
  SET     @ParseStr=@Str; 
  SET     @Hex= '00000000-0000-0000-0000-000000000000'; 
  SET     @CurrWord=0; 
  SET     @BitsCount=0; 
  SET     @HightByte=0; 
  SET     @LowByte=0; 
   
  IF (@Str IS NOT NULL OR @Str<>'') 
   BEGIN 
     SET    @Position = PATINDEX(@Pattern, @ParseStr);--取得第一个'%'所在的位置 
     WHILE @Position>0 
      BEGIN 
        SET @Hex=STUFF(@Hex,7,2,LEFT(RIGHT(@ParseStr,len(@ParseStr) - @Position),2)); 
        SET @HightByte=CAST(CAST(@Hex AS BINARY(1)) AS INT); 
         
        IF (@HightByte & 127=@HightByte) 
         BEGIN--ASCII码直接转为UTF-8或UTF-16 
           SET @CurrWord=@HightByte; 
           SET @BitsCount=1; 
         END 
          
        IF (@HightByte & 192=192) 
         BEGIN--Unicode编码 
           SET @CurrWord=@HightByte & 31 ; 
           SET @BitsCount=2; 
         END 
 
        IF (@HightByte & 224=224) 
         BEGIN--UTF-8编码 
            SET @CurrWord = @HightByte & 15 
            SET @BitsCount = 3   
         END 
 
        IF (@HightByte & 240=240) 
         BEGIN--UTF-16编码 
            SET @CurrWord = @HightByte & 7 
            SET @BitsCount = 4   
         END 
 
        DECLARE @Index INT;         
        DECLARE @NEWCHAR NVARCHAR(2); 
        SET @Index=1; 
        SET @NEWCHAR=''; 
        WHILE @Index<@BitsCount 
         BEGIN 
              IF (LEN(@ParseStr)-@Position-3*@Index)<0 
               BEGIN 
                   SET @ParseStr=@Str ;    
                   SET @Position=0; 
                   BREAK;               
               END 
            SET @NEWCHAR = LEFT(RIGHT(@ParseStr,LEN(@ParseStr) - @Position - 3* @Index),2);    
            --如果没有16进制编码则中断外层WHILE 
            IF @NEWCHAR NOT LIKE '[a-f0-9][a-f0-9]' 
             BEGIN 
                SET @ParseStr = @Str 
                SET @Position=0;--中断外层WHILE 
                BREAK; 
             END     
 
            SET @Hex = STUFF(@Hex, 7, 2, @NEWCHAR)       
 
            SET @LowByte = CAST(CAST(@Hex AS BINARY(1)) AS INT); 
 
            IF @LowByte&192=192 
            BEGIN 
                SET @ParseStr = @Str 
                SET @Position=0;--中断外层WHILE 
                BREAK; 
            END  
             
            SET @CurrWord = (@CurrWord * 64) | (@LowByte & 63)               
            SET @Index =@Index+ 1                                                 
         END                                    
 
         IF @BitsCount > 1            
          SET @ParseStr = STUFF(@ParseStr, @Position, 3*(@BitsCount), NCHAR(@CurrWord)) 
         ELSE  
          BEGIN 
            set @ParseStr = STUFF(@ParseStr, @Position, 2, NCHAR(@CurrWord)) 
            set @ParseStr = STUFF(@ParseStr, @Position+1, 1, N'')        
          END 
        ----取得下一个'%'所在的位置 
        SET  @Position = PATINDEX(@Pattern, @ParseStr); 
      END 
   END 
   RETURN @ParseStr; 
END