Algunos ejemplos de PL/SQL

[Saberuneko]

Me ha dado por compartir algunos ejemplos de nivel simple que pueden resultar útiles para ser tomados como referencia en algunos casos:

Para estos ejemplos, en ocasiones utilizaremos las siguientes tablas:

Código [Seleccionar] Expandir

CREATE TABLE EMPLE(
EMP_NO NUMERIC(4) NOT NULL,
NOMBRE VARCHAR2(35),
FECHA_ALTA DATE NOT NULL,
OFICIO NUMERIC(15),
SALARIO NUMERIC(6),
COMISION NUMERIC(6),
DEP_NO NUMERIC(2) NOT NULL);

CREATE TABLE DEPART(
DEP_NO NUMERIC(2),
NOMBRE VARCHAR2(25),
LOCALIDAD VARCHAR2(30));

Mostrar el número de empleados en la tabla EMPLE utilizando un bloque anónimo:

Código [Seleccionar] Expandir

DECLARE
V_NUM NUMBER;
BEGIN
SELECT COUNT(EMP_NO)
INTO V_NUM
FROM EMPLE;

DBMS_OUTPUT.PUT_LINE('NUMERO DE EMPLEADOS: ' V_NUM);
END;
/

Mostrar la fecha del sistema con formato personalizado:
(Utilización de las funciones TO_CHAR y el valor SYSDATE de la tabla DUAL).

Código [Seleccionar] Expandir

DECLARE
OP VARCHAR2(100);
BEGIN
SELECT TO_CHAR(SYSDATE, '"EL DIA " DD "DE " MONTH "DE " YYYY "A LAS "  HH24 "Y " MI "CON " SS')
INTO OP
FROM DUAL;

DBMS_OUTPUT.PUT_LINE(OP);
END;
/

Concatenación de dos cadenas pasadas como parámetro a un procedimiento:

Código [Seleccionar] Expandir

CREATE OR REPLACE PROCEDURE EJ1 (PAR1 IN VARCHAR2, PAR2 IN VARCHAR2)
DECLARE
RES VARCHAR2(40);
BEGIN
RES:= UPPER(PAR1)||UPPER(PAR2);
DBMS_OUTPUT.PUT_LINE(RES);
END;
/

Procedimiento que divide dos números pasados como parámetros:
(Ejemplo de uso de EXCEPTION al dividir entre cero).

Código [Seleccionar] Expandir

CREATE OR REPLACE PROCEDURE EJ3(NUM1 NUMBER, NUM2 NUMBER)
IS
RES NUMBER;
BEGIN
RES:=NUM1/NUM2;
DBMS_OUTPUT.PUT_LINE(RES);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('NO SE PUEDE DIVIDIR POR CERO');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR INESPERADO');
END;
/

Mostrar los datos de un empleado a partir de su número. Saltará una exception en caso de que el número indicado no exista:

Código [Seleccionar] Expandir

CREATE OR REPLACE PROCEDURE EJ4(NUM1 NUMBER)
IS
  APE EMPLE.APELLIDO%TYPE;
  SAL EMPLE.SALARIO%TYPE;
  FEC EMPLE.FECHA_ALT%TYPE;
BEGIN
SELECT APELLIDO, SALARIO, FECHA_ALT
INTO APE, SAL, FEC
FROM EMPLE
WHERE EMP_NO LIKE NUM1;
DBMS_OUTPUT.PUT_LINE(APE || ' ' || SAL || ' ' || FEC);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO EXISTE EL EMP_NO');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/

Función que devuelve el número de años completos entre dos fechas.
(Uso de las funciones TRUNC, ABS y TO_DATE y MONTHS_BETWEEN)

Código [Seleccionar] Expandir

CREATE OR REPLACE FUNCTION EJ9(D1 VARCHAR2, D2 VARCHAR2)
RETURN NUMBER
IS
  DD1 DATE;
  DD2 DATE;
RES NUMBER;
BEGIN
  DD1 := TO_DATE(D1);
  DD2 := TO_DATE(D2);
