Manual De Oracle 9i

UNIVERSIDAD AUTONOMA DE LOS ANDES FACULTAD: Sistemas Mercantiles ESPECIALIDAD: Ingeniería en Sistemas NIVEL: Séptimo NOMBRE: Carlos Bravo TITULO DEL TRABAJO: Manual de Oracle 9i

1

Índice PRE INSTALACION DE ORACLE………………………………………………… 01 Planificación…………………………………………………………………. 01 Tareas previas………………………………………………………………… 01 DESINSTALACION DEL SOFTWARE DE ORACLE PREVIO………………....... 01 Desinstalar el software de oracle………………………………………………02 Variable de entorno…………………………………………………………… 03 Borrar las carpetas de Oracle…………………………………………………. 04 INSTALACION DE ORACLE 9i……………………………………………………. 05 Pasos para la instalación……………………………………………………… 05 Prueba de la instalación………………………………………………………. 14 CONECTAR MEDIANTE SQL PLUS………………………………………………. 16 Breve historia de Oracle………………………………………………………. 16 Estrategia de Oracle…………………………………………………………… 18 Conexiones……………………………………………………………………. 19 ESTRUCTURA DE LA BASE DE DATOS………………………………………….. 19 Archivos de datos……………………………………………………………… 19 Archivos de control……………………………………………………………. 19 Archivos de parámetros……………………………………………………….. 19 Archivos de contraseña………………………………………………………... 19 Copias de archivos rehacer……………………………………………………. 19 Instancia de base de datos……………………………………………………... 19 Procesamiento de instrucciones SQL…………………………………………..20 ARQUITECTURA DE BASE DE DATOS ORACLE………………………………. 20 Tablespaces…………………………………………………………………… 21 Segmentos…………………………………………………………………….. 21 Extensión……………………………………………………………………… 21 Bloque de datos Oracle……………………………………………………….. 21 Archivos de datos……………………………………………………………… 21 Bloques de sistema…………………………………………………………….. 21 USUARIOS…………………………………………………………………………… 22 Privilegios…………………………………………………………………….. 22 Perdida de información……………………………………………………….. 22 Fallo en una instrucción………………………………………………………. 23 Fallo en la comunicación……………………………………………………... 23 Caída del servidor…………………………………………………………….. 23 HERRAMIENTAS DE ORACLE……………………………………………………. 23 SQL PLUS…………………………………………………………………….. 23 iSQL*PLUS…………………………………………………………………… 23 Servidor http de oracle………………………………………………………… 23 ESTRUCTURA DE LENGUAJE SQL………………………………………………. 28 Instrucciones de transferencia…………………………………………………. 28 Normas de escritura…………………………………………………………….28 Esquemas de usuario y objeto…………………………………………………. 29 Creación de tablas………………………………………………………………29 Ordenación…………………………………………………………………….. 46 Agrupación…………………………………………………………………….. 48

2

Manual de Oracle 9i INSTALACIÓN DE ORACLE 9I Versión para Windows El presente manual es una guía práctica de instalación y manejo de Oracle. Por lo que trataremos de resumir al máximo la teoría que se encuentra en un verdadero manual que en alguno de los casos puede contener más de 200 páginas. Este documento esta orientado a ayudar a usuarios particulares que deseen comenzar a practicar con el sistema gestor de bases de datos (SGBD) Oracle en su versión 9i. La instalación comentada es la versión Oracle 9i para Windows. Por lo que se da por hecho que ese es el sistema operativo instalado.

PREINSTALACIÓN Planificación Antes de proceder a la instalación conviene planificar un poco lo que será la instalación. Oracle anima a seguir la arquitectura OFA (Optimal Flexible Architecture) con la que se consiguen mejores resultados. Esta arquitectura es un conjunto de reglas que permiten crear bases de datos que requieren muy poco mantenimiento. Tareas previas Antes de proceder a la instalación real de Oracle, debemos realizar una serie de tareas previas. Estas tareas son: Verificación de los requerimientos. Como en la actualidad ya no es dable que se trabaje con equipos inferiores a Pentium IV entonces la instalación de Oracle requiere estos requisitos mínimos: Windows 2000 con service pack 1 o Windows XP Professional o Windows 2003 Server Protocolo TCP/IP o TCP/IP con SSL

3

