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

GCP: funciones condicionales en BigQuery

 

El uso de funciones y sentencias condicionales es recurrente en el desarrollo de programas. Por ejemplo, para que el flujo se desvíe hacia cierto camino cuando así sea requerido. El lenguaje SQL que usa BigQuery no escapa de esa singularidad. 

Existen 6 sentencias o expresiones condicionales en BigQuery y éstas son:

1. CASE expr.
2. CASE.
3. COALESCE.
4. IF.
5. IFNULL.
6. NULLIF.

CASE expr

Compara la expresión dada con cada cláusula ``WHEN`` sucesiva y produce el primer resultado donde los valores son iguales.

Ejemplo:

DECLARE evaluacion STRING
SET evaluacion = 'sobresaliente';

SELECT CASE evaluacion  
       WHEN 'bueno' THEN 8
       WHEN 'excelente' THEN 9
       WHEN 'sobresaliente' THEN 10
       ELSE -1  as eval_result;

CASE

Evalúa la condición de cada cláusula ``WHEN`` sucesiva y produce el primer resultado donde la condición se evalúa como VERDADERO.

Ejemplo:

SELECT
  matematicas,
  historia,
  CASE
    WHEN matematicas > 9 THEN 'Sobresaliente'
    WHEN historia = 7 THEN 'Aceptable'
    ELSE 'Debes mejorar'
    END
    AS result_calf
FROM calificaciones

COALESCE

Produce el valor de la primera expresión no NULL, si la hay, de lo contrario es NULL.

Ejemplo:

SELECT COALESCE(NULL, 'Es un nulo', 'Es un valor no nulo') as resultado;

IF

Si una expresión se evalúa como VERDADERO, produce un resultado especificado; de lo contrario, produce la evaluación para un resultado else.

Ejemplo:

DECLARE x INT64
DECLARE y INT64;

SET x = 10;
SET y = 20;

SELECT
  x AS A,
  y AS B,
  IF(x < y, 'true', 'false') AS resultado;

IFNULL

Si una expresión se evalúa como NULL, produce un resultado especificado; de lo contrario, produce la expresión.

Ejemplo:

DECLARE valor INT64 DEFAULT 10;

SELECT IFNULL(NULL, 0) as result; -- NULL

SELECT IFNULL(valor, 0) as result; -- 10

NULLIF

Produce NULL si la primera expresión que coincide con otra se evalúa como VERDADERO; de lo contrario, devuelve la primera expresión.

Ejemplo:

DECLARE x INT64 DEFAULT 10;

SELECT NULLIF(0, 0) as resultado; -- NULL

SELECT NULLIF(x, 0) as resultado; -- 10

Más ejemplos en próximas entregas.

Enlaces:

https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions

Un vistazo a la programación funcional

La Programación Funcional (Functional Programming)es un paradigma de programación declarativa (decir qué queremos, pero no cómo hacerlo) basado en el uso de funciones matemáticas donde:

• Estaremos trabajando principalmente con funciones (el elemento principal para construir programas), evitaremos los datos mutables, así como el hecho de compartir estados entre funciones.
• Y donde el valor generado por una función depende exclusivamente de los argumentos alimentados a la función.
• Las funciones serán tratadas como ciudadanos de primera clase y podrán ser asignadas a variables además podrán ser utilizadas como entrada y salida de otras funciones.
• Además, evitamos a toda costa los efectos secundarios que alteren el estado del programa u otros programas.
• Ejemplos de lenguajes que soportan este paradigma son Haskell, Elixir, Erlang , Scala, y Clojure.

Características principales:

1. Funciones puras: Las funciones siempre producen el mismo resultado para los mismos argumentos y no tienen efectos colaterales (no modifican variables globales ni realizan I/O directamente). 
2.  Inmutabilidad: Los datos no se pueden modificar una vez creados. En lugar de cambiar valores, se crean nuevos valores. 
3.  Funciones de orden superior: Las funciones pueden aceptar otras funciones como argumentos o devolver funciones. 
4.  Composición de funciones: Las funciones se pueden combinar para crear otras más complejas, facilitando la reutilización de código. 
5.  Evaluación perezosa: Las expresiones no se evalúan hasta que su resultado es necesario, optimizando el rendimiento en muchos casos.

Ventajas:

1. Código más predecible y fácil de razonar gracias a la ausencia de efectos colaterales. 
2. Facilidad para la concurrencia y el paralelismo, ya que no hay variables mutables que puedan causar conflictos.

Algunos conceptos clave de la Programación Funcional

