Stored procedures Oracle PL/SQL / GCP BigQuery

Haciendo un poco de memoria, recordaremos lo que es un Stored Procedure.

Un Stored Procedure (procedimiento almacenado) es:

• Un subprograma que ejecuta una acción específica.
• No devuelve valor alguno (pero mediante un mecanismo podemos recuperar valores).
• Posee un nombre y puede o no tener parámetros y un bloque donde se efectuarán las acciones a realizar.
• Se puede llamar desde otros Stored Procedures y/o consultas SQL.

Si usamos Oracle, está será será su sintaxis:

CREATE {OR REPLACE} PROCEDURE nombre_proc( param1 [IN | OUT | IN OUT] tipo,... )
IS
  -- Declaración de variables locales
  BEGIN
  -- Instrucciones de ejecución
  [EXCEPTION]
  -- Instrucciones de excepción
END;

En BigQuery, sería de esta forma:

CREATE PROCEDURE dataset_name.procedure_name(OUT param1 TYPE, ...)
BEGIN
-- statements here
  [EXCEPTION]
  -- statements here
END

La forma de invocar un Stored Procedure en Oracle:

CALL miprocedimiento();

En BigQuery:

CALL mydataset.miprocedimiento();

Lenguaje procedimental

Tanto GCP BigQuery como Oracle PL/SQL poseen sus propias peculiaridades. Empecemos con los bloques.

En PL/SQL:

DECLARE
BEGIN
   dbms_output.put_line('Hola, mundo');
END;
/

En BigQuery:

BEGIN
   SELECT FORMAT('Hola, mundo');
END;

También podemos hacer esto:

DECLARE saludo STRING DEFAULT 'Hola, mundo';
SELECT FORMAT(saludo);

Para declarar variables en PL/SQL:

DECLARE 
   entero integer := 10;
   numero NUMBER(3):= 123;
   nombre VARCHAR2(45);
   flotante real;
   tiempo_salida TIMESTAMP(3):= '23-JUL-2024 07:22:53.175';
   verdadero BOOLEAN:= FALSE;
   fecha DATE;
   ...
BEGIN
 -- SENTENCIAS
END;
/
     
   

En BigQuery:

DECLARE nombre STRING DEFAULT 'MARIA';
DECLARE fecha DATE;
DECLARE verdadero BOOL DEFAULT TRUE;
DECLARE numero INT64;
DECLARE otro_num NUMERIC DEFAULT 12.0;

SET numero = 9;
SET fecha = '2024-09-12';

...    
   

Es muy parecida la forma en que se declaran las variables.

Al ser lenguajes procedimentales tendrán:

1. Sentencias Top-Down o de arriba hacia abajo. 
2. Sentencias iterativas o repetitivas. 
3. Sentencias selectivas o condicionales.

Sentencias Top-Down

Sentencias de arriba hacia abajo. No hay mucha ciencia e ello. Comencemos con PL/SQL:

DECLARE
-- Declaramos las variables
numero INTEGER;
verdadero BOOLEAN;
fecha DATE;
presente NUMBER;

BEGIN
-- Seteamos sus valores
numero := 33;
verdadero := FALSE;
fecha := TO_DATE('2024/07/09', 'yyyy/mm/dd');
presente := 2300.23;


--
END;
/

En BigQuery:

BEGIN
DECLARE numero INT64;
DECLARE verdadero BOOL;
DECLARE fecha DATE;
DECLARE presente NUMERIC;

-- Seteamos sus valores
SET numero = 33;
SET verdadero = FALSE;
SET fecha = FORMAT_DATE("%Y-%d-%m",'2024/07/09');
SET presente = 2300.23;


--
END;

Sentencias iterativas o repetitivas

Bucle FOR en PL/SQL

FOR contador IN 1..20
LOOP
   contador := contador + 1;
END LOOP;

Bucle WHILE en PL/SQL

WHILE contador <= 100
LOOP
   contador := contador + 1;
END LOOP;

Bucle FOR en BigQuery

DECLARE vector_a ARRAY<STRING>;
SET vector_a = ['_01', '_02', '_03'];

FOR loop_variable_name IN (SELECT * FROM UNNEST(vector_a))
DO
  -- Hacer algo aquí;
END FOR;

Bucle WHILE en BigQuery

declare cont int64 default 0;
set TAM = (select count(*) from `project.dataset.tabla1`);
set cont = 1;

while cont <= TAM do
   -- 
   SET cont = cont + 1;
end while;

Solo son algunas sentencias repetitivas. Ambos incluyen otras como: LOOP.

Sentencias selectivas o condicionales

IF en PL/SQL

DECLARE 
  numero INTEGER;
  titulo VARCHAR2(5):="ABCD";

BEGIN
  numero:=33;
IF numero = 33 THEN
   SELECT "Hola, "||numero FROM dual;
END IF;

IF LENGTH(titulo) = 5 THEN
   SELECT 'La cadena tiene una longitud de 5';
ELSE 
   SELECT 'La cadena tiene una longitud de '||LENGTH(titulo);
END IF;

END;
/

IF en BigQuery

DECLARE numero INT64;
DECLARE titulo DEFAULT 'ABCD';
SET numero = 33;

IF numero = 33 THEN
   SELECT FORMAT('Hola, ', CAST(numero AS STRING));
END IF;

IF LENGTH(titulo) = 5 THEN
   SELECT 'La cadena tiene una longitud de 5';
ELSE 
   SELECT 'La cadena tiene una longitud de '||LENGTH(titulo);
END IF;

Solo mostramos algunas sentencias. Si quieres ver sentencias condicionales en BigQuery, puedes echar un vistazo en este link:

https://codemonkeyjunior.blogspot.com/2024/11/gcp-funciones-condicionales-en-bigquery.html

Continuaremos con este tema en siguientes posts.

Enlaces:

https://elbauldelprogramador.com/plsql-procedimientos-y-funciones/
https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm