Se puede descargar un manual de la universidad de Jaén desde aqui

Es una herramienta gráfica para el desarrollo en bases de datos Oracle. Permite visualizar objetos de base de datos, ejecutar sentencias SQL, ejecutar scripts SQL, editar y depurar sentencias PL/SQL. También permite ejecutar informes ya proporcionados o los creados y salvados por el usuario.

Funciones elementales

Operativa básica

El Oracle SQL Developer es una herramienta construida en torno a un interfaz principal que permite navegar por un árbol jerárquico de objetos contenidos en bases de datos y realizar operaciones sencillas sobre ellos. Proporciona además alguna herramienta adicional, especialmente un área para ejecutar sentencias SQL y PL/SQL.

Interfaz principal

El interfaz principal del Oracle SQL Developer es sencillo: en general se utiliza la parte de la izquierda para buscar y seleccionar objetos y la parte de la derecha para mostrar información sobre dichos objetos.
 
La ventana de la izquierda tiene dos pestañas:
  Bajo estas pestañas se muestra un árbol jerárquico de objetos, clasificados en función de su tipo. Si se selecciona un nodo (por ejemplo, “Tablas”, que contiene las tablas para una conexión) los objetos tos mostrados pueden filtrarse en función de su nombre. El usuario puede definir el filtro utilizando el icono    . Para dejar de utilizar dicho filtro, se escogería la opción de “Limpiar Filtro” en el menú de contexto de dicho nodo, tal y como se muestra en la figura.
 

 
Además de dicha opción también se tendrá la posibilidad de crear un nuevo objeto del tipo del nodo seleccionado.

Si se selecciona un objeto de un tipo determinado, su menú de contexto ofrecerá operaciones específicas de ese objeto (por ejemplo, “Tabla>Borrar” para borrar una tabla, “Indice>Reconstruir” para reconstruir un índice, etc.)
 
La parte de la derecha del interfaz muestra información sobre los objetos seleccionados, organizada en diferentes pestañas. Por ejemplo, al seleccionar una tabla denominada “prueba” se podría mostrar lo siguiente:
 


Para los objetos de tipo tabla, existe la pestaña “Datos” que permite ver y modificar los datos almacenados en la misma.
 
Para la mayoría de objetos, existe una pestaña “SQL”, que muestra la sentencia SQL de creación de dicho objeto. Desde las distintas pestañas los datos se pueden exportar utilizando la opción de “Exportar Datos” del menú contextual.

SQL Worksheet (Hoja de trabajo o ejecución de comandos SQL) 

Desde “Herramientas>Hoja de Trabajo SQL” (o pulsando el icono de SQL, o pulsando Alt+F10) se puede acceder a esta herramienta del SQL Developer. Puede utilizarse para escribir y ejecutar sentencias SQL, PL/SQL y SQL*Plus.
 
El interfaz tiene dos secciones principales:
 
 
El plan de ejecución muestra un árbol con la jerarquía de las operaciones que forman la sentencia. Para cada operación, se muestra el orden de las tablas referenciadas por la sentencia, el método de acceso a cada tabla, el método de unión para las tablas que hayan sido afectadas por operaciones de tipo JOIN, y operaciones como filtrado, ordenado o agregado. Además se muestran columnas con información sobre optimización (por ejemplo el coste de cada operación), particionamiento y ejecución paralela.
 

 

Capacidades

A continuación se describen las capacidades o tareas a nivel global más importantes que permite desempeñar la aplicación. Para profundizar en alguna de ellas más allá del presente texto, nada mejor que consultar la documentación oficial de la herramienta proporcionada por oracle.

Crear conexiones

Para acceder al panel de crear conexiones debemos pulsar sobre el icono indicado en la figura, el cual se encuentra en la pestaña “Conexiones”.
 

 
Podemos crear y testear tantas conexiones a bases de datos como queramos. Destaca la capacidad que tiene para conectarse a otro tipo de bases de datos aparte de Oracle. Se permite almacenar los datos de conexión a todas las BBDD que queramos. Mediante el parámetro “Tipo de Conexión” podemos elegir introducir los parámetros de la conexión de forma manual, seleccionar una de las BBDD definidas en el fichero tnsnames.ora o bien introducir los datos con formato URL de JDBC.
 