1. Funciones puras: Funciones que siempre producen el mismo resultado para los mismos argumentos. 
2. Inmutabilidad: Los valores no pueden ser modificados una vez creados.
3. Currificación: Funciones que pueden ser llamadas con menos argumentos de los que requieren originalmente.
4. Composición de funciones: Encadenar funciones mediante el operador (.).
5. Pattern matching: Hacer coincidir patrones en listas, tuplas, y otros datos estructurados.
6. Guards y condicionales: Usar guards para control de flujo condicional.
7. Recursión: La forma principal de control de iteraciones.

Ejemplo 1. Crear una función pura que nos sume dos números enteros. Este ejemplo lo haremos en Haskell.

suma.hs

suma :: Int -> Int -> Int
suma x y = x + y

main :: IO()
main = print (suma 8 3)

Como se puede ver, iniciamos con la delaración de tipos (Int). Definimos la función (suma) y en la d¿función principal (main) asignamos dos valores por default (8 y 3), lo cual nos dará como resultado 11.

Si solo queremos ejecutar el programa, abrimos una terminal y tecleamos lo siguiente:

runghc suma.hs

Si queremos compilar y crear un ejecutable, entonces:

ghc -o suma.exe suma.hs

Como la definición de una Función Pura indica, el resultado será el mismo cuando los argumentos son los mismos. Si invertimos el lugar del los valores (8, 3 por 3,8) el resultado seguirá siendo el mismo.

Aprender programación funcional se ha vuelto una "moda" que ya no solo implica el ámbito académico. Puesto que ya son muchos proyectos como la I.A. o Aprendizaje Automático que cada vez más requieren desarrolladores que dominen los principios de la matemática y, por ende, la programación funcional.

Enlaces:

https://codemonkeyjunior.blogspot.com/search?q=erlang
https://codemonkeyjunior.blogspot.com/search?q=elixir
https://emanuelpeg.blogspot.com/search?q=haskell

Terraform o Infraestructura cómo código

 

Según la Wikipedia, la Infraestructura como Código (Infrastructure as Code, en Inglés) es: el proceso de gestión y aprovisionamiento de centros de datos informáticos a través de archivos de definición legibles por máquina, en lugar de configuración de hardware físico o herramientas de configuración interactiva.

Básicamente:

• Es la capacidad de aprovisionar y respaldar su infraestructura de computación a través de código en lugar de procesos y configuraciones manuales.  
• Permite definir el estado deseado de su infraestructura sin incluir todos los pasos para llegar a ese estado.
• Automatiza la administración de la infraestructura para que los desarrolladores puedan centrarse en crear y mejorar las aplicaciones en lugar de administrar los entornos.

Enfoques diferentes de la infraestructura como código

Declarativa

La IaC declarativa permite al desarrollador describir los recursos y la configuración que componen el estado final de un sistema deseado.

A continuación, la solución de IaC crea este sistema a partir del código de infraestructura. Esto hace que la IaC declarativa sea fácil de usar, siempre que el desarrollador sepa qué componentes y configuraciones necesita para ejecutar su aplicación.

Imperativa

La IaC imperativa permite al desarrollador describir todos los pasos para configurar los recursos y llegar al sistema y al estado de ejecución deseados.

Si bien no es tan sencillo escribir la IaC imperativa como la IaC declarativa, el enfoque imperativo se hace necesario en las implementaciones de infraestructuras complejas. Esto es especialmente cierto cuando el orden de los eventos es crítico.

¿Qué es Terraform?

Terraform es una herramienta de Infraestructura como Código (IaC) que permite definir, provisionar y gestionar infraestructura en la nube o en entornos locales mediante archivos de configuración.

Estos archivos, escritos en un lenguaje declarativo llamado HashiCorp Configuration Language (HCL), describen los recursos y servicios que se quieren desplegar, como servidores, redes, bases de datos, entre otros.

Terraform es capaz de interactuar con múltiples proveedores de servicios en la nube (como AWS, Azure, Google Cloud) y otras plataformas, facilitando la creación y modificación de infraestructuras complejas de forma eficiente.

Se relaciona con IaC porque permite automatizar y versionar la infraestructura de la misma manera que se gestiona el código de software, asegurando que las configuraciones sean reproducibles, auditables y escalables.

Instalando Terraform

En Linux:

Abrimos una terminal y escribimos estos comandos:

# Actualizar paquetes
sudo apt-get update && sudo apt-get install -y gnupg software-properties-common

# Añadir repositorio oficial de HashiCorp:

wget -O- https://apt.releases.hashicorp.com/gpg | gpg --dearmor | sudo tee /usr/share/keyrings/hashicorp-archive-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/hashicorp-archive-keyring.gpg] https://apt.releases.hashicorp.com $(lsb_release -cs) main" | sudo tee /etc/apt/sources.list.d/hashicorp.list

