SQL

El SQL es un lenguaje de acceso a bases de datos que explota la flexibilidad y potencia de los sistemas relacionales permitiendo gran variedad de operaciones en éstos últimos.

Es un lenguaje declarativo de "alto nivel" o "de no procedimiento", que gracias a su fuerte base teórica y su orientación al manejo de conjuntos de registros, y no a registros individuales, permite una alta productividad en codificación y la orientación a objetos. De esta forma una sola sentencia puede equivaler a uno o más programas que se utilizarían en un lenguaje de bajo nivel orientado a registros.

Tipos de Sentencias

Las sentencias SQL pertenecen a dos categorías principales: Lenguaje de Definición de Datos, DDL y Lenguaje de Manipulación de Datos, DML. Estos dos lenguajes no son lenguajes en sí mismos, sino que es una forma de clasificar las sentencias de lenguaje SQL en función de su cometido. La diferencia principal reside en que el DDL crea objetos en la base de datos y sus efectos se pueden ver en el diccionario de la base de datos; mientras que el DML es el que permite consultar, insertar, modificar y eliminar la información almacenada en los objetos de la base de datos.

Cuando se ejecutan las sentencias DDL de SQL, el SGBD confirma la transacción actual antes y después de cada una de las sentencias DDL. En cambio, las sentencias DML no llevan implícito el commit y se pueden deshacer. Existe pues un problema al mezclar sentencias DML con DDL, ya que estas últimas pueden confirmar las primeras de manera involuntaria e implicita, lo que en ocasiones puede ser un problema.

A continuación se presenta una tabla con las sentencias SQL más comunes, clasificadas según el lenguaje al que pertenecen.

Sentencia DDL	Objetivo
Alter procedure	Recompilar un procedimiento almacenado.
Alter Table	Añadir o redefinir una columna, modificar la asignación de almacenamiento.
Analyze	Recoger estadísticas de rendimiento sobre los objetos de la BD para utilizarlas en el optimizador basado en costes.
Create Table	Crear una tabla.
Create Index	Crear un índice.
Drop Table	Eliminar una tabla.
Drop Index	Eliminar un índice.
Grant	Conceder privilegios o papeles, roles, a un usuario o a otro rol.
Truncate	Eliminar todas las filas de una tabla.
Revoke	Retirar los privilegios de un usuario o rol de la base de datos.
Sentencia DML	Objetivo
Insert	Añadir filas de datos a una tabla.
Delete	Eliminar filas de datos de una tabla.
Update	Modificar los datos de una tabla.
Select	Recuperar datos de una tabla.
Commit	Confirmar como permamentes las modificaciones realizadas.
Rollback	Deshacer todas las modificaciones realizadas desde la última confirmación.

 SQL*Plus

---

La herramienta que nos proporciona ORACLE para interactuar con la base de datos se llama SQL*Plus. Básicamente, es un intérprete SQL con algunas opciones de edición y formateo de resultados.

Antes de ver la manera de conectarse a SQL*Plus, conviene tener claros algunos conceptos:

Usuario/Clave

Para poder acceder a una base de datos gestionada por ORACLE debemos ser un usuario autorizado de la misma y conocer la palabra clave, password, asociada al usuario.

Variable de ambiente ORACLE_SID

Indica la base de datos con la que vamos a trabajar.

 Conexión

Para entrar en SQL*Plus se debe ejecutar el comando

$ sqlplus usuario/passwd

donde le indicamos al SGBD Oracle quién somos y nuestra palabra clave.

Si la configuración del SGBD Oracle se corresponde a una configuración cliente-servidor asentada sobre una red (SQL*Net v2) deberemos indicar, además, el sevicio (o base de datos) con el que queremos contactar. Esto se hace colocando el símbolo @ antes del nombre del servicio como se indica a continuación:

$ sqlplus usuario/passwd@servicio

Otra circunstancia que hay que tener en cuenta a la hora de conectarnos a SQL*Plus es el modo establecido por el DBA para la autentificación del usuario de la base de datos. La primera posibilidad es que recaiga sobre el SGBD Oracle la autentificación de los usuarios, por lo que tendremos que darle nuestro nombre de usuario y la palabra de paso. Pero existe la posibilidad de que el SGBD Oracle deje en manos del Sistema Operativo esta responsabilidad. Así, no será necesario demostrarle al SGBD Oracle quién somos ya que el SO se ha encargado previamente de comprobar que todo es correcto. En este segundo caso, el comando de conexión con SQL*Plus debe omitir el nombre de usuario y la palabra clave, pero manteniendo el resto de esta manera:

$ sqlplus /@servicio

Una vez que hemos conseguido entrar en SQL*Plus nos presenta el prompt y espera la insercción de sentencias SQL. Todas las sentencias deben acabar con un ';'. Una sentencia puede continuar en varias líneas, que SQL*Plus va numerando. Si queremos anular la sentencia actual podemos hacerlo colocando un '.' como único carácter en una línea. Si queremos volver a ejecutar la última sentencia se puede hacer con el comando '/'. Si queremos ejecutar las sentencias que almacena un fichero .sql podemos hacerlo anteponiendo el símbolo '@' al nombre del fichero.

Para cerrar la sesión vale con teclear 'exit'.  

Posibilidades de Edición

SQL*Plus almacena en un buffer la última sentencia SQL introducida. El buffer mantiene sólo una sentencia cada vez, y si se introduce una nueva sentencia se sobrescribe sobre la anterior.

La sentencia en el buffer puede ser recuperada para ejecutarla de nuevo con los comandos:

• RUN que visualiza la sentencia en el buffer antes de ejecutarla;
• / que ejecuta la sentencia sin visualizarla.

SQL*Plus también nos permite editar la sentencia SQL alamacenada en el buffer mediante un sencillo (y limitado) editor en línea, cuyos comandos se enumeran a continuación:

Comando	Abreviatura	Descripción
APPEND texto	A texto	Añade texto al final de la línea.
CHANGE/fuente/destino	C/fuente/destino	Cambia el contenido 'fuente' por el 'destino'
CHANGE/texto	C/texto	Quita 'texto' de una línea.
CLEAR BUFFER	CL BUFF	Borra el buffer
DEL	DEL	Borra una línea.
INPUT	I	Inserta una o más líneas.
INPUT texto	I texto	Inserta una línea con 'texto'.
LIST	L	Lista las líneas del buffer
LIST n	L n ó n	Lista la línea n-ésima.
LIST *	L *	Lista la línea actual.
LIST LAST	L LAST	Lista la última línea.
LIST m n	L m n	Lista las líneas desde la m-ésima a la n-ésima.

Al contenido del buffer también se puede acceder desde el editor del Sistema Operativo. Así, el buffer podrá ser manipulado con las posibilidades del editor con el que estemos acostumbrados a trabajar. Al salir del editor se devuelve el control al SQL*Plus. Para conseguir trabajar con el editor del Sistema Operativo basta con colocar la variable DEFINE_EDITOR y luego llamar al editor.

SQL> define_editor=vi

SQL> edit

 Utilización de Ficheros

SQL*Plus considera dos tipos de ficheros: de spool y de comandos.

Un fichero de spool almacena los resultados de una consulta (o varias) en un fichero con la extensión .lst (o lo manda a la impresora).

Los comandos asociados con los ficheros spool son

SPOOL fichero

Manda el resultado de las consultas al fichero.

SPOOL OUT

Manda el resultado de las consultas a la impresora.

SPOOL OFF

Cierra el fichero de spool.

EXIT

Al salir de SQL*Plus se cierran los ficheros de spool.

Los archivos de comandos almacenan comandos SQL y SQL*Plus para ser editado, almacenado y/o ejecutado; y tienen por defecto la extensión .sql :

• Para editarlo se puede utilizar el comando edit fichero.
• Para ejecutarlo se utilizará el comando START fichero o @fichero

El SQL*Plus nos proporciona más posibilidades en relación con los ficheros de comandos, la comunicación con el usuario final y la generación de informes. Pero antes de ver este tipo de cosas, es mejor que sigamos profundizando en el conocimiento del lenguaje SQL. Al final del curso se puede se encuentra un capítulo con algunas de las cosillas que quedan por contar del SQL*Plus.

---

Creación

---

La primera fase de cualquier base de datos comienza siempre con sentencias DDL, ya que antes de poder almacenar información debemos definir los objetos básicos donde agrupar la información. Los objetos básicos con que trabaja SQL son las tablas. Una tabla es un conjunto de celdas agrupadas en filas y columnas donde se almacenan elementos de información.

Antes de llevar a cabo la creación de una tabla conviene planificar:

• nombre de la tabla,
• nombre de cada columna,
• tipo y tamaño de los datos almacenados en cada columna,
• información adicional, restricciones, etc.

Hay que tener en cuenta también ciertas restricciones en la formación de los nombres de las tablas: longitud máxima de 30 caracteres, no puede haber nombres de tabla duplicados, deben comenzar con un carácter alfabético, permitir caracteres alfanuméricos y el guión bajo '_', y Oracle no distingue entre mayúsculas y minúsculas.

La sintaxis del comando que permite crear un tabla es la siguiente:

CREATE TABLE [esquema.]tabla ({columna tipoColumna [NOT NULL],}⁺,

       {CONSTRAINT nombreRestricción

          {UNIQUE ([column,]⁺)|

           DEFAULT expresion|

           CHECK (condicion)|

           PRIMARY KEY ([column,]⁺)|

           FOREIGN KEY (column) REFERENCES tabla(columna)},}^*)

Del examen de la sintaxis de la sentencia Create Table se pueden concluir que necesitamos conocer los distintos tipos de columna y las distintas restricciones que se pueden imponer al contenido de las columnas. Vayamos por partes.

4.1 Tipos de Columnas

Existen varios tipos de datos en SQL. De esta manera, cada columna puede albergar una información de naturaleza distinta. Los tipos de datos más comunes y sus características se resumen en la siguiente tabla.

Tipo de Dato	Descripción
VARCHAR2(tamaño)	Almacena datos de tipo carácter alfanumérico de longitud variable, con un tamaño máximo de 2.000.
CHAR(tamaño)	Almacena datos de tipo carácter alfanumérico de longitud fija, con un tamaño máximo de 255.
LONG	Almacena datos de tipo carácter alfanumérico de longitud variable con un tamaño máximo de hasta 2 Gb.
NUMBER(dig,dec)	Almacena datos numéricos de dig dígitos, de los cuales dec son decimales. El tamaño máximo es de 38 dígitos.
DATE	Almacena fechas desde el 1-Ene-4712 AC hasta el 31-Dic-4712 DC.
RAW(tamaño)	Almacena datos de longitud variable, con un tamaño máximo de 255 bytes.
LONG RAW	Almacena datos de longitud variable, con un tamaño máximo de 2 Gb.

Funciones

Existen en SQL muchas funciones que pueden complementar el manejo de los datos en las consultas. Se utilizan dentro de las expresiones y actuan con los valores de las columnas, variables o constantes.

Se pueden incluir en las clásulas SELECT, WHERE y ORDER BY.

Pueden anidarse funciones dentro de funciones. Y existe una gran variedad de funciones para cada tipo de datos:

• aritméticas,
• de cadenas de caracteres,
• de manejo de fechas,
• de conversión,
• otras,
• de grupo.

Funciones Aritméticas

Función	Cometido	Ejemplo	Resultado
ABS(n)	Calcula el valor absoluto de n.	select abs(-15) from dual;	15
CEIL(n)	Calcula el valor entero inmediatamente superior o igual a n.	select ceil(15.7) from dual;	16
FLOOR(n)	Calcula el valor entero inmediatamante inferior o igual a n.	select floor(15.7) from dual;	15
MOD(m,n)	Calcula el resto resultante de dividir m entre n.	select mod(11,4) from dual;	3
POWER(m,n)	Calcula la potencia n-esima de m.	select power(3,2) from dual;	9
ROUND(m,n)	Calcula el redondeo de m a n decimales. Si n<0 el redondeo se efectua a por la izquierda del punto decimal.	select round(123.456,1) from dual;	123.5
SQRT(n)	Calcula la raíz cuadrada de n.	select sqrt(4) from dual;	2
TRUNC(m,n)	Calcula m truncado a n decimales (n puede ser negativo).	select trunc(123.456,1) from dual;	123.4
SIGN(n)	Calcula el signo de n, devolviendo -1 si n<0, 0 si n=0 y 1 si n>0.	select sign(-12) from dual;	-1

Funciones de Cadenas de Caracteres

Función	Cometido	Ejemplo	Resultado
CHR(n)	Devuelve el carácter cuyo valor codificado es n.	select chr(65) from dual;	A
ASCII(cad)	Devuelve el valor ascii de cad.	select ascii('A') from dual;	65
CONCAT(cad1,cad2)	Devuelve cad1 concatenada con cad2. Esta función es esquivalente al operador ||.	select concat(concat(nombre,' es '),oficio) from emp;	Cano es Presidente, etc.
LOWER(cad)	Devuelve la cadena cad con todas sus letras convertidas a minúsculas.	select lower('MinUsCulAs') from dual;	minusculas
UPPER(cad)	Devuelve la cadena cad con todas sus letras convertidas a mayúsculas.	select upper('maYuSCulAs') from dual;	MAYUSCULAS
INITCAP(cad)	Devuelve cad con el primer caracter en mayúsculas.	select initcap('isabel') from dual;	Isabel
LPAD(cad1,n,cad2)	Devuelve cad1 con longitud n, y ajustada a la derecha, rellenando por la izquierda con cad2.	select lpad('P',5,'*') from dual;	****P
RPAD(cad1,n,cad2)	Devuelve cad1 con longitud n, y ajustada a la izquierda, rellenando por la derecha con cad2.	select rpad('P',5,'*') from dual;	P****
REPLACE(cad,ant,nue)	Devuelve cad en la que cada ocurrencia de la cadena ant ha sido sustituida por la cadena nue.	select replace('digo','i','ie') from dual;	diego
SUBSTR(cad,m,n)	Devuelve la sudcadena de cad compuesta por n caracteres a partir de la posicion m.	select substr('ABCDEFG',3,2) from dual;	CD
LENGTH(cad)	Devuelve la longitud de cad.	select length('cadena') from dual;	6

Funciones de Manejo de Fechas

Función	Cometido	Ejemplo	Resultado
SYSDATE	Devuelve la fecha y hora actuales.	select sysdate from dual;	14-MAR-97
ADD_MONTHS(d,n)	Devuelve la fecha d incrementada en n meses.	select add_months(sysdate,4) from dual;	14-JUL-97
LAST_DAY(d)	Devuelve la fecha del último día del mes de d.	select last_day(sysdate) from dual;	31-MAR-97
MONTHS_BETWEEN(d1, d2)	Devuelve la diferencia en meses entre las fechas d1 y d2.	select months_between(sysdate,'01-JAN-97') from dual;	2.43409424
NEXT_DAY(d,cad)	Devuelve la fecha del primer día de la semana cad después de la fecha d.	select next_day(sysdate, 'sunday') from dual;	16-MAR-97

Funciones de Conversión de Tipos

Función	Cometido	Ejemplo	Resultado
TO_NUMBER(cad,fmto)	Convierte la cadena cad a un número, opcionalmente de acuerdo con el formatofmto.	select to_number('12345') from dual;	124345
TO_CHAR(d, fmto)	Convierte la fecha d a una cadena de caracteres, opcionalmente de acuerdo con el formato fmto.	select to_char(sysdate) from dual;	'14-MAR-97'
TO_DATE(cad,fmto)	Convierte la cadena cad de tipo varchar2 a fecha, opcionalmente de acuerdo con el formato fmto.	select to_date('1-JAN-97') from dual;	01-JAN-97

Con las fechas pueden utilizarse varios formatos. Estos formatos permiten modificar la presentación de una fecha. En la siguiente tabla se presentan algunos formatos de fecha y el resultado que generan.

Máscaras de Formato Numéricas

Formato	Cometido	Ejemplo	Resultado
cc ó scc	Valor del siglo.	select to_char(sysdate,'cc') from dual;	20
y,yyy ó sy,yyy	Año con coma, con o sin signo.	select to_char(sysdate,'y,yyy') from dual;	1,997
yyyy ó yyy ó yy ó y	Año sin signo con cuatro, tres, dos o un dígitos.	select to_char(sysdate,'yyyy') from dual;	1997
q	Trimestre.	select to_char(sysdate,'q') from dual;	1
ww ó w	Número de la semana del año o del mes.	select to_char(sysdate,'ww') from dual;	11
mm	Número del mes.	select to_char(sysdate,'mm') from dual;	03
ddd ó dd ó d	Número del día del año, del mes o de la semana.	select to_char(sysdate,'ddd') from dual;	073
hh ó hh12 ó hh24	La hora en formato 12h. o 24h.	select to_char(sysdate,'hh') from dual;	12
mi	Los minutos de la hora.	select to_char(sysdate,'mi') from dual;	15
ss ó sssss	Los segundos dentro del minuto, o desde las 0 horas.	select to_char(sysdate,'sssss') from dual;	44159

Máscaras de Formato de Caracteres

Formato	Cometido	Ejemplo	Resultado
syear ó year	Año en Inglés	select to_char(sysdate,'syear) from dual;	nineteen ninety-seven
month o mon	Nombre del mes o su abreviatura de tres letras.	select to_char(sysdate,'month') from dual;	march
day ó dy	Nombre del día de la semana o su abreviatura de tres letras.	select to_char(sysdate,'day') from dual;	friday
a.m. ó p.m.	El espacio del día.	select to_char(sysdate,'a.m.') from dual;	p.m.
b.c. ó a.d.	Indicador del año respecto al del nacimiento de Cristo.	select to_char(sysdate,'b.c.') from dual;	a.d.

Otras Funciones

Función	Cometido	Ejemplo	Resultado
DECODE(var, val1, cod1, val2, cod2, ..., defecto)	Convierte el valor de var, de acuerdo con la codificación.	select decode(oficio, 'Presidente', 'P', 'Director', 'D', 'X') from emp;	P, D, X, ...
GREATEST(exp1, exp2, ...)	Devuelve el mayor valor de una lista.	sin ejemplo.	sin ejemplo.
LEAST(cad,fmto)	Devuelve el menor valor de una lista.	sin ejemplo.	sin ejemplo.
NVL(val, exp)	Devuelve la expresión exp si val es NULL, yval si en otro caso.	select salario+nvl(comision,0) from emp;	450000, 350000, ...