Bloques anónimos PL/SQL

Empezaremos con los bloques anónimos, caracterizados porque no tienen nombre y se suelen crear y ejecutar desde PL/SQL.

Todo bloque debe acabar en . para que sea almacenado en el buffer SQL. Una vez guardado lo podemos ejecutar con la orden “run”. También podemos guardarlo en un fichero con la siguiente orden:

save nombrefichero [replace]

El replace sólo lo pondremos si el fichero ya esta creado.

Para cargar y ejecutar este bloque anónimo guardado en fichero ejecutaremos la siguiente orden:

start nombrefichero

El start lo podemos cambiar por la @ y nos funcionará igualmente.

Pero también podemos cargarlo sin ejecutarlo con la orden “get” y luego ejecutarlo posteriormente con la orden “run”

Un ejemplo muy sencillo de bloque seria el que nos muestra en pantalla un nombre.

BEGIN

DBMS_OUTPUT.PUT_LINE('nombre');


END;
.

Además en los bloques PL/SQL se pueden utilizar lo que llamamos variables de sustitución, que nos pedirán datos por pantalla antes de ejecutar el bloque. Estas variables tienen que ir antepuestas del & para que funcionen.

Un ejemplo seria un bloque que nos pide el DNI de un usuario y nos muestra su nombre.

DECLARE
Vnom clientes.nombre%TYPE;
BEGIN
select nombre into Vnom from clientes where NIF= '&V_nif';
DBMS_OUTPUT.PUT_LINE (Vnom);
END;
.


ejemplo



SQL> CREATE PROCEDURE credit (acc_no IN NUMBER, amount IN NUMBER)
1> AS BEGIN
2> UPDATE accounts
3> SET balance = balance + amount
4> WHERE account_id = acc_no;
5> END;

Funciones

¿QUÉ ES UNA FUNCIÓN?

Una función es un subprograma que devuelve un valor.

La sintaxis para construir funciones es la siguiente:

CREATE [OR REPLACE]
FUNCTION [( IN ,  IN , ...)]
RETURN 
IS
  result ;
BEGIN

  return(result);
[EXCEPTION]
  -- Sentencias control de excepcion
END [];

El uso de OR REPLACE permite sobreescribir una función existente.
Si se omite, y la función existe, se producirá, un error.
La sintaxis de los parámetros es la misma que en los procedimientos almacenado,
exceptuando que solo pueden ser de entrada.

Ejemplo:

CREATE OR REPLACE FUNCTION fn_Obtener_Precio(p_producto VARCHAR2) RETURN NUMBER IS result NUMBER; BEGIN SELECT PRECIO INTO result FROM PRECIOS_PRODUCTOS WHERE CO_PRODUCTO = p_producto; return(result); EXCEPTION WHEN NO_DATA_FOUND THEN return 0; END ;

Si el sistema nos indica que el la función se ha creado con errores de compilación podemos ver estos errores de compilacion con la orden SHOW ERRORS en SQL *Plus.
Una vez creada y compilada la función podemos ejecutarla de la siguiente forma:

DECLARE
  Valor NUMBER;
BEGIN
 Valor := fn_Obtener_Precio('000100');

END; 

Las funciones pueden utilizarse en sentencias SQL de manipulación de datos (SELECT, UPDATE, INSERT y DELETE):

SELECT CO_PRODUCTO,
      DESCRIPCION,
      fn_Obtener_Precio(CO_PRODUCTO)
FROM PRODUCTOS; 

Trigger

¿QUÉ ES UN TRIGGER?

Un trigger(disparacdor o activador) define una acción que la base de datos siempre debería realizar cuando ocurre
algún tipo de acontecimiento que la afecta.


Se utilizan para:

*mantener la integridad referencial.

*asegurar reglas de negocios complejas y

*auditar cambios en los datos.


Para definir un trigger se debe:

*Especificar las condiciones bajo las que el trigger será ejecutado.

*Especificar las acciones que se realizarán cuando el trigger se ejecute.



Las acciones del trigger se definen mediante bloques de PL/SQL nominados con las secciones:


*Declarativa

*Ejecutable

*Manejo de excepciones

*Los triggers se almacenan en la BD y están asociados a una tabla.

*Pueden afectar a n filas.

*Se ejecutan de manera implícita ante eventos(operación de inserción, modifiación o borrado)

*Se compilan cada vez que se activan.

