Programación de Base de Datos - IL4 - S13

Guía de Nombre Nombre
laboratorio de carrera Del curso
Programación en Base de Datos
Introducción y Definición de Oracle

Sesión 13
Objetivo del Construye programas avanzados utilizando el lenguaje PL/SQL que soporten la lógica de
Laboratorio negocio mediante la incorporación de funciones y procedimientos almacenados,
organizados en paquetes y a través de disparadores almacenados en la base de datos.
“Es un sistema de gestión de base de datos de tipo objeto-relacional (ORDBMS, por el

Marco acrónimo en inglés de Object - Relational Data Base Management System), desarrollado
teórico
por Oracle Corporation. Se considera como uno de los sistemas de bases de datos más
completos, destacando: soporte de transacciones, estabilidad, escalabilidad, y soporte
multiplataforma”.
Oracle es un sistema gestor de base de datos con características objeto - relacional, que
pertenece al modelo evolutivo de SGDB, este sistema permite controlar y gestionar
grandes volúmenes de contenidos.
a. Hardware
Recursos  Sistema. Oracle en Windows requiere un PC Intel x86, AMD64 o Intel EM64T
 Memoria. Al menos 1 GB de RAM y el doble en virtual.
 Espacio en disco duro. Al menos 6 GB para la instalación (algunas instalaciones
requieren menos). Además, necesitamos poder almacenar 500 MB en la carpeta TEMP
del sistema.
 Tarjeta gráfica. Debe de ser capaz de mostrar 1024 por 768 píxeles como mínimo y
256 colores.
b. Software
 Windows 2003 Server y 2003 Server R2.
 Windows XP Professional.
 Windows Vista, pero no la versión Home Edition.
 Windows Server 2008 y 2012. No la versión Server Core.
Compiladores. Se usan para la gente que crean aplicaciones en Oracle usando
lenguajes como Pro C, Pro COBOL, JAVA.
 Visual C++.NET 2005 8.0 o Intel 10.1 C, .Net Express.
Navegador. Para configurar algunos servicios de Oracle. Debe de ser navegador
moderno (Internet Explorer 6 o superior, Firefox 2.0 o superior, Safari 3.1 o
superior, Chrome 3.0 o superior)
Usar dirección IP única en la máquina en la que se instala Oracle. Es decir, no usar
DHCP para direccionar la IP en el servidor de Oracle. No es un requisito obligatorio,
pero es muy recommendable.
1
______________________________________________________________________
13.1. IMPLEMENTANDO PROCEDIMIENTOS ALMACENADOS EN MANTENIMIENTO DE

TABLAS.
Oracle permite acceder y manipular información de la base de datos definiendo objetos procedurales
(subprogramas) que se almacenan en la base de datos. Estos objetos procedurales son unidades de
programa PL/SQL: Funciones y Procedimientos almacenados.
Los procedimientos o funciones son bloques PL/SQL con nombre, que pueden recibir parámetros y
pueden ser invocados desde distintos entornos: SQL*PLUS, Oracle*Forms, desde otros
procedimientos y funciones y desde otras herramientas Oracle y aplicaciones.
Los procedimientos y funciones llevan a cabo tareas específicas, y su mayor diferencia radica en que
las funciones devuelven un valor.
RECORDEMOS:
Su sintaxis es la siguiente:
CREATE OR REPLACE PROCEDURE <NOMBRE_PROCEDURE> (PARAMETROS)
IS
<DEFINICION_VARIABLES_LOCALES_DEL_PROCEDIMIENTO,>
BEGIN
<CODIGO_PLSQL>
. . . . . . . . . . . . . .
END;
 El uso de OR REPLACE permite sobre escribir un procedimiento existente. Si se omite, y el

procedimiento existe, se producirá, un error.
 La sintaxis es muy parecida a la de un bloque anónimo, salvo porque se reemplaza la sección

DECLARE por la secuencia PROCEDURE... IS en la especificación del procedimiento.
 Debemos especificar el tipo de datos de cada parámetro. Al especificar el tipo de dato del
parámetro no debemos especificar la longitud del tipo.
LOS PARÁMETROS QUE PUEDE RECIBIR UN STORED PROCEDURE PUEDEN SER DE 3

