Módulo 00: Introducción a PL/SQL y Funciones Básicas de SQL

Objetivos del módulo

• Entender qué es PL/SQL y cuál es su relación con SQL
• Conocer las características principales de PL/SQL y sus ventajas
• Dominar las funciones numéricas más utilizadas en Oracle
• Manejar funciones de cadenas de caracteres para transformar textos
• Trabajar con funciones de fechas y realizar operaciones temporales
• Utilizar funciones de conversión de tipos de datos
• Conocer otras funciones útiles como NVL, GREATEST, LEAST y DECODE

---

1. ¿Qué es PL/SQL?

PL/SQL (Procedural Language/SQL) es el lenguaje de programación procedural de Oracle que extiende las capacidades de SQL. Mientras que SQL es un lenguaje declarativo (le dices qué quieres obtener), PL/SQL es un lenguaje procedural (le dices cómo hacerlo paso a paso).

PL/SQL permite:

• Escribir bloques de código con lógica de negocio
• Usar variables, constantes y tipos de datos complejos
• Implementar estructuras de control (IF, CASE, bucles)
• Crear procedimientos y funciones almacenados en la base de datos
• Gestionar errores (excepciones) de forma controlada
• Trabajar con cursores para procesar filas una a una

📘 Concepto: Piensa en SQL como la herramienta para consultar y manipular datos y en PL/SQL como el lenguaje para programar lógica de negocio directamente dentro de la base de datos Oracle. Juntos forman un equipo muy potente.

¿Por qué aprender PL/SQL?

Ventaja	Descripción
Rendimiento	El código se ejecuta directamente en el servidor de BD, minimizando el tráfico de red
Integración total con SQL	Puedes usar sentencias SQL directamente dentro de PL/SQL
Portabilidad dentro de Oracle	Funciona en cualquier base de datos Oracle (11g, 12c, 19c, 21c, 23ai)
Seguridad	Permite encapsular la lógica de negocio y controlar el acceso a los datos
Reutilización	Procedimientos y funciones almacenados se pueden reutilizar desde cualquier aplicación
Gestión de errores	Sistema robusto de excepciones para controlar situaciones inesperadas
Gran demanda laboral	Oracle sigue siendo uno de los SGBD más utilizados en el ámbito empresarial

---

2. Arquitectura de PL/SQL

PL/SQL se ejecuta dentro del motor PL/SQL del servidor Oracle. Cuando envías un bloque PL/SQL, el motor separa las sentencias SQL de las sentencias procedurales:

┌─────────────────────────────────────────────────────┐
│              TU BLOQUE PL/SQL                        │
│  (variables, IF, LOOP, SELECT, INSERT, etc.)         │
├─────────────────────────────────────────────────────┤
│              MOTOR PL/SQL                            │
│  ┌──────────────────┐  ┌──────────────────────┐     │
│  │ Sentencias        │  │ Sentencias SQL        │     │
│  │ procedurales      │  │ (SELECT, INSERT,      │     │
│  │ (IF, LOOP,        │  │  UPDATE, DELETE)       │     │
│  │  variables)        │  │                        │     │
│  │                    │  │  Se envían al motor    │     │
│  │ Se ejecutan en    │  │  SQL de Oracle          │     │
│  │ el motor PL/SQL   │  │                        │     │
│  └──────────────────┘  └──────────────────────┘     │
├─────────────────────────────────────────────────────┤
│              MOTOR SQL DE ORACLE                     │
│  Ejecuta las sentencias SQL y devuelve resultados    │
├─────────────────────────────────────────────────────┤
│              BASE DE DATOS ORACLE                    │
└─────────────────────────────────────────────────────┘

💡 Truco: Enviar un bloque PL/SQL con varias sentencias SQL es mucho más eficiente que enviar cada sentencia SQL por separado desde una aplicación cliente, porque se reduce drásticamente el número de viajes de red (round-trips) entre el cliente y el servidor.

---

3. Preparación del entorno

Antes de trabajar con funciones SQL, es recomendable configurar el formato de fechas para que sea más legible y consistente:

-- Formato ISO para fechas: YYYY-MM-DD
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

⚠️ Importante: Este comando solo afecta a tu sesión actual. Cada vez que te conectes de nuevo, necesitarás ejecutarlo otra vez si quieres mantener ese formato.

---

4. Funciones numéricas

Oracle proporciona un amplio conjunto de funciones para operar con valores numéricos.

4.1. ROUND y TRUNC

ROUND redondea un número al número de decimales especificado. TRUNC trunca (corta) sin redondear.

