Trabajar con procedimientos almacenados de SQL

Un procedimiento almacenado es un conjunto de instrucciones que se pueden llamar desde otras consultas u otros procedimientos almacenados. Un procedimiento puede recibir argumentos de entrada y devolver valores como salida. Asigna un nombre a un procedimiento y lo almacenas en un conjunto de datos de BigQuery. Un procedimiento almacenado puede acceder a datos o modificarlos en varios conjuntos de datos por varios usuarios. También puede contener una consulta con varias instrucciones.

Algunos procedimientos almacenados están integrados en BigQuery y no es necesario crearlos. Se denominan procedimientos del sistema y puedes consultar más información sobre ellos en la referencia de procedimientos del sistema.

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

Crear un procedimiento almacenado

Para crear un procedimiento, usa la instrucció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 con varias instrucciones. La consulta de varias instrucciones define una variable, ejecuta una instrucción INSERT y muestra el resultado como una cadena 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 instrucciones BEGIN y END.

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

CALL mydataset.create_customer();

Enviar un valor con un parámetro de entrada

Un procedimiento puede tener parámetros de entrada. Un parámetro de entrada permite introducir datos en un procedimiento, pero no permite obtener datos.

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

Enviar un valor con un parámetro de salida

Un procedimiento puede tener parámetros de salida. Un parámetro de salida devuelve 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 devuelve el nuevo ID de cliente 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

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

Enviar y recibir un valor con un parámetro de entrada/salida

Un procedimiento también puede tener parámetros de entrada o salida. Un parámetro de entrada/salida devuelve un valor del procedimiento y también acepta entradas 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 argumento.

Autorizar rutinas

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

Las rutinas autorizadas pueden crear, eliminar y manipular tablas, así como invocar otros procedimientos almacenados en la tabla subyacente.

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

Llamar 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();

Llamar a un procedimiento del sistema

Para llamar a un procedimiento del sistema integrado, 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;