Desencadenadores (triggers)

Publicado: julio 13, 2011 en Oracle

Triggers

Declaración de triggers

    Un trigger es un bloque PL/SQL asociado a una tabla, que se ejecuta como consecuencia de una determinada instrucción  SQL (una operación DML: INSERT, UPDATE o DELETE) sobre dicha tabla.

    La sintaxis para crear un trigger es la siguiente:


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 (<condicion>)]]

DECLARE
  -- variables locales
BEGIN
  -- Sentencias
[EXCEPTION]
  -- Sentencias control de excepcion
 
END <nombre_trigger>;

    El uso de OR REPLACE permite sobreescribir un trigger existente. Si se omite, y el trigger existe, se producirá, un error.

    Los triggers pueden definirse para las operaciones INSERT, UPDATE o DELETE, y pueden ejecutarse antes o después de la operación.   El modificador BEFORE AFTER indica que el trigger se ejecutará antes o despues de ejecutarse la sentencia SQL definida por DELETE INSERT  UPDATE. Si incluimos el modificador OF el trigger solo se ejecutará cuando la sentencia SQL afecte a los campos incluidos en la lista.

    El alcance de los disparadores puede ser la fila o de orden. El modificador FOR EACH ROW indica que el trigger se disparará cada vez que se realizan operaciones sobre una fila de la tabla. Si se acompaña del modificador WHEN, se establece una restricción; el trigger solo actuará, sobre las filas que satisfagan la restricción.

    La siguiente tabla resume los contenidos anteriores.

Valor

Descripción

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.

    Dentro del ambito 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 despues(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.

    El siguiente ejemplo muestra un trigger que inserta un registro en la tabla PRECIOS_PRODUCTOS cada vez que insertamos un nuevo registro en la tabla PRODUTOS:


CREATE OR REPLACE TRIGGER TR_PRODUCTOS_01
 
AFTER INSERT ON PRODUCTOS 
 
FOR EACH ROW
DECLARE
  -- local variables
BEGIN
  INSERT 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, DESCRIPCION)
VALUES
('000100','PRODUCTO 000100');

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.
  • Se ejecuta, si existe, el disparador de tipo AFTER con nivel de orden.

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 oSAVEPOINT. 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 LONG RAW