256 MB de memoria RAM Al menos 200 MB de memoria virtual mínima y 400 MB de máxima VGA de 256 colores 140 MB en el disco duro del sistema (normalmente C) y otras 300 MB más en ese mismo disco para el software de Administrador y otras herramientas de Oracle. Además se requiere 2,85 GB en el disco que albergará la carpeta raíz de Oracle, el llamado Oracle Home, 1 GB más si se usa el Oracle Management Server y otros 2,35 GB más si se usa el Oracle Internet Directory. Las herramientas de administrador requieren 750 MB y las de ejecución (Runtime) otras 300 MB. Además hay que tener en cuenta que los tamaños de disco indicados sólo son válidos si se usan discos duros con el sistema NTFS, si es FAT32 hay que doblar el tamaño (llegando a las 13 GB con instalación absoluta). Se requiere también un navegador web. Especialmente Internet Explorer 5 o superior o Netscape 4.7 o superior (o cualquiera compatible Desinstalar el software Oracle previo. Si tuviera una versión de Oracle anterior, deberá desinstalarlo. Desinstalar Oracle Para una instalación limpia o bien porque ya no se desea utilizar el SGBD Oracle, a veces se requiere desinstalar este software. El proceso de desinstalación ha de ser muy cuidadoso para evitar dejar residuos en el ordenador. Los pasos son: 1. Desinstalar el software de Oracle 2. Borrar las entradas en el registro de Windows 3. Cambiar las variables de entorno de Oracle 4. Reiniciar el ordenador 5. Borrar las carpetas de Oracle

Desinstalar el software de Oracle

4

Antes de retirar el software de Oracle, se necesita primero parar las instancias en ejecución, para ello hay que: 1. Cerrar todas las aplicaciones abiertas 2. Abrir el Asistente de Configuración de Bases de datos (Oracle Database Configuration Assistant) que se encuentra en la carpeta de programas de Oracle y cerrar todas las instancias de base de datos. Eso se puede hacer también desde la consola de administración (Enterprise Console Manager) de Oracle.

3. Ilustración 1, Ventana relacionados con Oracle e Servicios en Windows XP, abierta en la zona de los servicios 4. Ir al apartado Servicios de las herramientas administrativas en el panel de control y detener todos los servicios relacionados con Oracle. Por si acaso pulsar Ctrl+Alt+Supr y en la ventana del administrador de tareas de Windows, eliminar los procesos (si les hubiera) de nombre: Oracle, isqlplus, java, TNSLSNR y Apache Usar el Instalador Universal de Oracle (Oracle Universal Installer, que se encuentra en la página de oracle. 5. normalmente en Oracle Installation Products en el menú de Programas), hacer clic en Desinstalar programas y marcar todos los productos Oracle para desinstalar.

5

1. Ir a Inicio-Ejecutar y escribir REGEDIT en el cuadro que aparece. Aceptar el cuadro. Aparecerá el registro de Windows. Antes de tocarle conviene hacer una copia de él (se puede hacer usando el comando Exportar en el menú Archivo) 2. Ir al apartado HKEY_LOCAL_MACHINE_SOFTWARE\SOFTWARE del registro y eliminar la carpeta ORACLE entera. 3. Ir al apartado HKEY_LOCAL_MACHINE_SOFTWARE\SYSTEM\ CurrentControlSet\Services y eliminar cualquier carpeta que comience con el texto Oracle. 3. Ir al apartado HKEY_LOCAL_MACHINE_SOFTWARE\SYSTEM\ CurrentControlSet\Services\EventLog\Application y eliminar cualquier carpeta que comience con el texto Oracle 4. Repetir los pasos anteriores para cualquier carpeta con el texto oracle que se encuentre en cualquier carpeta ControlSet dentro del registro (esas carpetas están dentro de SYSTEM en HKEY_LOCAL_MACHINE_SOFTWARE) Variables de entorno Por último conviene modificar las variables de entorno modificadas por el propio Oracle. Para ello basta ir al apartado Sistema del Panel de control, buscar el apartado dedicado a las variables de entorno y eliminar las entradas relacionadas con Oracle, incluida la ruta en la variable PATH (dejando por supuesto el resto de ruta del PATH) y las referencias al JDK (kit de desarrollo en Java, que se instala con Oracle) 6

Tras hacer todos los pasos anteriores, al reiniciar el PC, Oracle estará correctamente desinstalado. Borrar las carpetas de Oracle Tras reiniciar se podrán borrar las carpetas que tienen los residuos de Oracle.Las carpetas a borrar son la carpeta de Oracle (en la ruta indicada en su instalación, por ejemplo C:\Oracle), la carpeta Oracle en Archivos de programa (normalmente en el disco C) y otras carpetas relacionadas. INSTALACION DE ORACLE Antes de instalar hay que tener en cuenta que debemos asegurar que disponemos de 400 MB en el disco duro. Este espacio es temporal. Por defecto se tomará en la carpeta TEMP del sistema, hay que comprobar de que el disco duro en el que se encuentra esa carpeta dispone de se espacio, de otro modo habrá que cambiar la ubicación de TEMP a otro disco duro con ese espacio. Para cambiar la ubicación de esa carpeta se debe modificar la ruta de la variable de entorno temp (desde el apartado de variables de entorno en Sistema en el Panel de control Pasos en la instalación Tras lanzar la instalación, se ejecuta el Universal Installer de Oracle, el programa Java encargado de realizar las instalaciones y desinstalaciones. Tras su ejecución ocurre lo siguiente: 1. Aparecerá la ventana de bienvenida del instalador. Pulsar Siguiente

7

2. Elegir una ruta para la carpeta Home de Oracle. Esa carpeta es la encargada de almacenar el software de base de datos de Oracle. Se nos pregunta qué nombre tendrá esa carpeta Home (por defecto se llama OraHome92) y la ubicación de la misma (normalmente dentro de la carpeta Oracle en el disco con más espacio disponible). Pulsar Siguiente 7

3. Aparece una lista con las posibles instalaciones que permite el CD. Lógicamente se elegirá Database si es la base de datos lo que queremos instalar.

8

4. La opción “Client” permite instalar sólo las aplicaciones necesarias para utilizar un servidor Oracle previamente instalado (la instalación cliente puede instalar herramientas de Administración o herramientas Runtime, para desarrolladores). 5. En la siguiente pantalla se nos pregunta qué versión de Oracle instalaremos. Se nos ofrecen tres posibilidades: Versión Enterprise. Versión con todas las posibilidades de Oracle Versión Estándar. Versión para grupos de trabajo o aplicaciones de un solo departamento. Versión Personal. Como la Enterprise pero para un solo usuario y ordenador Personalizada. Permite clarificar mejor la forma de instalar Oracle (es la más complejo, pero la que más opciones permite) Elegir nuestra opción y pulsar Siguiente

9

6. Se nos pregunta (salvo que hayamos elegido la versión personalizada) por el tipo de base de datos que se creará en la instalación. Ya en la instalación se crea una base de datos que puede ser: Propósito general. Bases de datos que se utilizan para todo tipo de tareas (ante la duda conviene utilizar esta opción). Procesamiento de transacciones, preparada para utilizar transacciones muy a menudo. Almacenes de datos, base de datos pensada para primar el almacenamiento de grandes volúmenes de datos (Warehousing). Personalizada, permite especificar más claramente la base de datos a utilizar (esta opción requiere más pasos que las otras) Sólo software, instala el software de base de datos sin crear una primera base de datos (habrá que crearla más adelante.

10

7. Elegir la opción preferida (ante la duda elegir uso General) y pulsar Siguiente. 8. Si se está migrando de una versión anterior aparecerá un cuadro que nos permite migrar (o no) los datos de la versión anterior. 9. Si el equipo tiene Microsoft Transaction Server se nos pedirá el número de puerto (normalmente el 2030) que ha de utilizarse para usar dicho software de transacciones. 10. Se nos pide el identificador (SID, System Identifier) y el nombre global de la base de datos. El nombre global es un nombre único para la red a la que pertenece

el

servidor

en

el

que

instalamos

Oracle

(por

ejemplo

inicial.donbosdco.local). El SID es el nombre que identifica a la base de datos en el ordenador en el que estamos. En el cuadro se pone primero el nombre global el instalador sugiere el SID correspondiente. Pulsar Siguiente

11. Indicar la ruta en la que se instalarán por defecto los archivos de la base de datos. Es conveniente que esta carpeta esté en una unidad distinta a la de la carpeta Home de Oracle, para acelerar el rendimiento. Esta carpeta se suele llamar OraData.

11

12. Indicar el juego de caracteres que se utilizará en la base de datos. Normalmente se toma el juego de caracteres que utiliza el sistema operativo. Este dato si se rellena mal ocasiona que algunos de nuestros caracteres nacionales, nunca se muestren adecuadamente.

12

13. Tras aceptar el cuadro anterior se nos muestra un resumen de la instalación. Tras este resumen comienza la copia de los archivos en nuestro disco duro. Este es el paso más largo, se pueden tardar bastantes minutos (incluso alguna hora) en completarse).

14. En segundo plano se configurarán los servicios de red necesarios para el funcionamiento de Oracle, la base de datos y el servidor http de Oracle. (Ver imagen anterior). 15. En cuanto se instala la base de datos y se lanza la instancia asociada a ella (la instancia es la base de datos en ejecución, desde ese instante al arrancar Windows la instancia estará en memoria salvo que la detengamos) se nos piden las contraseñas asociadas a los usuarios administrativos SYS y SYSTEM. Es muy importante recordarlas y no perderlas.

13

16. Tras el paso anterior se lanza el Agente de Oracle (Oracle Intelligent Agent) y el servidor http. Al final se indicarán los puertos necesarios para comunicar con el servidor http de Oracle (añadiendo el texto /isqlplus a esa dirección se podrá conectar desde un navegador con el servidor Oracle vía http). En el caso de instalaciones más personalizadas se nos preguntarán más datos sobre la configuración de la base de datos y el servidor http, pero eligiendo las opciones indicadas en este manual, no habrá más preguntas.

14

17. En el caso de instalaciones más personalizadas se nos preguntarán más datos sobre la configuración de la base de datos y el servidor http, pero eligiendo las opciones indicadas en este manual, no habrá más preguntas. Prueba de la instalación. Para comprobar si la instalación es correcta, se debe hacer lo siguiente. 1. Ir al grupo de programas de Oracle en Windows (normalmente InicioProgramas-Oracle Home92) y hacer clic sobre Enterprise Manage Console (consola de administración). 2. Activar la casilla Iniciar en modo autónomo y aceptar el cuadro

3. Desde el programa de administración, expandir el apartado Bases de datos. Aparecerán las bases que hemos creado, después se nos pedirá un nombre de usuario y contraseña para conectarnos con la base de datos. Inicialmente habrá que conectar con el usuario SYSTEM y la contraseña que especificamos para dicho usuario en la instalación indicar que conectamos como SYSDBA (administrador) lo que nos permite realizar todo tipo de tareas.

15

4. Si aparecen subapartados dentro de la base de datos (Instancia, Seguridad,...) es que hemos conectado correctamente. Podremos realizar cualquier operación de administración sobre la base de datos.

Conectar mediante iSQL*Plus Durante la instalación se habrá iniciado un servidor http para Oracle. Para verificar que funciona habrá que probar si podemos conectar con él. Para ello, simplemente hay que abrir un navegador cualquiera (Explorer por ejemplo) y escribir http:// seguido del nombre de nuestro ordenador dos puntos y el número de puerto en el que se instaló el servidor (se nos indicó el mismo durante la instalación, véase paso 16 de la instalación), por ejemplo http://ordenata:7778. Si sale bien aparecerá la pantalla del servidor Apache. Si

a

la

dirección

anterior

le

añadimos

/isqlplus

(por

http://ordenata:7778/isqlplus) entonces aparece la pantalla inicial de iSQL*Plus:

16

ejemplo

Desde esa pantalla se indica el nombre de usuario, contraseña y nombre global de la base de datos a la que conectamos. Eso nos permite un interfaz muy cómodo para trabajar con Oracle. Breve historia de Oracle 1978 Primera versión del sistema Oracle para el sistema PDP-11 de Digital Equipment. Se basaba en las tesis de E. F. Codd y se convierte en el primer RDBMS comercial 1980 Se crea Oracle Systems, empresa propietaria del DBMS Oracle 1981 Versión 3 de Oracle, escrita en C (2,5 millones de dólares en ventas) 1984 Versión 4, conexión cliente / servidor en sistema VAX/VMS. 13 millones de ventas arquitectura del DBMS Oracle aproximación a Oracle 1986 Versión 5 de Oracle, posibilidad de capacidades distribuidas. 55 millones en ventas 1988 Versión 6, incluye herramienta CASE, 188 millones de ventas 1991 Se alcanzan los mil millones de dólares 1993 Versión 7 de Oracle para el sistema Unix. Se convierte en el mejor gestor de bases de datos enormes 1994 2000 millones de ventas, versión de Oracle para PC 1997 Versión 8, se lanza hacia Internet y adopta Java. 5000 millones en ventas 1998 Versión de Oracle para Linux

17

1999 Versión 8i, mayor integración de Java e Internet. 8000 millones en ventas 2000 Más de 10.000 millones en ventas, primera solución de bases de datos del mercado 2001 Aparece la versión 9i con grandes mejoras en la distribución 2003 Aparece la versión 10g, con grandes mejoras en cuanto al tamaño de Almacenamiento y velocidad. otros servidores Los rivales de Oracle fundamentales hoy en día son: Ingres de Computer Associates DB/2 de IBM SQL Server de Microsoft ADABAS de Software AG Informix PostgreSQL (de código abierto) Estrategia de Oracle Oracle es una empresa que fabrica diversos productos. Su idea es aportar un producto autosuficiente para el mantenimiento de datos y la creación de aplicaciones basados en estos. Sus tres productos más importantes son: Oracle DataBase. El DBMS Oracle, junto con las herramientas fundamentales para hacer de servidor y los programas clientes necesarios para conectar clientes Oracle Application Server. Servidor de aplicaciones para la creación de programas distribuidos. Oracle Developer Suite. Programas para la generación de aplicaciones rápidas basadas en bases de datos Oracle. servidor Oracle. Un servidor Oracle es el software que permite una administración y desarrollo de bases de datos. Tiene tres posibilidades de ejecución: Local o basada en host. El servidor se ejecuta en la misma máquina en la que se conectan los clientes. La versión personal de Oracle database, produce servidores de este tipo. Cliente-Servidor. Enfoque más típico. El servidor reside en un ordenador distinto respecto al que los usuarios van a usar para conectarse a la base de datos. Cliente-Servidor de Aplicaciones-Servidor. Los usuarios acceden a un Servidor de aplicaciones (Oracle Application Server) que, a su vez, accede al servidor Oracle. Los tres elementos (cliente, servidor de aplicaciones, servidor Oracle) pueden estar en tres máquinas distintas.

18

Elementos del servidor Oracle El servidor Oracle está formado por dos elementos: La instancia de la base de datos. Consta de datos (llamados estructuras de memoria) y de procesos en memoria (procesos background) necesarios para dar servicio a los usuarios de la base de datos. Puede haber más de una instancia si se distribuye la base de datos en más de una máquina. Cada instancia abre una y sólo una base de datos. Ficheros en disco. Representan la base de datos en sí. Consta de: Estructuras lógicas: Tablespaces, objetos del esquema de usuario. Estructuras físicas: Los ficheros de datos almacenados en disco. Los ficheros de datos (asociados a los tablespaces), los ficheros redo log y los ficheros de control Conexiones Para establecer una sesión con la base de datos, el usuario necesita conectar con la instancia de la base de datos. Normalmente esto significa arrancar una herramienta cliente como SQL*Plus o ejecutar una aplicación de desarrollo de bases de datos (como Oracle Forms); entonces se ejecuta un proceso de usuario. Cuando esto ocurre, en el servidor se establece un proceso de servidor. Este proceso es el encargado de comunicar al usuario con la instancia Oracle en nombre del proceso de usuario. Cada vez que el usuario ejecuta instrucciones SQL, éstas son transmitidas a la instancia Oracle por el proceso servidor. De este modo una conexión es un camino entre un proceso de usuario y un servidor Oracle. Cada sesión es una conexión de un usuario con el servidor Oracle. Un usuario puede establecer múltiples sesiones (si se conecta desde diferentes herramientas y máquinas). Estructura de las bases de datos Oracle Desde el punto de vista de Oracle, una base de datos es una colección de datos tratados como una única unidad. Una base de datos Oracle contiene tres tipos de ficheros: Archivos de datos. Contiene los datos actuales de la base de datos así como el diccionario de datos. Archivos rehacer (redo logs). Almacenan datos recuperables en caso de error grave. Archivos de control. Necesarios para mantener la integridad de la base de datos. Además se utilizan otros archivos de forma auxiliar 19

Archivos de parámetros. Que definen algunas características de una instancia Oracle. Archivos de contraseñas. Que sirven para autentificar a los usuarios. Copias de archivos rehacer. Utilizadas para la recuperación de datos. Instancia de la base de datos Usuario 1 Usuario 2 Usuario 3 Usuario n System Global Área (SGA) Estructuras de memoria Zona compartida (Shared pool) Library Cache Caché del diccionario de datos Buffer de la base de datos Buffer de los archivos rehacer Otras Procesos background Procesamiento de instrucciones SQL Para poder ejecutar SQL sobre la base de datos, hay que conectar con la instancia Oracle de la base de datos, lo cual requiere la comunicación entre un proceso cliente y el servidor (el proceso cliente puede ser una instancia de SQL*Plus por ejemplo). Los componentes utilizados por Oracle para procesar el SQL dependen del código enviado:  Las consultas devuelven filas  Las instrucciones DML (Lenguaje de Manipulación de Datos) graban cambios  la instrucción commit asegura el proceso de la transacción Pero de manera general los pasos en ese proceso son: 1. El usuario abre la herramienta que permite el envío de peticiones SQL (por ejemplo SQL*Plus). 2. El usuario introduce su nombre de usuario y contraseña Arquitectura del DBMS Oracle servidor Oracle 3. Oracle consulta el diccionario de datos para verificar la existencia del usuario y para validar su permiso de conexión. Si lo tiene, se produce la conexión. 4. El usuario escribe la instrucción SQL (supongamos que es una instrucción de modificación) 5. Oracle traduce la instrucción con el analizador de instrucciones (devolvería un error si la instrucción no es válida) 6. Oracle traduce los nombres usados en la instrucción con la ayuda del

20

diccionario de datos. 7. Si es una instrucción de mostrar datos (SELECT), comprueba si otros usuarios han enviado hace poco esa misma instrucción, eso lo comprueba en el caché de instrucciones de la SGA. Si la instrucción está ahí coge los resultados del buffer caché de la base de datos. 8. Si la instrucción conlleva cambios, el servidor bloquea las filas que se modificarán. 9. La base de datos graba los cambios (si les hubo) y actualiza los archivos deshacer 10. La base de datos graba los nuevos valores para los datos 11. Oracle libera del bloqueo los registros 12. El usuario recibe un mensaje de éxito Estructura lógica Está formada por: Tablespaces. Pertenecen sólo a una base de datos y sirven para agrupar los datos de la base de datos. Cada tablespace está formado físicamente por uno o más archivos de datos. Están divididos en 0 o más segmentos. Se pueden visualizar en línea o fuera de línea y pueden ser activados en sólo lectura o en lectura / escritura. Segmento. Sirven para almacenar las estructuras lógicas de la base de datos (tablas, índices,...). Un tablespace se compone de uno o más segmentos. Pero el mismo segmento no puede estar en más de un tablespace. Extensiones. División que se hace a cada segmento. El DBA puede añadir o quitar extensiones a los segmentos a fin de hacer que ganen o pierdan espacio. arquitectura del DBMS Oracle conceptos del sistema Bloque Oracle o bloque de datos. Es la unidad mínima de datos para Oracle y se corresponde a una o más unidades de datos mínimas del sistema operativo en el que nos encontremos. Estructura física Archivos de datos. Son archivos en disco que sirven para almacenar los datos físicamente (en una unidad de disco). Cada archivo de datos pertenece sólo a un tablespace. Su tamaño se puede gestionar. Bloques de sistema. La división mínima de los datos que hace el sistema 21

operativo. Transacciones Los cambios en la base de datos no son guardados hasta que tras una serie de instrucciones se decide llevar a cabo esos cambios. Hasta ese momento todo lo realizado se toma como provisional. Un fallo en la máquina permitiría invertir los cambios. Una transacción son varias operaciones SQL que forman una unidad de trabajo. Comienza cuando una persona se conecta y de ahí hasta que ejecuta la instrucción Commit (ejecutar la transacción) o rollback (anular la transacción). La anulación deja la base de datos en el estado anterior al comienzo de la transacción. Tras un commit o un rollback comienza la siguiente transacción. En Oracle se admite además el uso de puntos de ruptura (checkpoints) para almacenar valores intermedios y volver a cualquier de ellos si interesa. Pero esto ralentiza excesivamente el sistema. Usuarios Los usuarios son las personas que acceden de una forma o de otra a la base de datos. Cada usuario tiene una vista determinada de la base de datos. Hay varios conceptos sobre los usuarios a tener en cuenta. Privilegios Controlan el permiso que posee un usuario de ejecutar una determinada instrucción SQL. Un usuario que quiera crear una tabla, deberá tener el privilegio (o permiso) adecuado para ello. Además se pueden colocar privilegios en los objetos, es decir, un usuario propietario de una tabla puede otorgar privilegios a esa tabla (se trataría de un privilegio a nivel de objeto) para que haya otros usuarios que la puedan usar. rol Son agrupaciones de privilegios que facilitan la tarea de gestionar a los usuarios. Así cuando una serie de usuarios van a tener los mismos privilegios, se crea un rol que contenga esos privilegios y a esos usuarios se les asigna el rol. Oracle proporciona varios roles ya preparados, por ejemplo el rol DBA da privilegio absoluto a un usuario. Copyright-Copyleft: © Jorge Sánchez 2004 esquemas Los esquemas están asociados a los usuarios. Agrupan los objetos lógicos que pertenecen al usuario. Es decir es el conjunto de tablas, vistas, sinónimos, instantáneas, enlaces de base de datos, procedimientos y funciones, paquetes,... Cada usuario tiene su propio esquema y, en principio, un usuario no tiene acceso a los elementos de otro usuario, salvo que sea un administrador o que otro usuario ceda el 22

privilegio de utilización de uno o más de sus objetos al resto de usuarios. pérdidas de información Es una de las tareas y herramientas fundamentales que nos proporcionan las bases de datos. Hay posibilidad de perder datos de nuestra base de datos por alguna de estas razones: Fallo en una instrucción. Hay instrucciones que pueden provocar la pérdida no deseada de cientos de registros en un momento (DELETE o UPDATE). En ese caso basta ejecutar un rollback antes de que la instrucción se lleve a cabo. Fallo en la comunicación. Si una conexión de usuario se corta anulando el proceso de usuario relacionado. En ese caso, Oracle anula los cambios de la última transacción (el resto de transacciones sí se almacena). Caída del servidor. Puede que la instancia Oracle se deje de ejecutar. En ese caso basta con lanzar de nuevo la instancia. El proceso SMON se encargará de grabar los archivos rehacer y aplica de nuevo las transacciones confirmadas. Se anulan los cambios no confirmados. Pérdida de datos en los archivos. Es el único caso en el que tiene que intervenir el administrador. La única posibilidad es recuperarles de una copia de seguridad Herramientas de Oracle El software del sistema de bases de datos Oracle incorpora herramientas para realizar la mayoría de tareas comunes en una base de datos: Oracle Universal Installer. gestor de instalaciones, controla cada nueva instalación de software Oracle a fin de que se integren de la mejor manera posible SQL*plus. Programa cliente que permite conexión con el servidor Oracle para enviarle secuencias SQL y PL/SQL iSQL*plus. Permite conexiones al servidor Oracle con la misma finalidad que el anterior pero utilizando una navegador de Internet, lo que facilita el trabajo SQL*plus WorkSheet. Permite conexiones al servidor de Oracle, utilizando un entorno más potente (procede del Oracle Enterprise Manager) Oracle Enterprise Manager. Entorno que permite la administración y configuración completa del servidor Oracle. SQL*Loader. Permite cargar en bases de datos de Oracle información que procede de un archivo de texto. Necesaria para utilizar en las bases de datos de Oracle, información que procede de otro software. Import/Export. Para importar y exportar datos entre instancias de Oracle. De un 23

servidor a otro por ejemplo. también se utiliza como herramienta de copia de seguridad. Servidor http de Oracle. Basado en el servidor Apache, permite opciones de documentación y sobre todo la comunicación directa a través de iSQL*Plus con el servidor Oracle sin tener necesidad de instalar software adicional. Copyright-Copyleft: © Jorge Sánchez 2004 Net Manager. Permite la administración de los servicios de red a fin de configurar las conexiones hacia instancias de Oracle. Oracle Forms. Permite crear aplicaciones visuales sobre bases de datos de Oracle Oracle Reports. Asistente para la producción de informes Oracle Designer. Herramienta CASE de Oracle, para crear esquemas en el ordenador y que el software produzca los resultados del mismo Oracle JDeveloper. Crea aplicaciones Java pensadas para desarrollar formularios sobre datos de Oracle Oracle Developer Suite. Integra todos los componentes anteriores Oracle AS (Application Server). Servidor de aplicaciones de Oracle. Permite compilar aplicaciones J2EE Pro C/C++ Precompilador de C/C++ para Oracl

Manual de SQL para Oracle 9i En este manual en muchos apartados se indica sintaxis de comandos. Esta sintaxis sirve para aprender a utilizar el comando, e indica la forma de escribir dicho comando en el programa utilizado para escribir SQL. Ejemplo: SELECT * | {[DISTINCT] columna | expresión [alias], ...} FROM tabla;

Otras veces se describen códigos de ejemplo de un comando. Los ejemplos se escriben también con fondo gris, pero sin el reborde. Ejemplo: SELECT nombre FROM cliente; Los ejemplos sirven para escenificar una instrucción concreta, la sintaxis se utiliza para indicar las posibilidades de un comando. Para indicar la sintaxis de un comando se usan

24

símbolos especiales. Los símbolos que utiliza este libro (de acuerdo con la sintaxis que se utiliza normalmente en cualquier documentación de este tipo) son: PALABRA Cuando en la sintaxis se utiliza una palabra en negrita, significa que es un comando que hay que escribir literalmente. texto El texto que aparece en cursiva sirve para indicar que no hay que escribirle literalmente, sino que se refiere a un tipo de elemento que se puede utilizar en el comando. Ejemplo: SELECT columna FROM tabla; El texto columna hay que cambiarlo por un nombre concreto de columna (nombre, SELECT { * | columna | expresión } FROM tabla; SQL*Plus Para poder escribir sentencias SQL al servidor Oracle, éste incorpora la herramienta SQL*Plus. Toda instrucción SQL que el usuario escribe, es verificada por este programa. Si la instrucción es válida es enviada a Oracle, el cual enviará de regreso la respuesta a la instrucción; respuesta que puede ser transformada por el programa SQL*Plus para modificar su salida. Para que el programa SQL*Plus funcione en el cliente, el ordenador cliente debe haber sido configurado para poder acceder al servidor Oracle. En cualquier caso al acceder a Oracle con este programa siempre preguntará por el nombre de usuario y contraseña. Estos son datos que tienen que nos tiene que proporcionar el administrador (DBA) de la base de datos Oracle. Para conectar mediante SQL*Plus podemos ir a la línea de comandos y escribir el texto sqlplus. A continuación aparecerá la pantalla:

25

de datos (información que deberá indicarnos el administrador o DBA). Tras indicar esa Información conectaremos con Oracle mediante SQL*Plus, y veremos aparecer el símbolo: SQL> Tras el cual podremos comenzar a escribir nuestros comandos SQL. Ese símbolo puede cambiar por un símbolo con números 1, 2, 3, etc.; en ese caso se nos indica que la instrucción no ha terminado y la línea en la que estamos. Otra posibilidad de conexión consiste en llamar al programa SQL*Plus indicando contraseña y base de datos a conectar. El formato es: slplus usuario/contraseña@nombreServicioBaseDeDatos Ejemplo: slplus usr1/[email protected] En este caso conectamos con SQL*Plus indicando que somos el usuario usr1 con contraseña miContra y que conectamos a la base de datos inicial de la red forempa.net. El nombre de la base de datos no tiene porque tener ese formato, habrá que conocer como es el nombre que representa a la base de datos como servicio de red en la red en la que estamos. Versión gráfica de SQL*Plus Oracle incorpora un programa gráfico para Windows para utilizar SQL*Plus. Se puede llamar a dicho programa desde las herramientas instaladas en el menú de programas de Windows, o desde la línea de programas escribiendo sqlplusw. Al llamarle aparece esta pantalla:

26

Como en el caso anterior, se nos solicita el nombre de usuario y contraseña. La cadena de Host es el nombre completo de red que recibe la instancia de la base de datos a la que queremos acceder en la red en la que nos encontramos. También podremos llamar a este entorno desde la línea de comandos utilizando la sintaxis comentada anteriormente. En este caso: slplusw usuario/contraseña@nombreServicioBaseDeDatos por el nombre de usuario y contraseña. iSQL*Plus Es un producto ideado desde la versión 9i de Oracle. Permite acceder a las bases de datos Oracle desde un navegador. Para ello necesitamos tener configurado un servidor web Oracle que permita la conexión con la base de datos. Utilizar iSQL*Plus es indicar una dirección web en un navegador, esa dirección es la de la página iSQL*Plus de acceso a la base de datos. Desde la página de acceso se nos pedirá nombre de usuario, contraseña y nombre de la base de datos con la que conectamos (el nombre de la base de datos es el nombre con el que se la conoce en la red). Si la conexión es válida aparece esta pantalla:

27

Ilustración 1, Pantalla de iSQL*Plus una vez conectados a una base de datos En esa pantalla en el apartado Introducir Sentencias, se escribe la sentencia que deseamos enviar. El botón Ejecutar hace que se valide y se envíe a Oracle. Se pueden almacenar sentencias SQL usando el botón Examinar y cargar sentencias

Previamente guardadas mediante Cargar archivos de comandos. Estructura del lenguaje SQL SELECT. Se trata del comando que permite realizar consultas sobre los datos de la base de datos. Obtiene datos de la base de datos. DML, Data Manipulation Language (Lenguaje de manipulación de datos). Modifica filas (registros) de la base de datos. Lo forman las instrucciones 28

INSERT, UPDATE, MERGE y DELETE. DDL, Data Definition Language (Lenguaje de definición de datos). Permiten modificar la estructura de las tablas de la base de datos. Lo forman las instrucciones CREATE, ALTER, DROP, RENAME y TRUNCATE. Instrucciones de transferencia. Administran las modificaciones creadas por las instrucciones DML. Lo forman las instrucciones ROLLBACK, COMMIT y SAVEPOINT DCL, Data Control Language (Lenguaje de control de datos). Administran los derechos y restricciones de los usuarios. Lo forman las instrucciones GRANT y REVOKE. Normas de escritura En SQL no se distingue entre mayúsculas y minúsculas. Da lo mismo como se escriba. El final de una instrucción lo calibra el signo del punto y coma Los comandos SQL (SELECT, INSERT,...) no pueden ser partidos por espacios o saltos de línea antes de finalizar la instrucción. El intérprete SQL plus indicas Se pueden tabular líneas para facilitar la lectura si fuera necesario Los comentarios en el código SQL comienzan por /* y terminan por tablas Esquemas de usuario y objetos Cada usuario de una base de datos posee un esquema. El esquema tiene el mismo nombre que el usuario y sirve para almacenar los objetos de esquema, es decir los objetos que posee el usuario. Esos objetos pueden ser: tablas, vistas, secuencias, índices, sinónimos e instantáneas. Esos objetos son manipulados y creados por los usuarios. En principio sólo los administradores y los usuarios propietarios pueden acceder a cada objeto, salvo que se modifiquen los privilegios del objeto para permitir su acceso por parte de otros usuarios. Creación de tablas Nombre de las tablas Deben cumplir las siguientes reglas:  Deben comenzar con una letra  No deben tener más de 30 caracteres

29

 Sólo se permiten utilizar letras del alfabeto (inglés), números o el signo de subrayado (también el signo $ y #, pero esos se utilizan de manera especial por lo que no son recomendados).  No puede haber dos tablas con el mismo nombre para el mismo usuario (pueden coincidir los nombres si están en distintos esquemas)  No puede coincidir con el nombre de una palabra reservada de Word orden CREATE TABLE  Es la orden SQL que permite crear una tabla. Por defecto será almacenad en el tablespace por defecto del usuario que crea la tabla. Sintaxis: CREATE TABLE [esquema.] nombreDeTabla (nombreDeLaColumna1 tipoDeDatos [, ...]); Ejemplo: CREATE TABLE proveedores (nombre varchar2(25)); Crea una tabla con un solo campo de tipo varchar2. Sólo se podrá crear la tabla si el usuario posee los permisos necesarios para ello. Si la tabla pertenece a otro esquema (suponiendo que el usuario tenga permiso para grabar tablas en ese otro esquema), se antepone al nombre de la tabla , el nombre del esquema: CREATE TABLE otroUsuario.proveedores (nombre varchar2(25)); Orden DESCRIBE El comando DESCRIBE, permite obtener la estructura de una tabla. Ejemplo: DESCRIBE proveedores; Y aparecerán los campos de la tabla proveedores. Orden INSERT Permite añadir datos a las tablas. Más adelante se comenta de forma más detallada. Su sintaxis básica es: INSERT INTO tabla [(columna1 [, columna2...])] VALUES (valor1 [,valor2]); Indicando la tabla se añaden los datos que se especifiquen tras el apartado values en un nuevo registro. Los valores deben corresponderse con el orden de las columnas. Si no es así se puede indicar tras el nombre de la tabla y entre paréntesis. Ejemplo: INSERT INTO proveedores(nombre, CIF) VALUES (‘Araja SA’,’14244223Y’);

30

Consultar las tablas del usuario En el diccionario de datos hay una entrada que permite consultar las tablas de cada usuario. Esa entrada es USER_TABLES. De forma que SELECT * FROM USER_TABLES obtiene una vista de las tablas del usuario actual. Hay diversas columnas que muestran datos sobre cada tabla, entre ellas la columna TABLES_NAME muestra el nombre de la tabla. Borrar tablas La orden DROP TABLE seguida del nombre de una tabla, permite eliminar la tabla en cuestión. Al borrar una tabla:  Desaparecen todos los datos  Cualquier vista y sinónimo referente a la tabla seguirán existiendo, pero ya no funcionarán (conviene eliminarlos)  Las transacciones pendientes son aceptadas (COMMIT)  Sólo es posible realizar esta operación si se es el propietario de la tabla o se posee el privilegio DROP ANY TABLE El borrado de una tabla es irreversible, y no hay ninguna petición de confirmación, por lo que conviene ser muy cuidadoso con esta operación. Tipos de datos Equivalentes ANSI SQL con el SQL de Oracle Hay diferencias entre los tipos de datos del estándar ANSI con respecto al SQL de Oracle. Aunque Oracle es capaz de utilizar bases de datos con formato ANSI y tipos compatibles con el mismo, la equivalencia ANSI / Oracle la dicta esta tabla: Tipos ANSI SQL Equivalente Oracle SQL CHARACTER(n) CHAR(n) CHAR(n) CHARACTER VARYING(n)

31

CHAR VARYING(n) VARCHAR(n) NATIONAL CHARACTER(n) NATIONAL CHAR(n) NCHAR(n) NCHAR(n) NATIONAL CHARACTER VARYING(n) NATIONAL CHAR VARYING(n) NCHAR VARYING(n) NVARCHAR2(n) NUMERIC(p,s) DECIMAL(p,s) NUMBER(p,s) INTEGER INT SMALLINT NUMBER(38) FLOAT(b) DOUBLE DOUBLE PRECISSION REAL NUMBER LONG VARCHAR(n) LONG textos Para los textos disponemos de los siguientes tipos:  VARCHAR2. Para textos de longitud variable de hasta 4000 caracteres  CHAR. Para textos de longitud fija de hasta 2000 caracteres.  NCHAR. Para el almacenamiento de caracteres nacionales de texto fijo  NVARCHAR2. Para el almacenamiento de caracteres nacionales de longitud Variable. En todos estos tipos se indican los tamaños entre paréntesis tras el nombre del tipo. Ese tamaño en el caso de los tipos VARCHAR2 es obligatorio, en el caso de los tipos CHAR son opcionales (de no ponerlos se toma el uno).

32

Conviene poner suficiente espacio para almacenar los valores. En el caso de los VARCHAR, Oracle no malgasta espacio por poner más espacio del deseado ya que si el texto es más pequeño que el tamaño indicado, el resto del espacio se ocupa. números El tipo NUMBER es un formato versátil que permite representar todo tipo de números. Su rango recoge números de entre 10-130 y 9,99999999999 * 10128. Fuera de estos rangos Oracle devuelve un error. Los números decimales (números de coma fija) se indican con NUMBER(p,s), donde p es la precisión máxima y s es la escala (número de decimales a la derecha de la coma). Por ejemplo, NUMBER (8,3) indica que se representan números de ocho cifras de precisión y tres decimales. Los decimales en Oracle se presenta con el punto y no con la coma. Para números enteros se indica NUMBER(p) donde p es el número de dígitos. Eso es equivalente a NUMBER(p,0). Para números de coma flotante (equivalentes a los flota o double de muchos lenguajes de programación) simplemente se indica el texto NUMBER sin precisión ni escala. precisión y escala La cuestión de la precisión y la escala es compleja. Para entenderla mejor, se muestran estos ejemplos: Formato Número escrito por el usuario Se almacena como… NUMBER 345255.345 345255.345 NUMBER(9) 345255.345 345255 NUMBER(9,2) 345255.345 345255.36 NUMBER(7) 345255.345 Da error de precisión NUMBER(7,-2) 345255.345 345300 NUMBER(7,2) 345255.345 Da error de precisión En definitiva, la precisión debe incluir todos los dígitos del número (puede llegar hasta 38 dígitos). La escala sólo indica los decimales que se respetarán del número, pero si es negativa indica ceros a la izquierda del decimal. tipo LONG Se trata de un tipo de datos que actualmente se mantiene por compatibilidad. Se recomienda encarecidamente utilizar en su lugar el tipo CLOB (que se comentará más

33

adelante). En cualquier caso este tipo permite almacenar textos de hasta 2 GB de tamaño. Pero no puede formar clave, ni índice, ni ser parte de la cláusula WHERE, ni GROUP BY, ni SELECT con DISTINCT, ni pueden ser UNIQUE y sólo puede haber un campo de este tipo en una misma tabla entre otras limitaciones. fechas y horas DATE El tipo DATE permite almacenar fechas. Las fechas se pueden escribir en formato día, mes y año entre comillas. El separador puede ser una barra de dividir, un guión y casi cualquier símbolo. Para almacenar la fecha actual basta con utilizar la función SYSDATE que devuelve esa fecha.

TIMESTAMP. Es una extensión del anterior, almacena valores de día, mes y año, junto con hora, minuto y segundos (incluso con decimales). Con lo que representa un instante concreto en el tiempo. Un ejemplo de TIMESTAMP sería ‘2/2/2004 18:34:23,34521’. En este caso si el formato de fecha y hora del sistema está pensado para el idioma español, el separador decimal será la coma (y no el punto). intervalos Hay unos cuantos tipos de datos en Oracle que sirven para almacenar intervalos de tiempo (no fechas, sino una suma de elementos de tiempo). INTERVAL YEAR TO MONTH. Este tipo de datos almacena años y meses. Tras la palabra YEAR se puede indicar la precisión de los años (cifras del año), por defecto es de dos. Ejemplo: CREATE TABLE tiempos (meses INTERVAL YEAR(3) TO MONTH); INSERT INTO tiempos VALUES(’3-2’); En el ejemplo se inserta un registro que representa 3 años y dos meses. INTERVAL DAY TO SECOND. Representa intervalos de tiempo que expresan días, horas, minutos y segundos. Se puede indicar la precisión tras el texto DAY y el número de decimales de los segundos tras el texto SECOND. Ejemplo: CREATE TABLE tiempos (dias INTERVAL DAY(3) TO SECOND(0)); INSERT INTO tiempos VALUES(’2 7:12:23’); RAW. Sirve para almacenar valores binarios de hasta 2000 bytes (se puede especificar el tamaño máximo entre paréntesis). El valor LONG RAW almacena hasta 2GB. 34

LOB. Son varios tipos de datos que permiten almacenar valores muy grandes. Más adelante se comentan en su totalidad. Incluye a BLOB, CLOB, NCLOB y BFILE. ROWID. Valor hexadecimal que representa la dirección única de una fila en su tabla. RENAME. Permite el cambio de nombre de cualquier objeto. Sintaxis: RENAME nombreViejo TO nombreNuevo borrar contenido de tablas La orden TRUNCATE TABLE seguida del nombre de una tabla, hace que se elimine el contenido de la tabla, pero no la tabla en sí. Incluso borra del archivo de datos el espacio ocupado por la tabla. Esta orden no puede anularse con un ROLLBACK. Modificar tablas. ALTER TABLE.

Permite hacer cambios en la estructura de una tabla. añadir

columnas ALTER TABLE nombreTabla ADD(nombreColumna TipoDatos [Propiedades] [,columnaSiguiente tipoDatos [propiedades]...) Permite añadir nuevas columnas a la tabla. Se deben indicar su tipo de datos y sus propiedades si es necesario (al estilo de CREATE TABLE). Las nuevas columnas se añaden al final, no se puede indicar otra posición. borrar columnas ALTER TABLE nombreTabla DROP(columna); Elimina la columna indicada de manera irreversible e incluyendo los datos que contenía. No se puede eliminar la última columna (habrá que usar DROP TABLE). modificar columna Permite cambiar el tipo de datos y propiedades de una determinada columna. Sintaxis: ALTER TABLE nombreTabla MODIFY(columna tipo [propiedades] [columnaSiguiente tipo [propiedades] ...] Los cambios que se permiten son: Permite añadir nuevas columnas a la tabla. Se deben indicar su tipo de datos y sus propiedades si es necesario (al estilo de CREATE TABLE). Las nuevas columnas se añaden al final, no se puede indicar otra posición.  Incrementar precisión o anchura de los tipos de datos  Sólo se puede reducir la anchura si la anchura máxima de un campo si esa columna posee nulos 35

Borrar columnas ALTER TABLE nombreTabla DROP(columna); Elimina la columna indicada de manera irreversible e incluyendo los datos que contenía. No se puede eliminar la última columna (habrá que usar DROP TABLE). modificar columna Permite cambiar el tipo de datos y propiedades de una determinada columna. Sintaxis: ALTER TABLE nombreTabla MODIFY(columna tipo [propiedades] [columnaSiguiente tipo [propiedades] ...] Los cambios que se permiten son:  Incrementar precisión o anchura de los tipos de datos  Sólo se puede reducir la anchura si la anchura máxima de un campo si esa columna Posee nulos en todos los registros, o todos los valores so o no hay registros  Se puede pasar de CHAR a VARCHAR2 y viceversa (si no se modifica la anchura)  Se puede pasar de DATE a TIMESTAMP y viceversa Añadir comentarios a las tablas Se le pueden poner comentarios a las tablas y las columnas. Un comentario es un texto descriptivo utilizado para documentar la tabla. Sintaxis: COMMENT ON { TABLE NombreTabla | COLUMN tabla.nombreColumna } IS ‘Comentario’ Para mostrar los comentarios puestos se usan las siguientes vistas del diccionario de datos mediante la instrucción SELECT:  USER_TAB_COMMENTS. Comentarios de las tablas del usuario actual.  USER_COL_COMMENTS. Comentarios de las columnas del usuario actual.  ALL_TAB_COMMENTS. Comentarios de las tablas de todos los usuarios (sólo administradores)  ALL_COL_COMMENTS. Comentarios de las columnas de todos los usuarios  (sólo administradores).

36

Valor por defecto A cada columna se le puede asignar un valor por defecto durante su creación mediante la propiedad DEFAULT. Se puede poner esta propiedad durante la creación o modificación de la tabla, añadiendo la palabra DEFAULT tras el tipo de datos del campo y colocando detrás el valor que se desea por defecto. Ejemplo: CREATE TABLE articulo (cod NUMBER(7), nombre VARCHAR2(25), precio NUMBER(11,2) DEFAULT 3.5); Restricciones Una restricción es una condición de obligado cumplimiento para una o más columnas de la tabla. A cada restricción se le pone un nombre, en el caso de no poner un nombre (en las que eso sea posible) entonces el propio Oracle le coloca el nombre que es un Mnemotécnico con el nombre de tabla, columna y tipo de restricción. Su sintaxis general es: {CREATE TABLE nombreTabla | ALTER TABLE nombreTabla {ADD | MODIFY}} (campo tipo [propiedades] [,...] CONSTRAINT nombreRestricción tipoRestricción (columnas) [,CONSTRAINT nombrerestricción tipoRestricción (columnas) ...)

nombre, pero entonces será críptico. Por eso es mejor ponerle uno mismo. Los nombres de restricción no se pueden repetir para el mismo esquema, por lo que es buena idea incluir de algún modo el nombre de la tabla, los campos involucrados y el tipo de restricción en el nombre de la misma. Por ejemplo pieza_id_pk podría indicar que el campo id de la tabla pieza tiene una clave principal (PRIMARY KEY). Prohibir nulos La restricción NOT NULL permite prohibir los nulos en una determinada tabla. Eso obliga a que la columna tenga que tener obligatoriamente un valor para que sea almacenado el registro. Se puede colocar durante la creación (o modificación) del campo añadiendo la palabra 37

NOT NULL tras el tipo: CREATE TABLE cliente(dni VARCHAR2(9) NOT NULL); En ese caso el nombre le coloca Oracle. La otra forma (que admite nombre) es: CREATE TABLE cliente(dni VARCHAR2(9) CONSTRAINT dni_sinnulos NOT NULL(dni)); Valores únicos Las restricciones de tipo UNIQUE obligan a que el contenido de uno o más campos no puedan repetir valores. Nuevamente hay dos formas de colocar esta restricción: CREATE TABLE cliente(dni VARCHAR2(9) UNIQUE); En ese caso el nombre de la restricción la coloca el sistema Oracle. Otra forma es: CREATE TABLE cliente(dni VARCHAR2(9) CONSTRAINT dni_u UNIQUE); Esta forma permite poner un nombre a la restricción. Si la repetición de valores se refiere a varios campos, la forma sería: CREATE TABLE alquiler(dni VARCHAR2(9), cod_pelicula NUMBER(5), CONSTRAINT alquiler_uk UNIQUE(dni,cod_pelicula) ; La coma tras la definición del campo cod_pelicula hace que la restricción sea independiente de ese campo. Eso obliga a que, tras UNIQUE se indique la lista de campos. Los campos UNIQUE son las claves candidatas de la tabla (que habrán sido detectadas en la fase de diseño de la base de datos). Clave primaria La clave primaria de una tabla la forman las columnas que indican a cada registro de la misma. La clave primaria hace que los campos que la forman sean NOT NULL (sin posibilidad de quedar vacíos) y que los valores de los campos sean de tipo UNIQUE (sin posibilidad de repetición). Si la clave está formada por un solo campo basta con: CREATE TABLE cliente( dni VARCHAR2(9) PRIMARY KEY, nombre VARCHAR(50)) ; O, poniendo un nombre a la restricción: CREATE TABLE cliente( 38

dni VARCHAR2(9) CONSTRAINT cliente_pk PRIMARY KEY, nombre VARCHAR(50)) ; Si la clave la forman más de un campo: CREATE TABLE alquiler(dni VARCHAR2(9), cod_pelicula NUMBER(5), CONSTRAINT alquiler_pk PRIMARY KEY(dni,cod_pelicula) ; clave secundaria o foránea Una clave secundaria o foránea, es uno o más campos de una tabla que están relacionados con la clave principal de los campos de otra tabla. La forma de indicar una clave foránea es: CREATE TABLE alquiler(dni VARCHAR2(9), cod_pelicula NUMBER(5), CONSTRAINT alquiler_pk PRIMARY KEY(dni,cod_pelicula), CONSTRAINT dni_fk FOREIGN KEY (dni) REFERENCES clientes(dni), CONSTRAINT pelicula_fk FOREIGN KEY (cod_pelicula) REFERENCES peliculas(cod)); Esta completa forma de crear la tabla alquiler incluye sus claves foráneas, el campo dni hace referencia al campo dni de la tabla clientes y el campo cod_pelicula que hace referencia al campo cod de la tabla peliculas. También hubiera bastado con indicar sólo la tabla a la que hacemos referencia, si no se indican los campos relacionados de esa tabla, se toma su clave principal (que es lo normal). Esto forma una relación entre dichas tablas, que además obliga al cumplimiento de la integridad referencial. Esta integridad obliga a que cualquier dni incluido en la tabla alquiler tenga que estar obligatoriamente en la tabla de clientes. De no ser así el registro no será insertado en la tabla (ocurrirá un error). Otra forma de crear claves foráneas (sólo válida para claves de un solo campo) es: CREATE TABLE alquiler( dni VARCHAR2(9) CONSTRAINT dni_fk REFERENCES clientes(dni), cod_pelicula NUMBER(5) CONSTRAINT pelicula_fk REFERENCES peliculas(cod) CONSTRAINT alquiler_pk PRIMARY KEY(dni,cod_pelicu )); 39

Esta definición de clave secundario es idéntica a la anterior, sólo que no hace falta colocar el texto FOREIGN KEY. La integridad referencial es una herramienta imprescindible de las bases de datos relacionales. Pero provoca varios problemas. Por ejemplo, si borramos un registro en la tabla principal que está relacionado con uno o varios de la secundaria ocurrirá un error, ya que de permitírsenos borrar el registro ocurrirá fallo de integridad (habrá claves secundarios refiriéndose a una clave principal que ya no existe). Por ello Oracle nos ofrece dos soluciones a añadir tras la cláusula REFERENCES:  ON DELETE SET NULL. Coloca nulos todas las claves secundarias relacionadas con la borrada.  ON DELETE CASCADE. Borra todos los registros cuya clave secundaria es igual que la clave del registro borrado.

Si no se indica esta cláusula, no se permite el borrado de registros relacionados. El otro problema ocurre si se desea cambiar el valor de la clave principal en un registro relacionado con claves secundarias. En muchas bases de datos se implementan soluciones consistentes en añadir ON UPDATE CASCADE o ON UPDATE SET NULL. Oracle no implementa directamente estas soluciones. Por lo que hay que hacerlo de otra forma. Las soluciones son:  Implementar un TRIGGER para que cuando se actualice el registro se actualicen las claves secundarias (el mecanismo de funcionamiento es parecido al que se muestra en el siguiente párrafo).  Añadir un registro igual que el que se quiere cambiar en la tabla principal, pero con el nuevo valor de la clave. Mediante una instrucción UPDATE actualizar a ese valor de clave todos los registros de la tabla secundaria cuyo valor coincida con la antigua clave. Finalmente borrar el registro en la tabla principal con el valor antiguo de la clave.  La sintaxis completa para añadir claves foráneas es: CREATE TABLE tabla(lista_de_campos CONSTRAINT nombreRestriccion FOREIGN KEY (listaCampos)

40

REFERENCES tabla(clavePrincipalRelacionada) [ON UPDATE {SET NULL | CASCADE}]); Si es de un solo campo existe esta alternativa: CREATE TABLE tabla(lista_de_campos tipos propiedades, nombreCampoClaveSecundaria CONSTRAINT nombreRestriccion REFERENCES tabla(clavePrincipalRelacionada) [ON UPDATE {SET NULL | CASCADE}] Son restricciones que dictan una condición que deben cumplir los contenidos de una columna. La expresión de la condición es cualquier expresión que devuelva verdadero o falso, pero si cumple estas premisas:  No puede hacer referencia a números de fila  No puede hacer referencia a objetos de SYSTEM o SYS  No se permiten usar las funciones SYSDATE, UID, USER y USERENV  No se permiten referencias a columnas de otras tablas (si a las de la misma tabla) Una misma columna puede tener múltiples CHECKS en su definición (se pondrían varios CONSTRAINT seguidos, sin comas). Ejemplo: CREATE TABLE ingresos(cod NUMBER(5) PRIMARY KEY, concepto VARCHAR2(40) NOT NULL, importe NUMBER(11,2) CONSTRAINT importe_min CHECK (importe>0) CONSTRAINT importe_max CHECK (importe<8000)); Para poder hacer referencia a otras columnas hay que construir la restricción de forma independiente a la columna: CREATE TABLE ingresos(cod NUMBER(5) PRIMARY KEY, concepto VARCHAR2(40) NOT NULL, importe_max NUMBER(11,2), importe NUMBER(11,2), CONSTRAINT importe_maximo CHECK (importe
41

Añadir restricciones Es posible querer añadir restricciones tras haber creado la tabla. En ese caso se utiliza la siguiente sintaxis: ALTER TABLE tabla ADD [CONSTRAINT nombre] tipoDeRestricción(columnas); tipoRestricción es el texto CHECK, PRIMARY KEY o FOREIGN KEY. Las restricciones NOT NULL deben indicarse mediante ALTER TABLE .. MODIFY colocando NOT NULL En el campo que se modifica. borrar restricciones Sintaxis: ALTER TABLE tabla DROP PRIMARY KEY | UNIQUE(campos) | CONSTRAINT nombreRestricción [CASCADE] La opción PRIMARY KEY elimina una clave principal (también quitará el índice UNIQUE sobre las campos que formaban la clave. UNIQUE elimina índices únicos. La opción CONSTRAINT elimina la restricción indicada. La opción CASCADE hace que se eliminen en cascada las restricciones de integridad que dependen de la restricción eliminada. Por ejemplo en: CREATE TABLE curso( cod_curso CHAR(7) PRIMARY KEY, fecha_inicio DATE, fecha_fin DATE, tItulo VARCHAR2(60), cod_siguientecurso CHAR(7), CONSTRAINT fecha_ck CHECK(fecha_fin>fecha_inicio), CONSTRAINT cod_ste_fk FOREIGN KEY(cod_siguientecurso) REFERENCES curso ON DELETE SET NULL); Tras esa definición de tabla, esta instrucción: ALTER TABLE curso DROP PRIMARY KEY; Produce este error: ORA-02273: a esta clave única/primaria hacen referencia 42

algunas claves ajenas Para ello habría que utilizar esta instrucción: ALTER TABLE curso DROP PRIMARY KEY CASCADE; Esa instrucción elimina la clave secundaria antes de eliminar la principal. También produce error esta instrucción: ALTER TABLE curso DROP(fecha_inicio); ERROR en línea 1: ORA-12991: se hace referencia a la columna en una restricción de multicolumna Cambiar de nombre a las restricciones Para hacerlo se utiliza este comando: ALTER TABLE table RENAME CONSTRAINT nombreViejo TO nombreNuevo; mostrar restricciones La vista del diccionario de datos USER_CONSTRAINTS permite identificar las restricciones colocadas por el usuario (ALL_CONSTRAINTS permite mostrar las restricciones de todos los usuarios, pero sólo está permitida a los administradores). En esa vista aparece toda la información que el diccionario de datos posee sobre las restricciones. En ella tenemos las siguientes columnas interesantes: Columna Tipo de datos Descripción OWNER VARCHAR2(20) Indica el nombre del usuario propietario de la tabla CONSTRAINT_NAME VARCHAR2(30) Nombre de la restricción Consultas SELECT Capacidades Sin duda el comando más versátil del lenguaje SQL es el comando SELECT. Este comando permite:  Obtener datos de ciertas columnas de una tabla (proyección)  Obtener registros (filas) de una tabla de acuerdo con ciertos criterios (selección)  Mezclar datos de tablas diferentes (asociación, join)  sintaxis sencilla

43

SELECT * | {[DISTINCT] columna | expresión [[AS] alias], ...} FROM tabla; Donde:  *. El asterisco significa que se seleccionan todas las columnas  DISTINCT. Hace que no se muestren los valores duplicados.  columna. Es el nombre de una columna de la tabla que se desea mostrar  expresión. Una expresión válida SQL  alias. Es un nombre que se le da a la cabecera de la columna en el resultado de esta Instrucción. Ejemplos: /* Selección de todos los registros de la tabla clientes */ SELECT * FROM Clientes; /* Selección de algunos campos*/ SELECT nombre, apellido1, apellido2 FROM Clientes;

Cálculos aritméticos Los operadores + (suma), - (resta), * (multiplicación) y / (división), se pueden utilizar para hacer cálculos en las consultas. Cuando se utilizan como expresión en una consulta SELECT, no modifican los datos originales sino que como resultado de la vista generada por SELECT, aparece un nueva columna. Ejemplo: SELECT nombre, precio,precio*1.16 FROM articulos SQL para Oracle 9i Comandos internos SQL e iSQL*Plus Esa consulta obtiene tres columnas. La tercera tendrá como nombre la expresión utilizada, para poner un alias basta utilizar dicho alias tras la expresión: SELECT nombre, precio, precio*1.16 AS precio_con_iva FROM articulos; Los nombres pueden llevar espacios si se ponen con comillas dobles: SELECT nombre, precio, precio*1.16 AS "precio con iva" FROM articulos;

44

Esas comillas dobles cumplen otra función y es la de hacer que se respeten las mayúsculas y minúsculas del nombre (de otro modo el nombre de la columna aparece siempre en mayúsculas La prioridad de esos operadores es: tienen más prioridad la multiplicación y división, después la suma y la resta. En caso de igualdad de prioridad, se realiza primero la operación que esté más a la izquierda. Como es lógico se puede evitar cumplir esa prioridad usando paréntesis; el interior de los paréntesis es lo que se ejecuta primero. Cuando una expresión aritmética se calcula sobre valores NULL, el resultado de la expresión es siempre NULL. concatenación El operador || es el de la concatenación. Sirve para unir textos. Ejemplo: SELECT tipo, modelo, tipo || '-' || modelo "Clave Pieza" FROM piezas; El resultado puede ser: TIPO MODELO Clave Pieza AR 6 AR-6 AR 7 AR-7 AR 8 AR-8 AR 9 AR-9 AR 12 AR-12 AR 15 AR-15 AR 20 AR-20 AR 21 AR-21 BI 10 BI-10 BI 20 BI-20 BI 38 BI-38 BI 57 BI-57 Condiciones Se pueden realizar consultas que restrinjan los datos de salida de las tablas. Para ello se utiliza la cláusula WHERE. Esta cláusula permite colocar una condición que han de cumplir todos los registros, los que no la cumplan no aparecen en el resultado. © Copyright - Copyleft ' Jorge Sánchez 2004 45

Ejemplo: SELECT Tipo, Modelo FROM Pieza WHERE Precio>3; operadores de comparación Se pueden utilizar en la cláusula WHERE, son: Operador Significado > Mayor que < Menor que >= Mayor o igual que <= Menor o igual que = Igual <> Distinto != Distinto Se pueden utilizar tanto para comparar números como para comparar textos y fechas. En el caso de los textos, las comparaciones se hacen en orden alfabético. Sólo que es un orden alfabético estricto. Es decir el orden de los caracteres en la tabla de códigos. Así la letra Ñ y las vocales acentuadas nunca quedan bien ordenadas ya que figuran con códigos más altos. Las mayúsculas figuran antes que las minúsculas (la letra 'Z' es menor que la 'a'). Valores lógicos Son: Operador Significado AND Devuelve verdadero si las expresiones a su izquierda y derecha son ambas verdaderas OR Devuelve verdadero si cualquiera de las dos expresiones a izquierda y derecha del OR, son verdaderas NOT Invierte la lógica de la expresión que está a su derecha. Si era verdadera, mediante NOT pasa a ser falso. Ejemplo: /* Obtiene a las personas de entre 25 y 50 años SELECT nombre,apellidos FROM personas WHERE edad>=25 AND edad<=50; /*Obtiene a la gente de más de 60 años o de menos de 20 SELECT nombre,apellidos FROM personas WHERE edad>60 OR edad<20;

46

BETWEEN. El operador BETWEEN nos permite obtener datos que se encuentren en un rango. Uso: SELECT tipo,modelo,precio FROM piezas WHERE precio BETWEEN 3 AND 8; Saca piezas cuyos precios estén entre 3 y 8 (ambos incluidos). IN. Permite obtener registros cuyos valores estén en una lista: SELECT tipo,modelo,precio FROM piezas WHERE precio IN (3,5, 8); Obtiene piezas cuyos precios sea 3, 5 u 8, sólo uno de esos tres. LIKE. Se usa sobre todo con textos, permite obtener registros cuyo valor en un campo cumpla una condición textual. LIKE utiliza una cadena que puede contener estos símbolos: Símbolo Significado % Una serie cualquiera de caracteres _ Un carácter cualquiera Ejemplos: /* Selecciona nombres que empiecen por S */ SELECT nombre FROM personas WHERE nombre LIKE 'A%'; /*Selecciona las personas cuyo apellido sea Sanchez, Senchez, Stnchez,...*/ SELECT apellido1 FROM Personas WHERE apellido1 LIKE 'S_nchez'; IS NULL Devuelve verdadero si una expresión contiene un nulo: SELECT nombre,apellidos FROM personas WHERE telefono IS NULL Esa instrucción selecciona a la gente que no tiene teléfono Precedencia de operadores A veces las expresiones que se producen en los SELECT son muy extensas y es difícil saber que parte de la expresión se evalúa primero, por ello se indica la siguiente tabla de precedencia: Orden de precedencia Operador 1 *(Multiplicar) / (dividir) 2 + (Suma) - (Resta) 3 || (Concatenación) 47

4 Comparaciones (>, <, !=, ...) 5 IS [NOT] NULL, [NOT ]LIKE, IN 6 NOT 7 AND 8 OR Ordenación El orden inicial de los registros obtenidos por un SELECT no guarda más que una relación respecto al orden en el que fueron introducidos. Para ordenar en base a criterios más interesantes, se utiliza la cláusula ORDER BY. En esa cláusula se coloca una lista de campos que indica la forma de ordenar. Se ordena primero por el primer campo de la lista, si hay coincidencias por el segundo, si ahí también las hay por el tercero, y así sucesivamente. Se puede colocar las palabras ASC O DESC (por defecto se toma ASC). Esas palabras significan en ascendente (de la A a la Z, de los números pequeños a los grandes) o en descendente (de la Z a la a, de los números grandes a los pequeños) respectivamente. Sintaxis completa de SELECT: SELECT expresiones FROM tabla [WHERE condición] ORDER BY listaDeCamposOAlias; Funciones Oracle incorpora una serie de instrucciones que permiten realizar cálculos avanzados, o bien facilitar la escritura de ciertas expresiones. Todas las funciones reciben datos para poder operar (parámetros) y devuelven un resultado (que depende de los parámetros enviados a la función. Los argumentos se pasan entre paréntesis: nombreFunción[(parámetro1[, parámetro2,...])] Si una función no precisa parámetros (como SYSDATE) no hace falta colocar los paréntesis. Las hay de dos tipos:  Funciones que operan con una sola fila  Funciones que operan con varias filas. Agrupaciones Es muy común utilizar consultas en las que se desee agrupar los datos a fin de realizar

48

cálculos en vertical, es decir calculados a partir de datos de distintos registros. Para ello se utiliza la cláusula GROUP BY que permite indicar en base a qué registros se realiza la agrupación. Con GROUP BY la instrucción SELECT queda de esta forma: SELECT listaDeExpresiones FROM listaDeTablas [JOIN tablasRelacionadasYCondicionesDeRelación] [WHERE condiciones] [GROUP BY grupos] [HAVING condiciones de grupo] [ORDER BY columnas]; En el apartado GROUP BY, se indican las columnas por las que se agrupa. La función de este apartado es crear un único registro por cada valor distinto en las columnas del grupo. Si por ejemplo agrupamos en base a las columnas tipo y modelo en una tabla de existencias, se creará un único registro por cada tipo y modelo distintos: SELECT tipo,modelo FROM existencias GROUP BY tipo,modelo; Si la tabla de existencias sin agrupar es: TI MODELO N_ALMACEN CANTIDAD AR 6 1 2500 AR 6 2 5600 AR 6 3 2430 AR 9 1 250 AR 9 2 4000 AR 9 3 678 AR 15 1 5667 AR 20 3 43 BI 10 2 340 BI 10 3 23 BI 38 1 1100 BI 38 2 540 BI 38 3 La consulta anterior creará esta salida: TI MODELO 49

AR 6 AR 9 AR 15 AR 20 BI 10 BI 38 Es decir es un resumen de los datos anteriores. Los datos n_almacen y cantidad no están disponibles directamente ya que son distintos en los registros del mismo grupo. Sólo se pueden utilizar desde funciones (como se verá ahora). Es decir esta consulta es errónea: SELECT tipo,modelo, cantidad FROM existencias GROUP BY tipo,modelo; SELECT tipo,modelo, cantidad * ERROR en línea 1: ORA-00979: no es una expresión GROUP BY LINKOGRAFIA http://creativecommons.org/licenses/by-nc-sa/2.0/

http://www.programatium.com/bd/oracle.htm

50