TIPOS:
Los parámetros son variables que reciben valores desde el exterior y después de ciertos procesos
son capaces de hacer retorno de valores.
IN (Default):
Si un parámetro es IN (Entrada) no puede ser modificado en el transcurso del código PLSQL.
OUT:
Si un parámetro es OUT (Salida) siempre llega al código del stored procedure con el valor de NULL y
cuando termina el código de PLSQL el parámetro mantiene el valor de forma persistente.
IN OUT: (Entrada/Salida)
Es una combinación de ambos.
Programación avanzada en Oracle.
2
______________________________________________________________________
Para estos ejemplos contamos con el siguiente modelo relacional.
LA TABLA CLIENTE CUENTA SON 5 REGISTROS.
LA TABLA EMPLEADO CUENTA CON 5 REGISTROS:
LA TABLA PRODUCTO CUENTA CON 10 REGISTROS:
13.2.- PROCEDIMIENTO ALMACENADO DE INSERCIÓN DE DATOS EN PL/SQL.
3
______________________________________________________________________
Elaborar procedimientos almacenados que ofrezca mantenimiento de datos (INSERCION,

ACTUALIZACION y CONSULTA, para las tablas CLIENTE, EMPLEADO y PRODUCTO.
Respetando la cantidad de registros empezaremos con los mantenimientos.
PROCEDIMIENTOS ALMACENADOS NECESARIOS PARA EL MANTENIMIENTO DE CLIENTES.
GRABAR CLIENTE:
CREATE OR REPLACE PROCEDURE SP_GRABAR_CLIENTE
(CODIGO IN VARCHAR2, NOM IN VARCHAR2,
RUC IN VARCHAR2, DIR IN VARCHAR2, EST IN VARCHAR2)
IS
BEGIN
INSERT INTO CLIENTE VALUES (CODIGO, NOM, RUC, DIR, EST);
COMMIT;
DBMS_OUTPUT.PUT_LINE ('EXITO: Cliente Grabado Satisfactoriamente!');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE ('ERROR: El Cliente ya existe');
END;
CONSULTAR CLIENTE:
CREATE OR REPLACE PROCEDURE SP_CONSULTAR_CLIENTE
(CODIGO IN VARCHAR2)
IS
COD CLIENTE.IDCLI%TYPE;
NOM CLIENTE.NOMBRE%TYPE;
RUC CLIENTE.RUC%TYPE;
DIR CLIENTE.DIRECCION%TYPE;
EST CLIENTE.ESTADO%TYPE;
BEGIN
SELECT IDCLI, NOMBRE,RUC,DIRECCION,ESTADO INTO COD,NOM,RUC,DIR,EST
FROM CLIENTE WHERE IDCLI=CODIGO;
DBMS_OUTPUT.PUT_LINE ('============================================');
DBMS_OUTPUT.PUT_LINE ('EXITO: Cliente Encontrado!');
DBMS_OUTPUT.PUT_LINE ('Codigo : ' || COD);
DBMS_OUTPUT.PUT_LINE ('Hombre : ' || NOM);
DBMS_OUTPUT.PUT_LINE ('Ruc : ' || RUC);
DBMS_OUTPUT.PUT_LINE ('Dirección : ' || DIR);
DBMS_OUTPUT.PUT_LINE ('Estado : ' || EST);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line ('Código de Cliente No Existe!');
END;
ACTUALIZAR CLIENTE:
4
______________________________________________________________________
CREATE OR REPLACE PROCEDURE SP_ACTUALIZAR_CLIENTE

(CODIGO IN VARCHAR2, newNOM IN VARCHAR2,
newRUC IN VARCHAR2, newDIR IN VARCHAR2, newEST IN VARCHAR2)
IS
oldCOD CLIENTE.IDCLI%TYPE;
oldNOM CLIENTE.NOMBRE%TYPE;
oldRUC CLIENTE.RUC%TYPE;
oldDIR CLIENTE.DIRECCION%TYPE;
oldEST CLIENTE.ESTADO%TYPE;
CONDICION VARCHAR2(50):='CAMBIOS REALIZADOS CON EXITO';
BEGIN
SELECT IDCLI, NOMBRE, RUC, DIRECCION, ESTADO
INTO oldCOD,oldNOM,oldRUC,oldDIR,oldEST
FROM CLIENTE WHERE IDCLI=CODIGO;
IF newNOM <> oldNOM THEN
UPDATE CLIENTE SET NOMBRE=newNom WHERE IDCLI=CODIGO;
ELSIF newRUC<>oldRUC THEN
UPDATE CLIENTE SET RUC=newRUC WHERE IDCLI=CODIGO;
ELSIF newDIR<>oldDIR THEN
UPDATE CLIENTE SET DIRECCION=newDIR WHERE IDCLI=CODIGO;
ELSIF newEST<>oldEST THEN
UPDATE CLIENTE SET ESTADO=newEST WHERE IDCLI=CODIGO;
ELSE
CONDICION:='NO HUBO CAMBIOS';
END IF;
IF CONDICION ='CAMBIOS REALIZADOS CON EXITO' THEN
COMMIT;
END IF;
DBMS_OUTPUT.PUT_LINE(CONDICION);
EXCEPTION
DBMS_OUTPUT.put_line ('Código del Cliente No Existe!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error de Actualización');
END;
PRUEBA DE LOS PROCEDIMIENTOS ALMACENADOS DE CLIENTES.

PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_GRABAR_CLIENTE.
Ejecute este código, para realizar una inserción.
SET SERVEROUTPUT ON;
BEGIN
SP_GRABAR_CLIENTE ('C0006',
'CASTRO VALENCIA ELSA',
'10254568977',
'AV. LOS PALOMARES 234 CERCADO',
'ACTIVO');
END;
5
______________________________________________________________________
Mediante una consulta a la tabla Cliente, verifique el contenido de la tabla, debe existir un nuevo
registro cuyo código empieza con ‘C0006’. SELECT * FROM CLIENTE;
PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_CONSULTAR_CLIENTE.
Ejecute este código, para realizar una consulta del código de cliente: ‘C0006’.
BEGIN
SP_CONSULTAR_CLIENTE ('C0006');
END;
Resultado:
Mediante una consulta a la tabla Cliente, verifique el contenido de la tabla, debe existir un nuevo
registro cuyo código empieza con ‘C0006’. SELECT * FROM CLIENTE;
Si ingresamos un código de cliente que no existe, por ejemplo: ‘C0100’, el programa devolverá
“Código de Cliente no Existe!”
PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_ACTUALIZAR_CLIENTE.
Para realizar una actualización, requerimos un registro modelo, para hacer cambios, en este ejemplo
usaremos al código de cliente ‘C0006’, cambiaremos el nombre y su estado
SELECT * FROM CLIENTE;
6
______________________________________________________________________
Realizar la actualización con los nuevos datos.
NUEVO NOMBRE = ‘OLIVARES CARPIO ALEXA’

NUEVO RUC = ‘1085858587’
NUEVA DIRECCION = ‘JR. HUARAZ 432 BREÑA’
Los demás datos quedan igual.
Ejecute este procedimiento, para realizar los cambios en código de cliente: ‘C0006’
BEGIN
SP_ACTUALIZAR_CLIENTE ('C0006',
‘OLIVARES CARPIO ALEXA’,
'1085858587',
'JR. HUARAZ 432 BREÑA’,
'ACTIVO');
END;
REGISTROS DE CLIENTES ANTES DE LA ACTUALIZACION:
REGISTROS DE CLIENTES DESPUES DE LA ACTUALIZACION:
FIN DE MANTENIMIENTO DE LA TABLA CLIENTE.
7
______________________________________________________________________
13.3.- PROCEDIMIENTO ALMACENADO DE ELIMINACIÓN DE DATOS EN PL/SQL.
PROCEDIMIENTO ALMACENADOS NECESARIOS PARA EL MANTENIMIENTO DE

EMPLEADOS.
GRABAR EMPLEADO:
CREATE OR REPLACE PROCEDURE SP_GRABAR_EMPLEADO
(CODIGO IN VARCHAR2, NOM IN VARCHAR2, RUC IN VARCHAR2,
DIR IN VARCHAR2, EST IN VARCHAR2)
IS
BEGIN
INSERT INTO EMPLEADO VALUES (CODIGO, NOM, RUC, DIR, EST);
COMMIT;
DBMS_OUTPUT.PUT_LINE ('EXITO: Empleado Grabado Satisfactoriamente!');
EXCEPTION
DBMS_OUTPUT.PUT_LINE ('ERROR: El Empleado ya existe');
END;
ELIMINAR EMPLEADO.
CREATE OR REPLACE PROCEDURE SP_GRABAR_EMPLEADO
IS
BEGIN
DELETE FROM EMPLEADO WHERE IDEMP = CODIGO;
COMMIT;
DBMS_OUTPUT.PUT_LINE ('EXITO: Eliminado Satisfactoriamente!');
EXCEPTION
DBMS_OUTPUT.put_line ('Empleado No Existe!');
END;
CONSULTAR EMPLEADO:
CREATE OR REPLACE PROCEDURE SP_CONSULTAR_EMPLEADO
IS
COD EMPLEADO.IDEMP%TYPE;
NOM EMPLEADO.NOMBRE%TYPE;
RUC EMPLEADO.RUC%TYPE;
DIR EMPLEADO.DIRECCION%TYPE;
EST EMPLEADO.ESTADO%TYPE;
BEGIN
SELECT IDEMP,NOMBRE,RUC,DIRECCION,ESTADO INTO COD,NOM,RUC,DIR,EST
FROM EMPLEADO WHERE IDEMP=CODIGO;
DBMS_OUTPUT.PUT_LINE ('EXITO: Empleado Encontrado!');
DBMS_OUTPUT.PUT_LINE ('Nombre : ' || NOM);
DBMS_OUTPUT.PUT_LINE ('Ruc : ' || RUC);
DBMS_OUTPUT.PUT_LINE ('Direccion : ' || DIR);
EXCEPTION
DBMS_OUTPUT.put_line ('Código de Empleado No Existe!');
END;
8
______________________________________________________________________
ACTUALIZAR EMPLEADO:
CREATE OR REPLACE PROCEDURE SP_ACTUALIZAR_EMPLEADO
(CODIGO IN VARCHAR2, newNOM IN VARCHAR2,
newRUC IN VARCHAR2, newDIR IN VARCHAR2, newEST IN VARCHAR2)
IS
oldCOD EMPLEADO.IDEMP%TYPE;
oldNOM EMPLEADO.NOMBRE%TYPE;
oldRUC EMPLEADO.RUC%TYPE;
oldDIR EMPLEADO.DIRECCION%TYPE;
oldEST EMPLEADO.ESTADO%TYPE;
BEGIN
SELECT IDEMP, NOMBRE, RUC, DIRECCION, ESTADO
INTO oldCOD, oldNOM, oldRUC, oldDIR, oldEST
FROM EMPLEADO WHERE IDEMP=CODIGO;
IF newNOM <> oldNOM THEN
UPDATE EMPLEADO SET NOMBRE=newNom WHERE IDEMP=CODIGO;
ELSIF newRUC<>oldRUC THEN
UPDATE EMPLEADO SET RUC=newRUC WHERE IDEMP=CODIGO;
ELSIF newDIR<>oldDIR THEN
UPDATE EMPLEADO SET DIRECCION=newDIR WHERE IDEMP=CODIGO;
ELSIF newEST<>oldEST THEN
UPDATE EMPLEADO SET ESTADO=newEST WHERE IDEMP=CODIGO;
ELSE
CONDICION: ='NO HUBO CAMBIOS';
END IF;

COMMIT;
END IF;
DBMS_OUTPUT.PUT_LINE (CONDICION);
EXCEPTION
DBMS_OUTPUT.put_line('Código del Empleado No Existe!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error de Actualización');
END;
PRUEBA DE LOS PROCEDIMIENTOS ALMACENADOS DE EMPLEADOS.

PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_GRABAR_EMPLEADO.
BEGIN
SP_GRABAR_EMPLEADO ('E0006',
'ESPEJO LA ROSA JUAN',
'10225588747',
'CALLE LOS TULIPANES 222 LOS OLIVOS',
'ACTIVO');
END;
9
______________________________________________________________________
Mediante una consulta a la tabla Empleado, verifique el contenido de la tabla, debe existir un nuevo
registro cuyo código empieza con ‘E0006’. SELECT * FROM EMPLEADO;
PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_CONSULTAR_EMPLEADO.
Ejecute este código, para realizar una consulta del código de cliente: ‘E0006’.
BEGIN
SP_CONSULTAR_EMPLEADO ('E0006');
END;
Resultado:
Si ingresamos un código de Empleado que no existe, por ejemplo: ‘E0100’, el programa devolverá
“Código de Empleado no Existe!”
PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_ACTUALIZAR_EMPLEADO.
usaremos al código de cliente ‘E0006’.
SELECT * FROM EMPLEADO;
10
______________________________________________________________________
NUEVO NOMBRE = ‘COLMENARES LOPEZ ELENA’

NUEVO RUC = ‘00000000000’
NUEVA DIRECCION = ‘AV. PERU 3676 SMP’
Ejecute este procedimiento, para realizar los cambios en código de cliente: ‘E0006’
BEGIN
SP_ACTUALIZAR_EMPLEADO ('E0006',
'COLMENARES PEREZ ELENA',
'00000000000',
'AV. PERU 3676 SMP',
'ACTIVO');
END;
REGISTROS DE EMPLEADOS ANTES DE LA ACTUALIZACION:
REGISTROS DE EMPLEADOS DESPUES DE LA ACTUALIZACION:
FIN DE MANTENIMIENTO DE LA TABLA EMPLEADO.
11
______________________________________________________________________
13.4.- PROCEDIMIENTO ALMACENADO DE ACTUALIZACIÓN DE DATOS EN PL/SQL
PROCEDIMIENTOS ALMACENADOS NECESARIOS PARA EL MANTENIMIENTO DE

PRODUCTO.
GRABAR PRODUCTO:
CREATE OR REPLACE PROCEDURE SP_GRABAR_PRODUCTO
(CODIGO IN VARCHAR2, NOM IN VARCHAR2, PREC IN NUMERIC,
ST IN INTEGER, EST IN VARCHAR2)
IS
BEGIN
INSERT INTO PRODUCTO VALUES (CODIGO, NOM, PREC, ST, EST);
COMMIT;
DBMS_OUTPUT.PUT_LINE ('EXITO: Producto Grabado Satisfactoriamente!');
EXCEPTION
DBMS_OUTPUT.PUT_LINE ('ERROR: El Producto ya existe');
END;
CONSULTAR PRODUCTO:
CREATE OR REPLACE PROCEDURE SP_CONSULTAR_PRODUCTO
IS
COD PRODUCTO.IDPROD%TYPE;
NOM PRODUCTO.NOMBRE%TYPE;
PREC PRODUCTO.PRECIO%TYPE;
ST PRODUCTO.STOCK%TYPE;
EST PRODUCTO.ESTADO%TYPE;
BEGIN
SELECT IDPROD, NOMBRE,PRECIO,STOCK,ESTADO INTO COD,NOM,PREC,ST,EST
FROM PRODUCTO WHERE IDPROD=CODIGO;
DBMS_OUTPUT.PUT_LINE ('EXITO: Producto Encontrado!');
DBMS_OUTPUT.PUT_LINE ('Nombre : ' || NOM);
DBMS_OUTPUT.PUT_LINE ('Ruc : ' || PREC);
DBMS_OUTPUT.PUT_LINE ('Direccion : ' || ST);
EXCEPTION
DBMS_OUTPUT.put_line ('Código de Producto No Existe!');
END;
12
______________________________________________________________________
ACTUALIZAR PRODUCTO:
CREATE OR REPLACE PROCEDURE SP_ACTUALIZAR_PRODUCTO
(CODIGO IN VARCHAR2, newNOMBRE IN VARCHAR2,
newPRECIO IN NUMERIC, newSTOCK IN INTEGER, newESTADO IN VARCHAR2)
IS
oldCODIGO PRODUCTO.IDPROD%TYPE;
oldNOMBRE PRODUCTO.NOMBRE%TYPE;
oldPRECIO PRODUCTO.PRECIO%TYPE;
oldSTOCK PRODUCTO.STOCK%TYPE;
oldESTADO PRODUCTO.ESTADO%TYPE;
BEGIN
SELECT IDPROD, NOMBRE, PRECIO, STOCK, ESTADO
IF newNOMBRE
INTO <> oldNOMBRE,
oldCODIGO, oldNOMBRE THEN
oldPRECIO, oldSTOCK, oldESTADO
UPDATE PRODUCTO SET NOMBRE=newNOMBRE WHERE IDPROD=CODIGO;
FROM PRODUCTO WHERE IDPROD=CODIGO;
ELSIF newPRECIO <> oldPRECIO THEN
UPDATE PRODUCTO SET PRECIO=newPRECIO WHERE IDPROD=CODIGO;
ELSIF newSTOCK <> oldSTOCK THEN
UPDATE PRODUCTO SET STOCK=newSTOCK WHERE IDPROD=CODIGO;
ELSIF newESTADO <> oldESTADO THEN
UPDATE PRODUCTO SET ESTADO=newESTADO WHERE IDPROD=CODIGO;
ELSE
CONDICION:='NO HUBO CAMBIOS';
END IF;
COMMIT;
END IF;
DBMS_OUTPUT.PUT_LINE (CONDICION);
EXCEPTION
DBMS_OUTPUT.put_line ('Código del Producto No Existe!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error de Actualizacion');
END;
PRUEBA DE LOS PROCEDIMIENTOS ALMACENADOS DE PRODUCTOS.

PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_GRABAR_PRODUCTO.
BEGIN
SP_GRABAR_PRODUCTO ('P0011','COCINA A GAS 3 HORNILLAS', +
1495.99, +
50, +
'ACTIVO');
END;
13
______________________________________________________________________
Mediante una consulta a la tabla Producto, verifique el contenido de la tabla, debe existir un nuevo
registro cuyo código empieza con ‘P0011’. SELECT * FROM PRODUCTO;
PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_CONSULTAR_PRODUCTO.
Ejecute este código, para realizar una consulta del código de cliente: ‘P0011’.
BEGIN
SP_CONSULTAR_PRODUCTO ('P0011');
END;
Resultado:
Si ingresamos un código de Empleado que no existe, por ejemplo: ‘P0100’, el programa devolverá
“Código de Producto no Existe!”
14
______________________________________________________________________
PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_ACTUALIZAR_PRODUCTO.
usaremos al código de cliente ‘P0011’.
SELECT * FROM PRODUCTO;
NUEVO NOMBRE = ‘LAPTOP i5 15P 500GB HD H6565’

NUEVO PRECIO = 1800.99
NUEVO STOCK = 50
Ejecute este procedimiento, para realizar los cambios en código de cliente: ‘E0006’
BEGIN
SP_ACTUALIZAR_PRODUCTO ('P0011',' LAPTOP i5 15P 500GB HD H6565', +
1800.99, +
50, +
'ACTIVO');
END;
REGISTROS DE PRODUCTOS ANTES DE LA ACTUALIZACION:
15
______________________________________________________________________
REGISTROS DE EMPLEADOS DESPUES DE LA ACTUALIZACION:
FIN DE MANTENIMIENTO DE LA TABLA EMPLEADO.
16
______________________________________________________________________
FAVOR DE DESCARGAR EL ARCHIVO Semana13_PL_SQL_2.sql,

ESTE SCRIPT CONTIENE LA ESTRUCTURA DE TABLAS DEL MODELO RELACIONAL.
Procedimientos Almacenados y funciones, utilizando el lenguaje PL/SQL.
GENERADOR DE CODIGOS.
Para realizar este ejemplo, se debe contar con el siguiente modelo relacional.
Ejemplo 2:
Se pide crear los procedimientos y funciones necesarios para generar código automático en la tabla
PERSONAS, con los mantenimientos de datos incluidos.
.
PARA GENERAR CODIGOS AUTOMATICOS, SE DEBE CONTAR CON LA TABLA GENERADOR,
ESTA TABLA CONTENDRÁ EL ULTIMO VALOR GENERADO DE DICHA TABLA.:
ES DECIR, LA TABLA PERSONAS TIENE 22 REGISTROS

GRABADOS, EL ULTIMO CODIGO DE LA TABLA
PERSONAS ES EL P0022.
22 ES EL ULTIMO VALOR GENERADO Y ESTE SE

INCREMENTARÁ EN 1 MAS, SEGÚN LAS NUEVAS
INSERCIONES, TAL COMO SE OBSERVARA EN LAS
PRUEBAS.
17
______________________________________________________________________
ESTA FUNCIÓN PERMITE MULTIPLICAR UNA CADENA UNA CIERTA CANTIDAD DE VECES.
CREATE OR REPLACE FUNCTION REPLICATE (IN_STR VARCHAR2, IN_INT NUMBER)
RETURN VARCHAR2
IS
P_STR VARCHAR2(4000);
BEGIN
P_STR := '';
FOR I IN 1..IN_INT LOOP
P_STR := P_STR||IN_STR;
END LOOP;
RETURN P_STR;
END;
PROCEDIMIENTO ALMACENADO GENERADOR.

CREATE OR REPLACE PROCEDURE SP_GENERADOR
(Param IN Varchar2, CodGen OUT Varchar2)
IS
CEROS NUMBER; ULT NUMBER;
BEGIN
SELECT ULTIMO INTO ULT FROM GENERADOR WHERE PARAMETRO=Param;
CEROS:= 4 - LENGTH(TO_CHAR(ULT+1));
CodGen:=SUBSTR(Param,1,1) || REPLICATE('0',CEROS) || TO_CHAR(ULT+1);
UPDATE GENERADOR SET ULTIMO=ULT+1 WHERE PARAMETRO=Param;
COMMIT;
END;
PROCEDIMIENTO ALMACENADO SP_MANTENIMIENTO_PERSONAS

CREATE OR REPLACE PROCEDURE SP_MANTENIMIENTO_PERSONAS
(CONDICION IN VARCHAR2, IDP IN OUT VARCHAR2,
IDC IN VARCHAR2, IDPA IN INT,
NOM IN VARCHAR2, DOC IN VARCHAR2, FN IN DATE,
EC IN VARCHAR2, SEX IN VARCHAR2, DIR IN VARCHAR2,
EST IN VARCHAR2)
IS
XCOD VARCHAR2 (10);
BEGIN
IF CONDICION='I' THEN
SP_GENERADOR ('PERSONAS', XCOD);
IDP:=XCOD;
INSERT INTO PERSONAS VALUES (IDP, IDC, IDPA, NOM,
DOC, FN, EC, SEX, DIR, EST);
COMMIT;
END IF;
IF CONDICION='U' THEN
UPDATE PERSONAS SET IDCONT=IDC,
IDPAIS=IDPA, NOMBRE=NOM, DNI=DOC,
FNAC=FN, ECIVIL=EC, SEXO=SEX, DIR=DIR,
ESTADO=EST WHERE IDPER=IDP;
COMMIT;
END IF;
END;
18
______________________________________________________________________
REALIZANDO UNA PRUEBA DE INSERCIÓN DE 5 REGISTROS CON CODIGO DE PERSONA

AUTOMATICO, EJECUTE ESTE CODIGO Y OBSERVE LOS RESULTADOS.
DECLARE
CODIGO VARCHAR(5):='';
BEGIN
SP_MANTENIMIENTO_PERSONAS ('I', CODIGO,'C0001',1,
'JUAN PEREZ ALCALDE','05487898',
'15/05/1985','SOLTERO','MASCULINO',
'AV. PERU 1324 SMP','ACTIVO');
'ESMERALDA CHACON SANCHEZ','01587888',
'25/02/1980','SOLTERA','FEMENINO',
'AV. LOS OLIVOS 2345','ACTIVO');
'PEREZ SALAS ROGELIO','12457832',
'AV. PERU 1234 SAN MARTIN DE PORRES','ACTIVO');
'STUART ROLL ROCKY','87654321',
'AV. LOS ANGELES 2323','ACTIVO');
'SMITH ROGERS CLARA','05458784',
'03/05/1988','CASADO','FEMENINO',
'AV. LOS SPRINTS 123 NEW COLORADO','ACTIVO');
END;
REALICE UNA CONSULTA EN LA TABLA PERSONAS: VERA QUE HAY 5 NUEVAS FILAS CON
LOS CODIGOS: ‘P0023’, ‘P0024’, ‘P0025’, ‘P0026’ y ‘P0027’. AUTOGENERADOS
ANTES DE LA INSERCION, LA FILA PERSONAS MARCABA 22:
DESPUES DE LA INSERCION, VERIFIQUE LA TABLA GENERADOR, MEDIANTE UNA

