SQL调用API获取值

最新推荐文章于 2025-07-04 11:09:45 发布

原创

最新推荐文章于 2025-07-04 11:09:45 发布 · 3.1k 阅读

4 ·

CC 4.0 BY-SA版权

本文介绍如何在SQL中调用API获取JSON数据，并利用parseJSON进行解析。当传入多个日期参数时，返回的JSON串可以通过T-SQL进行处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL调用API获取值

--一、首先要开启组件的配置
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures';
GO

--二、使用sqlserver调用对应的接口以及结果
--/////////////主要代码，调取API获取值/////////////////
DECLARE @ServiceUrl as varchar(1000) 
set @ServiceUrl ='http://tool.bitefu.net/jiari/'  
--////////////////////////////////////////////一共有三个API，自己查看测试后使用
--http://tool.bitefu.net/jiari/ 
--http://www.easybots.cn/holiday_api.net 
--https://www.juhe.cn/docs/api/id/177 —- 这个需要实名认证 
--///////////////////////////////////////////
DECLARE @data varchar(max);
set @data='d=20190430'                  
 
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',@ServiceUrl,'false'
Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/x-www-form-urlencoded'
Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
EXEC sp_OAGetErrorInfo @Object --异常输出

SELECT @ResponseText
--工作日为0，休息日1，节假日2。所以没有做详细判断，都取不到结果则返回-1。
Exec sp_OADestroy @Object

如果传入多个日期的话，会返回一个json串这时我们可以用parseJSON来进行解析

 DECLARE @sad NVARCHAR(200)
EXEC T_Api_Calendar '20190501,20190502,20190503',@sad OUTPUT
PRINT @sad

Select * from parseJSON(@sad)

下面是parseJSON的创建

CREATE FUNCTION dbo.parseJSON( @JSON NVARCHAR(MAX))
	RETURNS @hierarchy TABLE
	  (
	   element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
	   sequenceNo [int] NULL, /* the place in the sequence for the element */
	   parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
	   Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
	   NAME NVARCHAR(2000),/* the name of the object */
	   StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */
	   ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/
	  )
	AS
	BEGIN
	  DECLARE
	    @FirstObject INT, --the index of the first open bracket found in the JSON string
	    @OpenDelimiter INT,--the index of the next open bracket found in the JSON string
	    @NextOpenDelimiter INT,--the index of subsequent open bracket found in the JSON string
	    @NextCloseDelimiter INT,--the index of subsequent close bracket found in the JSON string
	    @Type NVARCHAR(10),--whether it denotes an object or an array
	    @NextCloseDelimiterChar CHAR(1),--either a '}' or a ']'
	    @Contents NVARCHAR(MAX