Algunos ejemplos de PL/SQL

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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