Mengenkapsulasi perubahan data dalam prosedur tersimpan

Selesai

Meskipun Anda dapat menjalankan CREATE EXTERNAL TABLE AS SELECT pernyataan (CETAS) dalam skrip setiap kali Anda perlu mengubah data, ada baiknya untuk merangkum operasi transformasi dalam prosedur tersimpan. Pendekatan ini dapat mempermudah operasional perubahan data dengan memungkinkan Anda menyediakan parameter, mengambil output, dan menyertakan logika tambahan dalam satu panggilan prosedur.

Misalnya, kode berikut membuat prosedur tersimpan yang menghapus tabel eksternal jika sudah ada sebelum membuatnya kembali dengan data urutan untuk tahun yang ditentukan:

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

Catatan

Seperti yang dibahas sebelumnya, menghilangkan tabel eksternal yang ada tidak menghapus folder yang berisi file datanya. Anda harus menghapus folder target secara eksplisit jika folder tersebut ada sebelum menjalankan prosedur tersimpan, jika tidak, kesalahan akan terjadi.

Selain mengenkapsulasi logika Transact-SQL, prosedur tersimpan juga memberikan manfaat berikut:

Mengurangi lalu lintas jaringan klien ke server

Perintah dalam prosedur dijalankan sebagai satu kumpulan kode; yang secara signifikan dapat mengurangi lalu lintas jaringan antara server dan klien karena hanya panggilan untuk menjalankan prosedur yang dikirim melalui jaringan.

Memberikan batas keamanan

Beberapa pengguna dan program klien dapat melakukan operasi pada objek database yang mendasari melalui prosedur, bahkan jika pengguna dan program tidak memiliki izin langsung pada objek yang mendasarinya. Prosedur mengontrol proses dan aktivitas apa yang dilakukan dan melindungi objek database yang mendasarinya; menghilangkan persyaratan untuk memberikan izin pada tingkat objek individu dan menyederhanakan lapisan keamanan.

Memudahkan perawatan

Setiap perubahan dalam logika atau lokasi sistem file yang terlibat dalam perubahan data hanya dapat diterapkan pada prosedur tersimpan; tanpa memerlukan pembaruan untuk aplikasi klien atau fungsi panggilan lainnya.

Meningkatkan performa

Prosedur tersimpan dikompilasi saat pertama kali dieksekusi, dan rencana eksekusi yang dihasilkan disimpan dalam cache dan digunakan kembali pada proses berikutnya dari prosedur tersimpan yang sama. Akibatnya, dibutuhkan lebih sedikit waktu untuk memproses prosedur.