-- Redondear y truncar la diferencia de días
SELECT 
    (SYSDATE - fec_carnet) DIAS, 
    ROUND(SYSDATE - fec_carnet) DIAS_REDONDEO, 
    ROUND(SYSDATE - fec_carnet, 5) DIAS_REDONDEO_CINCO_CIFRAS, 
    TRUNC(SYSDATE - fec_carnet) DIAS_TRUNCADO,    
    TRUNC(SYSDATE - fec_carnet, 5) DIAS_TRUNCADO_CINCO_CIFRAS    
FROM conductor;

📘 Concepto: La diferencia clave es que ROUND(3.567, 1) devuelve 3.6 (redondea hacia arriba), mientras que TRUNC(3.567, 1) devuelve 3.5 (simplemente corta). Si no se indica el segundo parámetro, ambas trabajan con 0 decimales.

4.2. ABS (valor absoluto)

Devuelve el valor absoluto de un número, eliminando el signo negativo si lo tiene:

SELECT ABS(-56) AS ABSOLUTO_NEGATIVO, ABS(56) AS ABSOLUTO_POSITIVO FROM dual;
-- Resultado: 56, 56

4.3. MOD y división entera

MOD devuelve el resto de una división. Combinado con TRUNC, podemos obtener la parte entera:

SELECT 
    MOD(21, 2) AS RESTO,          -- Resultado: 1
    TRUNC(21 / 2) AS PARTE_ENTERA -- Resultado: 10
FROM dual;

4.4. SIGN (comprobar el signo)

Devuelve -1, 0 o 1 según el número sea negativo, cero o positivo:

SELECT 
    SIGN(-5) AS NEGATIVO,  -- Resultado: -1
    SIGN(5) AS POSITIVO,   -- Resultado: 1
    SIGN(0) AS CERO        -- Resultado: 0
FROM dual;

4.5. Funciones de agregación

Las funciones AVG, COUNT, MAX, MIN y SUM pueden usarse sin GROUP BY para obtener un único resultado sobre toda la tabla:

-- Estadísticas de infracciones
SELECT 
    MAX(valor_economico) VALOR_MAXIMO, 
    MIN(valor_economico) VALOR_MINIMO, 
    AVG(valor_economico) VALOR_MEDIO,
    SUM(valor_economico) VALOR_TOTAL,
    COUNT(*) NUM_INFRACCIONES
FROM infraccion;

También funcionan con fechas y textos, no solo con números:

-- Estadísticas de conductores
SELECT 
    MAX(fec_carnet) CARNET_MAS_RECIENTE, 
    MIN(fec_carnet) CARNET_MAS_ANTIGUO,
    MAX(fec_nacimiento) CONDUCTOR_MENOR,
    MIN(fec_nacimiento) CONDUCTOR_MAYOR,
    COUNT(*) NUM_CONDUCTORES
FROM conductor;

Para obtener datos de diferentes tablas en una sola consulta, usamos subconsultas con DUAL:

SELECT 
    (SELECT MAX(fec_carnet) FROM conductor) CARNET_MAS_RECIENTE,
    (SELECT MAX(valor_economico) FROM infraccion) VALOR_MAXIMO_INFRACCION,
    (SELECT COUNT(*) FROM multa) NUM_MULTAS
FROM DUAL;

4.6. COUNT con DISTINCT

DISTINCT dentro de COUNT permite contar valores únicos (sin repeticiones):

-- Colores diferentes de vehículos
SELECT COUNT(DISTINCT(color)) NUM_COLORES_DIFERENTES FROM vehiculo;

-- Marcas diferentes de vehículos
SELECT COUNT(DISTINCT(marca)) NUM_MARCAS_DIFERENTES FROM vehiculo;

-- Días diferentes en los que hay multas registradas
SELECT COUNT(DISTINCT(fecha)) NUM_DIAS_CON_MULTAS FROM multa;

---

5. Funciones de cadenas de caracteres

5.1. UPPER, LOWER, LENGTH y concatenación

SELECT 
    UPPER(lugar) MAYUSCULAS, 
    LOWER(lugar) MINUSCULAS, 
    LENGTH(lugar) NUM_CARACTERES_LUGAR,
    CONCAT(CONCAT(lugar, ' - Fecha: '), fecha) CONCATENADO_CON_CONCAT,
    lugar || ' - Fecha: ' || fecha "CONCATENADO_CON_||"
FROM multa;

