Introducción a Oracle PL/SQL

PL/SQL significa Procedural Language/Structured Query Language. Es un lenguaje de programación incluido dentro de la base de datos. Los procedimientos, funciones, disparadores y paquetes creados con el PL/SQL se almacenan en base de datos. Están incluidos dentro de las políticas de seguridad de Oracle y son altamente recomendables, para el tratamiento de datos.

El PL/SQL surge ante la necesidad de ampliar las posibilidades del SQL, ya que es un lenguaje de consulta y no un lenguaje de programación.
La ventaja respecto a otros lenguajes, para trabajar con una base de datos, es poder trabajar directamente en el servidor de base de datos. Es un lenguaje de programación concebido por y para Oracle.

Con PL/SQL vamos a poder crear:

• Procedimientos
• Funciones
• Triggers
• Paquetes
• Bloques PL/SQL

Algunas pinceladas de las características del lenguaje PL/SQL serían:

• Utilización de constantes, variables y tipos (tanto predefinidos, como definidos por el usuario)
• Estructuras de control: bucles, ordenes condicionadas
• Paquetes, procedimientos, funciones, bloques PL/SQL
• Funciones predefinidas: aritméticas, lógicas, relacionales, …
• Cursores
• Tratamiento de excepciones
• Posibilidad de utilización de comentarios

El lenguaje PL/SQL no distingue de mayúscula de minúscula.
Los comentarios monolínea van precedidos de «–» y los comentarios multilíneas estarían entre «/*» y «*/».

VARIABLES Y CONSTANTES

La declaración de variables tiene el formato siguiente:

Nombre_variable tipo_dato [NOT NULL] [ :=valor];

La declaración de constantes tiene el formato siguiente:

Nombre_constante CONSTANT tipo_dato :=valor;

Siendo tipo_dato: tipo de datos soportados por Oracle como: NUMBER, DATE, CHAR, VARCHAR VARCHAR2, BOOLEAN, …

BLOQUE PL/SQL

Elementos de un bloque serían:

• Declaraciones (opcional): Es donde se declaran las constantes, variables y cursores, que se utilizarán en el bloque.
• Instrucciones (obligatorio): Contiene las sentencias a ejecutar
• Excepciones (opcional): Desde este elemento se puede capturar las excepciones del sistema o lanzada por el propio usuario

BLOQUE PL/SQL NO ALMACENADO:

[ DECLARE | IS / AS ]
BEGIN
[ EXCEPTION ]
END

PROCEDIMIENTO:

CREATE [OR REPLACE] PROCEDURE nombre_procedimiento [nombre_parametro modo tipodatos_parametro ] IS | AS
BEGIN
[ EXCEPTION ]
END

FUNCIÓN:

CREATE [OR REPLACE] FUNCTION nombre_función [nombre_parámetro modo tipodatos_parametro ] RETURN tipodatos_retorno IS | AS
BEGIN
[ EXCEPTION ]
END

TRIGGER:

CREATE [OR REPLACE] TRIGGER nombre_trigger
momento_ejecución evento [evento] ON nombre_tabla
[REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW
[WHEN condición]
BEGIN
[ EXCEPTION ]
END

Ejemplo procedimiento:

DECLARE
/* Declaración de la variable donde se recogerá el valor de la fecha actual */
    fecha DATE;
/* Declaración genérica de error no controlado por el sistema */
    error_generico CONSTANT VARCHAR2(10):= 'Error descontrolado';
BEGIN
/* Parte de ejecución */
    SELECT SYSDATE INTO fecha -- Recoge la fecha actual y la asigna a la variable fecha
      FROM DUAL;

    dbms_output.put_line('La fecha actual es: '||fecha);
EXCEPTION
-- tratamiento de excepciones
    WHEN OTHERS THEN -- Captura las excepciones generadas por el sistema
        dbms_output.put_line(error_generico); -- Muestra el error declarado previamente como constante
END;
/

CURSORES

Son elementos, para poder gestionar los valores devueltos por una consulta a la base de datos.
Se pueden distinguir dos tipos:

• Cursores implícitos: Se utilizan, cuando una consulta devuelve un registro
• Cursores explícitos: Son controlados por el programador y suelen ser más eficientes

Ejemplo cursor implícito:

declare
    vprovincia VARCHAR2(50);

begin
    SELECT DESCRIPCION INTO vprovincia
      FROM PROVINCIAS
     WHERE CO_PROVINCIA = '47';

    dbms_output.put_line('La provincia es: ' || vprovincia);
end;
/

Salida:

La provincia es: Valladolid

Ejemplo cursor explicito:

DECLARE
    CURSOR cprovincias IS
        SELECT CO_PROVINCIA, DESCRIPCION
          FROM PROVINCIAS;

    CO_PROVINCIA VARCHAR2(3);
    descripcion VARCHAR2(50);

BEGIN
    OPEN cprovincias;
    FETCH cprovincias INTO CO_PROVINCIA,descripcion;
    WHILE cprovincias %found LOOP
        dbms_output.put_line(descripcion);
        FETCH cprovincias INTO CO_PROVINCIA,descripcion;
    END LOOP;
    CLOSE cprovincias;
END;
/

También podría ser:

BEGIN
    FOR REG IN (SELECT * FROM PROVINCIAS) LOOP
        dbms_output.put_line(reg.descripcion);
    END LOOP;
END;
/

EXCEPCIONES

Para controlar los errores, que se genera en la aplicación se utilizan las excepciones. Estas pueden ser personalizadas o del sistema.
Para lanzar una excepción se lanzaría de la siguiente manera:

RAISE_APPLICATION_ERROR(<error_num>,);

El número de error personalizado estaría en el rango del -20001 al -20999.

Ejemplo captura de excepción del manual junto a excepción genérica:

CREATE OR REPLACE PROCEDURE comprobar_negativo (valor NUMBER) AS

    VALOR_ERRONEO EXCEPTION;

BEGIN
    IF valor < 0 THEN
        RAISE VALOR_ERRONEO;
    END IF;
    
    dbms_output.put_line('Correcto: '||valor||' es >= 0');
EXCEPTION
    WHEN VALOR_ERRONEO THEN -- Captura de error usuario
        dbms_output.put_line('ERROR: Valor negativo');
    WHEN OTHERS THEN -- Captura error genérico
        err_num := SQLCODE;
        err_msg := SQLERRM;
        dbms_output.put_line('Error:'||TO_CHAR(err_num));
        dbms_output.put_line(err_msg);
END;
/

TRANSACCIONES

Las transacciones son un conjuntos de operaciones en la base de datos, que se tratan como una unidad. Se realizan todas o se descartan todas. Para confirmar las transacciones pendientes se utiliza la sentencia COMMIT y para deshacer las transacciones pendientes se realizaría con ROLLBACK.

Ejemplo de manejo de transacciones:

DECLARE
    vValor NUMBER := 100;
    vOrigen VARCHAR2(1) :='A';
    vDestino VARCHAR2(1) := 'B';

BEGIN
    UPDATE tabla SET valor = vValor
     WHERE origen = vOrigen;

    INSERT INTO tabla2 (Origen, Destino, valor, FECHA)
                VALUES (vOrigen, vDestino, vValor, SYSDATE);

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error en la transaccion:'||SQLERRM);
        dbms_output.put_line('Se deshacen las modificaciones);
        ROLLBACK;
END;
/