procedimientos almacenados y...

PROCEDIMIENTOS ALMACENADOS Y TRIGGERS

Bases de DatosIngeniería de Sistemas y Computación

Universidad Nacional de Colombia2007

Bases de Datos - 2007

Procedimientos almacenados

Un procedimiento almacenado es un conjunto de sentencias SQL y de control de flujoUn procedimiento almacenado es una colección de instrucciones guardadas en la Base de DatosLa ventaja de un procedimiento almacenado es que al ser ejecutado, éste se ejecuta directamente en el motor de bases de datos, el cual usualmente corre en un servidor separado.


Procedimientos almacenados

Beneficios de los procedimientos almacenados:Simplifican la ejecución de tareas repetitivasCorren más rápido que las mismas instrucciones ejecutadas en

forma interactivaReducen el tráfico a través de la redPueden capturar errores antes que ellos puedan entrar a la

base de datosEstablece consistencia porque ejecuta las tareas de la misma

formaPermite el desarrollo modular de aplicacionesAyuda a proveer seguridadPuede forzar reglas y defaults complejos de los negocios


Tipos de procedimientos almacenados

Procedimientos almacenados definidos por el usuarioSon procedimientos definidos por el usuario que se deben llamar explícitamente

TriggersSon procedimientos definidos por el usuario que se ejecutan automáticamente cuando se modifica un dato en una tabla

Procedimientos del sistemaProcedimientos suministrados por el sistema

Procedimientos extendidosProcedimientos que hacen llamadas al sistema operativo y ejecutan tareas a ese nivel


Interactive Execution


Creación de un procedimiento almacenado


Ejecución de un procedimiento almacenado


Ventajas en el rendimiento

Un procedimiento almacenado se ejecuta más rápido que un batch porque:El procedimiento almacenado ya ha sido analizadoYa se han resuelto las referencias a los objetos referenciados en el procedimiento almacenadoNo se necesita construir el árbol de búsqueda, él usa el que se hace en el momento de compilarloNo se necesita crear un plan de búsqueda, porque ya el procedimiento tiene uno


Crear y borrar procedimientos almacenados

Sintaxis simplificada para create:create proc procedure_nameas

statementsreturnEjemplo:create proc proc_update_titlesas

update titlesset price = price * $0.95where total_sales < 3000

return

Sintaxis simplificada para drop:drop proc procedure_nameEjemplo:drop proc proc_update_titles


Crear procedimientos almacenados

Sintaxis para Sybase:create procedure [owner.]procedure_name[;number] [[ (]@parameter_name datatype[= default] [output]

[, @parameter_name datatype[= default] [output]]...[)]][with recompile] as sql_statements



Sintaxis para SQL Server:create procedure procedure_name

(parameter_name datatype default_value[, parameter_name datatype default_value...] )

asstatements

return



Sintaxis para Oracle:CREATE [OR REPLACE]

PROCEDURE <procedure_name> [(<param1> [IN|OUT|IN OUT] <type>,

<param2> [IN|OUT|IN OUT] <type>, ...)] IS-- Declaracion de variables localesBEGIN-- Sentencias

[EXCEPTION]-- Sentencias control de excepcion

END [<procedure_name>];


Ejecutar procedimientos almacenados

Sintaxis simplificada:[exec | execute] procedure_nameEjemplo:execute proc_update_titles


Variables

Los procedimientos almacenados pueden crear y usar variables locales

Las variables sólo existen mientras exista el procedimientoLas variables no las puede usar otro proceso


Sentencias válidas e inválidas

Un procedimiento almacenado puede:Seleccionar y modificar datosCrear tablas temporales y permanentesLlamar otros procedimientos almacenadosReferenciar objetos de bases de datos

Un procedimiento almacenado no puede ejecutar:use databasecreate viewcreate defaultcreate rulecreate procedurecreate trigger


Procedimientos almacenados y permisos

Para permitir que otros usen un procedimiento almacenado, el propietario debe dar los respectivos permisosSintaxis simplificada:grant executeon procedure_nameto user_listEjemplo:grant executeon proc_update_titlesto icastaneda, grupo5, aortizs



Crear un procedimiento almacenado sencillo:create proc proc_helloas

print "Hello, <your_name>"return

Ver el código fuente de un procedimiento:sp_helptext proc_hello

Ejecutar el procedimiento:exec proc_hello

Borrar los objetos de bases de datos:drop proc proc_hello


Parámetros de entrada