💡 Truco: En Oracle, el operador || es la forma más cómoda y legible de concatenar cadenas. CONCAT solo admite dos parámetros, por lo que para concatenar más de dos valores habría que anidar varias llamadas.

5.2. LPAD y RPAD (rellenar texto)

Rellenan una cadena por la izquierda (LPAD) o por la derecha (RPAD) hasta alcanzar una longitud determinada:

SELECT 
    valor_economico, 
    LPAD(valor_economico, 20, '*') IZQUIERDA, 
    RPAD(valor_economico, 20, '*') DERECHA 
FROM infraccion;

5.3. LTRIM, RTRIM y TRIM (eliminar caracteres)

Eliminan caracteres al principio, al final o en ambos extremos de una cadena:

-- Eliminar espacios
SELECT 
    '  texto  ' AS ORIGINAL, 
    LTRIM(RTRIM('  texto  ')) SIN_ESPACIOS_1, 
    TRIM('  texto  ') SIN_ESPACIOS_2 
FROM DUAL;

-- Eliminar un carácter específico con TRIM
SELECT 
    '***texto***' AS ORIGINAL, 
    TRIM('*' FROM '*1*texto*1*') "SIN_*" 
FROM DUAL;

⚠️ Importante: TRIM solo puede eliminar un único carácter del principio y final. Si necesitas eliminar varios caracteres diferentes, utiliza LTRIM y RTRIM, que aceptan un conjunto de caracteres a eliminar.

5.4. INSTR (buscar posición de un texto)

Busca una subcadena dentro de otra y devuelve la posición (empezando en 1):

SELECT nombre, INSTR(nombre, ' ') POSICION_BLANCO FROM conductor;

5.5. SUBSTR (extraer un trozo de texto)

Extrae una porción de una cadena indicando la posición inicial y, opcionalmente, la longitud:

-- Extraer solo el nombre (antes del primer espacio)
SELECT 
    nombre NOMBRE_APELLIDO, 
    SUBSTR(nombre, 1, INSTR(nombre, ' ')) NOMBRE 
FROM conductor;

5.6. Combinando funciones de texto

Se pueden combinar varias funciones para transformaciones complejas:

-- Formato APELLIDO, NOMBRE
SELECT 
    nombre NOMBRE_APELLIDO, 
    LTRIM(SUBSTR(nombre, INSTR(nombre, ' '))) || ',' || 
    RTRIM(SUBSTR(nombre, 1, INSTR(nombre, ' '))) APELLIDO_NOMBRE
FROM conductor;

5.7. REPLACE (reemplazar texto)

Sustituye todas las ocurrencias de una subcadena por otra:

SELECT nombre, REPLACE(nombre, 'Ó', 'O') NOMBRE_SIN_ACENTO FROM conductor;

-- Se pueden encadenar varios REPLACE
SELECT 
    telefono TELEFONO, 
    REPLACE(REPLACE(telefono, ' ', ''), '+34', '') SOLO_TELEFONO 
FROM conductor;

5.8. INITCAP (primera letra en mayúscula)

Convierte la primera letra de cada palabra a mayúscula y el resto a minúsculas:

SELECT nombre, INITCAP(nombre) NOMBRE_FORMATEADO FROM conductor;

---

6. Funciones de fecha

6.1. SYSDATE y operaciones básicas

SYSDATE devuelve la fecha y hora actual del servidor. Se pueden sumar y restar días directamente:

SELECT SYSDATE HOY, SYSDATE + 7 "HOY+7 DIAS" FROM DUAL;

6.2. ADD_MONTHS (añadir meses)

Añade un número de meses a una fecha. Para sumar años, multiplicamos por 12:

SELECT 
    SYSDATE HOY, 
    ADD_MONTHS(SYSDATE, 3) "HOY+3 MESES", 
    ADD_MONTHS(SYSDATE, 12) "HOY+1 AÑO" 
FROM DUAL;

6.3. MONTHS_BETWEEN (diferencia en meses)

Calcula los meses transcurridos entre dos fechas:

-- Edad al sacarse el carnet de conducir
SELECT 
    fec_nacimiento, 
    fec_carnet, 
    TRUNC(MONTHS_BETWEEN(fec_carnet, fec_nacimiento) / 12) EDAD_CARNET 
FROM conductor;

6.4. NEXT_DAY (siguiente día de la semana)

Devuelve la fecha del siguiente día de la semana especificado (1=Lunes … 7=Domingo en muchas configuraciones):

