• Author / Uploaded
• Climat SA

Citation preview

ORACLE

Curso de Fundamentos de SQL para Oracle 11g.

Ing. Monica Jaimes

Caracas, 14-04-2016.

“Téngase Paciencia en la Ejecución y Caridad en el Corazón”. Don Bosco

INDICE GENERAL

Pág. INTRODUCCIÓN

1

Capítulo I: Base de Datos

4

Capítulo II: SQL Conceptos Generales y sus Características

15

Capítulo III: Funciones de manipulación de Textos y de Fechas SQL

40

Capítulo Iv: Funciones de Transformación, Manipulación de Datos.

49

2

Introducción

El lenguaje SQL permite la comunicación con los Sistemas de Bases de Datos Relacionales (SGBD). La palabra SQL está formada por las iniciales de STRUCTURED QUERY LANGUAGE (Lenguaje de Consulta Estructurado). Esta definición inicial no describe completamente las posibilidades del lenguaje, ya que con él se hacen todas las operaciones necesarias para la gestión del SGBD (actualizaciones de datos, definición de objetos, operaciones de control y consultas de datos). Una de las características más importantes de SQL es que sus sentencias pueden manejar conjuntos de registros. Esto confiere al lenguaje una gran potencia, consiguiéndose una alta productividad.

3

CAPITULO I 1._Base de Datos: Es un Repositorio de información organizada y estructurada, en una Base de Datos se organizan campos, registros y archivos.

Existen base de datos del tipo Relacional, jerárquica, de red, en el caso de Oracle es un tipo de Base de Datos de tipo relacional. Tipos de bases de datos

Desde el punto de vista de la organización lógica: Jerárquicas. (Progress) Relacionales. (Oracle, Access, Sybase9) Desde el punto de vista de número de usuarios: Monousuario (dBase, Access, Paradox9) Multiusuario cliente/servidor (Oracle, Sybase9)

1.1._Sistemas de Bases de Datos relacionales Un Sistema de Gestión de Base de Datos relacional (SGBD) es un conjunto de programas que se encarga de gestionar los datos almacenados en la Base de Datos. Estos sistemas se caracterizan por presentar los datos a los usuarios en forma de tablas, formadas por filas y columnas, todas las operaciones se realizan con sentencias SQL. También existe una independencia entre el almacenamiento físico de los datos y la estructura lógica de la base de datos (tablas).El SGBD coordina todas las peticiones realizadas por los usuarios, asegurando en todo momento la integridad de los datos. Además el SGBD tiene un conjunto de tablas predefinidas que forman el Diccionario de Datos o Catálogo. En estas tablas se guardan las definiciones de los objetos/estructuras (tablas, vistas, índices, etc.) que componen la base de datos. Estas tablas son mantenidas automáticamente por el SGBD y pueden ser consultadas por los usuarios.

4

A continuación definiremos algunos conceptos básicos relacionados a Base de Datos: Esquema de una Bd: Es la representación gráfica de la estructura de la BD, es la que define las tablas, campos, relaciones. Campo: Es la unidad mínima de referencia, también podemos definirla como el conjunto de columnas o categorías verticales. Registros: Son las filas o agrupaciones horizontales de datos de campos. ORACLE El servidor Oracle es un sistema para manejo de bases de datos objeto-relacionales que proporciona una aproximación abierta e integrada para el manejo de información. El usuario que necesite interactuar con el servidor Oracle necesita primero establecer una conexión a la Base de Datos. Los pasos siguientes se llevan a cabo para conectarse a una Base de datos: · El usuario ejecuta una herramienta como SQL*PLUS, o corre una aplicación, Originando un proceso usuario. · Cuando un usuario se conecta al servidor Oracle especificando un usuario, password,y una Base de datos, se crea un proceso en la máquina que está ejecutando el Servidor Oracle. Este proceso se conoce como proceso servidor

Este gestor de Base de datos es utilizado por grandes Empresas ya que maneja grandes volúmenes de datos por ende su mantenimiento es muy costoso y por ende requiere de licencia.

5

Tipos de datos en Oracle En la definición de columnas de las tablas se utilizan fundamentalmente tres tipos de datos: Columnas numéricas. Contienen números enteros, decimales y en coma flotante. La norma ANSI define tipos diferentes para cada uno de estos números: SMALLINT para enteros pequeños, INTEGER para enteros grandes, NUMERIC y DECIMAL para números con parte entera y parte fraccionaria y FLOAT, DOUBLE PRECISION Y REAL para números en formato en coma flotante. En el manual se utiliza el tipo NUMBER(p,s) de Oracle con el que se pueden Implementan los tipos anteriores. En la definición NUMBER (p,s), el entero ‘p’ se llama precisión y es el número de total de dígitos (parte entera y parte fraccionaria). El entero ‘s’, es el número de dígitos que puede tener la parte fraccionaria. Columnas alfanuméricas. Contienen cadenas de caracteres de longitud fija y de longitud variable. La norma ANSI define tipos de datos de longitud fija con CHARACTER, CHAR, NATIONAL CHARACTER, NCHAR, etc. y tipos de longitud variable con CHARACTER VARYING, CHAR VARYING, NATIONAL CHARACTER VARYING, NCHAR VARYING, etc.. Tipo de columnas VARCHAR(n) de Oracle, que permite cadenas de caracteres de longitud variable con valor máximo igual al entero ‘n’. Las columnas alfanuméricas de longitud variable se caracterizan principalmente porque el SGBD requiere un espacio de almacenamiento igual a la longitud real del dato no a la longitud máxima de definición de la columna. Columnas de fechas. Estas columnas contienen información de datos temporales (año, mes, día, hora, minuto y segundo). Este tipo de datos no está definido en la norma ANSI, por lo cual cada fabricante lo implementa de forma diferente. Se utilizan los tipos: TIME para contener horas, TIMESTAMP para contener instantes en el tiempo y DATE para contener días.

