Trabaja con procedimientos almacenados de SQL

Un procedimiento almacenado es un conjunto de instrucciones que se pueden llamar desde otras consultas o desde otros procedimientos almacenados. Un procedimiento puede tomar argumentos de entrada y mostrar valores como resultados. Debes nombrar y almacenar un procedimiento en un conjunto de datos de BigQuery. Un procedimiento almacenado puede acceder a los datos o modificarlos en varios conjuntos de datos de varios usuarios. También puede contener una consulta de varias instrucciones.

Algunos procedimientos almacenados están compilados en BigQuery y no necesitan crearse. Estos se llaman procedimientos del sistema y puedes obtener más información sobre ellos en la referencia de procedimientos del sistema.

Los procedimientos almacenados admiten declaraciones de lenguaje de procedimiento, que te permiten realizar variables como definir variables e implementar el flujo de control. Puedes obtener más información sobre las declaraciones de lenguaje de procedimiento en la referencia del lenguaje de procedimiento.

Crea un procedimiento almacenado

Para crear un conjunto de datos, usa la declaración CREATE PROCEDURE.

En el siguiente ejemplo conceptual, procedure_name representa el procedimiento y el cuerpo del procedimiento aparece entre las instrucciones BEGIN y END:

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

En el siguiente ejemplo, se muestra un procedimiento que contiene una consulta de varias instrucciones. La consulta de varias instrucciones establece una variable, ejecuta una instrucción INSERT y muestra el resultado como una string de texto con formato.

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

En el ejemplo anterior, el nombre del procedimiento es mydataset.create_customer y el cuerpo del procedimiento aparece entre las declaraciones BEGIN y END.

Para llamar al procedimiento, usa la instrucción CALL:

CALL mydataset.create_customer();

Pasa un valor con un parámetro de entrada

Un procedimiento puede tener parámetros de entrada. Un parámetro de entrada permite la entrada para un procedimiento, pero no la salida.

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

Pasa un valor con un parámetro de salida

Un procedimiento puede tener parámetros de salida. Un parámetro de salida muestra un valor del procedimiento, pero no permite la entrada del procedimiento. Para crear un parámetro de salida, usa la palabra clave OUT antes del nombre del parámetro.

Por ejemplo, esta versión del procedimiento muestra el ID de cliente nuevo a través del parámetro 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

Si deseas llamar a este procedimiento, debes usar una variable para recibir el valor de salida:

--- 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;

Pasar un valor con un parámetro de entrada/salida

Un procedimiento también puede tener parámetros de entrada/salida. Un parámetro de entrada/salida muestra un valor del procedimiento y también acepta la entrada para el procedimiento. Para crear un parámetro de entrada o salida, usa la palabra clave INOUT antes del nombre del parámetro. Para obtener más información, consulta Modo de argumentos.

Autoriza rutinas

Puedes autorizar procedimientos almacenados como routines. Las rutinas autorizadas te permiten compartir resultados de consultas con usuarios o grupos específicos sin darles acceso a las tablas subyacentes que generaron los resultados. Por ejemplo, una rutina autorizada puede calcular una agregación en los datos o buscar un valor de tabla y usar ese valor en un cálculo.

Las rutinas autorizadas pueden create, descartar y modificar tablas, así como invocar otros procedimientos almacenados en la tabla subyacente.

Para obtener más información, consulta Rutinas autorizadas.

Llama a un procedimiento almacenado

Para llamar a un procedimiento almacenado después de crearlo, usa la instrucción CALL. Por ejemplo, la siguiente instrucción llama al procedimiento almacenado create_customer:

CALL mydataset.create_customer();

Nota: Llamar a un procedimiento almacenado en lugar de incluir la instrucción de SQL del procedimiento directamente en tu consulta genera una pequeña sobrecarga de rendimiento.

Llama a un procedimiento del sistema

Para llamar a un procedimiento integrado del sistema, usa la instrucción CALL. Por ejemplo, la siguiente instrucción llama al procedimiento del sistema BQ.REFRESH_MATERIALIZED_VIEW:

CALL BQ.REFRESH_MATERIALIZED_VIEW;