1 / 17

Procedimientos Almacenados Funciones Agregadas

Procedimientos Almacenados Funciones Agregadas. PROCEDIMIENTOS ALMACENADOS. DEFINICIÓN.

toki
Télécharger la présentation

Procedimientos Almacenados Funciones Agregadas

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Procedimientos Almacenados Funciones Agregadas

  2. PROCEDIMIENTOS ALMACENADOS

  3. DEFINICIÓN • Un procedimiento almacenado es un conjunto de instrucciones en PL/SQL, que pueden ser llamados usando el nombre que se le haya asignado. Estos pueden hacer referencia a tablas, vistas, a funciones definidas por el usuario, a otros procedimientos almacenados. • PL/SQL (ProceduralLanguage/StructuredQueryLanguage) es lenguaje que permite definir procedimientos utilizando sentencias SQL. Es el lenguaje de programación estructurado de las bases de datos Oracle. • A un procedimiento almacenado lo podemos: • Crear • Borrar • Ejecutar • Reemplazar

  4. SINTAXIS PARA CREACION/REEMPLAZO CREATE [OR REPLACE] PROCEDURE nombre([parámetros])IS[zona de declaraciones]BEGIN[zona de ejecución]EXCEPTION[zona de excepciones]END nombre; • Parámetros: a la hora de crear un procedimiento almacenado de Oracle, podemos declarar parámetros de diferentes tipos (NUMBER, VARCHAR, etc) y estos pueden ser de entrada (IN), salida (OUT) o incluso de entrada y salida (INOUT). • IN: Se utiliza como un parámetro de entrada para el procedimiento almacenado. No se puede cambiar la información de este parámetro dentro del procedimiento. • OUT: Se utiliza como parámetro de salida en el procedimiento almacenado. Se puede cambiar la información de este parámetro dentro del procedimiento. • INOUT: Se utiliza como parámetro de entrada y de salida. Se puede cambiar la información de este parámetro dentro del procedimiento.

  5. Zona de declaraciones: utilizaremos esta sección del procedimiento para declarar las variables que necesitemos utilizar en el procedimiento. • Zona de ejecución: en esta sección va la lógica del procedimiento, es decir, la parte de la aplicación que se ejecutará. • Zona de excepciones: se utiliza para el tratamiento de excepciones del procedimiento. EJEMPLO DE LA CREACION CREATEPROCEDURE suma(Num1 IN NUMBER, Num2 IN NUMBER, resultado OUT NUMBER)ISBEGINresultado := Num1 + Num2;END;

  6. SINTAXIS PARA BORRAR DROP PROCEDURE nombre; Ejemplo: DROP PROCEDURE suma; SINTAXIS PARA EJECUTAR DECLAREvariables;BEGIN llamada a los procedimientos;END; Ejemplo: DECLAREresultado int;BEGIN //Procedimiento con parámetros suma(20,30,resultado); //Procedimiento sin parámetros execute suma();END;

  7. TRIGGERS Un disparador define una acción que la base de datos debe llevar a cabo cuando se produce algún suceso relacionado con la misma. Los disparadores (triggers) pueden utilizarse para completar la integridad referencial, también para imponer reglas de negocio complejas o para auditar cambios en los datos. La sintaxis es: 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 localesBEGIN  -- Sentencias[EXCEPTION]  -- Sentencias control de excepción  END<nombre_trigger>;

  8. INSERT, DELETE, UPDATE: Define qué tipo de orden DML provoca la activación del disparador. • BEFORE , AFTER: Define si el disparador se activa antes o después de que se ejecute la orden. • FOR EACH ROW: Los disparadores con nivel de fila se activan una vez por cada fila afectada por la orden que provocó el disparo. Los disparadores con nivel de orden se activan sólo una vez, antes o después de la orden. Los disparadores con nivel de fila se identifican por la cláusula FOR EACH ROW en la definición del disparador.. • La cláusula WHEN sólo es válida para los disparadores con nivel de fila. • También podemos definir: • Orden de ejecución de los triggers • Restricciones de los triggers • Utilización :OLD y :NEW • Utilización de predicados de los triggers

  9. EJEMPLO DE TRIGGER CREATE OR REPLACE TRIGGER tr_productos_01AFTER INSERT ON productos  FOR EACH ROWDECLARE  -- local variables BEGININSERT INTO precios_productos  (co_producto, precio, fx_actualizacion)VALUES  (:NEW.co_producto, 100, sysdate);END ; El trigger se ejecutará cuando sobre la tabla PRODUCTOS se ejecute una sentencia INSERT INSERT INTO productos(co_producto, descripción)VALUES('000100', 'producto 000100');

  10. ORDEN DE EJECUCIÓN DE LOS TRIGGERS • Una misma tabla puede tener varios triggers. En tal caso es necesario conocer el orden en el que se van a ejecutar. Los disparadores se activan al ejecutarse la sentencia SQL. Si existe, se ejecuta el disparador de tipo BEFORE (disparador previo) con nivel de orden. Para cada fila a la que afecte la orden: • Se ejecuta si existe, el disparador de tipo BEFORE con nivel de fila. • Se ejecuta la propia orden. • Se ejecuta si existe, el disparador de tipo AFTER (disparador posterior) con nivel de fila. RESTRICCIONES DE LOS TRIGGERS • El cuerpo de un trigger es un bloque PL/SQL. Cualquier orden que sea legal en un bloque PL/SQL, es legal en el cuerpo de un disparador, con las siguientes restricciones: • Un disparador no puede emitir ninguna orden de control de transacciones: COMMIT, ROLLBACK o SAVEPOINT. El disparador se activa como parte de la ejecución de la orden que provocó el disparo, y forma parte de la misma transacción que dicha orden. Cuando la orden que provoca el disparo es confirmada o cancelada, se confirma o cancela también el trabajo realizado por el disparador. • Por razones idénticas, ningún procedimiento o función llamado por el disparador puede emitir órdenes de control de transacciones. • El cuerpo del disparador no puede contener ninguna declaración de variables LONG o LONGRAW

  11. UTILIZACION DE :OLD Y :NEW • Dentro del ámbito de un trigger disponemos de las variables OLD y NEW . • Estas variables se utilizan del mismo modo que cualquier otra variable PL/SQL, con la salvedad de que no es necesario declararlas,son de tipo %ROWTYPE y contienen una copia del registro antes (OLD) y después(NEW) de la acción SQL (INSERT, UPDATE, DELTE) que ha ejecutado el trigger. • Utilizando esta variable podemos acceder a los datos que se están insertando, actualizando  o borrando.

  12. UTILIZACION DE PREDICADOS DE LOS TRIGGERS • Dentro de un disparador en el que se disparan distintos tipos de órdenes DML (INSERT, UPDATE y DELETE), hay tres funciones booleanas que pueden emplearse para determinar de qué operación se trata. Estos predicados son INSERTING, UPDATING y DELETING. • Tenemos entonces: • INSERTING: TRUE si la orden de disparo es INSERT; FALSE en otro caso. • UPDATING: TRUE si la orden de disparo es UPDATE; FALSE en otro caso.  • DELETING: TRUE si la orden de disparo es DELETE; FALSE en otro caso.

  13. FUNCIONES AGREGADAS

  14. DEFINICION • Las funciones agregadas proporcionan utilidades de cálculo sobre los datos de las tablas. • Algunas son: SYSDATE NVL DECODE TO_DATE TO_CHAR TO_NUMBER TRUNC LENGTH INSTR REPLACE SUBSTR UPPER LOWER ROWIDTOCHAR  RPAD LPAD RTRIM LTRIM TRIM MOD AVG COLLECT(column)CORRCORR_*COUNTCOVAR_POPCOVAR_SAMPCUME_DISTDENSE_RANKFIRSTGROUP_IDGROUPINGGROUPING_IDLASTMAXMEDIANMIN PERCENTILE_CONTPERCENTILE_DISCPERCENT_RANK RANKSTATS_BINOMIAL_TEST STATS_CROSSTAB STATS_F_TEST STATS_KS_TESTSTATS_MODE STATS_MW_TEST STATS_ONE_WAY_ANOVA STATS_T_TEST_* STATS_WSR_TEST STDDEV STDDEV_POP STDDEV_SAMP SUM VAR_POP VAR_SAMP VARIANCE

  15. DEFINICION DE ALGUNAS DE LAS FUNCIONES • AVG: Calcula la media de un conjunto de datos numéricos. SELECTAVG(salario) “Media" FROM scott.emp • COUNT(DISTINCT expresión): Devuelve el número filas que retorna la consulta. Si se incluye la clausula DISTINCT cuenta el número de filas con distinta expresión. No se cuentan las filas con expresión a nulo. Si ponemos count(*) entonces si que se cuentan filas con campos nulos. SELECTCOUNT(*) "Total"FROM scott.emp; • MAX([ DISTINCT | ALL ] expresión)[OVER (analytic_clause)]: Devuelve el máximo valor de expresión. SELECTMAX(salario) “Máximo"FROM scott.emp;

  16. MIN([ DISTINCT | ALL ] expresión)[ OVER (analytic_clause)]: Devuelve el mínimo valor de expresión. SELECTMIN(salario) “Mínimo"FROM scott.emp; • RANK: Calcula el ranking de un valor en un grupo de valores. Devuelve un valor numérico. Calcula el ranking de un hipotético empleado con salario 3000 respecto a la tabla de empleados. SELECT RANK(3000, .05)  WITHIN GROUP (ORDER BY salario, comm) “Ranking” FROM scott.emp; • SUM([ DISTINCT | ALL ] expresión)[ OVER (analytic_clause)]: Devuelve el sumatorio de los valores de expresión. SELECT SUM(salario) ”Total” FROM scott.emp;

  17. GRACIAS http://www.nosolounix.com/2010/12/crear-una-funcion-en-oracle.html http://www.devjoker.com/contenidos/catss/67/Funciones-integradas-de-PLSQL.aspx https://sites.google.com/site/josepando/home/funciones-sql/funciones-agregadas

More Related