6

Tipo DATE de Oracle que contiene información de año, mes, día, hora, minuto y segundo. Existen otros tipos de datos que permiten guardar información de gran Tamaño (gigabytes) tanto de tipo binario como de tipo alfanumérico. Estos tipos tampoco se definen en la norma ANSI y por lo cual los fabricantes los implementan de forma diferente. En el apartado de anexos se indica una equivalencia entre los tipos de datos ANSI y DB2 de IBM y los tipos de Oracle.

1.2._Portal de página Oficial de Oracle y pasos a seguir para descargar las aplicaciones. Para iniciar el curso de Sql se requiere tener instalado el Ejecutor de Oracle 11g XE y una interfaz gráfica SQL Developer, actualmente los Proveedores de Oracle a través de su portal http://www.oracle.com/ facilita la descarga de estas aplicaciones de forma Gratuita, lo único que usted debe tener un usuario y clave de autenticación, una vez que este se encuentre registrado y autenticado podrá descargar las aplicaciones requeridas, como en el ejemplo del Anexo 1.

Anexo 1.Autenticacion de Usuario en el Portal oficial de Oracle. 7

En la opcion de descargas, se visualizan todas las aplicaciones que se requieran descargar, en este caso se trabajara con la Version de Oracle 11G EXPRESS EDITION Como gestor de base de datos que solo permite la instalacion en una sola maquina (cliente –servidor) ya que esta version fue realizada para desarrolladores y es completamente Gratuita, por lo que no requiere de Licencia, adicionalmente en la opcion Descargas, para efectos del curso descargaremos dos Aplicaciones (ORACLE DATABASE 11G EXPRESS EDITION

y

SQL

DEVELOPER ) .Ver Anexo 2

Anexo 2. Pantalla de Descargas de Aplicaciones Una vez lo hayan descargado, para el caso del instalador Oracle 11g XE seleccionan el ejecutable y se sugiere Ejecutar como administrador para evitar problemas durante la instalación. Ver Anexo 3

8

Anexo 3. Ejecutando Instalador de Oracle 11g XE.

En la ventana de Especificación de Password de Base de Datos, tenemos que introducir la contraseña que se usaran para los usuarios SYS y SYSTEM, se sugiere guardarla o recordarla, ya que si esta contraseña la olvidas no podrán realizar tareas de administración. Los usuarios SYS y SYSTEM en Oracle son como el Root en Unix y administrador en Windows. Ver Anexo 4.

ANEXO 4. Asignacion de Clave de System

Una vez instalado en INICIO, en la carpeta de Oracle podran verificar la conexión o por consola de comandos en la Opcion “RUN SQL COMMAND LINE” (ANEXO 5). Recuerde que la clave por defecto es SYSTEM y la clave la que hayas asignado en la instalacion.Ver ANEXO 5

9

CONSOLA DE COMANDOS SQL

ANEXO 5. ORACLE INSTALADO

1.3._Instrucciones para Conectarse a la BD y Creación de un Esquema por consola de Comandos. 1.3.1._Consola de Comandos con Oracle 11g XE.

ANEXO 6. EJECUCION DE COMANDOS PARA CONEXIÓN Y CREACION DE ESQUEMA VIA CONSOLA. Cuando se despliegue la consola observaran el promt “SQL> luego se conectaran al esquema propio de la base de datos con este comando: conn / as sysdba; el cual arrojara la sentencia Connect, luego procedemos a crear el usuario

10

con la clave que establezcas: en el ejemplo t usuario es TELECOM y clave :123 tal como se observa en el ANEXO 6. SQL> create user TELECOM identified by 123; Posteriormente concedes los privilegios al usuario de manera que puedan acceder a los recursos y permisos DBA, por ende con el usuario /clave, crear tus objetos (tablas, vistas, índices, procedures etc.) eso es un esquema, una colección de objetos. Los comandos son los siguientes: SQL> grant connect, resource to TELECOM; SQL> grant dba to TELECOM; 1.3.2._SQL DEVELOPER. Con esta aplicación o interfaz grafica podran acceder a conectarte al gestor de base de datos y a los Esquemas que se hayan creado. Para esta aplicación se requiere tener instalado el JDK que es el ejecutable de Java version 7, entonces le das clip en Accepp License Agrament y descargas el el JDK en la opcion que te coloque la flechita “SQL Developer requieres JDK 7 or Above”. ANEXO 7 http://www.oracle.com/technetwork/es/developer-tools/sqldeveloper/downloads/index.html

11

Ejecutor de Java versión 7 Instalador del Jdk dependiendo de La versión de Windows.

ANEXO 7. INSTALACION DE JDK Y SQL DEVELOPER. Posteriormente cuando descarguen la carpeta del SQL DEVELOPER, una vez que ya tengas instalado el JDK, dentro de la carpeta observaran varios archivos como se observa en el ANEXO 8, allí solo se ejecuta y se despliega la interfaz gráfica ya que es un aplicativo del tipo portable. Programa portable Ejecutor

ANEXO 8.SQL DEVELOPER

12

Si al abrir el sql developer, le despliega una ventanita como esta en el anexo (ANEXO 10), debes indicarle donde está ubicado el JDK de java si no lo ha encontrado el automáticamente.

ANEXO 9.PATCH DE JDK. Una vez puesto el path del Java JDK el programa SQL Developer arrancará. Podrán probar la conexión por el sql developer, se crearan una conexión de un esquema haciendo click en archivo en la opción Nuevo ---