An input Parámetro is a variable local to a procedimiento almacenado that can receive a value from the exec procedure Sentencia


Definir parámetros de entrada

Sintaxis simplificada:create procedure procedure_name

(parameter_name datatype default_value[, parameter_name datatype default_value...] )

asstatements

return


Pasar parámetros

Dos métodos para pasar valores a parámetros:Paso de parámetros por posiciónPaso de parámetros por nombre


Paso de parámetros por posición

Sintaxis para paso por posición:[exec | execute] procedure_name value [, value...]Ejemplo:exec proc_author_info "Ringer“,"Albert"

au_lname au_fname title-------- -------- -----Ringer Albert Is Anger the Enemy?Ringer Albert Life Without Fear

Los parámetros se deben pasar en el mismo orden en que ellos aparecen en la sentencia create procedureComo este método es más propenso a errores, se aconseja el paso por nombre


Paso de parámetros por nombre

Sintaxis para paso por nombre:[exec | execute] procedure procedure_nameparameter_name = value [, parameter_name = value ]Ejemplo:exec proc_author_info@lname = "Ringer", @fname = "Albert"

au_lname au_fname title-------- -------- -----Ringer Albert Is Anger the Enemy?Ringer Albert Life Without Fear

Los nombres de los parámetros en la sentencia exec deben concordar con los nombres de los parámetros usados en la sentencia create procedureLos parámetros pueden pasar en cualquier orden


Valores por default

Se puede asignar un valor por default a un parámetro cuando él no se indica en la sentencia execEjemplo:create proc proc_state_authors

(@state char(2) = "CA")as

select au_lname, au_fname, statefrom authorswhere state = @state

return

exec proc_state_authors -- No state value passed

au_lname au_fname state-------- -------- -----White Johnson CAGreen Marjorie CA...


Parámetros de entrada: errores comunes

Los valores que se pasan no tienen el mismo tipo de datos que los parámetros definidosEn la misma sentencia, se pasa un parámetro por posición después de haber pasado un parámetro por nombre

Aunque no es recomendado, es posible mezclar los dos métodos para pasar valores, sin embargo, después de pasar un valor a un parámetro por nombre, todos los restantes de deben pasar por nombre

Olvido de uno o más parámetrosEl olvido de uno o más valores para los parámetros, hace que se

usen los valores por defaultLos valores para los parámetros se pasan en un orden errado



Crear un procedimiento almacenado que tenga un parámetro de entrada:create proc proc_hello_param (@name varchar(30))as

print "Hello %1!", @namereturn

Ejecutar el procedimiento con y sin un valor para el parámetro de entrada. Una sentencia fallará:exec proc_hello_paramexec proc_hello_param "<yourname>"

Crear un procedimiento almacenado que tiene un valor por default para un parámetro de entrada:create proc proc_hello_def

(@name varchar(30) = "whoever you are")as

print "Hello %1!", @namereturn



• Ejecutar el procedimiento con y sin un valor para el parámetro de entrada:exec proc_hello_defexec proc_hello_def "<yourname>"

• ¿Cuál procedimiento almacenado parece ser más amigable?

• Borrar los objetos de bases de datos creados:drop proc proc_hello_param, proc_hello_def


Retorno de valores

A return Parámetro is a variable local to a procedimiento almacenado that can send a value to the exec procedure Sentencia


Crear parámetros que retornan valores

Sintaxis simplificada:create procedure procedure_name

(parameter_name datatype output[, parameter_name datatype output...] )

asstatements

return


Usar parámetros que retornan valores

Sintaxis simplificada:[exec | execute] procedure_name variable output

Los valores que retornan los parámetros se pasan automáticamente al conjunto respuestaEl retorno de valores se pueden pasar por nombre o por posición

Se recomienda el paso por nombre


Legibilidad

Para hacer un código más legible:Utilizar comentariosUtilizar indentaciónUsar espacios en blanco y dejar el código organizado en columnasDeclarar e iniciar las variables en un bloque

Establecer un conjunto de buenas prácticas para legibilidad


Chequeo de valores que van a ser parámetros de entrada

El servidor sólo chequea que los valores y sus respectivos parámetros concuerden en el tipo de datosLos procedimientos almacenados deben verificar que los valores pasados se encuentren dentro del dominio establecido


Mensajes de error por omisión de valores para los parámetros

Si un procedimiento almacenado requiere valores para los parámetros, el usuario debe incluir un tratamiento de esos errores y un manejo de mensajes de errorEjemplo:create proc proc_cutoff