SELECT 
    SYSDATE HOY, 
    NEXT_DAY(SYSDATE, 1) PRIMER_DIA_SIGUIENTE_SEMANA, 
    NEXT_DAY(SYSDATE, 3) TERCER_DIA_SIGUIENTE_SEMANA 
FROM DUAL;

⚠️ Importante: El número del día de la semana en NEXT_DAY depende de la configuración NLS_TERRITORY de tu sesión. Verifica con tu DBA o usa el nombre del día ('LUNES', 'MONDAY'…) para mayor claridad.

6.5. TRUNC con fechas

TRUNC aplicada a fechas permite obtener el primer día del mes o del año:

SELECT 
    SYSDATE HOY, 
    TRUNC(SYSDATE, 'YY') PRIMER_DIA_AÑO, 
    TRUNC(SYSDATE, 'MM') PRIMER_DIA_MES 
FROM DUAL;

6.6. LAST_DAY (último día del mes)

Devuelve el último día del mes de una fecha dada:

SELECT SYSDATE HOY, LAST_DAY(SYSDATE) ULTIMO_DIA FROM DUAL;

6.7. Ejemplo avanzado: días del mes actual

Combinando LAST_DAY y TRUNC podemos calcular cuántos días tiene el mes en curso:

SELECT 
    SYSDATE HOY, 
    LAST_DAY(SYSDATE) ULTIMO_DIA, 
    TRUNC(SYSDATE, 'MM') PRIMER_DIA, 
    (TRUNC(LAST_DAY(SYSDATE)) - TRUNC(SYSDATE, 'MM') + 1) NUM_DIAS
FROM DUAL;

---

7. Funciones de conversión

7.1. Conversión implícita

Oracle puede convertir tipos automáticamente en muchos casos:

-- Oracle convierte automáticamente números a texto para concatenar
SELECT 
    id || ' - ' || codigo || ' - ' || descripcion || ' - ' || 
    puntos || ' - ' || valor_economico || ' - ' || vigente INFRACCION
FROM infraccion;

7.2. TO_CHAR (convertir a texto)

Convierte números o fechas a texto con un formato específico:

-- Formato numérico: D=coma decimal, G=punto de miles (formato español)
SELECT 
    descripcion INFRACCION, 
    valor_economico IMPORTE,
    TO_CHAR(valor_economico * 1.135, '9G999D00') "IMPORTE+13.5%" 
FROM infraccion;

-- Formato fecha con hora
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FECHA_HORA FROM DUAL;

-- Otro formato de fecha
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FECHA_HORA FROM DUAL;

📘 Concepto: Los caracteres de formato numérico más comunes son: 9 (dígito opcional), 0 (dígito obligatorio), D (separador decimal según NLS), G (separador de miles según NLS), L (símbolo de moneda según NLS).

7.3. TO_DATE (convertir texto a fecha)

Convierte una cadena de texto a tipo DATE aplicando un formato:

SELECT TO_DATE('01/01/2020', 'DD-MM-YYYY') FECHA FROM DUAL;
SELECT TO_DATE('1 2 2020', 'MM DD YYYY') FECHA FROM DUAL;
SELECT TO_DATE('2020 5 12', 'YYYY MM DD') FECHA FROM DUAL;

⚠️ Importante: Siempre es recomendable usar TO_DATE con un formato explícito en lugar de confiar en la conversión implícita de Oracle, ya que el formato por defecto puede variar según la configuración del cliente o del servidor.

7.4. TO_NUMBER (convertir texto a número)

Convierte una cadena de texto a un valor numérico aplicando un formato:

SELECT TO_NUMBER('125.263,52', '9G999G999D99') NUMERO FROM DUAL;

---

8. Otras funciones útiles

8.1. NVL (sustituir valores nulos)

Devuelve un valor alternativo cuando el campo es NULL:

SELECT matricula, NVL(color, '(Sin indicar)') COLOR FROM vehiculo;

💡 Truco: NVL2(expr, valor_si_no_nulo, valor_si_nulo) es una variante que permite especificar un valor diferente tanto si el campo es nulo como si no lo es.

8.2. GREATEST y LEAST (máximo y mínimo entre valores)

A diferencia de MAX/MIN (que trabajan con filas), GREATEST y LEAST comparan valores dentro de la misma fila:

SELECT GREATEST(5, 9, 7) MAXIMO FROM DUAL;  -- Resultado: 9
SELECT LEAST(5, 9, 7) MINIMO FROM DUAL;      -- Resultado: 5

-- Comparar fechas de un mismo registro
SELECT 
    fec_carnet, 
    fec_nacimiento, 
    GREATEST(fec_carnet, fec_nacimiento) FECHA_POSTERIOR 