conexión base de

datos.

Les mostrara esta pantalla en NOMBRE DE CONEXIÓN: colocaran el nombre que deseen, en USUARIO y clave Creado para el Esquema y luego confirmar los datos en Aceptar. Si desean realizarlo con el usuario del Sistema del XE es colocar usuario: SYSTEM y la clave con la que registro la instalación. Ver ANEXO 10

13

ANEXO 10. Conexión desde Sql Developer

*A continuación podrá poner en práctica lo anteriormente expuesto a través del taller de creación de Esquema y estructuras de una BD. Por favor revisar el Manual: “

14

CAPITULO II

2._Características Sql 2.1._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 “Instituto de Normalización de Estándar Nacional Americano” (ANSI) e “Organización de Estándar Internacional” (ISO).

2.2._Comandos Sql: Están estructurados en los siguientes Grupos: Lenguaje de definición de datos (DDL) o Create, Alter, Drop, Rename,Truncate,Comment. Lenguaje de manipulación de datos (DML) o Insert, Update, Delete, Merge. Lenguaje de control de datos (DCL) o Grant, Revoke. Control de transacciones o Commit, Rollback, Savepoint 2.3._Conceptos Generales. 2.3.1._Creación de una tabla

Las tablas son la estructura básica que permite almacenar la información en la base de datos. Muchas herramientas de base de datos le permiten crear tablas sin ingresar SQL, es decir de forma manual sin ejecutar ningún comando, pero

15

debido a que las tablas son los contenedores de toda la información, es importante incluir la sintaxis CREATE TABLE en este manual de Sql para Oracle. La sentencia CREATE TABLE se utiliza para crear una tabla en una base de datos existente. Sintaxis CREATE TABLE Create Table Nombre_Tabla (campo1 tipodato1, Campo2 tipodato2);

2.3.2._Creacion de un Objeto SECUENCIAL: es un objeto que se emplea para generar valores enteros secuenciales únicos y asignárselos a campos numéricos; se utilizan generalmente para las claves primarias de las tablas garantizando que sus valores no se repitan.

Sintaxis CREATE SEQUENCE

Create sequence NOMBRESECUENCIA start with VALORENTERO increment by VALORENTERO maxvalue VALORENTERO

2.3.3._Tabla DUAL: Es una estructura creada durante la instalación de Oracle, con una sola columna llamada DUMMY y una sola fila de contenido 'X'. El propietario es SYS y puede ser accedida por cualquier usuario, esta tabla es útil para realizar operaciones matemáticas, crear filas virtuales, asignar valores, obtener secuencias, utilizar funciones Sintaxis general: SELECT * FROM DUAL;

16

2.4._Las CONSTRAINTS: son restricciones que se utilizan para limitar el tipo de dato que puede recibir una columna de una tabla. Las restricciones se puede definir cuando creamos la tabla (CREATE TABLE) o posteriormente con la sentencia ALTER TABLE. Las posibles restricciones son: PRIMARY KEY FOREIGN KEY CHECK DEFAULT UNIQUE NOT NULL La clave primaria, PRIMARY KEY, identifica de manera única cada fila de una tabla .La columna definida como clave primaria (PRIMARY KEY) debe ser UNIQUE (valor único) y NOT NULL (no puede contener valores nulos). Cada tabla sólo puede tener una clave primaria (PRIMARY KEY). Ejemplo PRIMARY KEY, clave primaria en ORACLE, SQLSERVER, ACCESS CREATE TABLE personas (identificador int NOT NULL PRIMARY KEY, nombre varchar(255) NOT NULL, apellido1 varchar(255) NOT NULL ); La clave primaria (PRIMARY KEY) puede estar compuesta por varias columnas, por ejemplo por las columnas 'identificador' y 'nombre', entonces se define así: CREATE TABLE FACTURA ( NroFactura NUMBER (5), FechaEmision DATE, MontoTotal INTEGER, Igv DECIMAL, CONSTRAINT IDFAC_PK PRIMARY KEY (NroFactura)); La clave primaria también se puede definir después de haber creado la tabla, para eso utilizaremos el comando ALTER TABLE 17

Ejemplo PRIMARY KEY CON ALTER TABLE: ALTER TABLE “FACTURA” CONSTRAINT “IDFAC_PK” PRIMARY KEY (NroFactura); La restricción CHECK se utiliza para limitar el rango de valores que puede tener una columna. Se pueden definir varias restricciones CHECK en una tabla Ejemplo CHECK en ORACLE, SQLSERVER y ACCESS: CREATE TABLE departamentos ( dep int NOT NULL CHECK (dep>0) departamento varchar(255) ); La restricción DEFAULT se utiliza para establecer un valor por defecto a una columna. Si no se especifica un valor al insertar una fila, entonces se podrá el valor por defecto (DEFAULT) que tenga cada columna. SQL DEFAULT en la sentencia CREATE TABLE CREATE TABLE pedidos ( idpedido int, producto int, cantidad int, fecha date DEFAULT GETDATE() ); Si al crear un pedido, no especificamos el valor de la columna 'fecha', entonces para esa columna se insertará por defecto obtenido de ejecutar la función GETDATE(), que devuelve la fecha del sistema En lugar de una función, podemos insertar una valor concreto '2001-01-01' o cualquier otro valor. La clave externa o FOREIGN KEY, es una columna o varias columnas, que sirven para señalar cual es la clave primaria de otra tabla. La columna o columnas señaladas como FOREIGN KEY, solo podrán tener valores que ya existan en la clave primaria PRIMARY KEY de la otra tabla.