RES := ABS(MONTHS_BETWEEN(DD1, DD2)/12);
RETURN TRUNC(RES,0);
END;
/

Calcular los trienios entre dos fechas, utilizando la función anterior:
(Cómo utilizar una función dentro de otra)

Código [Seleccionar] Expandir

CREATE OR REPLACE FUNCTION TRIENIOS(FECHA1 DATE, FECHA2 DATE)
RETURN NUMBER
AS
V_TRIENIOS NUMBER(6);
BEGIN
V_TRIENIOS :=TRUNC(ANOS_DIF(FECHA1,FECHA2)/3);
RETURN V_TRIENIOS;
END;

Ejemplo de función recursiva, cálculo del factorial de un número pasado como parámetro de la función:

Código [Seleccionar] Expandir

CREATE FUNCTION FACTORIAL (V_NUM NUMBER)
RETURN NUMBER
IS
BEGIN
IF (V_NUM<=1) THEN
RETURN 1;
ELSE
RETURN V_NUM*FACTORIAL(V_NUM-1);
END IF;
END;

Convertir un número a día correspondiente de la semana.
(Ejemplo muy básico de una estructura tipo CASE).

Código [Seleccionar] Expandir

CREATE OR REPLACE FUNCTION DIASCASE(NUM NUMBER)
RETURN VARCHAR2
AS
BEGIN
CASE NUM
WHEN '1' THEN
RETURN 'LUNES';
WHEN '2' THEN
RETURN 'MARTES';
WHEN '3' THEN
RETURN 'MIERCOLES';
WHEN '4' THEN
RETURN 'JUEVES';
WHEN '5' THEN
RETURN 'VIERNES';
WHEN '6' THEN
RETURN 'SABADO';
WHEN '7' THEN
RETURN 'DOMINGO';
ELSE
    RETURN 'NUMERO ERRONEO';
END CASE;
END;
/

Procedimiento que escribe los números de 1 a 10:
(Ejemplo simple de la utilización de LOOP).

Código [Seleccionar] Expandir

CREATE OR REPLACE PROCEDURE CONTAR1()
IS

VAR NUMERIC := 0;

BEGIN

LOOP
VAR := VAR + 1;
DBMS_OUTPUT.PUT_LINE(VAR);
EXIT WHEN VAR = 10;
END LOOP;
END;
/

Contar desde un máximo hasta un mínimo, con un cierto salto, los tres valores son parámetros:

Código [Seleccionar] Expandir

CREATE OR REPLACE PROCEDURE CONTAR4(MN NUMERIC, MX NUMERIC, JP NUMERIC)
IS
VAR NUMERIC := MN;
BEGIN
DBMS_OUTPUT.PUT_LINE('DESDE ' || MN || ' HASTA ' || MX || ' CON SALTO ' || JP);
LOOP
DBMS_OUTPUT.PUT_LINE(VAR);
VAR := VAR + JP;
EXIT WHEN VAR > MX;
END LOOP;
END;
/

Contar desde un número hasta cero, en este caso con un bucle for:

Código [Seleccionar] Expandir

CREATE OR REPLACE PROCEDURE CONTAR(N NUMBER)
IS
  C NUMBER;
BEGIN
  C := N;
IF N BETWEEN 1 AND 10 THEN
FOR C IN REVERSE 0..N
LOOP
DBMS_OUTPUT.PUT_LINE(C);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('NUMERO FUERA DE RANGO (1-10)');
END IF;
END;
/

Invertir una cadena introducida como parámetro:
(Uso de función SUBSTR)

Código [Seleccionar] Expandir

CREATE OR REPLACE PROCEDURE REVS2(WD VARCHAR2)
IS
C NUMBER;
L NUMBER;
RS_OUT VARCHAR2(20);
BEGIN
C := 0;
L := LENGTH(WD);
FOR C IN REVERSE 1..L
LOOP
RS_OUT := RS_OUT || SUBSTR(WD, C, 1);
END LOOP;
DBMS_OUTPUT.PUT_LINE(RS_OUT);
END;
/

[Carloswaldo]

Interesante, gracias por el aporte, lo he agregado a las FAQ's