# Instalar Terraform
sudo apt-get update && sudo apt-get install terraform

# Verificar instalación
terraform -v

Tendremos instalado Terraform en Linux.

En Mac OS:

Abrimos una terminal y escribimos estos comandos:

# Instala Terraform con Homebrew:
brew tap hashicorp/tap

brew install hashicorp/tap/terraform

terraform -v

Tendremos instalado Terraform en Mac OS.

En Windows:

Para esto es necesario contar con Chocolatey instalado en nuestras máquinas:

choco install terraform

Verificamos la instalación:

terraform -v

Tendremos Terraform instalado.

Ahora imaginemos que queremos crear una instancia de EC2 en AWS. Usaremos Terraform para lograrlo. La extensión del programa será *.tf

main.tf

# Especifica el proveedor de nube, en este caso AWS
provider "aws" {
  region = "us-east-1"  # Región donde se va a desplegar la infraestructura
}

# Definir una clave SSH para conectarse a la instancia
resource "aws_key_pair" "example" {
  key_name   = "example-key"
  public_key = "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC3..."  # Llave pública SSH
}

# Crear una instancia de EC2 en AWS
resource "aws_instance" "example" {
  ami           = "ami-0c55b159cbfafe1f0"  # Identificador de la AMI (Amazon Machine Image)
  instance_type = "t2.micro"  # Tipo de la instancia

  # Configurar la clave SSH para la instancia
  key_name = aws_key_pair.example.key_name

  tags = {
    Name = "Terraform Example Instance"  # Etiqueta para identificar la instancia
  }
}

# Salida para ver la IP pública de la instancia creada
output "instance_public_ip" {
  value = aws_instance.example.public_ip
}

1. Inicializamos el proyecto:

$ terraform init

2. Previsualizar los cambios (plan):

$ terraform plan

3. Aplicar cambios:

$ terraform apply

4. Destruir la infraestructura (cuando ya no sea necesaria):

$ terraform destroy

Hemos dado una breve explicación de lo que es IaC y cómo se relaciona con Terraform.

Enlaces:

https://chocolatey.org/
https://www.terraform.io/

PL/SQL: creando Stored Procedures (1era parte)

PL/SQL es un lenguaje de programación procedimental diseñado para operar en entornos Oracle.

Con este lenguaje podemos hacer:

• Automatizar tareas complejas dentro de la base de datos mediante procedimientos y funciones.
• Implementar reglas de negocio complejas con triggers y validaciones. 
• Gestionar transacciones y manejar errores de manera robusta. 
• Manipular conjuntos de datos utilizando cursores. 
• Integrar lógica de negocios en aplicaciones que interactúan con bases de datos Oracle.

En esta ocasión veremos cómo crear Stored Procedures o procedimientos almacenados. Los cuales son procedimiento que pueden o no devolver algún valor (contrario a las funciones que siempre devuelven algún valor). Estos podrán, una vez que sean creado, ser invocados en tus scripts.

La sintaxis para crear un Stored Procedure es la siguiente:

-- Sintaxis para crear un Stored Procedure 
CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]

IS
    [declaration_section]

BEGIN
    executable_section

[EXCEPTION
    exception_section]

END [procedure_name];

Para su invocación debemos anteponer la palabra ``CALL`` seguido del nombre.

CALL miprocedimiento();
CALL miprocedimiento(arg1, arg2, argN);

En un anterior post vimos cómo crear bloques PL/SQL. Un Stored procedure contiene bloques que harán determinada tarea que nosotros definamos.

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hola desde PL/SQL');
END;

Ejemplo 1. Crear un Stored procedure para mandar un mensaje a pantalla.

hola.sql

CREATE OR REPLACE PROCEDURE hola 
IS 
  nombre VARCHAR2(6):='Thomas';
BEGIN 
  IF nombre IS NOT NULL THEN 
    dbms_output.put_line('Hola, '||nombre);
  END IF;
END;
/

Lo invocamos de está forma:

SET VERIFY OFF;
SET SERVEROUTPUT ON;

-- Invocar Stored procedure
CALL hola;

Ejemplo 2. Crear un Stored Procedure para ver la versión de Oracle instalada.

mi_proc.sql

CREATE OR REPLACE PROCEDURE mi_proc
    IS
    version_inst VARCHAR(200);
    consulta VARCHAR(200):='SELECT BANNER FROM v$version';    
BEGIN
    dbms_output.put_line('Version instalada: ');
    -- Ejecutamos consulta
    EXECUTE IMMEDIATE consulta INTO version_inst;
    dbms_output.put_line(version_inst);
END;
/

Lo invocamos:

SET VERIFY OFF;
SET SERVEROUTPUT ON;

CALL mi_proc;

Enlaces:

https://codemonkeyjunior.blogspot.com/2023/07/plsql-bloques.html
https://www.plsqltutorial.com/

GCP BigQuery: funciones de cadena y tiempo

En otras entregas hemos visto la creación de funciones y el uso de funciones propias de BigQuery. Ahora nos enfocaremos en las funciones de manejo de tiempo.

Funciones de tiempo

• CURRENT_TIME
• EXTRACT
• FORMAT_TIME
• PARSE_TIME
• TIME
• TIME_ADD
• TIME_DIFF
• TIME_SUB
• TIME_TRUNC

Algunos ejemplos de su uso pueden ser:

SELECT CURRENT_TIME() as now;

Esto nos mostrará el tiempo actual.

BEGIN 

DECLARE T1 STRING;

SET T1 = "16:33:00";

SELECT EXTRACT(HOUR FROM TIME T1) as hour;

END;

Esto extraerá la hora de la variable ``STRING`` en formato de hora (hora:minutos:segundos).

BEGIN 

DECLARE T1 STRING;

SET T1 = "09:12:00";

SELECT PARSE_TIME("%I:%M:%S", T1);

END;

Esto convertirá la cadena ``STRING`` a una hora válida.

Como se puede ver, se ha creado una variable y se da el valor de una hora con sus minutos y segundos.

BEGIN 

DECLARE T1 STRING;
DECLARE T2 STRING;

SET T1 = "09:12:00";
SET T2 = "04:23:22";

SELECT PARSE_TIME("%I:%M:%S", T1);

SELECT PARSE_TIME("%T", T2);

END;

La función ``PARSE_TIME`` es muy útil a la hora de convertir cadenas a variables tipo ``TIME``. Si la cadena cumple el formato, la conversión será correcta.

Ahora veremos la función ``PARSE_TIME` que nos sirve para representar el tiempo (horas, minutos, segundos):

SELECT TIME(15, 30, 00) as time_hms;

Como se puede ver, las funciones de tiempo en BigQuery nos pueden ayudar para obtener los valores de fecha y hora con un formato adecuado.

Enlaces:

https://codemonkeyjunior.blogspot.com/2024/08/gcp-calculando-el-imc-con-bigquery.html

¿De qué trata esProc SPL?

esProc SPL es un lenguaje de procesamiento y cálculo de datos.

• Se ejecuta como una base de datos de análisis o middleware.
• Útil para cálculo y procesamiento de datos estructurados y semiestructurados. Ofrece un rendimiento superior y es ideal para tareas con uso intensivo de datos en el entorno impulsado por datos actual. 
• Útil para trabajo por lotes sin conexión, consulta en línea.
• Ni sistema SQL ni tecnología NoSQL. Trabaja directamente sobre los datos.
• Con sintaxis SPL de creación propia, más concisa y eficiente SPL: Lenguaje de proceso estructurado.

En conclusión, esProc SPL está diseñado para el procesamiento avanzado de datos y se destaca en el manejo eficiente de conjuntos de datos masivos.

Los sitios oficiales del lenguaje son:

• https://www.scudata.com/
• https://github.com/SPLWare/esProc

Si quieres aprenderlo, puedes revisar la documentación oficial:

https://www.scudata.com/html/SPL-programming-book.html

Al iniciar la aplicación, se abre un grid similar a las hojas de Excel. Es aquí donde se escribe el código:

Escribimos el código o instrucciones de manera similar a lo que se hace en Excel:

=2*1.5
=1*(2+3)
=65536*65536*65536*65536
=1*(2-3*(4+5))

Se pueden operar con tipos de datos Integer, Float, Long, etc. Además de también realizar operaciones booleanas (que dan resultados TRUE o FALSE). Hacer uso de sentencias de control para los bucles o condiciones a cumplir en el programa (for, ).

=if(x>0,x,-x)
=if(x>y,x,y)

!true = false
!false = true

=A1*A1*A1
>A1=B1

for 20

=[3,9,0,2,2.3,9.8]

if B1<=100

Podemos concluir en que esProc SPL es un lenguaje de script para procesamiento de datos, con funciones de biblioteca ricas y bien diseñadas y una sintaxis poderosa, que puede ejecutarse en un programa Java a través de la interfaz JDBC y computarse de forma independiente.

Continuaremos en próximas entregas.

Enlaces:

https://dev.to/bigsondev/10-programming-languages-worth-learning-in-2024-afa
https://github.com/SPLWare/esProc
https://www.scudata.com/
https://www.reddit.com/r/esProc_SPL/
https://www.scudata.com/html/SPL-programming-book.html