Bekerja dengan prosedur tersimpan SQL

Prosedur tersimpan adalah kumpulan pernyataan yang dapat dipanggil dari kueri lain atau prosedur tersimpan lainnya. Prosedur dapat mengambil argumen input dan menampilkan nilai sebagai output. Anda memberi nama dan menyimpan prosedur dalam set data BigQuery. Prosedur tersimpan dapat mengakses atau memodifikasi data di beberapa set data oleh banyak pengguna. Prosedur ini juga dapat berisi kueri multi-pernyataan.

Beberapa prosedur tersimpan telah diintegrasikan ke dalam BigQuery dan tidak perlu dibuat. Ini disebut dengan prosedur sistem dan Anda dapat mempelajarinya lebih lanjut di Referensi prosedur sistem.

Prosedur tersimpan mendukung pernyataan bahasa prosedur, yang memungkinkan Anda melakukan berbagai hal seperti menentukan variabel dan menerapkan alur kontrol. Anda dapat mempelajari pernyataan bahasa prosedur lebih lanjut dalam Referensi bahasa prosedur.

Membuat prosedur tersimpan

Untuk membuat prosedur, gunakan pernyataan CREATE PROCEDURE.

Dalam contoh konseptual berikut, procedure_name mewakili prosedur dan isi prosedur yang muncul di antara pernyataan BEGIN dan END:

CREATE PROCEDURE dataset_name.procedure_name()
BEGIN
-- statements here
END

Contoh berikut menunjukkan prosedur yang berisi kueri multi-pernyataan. Kueri multi-pernyataan menetapkan variabel, menjalankan pernyataan INSERT, dan menampilkan hasilnya sebagai string teks berformat.

CREATE OR REPLACE PROCEDURE mydataset.create_customer()
BEGIN
  DECLARE id STRING;
  SET id = GENERATE_UUID();
  INSERT INTO mydataset.customers (customer_id)
    VALUES(id);
  SELECT FORMAT("Created customer %s", id);
END

Pada contoh sebelumnya, nama prosedurnya adalah mydataset.create_customer, dan isi prosedur muncul di antara pernyataan BEGIN dan END.

Untuk memanggil prosedur, gunakan pernyataan CALL:

CALL mydataset.create_customer();

Memasukkan nilai dengan parameter input

Prosedur dapat memiliki parameter input. Parameter input mengizinkan input untuk suatu prosedur, tetapi tidak mengizinkan output.

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING)
BEGIN
DECLARE id STRING;
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

Mengeluarkan nilai dengan parameter output

Prosedur dapat memiliki parameter output. Parameter output menampilkan nilai dari prosedur, tetapi tidak mengizinkan input untuk prosedur. Untuk membuat parameter output, gunakan kata kunci OUT sebelum nama parameter.

Misalnya, versi prosedur ini menampilkan ID pelanggan baru melalui parameter id:

CREATE OR REPLACE PROCEDURE mydataset.create_customer(name STRING, OUT id STRING)
BEGIN
SET id = GENERATE_UUID();
INSERT INTO mydataset.customers (customer_id, name)
  VALUES(id, name);
SELECT FORMAT("Created customer %s (%s)", id, name);
END

Untuk memanggil prosedur ini, Anda harus menggunakan variabel untuk menerima nilai output:

--- Create a new customer record.
DECLARE id STRING;
CALL mydataset.create_customer("alice",id);

--- Display the record.
SELECT * FROM mydataset.customers
WHERE customer_id = id;

Memasukkan dan mengeluarkan nilai dengan parameter input/output

Prosedur juga dapat memiliki parameter input/output. Parameter input/output menampilkan nilai dari prosedur dan juga menerima input untuk prosedur tersebut. Untuk membuat parameter input/output, gunakan kata kunci INOUT sebelum nama parameter. Untuk mengetahui informasi selengkapnya, lihat Mode argumen.

Mengizinkan rutinitas

Anda dapat mengizinkan prosedur yang tersimpan sebagai rutinitas. Rutinitas yang diizinkan memungkinkan Anda membagikan hasil kueri kepada pengguna atau grup tertentu tanpa memberi mereka akses ke tabel pokok yang menampilkan hasil tersebut. Misalnya, rutinitas yang diizinkan dapat menghitung agregasi atas data atau mencari nilai tabel dan menggunakan nilai tersebut dalam komputasi.

Rutinitas yang diizinkan dapat membuat, menghapus, dan memanipulasi tabel, serta panggil prosedur lain yang tersimpan di tabel pokok.

Untuk informasi selengkapnya, lihat Rutinitas yang diizinkan.

Memanggil prosedur yang disimpan

Untuk memanggil prosedur tersimpan setelah dibuat, gunakan pernyataan CALL. Misalnya, pernyataan berikut memanggil prosedur tersimpan create_customer:

CALL mydataset.create_customer();

Memanggil prosedur sistem

Untuk memanggil prosedur sistem bawaan, gunakan pernyataan CALL. Misalnya, pernyataan berikut memanggil prosedur sistem BQ.REFRESH_MATERIALIZED_VIEW:

CALL BQ.REFRESH_MATERIALIZED_VIEW;