18

Ejemplo de FOREIGN KEY Definiciones de FOREIGN KEY en CREATE TABLE para ORACLE, ACCESS, SQLSER.

Si la clave externa o foránea (FOREIGN KEY) está compuesta por varias columnas o queremos ponerle un nombre, utilizaremos la fórmula siguiente: CONSTRAINT

fkpersonas

FOREIGN

KEY

(dep,

id)

REFERENCES

departamentos(dep,id). Ejemplo FOREIGN KEY con ALTER TABLE ALTER TABLE ADD FOREIGN KEY (dep) REFERENCES departamentos(dep) Ejemplo FOREIGN KEY múltiple (varias columnas) con ALTER TABLE: ALTER

TABLE

ADD

CONSTRAINT

fkpersonas

FOREIGN KEY

(dep)

REFERENCES departamentos(dep) Para borrar un clave externa (FOREIGN KEY) utilizamos DROP, pero varía según la base de dato: Borrar FOREIGN KEY en MySQL ALTER TABLE personas DROP FOREIGN KEY dep Borrar FOREIGN KEY en ORACLE, SQLSERVER y ACCESS ALTER TABLE personas DROP CONSTRAINT dep

19

2.5_La sentencia SELECT La sentencia SELECT nos permite consultar los datos almacenados en una tabla de la base de datos. Ver ANEXO 11.

ANEXO 11. Representación de una consulta Join El formato de la sentencia select es: SELECT

[ALL

|

DISTINCT

FROM

]

[{,}]

| [{,|}]

[WHERE

[GROUP

BY

[HAVING [ORDER

[{

BY

[{

AND|OR

}]]

[{,}]]

}]] [ASC

|

DESC]

[{,| [ASC | DESC ]}]]

20

A continuación los Elementos básicos de la Sentencia “SELECT”: 2.5.1_Operadores Lógicos en WHERE: Los criterios de selección de filas de la cláusula WHERE pueden tener la complejidad que se necesite. Pueden utilizarse condiciones de búsqueda múltiples usando los operadores lógicos: AND, OR y paréntesis para forzar el orden de la evaluación.

2.5.2_Comprobaciones con valores simples Todos estos operadores funcionan con letras o números y con columnas o literales.

3.6.1.1._ Igual que “ = ”, mayor que “ > ”, menor que “ < ” y distinto de “ =! ” Ejemplo: Recuperar los empleados con código de departamento menor que 45.

ANEXO 12. Resultado de Consulta

21

2.5.3_._LIKE, NOT LIKE

Este operador permite la comparación con patrones. Utiliza dos caracteres especiales en las cadenas de comparación: Subrayado (_), representa un posición. Tanto por ciento (%), representa una serie de espacios o caracteres. Tema LIKE ‘_M%’ Cualquier tema cuyo segundo carácter sea una M. Tema LIKE ‘%MA%’ Cualquier tema que contenga MA. Tema LIKE ‘__M’ Cualquier tema de tres posiciones que termine en M. Tema LIKE ‘M_’ Cualquier tema de dos posiciones que empiece por M. Tema LIKE ‘M%’ Cualquier tema que empiece por M. Las mayúsculas y minúsculas son significativas. Ejemplo: Recuperar los empleados que el nombre empiece por G. Las constantes alfanuméricas deben ir entre comillas simples.VER ANEXO 13.

Anexo 13. Uso de comillas simples

2.5.4_._NULL, NOT NULL

Este operador se utiliza para comprobar si existe un valor en una columna de una fila. Tema IS NULL - La columna Tema está vacía. Tema IS NOT NULL - La columna Tema tiene información Ejemplo: Recuperar los empleados sin comisión.

22

Anexo 14. Uso de NULL, NOT NULL

2.5.5_BETWEEN, NOT BETWEEN Este operador realiza una comparación con relación al intervalo fijado por dos valores. Estado BETWEEN ‘B’ AND ‘F’ Estado es igual a ‘B’ o ‘F’ o a cualquier carácter entre ellos (alfabéticamente). Estado NOT BETWEEN ‘B’ AND ‘F’ Estado es menor a ‘B’ o mayor a ‘F’ (alfabéticamente). Página BETWEEN 3 AND 20 Pagina es igual a 3 o 20 o a cualquier valor entre ellos. Página NOT BETWEEN 3 AND 20 Pagina es menor a 3 o mayor a 20.

Ejemplo: Recuperar los empleados de los departamentos comprendidos entre 20 ó 40.

Anexo 15. Uso de Between. 23

2.6._SQL JOIN Un SQL Join es una sentencia que se utiliza combinar registros de dos o más tablas en una base de datos Relacional. •

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

En Oracle existen dos formas de hacer una unión entre tablas, la clásica, que está disponible en todas las versiones, pero no cumple con los estándares ANSI y la estándar, que se puede usar a partir de Oracle 9i y cumple con la regulación SQL: 1999

Anexo 17. Sintaxis Clásica y Estándar

2.6.1._Producto Cartesiano Cuando se intenta unir dos tablas y la condición no es válida o no existe, se produce un producto cartesiano, que consiste en una combinación de las filas de las dos tablas independientemente de si están relacionadas o no. Para evitar este tipo de operaciones, que son tremendamente costosas para el servidor y ofrecen informaciones que en muy raras ocasiones son relevantes, no se debe omitir la cláusula WHERE y una condición válida al unir tablas entre sí. En el Anexo 18 podemos observar un ejemplo de esta consulta donde se devuelve un registro de la tabla lugar por cada registro de la tabla país, aunque no haya relación entre ellos.

24