Hay que decir que la herramienta SQL Developer necesita conectarse a una versión de Oracle 9i o superior para acceder a todo su potencial. La conexión con Oracle 8 es posible pero no todas sus opciones quedan disponibles, por lo que se desaconseja su utilización.

Modelo E/R

Se puede realizar mediante  Herramientas - Data Modeler.
Es necesario haber creado previamente las claves primarias y ajenas necesarias. Muestra una estructura de las relaciones, pero no permite la creación "en ese momento"


Explorar objetos

La herramienta SQL Developer cuenta con un explorador de objetos basado en árbol. Gracias a esto podemos acceder a todos los objetos que componen nuestra BDD de una manera fácil e intuitiva, tal como podemos observar en la siguiente imagen.
 

 
Entre los objetos a los que tenemos acceso destacan los siguientes:
   
Para facilitar la visualización de múltiples objetos han implementado una vista por pestañas, por lo que no supone un problema tener que analizar varios objetos simultáneamente.
 
Por último, otra característica reseñable es la de que los objetos del mismo tipo cuelgan juntos de un mismo nodo del árbol de visualización. Además, podemos aplicar filtros a cada nodo para restringir aún más qué es lo que queremos visualizar.

Crear objetos

A la hora de crear objetos, SQL Developer nos facilita la tarea gracias a los diálogos/formularios de creación que tiene para cada tipo de objeto. Como muestra de las capacidades de dichos diálogos a continuación se muestra el correspondiente a la creación de una nueva tabla.


 
Entre los datos soportados incluye soporte para:
   
En la creación de nuevas tablas podemos especificar secuencias y disparadores para ejecutar antes de inserción de datos para rellenar una columna con valores.

Modificar objetos

SQL Developer a la hora de modificar objetos también nos ofrece unos diálogos específicos para facilitar la tarea. Entre dichos diálogos se encuentran:
   
La mayoría de los objetos tienen un diálogo de edición general así como uno de modificaciones específicas disponible mediante el menú contextual del botón derecho del ratón.
 

 

Consultar y actualizar tablas

Otra de las funcionalidades disponible es la de consultar los datos de las tablas y actualizarlos. Los datos se nos presentan en una rejilla sobre la cual podemos realizar algunas operaciones de presentación tales como:
  Sobre la rejilla con los datos podemos hacer las clásicas operaciones de inserción, modificación y borrado de tuplas de una manera muy intuitiva gracias a la interfaz que han desarrollado.
 
Tenemos más posibilidades: tiene soporte para CLOBs y BLOBs, existe un sistema de tracking de los cambios realizados sobre los datos mediante mensajes de log y cuenta con un generador de consultas visual para formar querys. Para activarlo, abrir una sesión de SQL, pulsar el botón derecho sobre la sesión y elegir "Generador de consultas" 
 

Exportar datos, DDLs e importar datos

Otra de las características reseñables de SQL Developer es la capacidad que tiene para exportar e importar datos, así como DDLs.
 
Para hacer uso de estas funciones se emplea la función deseada en el menú contextual del objeto para el cual queramos exportar/importar. Por ejemplo, a la hora de consultar las opciones de exportación de una tabla de nuestra BD, las opciones disponibles son las siguientes:
 

 
 

 
Por el contrario, cuando queremos importar, la única opción disponible es la de importar desde una hoja de cálculo de Excel o fichero csv.