(@title_id char(6) = NULL,@max_price money = NULL)

asif @title_id is NULL or @max_price is NULLbegin

raiserror 20001"Execution for this procedure is:exec proc_cutoff title_id, max_price"return

end...

return


Retorno de valores de estado

A return status is a value that indicates whether ornot the procedure successfully completed



Values Meaning

Greater than 0 No predefined meaning; available for user-detected errors

0 Successful completetion

-1 through –99 System-detected error

Less than –99 No predefined meaning; available for user-detected erros



Sintaxis simplificada:create proc procedure_nameas

statementsreturn [return_status]Ejemplo:create proc proc_datacheckasif (select max(total_sales) from titles) < 3000

beginprint "All the books have sold

less than 3000."return 100

endelse

...return 0


Captura del valor del estado de retorno

Los valores de retorno se deben capturar en variablesSintaxis simplificada:[exec | execute] variable = procedure_nameparameter_list

Ejemplo:declare @status intexec @status = proc_datacheckif @status = 100

exec proc_max_under_3000else

print "proc_datacheck was successful"


Límite de anidamiento para procedimientos

Los procedimientos almacenados pueden llamar otros procedimientos almacenados

El máximo nivel de anidamiento es 16La variable @@nestlevel contiene el nivel de anidamiento

actualSi se excede el nivel máximo:

Se abortan los procedimientos pendientesEl servidor retorna un error


Crear procedimientos con recompile

En un procedimiento, usar la opción with recompilepara forzar al servidor a crear un nuevo plan de búsqueda cada vez que se ejecute el procedimientoSintaxis simplificada:create proc procedure_namewith recompileas

statementsreturn


Ejecución de procedimientos with recompile

Cuando se ejecute un procedimiento almacenado, usar la opción with recompile para forzar al servidor a crear un nuevo plan de búsqueda para esa ejecución del procedimientoEsta opción se puede usar cuando se ejecuta cualquier procedimiento almacenadoSintaxis simplificada:[exec | execute] procedure_name withrecompileEjemplo:execute proc_update_titles with recompile


Trigger

Un trigger es un procedimiento almacenado asociado con una tabla, el cual se ejecuta automáticamente cuando se modifica un dato de esa tabla

Can It Be Explicitly Called?

Can It Be Executed Automatically?

Can It Use Parameters?

User-Defined Stored Procedure

Yes No Yes

Trigger No Yes No


Aplicaciones Típicas de triggers

Hacer modificaciones en cascada sobre tablas relacionadasDeshacer cambios que violan la integridad de los datosForzar restricciones que son muy complejas para reglas y restriccionesMantener datos duplicadosMantener columnas con datos derivadosHacer ajustes de registros


Definición de un trigger

Un trigger se define asociado con una tabla para una o más sentencias de manipulación de datos

Un trigger se puede definir para insert, update, o delete o cualquier combinación de ellos


Activación de un trigger

Cuando se modifica un dato en una tabla que tiene declarado un trigger para esa sentencia, el trigger se “dispara”

El trigger se dispara una vez, independientemente del número de filas afectadas

El trigger se dispara aunque no hayan filas afectadas


Triggers and transacciones

Un trigger es parte de la transacción que causa el disparoEl trigger puede deshacer:

Así mismo solamenteAsí mismo y la sentencia que causa el disparoLa transacción total


Reglas para triggers

Los triggers pueden:Declarar variables localesInvocar procedimientos almacenados

Los triggers no pueden:Llamarse directamenteUsar parámetrosDefinirse sobre tablas temporales o vistasCrear objetos permanentes de base de datos

Las operaciones con registro mínimo (como selectinto) no disparan los triggers


Crear triggers

Sintaxis simplificada:create trigger trigger_nameon table_namefor {insert | update | delete} [, {insert | update | delete} ...]as

sql_statements


Crear triggers - Oracle

Sintaxis simplificada:CREATE [OR REPLACE] TRIGGER <nombre_trigger>{BEFORE|AFTER} {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN][OR {DELETE|INSERT|UPDATE [OF col1, col2, ..., colN]...]}ON <nombre_tabla>[FOR EACH ROW [WHEN (<condición>)]]DECLARE-- variables locales

BEGIN-- Sentencias

[EXCEPTION]-- Sentencias control de excepcion

END <nombre_trigger>;


Borrar Triggers

Sintaxis simplificada:drop trigger trigger_nameEjemplo:drop trigger trg_i_sales