Anexo 18. Ejemplo de un Producto Cartesiano 2.6.2._Unión de tablas con Sintaxis Oracle (Clásica)

Anexo 19. Sintaxis Clásica. Para unir tablas utilizando la sintaxis propia de Oracle, se deben especificar en el FROM las tablas implicadas en la unión y en el WHERE la condición o condiciones de unión entre ellas. En la cláusula SELECT es recomendable especificar el nombre cualificado de las columnas recuperadas, es decir, se debe especificar el nombre de la columna, precedido por el de la tabla propietaria de la misma. En caso de que la columna tenga el mismo nombre en varias tablas, los nombres cualificados son obligatorios. Si se quiere unir varias tablas, es necesario relacionarlas todas entre sí, es decir, que cada tabla aparezca al menos en alguna de las condiciones de unión.

25

En el caso de la Sintaxis clásica la forma como se escribe es de forma Implícita, recordemos que la sintaxis implícita fue establecido en SQL-89 y ocurre cuando a continuación de la cláusula FROM escribimos una coma.

2.6.3._Unión de Igualdad (Equijoin) Este tipo de unión implica que los valores de las columnas utilizadas en la condición de unión deben ser iguales. Es el tipo de unión que se suele producir entre tablas relacionadas por una clave primaria y una clave ajena.

Anexo 20. Unión Equijoin

En la cláusula SELECT se especifican las columnas, alias y/o expresiones, cualificadas con el nombre de la tabla correspondiente. En la cláusula FROM se especifican las tablas implicadas, separadas por comas. En la cláusula WHERE se especifica la condición o condiciones de unión entre las tablas, con los nombres de las columnas implicadas también cualificados. Es posible especificar tantas cláusulas de restricción en el WHERE como se necesite, utilizando las cláusulas tipo AND y OR.

26

Anexo 21. Uso de clausula Where , AND u Or.

2.6.4._Unión de no igualdad (Non-Equijoin) Son un tipo de unión no muy utilizado, que une dos tablas por una condición diferente a la de igualdad.

Anexo 22. Uso del Non-Equijoin

Nota: Otras condiciones como = pueden ser utilizadas, pero es mas sencillo el BETWEEN. Recuerde especificar primeramente el rango menor que el mayor en la clausula BETWEEN. Los alias de tablas han sido utilizados en el ejemplo por razones de desempeño, no por un posible caso de ambigüedad. 27

2.6.5._Uniones Externas (Outer Join Explicitos): Los registros que no cumplen las condiciones de unión entre las tablas, no aparecen en los resultados de las consultas, sin embargo, es posible mostrar estos registros utilizando las uniones externas. Estas uniones muestran tanto los registros que cumplen las condiciones como los que no las cumplen, mostrando la información que falta como valores nulos.

Anexo 23. JOIN EXPLICITOS El operador que marca la unión externa en el modo SQL clásico de oracle es el (+), que se coloca junto a la condición de unión que tiene información insuficiente, es decir junto a la columna de la tabla débil. El operador de unión externa (+) solamente puede aparecer en un lado de la igualdad y no puede utilizarse con operadores IN ni condiciones OR.

Los Outer Join según la forma clásica tenemos las siguientes:

2.6.5.1._RIGHT OUTER JOIN IMPLICITO: Combina los valores de la primera tabla con los valores de la segunda tabla. Siempre devolverá las filas de la segunda tabla, incluso aunque no cumplan la Condición. Ver anexo 24

28

Anexo 24. Consulta Right Outer Join. En el anexo 24 observamos el (+) en el lado izquierdo del “=”, lo cual indica un Right outer join. Se observa que el retorno de la consulta son todos los atributos de tabla_B (derecha) y solo los atributos que cumplen con la condición que definimos de tabla_A.

2.6.5.2._LEFT OUTER JOIN IMPLICITO: Combina los valores de la primera tabla con los valores de la segunda tabla. Siempre devolverá las filas de la primera tabla, incluso aunque no cumplan la Condición.

29

Anexo 25. Consulta Left Outer Join. En el anexo 25 observamos el (+) en el lado Derecho del “=”, lo cual indica un Left outer join. Se observa que el retorno de la consulta son todos los atributos de tabla_A (Izquierda) y solo los atributos que cumplen con la condición que definimos de tabla_B

30

Restricciones del OUTER JOIN: El operador outer Join puede aparecer en un solo lado de la expresión en el lado que tenga ausencia de información. Esto regresa aquellas filas de una tabla que no tenga una correspondencia con la otra tabla. Una condición que involucra un Outer Join no puede utilizar el Operador In o ser vinculado con otra condición por el operador OR.

2.6.5.3._AUTOUNIONES (SELF JOIN)

En ocasiones puede ser necesario unir una tabla consigo misma para obtener un resultado determinado, para ello, se debe instar la tabla dos veces en la cláusula FROM, asignándole un alias diferente. Por ejemplo, para obtener todos los datos del mismo perfil que Beatriz González, deberemos unir la tabla viajeros consigo misma:

Anexo 26. Consulta usando SELF JOIN

31

2.6.6._Unión de tablas usando sintaxis estándar (SQL: 1999)

Anexo 27. Sintaxis Estándar.

Los resultados obtenidos con ambas sintaxis son equivalentes. CROSS JOIN devuelve un producto cartesiano de las dos tablas. NATURAL JOIN une dos tablas basándose en que tengan el mismo nombre de columna. JOIN realiza una unión entre las dos tablas especificando el nombre de la columna que las une. Se puede expresar como INNER JOIN. LEFT / RIGHT / FULL OUTER JOIN realiza una unión externa entre las dos tablas especificando el nombre de la columna de la unión.

