ENTORNO SQL*PLUS

SQL*PLUS Entorno de ORACLE

SQL*PLUS es una herramienta de Oracle que reconoce y enva sentencias SQL al servidor Oracle para su ejecucin.

Contiene su propio lenguaje de comandos. Permite abreviatura de palabras claves de SQL*PLUS. Permite guardar y recuperar sentencias SQL en archivos.

SQL*PLUS Conexin a SQL*PLUS

Desde un entorno Windows:

Desde la lnea de comandos: sqlplus [username[/password[@database]]]

Comandos de Edicin

A[PPEND] texto C[HANGE] /antiguo /nuevo C[HANGE] /texto / CL[EAR] BUFF[ER] DEL [n] [m] I[INPUT] [texto] L[IST] [n] [m] R[UN] n [texto] 0 texto

Comandos de Ficheros

SAV[E] nombre_fichero[.ext] [REP[LACE] | APP[END]] GET nombre_fichero[.ext] STA[RT] nombre_fichero[.ext] @nombre_fichero[.ext] ED[IT] nombre_fichero[.ext] SPO[OL] [nombre_fichero[.ext] | OFF | OUT] EXIT

Variables de Sustitucin
Usar variables de sustitucin de SQL*PLUS para almacenar valores en forma temporal con &variable.

El doble ampersand && para cuando se quiere reutilizar el valor de una variable sin tener que preguntarle al usuario cada vez.

Uso de variables sustitucin

Use una variable precedida con un ampersand (&) para pedir un valor al usuario:

Si la variable no existe, SQL*PLUS le solicita al usuario un valor. SQL*PLUS no descarta la nueva variable una vez que esta es usada.

Caracteres y Fechas con Var. Sust.

Use comillas simples para fechas y cadenas de caracteres:

Tambin pueden usarse funciones como UPPER y LOWER con el ampersand:

Comando SET VERIFY

SET VERIFY { ON | OFF }

Definiendo este parmetro en ON, fuerza a SQL*PLUS a visualizar el texto de un comando antes y despus de que la variable sea remplazada con valores:

Variables de Usuario
Se predefinen variables usando uno de los dos comandos SQL*PLUS siguientes:
o o

DEFINE

Crea una variable de usuario de tipo CHAR.

ACCEPT Acepta un valor introducido por el usuario y lo almacena en una variable, adems de permitir introducir literales informativos as como definir el tipo de variable.

Comando DEFINE/UNDEFINE

Ejemplo de definicin de variable:

Ejemplo de uso de variable en SQL:

UNDEFINE elimina la definicin de una variable.

Comando ACCEPT

Crea un prompt personalizado para aceptar datos del usuario. Define explcitamente una variable de tipo NUMBER o DATE. Le oculta al usuario el dato que est introduciendo, por razones de seguridad.

Personalizacin del Entorno

Use comandos SET para controlar la sesin actual.

Verifique los valores que tiene definidos por medio del comando SHOW.

Parmetros del Entorno

ARRAY[SIZE] { 20 | n } COLSEP { _ | texto } FEED[BACK] { 6 | n | ON | OFF] HEA[DING] { OFF | ON } LIN[ESIZE] {80 | n } LONG { 80 | n } PAGES[IZE] { 24 | n } PAU[SE] { OFF | ON | text } TERM[OUT] { OFF | ON }

Comandos de Formato

COL[UMN] [opcin_de_columna] BRE[AK] [ON elemento_del_informe] TTI[TLE] [texto | OFF | ON] BTI[TLE] [texto | OFF | ON]

Comando COLUMN

Controla la visualizacin de una columna:

CLE[AR]

Limpia cualquier formato definido. Cambia la visualizacin de la columna segn un

FOR[MAT] formato modelo de formato.

HEA[DING] texto

Define la cabecera para una columna Alinea la cabecera de una columna a la izqda.,

JUS[TIFY] {alineacin} centro o derecha.