SINTAXIS DE UN TRIGGER: Esta instrucción nos permite definir un trigger:

CREATE TRIGGER  [NombreEsquema.]NombreTrigger
 ON {tabla | vista } [,...n] ]
 {FOR|AFTER|INSTEAD OF} {[INSERT][,][UPDATE][,][DELETE]}
 AS sentencia_sql  [;] [,...n ]   

Cuando se INSERTe un pedido, entrará en funcionamiento el trigger ActualizaVentasEmpleado y se ejecutarán las instrucciones que aparecen después de AS, en este caso actualizará (UPDATE) la tabla empleados sumará a las ventas del empleado (ventas) el importe del pedido insertado (inserted.importe), y sólo actualizará el empleado cuyo numemp coincida con el campo rep del pedido insertado (WHERE numemp=inserted.rep).

    -- Ahora comprobamos que funciona
SELECT * FROM empleados WHERE  numemp=108;
INSERT INTO pedidos (numpedido,fechapedido,rep,clie,cant,importe,fab,producto)
          VALUES  (123456789,getdate(),108,2103,10,100,'Aci',41001)
SELECT * FROM empleados WHERE  numemp=108; 

Vemos que al insertar un pedido de 100 € del empleado 108, sus ventas han aumentado en 100€.

ALTER TRIGGER

Permite modificar la definición del desencadenador, no permite cambiar su nombre, para cambiar el nombre de un desencadenador hay que eliminarlo (DROP TRIGGER) y volver a crearlo (CREATE TRIGGER).

    ALTER TRIGGER [NombreEsquema.]NombreTrigger
 ON {tabla|vista}
 {FOR|AFTER|INSTEAD OF} {[INSERT][,][UPDATE][,][DELETE]} [WITH APPEND]
 AS sentencia_sql  [;] [,...n ]   

La sintaxis es similar a la instrucción CREATE TRIGGER.
Ejemplo:

    ALTER TRIGGER  ActualizaVentasEmpleados
ON pedidos FOR INSERT
AS
UPDATE  empleados SET ventas=ventas+inserted.importe
FROM  empleados, inserted
WHERE numemp=inserted.rep AND  inserted.importe IS NOT NULL; 

Hemos modificado el desencadenador para que si el importe del pedido es nulo, no haga nada, no actualice con un valor nulo.

DISABLE TRIGGER

En ocasiones puede ser útil inhabilitar temporalmente un desencadenador sin que por ello suponga eliminarlo, para estos casos podemos utilizar la sentencia DISABLE TRIGGER.

    DISABLE TRIGGER  {[NombreEsquema.]NombreTrigger [,...n] | ALL }
 ON {NombreTablaVista | DATABASE |  ALL SERVER} [;]  

Ejemplo:

    DISABLE TRIGGER  ActualizaVentasEmpleado ON pedidos; 

Deshabilita el desencadenador que hemos creado anteriormente, si después de ejecutar esta sentencia se introduce un nuevo pedido, el empleado correspondiente no se actualizará.
Lo podemos comprobar con:

    SELECT * FROM empleados WHERE  numemp=108;
 INSERT INTO pedidos  (numpedido,fechapedido,rep,clie,cant,importe,fab,producto)
                    VALUES  (123456791,getdate(),108,2103,10,300,'Aci',41001)
 SELECT * FROM empleados WHERE  numemp=108;  

    DISABLE TRIGGER ALL ON pedidos;  

Deshabilita todos los desencadenadores asociados a la tabla pedidos:

    DISABLE TRIGGER ALL ON DATABASE;  

Deshabilita todos los desencadenadores definidos en la base de datos actual,deshabilita todos los desencadenadores definidos en el servidor:

    DISABLE TRIGGER ALL ON ALL SERVER; 

Eliminar un trigger:

DROP TRIGGER nombre_disparador;

Esta instrucción te permite ver los errores de compilacion:

SHOW ERREOS TRIGGER nombre_disparador;

Modelo Relacional

Modelo Relacional

Historia del Modelo Relacional