*A diferencia de la Sintaxis Clásica, la sintaxis estándar es tipo Explicita.

2.6.6.1_CROSS JOIN Equivale al producto cartesiano en la unión de dos tablas.

32

Anexo 28. Consulta usando CROSS JOIN.

2.6.6.2. _NATURAL JOIN En versiones anteriores a Oracle 11g un Join no era posible sin especificar explícitamente las columnas, fue a partir de Oracle 9i que fue posible este cambio, permitiendo que el Join se realice automáticamente basándose en las columnas de las dos tablas que tienen el mismo nombre y tipo de dato, usando la palabra reservada NATURAL JOIN.

Anexo 29. Consulta usando NATURAL JOIN. 33

2.6.6.3._LEFT OUTER JOIN EXPLICITO Recupera todos los resultados de la tabla situada a la izquierda de la condición, aunque no haya coincidencias con la tabla que está a la derecha.

Anexo 30. Consulta usando LEFT OUTER JOIN EXPLICITO

2.6.6.4._RIGHT OUTER JOIN EXPLICITO Recupera todos los resultados de la tabla situada a la derecha de la condición, aunque no haya coincidencias con la tabla que está a la izquierda.

34

Anexo 31. Consulta usando RIGHT OUTER JOIN EXPLICITO

2.6.7._Clausulas utilizadas en los Join: a._ Cláusula USING: Con la cláusula USING, se puede especificar manualmente las columnas de las tablas que están implicadas en una JOIN.

35

Anexo 32. Consulta usando Cláusula USING

Ni la NATURAL JOIN ni la cláusula USING permiten que se cualifique la columna o columnas que relacionan las tablas, ya que se selecciona automáticamente la tabla usada:

Anexo 33. Consulta usando Cláusula USING de Forma “ERRADA”.

36

b._Cláusula ON: Con la cláusula ON, además de las columnas implicadas, permite modificar la condición o condiciones de unión:

Anexo 34. Consulta usando Cláusula ON

2.7._SUBCONSULTAS Se puede incluir una sentencia SELECT como parte de la cláusula WHERE de una sentencia SELECT principal. Esta sentencia se denomina “subconsulta” y debe ir entre paréntesis. Primero se ejecuta la subconsulta, y el resultado obtenido se evalúa con la sentencia principal.

2.7.1_Subconsultas que generan valores simples. Todos los operadores simples se pueden utilizar con subconsultas, siempre que el resultado de la esta sea un valor simple.

37

Ejemplo:

Anexo 35._ SubConsulta con valores Simples

Si la subconsulta recupera más de un valor y en la condición se utiliza el igual se genera un error al no poder realizar la comparación.

Anexo 36._SubConsulta con más de un valor de forma “ERRADA” 38

2.7.2._Subconsultas que generan listas de valores. Cuando las sub-consultas recuperan más de un valor es necesario utilizar los operadores IN, NOT IN, ALL, ANY/SOME que manejan listas de valores.

Anexo 37._SubConsulta con listas de valores.

Si se utiliza el operador ALL la comparación es verdadera: si la condición se cumple para todas las filas recuperadas en la sub-consulta, o si en esta no se recupera ninguna fila.Si se utiliza uno de los operadores ANY o SOME la comparación es verdadera si se cumple para una de las filas recuperadas en la sub-consulta. La comparación es falsa si la sub-consulta no recupera ninguna fila. El operador IN es equivalente a ‘=ANY’ y NOT IN es equivalente a ‘ ALL’.

*A continuación podrá poner en práctica lo anteriormente expuesto a través del taller de Generación de Consultas SQL. Por favor revisar el Manual: “

39

CAPITULO III 3.1._FUNCIONES DE MANIPULACION DE TEXTOS Notación: FUNCIÓN (Columna alfanumérica o Cadena de caracteres [, opción])

Nombr

Funcionalidad

e ||

(Concatenación) Une dos cadenas de caracteres.

Como podemos observar el || es útil para concatenar valores, en este caso se concateno el valor “Luis” con “Jiménez” y se uso un espacio en blanco, adicional se concatena una “,” seguido de comillas simples, el cual se invoca con el CHAR (39), este tipo de concatenaciones son muy útiles para el uso de los SQL(s) Dinámicos, el cual trataremos más adelante durante los talleres.

INITCA

Cambia las primeras letras de las palabras de una cadena de caracteres.

P

LOWE

Convierte todas las letras de la cadena de caracteres a

R

Minúsculas.

40

UPPER

Convierte todas las letras de la cadena a mayúsculas.

RPAD

Aumenta el tamaño de una cadena, añadiendo un conjunto de caracteres en la parte derecha.

LPAD

Aumenta el tamaño de una cadena, añadiendo un conjunto de caracteres en la parte izquierda.

RTRIM

Suprime un conjunto de caracteres de la parte derecha de la cadena.

41

LTRIM

Suprime un conjunto de caracteres de la parte izquierda de la cadena.

LENGT

Indica la longitud de la cadena.

H

INSTR

Busca la localización de un carácter en una cadena.

SUBST

Extrae una parte de una cadena.

R

42

3.8_FUNCIONES DE MANIPULACION DE NUMEROS Estas funciones sirven para operar con los datos numéricos recuperados en las consultas. Se agrupan en tres tipos: las que trabajan con valores simples, las que trabajan con grupos de valores y las que trabajan con listas de valores. Algunas funciones modifican los valores originales, otras generan un resultado que indica algo sobre los ellos. 3.8.1._Funciones de Valores Simples: Nombre

Funcionalidad

Valor1 + valor2

Suma

Valor1 – valor2

