PROCEDIMIENTOS
Un procedimiento es un subprograma que ejecuta una acción específica y que no devuelve ningún valor por sí mismo.
Permiten almacenar instrucciones para ser utilizadas desde cualquier bloque sin la necesidad de declararlo por lo que se puede utilizar en otros procedimientos.
Para poder crear un procedimiento se hace uso de la palabra reservada CREATE y ejecutar el código de manera en la que se ejecuta un bloque PL/SQL.
►Las partes de un procedimiento son:
- Nombre - Parámetros (opcional) - Bloque de código
♦TIENE LA SIGUIENTE SINTAXIS:
CREATE {OR REPLACE} PROCEDURE nombre_proc( param1 [IN | OUT | IN OUT] tipo,... )
IS
-- Declaración de variables locales
BEGIN
-- Instrucciones de ejecución
[EXCEPTION]
-- Instrucciones de excepción
END;
∆EXPLICACIÓN DE LA SINTAXIS:
• CREATE: crear
• OR REPLACE: usado para volver a crear la función en caso de ya exista sin tener que eliminarla
• PROCEDURE: indicación de que lo que se hace es un procedimiento
• Nombre_proc: nombre que le queremos dar a nuestro procedimiento
• Parámetro/argumento: cada argumento se asocia a un modo y tipo de datos. Un procedimiento puede tener varios argumentos separados por comas.
o IN: envían valores a procedimientos almacenados por lo que su valor no puede ser reemplazado
o OUT: obtienen valores de los procedimientos, es similar a un retorno de funciones
o IN OUT: pueden enviar y obtener valores en los procedimientos almacenados.
o Si un parámetro no se define entonces es por defecto IN
• NOCOPY: El parámetro IN siempre se pasa como NOCOPY.
• BEGIN: se establecen las instrucciones de la ejecución
• EXCEPCIÓN: en caso de no encontrase un dato nos lanza un mensaje de que no se encuentra
• END: se termina de configurar el procedimiento
Una vez creado el procedimiento, se compila y se almacena en la BD de forma compilada para, posteriormente, ser invocado desde cualquier bloque PL/SQL.
►PASOS PARA LA CREACIÓN DE PROCEDIMIENTOS
1) PASO 1: Crear el procedimiento
a. Se usa la sintaxis anterior para delimitar nombre de procedimiento, parámetros, instrucciones
2) PASO 2: EJECUTAR EL PROCEDIMEINTO. Existen dos formas de pasar argumentos a un procedimiento almacenado al hacer la ejecución:
a. Notación posicional: se pasan los valores de los parámetros en el mismo orden en que el procedimiento los define (parámetro1, parámetro2)
b. Notación nominal: se pasan los valores en cualquier orden nombrando explícitamente el parámetro
3) Eliminar un procedimiento: se usa la siguiente sintaxis
DROP PROCEDURE Nombre_procedimiento
►EJEMPLO DE PROCEDIMIENTO
Crear una tabla de actualización de datos, donde se incluya un código de cuenta, el nombre de la persona que realiza el pago, el total del pago y la fecha en que se realiza el pago. Una vez creada inserta dos registros y luego realiza un procedimiento para actualizar el pago del individuo con código_cuenta = 231 a 2630.
1) Creamos la tabla con los cuatro atributos incluyendo el pago:
CREATE TABLE actualizando_pagos (
codigo_cuenta int,
nombre varchar2(20),
pago number,
fecha date
);
2) Insertamos dos registros, recordando agregar uno con código_cuenta = 231
insert into actualizando_pagos values (231, 'Santiago',12365, to_date('01-05-2022','DD/MM/YYYY'));
insert into actualizando_pagos values (232, 'Romina',1023, to_date('11-07-2022','DD/MM/YYYY'));
3) PROCEDIMIENTO PASO 1: Creamos el procedimiento actualizar_pagos con:
a. Dos parámetros
i. Cuenta: parámetro que manda a llamar el código_cuenta, es de tipo NUMBER
ii. Total: parámetro que establece el nuevo valor del pago, es de tipo NUMBER
b. BEGIN: se establecen las instrucciones
c. UPDATE: la instrucción es la actualización de la tabla actualizando_pagos
d. SET: determina los atributos y el parámetro que indicará su nuevo valor
e. WHERE: la condición del dato que se actualizará, aquí se toma el código_-cuenta establecido on el parámetro cuenta
f. END: se termina la configuración del procedimiento
create or replace PROCEDURE
actualizar_pagos (cuenta NUMBER, total NUMBER)
IS
BEGIN
UPDATE actualizando_pagos
SET
pago = total,
WHERE codigo_cuenta = cuenta;
END actualizar_pagos;
4) PROCEDIMEINTO PASO 2: EJECUTAMOS EL PROCEDIMIENTO
Pasamos los argumentos al procedimiento almacenado, para ello establecemos BEGIN para el inicio de las instrucciones,
actualizar_pagos con los parámetros (cuenta de donde se actualizará, nuevo valor del pago);
COMMIT indica que se finaliza la transacción; y se finaliza la instrucción con END
FORMA 1: NOTACIÓN POSICIONAL
BEGIN
actualizar_pagos(231,2630);
COMMIT;
END;
FORMA 2: NOTACIÓN NOMINAL
BEGIN
actualizar_pagos(cuenta => 231, total => 2630);
COMMIT;
END;
FUNCIONES
Las funciones son bloques de código que permiten la agrupación y organización de sentencias SQL que se ejecutan al invocar la función. Para ser creada se emplea la instrucción CREATE FUNCTION o CREATE OR REPLACE FUNCTION, esta última se usa para reemplazar una función que ya se encuentre hecha. Pueden ser hechas y reutilizarlas en diferentes códigos para ser llamadas cuando las necesitemos.
1PARTES DE MANERA GENERAL
- Cabecera
- Parámetros
- Sección de declaración de variables
- Bloque BEGIN/END para establecer las instrucciones
- Clausula RETURN
2SINTAXIS
CREATE {OR REPLACE} FUNCTION nombre_func(param1 tipo,param2 tipo,... ) RETURN tipo_dato IS
-- Declaración de variables locales
BEGIN
-- Instrucciones de ejecución
[EXCEPTION]
-- Instrucciones de excepción
END;
3EXPLICACIÓN DE LA SÍNTAXIS
• CREATE: se crea la función
• OR REPLACE: si la función ya existe se reemplaza
• Parámetro: se especifica el dato de entrada
o IN: El valor del parámetro no puede ser sobrescrito por la función
o OUT: El parámetro no puede ser referenciado por la función, pero si puede ser sobrescrito por esta
o IN OUT: el parámetro puede ser referenciado y sobrescrito por la función
• Tipo: se especifica el tipo de parámetro/ argumento
• RETURN: se retorna el valor que la función va a devolver. La cláusula es requerida debido a que las funciones devuelven datos, pueden ser de cualquier tipo excepto booleano.
• BEGIN: se establecen las instrucciones de ejecución
• EXCEPCIÓN: en caso de que el dato no exista aparecerá un mensaje de excepción
4PASOS PARA LA CREACIÓN DE FUNCIONES
1) PASO 1: Crear función
a. Se usa la sintaxis anterior para delimitar nombre de la función, parámetros, instrucciones y retorno de datos
2) PASO 2: UTILIZAR LA FUNCIÓN. Se manda a llamar a la función mediante una sentencia SQL o PL/SQL
3) PASO 3: ELIMINAR UNA FUNCIÓN. La función puede ser eliminada mediante la siguiente sintaxis
DROP FUNCTION Nombre_funcion;
►EJEMPLO DE FUNCION
Crear una función que tome los valores del nombre de cada producto dentro de una nueva variable, haciendo uso del código_p, posteriormente, realiza una consulta que muestre el nombre de los productos utilizados por la función.
1) Creamos la tabla con los cuatro atributos incluyendo el pago:
CREATE TABLE productosE (
codigo_p int,
nombreP varchar2(20),
grupo varchar2(20),
precio number
);
2) Insertamos los registros
insert into productosE values (250, 'Leche', 'Lacteos', 25.00);
insert into productosE values (251, 'Sabritas', 'Frituras', 15.00);
insert into productosE values (252, 'Papel higienico', 'Limpieza', 30.00);
insert into productosE values (253, 'Mangos', 'Frutas', 10.00);
insert into productosE values (254, 'Coladera', 'Cocina', 10.00);
3) PROCEDIMIENTO PASO 1: Creamos la función OBT_nombreproducto con:
a. Un parámetro
i. Bus_codigo: parámetro que obtendrá el valor del codigo_p en la tabla productosE
b. RETURN: se retornará un valor de tipo varchar2, es decir que será una cadena de caracteres
c. V_nombreProducto: variable que tomará el valor del dato que se buscará en la tabla productosE
d. BEGIN: se establecen las instrucciones
e. SELECT: se selecciona el atributo del nombre y se le asignará a la variable v_nombre Producto para así tomar los valores seleccionados
f. WHERE: la condición del dato que se actualizará, aquí se toma el código_p establecido con el parámetro bus_codigo
g. RETUNR V_nombreProducto: se retornan los valores que adquiere esta variable
f. END: se termina la configuración de la función
CREATE OR REPLACE FUNCTION OBT_nombreproducto (bus_codigo IN productosE.codigo_p%TYPE)
RETURN VARCHAR2 IS
V_nombreProducto productosE.nombreP%TYPE;
BEGIN
SELECT nombreP into V_nombreProducto from productosE
where codigo_p = bus_codigo;
RETURN V_nombreProducto;
END OBT_nombreproducto;
4) PROCEDIMEINTO PASO 2: UTILIZAR LA FUNCIÓN
Mandamos a llamar a la función, podemos hacerlo con ayuda de una consulta donde seleccionamos el dato que queremos mostrar, en este caso se selecciona
la función anterior y, dentro del paréntesis, se identifica el atributo que está utilizando la función para tomar los datos.
SELECT OBT_nombreproducto(codigo_p) as nombreP
FROM productosE;
TRIGGERS
Los Triggers son bloques de código PL/SQL que se asocian a una tabla y se ejecutan de manera automática al ejecutar una operación DML específica, como INSERT, DELETE, UPDATE. Es decir, son ejecutados automáticamente cuando se realizan operaciones sobre la tabla donde se estén ejecutando.
1USOS
- Proporcionan un sistema de gestión de la base de datos altamente personalizado
- Generan automáticamente valores derivados en columnas
- Evitan transacciones inválidas
- Exigen integridad referencian entre los nodos de la base de datos
- Obtiene réplicas síncronas de tablas
- Recopilan estadísticas de acceso de tablas
2SINTAXIS
CREATE {OR REPLACE} TRIGGER nombre_disp
[BEFORE|AFTER]
[DELETE|INSERT|UPDATE {OF columnas}] [ OR [DELETE|INSERT|UPDATE {OF columnas}]...]
ON tabla
[FOR EACH ROW [WHEN condicion disparo]]
[DECLARE]
-- Declaración de variables locales
BEGIN
-- Instrucciones de ejecución
[EXCEPTION]
-- Instrucciones de excepción
END;
3EXPLICACIÓN DE LA SINTAXIS
• CREATE: indica que se creará el elemento que está delante
• OR REPLACE: si el elemento, en este caso el trigger, ya existe este se reemplaza
• Se hace uso de cualquier orden de consulta, además de llamadas a funciones o procedimientos.
• DECLARE: se declaran las variables locales
• BEGIN: apartado donde se escriben las instrucciones que serán ejecutadas
• EXCEPTION: en caso de no existir algún dato se establece un mensaje de interrupción que lo indique y no marque error en la ejecución.
4REGLAS PARA PODER LLAMAR A FUNCIONES O PROCEDIMIENTOS
- No utilizar comandos DDL
- No se accede a las tablas con DELETE, INSERT, UPDATE
- No modificar llaves primarias ni llaves externas
- No llamar a procedimientos que utilicen sentencias de control de transacciones
5PREDICADOS CONDICIONALES
Utilizados en las sentencias del trigger, indican el tipo de operación o sentencia que ha disparado el trigger
• Inserting: Retorna true cuando el trigger ha sido disparado por un INSERT
• Deleting: Retorna true cuando el trigger ha sido disparado por un DELETE
• Updating: Retorna true cuando el trigger ha sido disparado por un UPDATE
• Updating (columna): Retorna true cuando el trigger ha sido disparado por un UPDATE y la columna ha sido modificada.
6TIPOS DE TRIGGERS
• Triggers de fila y Triggers de sentencia: permiten establecer el número de veces que la acción de disparo se va a ejecutar
o De fila (Row triggers): se especifica una vez por cada fila afectada por la sentencia de activación
o De sentencia (Statement Triggers): se especifica una vez por sentencia de activación
• Antes y después (BEFORE and AFTER Triggers): especifican cuando se da el disparo
o BEFORE Triggers: ejecutan la acción antes de que la sentencia de activación se ejecute
o AFTER Triggers: ejecutan la acción después de que la sentencia de activación se ejecute
• Triggers en LUGAR DE (INSTEAD OF TRIGGERS): proporcionan una manera de modificar vistas que no deben ser modificadas directamente a través de sentencias DML.
• Triggers de eventos del sistema y eventos de usuario (Trigger son System Events and User Events): utilizan triggers para publicar información sobre eventos de Base de Datos a los suscriptores.
7ELIMINAR O DESHABILITAR UN TRIGGER
La sentencia usada para eliminar un Trigger es la siguiente:
DROP TRIGGER nombreTrigger;
La sentencia para deshabilitar, habilitar o compilar un Trigger de Base de Datos es
ALTER TRIGGER
Ejemplo de Procedimientos
Lo que se realiza es crear un subprograma el cual va a ejecutar una acción en específico, pero sin devolver algún valor como se muestra en la figura lo primero de créate or replace procedure significa que se crea un procedimiento o volver a crear la función sin tener que eliminarla en caso de que ya exista posteriormente se declaran las variables locales en este caso v_number y v_varchar2, después siguen las instrucciones que va a realizar dicho programa.
Ahora para llamar a este procedimiento lo único que se hace es establecer la salida del servidor con set serveroutput on y las instrucciones en este caso la llamada del procedimiento.
Ejemplo de Funciones
Lo primero que se hace es escribir créate or replace function lo que hace referencia a crear una nueva función y si ya existe remplazarla después va el nombre posteriormente va un return seguida de tipo de dato que va a regresar, se declaran las variables si se ocupan en este caso no simplemente vamos a retornar un mensaje.
Para llamar a esta función lo único que se hace es un select con el nombre de la función y listo nos muestra la acción que realiza la función.
Ejemplo_Triggers
1Trigger de control de inserción de datos
En general el trigger o disparador es una serie de reglas predefinidas que se asocian con una tabla, sirven en ciertas operaciones con lo es en añadir, actualizar o eliminar registros. En este caso mostrare en primera estancia un trigger (before) que en general suele ejecutarse antes de la sentencia. Para este ejemplo utilizare dos 2 tablas (Libros y Control), una vez creada esta tabla ingresamos el trigger. Además del mensaje que aparecerá en el momento de la creación del trigger también podremos encontrarlo en nuestro explorador en la sección de Triggers
En pocas palabras lo que hace este trigger es insertar en la tabla control el usuario y la fecha en la que se inserto un dato en la tabla libros
*Antes de insertar datos
Libros
Control
Después de la inserción de datos
Como se puede observar al momento de ingresar un dato en la tabla de Libros se actualiza de forma automática la tabla de control, ingresando de forma automáticamente el usuario y la fecha en la que se ingresa el dato
*Trigger de borrado de datos
En este caso este trigger al momento de borrar un registro se disparará el evento provocando que se registre el usuario que borre algún registro al igual que la fecha
Y así tendremos además del cambio en la tabla también notaremos en control como el registro del usuario y de la fecha del cambio
En este caso el trigger se enfoco en eliminar los registros del curso 5