Utilización de :OLD y :NEW

    Dentro del ambito 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 despues(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.

    La siguiente tabla muestra los valores de OLD y NEW.

ACCION 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.

    Los registros OLD y NEW son sólo válidos dentro de los disparadores con nivel de fila.

    Podemos usar OLD y NEW como cualquier otra variable PL/SQL.

Utilización de predicados de los triggers: INSERTING, UPDATING y DELETING

    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.

    Su comportamiento es el siguiente:

Predicado

Comportamiento

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.


Funciones y paquetes

Publicado: julio 13, 2011 en Oracle

FUNCIONES PL/SQL

PL/SQL
Definición de Función

  • Una función es un bloque nombrado PL/SQL que devuelve un valor.
  • Una función puede estar almacenada en la B.D., como objeto de la B.D., para repetidas ejecuciones.
  • Una función puede ser llamada como parte de una expresión.

PL/SQL
Sintaxis Creación Funciones

PL/SQL
Creación de una Función

PL/SQL
Desde dónde llamarlas

  • Como columna de un SELECT.
  • Condiciones en cláusulas WHERE y HAVING.
  • Cláusulas ORDER BY y GROUP BY.
  • Cláusula VALUES de un comando INSERT.
  • Cláusula SET de un comando UPDATE.

PL/SQL
Restricciones en Funciones

  • No se permiten comandos INSERT, UPDATE o DELETE.
  • La función no puede llamar a otro subprograma que rompa una de las restricciones arriba indicadas.

PL/SQL
Borrado de Funciones

  • Para quitar una función de la parte del servidor, utilizando SQL*Plus, ejecutar el comando DROP FUNCTION.
  • Sintaxis:

 

 

Paquetes

 

Los paquetes en Oracle se utilizan para guardar subprogramas y otros objetos de la base de datos.

Un paquete consta de los siguientes elementos:

  • Especificación o cabecera: contiene las declaraciones públicas (es decir, accesibles desde cualquier parte de la aplicación) de sus programas, tipos, constantes, variables, cursores, excepciones, etc.
  • Cuerpo: contiene los detalles de implementación y declaraciones privadas, es decir, accesibles solamente desde los objetos del paquete.

La sintaxis de la cabecera es la siguiente:

   create [or replace] package nombre_paquete as
      <declaraciones públicas>
      <especificaciones de subprogramas>
   end nombre_paquete;

La sintaxis del cuerpo sería la siguiente:

   create [or replace] package body nombre_paquete as
      <declaraciones privadas>
      <código de subprogramas>
      [begin
         <instrucciones iniciales>]
   end nombre_paquete;

Como podéis observar la cabecera se compila independientemente del cuerpo. Os dejo un ejemplo de paquete para que lo veáis más claro.

/* Cabecera */
create or replace package busar_emple as
   TYPE t_reg_emple is RECORD
      (num_empleado emple.emp_no%TYPE,
      apellido emple.apellido%TYPE,
      salario emple.salario%TYPE,
      departamento emple.dept_no%TYPE);
   procedure ver_por_numero(v_cod emple.emp_no%TYPE);
   procedure ver_por_apellido(v_ape emple.apellido%TYPE);
   function datos (v_cod emple.emp_no%TYPE)
      return t_reg_emple;
end buscar_emple;
/* Cuerpo */
create or replace package body buscar_emple as
   vg_emple t_reg_emple;
   procedure ver_emple; /* procedimiento privado*/
   procedure ver_por_numero (v_cod emple.emp_no%TYPE)
   is
   begin
      select emp_no, apellido, salario, dept_no into vg_emple from emple where emp_no=v_cod;
      ver_emple;
   end ver_por_numero;
   procedure ver_por_apellido (v_ape emple.apellido%TYPE)
   is
   begin
      select emp_no,apellido,salario,dept_no into vg_emple from emple where apellido=v_apellido;
      ver_emple;
   end ver_por_apellido;
   function datos (v_cod emple.emp_no%TYPE)
      return t_reg_emple
   is
   begin
      select emp_no,apellido,salario,dept_no into vg_emple from emple where emp_no=v_cod;
   procedure ver_emple
      is
      begin
         DBMS_OUTPUT.PUT_LINE(vg_emple.num_empleado || ‘*’ || vg_emple.apellido || ‘*’ || vg_emple.salario || ‘*’|| vg_emple.departamento);
   end ver_emple;
end buscar_emple;

Como podéis ver este paquete nos permite buscar un empleado de tres formas distintas y visualizar sus datos.

Utilización de los objetos definidos en los paquetes

Podemos utilizar los objetos definidos en los paquetes básicamente de dos maneras distintas:

  • Desde el mismo paquete: esto quiere decir que cualquier objeto puede ser utilizado dentro del paquete por otro objeto declarado en el mismo.
    Para utilizar un objeto dentro del paquete tan sólo tendríamos que llamarlo. La llamada sería algo así: v_emple :=buscar_emple.datos(v_n_ape); (como veis no utilizamos el execute ya que nos encontramos dentro del paquete).
  • Desde fuera del paquete: Podemos utilizar los objetos de un paquete siempre y cuando haya sido declarado en la especificación del mismo. Para llamar a un objeto o procedimiento desde fuera del paquete utilizaríamos la siguiente notación: execute nombre_paquete.nombre_procedimiento(lista de parametros);
Declaración de cursores en paquetes

En los paquetes también podemos introducir cursores, para ello debemos declararlo en la cabecera del paquete indicando su nombre, los parámetros y tipo devuelto. Para que lo veáis más claro os dejo un ejemplo a continuación:

CREATE or REPLACE PACKAGE empleados AS
   …..
   CURSOR a RETURN empleado%ROWTYPE;
   …
   END empleados;
CREATE or REPLACE PACKAGE BODY empleados AS
   ….
   CURSOR a RETURN empleado%ROWTYPE
      SELECT * FROM empleado WHERE salario < 10000;
   ….
END empleados;

Los paquetes suministrados por Oracle son:

Standard : tiene la función to_char y abs
dbms_standard: tiene la función raise_aplication_error
dbms_output: con la función put_line
dbms_sql: que utiliza sql de forma dinámica.

NOTA: sql dinámico significa que el programa es capaz de ejecutar órdenes de definición y manipulación sobre objetos que sólo se conocen al ejecutar el paquete.
Un ejemplo de la utilización de dbms_sql es el siguiente:
   BEGIN
      ……
      id_cursor := DBMS_SQL.OPEN_CURSOR;
      DMBS_SQL.PARSE(id_cursor, instrucción,DMBS_SQL.V3);
      v_dum :=DMBS_SQL.EXECUTE(id_cursor);
      DMBS_SQL.CLOSE_CURSOR(id_cursor);
   ……

Lo que hacemos es abrir el cursor y nos devuelve el id del mismo para poder trabajar con él.
Después tenemos el DMBS_SQL.PARSE que analiza la instrucción que se va a ejecutar. Ya en la siguiente linea ejecutamos la sentencia y por último cerramos el cursor.

Procedimientos almacenados

Publicado: julio 13, 2011 en Oracle

PL/SQL

PL/SQL
¿Qué es PL/SQL?

  • Lenguaje de procesamiento procedimental.
  • Implementado por Oracle.
  • Dispone de estructuras de programación similares a las de la mayoría de los lenguajes de programación.
  • Objetivo: Interactuar con la B.D.

PL/SQL
Estructura Bloques Anónimos

  • [DECLARE]
    Define objetos PL/SQL que serán utilizados
    dentro del mismo bloque
  • BEGIN
    Sentencias Ejecutables
  • [EXCEPTION]
    Qué hacer si la acción ejecutada causa error
  • END;

PL/SQL
Estructura de Procedimiento

  • PROCEDURE nombre IS
    Sección Declarativa
  • BEGIN
    Sección Ejecutable
  • [EXCEPTION]
    Sección de Excepciones
  • END;

PL/SQL
Estructura de Función

  • FUNCTION nombre RETURN tipo_dato IS
    Sección Declarativa
  • BEGIN
    Sección Ejecutable
  • [EXCEPTION]
    Sección de Excepciones
  • END;

PL/SQL
Declaración de Variables

  • Reglas para nombres:
    • Dos variables pueden tener el mismo nombre, si están en bloques diferentes.
    • El nombre de la variable (identificador) no debería ser el mismo que el de una columna de una tabla utilizada en el bloque.

PL/SQL
Asignación e Inicialización de Variables

  • Asignación:

  • Inicialización:

  • Por defecto, todas las variables se inicializan a NULL.

PL/SQL
Tipos de Variables

  • ESCALARES
  • BOOLEANAS
  • COMPUESTAS
  • LOB
  • DE ENLACE (BIND)

PL/SQL
Variables Escalares

  • VARCHAR2 (longitud_máxima)
  • NUMBER [(precisión, escala)]
  • DATE
  • CHAR [(longitud_máxima)]
  • LONG
  • LONG RAW
  • BOOLEAN
  • BINARY_INTEGER

PL/SQL
Atributo %TYPE

  • Permite declarar una variable basada en:
    • Otras variables previamente declaradas
    • La definición de una columna de la base de datos
  • Preceder de %TYPE por:
    • La tabla y la columna de la base de datos
    • El nombre de la variable definida con anterioridad
  • Ejemplo:
    v_ename
    emp.ename%TYPE;

PL/SQL
Variables BOOLEANAS

  • A una variable Boolean sólo se le pueden asignar los valores: TRUE, FALSE o NULL.
  • Estas variables están conectadas por los operadores lógicos AND, OR y NOT.

PL/SQL
Tipos de Datos Compuestos

  • Tipos:
    • REGISTROS PL/SQL
    • TABLAS PL/SQL
  • Contienen componentes internos

PL/SQL
Creación de un Registro

  • Sintaxis:

  • Donde declaración_campo significa:

PL/SQL
Ejemplo de Registro PL/SQL

  • Declarar un registro para almacenar el número de empleado, nombre, trabajo y sueldo de un nuevo empleado:

  • Los componentes individuales del registro se referenciarán de forma cualificada; en este ejemplo:

PL/SQL
El Atributo %ROWTYPE

  • Define un registro con la estructura de la tabla o vista de la B.D.
  • Los campos del registro toman sus nombres y tipos de datos de las columnas de la vista o tabla.
  • Ejemplo:
    DECLARE registro_empleado emp%ROWTYPE;

PL/SQL
Ventajas de %ROWTYPE

  • El número y los tipos de datos de las columnas de la tabla pueden no ser conocidos.
  • Simplifica la programación al no tener que definir explícitamente los campos y tipos del registro.
  • Es útil para realizar recuperaciones de filas con la sentencia SELECT:

PL/SQL
Tablas PL/SQL

  • Cuentan con dos componentes:
    • TIPO DE DATOS DE CLAVE PRIMARIA BINARY_INTEGER
    • COLUMNA DE TIPO DE DATOS ESCALARES O DE REGISTRO.
  • Aumentan dinámicamente porque no tienen restricciones.
  • Se almacenan en memoria.

PL/SQL
Creación de Tablas PL/SQL

  • Sintaxis:

  • Ejemplo:

PL/SQL
Creación de Tablas PL/SQL

PL/SQL
Variables LOB

  • Permiten almacenar bloques de datos no estructurados, como pantallas de texto, imágenes gráficas, vídeo clips, y sonido, de hasta 4 Gb. de tamaño.

CLOB
Character Large Object. Se utiliza para almacenar bloques grandes de datos de caracteres.

BLOB
Binary Large Object. Se utiliza para almacenar objetos binarios grandes en la B.D.

BFILE
Binary File. Se utiliza para almacenar objetos binarios grandes en archivos del sistema operativo, fuera de la B.D.

NCLOB
National Language Character Large Object. Se utiliza para almacenar en la B.D. bloques grandes de datos NCHAR de un byte único o multi-bytes de ancho fijo.

PL/SQL
Variables de Enlace (Bind)

  • Son variables de SQL*Plus.
  • Las pueden referenciar bloques de PL/SQL mediante el uso del ampersand (&).
  • Ejemplo:

 

GENERALIDADES DE PL/SQL

PL/SQL
Generalidades de PL/SQL (I)

  • Comentarios en PL/SQL:
    • Anteponga a los comentarios de una sola línea dos guiones (–) a modo de prefijo.
    • Coloque comentarios de varias líneas entre /* y */.
  • Operadores de PL/SQL:

**, NOT
Exponenciación, negación lógica

+, –
Identidad, negación

*, /
Multiplicación, división

+, -, ||
Suma, resta, concatenación

=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN
Comparación

AND
Conjunción

OR
Inclusión

PL/SQL
Generalidades de PL/SQL (II)

  • Bloques Anidados y Ámbito de la Variable:

PL/SQL
Generalidades de PL/SQL (III)

  • Para hacer referencia a una variable de sustitución en PL/SQL debe anteponer a su nombre dos puntos (:) a modo de prefijo:

 

Generalidades de PL/SQL (IV)

  • Directrices de Programación para facilitar el mantenimiento del código:
    • Documente el código con comentarios.
    • Desarrolle una convención de mayúsculas/minúsculas para el código.
    • Desarrolle convenciones de nomenclatura para identificadores y otros objetos.
    • Sangre el código para facilitar la lectura.
    • Evite la ambigüedad entre variables locales, parámetros formales y nombres de columnas de las tablas de la B.D.

PL/SQL
Generalidades de PL/SQL (V)

  • Cuando trabaje con valores nulos puede evitar algunos de los errores más habituales si recuerda las siguientes reglas:
    • Las comparaciones en las que se incluyen valores nulos siempre resultan NULL.
    • Si se aplica el operador lógico NOT a un valor nulo resulta NULL.
    • En las sentencias de control condicionales, si la condición resulta NULL, no se ejecutarán las sentencias asociadas.

PL/SQL
Sentencias SQL en PL/SQL

  • SELECT recupera exactamente UNA fila.
  • INSERT añade una fila.
  • UPDATE modifica una o más filas existentes.
  • DELETE suprime una o más filas existentes.
  • COMMIT hace permanentes todas las modificaciones pendientes.
  • ROLLBACK elimina todas las modificaciones pendientes.
  • SAVEPOINT marca un punto intermedio en el procesamiento de las transacciones.

PL/SQL
Sentencia SELECT

  • Sintaxis:

  • Recuerde, sólo se debe de recuperar una fila. Más de una fila provocará errores.

PL/SQL
Ejemplo de SELECT

  • Recupere la suma de los salarios de todos los empleados de un departamento específico:

PL/SQL
Inserción de Datos

  • Añada nueva información sobre los empleados en la tabla emp:

PL/SQL
Actualización de Datos

  • Aumente el salario de todos los empleados de la tabla emp que son Analistas:

PL/SQL
Supresión de Datos

  • Suprima filas pertenecientes al departamento 10 de la tabla emp:

PL/SQL
Control de Transacciones

  • COMMIT finaliza la transacción actual realizando todos los cambios pendientes en la B.D.

  • ROLLBACK finaliza la transacción actual desechando todos los cambios pendientes.

PL/SQL
Control de Transacciones

PL/SQL
Control Flujo de Ejecución

  • Puede modificar el flujo lógico de sentencias utilizando sentencias IF condicionales y estructuras de control de bucles.
  • Sentencias IF condicionales:
    • IF-THEN
    • IF-THEN-ELSE
    • IF-THEN-ELSIF
  • Control de bucles:
    • Bucle básico LOOP
    • Bucle FOR
    • Bucle WHILE

PL/SQL
Sentencia IF

  • Sintaxis:

PL/SQL
Flujo de IF-THEN-ELSE

PL/SQL
Flujo de IF-THEN-ELSIF

PL/SQL
Condiciones Booleanas

PL/SQL
Bucle Básico: LOOP

  • Sintaxis:

  • Donde condición es una expresión o variable booleana (TRUE, FALSE o NULL).

PL/SQL
Bucle FOR

  • Sintaxis:

  • No declare el índice; se declara implícitamente como un BINARY_INTEGER. Fuera del bucle el índice no está definido.
  • Los límites desde..hasta deben de ser literales numéricos. Pueden ser expresiones que se convierten en valores numéricos.

PL/SQL
Bucle WHILE

  • Sintaxis:

  • La condición se evalúa al inicio de cada iteración

PL/SQL
Etiquetas y Loops Anidados

  • Puede anidar bucles a varios niveles.
  • Utilice etiquetas para distinguir entre los bloques y los bucles.
  • Salga al bucle externo con la sentencia EXIT que hace referencia a la etiqueta.
  • Los nombres de etiquetas deben ir antes de la palabra LOOP y entre
    los delimitadores << >>.

PL/SQL
Etiquetas y Loops Anidados

  • Ejemplo:

 

 

 

CURSORES

PL/SQL
Definición de Cursor

  • Útiles para las consultas que devuelven más de una fila.
  • Son declarados y nombrados por el programador, y manipulados por medio de sentencias específicas en las acciones ejecutables del bloque.

PL/SQL
Control de Cursores

  • 1º. Crear un área SQL específica DECLARE
  • 2º. Identificar el juego activo OPEN
  • 3º. Cargar la fila actual en variables FETCH
  • 4º. Si todavía existen filas sin leer, volver a 3º.
  • 5º. Si no existen más filas a leer CLOSE

PL/SQL
Declaración del Cursor

  • Sintaxis:

  • No incluya la cláusula INTO en la declaración del cursor.
  • Si es necesario procesar filas en algún orden, incluya la cláusula ORDER BY.

PL/SQL
Ejemplo de Declaración

PL/SQL
Apertura del Cursor

  • Sintaxis:

  • Si la consulta no devuelve ninguna fila, no se producirá ninguna excepción al abrir el cursor.
  • Para los cursores declarados con la cláusula FOR UPDATE, la sentencia OPEN bloquea estas filas.

PL/SQL
Recuperación de Datos

  • Sintaxis:

  • Incluya, en el FETCH, el mismo número de variables que las definidas en el cursor, y en el mismo orden.
  • Compruebe si el cursor tiene filas.

PL/SQL
Cierre del Cursor

  • Sintaxis:

  • Cierre el cursor una vez completado el procesamiento de las filas.
  • Si es necesario, vuelva a abrir el cursor.
  • No intente recuperar los datos de un cursor una vez que ha sido cerrado (INVALID_CURSOR).

PL/SQL
Atributos de Cursores

  • Proporcionan información de estado del cursor:

PL/SQL
El Atributo %ISOPEN

  • Utilice el atributo de cursor %ISOPEN antes de ejecutar una recuperación para comprobar si el cursor está abierto.
  • Ejemplo:

PL/SQL
Atributos %NOTFOUND, %ROWCOUNT

  • Utilice %ROWCOUNT para recuperar un número exacto de filas.
  • Utilice %NOTFOUND para determinar cuándo salir del bucle de lectura del cursor.
  • Antes de la primera recuperación, %NOTFOUND es NULL, así que si FETCH no se ejecuta nunca satisfactoriamente, no se saldría nunca del bucle de lectura.

PL/SQL
Ejemplo %NOTFOUND, %ROWCOUNT

  • Ejemplo:

PL/SQL
Bucles FOR de Cursor

  • Sintaxis:

  • Apertura, recuperación y cierre implícitos.
  • No declare el registro, está declarado implícitamente.

PL/SQL
Cursores con Parámetros

  • Sintaxis:

  • Permite abrir un cursor varias veces con un juego activo distinto cada vez.

PL/SQL
Cursores con Parámetros

  • Cada parámetro formal de la declaración del cursor debe tener un parámetro real correspondiente en la sentencia OPEN.
  • La sintaxis de los parámetros es:

PL/SQL
Cursores con Parámetros

  • Transfiera el número de departamento y el cargo a la cláusula WHERE:

PL/SQL
Cláusula FOR UPDATE

  • Sintaxis:

  • El bloqueo explícito le permite denegar el acceso mientras dura una transacción.
  • Bloquee las filas antes de la actualización o supresión.
  • La cláusula FOR UPDATE es la última cláusula de una sentencia SELECT, incluso después del ORDER BY.
  • NOWAIT devuelve un error de Oracle si las filas han sido bloqueadas por otra sesión, de lo contrario se espera.

PL/SQL
Cláusula WHERE CURRENT OF

  • Sintaxis:

  • Incluya la cláusula FOR UPDATE en la definición del cursor para bloquear las filas.
  • Especifique WHERE CURRENT OF en la sentencia UPDATE o DELETE para referirse a la fila actual del cursor.

Vistas

Publicado: julio 12, 2011 en Oracle

VISTAS

Concepto de Vista

  • Una vista es una tabla lógica basada en una tabla u otra vista.
  • No contiene datos en sí misma, pero es como una ventana a través de la cual se pueden ver o cambiar los datos de las tablas.
  • Podemos representar con ellas subconjuntos lógicos o combinaciones de datos.
  • Las tablas sobre las cuales se basa una vista se llaman tablas base.
  • Se almacenan en el Diccionario de Datos, USER_VIEWS.

S.Q.L.
¿Por qué usar Vistas?

  • Para restringir el acceso a la B.D.
  • Para realizar consultas complejas de manera fácil.
  • Para obtener una independencia de los datos
  • Para presentar diferentes vistas de los mismos datos.

S.Q.L.
Creación de una Vista

  • FORCE: Crea la vista sin importar que la tabla base exista o no.
  • WITH CHECK OPTION: Especifica que solamente las filas accesibles a la vista pueden ser insertadas o actualizadas.
  • CONSTRAINT: Nombre asignado a la restricción CHECK OPTION.
  • WITH READ ONLY: Asegura que ninguna operación DML pueda realizarse sobre esta vista.

S.Q.L.
Ejemplo creación de Vista

S.Q.L.
Eliminación de una Vista

  • Al borrar una vista no perderá los datos, porque la vista está basada en tablas subyacentes de la B.D.
  • Únicamente el creador o un usuario con el privilegio DROP ANY VIEW puede eliminar una vista.

S.Q.L.
Limitaciones DML en Vistas

  • Se pueden realizar operaciones DML sobre vistas simples.
  • No se puede eliminar una fila si la vista contiene Funciones de grupo, una cláusula GROUP BY o el comando DISTINCT.
  • No es posible modificar datos en la vista si contiene cualquiera de las condiciones anteriores, columnas definidas por expresiones o la pseudocolumna ROWNUM
  • No se puede agregar datos si la vista contiene cualquiera de las condiciones anteriores o cualquier columna NOT NULL no incluida por la vista (tabla base).

S.Q.L.
Creación de Sinónimos

  • Simplifican el acceso a los objetos al crear otro nombre para un objeto (sinónimo).
  • Hacen referencia a una tabla propia o de otro usuario.
  • Permite acortar la longitud de los nombre de los objetos a la vez que elimina la necesidad de cualificar el objeto con un esquema.
  • El DBA puede crear un sinónimo público accesible a todos los usuarios.

S.Q.L.
Eliminación de Sinónimos

  • Sólo el DBA puede eliminar un sinónimo público.

El lenguaje SQL (III):

Publicado: julio 12, 2011 en Oracle

El Operador UNION

El operador UNION devuelve todas las filas seleccionadas por cualquiera de las dos consultas. Utilice el operador UNION para devolver todas las filas de varias tablas y eliminar las filas duplicadas.

Instrucciones

• El número de columnas y los tipos de datos de las columnas que se están seleccionando deben ser idénticos en todas las sentencias SELECT utilizadas en la consulta. No es necesario que los nombres de las columnas sean idénticos.

• UNION opera sobre todas las columnas que se están seleccionando.

• Los valores NULL no se ignoran durante la comprobación de duplicados.

• El operador IN tiene una prioridad más alta que el operador UNION.

• Por defecto, la salida se clasifica en orden ascendente de la primera columna de la cláusula SELECT.


CREATE TABLE SCOTT.JOB_HISTORY AS SELECT * FROM SCOTT.EMP;

INSERT INTO SCOTT.JOB_HISTORY

VALUES (7499,’ALLEN’,’CLERK’,7698,TO_DATE(’20/02/1981′,’DD/MM/YYYY’),1000,300,20);

COMMIT;

SELECT

EMPNO,JOB

FROM

scott.emp

UNION

SELECT

EMPNO,JOB

FROM

SCOTT.JOB_HISTORY;


El operador UNION elimina los registros duplicados. Si hay registros creados en las tablas EMP y JOB_HISTORY que son idénticos, sólo se muestran una vez. Observe en la salida que el registro para el empleado con EMPNO 7499 aparece dos veces, porque JOB es diferente en cada fila.

Observe el siguiente ejemplo:


SELECT empno, job, deptno

FROM scott.emp

UNION

SELECT empno, job, deptno

FROM scott.job_history;


El Operador UNION ALL

Utilice el operador UNION ALL para devolver todas las filas de varias consultas.

Instrucciones
• A diferencia de lo que sucede con UNION, las filas duplicadas no se eliminan y la salida no se ordena por defecto.

• No se puede utilizar la palabra clave DISTINCT.

Nota: Con la excepción de lo anterior, las instrucciones para UNION y UNION ALL son las mismas.

Ejemplo:


SELECT

empno, job, deptno

FROM

scott.emp

UNION ALL

SELECT

empno, job, deptno

FROM

scott.job_history

ORDER BY

empno;

 

D.M.L.

S.Q.L.
Manipulación de Datos (DML)

  • Sentencias DML son:
    • INSERT Añade registros a una tabla.
    • UPDATE Modifica registros existentes de una tabla.
    • DELETE Elimina registros existentes de una tabla.

S.Q.L.
Control de Datos (DCL)

  • Estas sentencias se completan con los comandos de control de transacción (DCL), las cuales aseguran la consistencia de los datos.
    • COMMIT Finaliza la transacción actual haciendo que todos los cambios pendientes pasen a ser permanentes.
    • ROLLBACK Finaliza la transacción en curso descartando todos los cambios pendientes.
    • SAVEPOINT Establece una "marca" dentro de la transacción en curso, usada por COMMIT o ROLLBACK.

S.Q.L.
La Sentencia INSERT

  • Mediante esta sentencia sólo se inserta un registro cada vez.
  • El nombre de las columnas es opcional. Si se omiten se deben colocar los valores en el orden que las columnas tienen en la tabla.
  • Caracteres y fechas entre comillas simples.

S.Q.L.
Inserción de Valores Nulos

  • Método Implícito: Omitir la columna en la lista:

  • Método Explícito: Especificar NULL o el string vacío (‘’), para cadenas y fechas, en la lista de VALUES:

S.Q.L.
Inserción Valores Especiales

  • SYSDATE registra la fecha y hora actual:

  • USERID inserta el nombre del usuario actual

S.Q.L.
Inserción reg. de otra tabla

  • Se escribe el comando INSERT con una subconsulta.
  • No usar la cláusula VALUES.
  • Deben coincidir el número de columnas de INSERT con el de la subconsulta

S.Q.L.
La Sentencia UPDATE

  • Los registros a modificar se especifican por medio de la cláusula WHERE.
  • Si se omite WHERE se modificarían todos los registros de la tabla.

S.Q.L.
Modificación con subconsultas

  • P.ej.: Modificar el oficio y departamento del empleado 7698, con los valores correspondientes actualmente al empleado 7499:

S.Q.L.
La Sentencia DELETE

  • Los registros a eliminar se especifican en la cláusula WHERE.
  • Si se omite WHERE se borrarán todos los registros de la tabla.

S.Q.L.
Eliminación con subconsulta

  • Utilice subconsultas en sentencias DELETE, para eliminar registros de una tabla, basados en valores de otra tabla:

El lenguaje SQL (II):

Publicado: julio 12, 2011 en Oracle

JOIN

Concepto de JOIN

  • Un JOIN se utiliza para consultar datos de más de una tabla
  • La condición de JOIN se escribe en la cláusula WHERE.
  • Si existen columnas con el mismo nombre en las tablas seleccionadas, se deberán nombrar los campos
  • Ejemplo:

S.Q.L.
Tipos de JOIN

  • Existen dos tipos principales de JOIN:
    • EQUIJOIN Join sobre dos o más tablas, por igualdad de campos.
    • NON-EQUIJOIN Por desigualdad, sin correspondencia directa entre campos de tablas. La relación se puede establecer mediante criterios de rango (<, >, BETWEEN, …)
  • Y dos más adicionales:
    • OUTER JOIN Para ver, también, las filas que no complen la condición de Join. El operador de un Outer Join es el signo más (+), en el “lado” del join que es deficiente en información.
    • SELF JOIN Combinación de una tabla consigo misma.

S.Q.L.
Ejemplo de Equijoin

  • Ya que la columna DEPTNO es igual en ambas tablas, ésta debe ir prefijada por el nombre de la tabla para evitar la ambigüedad.

S.Q.L.
Ejemplo de Non-Equijoins

  • En este ejemplo se han usado alias de tablas (e para la tabla emp y s para la tabla salgrade).

S.Q.L.
Ejemplo de Outer Join

  • En este ejemplo se muestran los números y nombres de todos los departamentos, incluidos aquellos que no tienen empleado.
  • Si se le añade: AND emp.deptno is null, sólo se mostrarían las no coincidencias.

S.Q.L.
Ejemplo de Self Join

  • En este ejemplo la cláusula WHERE contiene la combinación “dónde un jefe de un trabajador coincide con el número de empleado para el jefe”

SUBCONSULTAS

¿Cuándo subconsultas?

  • ¿Quién tiene un salario superior al de Jones

S.Q.L.
Sintaxis de una subconsulta

  • La subconsulta se ejecuta una vez y antes de la consulta principal.
  • El resultado de ella es usado por la consulta principal externa.

S.Q.L.
Guía Uso de Subconsultas

  • Encierre las subconsultas entre paréntesis.
  • No añada una cláusula ORDER BY a una subconsulta.
  • Utilice operadores a nivel de fila para subconsultas que devuelvan solo una fila MONOREGISTRO.
  • Utilice operadores que actúan sobre varios registros para subconsultas que devuelven más de una fila MULTIREGISTRO.

S.Q.L.
Subconsultas Mono-registro

  • Devuelven un único registro.
  • Se utilizan operadores de comparación (=, >, >=, <, <= y <>).
  • Ejemplo:

S.Q.L.
Subconsultas Multi-registro

  • Devuelven más de un registro
  • Se utilizan comparadores multiregistro:
    • IN TRUE si se encuentra en la lista.
    • ANY (y sinónimo SOME) TRUE si la condición se cumple con algún registro de la lista devuelta por la subconsulta.
    • ALL TRUE si la condición se cumple con todos los registros de la lista devuelta por la subconsulta.
  • El operador NOT puede ser utilizado con los operadores IN, ANY y ALL.

S.Q.L.
Ejemplo subc. Multi-registro

S.Q.L.
Subcons. en cláusula FROM

  • Puede utilizar una subconsulta en una cláusula FROM de una sentencia SELECT:

  • Este ejemplo muestra los nombres, salarios, núm. Departamentos y media de salarios, de todos los empleados que cobran más que la media de salarios de su departamento.

Exists y No Exists

Los operadores “exists” y “not exists” se emplean para determinar si hay o no datos en una lista de valores.

Estos operadores pueden emplearse con subconsultas correlacionadas para restringir el resultado de una consulta exterior a los registros que cumplen la subconsulta (consulta interior). Estos operadores retornan “true” (si las subconsultas retornan registros) o “false” (si las subconsultas no retornan registros).

Cuando se coloca en una subconsulta el operador “exists”, Oracle analiza si hay datos que coinciden con la subconsulta, no se devuelve ningún registro, es como un test de existencia; Oracle termina la recuperación de registros cuando por lo menos un registro cumple la condición “where” de la subconsulta.

La sintaxis básica es la siguiente:

… where exists (SUBCONSULTA);

En este ejemplo se usa una subconsulta correlacionada con un operador “exists” en la cláusula “where” para devolver una lista de clientes que compraron el artículo “lapiz”:

select cliente,numero
from facturas f
where exists
(select *from Detalles d
where f.numero=d.numerofactura
and d.articulo=’lapiz’);

Puede obtener el mismo resultado empleando una combinación.

Podemos buscar los clientes que no han adquirido el artículo “lapiz” empleando “if not exists”:

select cliente,numero
from facturas f
where not exists
(select *from Detalles d
where f.numero=d.numerofactura
and d.articulo=’lapiz’);

El lenguaje SQL (I)

Publicado: julio 12, 2011 en Oracle

¿Qué es S.Q.L.?

  • Structured Query Language
  • Establecido como el lenguaje de base de datos relacional estándar.
  • Existen numerosos productos que soportan SQL, cada uno de ellos con pequeñas diferencias sin apenas importancia (p.ej. Oracle).
  • El SQL estándar es el publicado por ANSI e ISO.

Características de S.Q.L.

  • Lenguaje de definición de datos (DDL)
    • Create, Alter, Drop.
  • Lenguaje de manipulación de datos (DML)
    • Insert, Update, Delete.
  • Lenguaje de control de datos (DCL)
    • Grant, Revoke.
  • Control de transacciones
    • Commit, Rollback, Savepoint
  • Restricciones de integridad
    • Referencial, datos.

S.Q.L.
La sentencia SELECT

S.Q.L.
Sentencia Básica

  • Ejemplos:
    SELECT empno, ename
    FROM emp;
    SELECT *
    FROM emp;

S.Q.L.
Etiquetas de Columnas por defecto

  • Justificación de etiquetas y datos por defecto:
    • Izquierda fechas y caracteres
    • Derecha datos numéricos
  • Etiquetas y datos, por defecto, se muestran en mayúsculas.
  • Posibilidad del uso de alias de columnas.

S.Q.L.
Alias de columna

  • Un alias de columna renombra un encabezamiento de columna.
  • Útil, especialmente, en cálculos.
  • Sintaxis:
    • Sigue inmediatamente al nombre de la columna mediante la palabra clave AS entre ellos.
    • Se requiere encerrar un alias entre comillas dobles si contiene espacios en blanco, caracteres especiales o es case sensitive.

S.Q.L.
Uso de Alias de columna

  • SQL> SELECT ename AS nombre, sal salario, sal*12 AS “Salario Anual” FROM emp;
  • Resultado:

S.Q.L.
Expresiones aritméticas

  • Operadores:
    • Suma (+)
    • Resta (-)
    • Multiplicación (*)
    • División (/)
  • Cualquier sentencia SQL
  • Precedencia de operadores: * / + –
  • Operadores misma prioridad se evalúan de izq. a derecha.
  • Paréntesis sobreescriben reglas de precedencia.

S.Q.L.
Valor NULL

  • NULL es un valor inaccesible, sin valor desconocido o inaplicable.
  • NULL no representa ni un cero ni un blanco.
  • Las expresiones aritméticas que contengan NULL se evalúan a NULL (<> cero).

S.Q.L.
Operador de concatenación

  • Representado por dos barras verticales: ||
  • Vincula columnas o cadenas de caracteres.
  • Crea una columna resultado que es una expresión de tipo carácter.
  • Ejemplo:

S.Q.L.
Filas duplicadas

  • Las consultas, por defecto, muestran todas las filas, incluyendo las duplicadas.
  • Las duplicadas se eliminan usando DISTINCT en la cláusula SELECT:

S.Q.L.
Uso de la cláusula WHERE

  • Establece un criterio de selección aplicable a la tabla de la que se quiere recuperar datos.
  • Se especifica a continuación de FROM.
  • La condición de WHERE permite comparar columnas con otras columnas, valores, literales, expresiones aritméticas o funciones.

S.Q.L.
Sintaxis de WHERE

  • Ejemplo:

S.Q.L.
Operadores de comparación

  • Aplicables en las condiciones de la cláusula WHERE:
    • = Igual que
    • > Mayor que
    • >= Mayor que o igual a
    • < Menor que
    • <= Menor que o igual a
    • <> Distinto
    • BETWEEN m AND n Entre m y n (inclusive)
    • IN(lista) Se encuentra en la lista
    • LIKE Se ajusta a un patrón
    • IS NULL Es valor nulo

S.Q.L.
Ejemplos de comparación

  • SQL> SELECT ename, saL FROM emp
    WHERE sal BETWEEN 1000 AND 1500;
  • SQL> SELECT empno, ename, sal, mgr FROM EMP
    WHERE mgr IN (7902, 7566, 7788);
  • SQL> SELECT ename FROM emp
    WHERE ename LIKE‘_A%’;

    • Sintaxis de LIKE:
      • “%” representa cero o varios caracteres
      • “_” representa un solo carácter
  • SQL> SELECT ename, mgr FROM emp
    WHERE mgr IS NULL;

S.Q.L.
Operadores lógicos

  • AND devuelve TRUE si ambas condiciones son TRUE.
  • OR devuelve TRUE si alguna de las condiciones es TRUE.
  • NOT devuelve TRUE si la siguiente condición es FALSE.

S.Q.L.
Ejemplos con operadores lógicos

  • SQL> SELECT empno, ename, job, sal FROM emp
    WHERE sal >= 1100
    AND job = ‘CLERK’;
  • SQL> SELECT empno, ename, job, sal FROM emp
    WHERE sal >= 2000
    OR job = ‘MANAGER’;
  • SQL> SELECT ename, job FROM emp
    WHERE job NOT IN (‘CLERK’, ‘MANAGER’, ‘ANALYST’);

S.Q.L.
Reglas de precedencia

  • Siempre es aconsejable, para facilitar la lectura de la sentencia SQL, utilizar paréntesis que fuercen la prioridad de los operadores lógicos.
  • Sin paréntesis, el orden de evaluación es:
    • 1: Todos los operadores de comparación
    • 2: NOT
    • 3: AND
    • 4: OR

S.Q.L.
Recuperaciones ordenadas

  • Las filas recuperadas en la sentencia SELECT, si no se especifica nada, no tienen ningún orden determinado.
  • Se pueden ordenar con la cláusula ORDER BY siempre al final de una sentencia SELECT.
    • ASC Orden ascendente. Por defecto.
    • DESC Orden descendente.

S.Q.L.
Sintaxis de ORDER BY

  • Ejemplo:

 

FUNCIONES S.Q.L.

S.Q.L.
Funciones SQL

  • Existen dos tipos de funciones:
    • Funciones a nivel de fila
    • Funciones a nivel de múltiples filas
  • Funciones a nivel de fila. Tipos:
    • Carácter
    • Número
    • Fecha
    • Conversión
  • Funciones a nivel de grupo. Tipos:
    • Manipulan grupos de filas y devuelven un resultado por cada uno de ellos.

S.Q.L.
Funciones de Caracteres

  • Pueden ser de dos tipos:
    • Funciones de conversión
    • Funciones de manipulación de caracteres
  • De manipulación de caracteres:
    • CONCAT
    • SUBSTR
    • LENGTH
    • INSTR
    • LPAD Y RPAD
  • De conversión:
    • LOWER , UPPER e INITCAP

S.Q.L.
Funciones de conversión caracteres

  • LOWER: Convierte a minúsculas.
  • UPPER: Convierte a mayúsculas.
  • INITCAP: Convierte la primera letra de cada palabra en mayúsculas, y el resto en minúscula.
  • Atención: Usar una función de conversión dentro de la cláusula WHERE puede ser altamente ineficiente porque si la columna afectada forma parte de un índice éste lo desactiva, provocando un bajo rendimiento.

S.Q.L.
Funciones manipulación caracteres

  • CONCAT: Concatena dos valores.
  • SUBSTR: Extrae una subcadena.
  • LENGTH: Devuelve la longitud de la cadena.
  • INSTR: Devuelve la posición de un carácter o subcadena.
  • LPAD: Justifica a la derecha la cadena.
  • RPAD: Justifica a la izquierda la cadena.

S.Q.L.
Funciones Numéricas

  • ROUND (columna | expresión, n)
    • Redondea a n posiciones decimales. Si se omite n, no se redondea con decimales. Si n es negativo, los números a la izquierda del punto decimal se redondean a decenas, centenas, …
  • TRUNC (columna | expresión, n)
    • Trunca en la enésima posición decimal. Si se omite n, sin lugares decimales. Si n es negativo, los números a la izquierda del punto decimal se truncan a cero.
  • MOD (m, n)
    • Devuelve el resto de la división de m por n.

S.Q.L.
Ejemplos de funciones numéricas

  • SQL> SELECT ROUND(45.923, 2), ROUND(45.923, 0), ROUND(45.923, -1)
    FROM SYS.DUAL;
  • Resultado: 45.92   46   50
  • SQL> SELECT TRUNC(45.923, 2), TRUNC(45,923), TRUNC(45.923, -1)
    FROM SYS.DUAL;
  • Resultado: 45.92   45   40

S.Q.L.
Trabajando con fechas

  • Oracle almacena fechas en un formato numérico interno de 7 bytes:
    • Siglo, año, mes, día, horas, minutos, segundos
  • El formato de fecha por defecto es DD-MON-YY
  • SYSDATE es una función que devuelve fecha y hora (pseudocolumna del sistema)
  • DUAL es una tabla virtual de la bd., que puede ser usada para inspeccionar SYSDATE.

S.Q.L.
Operadores aritméticos de fechas

  • Sumar o restar un número a/o de una fecha da por resultado una fecha.
  • Restar dos fechas para encontrar la cantidad de días entre esas fechas.
  • Sumar horas a una fecha dividiendo la cantidad de horas por 24.

S.Q.L.
Funciones de Fecha (I)

  • MONHTS_BETWEEN (fecha1, fecha2)
    • Número de meses entre dos fechas. El resultado puede ser positivo o negativo.
  • ADD_MONTHS (fecha, n)
    • Añade n meses a fecha, según calendario. N debe de ser un número entero y puede ser negativo.
  • NEXT_DAY (fecha, ‘caracter’)
    • Devuelve la fecha del día especificado (‘carácter’) siguiente a fecha. Carácter puede ser un número representando un día o una cadena de caracteres, p.ej. ‘FRIDAY’.

S.Q.L.
Funciones de Fecha (II)

  • LAST_DAY (fecha)
    • Devuelve la fecha del último día del mes que contiene fecha.
  • ROUND (fecha [,’fmt’])
    • Cuando no se especifica ningún formato, devuelve la fecha del primer día del mes contenido en fecha. Si fmt=YEAR, encuentra el primer día del año.
  • TRUNC (fecha [,’fmt’])
    • Devuelve la fecha con la porción del día truncado en la unidad especificada por el modelo de formato fmt. Si se omite el formato, laf echa se trunca en el día más próximo.

S.Q.L.
Ejemplos funciones de fecha

  • MONTS_BETWEEN (‘01-SEP-95’, ‘11-JAN-94’) 19.6774194
  • ADD_MONTHS(‘11-JAN-94’, 6) ‘11-JUL-94’
  • NEXT_DAY (‘01-SEP-95’, ‘FRIDAY’) ‘08-SEP-95’
  • LAST_DAY (‘01-SEP-95’) ‘30-SEP-95’
  • ROUND (‘25-JUL-95’, ‘MONTH’) ‘01-AUG-95’
  • ROUND (‘25-JUL-95’, ‘YEAR’) ‘01-JAN-96’
  • TRUNC (‘25-JUL-95’, ‘MONTH’) ‘01-JUL-95’
  • TRUNC (‘25-JUL-95’, ‘YEAR’) ‘01-JAN-95’

S.Q.L.
Formatos de Fecha (I)

  • YYYY / YEAR
    • Año completo en número / Año en letras
  • MM / MONTH
    • Nº del mes con dos dígitos / Nombre completo del mes
  • DY / DAY
    • Día de la semana en tres letras / Nombre completo del día
  • fm (fill mode)
    • Elimina los espacios en blanco de relleno o suprime ceros a la izquierda

S.Q.L.
Formatos de Fecha (II)

  • Obtención de la hora:
    • HH / HH12 / HH24
      • Hora del día / Hora (1-12) / Hora (1-24)
    • MI / SS / SSSS
      • Minutos / Segundos / Segundos después de medianoche
    • AM o PM
      • Indicador del Meridiano
    • Sufijo SP / SPTH o THSP
      • Deletreo del número / Deletreo números ordinales
    • Se permiten literales

S.Q.L.
Funciones de conversión (I)

  • La conversión de tipos de datos puede ser:
      • IMPLÍCITA: Realizada automáticamente por Oracle
      • EXPLÍCITA: El usuario es quien la realiza
  • Conversión Implícita de datos
      • De VARCHAR2 o CHAR a NUMBER
      • De VARCHAR2 o CHAR a DATE
      • De NUMBER a VARCHAR2
      • De DATE a VARCHAR2
    • Estas conversiones se realizan por asignaciones, si Oracle 8 puede convertir el tipo de dato del valor utilizado en la asignación en el tipo de dato que era el objetivo de la asignación.

S.Q.L.
Funciones de conversión (II)

  • TO_CHAR (número | fecha [,’fmt’])
    • Convierte un número o fecha en una cadena de caracteres VARCHAR2 con el modelo de formato fmt.
      • 9: Representa un número
      • 0: Fuerza a que se muestra el cero
      • $: Signo de dólar
      • L: Usa el signo de moneda local
      • .: Imprime el punto decimal
      • ;: Imprime el indicador de millar
      • Para fechas, los fmt anteriores.

S.Q.L.
Funciones de conversión (III)

  • TO_NUMBER (char)
    • Convierte una cadena de caracteres con dígitos en un número.
  • TO_DATE (char [,’fmt’])
    • Convierte una cadena de caracteres representando una fecha en un valor de fecha según el fmt especificado. Si se omite el fmt, el formato es DD-MON-YY.
  • NVL (expr1, expr2)
    • Convierte un nulo (expr1) a un valor de tipo fecha, cadena o número (expr2).

S.Q.L.
La Función DECODE

  • Hace las veces de sentencia CASE o IF-THEN-ELSE, para facilitar consultas condicionales.
    • Descifra una expresión después de compararla con cada valor de búsqueda. Si la expresión es la misma que la búsqueda, se devuelve el resultado. Si se omite el valor por defecto, se devolverá un valor nulo donde una búsqueda no coincida con ninguno de los valores resultantes.

S.Q.L.
Uso de DECODE

  • SQL> SELECT job, sal,
    DECODE (job, ‘ANALYST’, sal*1.1, ‘CLERK’, sal*1.15, ‘MANAGER’, sal*1.20, sal) AS “Nuevo salario”
    FROM emp;
  • Si job = ‘ANALYST ‘ entonces el salario se incrementa en un 10%
  • Si job = ‘CLERK’ entonces se incrementa en un 15%
  • Si jog = ‘MANAGER’ entonces se incrementa en un 20%
  • Para otro caso, entones no hay incremento de salario

FUNCIONES DE GRUPO

S.Q.L.
Funciones de Grupo (I)

  • AVG ([DISTINCT | ALL] n)
    • Valor promedio de n.
  • COUNT ({* | [DISTINCT | ALL |] expr})
    • Cantidad de filas con expr no nulo. Con * se cuentan todas las filas incluyendo duplicadas y valores nulos.
  • MAX ([DISTINCT | ALL] expr)
    • Valor máximo de expr.
  • MIN ([DISTINCT | ALL] expr)
    • Valor mínimo de expr., ignorando los valores nulos.

S.Q.L.
Funciones de Grupo (II)

  • STDDEV ([DISTINCT | ALL] n)
    • Desviación estándar de n, ignorando los valores nulos.
  • SUM ([DISTINCT | ALL] n)
    • Suma los valores de n, ignorando los valores nulos.
  • VARIANCE ([DISTINCT | ALL] n)
    • Varianza de n, ignorando los valores nulos
  • Estas funciones no se pueden usar en la cláusula WHERE.

S.Q.L.
Funciones de grupo y Nulos

  • Las funciones de grupo IGNORAN los valores nulos de las columnas.
  • ¿Qué resultado obtendríamos se calculamos la media de la comisión de los empleados?

S.Q.L.
NVL y funciones de grupo

  • Esta media no es correcta porque se han ignorado las filas cuya comisión es nula.
  • Solución: Uso de la función NVL para forzar a las funciones de grupo que admitan los valores nulos.

S.Q.L.
Sintaxis de GROUP BY

  • Crea grupo de datos, por lo tanto se pueden usar funciones de grupo para devolver información resumida para cada grupo.

S.Q.L.
Uso de GROUP BY (I)

  • Si se incluye una función de grupo en una cláusula SELECT, no se puede seleccionar resultados individuales a menos que la columna aparezca en la cláusula GROUP BY.
  • No se pueden usar alias en GROUP BY.
  • Por defecto, tras un GROUP BY, las filas se ordenan de forma ascendente
  • Ejemplo:
    >SQL> SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;

S.Q.L.
Uso de GROUP BY (II)

  • La columna referenciada por GROUP BY no es necesario seleccionarla.
  • Todas las columnas mencionadas en la SELECT que no son funciones de grupo, tienen que estar en la cláusula GROUP BY.
  • Se pueden formar agrupaciones sobre múltiples columnas:
    >SQL> SELECT deptno, job, sum(sal) FROM emp GROUP BY deptno, job;

S.Q.L.
Consultas no válidas

  • Cualquier columna o expresión en la SELECT que no sea una función agregada, tiene que ser especificada en la cláusula GROUP BY
    SQL> SELECT deptno, COUNT(ename) FROM emp;
  • No puede usar una cláusula WHERE para restringir grupos. Utilice la cláusula HAVING para restringir grupos.
    SQL> SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) > 2000 GROUP BY deptno;

S.Q.L.
Cláusula HAVING

  • Use la cláusula HAVING para restringir grupos:
    • Los registros son agrupados
    • Se aplica la función de grupo
    • Los grupos que se corresponden con la cláusula HAVING se visualizan (condición TRUE).
  • HAVING puede preceder a GROUP BY, pero se recomienda que se ponga en primer lugar GROUP BY porque es más lógico. (1º se calculan grupos y posteriormente se calcula HAVING sobre esos gpos.).

S.Q.L.
Sintaxis de HAVING