Codd propuso que los sistemas de bases de datos deberían presentarse a los usuarios con una visión de los datos organizados en estructuras llamadas relaciones, definidas como conjuntos de tuplas (filas) y no como series o secuencias de objetos, con lo que el orden no es importante. Por tanto, detrás de una relación puede haber cualquier estructura de datos compleja que permita una respuesta rápida a una variedad de consultas. Codd hizo entonces énfasis en que el usuario de un sistema relacional sólo debía preocuparse por el qué consultar y no el cómo de las estructuras de almacenamiento (lo que ahora se conoce como modelo físico). Aún hoy se consideran validas sus afirmaciones.
Puede parecer extraño, pero las ideas de Codd no fueron “recibidas con los brazos abiertos” en IBM, donde realizaba sus labores de investigación, según afirma Harlwood Kolsky, un físico y antiguo compañero de Codd; “fue un enfoque revolucionario”, recuerda Kolsky. El nuevo enfoque de Codd, basado en la teoría matemática de conjuntos, no tuvo eco inmediato en IBM, que prefirió a IMS, un producto al que se le había invertido una fuerte cantidad de esfuerzo y dinero.
Un grupo de la Universidad de Berkeley en California, liderado por Michael Stonebreaker, creyó en la idea del modelo relacional y obtuvo financiamiento para desarrollar un sistema, el Ingres, cuya primera versión se presentó en 1974 y fue el primer manejador relacional de bases de datos funcional. Esto tuvo como consecuencia que IBM reaccionara poniendo en marcha otro sistema relacional, el System R con características de multiusuario y un lenguaje de consulta estructurado, el SEQUEL que luego pasaría a llamarse SQL (Structured Query Language). Para entonces Larry Ellison, un empresario del Valle del Silicón, había tomado ventajas de los escritos de Codd para crear un nuevo producto y una nueva empresa que hasta la fecha se conoce como Oracle.
En 1985 Codd publicó sus famosas 12 reglas sobre el modelo relacional de bases de datos, un resumen de sus características fundamentales. Es preciso resaltar que todavía hoy algunas de estas reglas son de difícil implementación para los fabricantes de manejadores de bases de datos relacionales. Además de ser considerado como el padre del modelo relacional, Codd también incursionó en el modelo multidimensional de análisis de datos conocido como OLAP (On Line Analytical Processing) y en 1993 Codd y algunos de sus colegas publicaron las “12 reglas para OLAP”.

¿Qué es un Modelo Relacional?
La estructura fundamental del modelo relacional es la relación, es decir una tabla bidimensional constituida por filas (tuplas) y columnas (atributos). Las relaciones representan las entidades que se consideran interesantes en la base de datos. Cada instancia de la entidad encontrará sitio en una tupla de la relación, mientras que los atributos de la relación representan las propiedades de la entidad. Por ejemplo, si en la base de datos se tienen que representar personas, podrá definirse una relación llamada "Personas", cuyos atributos describen las características de las personas. Cada tupla de la relación "Personas" representará una persona concreta. Por ejemplo, la relación:
Personas (RFC, nombre, apellido, sexo, estadoCivil, fechaNacimiento).
Es apenas una definición de la estructura de la tabla, es decir su nombre y la lista de atributos que la componen. Si esta estructura se puebla con datos, entonces tendremos una lista de valores individuales para cada tupla, atributo por atributo.
Aunque una relación es más conocida como tabla, las tuplas como filas y los atributos como columnas, en este escrito usaremos la terminología original(No debemos confundir relación con el mismo término usado en el modelado de Entidad-Relación que se usa para describir las asociaciones que existen entre entidades) y de donde deriva el nombre del modelo.
Las tuplas en una relación son un conjunto en el sentido matemático del término, es decir una colección no ordenada de elementos diferentes. Para distinguir una tupla de otra, se recurre al concepto de "llave primaria", o sea un atributo o conjunto de atributos que permiten identificar unívocamente una tupla en una relación (en el ejemplo, el atributo RFC cumple con esta función). Naturalmente, en una relación puede haber más combinaciones de atributos que permitan identificar unívocamente una tupla ("llaves candidatas"), pero entre éstas se elegirá una sola para utilizar como llave primaria. Los atributos de la llave primaria no pueden asumir el valor nulo (que significa un valor no determinado), en tanto que ya no permitirían identificar una tupla concreta en una relación.
Esta propiedad de las relaciones y de sus llaves primarias se conoce como integridad de las entidades.
Cada atributo de una relación se caracteriza por un nombre y por un dominio. El dominio indica qué valores pueden ser asumidos por una columna de la relación. A menudo un dominio se define a través de la declaración de un tipo para el atributo (por ejemplo diciendo que es una cadena de diez caracteres), pero también es posible definir dominios más complejos y precisos. Por ejemplo, para el atributo "sexo" de nuestra relación "Personas" podemos definir un dominio por el cual los únicos valores válidos son 'M' y 'F'; o bien para el atributo "fechaNacimiento" podremos definir un dominio por el que se consideren válidas sólo las fechas de nacimiento después del uno de enero de 1960, si en nuestra base de datos no está previsto que haya personas con fecha de nacimiento anterior a esa. El motor de datos se ocupará de controlar que en los atributos de las relaciones se incluyan sólo los valores permitidos por sus dominios. Característica fundamental de los dominios de una base de datos relacional es que sean "atómicos", es decir que los valores contenidos en los atributos no se puedan separar en valores de dominios más simples. Más formalmente se dice que no es posible tener atributos con valores múltiples (multivaluados).
La normalización, o sea la razón y uso de las formas normales, es evitar la repetición innecesaria de datos (redundancia). Una solución a este problema es repartirlos en varias relaciones y utilizar referencias por valor entre ellas. Este es un ejemplo típico de que la tupla de una relación, digamos de Empleados, no deba repetir toda la información de su departamento, sino que debe utilizar una referencia por valor a la tupla de la relación Departamento, donde están todos estos datos. Este procedimiento ahorra espacio de almacenamiento, optimiza el rendimiento y, al eliminar la redundancia, impide modificaciones parciales o incompletas que podrían dar lugar a inconsistencias. Existen hasta 6 formas normales pero, en la práctica, se adopta generalmente la tercera forma normal.