Ejercicio: Importar los datos de la base de datos usuario.mdb empleando ficheros csv

  1. Descargar de la página web de la asignatura la base de datos access usuario.mdb. Desbloquearla en sus propiedades
  2. Exportar desde access las tablas templa y tdepta con formato csv
    1. Separador de campos: ; (punto y coma)
    2. Cualificador del texto: ninguno
    3. Incluir nombres de campo en la primera fila
  3. Importar los csv anteriores desde SQL Developer (Tablas - Botón derecho) indicando 
    1. Que el separador de campos es punto y coma
    2. Que tiene cabecera (header)
    3. Dar nombre a la tabla. 
    4. Seleccionar todos los campos. 
      1. Ir campo a campo indicando el tipo de datos
        1. codtra, niveduc y salario son de tipo number).
        2. fechnac y feching los hemos puesto como varchar (problema entre tipos de representar las fechas). Debemos arreglarlas
          1. Crear 2 nuevos campos: fech_ing y fech_nac del tipo date
          2. Arreglar los campos feching y fechnac eliminando las horas con:
            1. UPDATE templa SET feching=rtrim(feching,' 0:00:00');
            2. UPDATE templa SET fechnac=rtrim(fechnac,' 0:00:00');
          3. Actualizar fech_ing con feching y fech_nac con fechnac
            1. UPDATE templa SET fech_ing=feching,fech_nac=fechnac;
          4. Borrar los campos feching y fechnac
          5. Renombrar los campos fech_ing y fech_nac a feching y fechnac
  4. Crear las claves primarias de las 2 tablas (botón derecho, restricción, agregar clave primaria)
  5. Crear las claves ajenas (botón derecho, restricción, agragar clave ajena)
  6. Realizar el modelo E/R (Herramientas - Data Modeler)
  7. Hacer un informe con la media salarial de cada departamento: gráfico de barras
    1. Informes de usuario. 
      1. SQL: SELECT ''Sueldo medio',dept,avg(sueldo) FROM templa GROUP BY dept;
      2. Estilo: Chart. 
      3. Detalles del Chart: Barras verticales y 3D

Funciones y procedimientos PL/SQL

Incrementan el potencial de SQL añadiendo sentencias como IF, FOR, WHILE, ... para usar un verdadero lenguaje de programación

Funciones - Nueva Función 



Hay que marcar el nombre de la función y el tipo de datos devuelto

Después se crea el cuerpo de la función pulsando sobre el icono de compilar (2 ruedas dentadas) para grabarla. Por ejemplo, la función FUNCION1 recibe 2 números y devuelve su producto:

CREATE OR REPLACE FUNCTION FUNCION1(a number,b number) RETURN NUMBER AS
  c number;
BEGIN
  c:=a*b;
  RETURN c;
END FUNCION1; 

Las funciones que no usan ninguna tabla, pueden probarse con la tabla inexistente dual:
SELECT funcion1(3,2) FROM dual; devolverá 6

Los procedimientos se ejecutan mediante el comando:
EXEC nombredelprocedimiento;

Ejemplos de sentencias de control en Oracle:

CREATE OR REPLACE FUNCTION F1 (nota number) RETURN varchar2 AS
    respuesta VARCHAR2(50);
BEGIN
    -- Uso de la sentencia IF
    IF nota>=0 AND nota<5 THEN
        respuesta:='Suspenso';
    ELSIF nota>=5 AND nota<6 THEN
        respuesta:='Suficiente';
    ELSIF nota>=6 AND nota<=7 THEN
        respuesta:='Bien';
    ELSIF nota>=7 AND nota<9 THEN
        respuesta:='Notable';
    ELSIF nota>=9 AND nota<=10 THEN
        respuesta:='Sobresaliente';
    ELSE
        respuesta:='Nota erronea';
    END IF;
    RETURN respuesta;
END F1;

--------------------------------------------------------------

CREATE OR REPLACE FUNCTION F1 (nota number) RETURN varchar2 AS
    respuesta VARCHAR2(50);
BEGIN
    -- Uso de la sentencia CASE
CASE
    WHEN (nota between 0 AND 4.99) THEN respuesta:='Suspenso';
    WHEN (nota between 5 AND 5.99) THEN respuesta:='Suficiente';
    WHEN (nota between 6 AND 6.99) THEN respuesta:='Bien';
    WHEN (nota between 7 AND 8.99) THEN respuesta:='Notable';
    WHEN (nota between 9 AND 10) THEN respuesta:='Sobresaliente';
    ELSE respuesta:='Nota erronea';