FROM conductor;

8.3. DECODE (condiciones en consulta)

DECODE es una función de Oracle que permite evaluar condiciones de igualdad de forma compacta (similar a un CASE simple):

SELECT 
    descripcion, 
    DECODE(vigente, 0, 'NO ESTÁ VIGENTE', 1, 'VIGENTE', '-') ESTADO 
FROM infraccion;

-- Combinando con SIGN para rangos
SELECT 
    descripcion, 
    valor_economico, 
    DECODE(SIGN(valor_economico - 150), -1, '<150', 0, '=150', 1, '>150') VALORACION
FROM infraccion;

📘 Concepto: Aunque DECODE sigue funcionando, Oracle recomienda usar CASE en las nuevas aplicaciones porque es estándar SQL, más legible y más potente (permite condiciones con operadores complejos, no solo igualdad).

---

9. Ejercicios prácticos

Ejercicio 1: Funciones numéricas

Usando la tabla DUAL, calcula el redondeo y el truncamiento de 123.4567 con 2 decimales, y obtén el resto de dividir 17 entre 5:

SELECT 
    ROUND(123.4567, 2) REDONDEO,
    TRUNC(123.4567, 2) TRUNCADO,
    MOD(17, 5) RESTO
FROM DUAL;

Ejercicio 2: Funciones de texto

Dada la cadena ' hola mundo ', elimina los espacios sobrantes, conviértela a mayúsculas y obtén la longitud resultante:

SELECT 
    TRIM('  hola mundo  ') AS LIMPIO,
    UPPER(TRIM('  hola mundo  ')) AS MAYUSCULAS,
    LENGTH(TRIM('  hola mundo  ')) AS LONGITUD
FROM DUAL;

Ejercicio 3: Funciones de fecha

Calcula cuántos días faltan para fin de año y cuántos meses han pasado desde el 1 de enero del año actual:

SELECT 
    TRUNC(TO_DATE('31/12/' || TO_CHAR(SYSDATE, 'YYYY'), 'DD/MM/YYYY') - SYSDATE) DIAS_FIN_AÑO,
    TRUNC(MONTHS_BETWEEN(SYSDATE, TRUNC(SYSDATE, 'YY'))) MESES_TRANSCURRIDOS
FROM DUAL;

Ejercicio 4: Funciones de conversión

Muestra la fecha actual con el formato Día DD de Mes de YYYY a las HH:MI:SS:

SELECT 
    TO_CHAR(SYSDATE, 'Day DD "de" Month "de" YYYY "a las" HH24:MI:SS') FECHA_FORMATEADA
FROM DUAL;

Ejercicio 5: Combinación de funciones

A partir de un importe de 1250.50, muéstralo formateado como moneda con el símbolo del euro y rellena con asteriscos hasta 20 caracteres (útil en cheques):

SELECT 
    LPAD(TO_CHAR(1250.50, '9G999D00') || ' €', 20, '*') IMPORTE_CHEQUE
FROM DUAL;

---

Resumen

Concepto	Detalle
PL/SQL	Extensión procedural de SQL creada por Oracle
Motor PL/SQL	Separa sentencias SQL de sentencias procedurales para su ejecución
ROUND / TRUNC	Redondear y truncar valores numéricos o fechas
ABS / MOD / SIGN	Valor absoluto, resto de división y signo de un número
Funciones de agregación	MAX, MIN, AVG, SUM, COUNT — operan sobre conjuntos de filas
UPPER / LOWER / INITCAP	Convertir texto a mayúsculas, minúsculas o capitalizado
CONCAT / ||	Concatenar cadenas de texto
LPAD / RPAD	Rellenar texto por la izquierda o derecha
TRIM / LTRIM / RTRIM	Eliminar caracteres de los extremos de una cadena
INSTR / SUBSTR	Buscar posición y extraer subcadenas
REPLACE	Reemplazar texto dentro de una cadena
SYSDATE	Fecha y hora actual del servidor
ADD_MONTHS / MONTHS_BETWEEN	Sumar meses y calcular diferencia en meses
NEXT_DAY / LAST_DAY	Siguiente día de la semana y último día del mes
TO_CHAR / TO_DATE / TO_NUMBER	Funciones de conversión entre tipos de datos
NVL	Sustituir valores nulos por un valor alternativo
GREATEST / LEAST	Obtener el mayor o menor valor entre varios argumentos
DECODE	Evaluación condicional compacta (preferir CASE en código nuevo)