PRI[NT] / NOPRI[NT]

Muestra / Oculta la columna.

NUL[L] texto WRA[PPED]

Especifica el texto a visualizar en caso de valores nulos. Contina escribiendo en las lneas siguientes.

Formatos con COLUMN

Elemento Descripcin Ejemplo Resultado

An

Ancho de columna de n

Suprime ceros

999999

1234

Fuerza ceros

099999

01234

Signo dlar flotante

$9999

$1234

Moneda local

L9999

L1234

Posicin del punto decimal

9999.99

1234.00

Separador de miles

9,999

1,234

Comando BREAK

Suprime duplicados y secciona registros: Para suprimir duplicados: SQL> BREAK ON ename ON job
o o o

Para producir totales globales: SQL> BREAK ON report Para seccionar valores segn la ruptura: SQL> BREAK ON ename SKIP 4 ON job SKIP2

Limpiar todas las definiciones BREAK: SQL> CLEAR BREAK

o

Comandos TTITLE y BTITLE

Sintaxis de TTITLE y BTITLE:

Definir cabecera del informe: SQL> TTITLE Informe de|Salarios

Definir el pie de pgina del informe: SQL> BTITLE Confidencial

PL/SQL
Qu es PL/SQL?

Lenguaje de procesamiento procedimental. Implementado por Oracle.

Dispone de estructuras de programacin similares a las de la mayora de los lenguajes de programacin. Objetivo: Interactuar con la B.D.

Estructura Bloques Annimos

[DECLARE] Define objetos dentro del mismo bloque

PL/SQL

que

sern

utilizados

BEGIN Sentencias Ejecutables

[EXCEPTION] Qu hacer si la accin ejecutada causa error END;

Estructura de Procedimiento
PROCEDURE nombre IS Seccin Declarativa

BEGIN Seccin Ejecutable [EXCEPTION] Seccin de Excepciones END;

Estructura de Funcin
FUNCTION nombre RETURN tipo_dato IS Seccin Declarativa

BEGIN Seccin Ejecutable [EXCEPTION] Seccin de Excepciones END;

Declaracin de Variables

Reglas para nombres: Dos variables pueden tener el mismo nombre, si estn en bloques diferentes.
o o

El nombre de la variable (identificador) no debera ser el mismo que el de una columna de una tabla utilizada en el bloque.

Asignacin e Inicializacin de Variables

Asignacin:

Inicializacin:

Por defecto, todas las variables se inicializan a NULL.

PL/SQL Tipos de Variables

ESCALARES

BOOLEANAS COMPUESTAS LOB DE ENLACE (BIND)

Variables Escalares

VARCHAR2 (longitud_mxima) NUMBER [(precisin, escala)] DATE CHAR [(longitud_mxima)] LONG LONG RAW BOOLEAN BINARY_INTEGER

Atributo %TYPE

Permite declarar una variable basada en:

o o

Otras variables previamente declaradas La definicin de una columna de la base de datos

Preceder de %TYPE por:

o o

La tabla y la columna de la base de datos El nombre de la variable definida con anterioridad

Ejemplo: v_ename emp.ename%TYPE;

Variables BOOLEANAS
A una variable Boolean slo se le pueden asignar los valores: TRUE, FALSE o NULL.

Estas variables estn conectadas por los operadores lgicos AND, OR y NOT.

PL/SQL Tipos de Datos Compuestos

Tipos:
o

REGISTROS PL/SQL

TABLAS PL/SQL

Contienen componentes internos

Creacin de un Registro

Sintaxis:

Donde declaracin_campo significa:

Ejemplo de Registro PL/SQL

Declarar un registro para almacenar el nmero de empleado, nombre, trabajo y sueldo de un nuevo empleado:

Los componentes individuales del registro se referenciarn de forma cualificada; en este ejemplo:

El Atributo %ROWTYPE

Define un registro con la estructura de la tabla o vista de la B.D.

Los campos del registro toman sus nombres y tipos de datos de las columnas de la vista o tabla. Ejemplo: DECLARE registro_empleado emp%ROWTYPE;

Ventajas de %ROWTYPE
El nmero y los tipos de datos de las columnas de la tabla pueden no ser conocidos.

Simplifica la programacin al no tener que definir explcitamente los campos y tipos del registro. Es til para realizar recuperaciones de filas con la sentencia SELECT:

Tablas PL/SQL

Cuentan con dos componentes:

o o

TIPO DE DATOS DE CLAVE PRIMARIA BINARY_INTEGER COLUMNA DE TIPO DE DATOS ESCALARES O DE REGISTRO.

Aumentan dinmicamente porque no tienen restricciones. Se almacenan en memoria.

Creacin de Tablas PL/SQL

Sintaxis:

Ejemplo:

Creacin de Tablas PL/SQL

Variables LOB
Permiten almacenar bloques de datos no estructurados, como pantallas de texto, imgenes grficas, vdeo clips, y sonido, de hasta 4 Gb. de tamao.

CLOB

Character Large Object. Se utiliza para almacenar bloques grandes de datos de caracteres.

BLOB

Binary Large Object. Se utiliza para almacenar objetos binarios grandes en la B.D.

BFILE

Binary File. Se utiliza para almacenar objetos binarios grandes en archivos del sistema operativo, fuera de la B.D.

National Language Character Large Object. Se utiliza para almacenar en la NCLOB B.D. bloques grandes de datos NCHAR de un byte nico o multi-bytes de ancho fijo.

PL/SQL Variables de Enlace (Bind)

Son variables de SQL*Plus. Las pueden referenciar bloques de PL/SQL mediante el uso del ampersand Ejemplo:

(&).

Ejercicios

Basados en las tablas emp y dept del usuario Scott de la database Oracle.

Crear el modelo de datos necesario para representar la actividad de un Videoclub. Mostrar los nombres de los empleados del deptno 10, junto con los meses que llevan trabajando en la empresa. Extraer el nombre de empleado y su fecha de alta en la empresa con formato Dia ( letras) , mes ( letras ), ao, horas, minutos y segundos. Mostrar el n de empleado, job, nombre, as como la comisin. Si no tiene comisin, poner el texto Sin Comisin. La informacin debe de salir ordenada por n de empleado en descendente.

N de empleado con el salario ms alto.

Nombre de deptno y Nombre de empleado que ms tarde entr en la empresa, para cada uno de los departamentos. Incrementar un 20% el salario de todos los empleados que no son de Bostn.

Borrar los empleados cuyo nombre no comienza por A , que no son Analistas y que trabajan en New York. Nombre de empleado, suma de salario y comisin para los empleados cuyo salario es superior a la media de su departamento.

GENERALIDADES DE PL/SQL
Generalidades de PL/SQL (I)

Comentarios en PL/SQL: Anteponga a los comentarios de una sola lnea dos guiones (--) a modo de prefijo.
o o

Coloque comentarios de varias lneas entre /* y */.

Operadores de PL/SQL:

**, NOT

Exponenciacin, negacin lgica

+, -

Identidad, negacin

*, /

Multiplicacin, divisin

+, -, ||

Suma, resta, concatenacin

=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, Comparacin IN

AND

Conjuncin

OR

Inclusin

Generalidades de PL/SQL (II)

Bloques Anidados y mbito de la Variable:

Generalidades de PL/SQL (III)

Para hacer referencia a una variable de sustitucin en PL/SQL debe anteponer a su nombre dos puntos (:) a modo de prefijo:

Generalidades de PL/SQL (IV)

Directrices de Programacin para facilitar el mantenimiento del cdigo:

o o o o o

Documente el cdigo con comentarios. Desarrolle una convencin de maysculas/minsculas para el cdigo.

Desarrolle convenciones de nomenclatura para identificadores y otros objetos. Sangre el cdigo para facilitar la lectura.

Evite la ambigedad entre variables locales, parmetros formales y nombres de columnas de las tablas de la B.D.

Generalidades de PL/SQL (V)

Cuando trabaje con valores nulos puede evitar algunos de los errores ms habituales si recuerda las siguientes reglas:

Las comparaciones en las que se incluyen valores nulos siempre resultan NULL.
o o o

Si se aplica el operador lgico NOT a un valor nulo resulta NULL.

En las sentencias de control condicionales, si la condicin resulta NULL, no se ejecutarn las sentencias asociadas.

Sentencias SQL en PL/SQL

SELECT recupera exactamente UNA fila. INSERT aade una fila. UPDATE modifica una o ms filas existentes. DELETE suprime una o ms filas existentes. COMMIT hace permanentes todas las modificaciones pendientes. ROLLBACK elimina todas las modificaciones pendientes.

SAVEPOINT marca un punto intermedio en el procesamiento de las transacciones.

Sentencia SELECT

Sintaxis:

Recuerde, slo se debe de recuperar una fila. Ms de una fila provocar errores.

Ejemplo de SELECT
Recupere la suma de los salarios de todos los empleados de un departamento especfico:

Insercin de Datos

Aada nueva informacin sobre los empleados en la tabla emp:

Actualizacin de Datos

Aumente el salario de todos los empleados de la tabla emp que son Analistas:

Supresin de Datos

Suprima filas pertenecientes al departamento 10 de la tabla emp:

Control de Transacciones
COMMIT finaliza la transaccin actual realizando todos los cambios pendientes en la B.D.

ROLLBACK finaliza la transaccin actual desechando todos los cambios pendientes.

Control de Transacciones

Control Flujo de Ejecucin

Puede modificar el flujo lgico de sentencias utilizando sentencias IF condicionales y estructuras de control de bucles.

Sentencias IF condicionales:
o o o

IF-THEN IF-THEN-ELSE IF-THEN-ELSIF

Control de bucles:
o o o

Bucle bsico LOOP Bucle FOR Bucle WHILE

Sentencia IF

Sintaxis:

Flujo de IF-THEN-ELSE

Flujo de IF-THEN-ELSIF

PL/SQL Condiciones Booleanas

Bucle Bsico: LOOP

Sintaxis:

Donde condicin es una expresin o variable booleana (TRUE, FALSE o NULL).

Bucle FOR

Sintaxis:

No declare el ndice; se declara implcitamente como un BINARY_INTEGER. Fuera del bucle el ndice no est definido.

Los lmites desde..hasta deben de ser literales numricos. Pueden ser expresiones que se convierten en valores numricos.

Bucle WHILE

Sintaxis:

La condicin se evala al inicio de cada iteracin

Etiquetas y Loops Anidados

Puede anidar bucles a varios niveles. Utilice etiquetas para distinguir entre los bloques y los bucles.

Salga al bucle externo con la sentencia EXIT que hace referencia a la etiqueta. Los nombres de etiquetas deben ir antes de la palabra LOOP y entre los delimitadores << >>.

Etiquetas y Loops Anidados

Ejemplo:

Ejercicios Bloques Annimos

Basados en las tablas Emp & Dept del esquema del usuario Scott de Oracle

Actualizar los vendedores con una comisin mayor que 350$ con un incremento del 15% de su salario. Si la operacin afecta a mas de tres empleados, deshacer la transaccin, en cualquier otro caso validar la transaccin. Informar por pantalla de la operacin realizada. Insertar en la tabla TEMP 100 filas. En la primera columna se insertar un ndice secuencial (1, 2, 3...) y en la segunda columna un comentario indicando si el nmero generado es par o impar.

Actualizar el trabajo a DIRECTOR a todos aquellos empleados cuyo salario sea mayor que 2000$. Almacenar el nmero de empleados actualizados por la operacin en la tabla TEMP. Si los afectados son mas de cinco personas, borrar los empleados cuyo salario sea mayor que 3000$, insertar en la tabla TEMP el nmero de empleados borrados y validar la transaccin.

Calcular por medio de un bloque PL/SQL 9i annimo el total de ganancias de los empleados (salario y comisin) y cuntos de stos tienen un salario superior a 2000$ para todos los departamentos de la empresa.

CURSORES
Definicin de Cursor

tiles para las consultas que devuelven ms de una fila.

Son declarados y nombrados por el programador, y manipulados por medio de sentencias especficas en las acciones ejecutables del bloque. Control de Cursores

1. Crear un rea SQL especfica 2. Identificar el juego activo

DECLARE

OPEN FETCH

3. Cargar la fila actual en variables

4. Si todava existen filas sin leer, volver a 3. 5. Si no existen ms filas a leer CLOSE

Declaracin del Cursor

Sintaxis:

No incluya la clusula INTO en la declaracin del cursor.

Si es necesario procesar filas en algn orden, incluya la clusula ORDER BY. Ejemplo de Declaracin

Apertura del Cursor

Sintaxis:

Si la consulta no devuelve ninguna fila, no se producir ninguna excepcin al abrir el cursor.

Para los cursores declarados con la clusula FOR UPDATE, la sentencia OPEN bloquea estas filas. Recuperacin de Datos

Sintaxis:

Incluya, en el FETCH, el mismo nmero de variables que las definidas en el cursor, y en el mismo orden.

Compruebe si el cursor tiene filas.

Cierre del Cursor

Sintaxis:

Cierre el cursor una vez completado el procesamiento de las filas. Si es necesario, vuelva a abrir el cursor.

No intente recuperar los datos de un cursor una vez que ha sido cerrado (INVALID_CURSOR). Atributos de Cursores

Proporcionan informacin de estado del cursor:

El Atributo %ISOPEN Utilice el atributo de cursor %ISOPEN antes de ejecutar una recuperacin para comprobar si el cursor est abierto.

Ejemplo:

Atributos %NOTFOUND, %ROWCOUNT

Utilice %ROWCOUNT para recuperar un nmero exacto de filas.

Utilice %NOTFOUND para determinar cundo salir del bucle de lectura del cursor. Antes de la primera recuperacin, %NOTFOUND es NULL, as que si FETCH no se ejecuta nunca satisfactoriamente, no se saldra nunca del bucle de lectura. Ejemplo %NOTFOUND, %ROWCOUNT

Ejemplo:

Bucles FOR de Cursor

Sintaxis:

Apertura, recuperacin y cierre implcitos. No declare el registro, est declarado implcitamente.

Cursores con Parmetros

Sintaxis:

Permite abrir un cursor varias veces con un juego activo distinto cada vez.

Cursores con Parmetros Cada parmetro formal de la declaracin del cursor debe tener un parmetro real correspondiente en la sentencia OPEN.

La sintaxis de los parmetros es:

Cursores con Parmetros Transfiera el nmero de departamento y el cargo a la clusula WHERE:

Clusula FOR UPDATE

Sintaxis:

El bloqueo explcito le permite denegar el acceso mientras dura una transaccin.

Bloquee las filas antes de la actualizacin o supresin.

La clusula FOR UPDATE es la ltima clusula de una sentencia SELECT, incluso despus del ORDER BY.

NOWAIT devuelve un error de Oracle si las filas han sido bloqueadas por otra sesin, de lo contrario se espera.

Clusula WHERE CURRENT OF

Sintaxis:

Incluya la clusula FOR UPDATE en la definicin del cursor para bloquear las filas.

Especifique WHERE CURRENT OF en la sentencia UPDATE o DELETE para referirse a la fila actual del cursor. Ejercicios Cursores Incrementar la comisin, en funcin del salario, de los empleados de Bostn y Nueva York segn su antigedad y cargo segn la siguiente tabla:

EXCEPCIONES
Concepto de Excepcin
Qu es una excepcin? Es un identificador de PL/SQL que surge durante la ejecucin.

Cmo surge? Se produce por un error Oracle o bien puede ser provocada explcitamente. Cmo se gestiona? Interrumpindola con un manejador de excepciones o

propagndola al entorno de llamadas.

Interrupcin de Excepciones

Sintaxis:

Excepciones de Oracle8

Excepciones de Oracle8

Excepciones de Usuario

Se declaran en la seccin declarativa DECLARE.

Se provocan explcitamente en la seccin ejecutable utilizando la sentencia RAISE. Se gestiona la excepcin dentro del bloque de excepciones EXCEPTION.

Excepciones de Usuario

Ejemplo:

Funciones para Identificar Excepciones

SQLCODE Devuelve el valor numrico del cdigo de error SQL. No se puede referenciar directamente, hay que asignarlo a una variable PL/SQL de tipo NUMBER.

SQLERRM VARCHAR2.

Devuelve el mensaje asociado con el nmero de error. Tipo

RAISE_APPLICATION_ERROR

Sintaxis:

Utilice el procedimiento RAISE_APPLICATION_ERROR para comunicar de forma interactiva una excepcin predefinida, devolviendo un cdigo y un mensaje de error no estndar.

RAISE_APPLICATION_ERROR

Se utiliza en dos lugares distintos:

o o

SECCIN EJECUTABLE SECCIN DE EXCEPCIONES

Ejemplos:

Ejercicios Excepciones
Para un nmero de empleado dado (por medio de comandos SQL*Plus), incrementarle el salario en un 10%. Controlar dos excepciones predefinidas de ORACLE, NO_DATA_FOUND y TOO_MANY_ROWS.

Por medio de comandos SQL*Plus, aceptar tres valores que correspondan al nmero, nombre y localidad de un departamento. Realizar un bloque PL/SQL 9i que inserte en la tabla DEPT los valores aceptados. Controlar los siguientes errores:
o o

Si el departamento ya existe, insertar en la tabla TEMP un error.

Si algn dato de los insertados es de mayor longitud que la especificada en la tabla, insertar un error en la tabla TEMP.

Si se producen otros errores, insertar en la tabla TEMP el nmero y el mensaje del error producido. Nota: El error ORACLE de longitud del dato fuera del rango es el 1438
o

FUNCIONES PL/SQL
Definicin de Funcin

Una funcin es un bloque nombrado PL/SQL que devuelve un valor.

Una funcin puede estar almacenada en la B.D., como objeto de la B.D., para repetidas ejecuciones. Una funcin puede ser llamada como parte de una expresin.

Sintaxis Creacin Funciones

Creacin de una Funcin

Desde dnde llamarlas

Como columna de un SELECT. Condiciones en clusulas WHERE y HAVING. Clusulas ORDER BY y GROUP BY. Clusula VALUES de un comando INSERT. Clusula SET de un comando UPDATE.

Restricciones en Funciones

No se permiten comandos INSERT, UPDATE o DELETE.

La funcin no puede llamar a otro subprograma que rompa una de las restricciones arriba indicadas. Borrado de Funciones Para quitar una funcin de la parte del servidor, utilizando SQL*Plus, ejecutar el comando DROP FUNCTION.

Sintaxis:

Ejercicios Funciones Crear la funcin ALTA_DEPT con tres argumentos: nmero, nombre y localidad del departamento. Este procedimiento debe comprobar que los nmeros de departamento son mltiplos de 10, si no es as dara un error.

Crear la funcin BAJA_DEPT con un argumento. Dado un nmero de departamento, borrarlo.

Crear la funcin MOD_DEPT con dos argumentos. Dado un nmero de departamento, actualizar la localidad al destino indicado en el parmetro. Informar con un booleano si se ha realizado correctamente la operacin.