在存储过程中封装数据转换

已完成

尽管每当需要转换数据时，都可以在脚本中运行 CREATE EXTERNAL TABLE AS SELECT (CETAS) 语句，但最好在存储过程中封装转换操作。 通过此方法，你可提供参数、检索输出并在单个过程调用中包含其他逻辑，从而更轻松地操作数据转换。

例如，下面的代码创建一个存储过程，如果外部表已存在，则删除该表，然后再使用指定年份的订单数据重新创建：

CREATE PROCEDURE usp_special_orders_by_year @order_year INT
AS
BEGIN

	-- Drop the table if it already exists
	IF EXISTS (
                SELECT * FROM sys.external_tables
                WHERE name = 'SpecialOrders'
            )
        DROP EXTERNAL TABLE SpecialOrders

	-- Create external table with special orders
	-- from the specified year
	CREATE EXTERNAL TABLE SpecialOrders
		WITH (
			LOCATION = 'special_orders/',
			DATA_SOURCE = files,
			FILE_FORMAT = ParquetFormat
		)
	AS
	SELECT OrderID, CustomerName, OrderTotal
	FROM
		OPENROWSET(
			BULK 'sales_orders/*.csv',
			DATA_SOURCE = 'files',
			FORMAT = 'CSV',
			PARSER_VERSION = '2.0',
			HEADER_ROW = TRUE
		) AS source_data
	WHERE OrderType = 'Special Order'
	AND YEAR(OrderDate) = @order_year
END

注意

如前文所述，删除现有的外部表不会删除包含其数据文件的文件夹。 如果目标文件夹存在，则必须在运行存储过程之前显式将其删除，否则会发生错误。

除了封装 Transact-SQL 逻辑外，存储过程还具有以下优点：

减少客户端到服务器的网络流量

过程中的命令作为单批代码执行，可显着减少服务器和客户端之间的网络流量，因为网络中只发送了执行过程的调用。

提供安全边界

多个用户和客户端程序可以通过过程对基础数据库对象执行操作，即使用户和程序对这些基础对象没有直接权限。 过程将控制执行哪些进程和活动，并保护基础数据库对象；无需在单个对象级别授予权限且简化了安全层。

简化维护

数据转换中涉及的逻辑或文件系统位置的所有更改只能应用于存储过程，无需更新客户端应用程序或其他调用函数。

提高了性能

存储过程在第一次执行时进行编译，生成的执行计划保存在缓存中，并在后续运行相同的存储过程时重用。 因此，处理过程所花费的时间较少。