PL SQL insert update delete select into [examples]

 Veremos como implementar procedimientos almacenados con programación PL/SQL de Oracle. Los procedimientos almacenados nos servirán para programar rutinas que queramos tener automatizadas y definidas previamente.

Responderemos:

    - ¿Como crear un procedimiento almacenado en Oracle PL/SQSL? 

    - ¿Como ejecutar un procedimiento almacenado en Oracle PL/SQL?

    - Trabajar con entradas y salidas de parámetros de un procedimientos almacenado y retornar registro

En este blog crearemos sólo procedimientos almacenados para hacer consultas SELECT, INSERT, UPDATE y DELETE en su forma mas sencilla, ya que se pueden hacer tan complejos como se requiera. 

Necesitamos tener instalador el servicio de base de datos de Oracle, en mi caso tengo la versión Oracle DataBase 11g  Express Edition pero los procedimientos almacenados que haremos nos servirán para prácticamente todas las versiones de Oracle. También necesitaremos el Oracle SQL Developer.

Empecemos. 

De entrada debemos crear una tabla o al menos tener una tabla para que en ella se apliquen o se dirijan los procedimientos almacenados.

Yo tengo la tabla PERSONA y con los siguientes atributos.

ID    NUMBER(5,0)    
NOMBRE    VARCHAR2(100 BYTE)   
APELLIDO    VARCHAR2(100 BYTE)    
TELEFONO    VARCHAR2(100 BYTE)    
CIUDAD    VARCHAR2(100 BYTE)  
DOMICILIO    VARCHAR2(100 BYTE)   
RFC    VARCHAR2(100 BYTE)   
CURP    VARCHAR2(100 BYTE)   

 

De lado izquierdo de nuestro SQL Developer tenemos la lista de objetos que podemos trabajar

Nos interesan el apartado de Tablas y Procedimientos solo tenemos que dar click derecno en tablas o procedimientos y seleccionar Nuevo, y el software nos guiará para poder crear nuestro objeto Tabla o Procedimiento. El que nos interesa en este momento es crear un procedimiento. 

Daremos click derecho sobre Procedimientos y  click en Nuevo Procedimiento, le pondremos un nombre e indicaremos que parámetros, tipo de dato y modo serán. El nombre de es como yo quiera trabajar a la variable en el procedimiento, el modo indica si será de entrada(IN) o se salida(OUT) y el tipo se refiere al tipo de dato y tiene que ser igual con el que quiera hacer match al de la tabla ya existente. 

En el ejemplo de la imagen estoy definiendo un nombre SP_SELECT_PERSONAS_BYID, es decir voy a seleccionar solo aquel registro que tenga o que coincida con dicho ID de entrada que lo defino como IN_ID modo IN, dicho parámetro se lo tengo que pasar cuando yo ejecute el procedimiento. Los demás parámetros  los he definido como de salida (OUT), es decir esos son los parámetros que recibiré al ejecutar el procedimiento.  

Podemos omitir la definición de parámetros de entrada y salida en esta ventana, ya que podemos declararlos manualmente en el código PL/SQL. 

Cuando hacemos el INTO es cuando pasamos los datos del SELECT a las variables de salida.

Una vez que tengamos esta parte ya podemos compilar nuestro procedimiento almacenado, podemos presionar ctrol + s para guardar los cambios y automáticamente se compila.

 

	CREATE OR REPLACE PROCEDURE sp_select_personas_byid (
	in_id IN NUMBER,
	out_nombre OUT VARCHAR2,
	out_apellido OUT VARCHAR2,
	out_telefono OUT VARCHAR2,
	out_ciudad OUT VARCHAR2,
	out_domicilio OUT VARCHAR2,
	out_rfc OUT VARCHAR2,
	out_curp OUT VARCHAR2
	) AS
	BEGIN
	SELECT
	nombre,
	apellido,
	telefono,
	ciudad,
	domicilio,
	rfc,
	curp
	INTO
	out_nombre,
	out_apellido,
	out_telefono,
	out_ciudad,
	out_domicilio,
	out_rfc,
	out_curp
	FROM
	persona
	WHERE
	id = in_id;
	END sp_select_personas_byid;

view raw SP_SELECT_PERSONAS_BYID.sql hosted with ❤ by GitHub

 

Continuamos haciendo de manera similar para los siguientes procedimientos. Este procedimiento es para insertar un nuevo registro por tanto para nuestras necesidades todas las variables son de entrada.

	CREATE OR REPLACE PROCEDURE sp_insert_person (
	in_id IN NUMBER,
	in_nombre IN VARCHAR2,
	in_apellido IN VARCHAR2,
	in_telefono IN VARCHAR2,
	in_ciudad IN VARCHAR2,
	in_domicilio IN VARCHAR2,
	in_rfc IN VARCHAR2,
	in_curp IN VARCHAR2
	) AS
	BEGIN
	INSERT INTO persona (
	id,
	nombre,
	apellido,
	telefono,
	ciudad,
	domicilio,
	rfc,
	curp
	) VALUES (
	in_id,
	in_nombre,
	in_apellido,
	in_telefono,
	in_ciudad,
	in_domicilio,
	in_rfc,
	in_curp
	);
	END sp_insert_person;

view raw sp_insert_person.sql hosted with ❤ by GitHub

Agregaremos otro para actualizar un registro 

 

	CREATE OR REPLACE PROCEDURE sp_update_person_byid (
	in_id IN NUMBER,
	in_nombre IN VARCHAR2,
	in_apellido IN VARCHAR2,
	in_telefono IN VARCHAR2,
	in_ciudad IN VARCHAR2,
	in_domicilio IN VARCHAR2,
	in_rfc IN VARCHAR2,
	in_curp IN VARCHAR2
	) AS
	BEGIN
	UPDATE persona
	SET
	nombre = in_nombre,
	apellido = in_apellido,
	telefono = in_telefono,
	ciudad = in_ciudad,
	domicilio = in_domicilio,
	rfc = in_rfc,
	curp = in_curp
	WHERE
	id = in_id;
	END sp_update_person_byid;

view raw sp_update_person_byid.sql hosted with ❤ by GitHub

También agregamos otro para eliminar un registro por su ID

	CREATE OR REPLACE PROCEDURE sp_delete_person_byid (
	in_id IN NUMBER
	) AS
	BEGIN
	DELETE FROM persona
	WHERE
	id = in_id;
	END sp_delete_person_byid;

view raw sp_delete_person_byid.sql hosted with ❤ by GitHub

Para ejecutar los procedimientos almacenados en el ambiente de SQL Developer debemos dar click en el botón ejecutar
 D

Posteriormente nos pedirá los datos correspondientes, en este caso estoy ejecutando el que selecciona por id, por lo tanto le paso el valor del ID que yo quiera y que existe en la tabla PERSONA.

Al darle Aceptar me retornará los resultados de la consulta.

Aquí ya tenemos los resultados.

Y de esta manera podemos checar que nuestros procedimientos se están ejecutando correctamente.

Claro!, esto es en su forma muy básica, porque puede ser que le mandemos un ID que no exista y truene nuestro procedimiento, entonces debemos meterle excepciones o comparaciones para validar dichos errores. 

En otro blog publicaré procedimientos mas elaborados con excepciones y validaciones. También veremos como ejecutarlos desde Java y como mandar y recibir los parámetros. Además que también queda pendiente como retornar varios registros (cuando hacemos un SELECT * FROM) en un solo procedimiento. 

Sería todo por el momento...

Cualquier duda o comentario es bienvenido.

Saludos!!