¿Qué es una base de datos?

Una base de datos o banco de datos (en ocasiones abreviada con la sigla BD o con la abreviatura b. d.) es un conjunto de datos pertenecientes a un mismo contexto y almacenados sistemáticamente para su posterior uso. En este sentido, una biblioteca puede considerarse una base de datos compuesta en su mayoría por documentos y textos impresos en papel e indexados para su consulta. En la actualidad, y debido al desarrollo tecnológico de campos como la informática y la electrónica, la mayoría de las bases de datos están en formato digital (electrónico), que ofrece un amplio rango de soluciones al problema de almacenar datos.

Existen programas denominados sistemas gestores de bases de datos, abreviado SGBD, que permiten almacenar y posteriormente acceder a los datos de forma rápida y estructurada. Las propiedades de estos SGBD, así como su utilización y administración, se estudian dentro del ámbito de la informática.

Las aplicaciones más usuales son para la gestión de empresas e instituciones públicas. También son ampliamente utilizadas en entornos científicos con el objeto de almacenar la información experimental.

Aunque las bases de datos pueden contener muchos tipos de datos, algunos de ellos se encuentran protegidos por las leyes de variospaíses. Por ejemplo, en España los datos personales se encuentran protegidos por la Ley Orgánica de Protección de Datos de Carácter Personal (LOPD)..

Fuentes:
http://docs.google.com/viewer?a=v&q=cache:gn7cHeB59aYJ:www.inegi.gob.mx/inegi/contenidos/espanol/prensa/Contenidos/Articulos/tecnologia/relacional.pdf+modelo+relacional+de+base+de+datos&hl=es&gl=mx&pid=bl&srcid=ADGEESjGzNo3GQIEXU66gC1J8jxLNN-DXPDb4bCf6_W3TGtI4ql6nqp--_8tMdqSYjqJMn11KqTtIK9xNqZr0kA1XYWwYCWAobp-l63adUFXVYzVA_dU-vnqh6K9doOT1SsfIaetU7NI&sig=AHIEtbT4XkJkSWz29yhhz7qXLF8iD4uySA

J. H. Orallo, La disciplina de los sistemas de bases de datos. Historia, situación actual y perspectivas; mayo 2002

Diccionario de datos

DICCIONARIO DE DATOS

¿Qué es Oracle?

Oracle es básicamente una herramienta cliente/servidor para la gestión de Bases de Datos. Es un producto vendido a nivel mundial, aunque la gran potencia que tiene y su elevado precio hace que sólo se vea en empresas muy grandes y multinacionales, por norma general. En el desarrollo de páginas web pasa lo mismo: como es un sistema muy caro no está tan extendido como otras bases de datos, por ejemplo, Access, MySQL, SQL Server, etc.

Vamos ahora en centrarnos en que es Oracle exactamente y como funciona la programación sobre éste. Oracle como antes he mencionado se basa en la tecnología cliente/servidor, pues bien, para su utilización primero sería necesario la instalación de la herramienta servidor (Oracle 8i) y posteriormente podríamos atacar a la base de datos desde otros equipos con herramientas de desarrollo como Oracle Designer y Oracle Developer, que son las herramientas básicas de programación sobre Oracle.

Para desarrollar en Oracle utilizamos PL/SQL un lenguaje de 5ª generación, bastante potente para tratar y gestionar la base de datos, también por norma general se suele utilizar SQL al crear un formulario.

El Developer es una herramienta que nos permite crear formularios en local, es decir, mediante esta herramienta nosotros podemos crear formularios, compilarlos y ejecutarlos, pero si queremos que los otros trabajen sobre este formulario deberemos copiarlo regularmente en una carpeta compartida para todos, de modo que, cuando quieran realizar un cambio, deberán copiarlo de dicha carpeta y luego volverlo a subir a la carpeta. Este sistema como podemos observar es bastante engorroso y poco fiable pues es bastante normal que las versiones se pierdan y se machaquen con frecuencia. La principal ventaja de esta herramienta es que es bastante intuitiva y dispone de un modo que nos permite componer el formulario, tal y como lo haríamos en Visual Basic o en Visual C, esto es muy de agradecer.

¿Qué es un diccionario de datos en Oracle?

Oracle posee un diccionario de datos; es decir la manera de extraer el catálogo de objetos de una base de datos, nos refererimos a: tablas, usuarios, roles, vistas, columnas de las tablas, secuencias, constraints, sinónimos, índices, triggers, funciones etc.., esta información se encuentra contenida en tablas y vistas del sistema.

Dichas tablas en base a las cuales podemos obtener esta información aplicando sentencias sql.

A continuación enumeramos las tablas más importantes del diccionario de datos que nos permitirá obtener información de los objetos de la base de datos.

• Código de funciones y procedimientos: dba_source, user_source, all_source
• Usuarios: dba_users, user_users, all_users
• Roles: dba_roles
• Roles asignados a roles o usuarios: dba_role_privs, user_role_privs
• Privilegios asignados a roles o usuarios: dba_sys_privs
• Permisos sobre tablas asignados a roles o usuarios: dba_tab_privs
• Límites de recursos: user_resource_limits
• Perfiles y sus límites de recursos asociados: dba_profiles
• Límites de recursos en cuanto a restricciones en claves: user_password_limits
• Límites de recursos en cuanto a espacio máximo en tablespaces: dba_ts_quotas, user_ts_quotas
• Tablespaces: dba_tablespaces, user_tablespaces
• Ficheros que componen los datafiles: dba_data_files
• Segmentos: dba_segments, user_segments, all_segments
• Segmentos de Rollback: dba_rollback_segs
• Extensiones que forman los segmentos: dba_extents, user_extents
• Bloques libres: dba_free_space, user_free_space
• Bloques libres que podrían unirse: dba_free_space_coalesced
• Secuencias: dba_sequences, user_sequences, all_sequences
• Tablas, vistas, sinónimos y secuencias: dba_catalog, user_catalog, all_catalog
• Tablas : dba_tables, user_tables, all_tables
• Campos de tablas: dba_cons_columns, user_cons_columns, all_cons_columns
• Columnas de las tablas: dba_tab_columns, user_tab_columns, all_tab_columns
• Vistas: dba_views, user_views, all_views
• Sinónimos: dba_synonyms, user_synonyms, all_synonyms
• Restricciones de clave primaria, externa, not null, integridad referencial: dba_constraints, user_constraints, all_constraints
• Índices: dba_indexes, user_indexes, all_indexes
• Columnas de los índices: dba_ind_columns, user_ind_columns, all_ind_columns

Los prefijos dba_ son exclusividad para los usuarios que tengan el rol dba que sería el administrador de la base de datos y mostraría información de accesos de los usuarios que tienen asignado dicho rol.
Los prefijos_user indican que mostrará información en el ámbito del usuario que se encuentre conectado; [esquema].[objeto].

Información sobre todos los objetos: tablas, vistas, funciones, procedimientos, índices, triggers, etc. : dba_objects, user_objects, all_objects

Fuentes: Calvo mangas Juan Miguel, (2009)"Base de datos:Nivel Medio"

http://mioracle.blogspot.com/2008/02/diccionario-de-datos.html