CONSULTA. LA FILA PERSONAS MARCA 27, INDICANDO QUE SE REALIZARON 5 NUEVAS
INSERCIONES.
19
______________________________________________________________________
REALIZANDO UNA PRUEBA DE ACTUALIZACION, EN ESTE CASO USAREMOS EL CODIGO

P0001, OBSERVE SUS DATOS, SE LES CONSIDERA DATOS ANTIGUOS.
EJECUTE EL SIGUIENTE CODIGO, OBSERVE LOS NUEVOS DATOS.

DECLARE
CODIGO VARCHAR2 (5):='P0001';
BEGIN
SP_MANTENIMIENTO_PERSONAS ('U', CODIGO,'C0001', 1,
'EDUARDO ROJAS CLEMENTE',
'00000000','01/01/1970','CASADO','MASCULINO',
'JR. HUASCAR 123 LIMA','ACTIVO');
END;
RESULTADO DE LA ACTUALIZACION.
CONCLUSIONES Y RECOMENDACIONES DE LA EXPERIENCIA
La codificación PL/SQL de Oracle es tan amplia, que ofrece a los estudiantes una alta gama de
alternativas para la resolución de problemas en procedimientos y funciones, no difíciles de entender,
con la prioridad de manejar los datos con suma responsabilidad por el programador Oracle.
20
______________________________________________________________________

Programación de Base de Datos - IL4 - S13

Cargado por

Copyright:

Formatos disponibles

Programación de Base de Datos - IL4 - S13

Cargado por

Información del documento

Título original

Derechos de autor

Formatos disponibles

Compartir este documento

Compartir o incrustar documentos

Opciones para compartir

¿Le pareció útil este documento?

¿Este contenido es inapropiado?

Copyright:

Formatos disponibles

Programación de Base de Datos - IL4 - S13

Cargado por

Copyright:

Formatos disponibles

Guía de Nombre Nombre

laboratorio de carrera Del curso

Programación en Base de Datos

Introducción y Definición de Oracle

“Es un sistema de gestión de base de datos de tipo objeto-relacional (ORDBMS, por el

13.1. IMPLEMENTANDO PROCEDIMIENTOS ALMACENADOS EN MANTENIMIENTO DE

 El uso de OR REPLACE permite sobre escribir un procedimiento existente. Si se omite, y el

 La sintaxis es muy parecida a la de un bloque anónimo, salvo porque se reemplaza la sección

LOS PARÁMETROS QUE PUEDE RECIBIR UN STORED PROCEDURE PUEDEN SER DE 3

Para estos ejemplos contamos con el siguiente modelo relacional.

LA TABLA CLIENTE CUENTA SON 5 REGISTROS.

LA TABLA EMPLEADO CUENTA CON 5 REGISTROS:

LA TABLA PRODUCTO CUENTA CON 10 REGISTROS:

13.2.- PROCEDIMIENTO ALMACENADO DE INSERCIÓN DE DATOS EN PL/SQL.

Elaborar procedimientos almacenados que ofrezca mantenimiento de datos (INSERCION,

Respetando la cantidad de registros empezaremos con los mantenimientos.

PROCEDIMIENTOS ALMACENADOS NECESARIOS PARA EL MANTENIMIENTO DE CLIENTES.

CREATE OR REPLACE PROCEDURE SP_ACTUALIZAR_CLIENTE

PRUEBA DE LOS PROCEDIMIENTOS ALMACENADOS DE CLIENTES.

PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_CONSULTAR_CLIENTE.

PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_ACTUALIZAR_CLIENTE.

Realizar la actualización con los nuevos datos.

NUEVO NOMBRE = ‘OLIVARES CARPIO ALEXA’

REGISTROS DE CLIENTES ANTES DE LA ACTUALIZACION:

REGISTROS DE CLIENTES DESPUES DE LA ACTUALIZACION:

FIN DE MANTENIMIENTO DE LA TABLA CLIENTE.

13.3.- PROCEDIMIENTO ALMACENADO DE ELIMINACIÓN DE DATOS EN PL/SQL.

PROCEDIMIENTO ALMACENADOS NECESARIOS PARA EL MANTENIMIENTO DE

IF CONDICION ='CAMBIOS REALIZADOS CON EXITO' THEN

PRUEBA DE LOS PROCEDIMIENTOS ALMACENADOS DE EMPLEADOS.

PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_CONSULTAR_EMPLEADO.

PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_ACTUALIZAR_EMPLEADO.

SELECT * FROM EMPLEADO;

Realizar la actualización con los nuevos datos.

NUEVO NOMBRE = ‘COLMENARES LOPEZ ELENA’

REGISTROS DE EMPLEADOS ANTES DE LA ACTUALIZACION:

REGISTROS DE EMPLEADOS DESPUES DE LA ACTUALIZACION:

FIN DE MANTENIMIENTO DE LA TABLA EMPLEADO.

13.4.- PROCEDIMIENTO ALMACENADO DE ACTUALIZACIÓN DE DATOS EN PL/SQL

PROCEDIMIENTOS ALMACENADOS NECESARIOS PARA EL MANTENIMIENTO DE

PRUEBA DE LOS PROCEDIMIENTOS ALMACENADOS DE PRODUCTOS.

PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_CONSULTAR_PRODUCTO.

PRUEBA DEL PROCEDIMIENTO ALMACENADO SP_ACTUALIZAR_PRODUCTO.

SELECT * FROM PRODUCTO;

Realizar la actualización con los nuevos datos.

NUEVO NOMBRE = ‘LAPTOP i5 15P 500GB HD H6565’

REGISTROS DE PRODUCTOS ANTES DE LA ACTUALIZACION:

REGISTROS DE EMPLEADOS DESPUES DE LA ACTUALIZACION:

FIN DE MANTENIMIENTO DE LA TABLA EMPLEADO.

FAVOR DE DESCARGAR EL ARCHIVO Semana13_PL_SQL_2.sql,

Procedimientos Almacenados y funciones, utilizando el lenguaje PL/SQL.

ES DECIR, LA TABLA PERSONAS TIENE 22 REGISTROS

22 ES EL ULTIMO VALOR GENERADO Y ESTE SE

PROCEDIMIENTO ALMACENADO GENERADOR.

PROCEDIMIENTO ALMACENADO SP_MANTENIMIENTO_PERSONAS

REALIZANDO UNA PRUEBA DE INSERCIÓN DE 5 REGISTROS CON CODIGO DE PERSONA

ANTES DE LA INSERCION, LA FILA PERSONAS MARCABA 22:

DESPUES DE LA INSERCION, VERIFIQUE LA TABLA GENERADOR, MEDIANTE UNA

REALIZANDO UNA PRUEBA DE ACTUALIZACION, EN ESTE CASO USAREMOS EL CODIGO