Una Base de Datos ORACLE es un conjunto de datos tratados como una unidad.El propósito general de una B.D. es almacenar y recuperar información relacionada entre sí.
SQL es el lenguaje no procedural(procedimental) de ORACLE en el que está basado la programación relacional.Todo el acceso a la base,en lectura y escritura,se hace a través de SQL únicamente.PL/SQL es el lenguaje procedural(procedimental) de ORACLE y éste puede hacer referencia a sentencias SQL.
Estos archivos constituyen la base de datos física y sólo son visibles para el administrados de la Base (DBA) que puede decidir su nombre,tamaño y emplazamiento.
Los datos de los usuarios y el DD constituyen el núcleo de la BD ORACLE y están representados en forma de tablas y de índices.Los datos de trabajo son necesarios para que el servidor ORACLE pueda funcionar,permitiendo realizar los tratamientos relacionales.
Un archivo de datos es un conjunto de bytes(octetos) agrupados en bloques (un bloque ORACLE es la unidad más pequeña de transferencia memoria/disco).Este no puede ser ampliado.Cuando se encuentre lleno,el DBA deberá crear un nuevo archivo,que asociará a la base de datos.Por ello,un objeto de la BD(tabla,índice) al crecer pueda encontrarse almacenado en varios archivos de datos.
Una tabla de usuario o del diccionario de datos ocupa un cierto número de bloques ORACLE.Los bloques ocupados por una tabla forman un segmento de datos.Un índice ocupa un segmento de índices.
Una extensión (EXTENT) es un número determinado de bloques parametrizable (Usualmente este número lo decide el DBA).Cada segmento de datos o índices está compuesto por una ó más extensiones.Generalmente los bloques de una misma extensión está contiguos en el disco y por ello es conveniente que un objeto,en el momento de su creación,ocupe una sóla extensión.Cuando todos los bloques de una extensión están utilizados y se tiene necesidad de espacio, ORACLE utiliza otras extensiones ( si existen) ó asigna al objeto una nueva extensión.En todos los casos,una extensión asignada a un objeto deja de pertenecer a éste cuando se produce una supresión del mismo(DROP) o su reorganización. P.ej. la supresión de todas las líneas de una tabla devuelve los bloques libres a la tabla pero no a los otros objetos de la BD..
Por ejemplo,un segmento de datos de una tabla que ocupase varios archivos y extensiones se estructuraría:
los segmetos de trabajo representan una parte de los bloques de los archivos de datos utilizada por ORACLE para sus necesidades.Hay cuatro tipos.Nos importan particularmente dos:
Una tabla,un índice ó un segmento de Rollback se encuentran en un y,sólo en un,TS,. En el momento de su creación es cuando el DBA ó usuario decide en que TS será depositado.
La relación entre DB,TS y archivos de datos podemos describirla en cuatro puntos:
Llamamos instance ORACLE ( caso,suceso ORACLE) a un conjunto de procesos y a una SGA.
El tamaño de la SGA es fijo durante una sesión ORACLE.Contiene:
Los procesos principales que forman una instance ORACLE son:
Las setencias SQL se dividen en varias categorías.Las más importantes son:
SQL*Plus es una ampliación del SQL creada por ORACLE para potenciar las funciones y posibilidades de éste.Los comandos del SQL "puro" son para trabajar con la BD y los de SQL*Plus para formatear resultados,establecer opciones y editar o almacenar comandos SQL.
DESC tablaEjemplos:
DESC indice_; DESC iva91_; DESC ret91_; DESC soc91_; DESC iva92_; DESC ret92_; DESC soc92_; DESC iaes; DESC secto;Vemos el nombre de las columnas y el tipo de dato de las tablas del curso.
SELECT col1,col2,... FROM tablacol1,col2,.. son los nombres de las columnas de las tablas a visualizar y tabla el nombre de la tabla.Para seleccionar unas filas determinadas utilizamos la cláusula WHERE.
SELECT col1,col2,... FROM tabla WHERE condicióncondición es una expresión lógica del tipo >,<,=,etc.
Podemos sustituir el nombre de todas las columnas por *. SELECT * significa que deseamos visualizar todas las columnas de la tabla
Ejemplos:
SELECT np,v001 FROM iva92_Seleccionamos las columnas np,v001 de iva92_ para todas las filas de la tabla.
SELECT * FROM ret92_ WHERE np=mivalor;Seleccionamos todas las columnas de la tabla ret92_ para el valor de np mivalor.
SELECT np,v001,v002,v003,v010 FROM soc92_ WHERE v001 > 0;Seleccionamos las columnas np,v001,v002,v003 y v010 de la tabla soc92_ para aquellas filas con v001 mayor que cero.
CREATE TABLE tabla ( col1 tipo_dato_col1, col2 tipo_dato_col2, . ....... coln tipo_dato_coln )col1,..,col2 son el nombre de las columnas y tipo_dato_col* es el tipo dato ORACLE, que luego veremos en profundidad.
También podemos crear una tabla a partir de otra ya existente mediante una SELECT.
CREATE TABLE tabla1 AS SELECT * FROM tabla2Obviamente,las columnas de tabla1 son del mismo tipo que las de tabla2.
Ejemplos:
CREATE TABLE mikk AS SELECT * FROM ret91_;Creamos una tabla idéntica a ret91_
INSERT INTO tabla VALUES ( valcol1,valcol2,...);Si queremos introducir un dato nulo podemos sustituir valcoln por NULL.
Ejemplos:
INSERT INTO mikk VALUES(valornp,valorv000,....,valorv011);
DELETE FROM tabla WHERE condición
Ejemplos
DELETE FROM mikk WHERE np=valornp;Borra la fila de mikk con np=valornp;
UPDATE tabla SET col1=valcol1,...,colj=valcolj WHERE condiciónActualiza las columnas 1 a j de tabla en las filas que cumplen condición.Si no utilizamos la cláusula WHERE,todas las filas serán modificadas.
Ejemplos:
UPDATE mikk SET v001=0,v002=1000,v011=100 WHERE np=valnp;Modifica las columnas v001,v002,v011 para la fila con np=valnp;
DROP TABLE tabla
Ejemplo:
DROP TABLE mikk;Suprime la tabla mikk.
Estos son:
COLUMN [ { col | expresión } [opción ( (
TTITLE[ [ opción [texto|variable( .... ( [OFF|ON( (
Por ejemplo:
COLUMN col HEADING texto COLUMN col FORMAT formato TTITLE textoCOLUMN cambia la cabecera standard col por texto
COLUMN np HEADING "NUMERO PUENTE"COLUMN saca la columna formateada.
COLUMN np FORMAT $9999999999Combinamos las dos
COLUMN np FORMAT $9999999999 HEADING "NUMERO PUENTE"TTITLE (de Top Title) pone un título,una fecha y un número de página en la parte superior de ésta.Ej.
TTITLE 'SOCIEDADES FINANCIERAS'Veremos todas las opciones y posibilidades de COLUMN y TTITLE más adelante.
Estos son:
| COMANDO | ABREVIATURA | FUNCIÓN |
|---|---|---|
| APPEND | A texto | Añade texto fin de línea |
| CHANGE | C/anterior/nuevo | Cambia textor anterior por nuevo |
| CHANGE | C/texto/ | Borra texto de una línea |
| DEL | DEL | Borra una línea |
| INPUT | I | Añade línea |
| INPUT texto | I texto | Añade una línea con texto |
| LIST | L | Lista todas las líneas del buffer |
| LIST n | L n | Lista la línea número n |
| LIST m n | L m n | Lista las líneas entre m y n |
| RUN | R | Ejecuta el comando SQL del buffer |
| CLEAR BUFFER | CL BUFF | Limpia el buffer (borra todas las líneas) |
Cuando se introduce un comando SQL,el sistema lo almacena en el buffer SQL.Este comando es el comando actual SQL y permanece en el buffer hasta que se introduce un nuevo comando SQL( no SQL*Plus) ó hasta que se limpia el buffer.
Los comandos de edición de este buffer,excepto LIST y RUN, afectan a una línea,la línea actual,y está señalada con un arterisco(*) cuando se lista el comando actual.
SPOOL fichero.No es necesaria extensión.ORACLE pone la extensión .LST ó .LIS por defecto.Cuando se quiere detener esta salida de la pantalla a fichero,se utiliza:
SPOOL OFF.Para enviar la salida por pantalla a impresora utilizamos:
SPOOL OUT.
SAVE ficheroORACLE le añade por defecto la extensión .SQL.Si el fichero existe, sobreescribe.El comando SAVE no guarda comandos de SQL*Plus tales como COLUMN ó TTITLE.
Para recuperar un fichero almacenado con el comando SAVE utilizamos:
GET ficheroExiste un comando de SQL*Plus que nos permite recuperar un fichero y ejecutarlo:
START ficheroSTART admite el paso de parámetros cuando el comando de SQL contiene datos paramétricos.Los datos paramétricos se indican con & seguido de un número del 1 al 9. Por ejemplo:
SELECT * FROM Soc92_ WHERE np=&1 SAVE mifichero START mifichero valor_npCuando se archiva en un fichero el comando actual de SQL mediante SAVE,las sentencias SQL*Plus no se archivan con él.Para evitar esto,podemos establecer otro buffer distinto del de SQL con:
SET BUFFER nombreEjemplo:
SET BUFFER mibuffer COLUMN np HEADING "NUMERO PUENTE" COLUMN v000 HEADING "BASE IMPONIBLE AL 6%" COLUMN v002 HEADING "BASE IMPONIBLE AL 12%" COLUMN v004 HEADING "BASE IMPONIBLE AL 33%" TTITLE "BASES IMPONIBLES" SELECT np,v000,v002,v004 FROM IVA91_ WHERE np=valor SAVE mibase START mibase
Normalmente,lo que nosotros utilizaremos será el editor del sistema operativo SQL*Plus permite acceder directamente al editor de nuestro S.0. mediante
EDIT ficheroSQL*Plus le añade la extensión .SQL por defecto.Para ejecutarlo podemos utilizar un comando equivalente a START:
@fichero
EJEMPLO.
En nuestra base,actualizamos una variable de iva92 para la empresa con np=x sumándole una cte y restándola esta cte de la misma variable.Este cambio lo reflejamos en una tabla llamada cambios con campos np,ch1,ch2.LLamamos a la variable VAR1 y la cte=1000.
CREATE TABLE CAMBIOS ( NP NUMBER(10), VAR1 NUMBER(15), VAR2 NUMBER(15) ); UPDATE iva92_ SET var1=var1+1000 WHERE np=x; UPDATE iva92_ SET var1=var1-1000 WHERE np=x; INSERT INTO cambios VALUES (x,500,-500); COMMIT WORK;La sentencia COMMIT hace permanentes los cambios.La setencia ROLLBACK deshace los cambios hechos en la BD( como si nunca se hubiese ejecutado la sentencia SQL).
Los SAVEPOINTS(puntos de salvaguardia) sirven para dividir las transacciones largas en partes más pequeñas.Se puede deshacer sólo parte de una transacción a través de los SAVEPOINTS( bloques PL/SQL)
EJEMPLO:
UPDATE iva92_ SET var1=var1+1000 WHERE np=x; UPDATE iva92_ SET var1=var1-1000 WHERE np=x; SAVEPOINT S1; INSERT INTO cambios VALUES (x,500,-500); ROLLBACK TO S1; COMMIT WORK;
SQL> SET PAUSE "sigue..." SQL> SET PAUSE ONNotar que los comandos de SQL*PLUS para manejo de salidas no tienen porque ir terminados con ';'. Una vez establecida la pausa en la salida, ejecutamos:
SQL> SELECT NP, ASAL, MARCA FROM SOC91_;
sigue...
NP ASAL MARCA
---------- ---------- ------
-99999922 5 1
-99999912 73 1
-99999797 9 1
-99999769 300 1
-99999743 79 14
....
Al utilizar sentencias de SQL podemos utilizar varias líneas, de forma que el comando no acaba hasta que no encuentra un ';'.
SQL> select np,asal,v000 2 from soc91_; sigue... NP ASAL V000 ---------- ---------- ---------- -99999922 5 0 -99999912 73 0 ...Es posible mediante el comodín '*' dar salida a todas las columnas de la tabla:
SQL> select * from indice_;
sigue...
NP DH91 IAE91 C SI91 DH92 IAE92 C SI92 DH93 IAE93 C S
--------- --------- ----- - --------- --------- ----- - --------- --------- ----
DH94 IAE94 C SI94 CIF RAZON
--------- ----- - --------- ---------- -----------------------------------------
-99999999 0 0 0 0 0 0
1 9 A 1100 A ACME S.L.
-99999986 0 0 0 0 9 1833 B 12
0 0 B ACME II S.A.
Al utilizar el comodín las columnas aparecen el el orden en el que se especificó al crear la tabla.
Podemos apreciar como las filas se dividen en varias líneas, haciendo más difícil su lectura. El uso de el comodín '*' no siempre es posible ya que cuando hay muchas columnas, nos dara un desbordamiento de memoria:
SQL> select * from soc91_; desbordamiento de memoria. Use el comando SET para reducir ARRAYSIZE o aumentarPara evitar el anterior error debemos disminuir el ARRAYSIZE que es el número de filas que oracle nos manda en un solo bloque (llamado batch), de forma que al disminuirlo necesitemos menos memoria. Por defecto este valor es 20 y no es conveniente cambiarlo ya que no tiene mucho sentido dar salida a filas muy largas. Para cambiar el valor de ARRAYSIZE se puede hacer de dos formas:
SQL> SELECT AGRUP FROM IAES; AGRUP --------- 1 1 1 1 ...... 2 2 2Como vemos si lo que queremos es ver los diferentes valores de la columna AGRUP, debemos utilizar la cláusula DISTINCT:
SQL> SELECT DISTINCT AGRUP FROM IAES; AGRUP --------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 14 filas seleccionadas.
SQL> SELECT NP NPUENTE,V049 EXPORTACIONES FROM SOC91_; NPUENTE EXPORTACIONES --------- ------------- -99999922 0 -99999912 156380400 -99999797 0 ......Si el alias tiene espacios en blanco, lo pondremos entre comillas:
SQL> SELECT NP "N. PUENTE",V001 "GASTOS ESTABLECIMIENTO" FROM SOC91_; N. PUENTE GASTOS ESTABLECIMIENTO --------- ---------------------- -99999922 0 -99999912 0 -99999797 3814117 .....
| Operador | Significado |
|---|---|
| = | igual a |
| != , ^= ó <> | diferente de |
| > | mayor |
| >= | mayor o igual |
| < | menor que |
| <= | menor o igual |
| BETWEEN ...AND .. | entre dos valores |
| IN (lista) | existe en lista |
| LIKE | se adapta a una estructura de caractes |
| IS NULL | es un valor nulo (no 0) |
SQL> SELECT *
2 FROM IAES
3 WHERE AGRUP = 1;
| | |
| | ----> constante
| ------> operador de comparación|
-----------> nombre de la columna
En el ejemplo anterior listariamos todos los códigos de iae para el código de agrupación 1.
Cuando la columna es de tipo carácter la constante debe de ir entre comillas simples. Por ejemplo, si queremos saber la delegación de hacienda de todas las sociedades que declaran en el 92 con iae '4' introduciremos el siguiente comando:
SQL> SELECT NP,IAE92,DH92 2 FROM INDICE_ 3 WHERE IAE92='4'; NP IAE92 DH92 --------- ----- --------- -99999685 4 26 -99999416 4 1 -99998630 4 1 ....También podemos utilizar expresiones compuestas mediante OR y AND. De esta forma podemos conocer todas las sociedades con iae 4 (Actividades agrarias ....) y que no declararón en la dh 1:
SQL> SELECT NP,IAE92,DH92 2 FROM INDICE_ 3 WHERE IAE92='4' AND DH92<>1; NP IAE92 DH92 --------- ----- --------- -99999685 4 26 -99998378 4 41 -99996666 4 2 .....Entre los operadores lógicos NOT, AND y OR, existe la prioridad dada por el orden anterior. Para cambiar este orden es posible poner paréntesis de forma que:
SQL> SELECT NP,IAE91,IAE92,DH91,DH92 2 FROM INDICE_ 3 WHERE IAE91 != IAE92 AND (IAE91='4' OR DH91=1); NP IAE91 IAE92 DH91 DH92 --------- ----- ----- --------- --------- -99999545 9 Z 1 1La consulta anterior nos da las sociedades cuyo iae cambia en los años 91 y 92 y, o bien declararón en la dh 1 en el 91 o bien su iae en el 91 erá el 4.
SQL> SELECT NP, V049 EXPORTACIONES 2 FROM SOC91_ 3 WHERE V049 BETWEEN 10000000 AND 20000000; NP EXPORTACIONES --------- ------------- -99999443 11290018 -99996693 15151838Esta misma consulta tambien la podriamos hacer aplicando otros operadores de comparación:
SQL> SELECT NP, V049 EXPORTACIONES 2 FROM SOC91_ 3 WHERE NOT (V049 < 10000000 OR V049 > 20000000);Si lo que deseamos es negar el operador BETWEEN le podemos anteponer NOT. Si queremos seleccionar filas en las que el valor de un campo se encuentre dentro de una lista de valores se utiliza el operador IN. De esta forma seleccionar las sociedades con iae 'Z' o bien '9' se hará:
SQL> SELECT NP,IAE91 "IAE del 91 Z o 9"
2 FROM INDICE_
3 WHERE IAE91 IN ('Z','9');
NP IAE d
--------- -----
-99999578 Z
-99999545 9
-99999443 Z
....
De análoga manera para buscar aquellos valores que no estan la lista anteponemos el operador NOT a IN.
Veamos ahora como buscar valores de columnas carácter con el operador LIKE. Para ello el operador toma una cadena entre comillas simples en la cuál pueden existir los carácteres comodines:
| Símbolo | Significado |
|---|---|
| % | cualquier secuencia de cero o más caracteres. |
| _ | un sólo carácter, cualquiera. |
Como ejemplo veamos todos los iaes en cuya descripción existe la palabra 'Electri':
SQL> SELECT DES FROM IAES 2 WHERE DES LIKE '%Electri%'; DES -------------------------------------------------------------------------------- Produccion, Transporte Y Distribucion De Energia Electrica Fabricacion De Herramientas Y Articulos Acabados En Metales, Con Exclusion De Ma Fabricacion De Hilos Y Cables Electricos
SQL> SELECT NP,ASAL FROM SOC91_ 2 ORDER BY ASAL; NP ASAL --------- --------- -99998702 0 -99998133 0 ..... -99999075 1 -99997356 1 ..... -99996866 2 -99996704 2La consulta anterior nos ordenara la salida en función del número de asalariados en orden ascendente (por defecto). Si lo que deseamos es ordenar por varios campos, por ejemplo por asal y para los mismos asalariados por exportaciones:
SQL> SELECT NP, ASAL,V049 EXPORTACIONES FROM SOC91_ 2 ORDER BY ASAL, V049 DESC; NP ASAL EXPORTACIONES --------- --------- ------------- -99998702 0 0 -99998133 0 0 ..... -99996693 2 15151838 -99996866 2 0 ..... -99998318 3 0 -99996587 3 0 .....El campo por el que se ordena no es obligatorio que sea una columna de la consulta.
No hay que usar la cláusula ORDER BY en tablas muy grandes ya que Oracle al ordenar tomará lo que se llama segmentos temporales (espacio en disco para ordenaciones intermedias), provocando una mayor lentitud en el comando así como que posiblemente el usuario no tenga permisos para ese tipo de operaciones y se provoque el error:
ERROR: ORA-01652: unable to extend temp segment by 1024 in tablespace TEMPDe esta forma si se deseea hacer ordenaciones de tablas muy grandes lo mejor es que consulte con el administrador de la base de datos.
La información contenida en una fila de una tabla puede ampliarse con el contenido de la fila de otra tabla.El encadenamiento se realiza en base a los valores de dos campos,uno de la primera tabla y otro de la segunda.
Normalmente,las columnas unidas tienen el mismo significado y datos del mismo tipo, pero estas condiciones no son necesarias.Las columnas de unión pueden tener distinto significado ó las tablas pueden tener distinto número de filas.Una unión de tablas es simplemente ampliar la información de una fila de una tabla con datos de filas de otras tablas, sirviendo como encadenamiento para esta ampliación los valores de ciertas columnas de las tablas unidas.Se puede incluso unir una tabla consigo misma.
Para hacer una consulta en la que se unan filas de dos tablas hay que especificar las columnas de unión en la cláusula WHERE mediante la expresión que describa el encadenamiento.
SELECT t1.col1,t2.col2 FROM t1,t2 WHERE condición AND t1.col1=t2.col1;t1,t2 son las tablas a unir sobre t1 con la condición sobre t1 y el nexo de encadenamiento t1.col1=t2.col2
Ejemplo:
SELECT indice_.np,indice_.dh92,indice_.iae92,soc92_.v001 FROM indice_,soc92_ WHERE indice_.np=&minp AND indice_.np=soc92_.np;Nos hemos referido a las columnas de la forma tabla.col.Esto es necesario cuando hay columnas con el mismo noombre en las tablas a unir.En este caso,podemos utilizar un sendos alias para las tablas con el fin de escribir menos y realizar uniones de una tabla consigo misma.
En el ejemplo anterior,como la única columna que se llama igual en ambas tablas es np,ahorramos tiempo haciendo:
SELECT I.np,dh92,iae92,v001 FROM indice_ I,soc92_ S WHERE I.np=&minp AND I.np=S.np;
En una no-equi-unión,es fácil que cada fila de una tabla pueda quedar encadenada a muchas filas de la otra tabla,incluso a todas.Por ello,se ha de tener cuidado con los criterios de selección,ya que si las tablas son grandes,el resultado puede llegar a ser, si X es el número de filas de la primera tabla,e Y de la segunda, X*Y filas seleccionadas.Esto también puede ocurrir en una equi-unión,pero es bastante menos frecuente.
Ejemplo:
SELECT S1.np,S2.np,S1.v001,S2.v001 FROM soc92_ S1,soc92_ S2 WHERE S1.v001 > S2.v001 AND S2.v001 > 0; SELECT S1.np,S2.np,S1.v001,S2.v001 FROM soc91_ S1,soc92_ S2 WHERE S1.v001 > S2.v001 ORDER BY S2.v001 DESC;
SELECT DISTINCT S2.np,S2.v002 FROM soc92_ S2, soc92_ S3 WHERE S2.v002 > S3.v002 AND S3.v001 > 0 ORDER BY S2.v002 DESC;
SELECT S1.np,S2.np,S1.v001,S2.v001 FROM soc91_ S1,soc92_ S2 WHERE S1.v001 > S2.v001 AND ROWNUM < 21 ORDER BY S2.v001 DESC;
Si una fila de una de las tablas no cumple las condiciones específicas de encadenamiento,no aparecerá en el resultado de la consulta.Por ejemplo,si hay una empresa en la tabla INDICE_ que no declare en sociedades en el año 92 nunca podrá encadenarse con una fila de SOC92_.Estas filas se pueden visualizar utilizando el operador de UNIÓN EXTERNA (+). Este operador se utiliza en la cláusula WHERE detrás de la columna de la tabla que contiene filas que no cumplen la condición de unión.El resultado del operador unión externa es que ORACLE trata la tabla a unir como si tuviese añadidas filas con una de las columnas cumpliendo la condición de encadenamiento y el resto con valores nulos.
Ejemplo:
SELECT I.np,S.np,v001 FROM indice_ I,soc92_ S WHERE I.np=S.np (+) AND I.dh92 = 28;
SELECT I.np,S.np,v001 FROM indice_ I,soc92_ S WHERE I.np=S.np (+) AND I.dh92 = 28 AND S.np IS NULL;
COLUMN columna FORMAT modeloEste comando al ser propio de SQL*PLUS no lleva ';' como finalizador de sentencia. Por ejemplo para ver el número de asalariados de las sociedades en el año 1992 procederiamos:
SQL> COLUMN sal FORMAT $99,999 SQL> SELECT np, asal FROM soc92_; NP ASAL --------- --------- -99999824 2 -99999797 7 .......Es posible especificar diferentes formatos, algunos de los más interesantes son:
| Especificación formato | Originalmente | se convierte en | Descripción | |
|---|---|---|---|---|
| 999.99 | 45.839 | 45.84 | Redondea a dos posiciones decimales. | |
| 99,999 | 56789 | 56,789 | Utiliza como separador de miles la ','. | |
| 0999 | 45 | 0045 | Pone ceros a la izqda. | |
| B999 | 0 | Un valor 0 aparece como blanco. | ||
| 9.99EEEE | 120000 | 1.20E+05 | Notación exponencial. | |
SQL> SELECT np, v051 + v052 + v053 - v054 "VENTAS" FROM soc92_; NP VENTAS --------- --------- -99999824 4275000 -99999797 65750000 ......De esta forma podremos calcular las ventas de las sociedades (en este caso modelo normal) como suma y resta de sus campos exportaciones, otras ventas, prestaciones de servicios y devoluciones y rappels sobre ventas.
Las expresiones pueden estar tambien en la cláusula WHERE, y en la cláusula ORDER BY:
SQL> SELECT np,asal 2 FROM soc92_ 3 WHERE v051+v052+v053-v054 >10E4; NP ASAL --------- --------- -99999824 2 -99999797 7 .....Esto no da las sociedades con ventas mayores a 10000 pts. Si deseamos hacer un ranking de sociedades por ventas hariamos:
SQL> SELECT np, 2 > FROM soc92_ 3 > ORDER BY v051+v052+v053-v054 DESC;Es posible fijar prioridades aritméticas con la inclusión de paréntesis en las expresiones.
| FUNCION | VALOR DEVUELTO |
|---|---|
| ABS(n) | Valor absoluto de n |
| CEIL(n) | Entero más pequeño mayor o igual a n. |
| COS(n) | Coseno de n. |
| COSH(n) | Coseno hiperbólico de n. |
| EXP(n) | e elevado a la n-esima potencia. |
| FLOOR(n) | Entero más grande igual o menor que n. |
| LN(n) | Logaritmo natural de n donde n>0 |
| LOG(m,n) | Logaritmo, base m, de n. |
| MOD(m,n) | Resto de dividir m por n. |
| POWER(m,n) | m elevado a la n-esima potencia. |
| ROUND(n,[m]) | n redondeado a m posiciones decimales. m por defecto es 0. |
| SIGN(n) | si n<0, -1; si n=0, 0; si n>0, 1; |
| SIN(n) | Seno de n en radianes. |
| SINH(n) | Seno hiperbólico de n. |
| SQRT(n) | Raíz cuadrada de n; si n<0, NULL. |
| TAN(n) | Tangente de n en radianes. |
| TANH(n) | Tangente hiperbólica de n. |
| TRUNC(n,[m]) | n truncado a m lugares decimales; m por defecto es 0. |
| AVG(DISTINCT |ALL n) | Valor medio de n ignorando los nulos. |
| COUNT([ALL] *) | Número de filas devuelto por una consulta o subconsulta. |
| COUNT([DISTINCT|ALL] expr) | Numero de filas donde expr no es nulo. |
| MAX([DISTINCT|ALL] expr) | Valor máximo de expr. |
| MIN([DISTINCT|ALL] expr) | Valor mínimo de expr. |
| STDDEV([DISTINCT|ALL] expr) | Desviación tìpica de n, ignora los valores nulos. |
| SUM([DISTINCT|ALL]n) | Suma de los valores de n. |
| VARIANCE([DISTINCT|ALL] n) | Varianza de n, ignora valores nulos. |
Veamos ahora algunos ejemplos de consulta a la base de datos utilizando funciones de agrupación.
SQL> SELECT COUNT(*) "Numero", AVG(v028) "Media ss" 2 FROM soc91_ 3 WHERE asal>=10; Numero Media ss --------- --------- 45 340237180
SQL> SELECT COUNT(*) "Numero", AVG(v028) "Media ss" 2 FROM soc91_ 3 WHERE asal>=10; Numero Media ss --------- --------- 45 340237180
SQL> SELECT COUNT(DISTINCT SECTOR) 2 FROM IAES; COUNT(DISTINCTSECTOR) --------------------- 68
Imaginemos que queremos saber los sueldos y salarios brutos para aquellas (pueden ser varias) sociedades con el número máximo de asalariados. Si intentamos hacer la siguiente consulta:
SQL> SELECT v028,MAX(asal) FROM soc91_; SELECT v028,MAX(asal) FROM soc91_ ERROR en línea 1: ORA-00937: la función de grupo no es de grupo simpleNos da un error ya que no podemos mezclar funciones de agrupación con datos individuales, para obtener nuestro objetivo deberiamos:
SQL> SELECT v028,asal 2 FROM soc91_ 3 WHERE asal= (SELECT MAX(asal) FROM soc91_); V028 ASAL --------- --------- 1,178E+09 834De esta forma introducimos el concepto de subconsulta que ya veremos más adelante. En el ejemplo anterior la subconsulta 'SELECT MAX(....' debe devolver un solo valor para de esta manera poder ser comparado con el campo asal.
Las funciones de agrupación al trabajar sobre un conjunto de filas tienen su principal uso en consultas en donde aparezca la cláusula GROUP BY.
SQL> SELECT iae91,AVG(asal) 2 FROM soc91_,indice_ 3 WHERE indice_.np=soc91_.np 4 GROUP BY iae91; IAE91 AVG(ASAL) ----- --------- 11511 182 11515 2 .......
Una vez hecho el GROUP BY no podemos filtrar la salida. Es decir, si queremos sacar aquellos iaes para los cuales haya al menos dos sociedades, ¿Cómo hacerlo?. Para ello utilizaremos la cláusula HAVING. Está cláusula solo se puede utilizar tras un GROUP BY, y su misión es filtrar la salida al igual que WHERE hace en la sentencia SELECT.
SQL> SELECT iae91, COUNT(*), AVG(asal) 2 FROM soc91_, indice_ 3 WHERE indice_.np = soc91_.np 4 GROUP BY iae91 5 HAVING COUNT(*)>2; IAE91 COUNT(*) AVG(ASAL) ----- --------- --------- 15011 3 65 4 4 33,5 Z 7 62,285714
Como último ejemplo compliquemos la consulta anterior sacando la média y número de los iaes cuyos asalariados medios sean mayor que la media de asalariados del iae '4'.
SQL> SELECT iae91, COUNT(*), AVG(asal) 2 FROM soc91_,indice_ 3 WHERE indice_.np = soc91_.np 4 GROUP BY iae91 5 HAVING AVG(asal) > 6 (SELECT AVG(ASAL) FROM soc91_,indice_ 7 WHERE iae91='4' AND soc91_.np = indice_.np ); IAE91 COUNT(*) AVG(ASAL) ----- --------- --------- 11511 1 182 12223 1 73 .....
SQL> COLUMN RAZON FORMAT A30 SQL> SELECT NP,RAZON FROM INDICE_; NP RAZON --------- ------------------------------ -99999999 ACME 1 -99999986 ACME 2 -99999978 ACME 3 -99999972 ESTA EMPRESA TIENE LA RAZON MUY LARGA -99999971 ACME 4 ......En el ejemplo anterior podemos ver que cuando la razon tiene más de 30 caracteres se pasa a una nueva línea, para evitar esto podemos utilizar el comando SQL*PLUS COLUMN ... TRUNC.
SQL> COLUMN RAZON TRUNCSi se desea quitar o limpiar la especificación de una columna se hará con COLUMN ... CLEAR.
La especificación de una columna queda vigente hasta que se vuelva a cambiar o bien hasta que se salga de la sesión SQL
SQL> COLUMN RAZON CLEAR SQL> COLUMN IAERAZON FORMAT A30 SQL> SELECT iae91 || '-' || razon IAERAZON 2 FROM indice_; IAERAZON ------------------------------ -ACME 1 -ACME 2 1504-ACME 3 .....
| Función | Ejemplo | Resultado |
|---|---|---|
| DECODE | DECODE(iae91,'Z',0,'4',1,2) | Traduce posibles valores del iae91 a valores numéricos. El último es el valor por defecto. |
| INITCAP | INITCAP(razon) | Pone en mayúsculas la primera letra de cada palabra. |
| INSTR | INSTR(marca,'0') | Posición del primer caracter '0' en marca. |
| LENGTH | LENGTH(razon) | Longitud de la razón en caracteres. |
| LOWER | LOWER(razon) | Convierte la razón a minúsculas. |
| SUBSTR | SUBSTR(razon,1,2) | Toma dos caracteres de la razón empezando por el 1º |
| UPPER | UPPER(razon) | Convierte la razón a mayúsculas. |
La función DECODE devuelve cualquier tipo de dato. El valor devuelto será del mismo tipo que su tercer argumento.
SQL> SELECT COUNT(*), 2 DECODE(IAE91,'Z','Activ. no clasif.','Otras actividades') 3 FROM INDICE_,SOC91_ 4 WHERE INDICE_.NP=SOC91_.NP 5 GROUP BY DECODE(IAE91,'Z','Activ. no clasif.','Otras actividades'); COUNT(*) DECODE(IAE91,'Z', --------- ----------------- 7 Activ. no clasif. 70 Otras actividadesEs posible operar con la salida de una función decode, veamos esta curiosa forma de acumular los asalariados para las sociedades con iae '4':
SQL> SELECT SUM(DECODE(IAE91,'4',1,0)*ASAL) 2 FROM SOC91_,INDICE_ 3 WHERE SOC91_.NP=INDICE_.NP; SUM(DECODE(IAE91,'4',1,0)*ASAL) ------------------------------- 134Por último veamos el uso de la función INSTR() como pilar fundamental para mirar las marcas en las tablas. La marca de entidad financiera en el año 91 viene identificada por un carácter '0' en la columna marca, por tanto si quisieramos ver la razon de las sociedades financieras que tenemos:
SQL> SELECT iae91,razon 2 FROM indice_, soc91_ 3 WHERE indice_.np=soc91_.np AND INSTR(marca,'0')>0; IAE91 RAZON ----- ----------------------------------------------------------- 1811 BANCO ACME
La consulta de las marcas siempre se hará con la función INSTR de tal manera que si esta devuelve un 0 indicará la ausencia de marca y en caso contrario su presencia.
SQL> COLUMN Y HEADING ' ' SQL> COLUMN Z HEADING ' ' SQL> SELECT 'La sociedad' Y,np, 'declara en el año 91 iae' Z, iae91 2 FROM indice_; NP IAE91 ----------- --------- ------------------------ ----- La sociedad -99999999 declara en el año 91 iae La sociedad -99999922 declara en el año 91 iae 1504 La sociedad -99999912 declara en el año 91 iae 12223 ....
Una valor nulo consiste en la ausencia de valor.Una columna tiene un valor nulo cuando no contiene ningún valor.
Un campo con valor nulo es diferente a un campo con valor cero,ya que cero es un dato.Un valor nulo no se debe utilizar para hacer referencia a otro valor,cono cero ó un carater en blanco.Los valores nulos se visualizan como blancos.
Los valores nulos son representados en la B.D. con un sólo byte.Por ello se recomienda ,en tablas con muchos campos,que las columnas con más probabilidad de contener valores nulos sean definidas las últimas en la sentencia CREATE TABLE para conservar espacio en disco.
Cuando una nueva fila es insertada y su valor es omitido,el valor por defecto es el NULO.
Ejemplo:
SELECT * FROM indice_ WHERE np IS NULL; SELECT * FROM indice_ WHERE np IS NOT NULL AND ROWNUM < 51;
No es posible la utilización del operador igual."np=NULL" no es válido( resp. "np != NULL").
Un valor carácter con cero caracteres se considera un valor nulo.
Ejemplo:
CREATE TABLE kk AS SELECT * FROM iaes;
INSERT INTO kk VALUES( NULL,69,15,NULL); INSERT INTO kk (sector,agrup) VALUES(69,15);
SELECT * FROM indice_ WHERE iae IS NULL;
DELETE FROM kk WHERE iae IS NULL;
No se recomienda utilizar valores carácter de longitud cero como valor nulo.Como veremos después,las operaciones con valores nulos requieren un tratamiento especial.
Ejemplo:
INSERT INTO kk
(iae,desc) VALUES ('5ABC','Este iae no existe');
INSERT INTO
(sector,agrup) VALUES (69,15);
SELECT COUNT(sector),COUNT(iae) FROM kk WHERE sector > 67;
FUNCIÓN NVL. En algunos casos es necesario considerar un valor nulo como cero u otro valor definido.De esto se encarga la función NVL. NVL(col,val)
Si el valor actual de col es NULO,NVL le da el valor val,en caso contrario,col mantiene su valor.La función NVL puede dar valores numéricos,carácter ó fecha,según el tipo de dato de col.
Ejemplo:
SELECT COUNT(sector),COUNT(iae) FROM kk WHERE sector > 67; SELECT COUNT(sector),COUNT(NVL(iae,' ')) FROM kk WHERE sector > 67; SELECT COUNT(sector),COUNT(iae) FROM kk WHERE iae LIKE '5%'; SELECT COUNT(NVL(sector,0)),COUNT(iae) FROM kk WHERE iae LIKE '5%';
ORACLE usa un formato interno para almacenar fechas.Estas son almacenadas en campos de longitud fija de 7 bytes cada uno,correspondiendo cada byte a siglos, años, meses, dias, horas, minutos y segundos respectivamente.
El formato de visualización por defecto es de la forma dd/mm/yy (día/mes/año) . Si incluimos una fecha y no hacemos referencia a la hora,esta por defecto es 12:00:00 A.M. (medianoche) .SQL*Plus reconoce una columna especial que da la hora y la fecha actual,SYSDATE.Esta pseudocolumna se puede utilizar en todas las tablas de la B.D,aunque no esté presente en las mismas.
Para visualizar la fecha en otros formatos utilizamos la función TO_CHAR.
TO_CHAR(date,formato)
Date es un valor fecha y formato se especifica entre ' '.Si no hay segundo argumento, se utiliza el formato estándar.
La función 'recíproca' de TO_CHAR es TO_DATE.
TO_DATE(chardate,format)
TO_DATE convierte un valor carácter con una fecha chardate en un valor fecha. Chardate se interpreta con el formato format.Si éste no existe,se interpreta con el formato estándar.
Ejemplo de formatos son:
Utilizaremos la tabla DUAL para hacer los ejemplos.DUAL es una tabla de la B.D. con un único campo DUMMY vacio.
Podemos visualizarlos haciendo:
SELECT TO_CHAR(SYSDATE,'formato') FROM tabla(dual).
También podemos convertir una cadena representando una fecha en fecha
SELECT TO_DATE('10/01/96') FROM tabla (dual).
Se pueden hacer operaciones aritméticas con valores fecha.Están son:
Ejemplos:
SELECT SYSDATE+20 FROM DUAL;
SELECT SYSDATE-20 FROM DUAL;
SELECT TO_DATE('10/01/96')-TO_DATE('2/01/96') FROM DUAL;
Para hacer operaciones no sólo con días,existen las funciones:
NEXT_DAY(SYSDATE,'LUNES');
SELECT TO_CHAR(NEW_TIME(SYSDATE,'GMT','HST'),'HH:MI PM') FROM DUAL;
Ejemplos:
COLUMN DIFFECHA HEADING DIFERENCIA
SELECT ROUND(TO_DATE('10/01/96 4:00 PM','DD/MM/YY HH:MI PM')
-TO_DATE('8/01/96 9:00 AM','DD/MM/YY HH:MI PM')) DIFFECHA
FROM DUAL;
COLUMN DIFFECHA HEADING DIFERENCIA
SELECT TO_DATE('10/01/96 4:00 PM','DD/MM/YY HH:MI PM')-
TO_DATE('8/01/96 9:00 AM','DD/MM/YY HH:MI PM') DIFFECHA
FROM DUAL;
COLUMN DIFFECHA HEADING DIFERENCIA
SELECT ROUND(TO_DATE('10/01/96 4:00 PM','DD/MM/YY HH:MI PM'))- ROUND(TO_DATE('8/01/96 9:00 AM','DD/MM/YY HH:MI PM')) DIFFECHA
FROM DUAL;
SQL> COLUMN DESCRIPCIÓN FORMAT A30 SQL> SELECT sector, des DESCRIPCIÓN 2 FROM SECTO 3 WHERE des LIKE '%grari%'; SECTOR DESCRIPCIÓN --------- ------------------------------ 1 Actividades Agrarias, Ganadera s y Pesqueras SQL>SELECT soc92_.np Np, indice_.iae92 Iae92, iaes.sector Sector 2 FROM iaes, soc92_, indice_ 3 WHERE soc92_.asal > (SELECT AVG(asal) 4 FROM iaes,indice_,soc92_ 5 WHERE iaes.agrup=1 6 AND indice_.iae92=iaes.iae 7 AND indice_.np=soc92_.np ) 8 AND indice_.iae92=iaes.iae AND indice_.np=soc92_.np; NP IAE92 SECTOR --------- ----- --------- -99999769 1311 16 -99998007 1343 19 -99997475 1343 19 ....
Es posible utilizar subconsultas en cláusulas WHERE de cualquier comando. Concretamente los comandos que admiten WHERE son: SELECT, UPDATE, DELETE, INSERT y CREATE.
SQL>SELECT soc92_.np Np, indice_.iae92 Iae92, iaes.sector Sector 2 FROM iaes, soc92_, indice_ 3 WHERE soc92_.asal > ALL (SELECT asal 4 FROM iaes,indice_,soc92_ 5 WHERE iaes.agrup=1 6 AND indice_.iae92=iaes.iae 7 AND indice_.np=soc92_.np ) 8 AND indice_.iae92=iaes.iae AND indice_.np=soc92_.np; NP IAE92 SECTOR --------- ----- --------- -99999769 1311 16 -99998007 1343 19 -99997475 1343 19
Analogamente podríamos hacerlo para aquellas que superen en número de asalariados al menos a una de la agrupación 1. Esto se haría con ANY.
SQL> SELECT soc91_.np, v049+v050+v051-v052 2 FROM soc91_,indice_ 3 WHERE iae91 IN ( SELECT iae91 FROM indice_ 4 WHERE cif91='Q') 5 AND indice_.np=soc91_.np NP V049+V050+V051-V052 --------- ------------------- -99999445 300337184 -99996595 0 ......
SQL> SELECT soc91_.np, v033 GP 2 FROM soc91_,indice_ 3 WHERE (substr(indice_.iae91,1,4),indice_.cif91) IN 4 (SELECT substr(indice_.iae92,1,4),indice_.cif92 5 FROM soc92_,indice_ 6 WHERE soc92_.asal BETWEEN 10 AND 100 7 AND indice_.np=soc92_.np) 8 AND indice_.np=soc91_.np NP GP --------- --------- -99999797 61398624 -99999743 452729216 ....
SQL> SELECT soc91_.np, v033 GP,indice_.si91 SI 2 FROM soc91_,indice_ 3 WHERE 4 (substr(indice_.iae91,1,4),indice_.cif91) IN 5 (SELECT substr(indice_.iae92,1,4),indice_.cif92 6 FROM soc92_,indice_ 7 WHERE soc92_.asal BETWEEN 10 AND 100 8 AND indice_.np=soc92_.np) 9 AND indice_.si91 = 10 (SELECT indice_.si92 FROM soc92_,indice_ 11 WHERE indice_.np=soc92_.np 12 AND indice_.np= -99999824) 13 AND indice_.np=soc91_.np NP GP SI --------- --------- --------- -99999797 61398624 11 -99999743 452729216 11 .....
SQL>SELECT soc92_.np Np, indice_.iae92 Iae92, iaes.sector Sector 2 FROM iaes, soc92_, indice_ 3 WHERE soc92_.asal > ANY (SELECT asal 4 FROM soc92_ 5 WHERE INSTR(marca,'2')>0 6 UNION 7 SELECT asal 8 FROM soc92_ 9 WHERE INSTR(marca,'5')>0 10 ) 11 AND indice_.iae92=iaes.iae AND indice_.np=soc92_.np NP IAE92 SECTOR --------- ----- --------- -99996667 1241 10 -99996563 1249 11 ...
Si se emplea la cláusula ORDER BY, debe situarse al final de toda la consulta, no al final de cada SELECT. Como los nombres de las columnas correspondientes pueden ser diferentes, se debe especificar la columna de ordenación por su posición en la tabla resultante:
ORDER BY 2si se ordena por la segunda columna.
Supongamos que queremos encontrar las sociedades que tienen unos gastos de personal en el 92 (v033) superiores a la media de los gastos de personal para aquellas sociedades con el mismo iae en el 92. La consulta principal sería:
SELECT A.np, A.v033 Gp FROM soc91_ A, indice_ B WHERE A.v033 > ( subconsulta que me de la media de Gp para el iae de la fila de la consulta principal) AND A.np=B.np;La subconsulta debería de ser:
SELECT AVG(soc91_.v033) FROM soc91_,indice_ WHERE iae_de_fila_consulta_principal = indice_.iae AND soc91_np=indice_.np;Donde 'iae_de_fila_consulta_principal' será el iae de la sociedad que se procesa en la consulta principal. Si ponemos la consulta toda junta sería:
SQL>SELECT A.np, A.v033 GP 2 FROM soc91_ A, indice_ B 3 WHERE A.v033 > 4 (SELECT AVG(C.v033) 5 FROM soc91_ C,indice_ D 6 WHERE B.iae91=D.iae91 7 AND C.np=D.np) 8 AND A.np=B.np NP GP --------- --------- -99999469 286503040 -99999443 816432896 ......Esta misma consulta la podriamos hacer por sector, lo cual complica aún mas la sentencia.
SQL>SELECT B.np,C.sector, B.v033 GP 2 FROM iaes C,soc91_ B, indice_ A 3 WHERE B.v033 > 4 (SELECT AVG(E.v033) 5 FROM iaes F,soc91_ E,indice_ D 6 WHERE C.sector=F.sector 7 AND substr(F.iae,1,4)=substr(D.iae91,1,4) 8 AND D.np=E.np) 9 AND C.iae=substr(A.iae91,1,4) 10 AND A.np=B.np NP SECTOR GP --------- --------- --------- -99999613 4 804940032 -99999769 16 793277184 .......Al tipo de consultas que hemos hecho se les llama subconsultas correlacionadas, ya que la ejecución de cada subconsulta está en correlación con el valor de un campo de cada fila candidata de la consulta principal.
La clave para la compresión de las anteriores consultas está en el uso de un alias. Si la subconsulta trabaja en la misma tabla que la consulta principal, la consulta principal debe definir un alias para esa tabla, y la subconsulta utilizará el alias para referirse a campos de las filas candidatas escogidas por la consulta principal.
Por ejemplo, si queremos saber aquellas sociedades del año 91 para las cuales existen en el año siguiente (el 92) alguna sociedad con el mismo iae (exacto a 5 dígitos):
SQL>SELECT B.np 2 FROM soc91_ B, indice_ A 3 WHERE EXISTS 4 (SELECT * 5 FROM soc92_ E,indice_ D 6 WHERE A.iae91=D.iae92 7 AND D.np=E.np) 8 AND A.np=B.np NP --------- -99999797 -99999769 .....
El comando CREATE TABLE permite crear una tabla.En el momento de su creación se debe especificar el nombre de la tabla,los nombres de las columnas y su tipo de dato y las restricciones de integridad.
Sintaxis:
CREATE TABLE nombre_tabla
(
col1 tipo_dato_col1(longitud1) [r. de integridad1],
col2 tipo_dato_col2(longitud1) [r. de integridad2],
...... ...... ....
colk tipo_dato_colk(longitudk) [r. de integridadk]
);
El nombre de la tabla ha de cumplir los requisitos siguientes: Ha de empezar con una letra comprendida entre A y Z ( resp. a-z,las mayúsculas y minúsculas con equivalentes).
Puede contener letras,números o el carácter especial "_".También puede incluir los caracteres $ y #. La longitud máxima permitida es de 30 caracteres.
Debe ser único para cada tabla y no puede ser igual a una palabra reservada para el manejo de ORACLE.
Si el nombre de la tabla se encuentra entre comillas("),éste puede contener cualquier carácter,excepto (").En este caso,si existe distinción entre mayúsculas y minúsculas
Sigue las mismas reglas que para el nombre de tabla.
Los tipos básicos de datos ORACLE son:
RAW y LONG RAW son usados para almacenar datos que no pueden ser interpretados por ORACLE.(P. ej.: LONG RAW puede contener gráficos, sonido, imágenes, documentos,etc.).Básicamente,RAW y LONG RAW son equivalentes a VARCHAR2 y LONG respectivamente.
Son mecanismos usados por ORACLE para evitar la entrada de datos no válidos.Se almacenan en el Diccionario de Datos y el costo de su inclusión es, como mucho, el mismo que la ejecución de una sentencia SQL que evaluase la restricción.
Las más comunes son:
Una FOREIGN KEY es la columna ó conjunto de columnas incluidas en la definición de la restricción de integridad referencial basadas en una REFERENCED KEY( clave referenciada).
Una REFERENCED KEY es la calve primaria de la misma ó diferente tabla referenciada por una FOREIGN KEY.
A la tabla que incluye la FOREIGN KEY se la denomina tabla hija ó tabla dependiente y a la tabla que es referenciada por la tabla hijo,tabla padre.
La acción referencial soportada por FOREIGN KEY también incluye restricciones sobre UPDATE Y DELETE Estas son:
UPDATE Y DELETE RESTRICT.El valor de la clave referenciada no puede ser actualizado o borrado si ésto viola una restricción de integridad.
DELETE CASCADE.Cuando es borrado un valor en la clave referenciada, todas las filas en las tablas hijas que dependan de ésta también serán borradas.
También podemos utilizar CREATE TABLE para crear una tabla a partir de otra:
CREATE TABLE nombre_tabla1
AS SELECT col1,..,coln FROM nombre_tabla2
[WHERE condición]
Lógicamente,el tipo y ancho de las columnas de tabla1 se toma de las respectivas columnas de tabla2.
Ejemplos:
CREATE TABLE kkiaes AS SELECT * FROM IAES;
ALTER TABLE kkiaes ADD PRIMARY KEY(iae);
CREATE TABLE iaes_simp (NP NUMBER(10), IAE VARCHAR2(5) );
ALTER TABLE iaes_simp ADD FOREIGN KEY(iae) REFERENCES kkiaes(iae);
INSERT INTO ies_simp (np,iae)
VALUES (-9999999,'4');
INSERT INTO iaes_simp(iae)
VALUES ('8');
DELETE FROM kkiae WHERE iae='4';
El comando ALTER TABLE se utiliza principalmente para modificar las columnas de una tabla.Podemos:
ALTER TABLE nombre_tabla
MODIFY ( nombre_col tipo_dato(longitud),.....);
Vemos que después de MODIFY se introduce entre paréntesis los nombres de las columnas a modificar,el tipo de dato y la anchura y/ó número de decimales.
Ejemplo:
ALTER TABLE iaes_simp MODIFY (np NUMBER(12,2));
Se puede aumentar el ancho de una columna ó modificar el número de decimales.Sin embargo,para reducir el ancho de la columna ó cambiar el tipo de dato es necesario que todos los valores que contenga la columna sean NULL.
También podemos cambiar una columna de NOT NULL a NULL y de NULL a NOT NULL añadiendo la cláusula NULL( resp. NOT NULL) al final de las especificaciones de columna siempre que la columna esté vacía .
Ejemplo:
DELETE FROM iaes_simp;
ALTER TABLE iaes_simp MODIFY (np NUMBER(15) NOT NULL);
ALTER TABLE nombre_tabla
ADD ( nombre_col tipo_dato(longitud) restricción de integridad,....);
Ejemplo:
ALTER TABLE iaes_simp ADD ( ventas NUMBER(15) NOT NULL);
Cuando se añade una columna a una tabla,se sitúa a la derecha de la última columna existente.Todos los campos de la nueva columna son inicialmente NULL por lo que no se puede definir una nueva columna como NOT NULL a no ser que la tabla este vacía.Una vez creada,cambiando los nulos por valores,podemos utilizar ALTER TABLE .. MODIFY para hacerla NOT NULL.
Ejemplos:
INSERT INTO iaes_simp(np,iae,ventas) VALUES(-9999999,'4',1500000);
ALTER TABLE iaes_simp ADD (ingreso number(15));
UPDATE iaes_simp SET ingreso=0;
ALTER TABLE iaes_simp MODIFY ingreso NOT NULL;
DROP TABLE iaes_simp;
INSERT INTO nombre_tabla
VALUES (col1=val1,....,coln=valn);
Los valores han de ir separados por ",",los valores carácter y fechas entre comillas simples (" ' ").El orden de los valores debe ser el mismo que el de definición de columnas de la tabla en el momento de su creación.
Si se desea introducir un valor nulo en un campo,se escribe NULL sin comillas.Cuando queremos introducir muchos nulos en una fila podemos especificar sólo las columnas y los valores que queramos introducir.
Ejemplo:
CREATE TABLE kk AS SELECT np,v001,v002,v003,v004,v005 FROM iva92_; WHERE ROWNUM<11; INSERT INTO kk (np) VALUES (5676768); SELECT * FROM kk;
Para insertar un valor fecha,debe venir en el formato fecha estándar (DD/MM/YY).Si viene en otro formato,utilizamos TO_DATE para obtener el formato estándar.
Ejemplo:
ALTER TABLE kk ADD mifecha DATE;Añadimos a kk una columna tipo fecha,
INSERT INTO kk (np,mifecha) VALUES(969696,'8/02/96');
INSERT INTO kk(np,mifecha)
VALUES(5858558,TO_DATE('10-02-96 10:30','DD-MM-YY HH:MI');
INSERT INTO nombre_tabla1 (col1,col2,...)
SELECT nombre_tabla2.col1, nombre_tabla2.col2,....
FROM nombre_tabla2
WHERE condición.
La cláusula SELECT reemplazaría a VALUES.
Ejemplo:
INSERT INTO kk (np,v000) SELECT iva92_.np,iva92_.v00 FROM iva92_ WHERE iva92_.v000 BETWEEN 500000 AND 1000000;
UPDATE nombre_tabla
SET col1=val1,..,coln=valn
WHERE condición.
UPDATE actualiza cada columna especificada a los valores indicados por el signo "=" en las filas seleccionadas en la condición.
Ejemplo:
UPDATE kk SET v000=0,v001=0;v002=0,v003=0;v004=0,v005=0 WHERE v000 IS NULL;
Otra forma admitida por UPDATE es:
UPDATE nombre_tabla
SET columna=expresión
WHERE condición.
Expresión es una asignación válida en SQL(operaciones con otros campos de la tabla,una subconsulta,etc.).
Ejemplo:
UPDATE kk SET v000=v001/0.06 WHERE v001 > 0;
Si la cláusula SET contiene una subconsulta,está devuelve exactamente una fila para cada fila a actualizar.Si la subconsulta no retorna ninguna fila,entonces se introduce un nulo en la columna( ó columnas ) a actualizar.
Ejemplo:
UPDATE kk SET V00=(SELECT v001/0.06 FROM iva91_ WHERE iva91_.np=kk.np) WHERE v000=0;
SELECT * FROM kk;
ROLLBACK;
UPDATE kk SET V00=(SELECT v001/0.06 FROM iva91_ WHERE iva91_.np=kk.np) WHERE v000=0 AND np IN (SELECT np WHERE iva91_.np=kk.np);
DELETE FROM nombre_tabla
WHERE condición;
En condición podemos incluir,como en UPDATE,operaciones y subconsultas.
Ejemplos:
DELETE FROM kk WHERE np > 0;
DELETE FROM kk WHERE np NOT IN (SELECT np FROM iva91_ WHERE iva91_.np=kk.np);
Si no utilizamos la cláusula WHERE,borramos todas las filas de la tabla,pero ésta sigue existiendo,ocupando el espacio ya asignado,aunque sin ninguna fila.Para que la tabla desaparezca haremos DROP TABLE nombre_tabla.
Ejemplo:
DELETE FROM kk;
DESC kk
DROP TABLE kk;
DESC kk
Para que los cambios sean efectivos necesitamos:
Al utilizar UPDATE,INSERT ó DELETE podemos comprobar el resultado de nuestra acción mediante un SELECT y validarla con COMMIT si el resultado es el previsto.Si nos hemos equivocado,podemos deshacer los cambios con ROLLBACK.
ROLLBACK no se puede utilizar después de que los cambios son efectivos.
Es recomendable hacer efectivos os cambios de forma periódica para evitar que un fallo del equipo arruine todo el trabajo.
Para hacer los cambios efectivos automáticamente hacemos:
SET AUTOCOMMIT ON( ó INMEDIATE) que activa la validación automática.A través de SET AUTOCOMMIT OFF desactivamos la validación automática.
Obviamente.no podemos hacer ROLLBACK con AUTOCOMMIT ON.
En SQL*PLUS es posible utilizar comandos específicos para mejorar la salida de nuestras consultas. Estos comandos no serán muy necesarios ya que podemos flexibilizar el aspecto de la salida final importando la consulta en una hoja de cálculo. Veamos algunos de estos comandos:
SQL> TTITLE LEFT 'EJEMPLO DE TITULO IZDA.' SKIP COL 6 'AHORA UNA LINEA ABAJO Y E
MPEZANDO EN COLUMNA 6.'
SQL> BTITLE COL 5 'TITULO DE PIE DE PAGINA'
SQL> SELECT NP,ASAL FROM SOC92_;
EJEMPLO DE TITULO IZDA.
AHORA UNA LINEA ABAJO Y EMPEZANDO EN COLUMNA 6.
NP ASAL
--------- ---------
-99999824 2
-99999797 7
.......
-99998378 68
-99998318 4
TITULO DE PIE DE PAGINA
TTITLE OFF BTITLE OFF TTITLE ON BTITLE ON
Y para consultar los activos basta con el comando TTITLE sin ningún argumento.
Por ejemplo imaginemos que queremos separar la salida de los gastos de personal para sociedades del 91 de tal manera que cuando cambie la delegación de hacienda se produzca un salto de página:
SQL> BREAK ON DH91 SKIP 1
SQL> SELECT DH91, V033 GP
2 FROM INDICE_,SOC91_
3 WHERE INDICE_.NP=SOC91_.NP
4 ORDER BY DH91;
EJEMPLO DE TITULO IZDA.
AHORA UNA LINEA ABAJO Y EMPEZANDO EN COLUMNA 6.
DH91 GP
--------- ---------
1 63411840
147294368
133280032
.....
133280032
2 29357170
150820192
1,003E+09
.....
TITULO DE PIE DE PAGINA
BREAK ON DH91 SKIP 1 DUPLICATESPara cancelar los BREAKS establecidos:
CLEAR BREAKSy para ver los actuales:
BREAK
SQL> BREAK ON DH91 SKIP 1
SQL> COLUMN GP FORMAT 999,999,999 HEADING 'GASTOS DE PERSONAL'
SQL> COMPUTE SUM OF GP ON DH91
SQL> SELECT DH91, V033 GP
2 FROM INDICE_,SOC91_
3 WHERE INDICE_.NP=SOC91_.NP
4 ORDER BY DH91;
EJEMPLO DE TITULO IZDA.
AHORA UNA LINEA ABAJO Y EMPEZANDO EN COLUMNA 6.
DH91 GASTOS DE PERSONAL
--------- ------------------
1 63,411,840
147,294,368
61,398,624
.....
503,614,528
68,657,408
54,844,844
********* ------------------
sum ############
2 57,417
5,528,840
167,947,600
....
Al igual que antes hemos hecho la suma es posible hacer cálculos de:
COMPUTE MIN OF .... COMPUTE MAX OF .... COMPUTE AVG OF .... COMPUTE STD OF .... COMPUTE VAR OF .... COMPUTE COUNT OF ....Para ver los 'COMPUTE' que están activados:
SQL> COMPUTEPara borrar los 'COMPUTE':
SQL> CLEAR COMPUTES
Una vista es como una ventana a través de la cual se puede ver o modificar parte de la información de una tabla ó varias tablas.Aunque las vistas no contienen datos por sí mismas,parecen tablas y,con algunas restricciones,pueden tratarse como tales.Podemos entender una vista como una consulta almacenada ó una tabla virtual.
Existen varias razones para el uso de vistas:
Asimismo,una vista nos permite salvar aplicaciones de los cambios hechos en las tablas base.Por ejemplo,si una vista hace referencia a una consulta sobre tres columnas de una tabla y una cuarta columna es añadida a la tabla,la definición de la vista no es afectada y las aplicaciones basadas en la misma tampoco.
Para crear un vista utilizamos CREATE VIEW
CREATE VIEW nombre_vista [( col1,.....)]
AS consulta;
Se puede utilizar cualquier tipo de consulta para CREATE VIEW escepto las que contengan la cláusula ORDER BY.También se puede definir una vista en términos de otra vista.
Una vez creada la vista,podemos hacer consultas de la misma como si fuera una tabla.Podemos unir una vista con tablas ó con otras tablas.
Cuando se crea una vista,es posible utilizar expesiones o funciones dentro de la consulta que la define.Estas expresiones aparecen como columnas en la vista,pero los valores de sus campos se calculan en base a la tablas origen cada vez que se consulta la vista.Estas columnas se denominan columnas virtuales y se les puede dar un nombre de forma explícita ( con el comando CREATE VIEW ) ó de forma implícita con un alias.
Los cambios en las tablas se transmiten automáticamente a las vistas.Por ello,si se altera la estructura original de una tabla,no podemos asegurar que la vista responda correctamente.Esto se debe a que,cuando se crea la vista,la estructura de la tabla ó tablas origen entran a formar parte de la definición de la misma.Para resolver este problema podemos :
1.- Creación de vistas.
CREATE VIEW wivaret92(np,iae,sector,ventas,asal,salario) AS SELECT i.np,i.iae92,sector,iv.v000+iv.v002+iv.v004+iv.v016+iv.v017+iv.v022, NVL(r.v000,0),NVL(r.v001,0) FROM iaes ia,indice_ i,ret92_ r,iva92_ iv WHERE i.np=iv.np AND iv.np=r.np(+) AND substr(i.iae92,1,4)=ia.iae;
CREATE VIEW wsectasal92(sector,ventas,empasal,asal,salario) AS SELECT sector,SUM(iv.v000+iv.v002+iv.v004+iv.v016+iv.v017+iv.v022), COUNT(r.v000),SUM(NVL(r.v000,0)),SUM(NVL(r.v001,0)) FROM iaes ia,indice_ i,ret92_ r,iva92_ iv WHERE i.np=iv.np AND iv.np=r.np(+) AND substr(i.iae92,1,4)=ia.iae GROUP BY sector;
2.-Utilización de vistas
SELECT sector,asal,DECODE(asal,0,0,ventas/asal) vpe FROM wsectasal92 SORT BY vpe DESC;
SELECT v.np,sector,asal,salario,v000 asal91,v001 sal91 FROM wivaret92 v,ret91_ r WHERE v.np=r.np(+) AND v00 IS NOT NULL AND asal > 100;
DROP VIEW wsectasal92
Para el año 92,obtener el número puente,sector y ratio ventas/asal(VPE) de aquellas empresas madrileñas cuyo salario medio fue superior al salario medio del año 91 de su sector( tomando el sector del año 92).
 |