Resta.

Valor1 * valor2

Multiplicación.

Valor1 / valor2

División.

ABS(valor)

Valor absoluto.

CEIL(valor)

Entero más pequeño igual o mayor que valor.

FLOOR(valor)

Entero mayor más pequeño o igual que valor.

EXP(valor)

e (2,71828183...) elevado al exponente valor.

MOD(valor, divisor)

Resto de dividir valor por divisor.

NVL(valor, sustituto)

Si valor es nulo lo reemplaza por sustituto.

POWER(valor, exponente)

Eleva valor al exponente.

ROUND(valor, precisión)

Redondea el valor con la precisión dada.

SIGN(valor)

1 si valor es positivo, -1 si valor es negativo.

SQRT(valor)

Raíz cuadrada de valor.

TRUNC(valor, precisión)

Trunca el valor con la precisión 43

VSIZE(valor)

Tamaño de almacenamiento de valor.

LN(valor)

Logaritmo en base e de valor.

LOG(valor)

Logaritmo en base 10 de valor.

3.8.2._Funciones de Grupo de Valores: Nombre

Funcionalidad

AVG(valor)

Media de valor para un grupo de filas.

COUNT(valor)

Cuenta las filas.

MAX(valor)

Máximo de valor para un grupo de filas.

MIN(valor)

Mínimo de valor para un grupo de filas.

STDDEV(valor)

Desviación estándar de valor para un grupo de filas.

SUM(valor)

Suma de valor para un grupo de filas.

VARIANCE(valor)

Varianza de valor para un grupo de filas.

3.8.3._Funciones de Lista de Valores: Nombre

Funcionalidad

GREATEST(valor1)

Mayor valor de la lista

LEAST(valor1)

Menor valor de la lista

3.9._ MANIPULACION DE FECHAS se va a utilizar una serie de funciones que permiten operar con datos de fechas y horas recuperados en las consultas. Algunas funciones modifican los valores originales. Otras funciones generan un resultado que indica algo sobre estos valores.

3.9.1._SYSDATE La variable del sistema o pseudo-columna SYSDATE, permite recuperar la fecha y hora del sistema. Se utiliza como una columna especial.

44

3.9.2._ Funciones de fechas

Nombre

Funcionalidad