END CASE;
return respuesta;
END F1;

----------------------------------------------------------------------------------------------

BEGIN
    -- Uso de la sentencia LOOP
contador:=1;
LOOP
    EXIT WHEN contador=numero;
    contador:=contador+1;
END LOOP;
return 'He contado hasta '||contador;
END F1;

-----------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION F1 (numero number) RETURN varchar2 AS
  contador number;
BEGIN
    -- Uso de WHILE
contador:=1;
WHILE contador<numero LOOP
    contador:=contador+1;
END LOOP;
return 'He contado hasta '||contador;
END F1;

----------------------------------------------------------------

CREATE OR REPLACE FUNCTION F1 RETURN varchar2 AS
 contador number;
BEGIN
    -- Uso de la sentencia FOR
FOR contador IN 1..10 LOOP
    -- Aqui es donde se realizan las acciones repetidas
  DBMS_OUTPUT.PUT_LINE('Contador vale '||contador);
END LOOP;
return 'Ya he acabado';
END F1;


Ejercicio de programación en PL-SQL:

Sean 2 tablas que contienen los datos de un torneo de futbol-sala:
Creamos las siguientes funciones para saber el número de partidos ganados y empatados, junto con los puntos conseguidos por un equipo en una jornada concreta:

CREATE OR REPLACE FUNCTION jugados(j number,e number) RETURN NUMBER AS
  c number;
BEGIN
    c:=0;
    SELECT count(*) INTO c FROM system.resultados WHERE (equipo1=e OR equipo2=e) AND jornada<=j AND goles1 IS NOT NULL;
    RETURN c;
END jugados;

----------------------------------------------

CREATE OR REPLACE FUNCTION ganados(j number,e number) RETURN NUMBER AS
  c number;
BEGIN
    c:=0;
    SELECT count(*) INTO c FROM system.resultados WHERE (equipo1=e AND goles1>goles2) OR (equipo2=e AND goles2>goles1) AND jornada<=j AND goles1 IS NOT NULL;
    RETURN c;
END ganados;

----------------------------------------------

CREATE OR REPLACE FUNCTION empatados(j number,e number) RETURN NUMBER AS
  c number;
BEGIN
    c:=0;
    SELECT count(*) INTO c FROM system.resultados WHERE (equipo1=e AND goles1=goles2) OR (equipo2=e AND goles2=goles1) AND jornada<=j AND goles1 IS NOT NULL;
    RETURN c;
END empatados;

-------------------------------------------------

CREATE OR REPLACE FUNCTION puntos(j number,e number) RETURN NUMBER AS
  c number;
BEGIN
    c:=ganados(j,e)*3+empatados(j,e);
    RETURN c;
END ganados;

Cursores en Oracle

Los cursores recorren un bloque de registros dentro de una función o procedimiento. Se pueden abrir (OPEN), avanzar (FETCH), cerrar (CLOSE), pero su uso, normalmente, suele hacerse de forma que el recorrido se hace completo después de haberlo cargado con una sentencia SQL.

Por ejemplo, si se deseamos hacer un procedimiento que recorra mediante un cursor la tabla templa. Aumentará el sueldo de las empleadas un 3% y a los del departamento A00 les da un bonus de 1500 euros:

CREATE OR REPLACE PROCEDURE RECORRIENDO AS
  cursor cur is select nuempl,sexo,dept from templa;
BEGIN
  -- Recorremos el cursor pasando cada registro a la variable registro
  FOR registro IN cur LOOP
    IF registro.sexo='F' THEN
      UPDATE templa SET salario=salario*1.03 WHERE nuempl=registro.nuempl;
    END IF;
    IF registro.dept='A00' THEN
      UPDATE templa SET salario=salario+1500 WHERE nuempl=registro.nuempl; 
    END IF;
  END LOOP;
END RECORRIENDO;

Con EXEC recorriendo; se actualizaría la tabla recorriéndola registro a registro

Depuración