Procedimientos del sistema para procedimientos almacenados

sp_depends {table_name | trigger_name}Cuando se da el nombre de tabla, lista todos

los objetos (incluyendo triggers) de la misma base de datos

Cuando se da el nombre de trigger, lista todas las tablas referencias

sp_help trigger_nameMuestra información del trigger

sp_helptext trigger_nameMuestra el código usado para crear el trigger

sp_rename old_trigger_name, new_trigger_nameCambia el nombre del trigger


Las tablas inserted y deleted

inserted y deleted son dos tablas que se crean automáticamente cada vez que se dispara un trigger

inserted almacena cualquier fila que se vaya a añadir a la tabladeleted almacena cualquier fila que se vaya a borrar de la tabla


:old :new en Triggers Oracle

Sentencia SQL OLD NEW

Insert No definido; todos los campos toman valor

NULL.

Valores que serán insertados cuando se

complete la orden.

Update Valores originales de la fila, antes de la actualización.

Nuevos valores que serán escritos cuando se complete la orden.

Delete Valores, antes del borrado de la fila.

No definidos; todos los campos toman el valor

NULL.


Borrados

A delete adds rows to the deleted table


Inserciones

An insert adds rows to the inserted table


Actualizaciones

An update adds rows to both tables


Reglas para las tablas inserted y deleted

Ambas tablas tienen las mismas columnas que la tabla asociada al triggerEl trigger puede consultar datos de las dos tablas

Otros procesos no pueden consultar datos de las dos tablas

El trigger no puede modificar datos en las dos tablasCada anidamiento de triggers tiene sus propias tablas inserted y deleted

Si un trigger modifica datos de su tabla asociada, esos cambios no se reflejan en las tablas inserted and deleted de ese trigger


Triggers y rollbacks

Tres tipos de rollbacks:Deshacer el triggerDeshacer el trigger y la sentencia que lo

disparóDeshacer toda la transacción


Deshacer un trigger

rollback trigger deshace el trigger y la sentencia que lo disparóSintaxis:rollback trigger [with raiserror error_number[error_statement] ]


Prácticas recomendadas al hacer Triggers

Las siguientes consideraciones se deben hacer al elaborar triggers:

@@rowcountif updatetriggers anidados triggers recursivos


if update

if update es una condición que le permite a un triggerchequear si ha habido un cambio en una determinada columnaSólo se puede usar en triggersUsualmente se usa para chequear si el valor de una llave primaria ha cambiadoSintaxis simplificada:if update (column_name) [ {and | or} update(column_name)]...


Triggers anidados

Un trigger anidado es un trigger que se dispara en respuesta a una modificación hecha en un trigger

Nivel máximo de anidamiento: 16Tanto los procedimientos almacenados como los triggers cuentan en la determinación del nivel máximo@@nestlevel retorna el nivel de anidamiento


Triggers recursivos

Un trigger recursivo es aquel que se dispara cuando modifica su propia tabla

Por default, un trigger que modifica su propia tabla no causa un disparo recursivo del trigger


Métodos para integridad de datos

Dos métodos para implementar integridad de datos

Domain Integrity

Entity Integrity Referential Integrity

Constraints

Check constraints

Primary key constraints, unique constraints

References constraints

Database Objects

Rules Indexes Triggers


Actualización de valores llave

*Valores de llaves primarias se pueden actualizar o borrar si no están referencidos en llaves foráneas

Solamente en triggers es posible borrar o actualizar una llave primariaSólo en triggers es posible hacer cambios en cascada

Acción deseada Restricciones Triggers

Insertarar valor de llave primaria Permitido Permitido

Insertar valor de llave foránea Permitido Permitido

Actualizar valor de llave primaria No permitido* Permitido

Actualizar valor de llave foránea Permitido Permitido

Borrar valor de llave primaria No permitido* Permitido

Borrar valor de llave foránea Permitido Permitido


Restricciones vs triggers

Ventajas de las restricciones:Las restricciones (y reglas) son más rápidas que los

triggersLas restricciones no requieren codificación adicionalEs mejor para chequear datos antes de ingresarlos a la

base de datosVentajas de los triggers:

Muy flexibleLos triggers pueden hacer cualquier cosa que se pueda codificar

Mejor para las reglas complejas del negocio que no se pueden expresar como restricciones referenciales tales como actualizaciones o borrados en cascada


Preguntas

Gracias por su Atención

procedimientos almacenados y...

Documents