ADD_MONTHS(fecha

Suma n meses a fecha.

GREATEST(fecha1

Mayor fecha de la lista.

LEAST(fecha1

Menor fecha de la lista.

LAST_DAY(fecha)

Último día del mes de la fecha.

MONTHS_BETWEEN(fecha1,fech

Meses entre dos fechas

a2) NEXT_DAY(fecha, ’ddd’)

Fecha del día de la siguiente semana identificado por ddd.

NEW_TIME(fecha1, ’eee’, ’fff’)

Fecha de la zona horaria fff ,cuando en la zona horaria eee la fecha es igual fecha1

TO_CHAR(fecha, ’máscara’)

Cambia una fecha a un valor tipo carácter con el formato indicado en la máscara

TO_DATE(cadena, ’máscara’)

Cambia una cadena a una fecha con el formato indicado en la máscara.

3.9.3._FUNCIONES DE CONVERSION DE FECHAS TO_CHAR (fecha, ’máscara’): Convierte una fecha a un valor tipo carácter, con el formato indicado en la Máscara. Ejemplo: Recuperar el día, el mes y el año del Sistema.

TO_DATE (cadena, ’máscara’): Convierte una cadena de caracteres con un formato de fecha según se indica en la máscara en un dato de tipo DATE.

45

3.9.4._ Formatos de máscaras de fechas Estos formatos se utilizan con TO_CHAR y con TO_DATE;

3.9.5._ ROUND Y TRUNC con fechas.

Cuando se inserta o actualiza una fecha por medio de un literal se le asigna por Omisión a la hora el valor de 12 A.M.(Medianoche). La función ROUND sobre una fecha, pasa la hora a 12 A.M del día si la hora es

46

anterior al mediodía, y a las 12 A.M. del día siguiente si la hora es posterior al Mediodía. La función TRUNC se comporta de forma similar, pero con la diferencia que sempre pasa la hora a las 12 A.M.

RESUMEN: Existe una variable del sistema o pseudo-columna SYSDATE con la que se recupera del sistema la fecha y hora actual. Con las funciones ADD_MONTHS, LAST_DAY, GREATEST, LEAST, etc. se modifican las fechas de las columnas de tipo DATE. 47

Las funciones TRUNC y ROUND redondea la hora a las 12 A.M. en las fechas de las columnas de tipo DATE. La función TO_CHAR transforma una cadena de caracteres en una fecha (DATE). La función TO_DATE transforma una fecha (DATE) en una cadena de Caracteres.

Ver “Taller de Fundamentos de SQL para Oracle 11g Capítulos III y IV”, en la sección “Capítulo III – Taller Funciones SQL.

48

CAPITULO IV

4.1_ Funciones de transformación. En este apartado se van a utilizar dos funciones con las que se puede controlar la salida basándose en la entrada. Estas son TRANSLATE Y DECODE.

4.1.1_ TRANSLATE Es una función que hace una sustitución carácter a carácter en una cadena. Notación: TRANSLATE (cadena, existe, cambia) Recorre cada carácter de ‘cadena’ y lo busca en ‘existe’, y si lo encuentra lo Sustituye por el carácter que ocupa la misma posición en ‘cambia’. Ejemplo: Sustituir en 43789: los 2 por C, los 4 por H, los 5 por K, los 7 por P y los 8 por L.

4. 1.2._DECODE Se puede considerar como una sustitución valor a valor. Para cada ‘valor’ DECODE comprueba si coincide con ‘si_valx’ y en este caso lo cambia por ‘entoncesx’, si no coincide con ninguno lo cambia por si_no_val_def. Notación: DECODE(valor, si_val1,entonces1, si_val2, entonces2, si_val3, entonces3, si_no_val_def ) Esta función es equivalente a IF, ELSIF y ELSE.

49

Ejemplo: Sustituir los valores de la columna dgsitlab por literales.

4.2._ Agrupación de filas.

En este apartado se van a utilizar dos cláusulas no obligatorias de la sentencia SELECT que actúan sobre grupos de filas, estas son GROUP BY y HAVING.

4.2.1._GROUP BY:

Agrupa filas según los valores de las columnas de agrupación indicadas. Notación: GROUP BY column1 [, columna2]. Por cada valor de las columnas de agrupamiento se genera una fila en el resultado de la consulta. Cada una de ellas puede contener: columnas generadas con funciones de grupo y columnas utilizadas para agrupar filas. Ejemplo: Agrupar los empleados que trabajan en Madrid, por su código de 50

departamento, sumando los salarios y contando las filas (empleados) con salarios y las filas (empleados) de cada departamento.

Ejemplo: Agrupar los empleados que trabajan en Madrid, por su código de departamento, sumando los salarios y contando las filas (empleados) con salarios y las filas (empleados) de cada departamento.

Si en la consulta anterior se selecciona una columna de la tabla que no se ha utilizado en la agrupación de filas, se genera un error.

51

4.2.2._HAVING Selecciona las filas generadas con la cláusula GROUP BY que cumplen una condición. Notación: HAVING condición Ejemplo: Agrupar los empleados que trabajan en Madrid, por su código de departamento, sumando los salarios y contando los empleados con salarios y los empleados de cada departamento, seleccionando sólo los grupos con código de departamento > 12.

4.3._ MANIPULACIÓN DE DATOS En este capítulo conoceremos las sentencias INSERT, DELETE y UPDATE que permiten modificar el contenido de las tablas añadiendo o borrando filas y modificando los valores de las columnas de las filas existentes, también veremos su utilidad si los combinamos con consultas sql.

52

4.3.1._INSERT: Ejemplo: Insertar tres registros en la tabla Países.

En la cláusula VALUES se indican los valores a insertar separados por comas y en el orden que tienen las columnas dentro de la tabla. Los valores de las columnas alfanuméricas y las columnas de fechas deben ir entre apóstrofes.

Por otra parte se pueden utilizar los “insert” con una sentencia SELECT en otra tabla. En el siguiente ejemplo disponemos de tres Tablas: La tabla “CONTINENTE”, la tabla “PAISES” y una tabla temporal “TMP_PAISES_EUROPEOS” que es la tabla donde se insertaran solo aquellos países que sean del continente Europeo.

53

Se diseña la consulta primero

Y luego agregamos el insert:

54

Es importante que la consulta “SELECT” tenga los mismos campos extraídos al de la Tabla Destino donde se insertara la información.

4.3.2._DELETE

Con esta sentencia se borran filas en una tabla. La cláusula WHERE es necesaria para eliminar sólo las filas deseadas.

4.3.3._._UPDATE Con esta sentencia se actualizan las filas seleccionadas de una tabla, con los valores asignados a las columnas en la cláusula SET. Las filas se seleccionan con la cláusula WHERE.

55

4.4._SQL DINAMICO: Son sentencias que son generadas durante los scripts, es decir se itera una misma sentencia Sql según registros se procesen para generar el script sql

4.5._COMMIT y ROLLBACK.

Con estas sentencias del grupo de Control de Transacción se confirman o deshacen las modificaciones realizadas a los datos dentro de una unidad de trabajo llamada transacción. Una transacción comienza con el inicio de una sesión o mediante la última sentencia COMMIT o ROLLBACK ejecutada y termina con el fin de la sesión o con la ejecución de una sentencia COMMIT o ROOLBACK. 4.5.1_COMMIT Las modificaciones no se guardan en la base de datos de una forma definitiva hasta que no se validan con un COMMIT. Mientras un usuario no valida una transacción (COMMIT) sólo él ve los datos actualizados. Cualquier otro usuario que tenga acceso a la tabla, recuperará los datos sin actualizar guardados en la base de datos.

4.5.2_ROLLBACK Con ROLLBACK se deshacen todas las modificaciones realizadas por el usuario desde el anterior COMMIT. Commit implícito y rollback automático Cuando se realizan alguna de las siguientes acciones se realiza un commit implícito: QUIT, EXIT, CREATE TABLE, DROP TABLE, CREATE VIEW, DROP , VIEW, CONNECT, DISCONNECT,GRANT, REVOKE y ALTER. Si se produce un fallo en el ordenador y existen actualizaciones sin validar, el SGBD hace un rollback automático (deshace las actualizaciones sin validar) cuando arranca de nuevo.

56

Resumen Con las sentencias SQL: INSERT, UPDATE y DELETE, se modifica el contenido de las tablas de la base de datos. Hay que tener cuidado con las condiciones de la cláusula WHERE de las sentencias UPDATE y DELETE, para que las actualizaciones sólo afecten a las filas deseadas. Las modificaciones no se guardan en la base de datos de una forma definitiva hasta que no se validan con un COMMIT.Con la sentencia ROLLBACK se pueden deshacer las modificaciones realizas desde el anterior COMMIT.

Ver “Taller de Fundamentos de SQL para Oracle 11g Capítulos III y IV”, en la sección “Capítulo IV– Taller Funciones de Transformación, manipulación de Datos y SQL Dinámico.

57