El SQL Developer se puede utilizar para ejecutar y hacer “debugging” de funciones y procedimientos PL/SQL. Para ejecutarlas, se puede seleccionar dicha función o procedimiento en la parte izquierda del interfaz y después escoger la opción de “Run” del menú de contexto.
 
Para realizar el “debugging”:
 

Disparadores:

Se crean de forma parecida a las funciones.

Ejemplo: Crear un disparador que cuando la tabla probando1 (codigo PK, nombre,salario) se actualice y el sueldo supere los 3000 euros deje un mensaje con los datos del intento de cambio en una tabla incidencias(texto). Además en ese caso dejará el sueldo en el límite de 3000

Disparador - Nuevo disparador
Dar los siguientes datos:

 create or replaceTRIGGER DISPARADOR1
BEFORE UPDATE ON PROBANDO1
REFERENCING OLD AS viejo NEW AS nuevo
FOR EACH ROW
WHEN (nuevo.salario>3000)
DECLARE
  mensaje varchar2(200);
BEGIN
  mensaje:='Sueldo superado: '||:viejo.nombre||:nuevo.salario||SYSDATE;
  :nuevo.salario:=3000;
  insert into incidencias values (mensaje);
END;

Ejemplo: Crear una tabla con clave primaria autoincrementada (no existe en Oracle)

a) Crear la tabla:

CREATE TABLE Fabricantes (cod_fabricante number primary key, nombre VARCHAR2(25) NOT NULL, pais VARCHAR2(25) DEFAULT ‘España’);

 b) Crear la secuencia:

CREATE SEQUENCE cfab START WITH 1 INCREMENT BY 1;

 c) Crear el disparador

CREATE TRIGGER TRIG_FAB BEFORE INSERT ON Fabricantes
FOR EACH ROW
BEGIN
    SELECT cfab.NEXTVAL INTO :NEW.cod_fabricante FROM DUAL;
END;

Informes

Una parte importante de SQL Developer es la de ejecución y creación de informes. La herramienta presenta una serie de informes predefinidos que abarcan múltiples aspectos típicos de la base de datos. Sin embargo, también existe la posibilidad de crear informes a medida.  

Informes preestablecidos:

Sobre la base de datos: contiene información sobre la base de datos conectada en ese momento, como se aprecia en la siguiente figura.
                            

 
DBA: contiene varias subcarpetas. La primera de ellas es “Database Parameters” con la opción “All Parameters” (Reports->Data Dictionary Reports->Database Administration->Database Parameters->All Parameters) que devuelve una lista con todos los parámetros de inicialización de la base de datos. 

 
 
En la carpeta “Storage” (Reports->Data Dictionary Reports->Database Administration->Storage) se pueden recuperar mucha información sobre el almacenamiento de los datos en la base de datos. Por ejemplo, a través de la opción “Free Space” (Reports->Data Dictionary Reports->Database Administration->Storage->Free Space) se puede recuperar el espacio disponible de uno o todos los tablespaces del sistema.
 

 
Otra carpeta interesante es la de “Session”. Dentro de esta carpeta se pueden crear informes para recuperar información sobre, por ejemplo, las sesiones activas en un determinado momento. Para ello, se puede hacer clic sobre la opción “The active sessions report” (Reports->Data Dictionary Reports->Database Administration->Sessions->Active Sessions) que muestra todas las conexiones activas en la base de datos. Se utilizará esta información para saber que usuario esta logado y obtener detalles sobre su sesión. 
 

 
El resto de subcarpetas que se encuentran en “Database Administration”, son muy parecidas a las explicadas anteriormente (Storage, Session, etc.), pero mostrando la información particular de cada carpeta. Así, la herramienta proporciona carpetas para hacer informes sobre cursores, tablas de la base de datos, usuarios registrados, etc. y la forma de obtener informes de cada una de ellas es muy intuitiva.
 
Una carpeta que destaca por la utilidad de la información que devuelve, es la de “Top SQL” (Reports->Data Dictionary Reports->Database Administration->Top SQL->*) que ofrece una idea general sobre qué está pasando en el sistema. Por ejemplo, se puede obtener una lista de querys por consumo de CPU, por ejecuciones, lecturas de disco, etc.

 

