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.
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.
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, ... |
