PL/SQL - Bloques

PL/SQL es un lenguaje de programación procedimental para manipular datos de una BD Oracle. Con este lenguaje podemos crear scripts para poder manipular información de tablas, ejecutar otros scripts, crear procedimientos que se activen ante un evento(triggers), que devuelvan valores (funciones) o que simplementen realicen una operación al invocar un procedimiento (stored procedures).

Si queremos saber la versión instalada podemos ejecutar esta sentencia:

-- Con una consulta 
SELECT * FROM v$version;

En PL/SQL podríamos ejecutar el siguiente bloque de código:

-- Con un bloque PL/SQL
SET SERVEROUTPUT ON;
DECLARE
    version_inst VARCHAR(200);
    consulta VARCHAR(200):='SELECT BANNER FROM v$version';
BEGIN
    -- Ejecutamos consulta
    EXECUTE IMMEDIATE consulta INTO version_inst;
    dbms_output.put_line('Version instalada: '||version_inst);
END;
/

Si queremos podríamos crear excepciones:

-- Con un bloque PL/SQL con manejo de excepción
SET SERVEROUTPUT ON;
DECLARE
   NULL_STRING EXCEPTION;
   PRAGMA 
   EXCEPTION_INIT(NULL_STRING, -06535);
   version_inst VARCHAR(200);
   consulta VARCHAR(200);
BEGIN
    -- Ejecutamos consulta
    EXECUTE IMMEDIATE consulta INTO version_inst;
    dbms_output.put_line('Version instalada: '||version_inst);
    EXCEPTION
        WHEN NULL_STRING THEN
           dbms_output.put_line('Error: No existe la tabla o la consulta es erronea o esta vacía');
END;
/

Como se puede ver, creamos una excepción (NULL_STRING) para verificar que la cadena de consulta es nula (vacía). Usamos PRAGMA la cual es una palabra reservada de Oracle para añadir directivas al compilador. Ésta le dice al compilador que se comporte de cierta manera, en este caso es una excepción (un error en el flujo del programa).

El pragma EXCEPTION_INIT asocia un nombre de excepción con un número de error de Oracle. Puede interceptar cualquier error ORA- y escribir un controlador específico para él en lugar de usar el controlador OTROS (OTHERS).

La sección de EXCEPTION WHEN se ejecuta cuando ocurre alguna excepción. Dependiendo de la programación se podrá (o no) cachar correctamente la excepción. Recordar que una excepción es una anomalía en el flujo de un programa, no necesariamente es un error grave. En caso de un error, el programa se terminará abruptamente.

Al final tendríamos algo como esto:

-- Con un bloque PL/SQL y verificando consulta
SET SERVEROUTPUT ON;
DECLARE
   NULL_STRING EXCEPTION;
   PRAGMA 
   EXCEPTION_INIT(NULL_STRING, -06535);
   version_inst VARCHAR(200);
   consulta VARCHAR(200):='SELECT BANNER FROM v$version';
BEGIN
    IF LENGTH(consulta) > 0 THEN
       -- Ejecutamos consulta
       EXECUTE IMMEDIATE consulta INTO version_inst;
       dbms_output.put_line('Version instalada: '||version_inst);
    END IF;
    EXCEPTION
        WHEN NULL_STRING THEN
           dbms_output.put_line('Error: No existe la tabla o la consulta es erronea o esta vacía');
END;
/

En próximos post veremos más ejemplos.

Enlaces:

https://livesql.oracle.com/
https://www.techonthenet.com/oracle
http://www.rebellionrider.com
https://docs.oracle.com
https://www.codifica.me/pragma/
https://elbauldelprogramador.com/plsql-excepciones/

Ejemplo de uso de MySQL: una pequeña bitácora

Tener una bitácora nos ayuda a tener un mejor control de nuestra información:

• Ver quien hace cambios por “error”.
• Ver la fecha de la operación,
• Tipo de operación (inserción, actualización y/o borrado).

Bueno he aquí los pasos para crear una pequeña bitácora

1. Crear la base de datos

$ create database prueba1;
$ show databases;
$ use prueba1;

2. Crear una tabla llamadas personas

create table personas (id_persona int auto_increment primary key, 
nombre varchar (50), 
apellido_p varchar (50), 
apellido_m varchar(50), 
edad int, 
peso double, 
talla double);

3. Crear un procedimiento almacenado para insertar registros

DELIMITER $$
DROP PROCEDURE IF EXISTS `prueba1`.`Insertar` $$
CREATE PROCEDURE `prueba1`.`Insertar`(in nombre varchar (50), apellido_p varchar (50), apellido_m varchar(50), edad int, peso double, talla double)

BEGIN
INSERT INTO personas (nombre,apellido_p, apellido_m , edad , peso , talla) VALUES (nombre,apellido_p, apellido_m , edad , peso , talla );

END $$

DELIMITER ;

4. Crear un procedimiento almacenado para actualizar registros

DELIMITER $$
DROP PROCEDURE IF EXISTS `prueba1`.`Actualizar` $$
CREATE PROCEDURE `prueba1`.`Actualizar` (in id_personax int,edadx int,pesox double)
BEGIN
update personas set edad=edadx,peso=pesox where id_persona=id_personax;
END $$

DELIMITER ;

5. Crear un procedimiento almacenado para eliminar registros

DELIMITER $$

DROP PROCEDURE IF EXISTS `prueba1`.`Borrar` $$
CREATE PROCEDURE `prueba1`.`Borrar` (in id int)
BEGIN
delete from personas where id_persona=id;
END $$

DELIMITER ; 

6. Invocar los procedimientos almacenados

Call Insertar (‘Horacio’,’Gomez’,’Carmona’,25,65,1.68);
Call Insertar (‘Hugo’,’Torres’,’Mendez’,32,67,1.70);
Call Actualizar (1, 26, 66);
Call Borrar (2);

Ver registros

select * from personas;

7. Crear un procedimiento almacenado que muestre todos los registros

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_verPersonas` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_verPersonas`()
select *from personas $$

DELIMITER ;

Para invocarlo

CALL sp_verPersonas();

8. Crear tabla que lleve un control de quien hace cambios en la base de datos prueba1

CREATE TABLE infobase (usuario varchar (50), 
descripcion varchar (50),
fecha TIMESTAMP default now());

9. Crear trigger para la inserción

delimiter $$

create trigger tr_insercion
befote insert on personas
for each row
begin
insert into infobase values(user(),’persona creada’,now());

end $$

10. Crear trigger para la actualización

delimiter $$

create trigger tr_actualizacion
befote update on personas
for each row
begin
insert into infobase(usuario,descripcion,fecha) values(user(),’persona modificada’,now());

end $$

11. Crear trigger para la eliminación

delimiter $$

create trigger tr_borrado
befote delete on personas
for each row
begin
insert into infobase values(user(),’persona eliminada’,now());

end $$

Ver cambios

select * from infobase;

12. Crear procedimiento almacenado para ver un registro en particular

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_verPersona` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_verPersona`(in id_buscar int)
select *from personas where id_persona=id_buscar $$

DELIMITER ;

13. Ahora hacer una operación: calcular el I.M.C. (Índice de Masa Corporal) de las personas registradas

Fórmula: peso/(talla*talla)

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_verImc` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_verImc`()
select id_persona,nombre,edad,peso,talla,peso/(talla*talla) from personas $$

DELIMITER ;

14. Programa en Java para ver los registros

package control.pruebas;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;

public class Codemonkey {

public static void main(String[] args) {
try{
 Class.forName("com.mysql.jdbc.Driver");
 Connection conecta=(Connection)  DriverManager.getConnection("jdbc:mysql://localhost:3306/prueba1","root","root");
 System.out.println("\tConexión exitosa\n");
 Statement sentencia= conecta.createStatement();
ResultSet registro=sentencia.executeQuery("select id_persona,nombre,edad,peso,talla,peso/(talla*talla) from personas");

   while(registro.next()){
    System.out.println("\t\nId: "+registro.getInt("id_persona"));
    System.out.println("Nombre: "+registro.getString("nombre"));
    System.out.println("Edad:"+registro.getInt("edad"));
    System.out.println("Peso:"+registro.getDouble("peso"));
    System.out.println("Talla: "+registro.getDouble("talla"));
    System.out.println("IMC: "+registro.getDouble("peso/(talla*talla)"));
  }
   conecta.close();
   System.out.println("\tConexión cerrada");
}catch(Exception ex){
    ex.printStackTrace();
}

}


}

Código:
http://www.pasteall.org/48005/mysql

Enlaces:

https://dev.mysql.com/doc/refman/8.4/en/create-procedure.html