Informes definidos por el usuario

 

   

Migrar desde bases de datos de terceros.

Un aspecto que se ha intentado potenciar con las nuevas versiones de SQL Developer es el de migración de datos desde BBDD de terceros hacia nuestra BD Oracle. Para ello la herramienta viene equipada con las siguientes características:
 
 

 

Ejercicios:

Realizar un filtrado en una tabla

Se va ha realizar un filtrado en la tabla S73A08T00 de la aplicación s73a, esta tabla esta compuesta por los siguientes campos:
 

 
Y el filtrado se va a realizar por el campo “CATEGORY”, seleccionando únicamente aquellos registros de la tabla que tengan la categoría de “DOGS”.

Resolución Lo primero que hay que hacer una vez abierta la herramienta Oracle SQL Developer, es conectarnos con la BD S73A, seleccionamos la base de datos, e indicamos el usuario y la contraseña para conectarnos.
 

 
Una vez realizada la conexión se muestra la interfaz para ver la tabla hay desplegar la conexión “s73a”, luego los objetos tablas y por último seleccionar la tabla S73A08T00

 
Ahora pinchando en la pestaña “Data” aparece una caja de texto para introducir el filtro a la tabla. Y una vez escribimos la condición de filtrado “CATEGORY=’DOGS’” se mostrarán únicamente los registros que cumplan la condición.

 

Exportación

Se va ha realizar una exportación de los datos de la tabla S73A08T00, y vamos a dejar el resultado en un fichero denominado Datos08.sql.

Resolución Lo primero que hay que hacer una vez abierta la herramienta Oracle SQL Developer, es conectarnos con la BD S73A, seleccionamos la base de datos, e indicamos el usuario y la contraseña para conectarnos.
 

 
Una vez realizada la conexión se muestra la interfaz para realizar la exportación de la tabla primero hay que ver la tabla, hay desplegar la conexión “s73a”, luego los objetos tablas y por último seleccionar la tabla S73A08T00, pinchar con el botón derecho del ratón y seleccionar “Export -> SQL Insert”
 

 
Y la aplicación muestra la pantalla para realizar la exportación, en esta pantalla indicamos el fichero en el que se crearan los scripts, además se pueden indicar las columnas a exportar y si se quiere alguna condición de filtrado.

 
 
Para el caso que nos ocupa no es necesario, por lo que una vez seleccionado el fichero pinchamos en “Aplicar” y los scripts son volcados en el fichero de la siguiente manera.
 

 

Creación de informes personalizados 

Se va ha realizar  un ejemplo en el que se mostrará cómo crear informes personalizados a partir de consultas a tablas de la base de datos.

Resolución En este ejemplo se parte de dos tablas de la base de datos L33:
    Lo que se pretende es conseguir una gráfica que muestre los salarios de todos los médicos por cada uno de los centros. Para acotar los datos, sólo se mostrará información de los médicos de dos centros: U20 (C.S. GANGOITI) y U21 (C.S.GAZTELEKU).
 
            La query que obtiene los datos que se van a mostrar en la gráfica es la siguiente:
 

 
Con toda esta información ya se puede empezar a crear el informe en SQL Developer.
 
El primer paso que se debe dar es crear un nuevo Report pinchando con el botón derecho del ratón sobre “User Defined Reports”
 

 
 En el menú desplegable se selecciona “Add Report” y se mostrará la pantalla de creación del informe. En esta pantalla se rellena el nombre del informe, la descripción y la query en el campo SQL. En este ejemplo los valores serán los siguientes:
  Query para esta búsqueda: 
 

 
En la lista desplegable “Style” se puede seleccionar el estilo del informe (table, chart, etc.). Si se selecciona Style=Chart, aparecerá la pestaña “Chart Details”. En esta pestaña se pueden configurar algunas características del gráfico que se va a obtener.
 



 
El resultado final será el que se muestra en la siguiente figura: