NOTAS DE

PL/SQL
 Universitat de Lleida

 Àrea de Sistemes d’Informació i Comunicacions

VERSION DATE: 15-NOVEMBER-2000
PL/SQL INDEX

Contenidos

PL/SQL I 1

1. definición y características de pl/sql 5

2. el bloque pl/sql 7
ESTRUCTURA: 7
zona de declaración: DE VARIABLES Y CONSTANTES 8
ZONA DE PROCESO 11
zona de excepciones 12

3. Funciones sql predefinidas 15

Funciones de carácter que devuelven valores de carácter 15
Funciones de carácter que devuelven valores numéricos 17
Funciones numéricas 18
Funciones de fecha 20
Funciones de conversión 22
Funciones de grupo 26
Otras Funciones 27

4. sentencias de control 31
Sentencia if 31
Bucle infinito: 32
Bucles condicionales: 33
Bucles numéricos: 34

5. sentencias de dml 36

6. cursores 40
manejo de cursores 41
BUCLES SOBRE cursores 41
ejemplo de cursores 42

7. REGISTROS Y TABLAS 47
registros pl/sql 47
TABLAS en pl/sql, Arrays 49

8. sentencias transaccionales 56

VERSIÓN 1 i
INDEX PL/SQL

9. control de errores 59
Tipos de excepciones: 59
Ejecución de excepciones 60
funciones 61
Ejemplos y tratamiento 61

RESUMEN 66

EVALUACIÓN 68

PL/SQL II 70

12. procedimientos y funciones 74

procedimientos 75
funciones 80
SUBPROGRAMAS LOCALES. 83
CONTROL DE PROCEDIMIENTOS Y FUNCIONES. 85

13. DESARROLLO Y UTILIZACIÓN DE PAQUETES 89

paquetes (packages) 89
funciones dentro de paquetes. 94

14. DISPARADORES EN BASE DE DATOS 99

DISPARADORES (TRIGGERS) 99
CREACIÓN DE DISPARADORES 101
concepto de tabla mutante 103

15. PL/SQL DINÁMICO 108

concepto 108
Métodos de implementación 110

ejercicio de desarrollo 118

RESUMEN 119

EVALUACIÓN 121

ANEXOS A CURSO PL/SQL 123

ANEXO 1: SQL*PLUS 124

1 Conexión 124

ii VERSIÓN 1
PL/SQL INDEX

2 Posibilidades de Edición 125

3 Utilización de Ficheros 127
4 Almacenamiento de ficheros de comandos 127
5 Ficheros de Comandos 129
6 Generación de Informes 132

ANEXO 2: SQL DE ORACLE 135

19. DQL. Data Query Language. 135

Sintaxis general de sentencia SELECT. 135
Lista de campos a recuperar (SELECT) 137
Clausula FROM 138
Selección de fila (WHERE) 139
Ordenación de resultados de una consulta (ORDER BY) 142
Manipulación de datos de conjuntos (GROUP BY, HAVING) 143
RecupEración jerárquica (CONNECT BY ... START WITH ...) 145
Expresiones sentencias SELECT (UNION, INTERSECT, MINUS) 146
Consultas Multitabla (CompOsiciones-Combinaciones) 149
Subconsultas. 151
Resumen final de la orden SELECT 156

20. DML: Data ManIPULATION Language. 158

Adicción de datos a la Base de Datos (INSERT) 158
Borrado de datos de la Base de Datos (DELETE). 161
Modificación de datos en la Base de Datos (UPDATE) 162

21. DDL. Data Definition Language 165

Creación de una tabla (CREATE TABLE) 165
Eliminación de una tabla (DROP TABLE) 169
Modificación de la definición de tabla (ALTER TABLE) 170
creación de Vistas (CREATE VIEW) 173
Supresión de vistas (DROP VIEW) 179
Creación de Sinónimos (CREATE SYNONYM) 180
Supresión de Sinónimos (DROP SYNONYM) 180
Cambios de nombre (RENAME) 181
Creación de índices (CREATE INDEX) 181
Eliminación de índices (DROP INDEX) 182

22. DCL. Data Control Language. 184

Seguridad del Sistema. 184

VERSIÓN 1 iii
INDEX PL/SQL

Acceso a los objetos. 185

ANEXO 3: TABLAS DEL DICCIONARIO. 187

tablas del diccionario. 187

ANEXO 4: OPERADORES 190

Operadores 190
Operadores lógicos 191
Patrón de consulta 193

iv VERSIÓN 1
 PL/SQL

PL/SQL I

VERSIÓN 1 1
PL/SQL

INTRODUCCIÓN

ORACLE es un sistema de base de datos relacional potente y flexible.

El lenguaje de programación PL/SQL está diseñado para manipular los

datos desde distintos entornos.

PL/SQL está integrado con el servidor de bases de datos y está en

algunas herramientas de cliente ORACLE, (Developer).

Su conocimiento es imprescindible para la manipulación de información

almacenada en bases de datos ORACLE y para el desarrollo de
cualquier aplicación en ese entorno.

En esta unidad didáctica se definen las principales características de

este lenguaje y su sintaxis, se enumeran las principales funciones
predefinidas.

Así mismo se estudian las órdenes SQL disponibles: de control, de

manipulación de datos y transaccionales.

Posteriormente se introduce el concepto de cursor y por último se explica

cómo se diseña el tratamiento de errores.

2 VERSIÓN 1
 PL/SQL

CONTENIDO

1. DEFINICIÓN Y CARACTERÍSTICAS DE PL/SQL

2. EL BLOQUE PL/SQL
ZONA DE DECLARACIÓN: DECLARACIÓN DE VARIABLES Y
CONSTANTES
ZONA DE PROCESO
ZONA DE EXCEPCIONES

3. FUNCIONES SQL PREDEFINIDAS

FUNCIONES DE CARÁCTER QUE DEVUELVEN VALORES
DE CARÁCTER
FUNCIONES DE CARÁCTER QUE DEVUELVEN VALORES
NUMÉRICOS
FUNCIONES NUMÉRICAS
FUNCIONES DE FECHA
FUNCIONES DE CONVERSIÓN
FUNCIONES DE GRUPO
OTRAS FUNCIONES

4. SENTENCIAS DE CONTROL

5. SENTENCIAS DE DML

6 CURSORES
MANEJO DE CURSORES
BUCLES SOBRE CURSORES
EJEMPLO DE CURSORES

7. REGISTROS Y TABLAS
REGISTROS PL/SQL
TABLAS EN PL/SQL

8. SENTENCIAS TRANSACCIONALES

9 CONTROL DE ERRORES

10 RESUMEN

VERSIÓN 1 3
PL/SQL

OBJETIVOS

El alumno tras el estudio de esta unidad didáctica podrá:

! Conocer la sintaxis y los elementos que componen el lenguaje

PL/SQL.

! Consultar y manipular la información almacenada en servidores de

Bases de Datos Oracle.

! Desarrollar estructuras básicas de programación en PL/SQL.

EXPECTATIVAS

• ··························································································································

························································································································

• ··························································································································

························································································································

• ··························································································································

························································································································

4 VERSIÓN 1
 PL/SQL

1. DEFINICIÓN Y CARACTERÍSTICAS DE PL/SQL

IMPORTANTE

♦ PL/SQL, (Procedural Language / SQL), es un lenguaje de programación

procedural estructurado en bloques que amplia la funcionalidad del lenguaje
standard SQL a través de estructuras como:

• Variables y tipos, (predefinidos y definidos por el usuario)

• Estructuras de control, (bucles y condiciones)

• Procedimientos y funciones

• Tipos de objetos y métodos, (a partir de versión 8)

Combina la flexibilidad de SQL con la potencia y configurabilidad de un 3GL al integrar

tanto las necesarias estructuras procedimentales como el acceso a bases de datos.

Nos permite utilizar técnicas procedurales como bucles, control de flujo, tratamiento de
variables y control de errores en el procesamiento de las filas devueltas en una
SELECT.

Permite tener código ejecutable almacenado en el servidor de base de datos.

NOTA

DQL. Data Query Language (select)

DML Data manipulation language (insert)

DDL Data definition language (create table)

VERSIÓN 1 5
PL/SQL

NOTA

La versión objeto de estudio será la 2.3.

Es un lenguaje estructurado en bloques: Bloque es la unidad básica en PL/SQL

Incorpora sentencias DML (INSERT, UPDATE, DELETE)

Maneja diversas estructuras de datos (registros, arrays)

Dispone de:

• Procedimientos y funciones (igual que en C por ejemplo).

• Paquetes (Grupos de identificadores, variables y funciones y/o procedimientos

agrupados en una misma construcción, cuya finalidad es el encapsulamiento,
la abstracción y el control de acceso).

• Disparadores (Eventos que se ejecutan cuando ocurre una operación de

manipulación de datos sobre una tabla)

NOTA

También se pueden incorporar órdenes DDL mediante SQL Dinámico.

6 VERSIÓN 1
 PL/SQL

2. EL BLOQUE PL/SQL

IMPORTANTE

♦ El Bloque PL/SQL es la unidad básica en todo programa PL/SQL.

♦ Un programa PL/SQL será un conjunto de bloques PL/SQL, situados de

manera secuencial o anidados

Todos los bloques tienen tres secciones diferenciadas:

• Sección declarativa.

• Sección ejecutable, (la única obligatoria).

• Sección de excepciones.

ESTRUCTURA:

[DECLARE]

Declaración de variables y constantes

Variable

Constante Se inicializa con un valor que no se puede modificar.

Cursor Area de trabajo que contiene los datos de la fila de la tabla en

uso. El cursor es el resultado de una sentencia SELECT.

VERSIÓN 1 7
PL/SQL

ExcepciónVariables para control de errores.

BEGIN

Sentencias ejecutables y sentencias DML

[EXCEPTION]

Control de excepciones

Es el punto al que se transfiere el control del programa siempre que exista un

problema. Los indicadores de excepción pueden ser definidos por el usuario o por el
sistema, como es por ejemplo la excepción ZERO_DIVIDE. Las excepciones se
activan automáticamente al ocurrir un error, existiendo la definición de la excepción
OTHERS que considera aquellos errores no definidos y que siempre se ubica al final
de todas las excepciones.

END; [Nombre del bloque]

Fin del Bloque.

NOTA

Este tipo de bloque se le suele llamar anónimo, y es el más utilizado en Scripts.

ZONA DE DECLARACIÓN: DE VARIABLES Y CONSTANTES

Las variables son utilizadas para guardar valores devueltos por una consulta o
almacenar cálculos intermedios.

Las constantes son campos que se definen y no alteran su valor en el proceso.

Nombre_campo [CONSTANT] Tipo de dato := valor_constante / expresión_pl_sql

8 VERSIÓN 1
 PL/SQL

Otra manera de declarar las variables: identificador%TYPE.

Donde el identificador es un objeto accesible por el usuario. Normalmente una

columna de una tabla:

Var1 number(59);

Var2 Var1%TYPE;

Var3 tabla.identificador%TYPE;

Se puede también declarar una fila variable cuyos campos tienen el mismo nombre y
tipo que las columnas de una tabla o fila recuperada de un cursor:

registro tabla%ROWTYPE

Es obligatorio sólo en bloques PL/SQL anónimos.

TIPOS DE DATOS.

VERSIÓN 1 9
PL/SQL

Number (n [, d]) – numérico n <= 38

Binary_Integer – entero, desde –2147483647 a +2147483647

PLS_Integer – entero, desde –2147483647 a +2147483647

Char – de longitud fija, hasta 255 caracteres

Varchar – de tipo carácter (obsoleto)

Varchar2 (n) – Tamaño variable hasta 2000 caracteres.

Date - Fechas

Boolean – TRUE o FALSE (no puede utilizarse en campos SQL)

Long- Cadena de caracteres de longitud variable hasta 2 Gigabytes

Raw- Datos binarios de longitud variable, hasta 255 caracteres (gráficos)

Longraw- Cadena binaria de longitud variable de hasta 2 Gigabytes (gráficos)

NOTA

El tipo PLS_Integer se suele utilizar para contadores. Es idéntico al

Binary_integer pero añade la característica de dar un mensaje de error en caso
de error.

El tipo Binary_Integer se suele utilizar para tablas de PL/SQL

10 VERSIÓN 1
 PL/SQL

ZONA DE PROCESO

Empieza con BEGIN.

En esta zona es donde se escriben todas las sentencias ejecutables: sentencias DML,
asignaciones, manejo de cursores, bucles, etc.

Es importante recordar que todas las SELECT ejecutadas dentro de un bloque PL/SQL
llevan asociado un INTO.

Una SELECT que no devuelva datos o que devuelva más de uno provoca un ERROR,
saltando la ejecución directamente al EXCEPTION.

ASIGNACIÓN DE VALORES

Las dos formas que existen para asignar valores a variables de memoria, son:

- Con el operador de asignación :=, como cuando calculamos el promedio de las

ventas asignándole valor a la variable xprom con la siguiente sentencia:

xprom:=xtotal/xcant;

- Con la sentencia SELECT que contiene la orden INTO, como se muestra, es la

asignación de valores a las variables xtotal y xcant con el siguiente código:

SELECT SUM(valor),

COUNT(valor)

INTO xtotal,xcant

FROM ventas

WHERE fecha=sysdate;

FIN DE BLOQUE PL/SQL

Todo bloque PL/SQL termina con END.

VERSIÓN 1 11
PL/SQL

COMENTARIOS

Hay dos tipos de comentarios:

Con el signo -- se indica comentario de una sola línea.

Mediante /* al principio del comentario y */ al final de éste.

ZONA DE EXCEPCIONES

Las excepciones saltan cuando se ha producido un error en la ejecución.

Es sección no obligatoria. Esta sección termina con un END, que finaliza el bloque
PL/SQL.

Es la parte del bloque PL/SQL donde se realiza el control de errores.

Manejo directo de excepciones ya tratadas con el uso de un WHEN:

Excepciones que pueden incluirse directamente después del WHEN:

Excepción Número de error

CURSOR_ALREADY_OPEN ORA–06511 –6511

DUP_VAL_ON_INDEX ORA–00001 –1

INVALID_CURSOR ORA–01001 –1001

INVALID_NUMBER ORA–01722 –1722

LOGIN_DENIED ORA–01017 –1017

NO_DATA–FOUND ORA–01403 +100

NOT_LOGGED_ON ORA–01012 –1012

PROGRAM_ERROR ORA–06501 –6501

12 VERSIÓN 1
 PL/SQL

ROWTYPE_MISMATCH ORA–06504 –6504

STORAGE_ERROR ORA–06500 –6500

TIMEOUT_ON_RESOURCE ORA–00051 –51

TOO_MANY_ROWS ORA–01422 –1422

VALUE_ERROR ORA–06502 –6502

ZERO_DIVIDE ORA–01476 –1476

EJERCICIO

EJERCICIO Nº 1

Crear un bloque PL/SQL con las siguientes características:

• Definir una constante de tipo numérico, de nombre x, a la que le vamos a

asignar el valor de 12.

• Definir una variable, y, del mismo tipo que el campo hiredate, de la tabla
EMP, de la base de datos DEMO.

• Definir una variable de tipo numérico que se llame z.

• Asignar a la variable y la fecha actual. Asignar a la variable z el resultado

de multiplicar x por 10.

• Mostrar por pantalla el contenido de las variables y, z.

VERSIÓN 1 13
 PL/SQL

EJERCICIO

EJERCICIO Nº 2

Se trata de hacer una consulta a la tabla EMP, concretamente del campo

ename de tal forma que el usuario introduzca un nombre por teclado y el bloque
PL/SQL sea capaz de recuperar el campo job de dicho empleado a través de
dicha consulta.

• Pedir que se introduzca por teclado un nombre, el cual lo

almacenaremos en la variable nombre.

• Una vez introducido el nombre, lanzar un bloque PL/SQL (previamente

creado) de tal forma que sea capaz de seleccionar la fila de la tabla EMP
cuyo ename sea igual al introducido por teclado y mostrar el campo job
correspondiente a ese empleado por pantalla.

14 VERSIÓN 1
 PL/SQL

3. FUNCIONES SQL PREDEFINIDAS

IMPORTANTE

♦ Existen diversas funciones predefinidas a las que se puede llamar desde

una orden SQL.

FUNCIONES DE CARÁCTER QUE DEVUELVEN VALORES DE

CARÁCTER

♦ CHR Chr(x)

Devuelve el carácter dado su número ASCII .

♦ CONCAT Concat (cadena1, cadena2)

Devuelve las dos cadenas encadenadas (Es idéntico a usar ||).

♦ INITCAP Initcap (cadena)

Devuelve la misma cadena, con el primer carácter de cada palabra en

mayúscula y el resto en minúscula.

♦ LOWER Lower (cadena)

Devuelve la misma cadena con todos los caracteres en minúscula.

♦ LPAD Lpad (cadena1,x [,cadena2])

VERSIÓN 1 15
PL/SQL

Devuelve la cadena1 rellena hacia la izquierda con los caracteres de la

cadena2 hasta completar la longitud x

♦ LTRIM Ltrim(cadena1, cadena2)

Devuelve la cadena1, quitando los caracteres de la izquierda contenidos

en cadena2.

♦ REPLACE Replace (cadena, cad_busc [,cad_subst])

Devuelve cadena, reemplazando todas las apariciones de la cadena

cad_busc con la cadena cad_subst. Si no se especifica cad_subst, se
elimina todas las apariciones de cad_busc.

♦ RPAD Rpad (cadena1, x [,cadena2])

Devuelve la cadena1 rellena por la derecha con los caracteres de la

cadena2 hasta completar la longitud x

♦ RTRIM Ltrim(cadena1, cadena2)

Devuelve la cadena1, quitando los caracteres de la derecha contenidos

en cadena2.

♦ SUBSTR Substr (cadena, a [,b])

Devuelve una porción, de b caracteres de longitud, de la cadena

proporcionada como argumento, empezando a partir de la posición a. Sí
a es cero, se trata como si fuera 1. Si a es positivo, se devuelven los
caracteres contando a partir de la izquierda. Si a fuera negativo, se
contaría desde el final de la cadena.

♦ UPPER Upper (cadena)

Devuelve la cadena proporcionada con todas las letras en mayúsculas.

16 VERSIÓN 1
 PL/SQL

SQL> SELECT LPAD (‘Short String’, 20, ‘XY’) Cadena FROM dual;

> XYXYXYXYShort String

SQL> SELECT LTRIM (‘xxxFin de la cadena’, ’x’) Cadena FROM dual;

> Fin de la cadena

SQL> SELECT REPLACE (‘This and That’, ’Th’, ’B’) Cadena FROM dual;

> Bit and Bat

SQL> SELECT SUBSTR (‘abc123def’, 5, 4) Cadena FROM dual;

> 23de

FUNCIONES DE CARÁCTER QUE DEVUELVEN VALORES

NUMÉRICOS

♦ ASCII ASCII (cadena)

Devuelve la representación decimal del primer byte de la cadena.

♦ INSTR INSTR (cadena1, cadena2 [,a[,b]])

Devuelve la posición de la cadena1 donde está contenida la cadena2.

(La cadena se explora desde la izquierda, empezando a partir de la

posición a. Sí a es negativo, entonces la cadena1 se explora desde la
derecha. El valor devuelto es el de la aparición número b de la cadena
de la búsqueda).

VERSIÓN 1 17
PL/SQL

♦ LENGTH Length (cadena)

Devuelve la longitud de la cadena.

SQL> SELECT INSTR(‘Scott’’s spot’, ‘ot’, 1, 2) Cadena FROM dual;

> 11

FUNCIONES NUMÉRICAS

♦ ABS ABS (x)

Devuelve el valor absoluto de x

♦ ACOS ACOS (x)

Devuelve el arco coseno de x

♦ ASIN ASIN (x)

Devuelve el arco seno de x

♦ ATAN ATAN (x)

Devuelve el arco tangente de x

♦ CEIL CEIL (x)

Devuelve el menor entero que sea mayor o igual que x

♦ COS COS (x)

18 VERSIÓN 1
 PL/SQL

Devuelve el coseno de x (en radianes)

♦ COSH COSH (x)

Devuelve el coseno hiperbólico de x

♦ EXP EXP (x)

Devuelve e elevado a la potencia x

♦ FLOOR FLOOR (x)

Devuelve el mayor entero que sea menor o igual que x

♦ LN LN (x)

Devuelve el logaritmo natural de x

♦ LOG LOG (x,y)

Devuelve el logaritmo de y en base x

♦ MOD MOD (x)

Devuelve el resto resultante de dividir z por y

♦ POWER POWER (x,y)

Devuelve el número x elevado a la potencia de y

♦ ROUND ROUND (x [,y])

Devuelve x redondeado a y posiciones a la derecha del punto decimal.

♦ SIN SIN (x)

Devuelve el seno de x (en radianes)

♦ SINH SINH (x)

Devuelve el seno hiperbólico de x

VERSIÓN 1 19
PL/SQL

♦ SQRT SQRT (x)

Devuelve la raiz cuadrada de X

♦ TAN TAN (x)

Devuelve la tangente de x (en radianes)

♦ TANH TANH (x)

Devuelve la tangente hiperbólica de x (en radianes)

♦ TRUNC TRUNC (x)

Devuelve el valor x truncado a y posiciones decimales

SQL> SELECT TRUNC(15.79,1) Truncate FROM DUAL ;

> 15.7

SQL> SELECT MIN(hiredate) Minimum Date FROM emp ;

> 17-DEC-80

FUNCIONES DE FECHA

♦ ADD_MONTHS ADD_MONTHS (d,x)

Devuelve la fecha d más x meses

♦ LAST_DAY LAST_DAY (d)

20 VERSIÓN 1
 PL/SQL

Devuelve la fecha del último día del mes al que d pertenece

♦ MONTHS_BETWEEN MONTHS_BETWEEN (fecha1, fecha2)

Devuelve el número de meses existentes entre fecha1 y fecha2

♦ NEXT_DAY NEXT_DAY(d, cadena)

Devuelve la fecha del día nombrado en cadena que es posterior a la

fecha d

♦ ROUND ROUND (d [, formato])

Redondea la fecha d a la unidad especificada.

♦ SYSDATE SYSDATE

Devuelve la fecha y hora actuales, de tipo DATE

♦ TRUNC TRUNC (d [, formato])

Devuelve la fecha d, truncada a la unidad especificada.

SQL> SELECT TO_CHAR(ADD_MONTHS(hiredate,1),

'DD-MON-YYYY') "Next month" FROM emp

WHERE ename = 'SMITH' ;

> 17-JAN-1981

SQL> SELECT TO_CHAR(

ADD_MONTHS(LAST_DAY(hiredate),5),

'DD-MON-YYYY') "Five months"

FROM emp

WHERE ename = 'MARTIN' ;

> 28-FEB-1982

VERSIÓN 1 21
PL/SQL

FUNCIONES DE CONVERSIÓN

CONVERSIONES IMPLÍCITAS

PL/SQL Convierte automáticamente entre los siguientes tipos de datos:

CONVERSIONES EXPLÍCITAS

Estas son las conversiones entre tipos que pueden hacerse:

♦ TO_CHAR TO_CHAR (d [,formato [, paramls]])

Convierte la fecha d a una cadena de caracteres

♦ TO_DATE TO_DATE (d [,formato [, paramls]])

Convierte la cadena de caracteres a una fecha

22 VERSIÓN 1
 PL/SQL

♦ TO_NUMBER TO_NUMBER (d [,formato [, paramls]])

Convierte la cadena a un valor numérico.

SQL> SELECT TO_DATE(

'January 15, 1989, 11:00 A.M.',

'Month dd, YYYY, HH:MI A.M.',

'NLS_DATE_LANGUAGE = American')

FROM DUAL;

> 15/01/1989,11:00:00

SQL> SELECT TO_CHAR(HIREDATE, 'Month DD, YYYY')

"New date format"

FROM emp

WHERE ename = 'SMITH' ;

> December 17, 1980

SQL> SELECT TO_NUMBER('-AusDollars10000','L9G99999D99',

' NLS_NUMERIC_CHARACTERS = '',.''

NLS_CURRENCY = ''AusDollars'' ') "Amount"

FROM DUAL;

> -10000

VERSIÓN 1 23
PL/SQL

FORMATOS NUMÉRICOS

Element Example Description

9 9999 significant digit

0 0999 leading zero instead of a blank

$ $999 leading dollar sign

B B999 leading blank instead of a zero

MI 999MI trailing minus sign

S S999 leading plus or minus sign

PR 999PR angle brackets for negative values

D 99D99 decimal character

G 9G99 group separator

C C999 ISO currency symbol

L L999 local currency symbol

, 9,999 comma

. 99.99 period

V 999V99 10n multiplier; n is the number of 9's after the V

EEEE 9.99EEEE scientific notation (you must have 4 E's)

RN, rn RN Upper or lower case roman numeral

24 VERSIÓN 1
 PL/SQL

FORMATOS DE FECHA:

CC, SCC Century (S prefixes BC dates with a minus sign)

YYYY, SYYYY Year (S prefixes BC dates with a minus sign)

IYYY Year based on ISO standard

YYY, YY, Y Last three, two or one digits of the year

IYY, IY, I Last three, two or one digits of the ISO year

Y,YYY (Four Y's with comma) put a comma in the year (1,995)

YEAR, SYEAR Year spelled out (S prefixes BC dates with a minus sign)

RR Last two digits of year in another century (allows for year 2000)

BC, AD BC or AD indicator

B.C., A.D. BC or AD indicators with periods

Q Numeric quarter of the year (1-4 with Jan-Mar=1)

MM 2 number month (Jan = 01)

RM Roman numeral month

MONTH Name of month spelled out (upper case - month is upper case)

MON abbreviated name of month (upper case - month is upper case)

WW Week of the year (1-53)

IW Week of the year (1-52, 1-53) based on ISO standard

W Week of month (1-5)

DDD day of year (1-366) (Don't forget leap years)

DD day of month (1-31)

VERSIÓN 1 25
PL/SQL

D day of week (1-7)

DAY Name of day (upper case, day is upper case)

DY Abbreviated name of day

J Julian day (Number of days since January 1, 4712 BC)

AM,PM Meridian indicator

A.M., P.M. Meridian indicator with periods.

HH, HH12 Hour of day (0-12)

HH24 Use 24 hour clock for hours (1-24)

MI Minute (0-59)

SS Second (0-60)

SSSSS (five S's) seconds past midnight. (0-86399)

None Date must be in the format 'DD-MON-YY';

FUNCIONES DE GRUPO

♦ AVG AVG ([Distinct | All] col)

Devuelve el promedio de los valores de la columna

♦ COUNT COUNT (* | [Distinct |all] col)

Devuelve el número de filas en la consulta

♦ MAX MAX ([Distinct | All] col)

Devuelve el valor máximo de la consulta de selección.

♦ MIN MIN ([Distinct | All] col)

Devuelve el valor mínimo de la consulta de selección.

26 VERSIÓN 1
 PL/SQL

♦ STDDEV STDDEV ([Distinct | All] col)

Devuelve la desviación estándar del elemento de lista de selección.

♦ SUM SUM ([Distinct | All] col)

Devuelve la suma de los valores para el elemento de lista de selección.

♦ VARIANCE VARIANCE ([Distinct | All] col)

Devuelve la varianza estadística del elemento de lista de selección.

OTRAS FUNCIONES

♦ DECODE DECODE (expr_base, compara1, valor1,

compara2, valor2…

predeterminado)

La función DECODE es similar a una serie de órdenes IF-THEN-ELSE

anidadas. La expresión expr_base se compara sucesivamente con
compara1, compara2, etc. Si expr_base coincide con el elemento de
comparación número i, se devuelve el valor i. Si expr_base no coincide
con ninguno de los valores de comparación, se devuelve el valor
predeterminado.

♦ GREATEST GREATEST (expr1 [expr2] …)

Devuelve la expresión con mayor valor de todos sus argumentos. Cada

expresión es convertida de forma implícita al tipo de expr1 antes de
realizar las comparaciones. Si expr1 es de tipo carácter, se realizan
comparaciones de carácter sin relleno de blancos, y el resultado es de
tipo VARCHAR2.

VERSIÓN 1 27
PL/SQL

♦ LEAST LEAST (expr1 [expr2] …)

Devuelve la expresión con menor valor de todos sus argumentos. Cada

expresión es convertida de forma implícita al tipo de expr1 antes de
realizar las comparaciones. Si expr1 es de tipo carácter, se realizan
comparaciones de carácter sin relleno de blancos, y el resultado es de
tipo VARCHAR2.

♦ NVL NVL (expr1 ,expr2)

Si el valor de expr1 es NULL, la función devuelve el valor de expr2; si

no, devuelve el valor de expr1. El valor devuelto es del mismo tipo que
expr1, salvo si expr1 es una cadena de caracteres, en cuyo caso el
valor de retorno es del tipo VARCHAR2.

♦ USER USER

Devuelve un valor VARCHAR2 que contiene el nombre del usuario

actual de Oracle.

♦ USERENV USERENV (opción)

Devuelve un valor VARCHAR2 que contiene información acerca de la

sesión actual, según la opción seleccionada.

OPCIONES

LANGUAGE Devuelve el idioma y país utilizados

SESSIONID Devuelve el identificador del elemento de registro

de la sesión

TERMINAL Devuelve un identificador, dependiente del

sistema operativo, para el terminal de la sesión actual.

28 VERSIÓN 1
 PL/SQL

SQL> SELECT ename, NVL(TO_CHAR(COMM),'NOT APPLICABLE')

"COMMISSION"
FROM emp
WHERE deptno = 30;

>ENAME COMMISSION
---------- -----------------
ALLEN 300
WARD 500
MARTIN 1400
BLAKE NOT APPLICABLE
TURNER 0
JAMES NOT APPLICABLE

SQL> SELECT TEXT

FROM USER_SOURCE
WHERE NAME = UPPER('&1')
ORDER BY NAME, DECODE(TYPE,'PACKAGE','S','PACKAGE BODY','B',

'PROCEDURE','R','FUNCTION','F'),TYPE,LINE;

SQL> SELECT USERENV('sessionid'), USERENV ('terminal'), USER FROM

dual;

> 6234 JPVF SCOTT

VERSIÓN 1 29
 PL/SQL

PRÁCTICA

EJERCICIO Nº 3

-Crear una tabla empleado como copia de la tabla emp. El nuevo nombre será
emp + nº de usuario.

-Modificar los registros de la tabla creada de modo que en lugar del nombre del
empleado aparezca el nombre del empleado terminado en @ para aquellos
empleados llamados JONES cuyo curso es el el 2

EJERCICIO Nº 4

-Hallar el día de la semana actual en castellano en letra mediante un DECODE

y sin utilizar la máscara ‘d’.

EJERCICIO Nº 5

- Obtener las nuevas comisiones de los empleados teniendo en cuenta que se

subirán un 5% el próximo año y que aquellos que no tengan les corresponderán
como tales la cuarta parte de su salario.

30 VERSIÓN 1
 PL/SQL

4. SENTENCIAS DE CONTROL

SENTENCIA IF

IF Condición THEN sentencias_ejecutables

[ELSIF condición THEN sentencias_ejecutables; ]

ELSE sentencias_ejecutables; ]

END IF;

SQL> DECLARE
v_NumberSeats rooms.number_seats%TYPE;
v_Comment VARCHAR2(35);
BEGIN
/* Recibe el número de asientos de la sala identificada por ID 99999.
Almacena resultado en v_NumberSeats. */
SELECT number_seats
INTO v_NumberSeats
FROM rooms
WHERE room_id = 99999;
IF v_NumberSeats < 50 THEN
v_Comment := 'Fairly small';
ELSIF v_NumberSeats < 100 THEN
v_Comment := 'A little bigger';
ELSE
v_Comment := 'Lots of room';

VERSIÓN 1 31
PL/SQL

END IF;
END;
/

BUCLE INFINITO:

LOOP

sentencias_ejecutables;

[EXIT when ....;]

END LOOP;

SQL> DECLARE
ranking_level NUMBER(3) := 1;
BEGIN
IF max_rank_in >= 1
THEN
LOOP
set_rank (ranking_level);
ranking_level := ranking_level + 1;
EXIT WHEN ranking_level > max_rank_in;
END LOOP;
END IF;
END;

32 VERSIÓN 1
 PL/SQL

BUCLES CONDICIONALES:

WHILE condición

LOOP

sentencias_ejecutables;

[EXIT when .... ;]

END LOOP;

SQL> DECLARE

v_Counter BINARY_INTEGER := 1;

BEGIN

-- Permanece dentro del bucle hasta

-- comprobar que se han hecho 50 iteraciones

WHILE v_Counter <= 50 LOOP

INSERT INTO temp_table

VALUES (v_Counter, 'Loop index');

v_Counter := v_Counter + 1;

END LOOP;

END;

VERSIÓN 1 33
PL/SQL

BUCLES NUMÉRICOS:

FOR índice IN [REVERSE] número_inicio..número_fin

LOOP

sentencias_ejecutables;

[EXIT when ....;]

END LOOP;

SQL> DECLARE

v_Counter NUMBER := 7;

BEGIN

Inserta el valor 7 en temp_table.

INSERT INTO temp_table (num_col)

VALUES (v_Counter);

Este bucle declara de nuevo v_Counter como BINARY_INTEGER, lo que

anula la declaración NUMBER de v_Counter.

FOR v_Counter IN 20..30 LOOP

-- Dentro del bucle, el rango de v_Counter es de 20 a 30.

INSERT INTO temp_table (num_col)

VALUES (v_Counter);

END LOOP;

34 VERSIÓN 1
 PL/SQL

-- Inserta otro 7 en temp_table.

INSERT INTO temp_table (num_col)

VALUES (v_Counter);

END;

EJERCICIO

EJERCICIO 7

EJERCICIO SOBRE BUCLES CONDICIONALES

• Crear una tabla DIAS con un campo de tipo numérico.

• Introducir una fila por cada día del mes actual de tal forma que el resultado
final sea algo parecido a esto:

DÍAS

1
2
3
4
5
...
29
30

• Realizar el ejercicio utilizando for...loop , loop...exit y while.

VERSIÓN 1 35
 PL/SQL

5. SENTENCIAS DE DML

IMPORTANTE

♦ PL/SQL puede incluir sentencias de manejo de datos:

SELECT INTO

INSERT

UPDATE

DELETE

♦ Para procesar sentencias DML, Oracle abre un cursor implícito por cada
sentencia SQL que tenga que procesar. Nos referiremos a él como SQL%.

NOTA

♦ Un cursor es un área de trabajo que utiliza ORACLE para consultas que

devuelven más de una fila, permitiendo la lectura y manipulación de cada
una de ellas. (Se verán en el capítulo siguiente).

Atributos de estos cursores:

• SQL%NOTFOUND "Devuelve TRUE si el INSERT, UPDATE o DELETE

no procesa ninguna fila.

36 VERSIÓN 1
 PL/SQL

• SQL%FOUND " Devuelve TRUE si un INSERT, UPDATE o DELETE ha

procesado alguna fila

• SQL%ROWCOUNT" Devuelve el número de fila leída procesadas por un

INSERT, UPDATE o DELETE.

Para el manejo de errores de una SELECT utilizaremos las excepciones

NO_DATA_FOUND, TOO_MANY_ROWS, etc.

DECLARE

v_StudentRecord students%ROWTYPE;

v_Department classes.department%TYPE;

v_Course classes.course%TYPE;

BEGIN

SELECT *

INTO v_StudentRecord

FROM students

WHERE id = 10000;

SELECT department, course

INTO v_Department, v_Course

FROM classes

WHERE room_id = 99997;

END;

VERSIÓN 1 37
 PL/SQL

BEGIN

UPDATE emp

SET salary = salary + 100

WHERE name = ‘SMITH’;

IF SQL%NOTFOUND THEN

DBMS_OUTPUT.PUT_LINE(‘Smith no trabaja ya en la empresa’);

END IF;

END;

EJERCICIO

EJERCICIO 8

EJERCICIO SOBRE SENTENCIAS DML

• Introducir desde teclado el número de un departamento y mostrar por

pantalla el nombre de dicho departamento y domicilio.

• Utilizar la tabla DEPT y un registro para recuperar los datos de dicha tabla.

• Probar que ocurre si se introduce un número de departamento que no figura

en la tabla DEPT.

38 VERSIÓN 1
 PL/SQL

EJERCICIO

EJERCICIO 9

• Crear un bloque PL/SQL que modifique la tabla EMP de tal forma aquellos
empleados cuyo número de empleado sea –1 pasen a tener como job
‘CLERK’.

• Si no existiera ningún empleado con número –1, insertar dicho empleado

con las siguientes características:

ENAME: ‘PEDRO PEREZ’

JOB: ‘CLERK’
MGR: 7934
DPTNO: 20

• Capturar el error utilizando el cursor implícito SQL%.

VERSIÓN 1 39
 PL/SQL

6. CURSORES

El conjunto de filas resultantes de una consulta con la sentencia SELECT, puede estar
compuesto por ninguna, una o varias filas, dependiendo de la condición que define la
consulta.

Para poder procesar individualmente cada fila de la consulta debemos definir un

cursor (que es un área de trabajo de memoria) que contiene los datos de las filas de la
tabla consultada por la sentencia SELECT.

IMPORTANTE

♦ Un cursor es un área de trabajo que utiliza ORACLE para consultas que

devuelven más de una fila, permitiendo la lectura y manipulación de cada
una de ellas.

♦ Declaración:

CURSOR NOMBRE_CURSOR IS SENTENCIA SELECT;

♦ Para recuperar los datos de un cursor primero hay que abrirlo (OPEN),
luego leerlo (FETCH), y por último cerrarlo (CLOSE).

Cursor parametrizado: cursor que utiliza parámetros en la SELECT.

CURSOR NOMBRE_CURSOR (nombre_parametro tipo_parametro)

IS SENTENCIA SELECT utilizando los parámetros;

Variable de tipo %ROWTYPE.

40 VERSIÓN 1
 PL/SQL

MANEJO DE CURSORES

Se declaran en la zona DECLARE.

Para recuperar todas las filas del cursor utilizaremos un bucle.

Atributos utilizados en el manejo:

• Nombre_cursor%NOTFOUND " Devuelve TRUE si falla y no tiene más

filas que devolver

• Nombre_cursor%FOUND " Contrario de %notfound

• Nombre_cursor%ROWCOUNT " Devuelve el número de fila leída.

• Nombre_cursor%ISOPEN " Devuelve TRUE si el cursor está abierto.

Para abrir el cursor utilizamos la sentencia OPEN.

# Si el cursor está parametrizado: OPEN nombre_cursor (parámetro).

# Para cursores sin parámetros recuperamos una fila utilizando:

FETCH NOMBRE_CURSOR INTO VARIBLE1, VARIABLE2, VARIABLE3...

Cerramos un cursor utilizando la sentencia CLOSE.

BUCLES SOBRE CURSORES

Realiza automáticamente las siguientes acciones:

• Abre el cursor.

VERSIÓN 1 41
PL/SQL

Se realiza la lectura y se ejecutan las sentencias del bucle hasta que no haya
más filas.

• Cierra el cursor.

FOR Nombre_registro IN N _cursor

LOOP

sentencias_ejecutables;

[EXIT when ....;]

END LOOP;

EJEMPLO DE CURSORES

A continuación veremos un ejemplo de cursor con las siguientes características:

Objetivo: Consultar las ventas de una fecha dada ordenadas de mayor a

menor.

Nombre:CVENTAS.

Parámetros: cfecha, variable que contiene la fecha a consultar.

42 VERSIÓN 1
 PL/SQL

PROCEDURE VENTAS5 (xfecha DATE) is

BEGIN

DECLARE CURSOR cventas (cfecha DATE)

IS SELECT articulo,valor

FROM ventas

WHERE fecha=cfecha

ORDER BY valor DESC;

xarticulo ventas.articulo%TYPE;

xvalor ventas.valor%TYPE;

BEGIN

OPEN cventas(xfecha);

FOR i IN 1..5 LOOP

FETCH cventas INTO xarticulo,xvalor;

EXIT WHEN cventas%NOTFOUND;

INSERT INTO ventamayor VALUES

(xfecha,xarticulo,xvalor);

COMMIT;

END LOOP;

CLOSE cventas;

END;

END;

VERSIÓN 1 43
PL/SQL

Para llamar al procedimiento ventas5 en una fecha dada, se puede escribir, por
ejemplo:

ventas5(to_date('15/11/95','DD/MM/YY')

ventas5(sysdate).
A continuación detallaremos las sentencias usadas en este procedimiento:

DECLARE cursor

Define el cursor, su consulta y la lista de parámetros que se pasan a la orden

WHERE, es solo la declaración del cursor y no la realización de la consulta.

xarticulo ventas.articulo%TYPE;

Define la variable xarticulo igual a la columna articulo de la tabla ventas, que

con el uso del atributo de variable %TYPE permite declarar una variable del
mismo tipo que una columna de la tabla. No es necesario conocer cómo está
definida esa columna en la tabla y, en caso que la definición de la columna sea
modificada, automáticamente se cambia la variable xarticulo.

OPEN cventas(xfecha);

Realiza la consulta asociada al cursor, pasando el valor del parámetro y

guardando sus resultados en un área de la memoria, desde la cual,
posteriormente, se pueden leer estas filas.

FOR i IN 1..5 LOOP

Ciclo numérico de repetición para poder consultar las 5 primeras ventas

devueltas por el cursor.

FETCH cventas INTO xarticulo,xvalor;

Lee la siguiente fila de datos del cursor cventas y pasa los datos de la consulta
a las variables xarticulo y xvalor.

EXIT WHEN cventas%NOTFOUND;

44 VERSIÓN 1
 PL/SQL

Garantiza la salida del ciclo antes de la última repetición, en caso que para una
fecha dada se hayan efectuado menos de 5 ventas, ya que en esta situación la
consulta del cursor devuelve menos de 5 filas.

%NOTFOUND es un atributo de cursor que es verdadero cuando la última

sentencia FETCH no devuelve ninguna fila, o cuando la select del cursor no
recupera ninguna fila.

INSERT INTO ventamayor

VALUES(xfecha,xarticulo,xvalor);

Insertar en la tabla ventamayor los valores leídos desde el cursor.

COMMIT;

Actualización de la tabla ventamayor.

END LOOP;

Fin del ciclo.

CLOSE cventas;

Cierra el cursor, eliminado sus datos del área de memoria.

EJERCICIO

EJERCICIO 10

Insertar en la tabla nueva de empleado los valores de aquellos empleados que

pertenezcan a los departamentos 20 y 10 en este orden y añadirles 1000 al
campo salario.

VERSIÓN 1 45
 PL/SQL

EJERCICIO

EJERCICIO 11

EJERCICIO SOBRE CURSORES

• Crear una tabla de totales por departamentos con dos campos: uno para los
números de departamento y otro numérico para meter los totales por
departamento.

• El proceso consiste en pedir por pantalla el número de departamento y

calcular el total de la suma de los salarios mas las comisiones. Utilizar un
cursor.

• Insertar el resultado en la tabla recién creada. Si un departamento no tuviera

empleados introducir un 0 en el campo total.

EJERCICIO

EJERCICIO 12

EJERCICIO SOBRE CURSORES Y SENTENCIAS DML

• Crear una tabla con los campos job y total_empleados.

• Contar cuántos empleados hay de cada tipo de job. Utilizar un cursor para
obtener los distintos tipos de trabajo, y otro cursor parametrizado en el cual
se le pasa el trabajo recuperado con el cursor anterior.

• Introducir en la tabla sólo aquellos trabajos que tengan más de 1 empleado.

46 VERSIÓN 1
 PL/SQL

7. REGISTROS Y TABLAS

REGISTROS PL/SQL

IMPORTANTE

♦ Los registros PL/SQL son estructuras de datos complejas que permiten

tratar variables diferentes, pero relacionadas, como si fuera una unidad.

TYPE NOMBRE_REGISTRO IS RECORD

( campo1 tipo1 [:=expr1],

campo2 tipo2 [:=expr2],

.....

campon tipon [:=exprn])

Una vez declarado un registro es necesario definir una variable de este tipo.

Podemos utilizar también %TYPE y %ROWTYPE.

Se puede asignar un registro a otro del mismo tipo o sus campo individualmente, con
la condición de que ambos sean del mismo tipo.

VERSIÓN 1 47
PL/SQL

EJEMPLO:

DECLARE

TYPE t_Rec1Type IS RECORD (

Field1 NUMBER,

Field2 VARCHAR2(5));

TYPE t_Rec2Type IS RECORD (

Field1 NUMBER,

Field2 VARCHAR2(5));

v_Rec1 t_Rec1Type;

v_Rec2 t_Rec2Type;

BEGIN

/* Aunque v_Rec1 y v_Rec2 sean los mismos campos

y tipos, el tipo RECORD en sí mismo es diferente

Sería una asignación ilegal que elevaría el error PLS-382. */

v_Rec1 := v_Rec2;

/* Estos campos, son del mismo tipo, por los que la asignación sería
legal. */

v_Rec1.Field1 := v_Rec2.Field1;

v_Rec2.Field2 := v_Rec2.Field2;

END;

48 VERSIÓN 1
 PL/SQL

TABLAS EN PL/SQL, ARRAYS

Las tablas en PL/SQL se asemejan a los arrays del lenguaje C.

Sintácticamente se las trata de la misma forma que a las matrices, aunque su

implementación es distinta.

DEFINICIÓN DE UNA TABLA

IMPORTANTE

♦ Una tabla PL/SQL es similar a una tabla de base de datos pero como si
tuviera dos columnas una de ellas es el índice y la otra el valor.

♦ La clave es siempre BINARY_INTEGER y el tipo valor es el tipo que hubiera

especificado en la definición.

♦ El máximo número de filas viene especificado por el número máximo de

valores que se pueden representar mediante el tipo BINARY_INTEGER.

VERSIÓN 1 49
PL/SQL

Dispone de control automático de asignación de memoria, a diferencia de C, en el que

hay que reservarla antes.

TYPE NOMBRE_TABLA IS TABLE OF TIPO_DE_DATO

INDEX BY BINARY_INTEGER;

ATRIBUTOS DE UNA TABLA

• TABLA.COUNT-> Devuelve el número de filas de una tabla.

• TABLA.DELETE->Borra filas de la tabla.

• TABLA.EXISTS-> Devuelve TRUE si existe en la tabla el elemento

especificado.

• TABLA. FIRST-> Devuelve el índice de la primera fila desde la tabla.

• TABLA.LAST-> Devuelve el índice de la última fila de la tabla.

• TABLA.NEXT-> Devuelve el índice de la fila de la tabla que sigue a la fila

especificada.

• TABLA.PRIOR-> Devuelve el índice de la fila de la tabla que antecede a la fila

especificada.

DECLARE
TYPE t_LastNameTable IS TABLE OF students.last_name%TYPE
INDEX BY BINARY_INTEGER;
v_LastNames t_LastNameTable;
v_Index BINARY_INTEGER;
BEGIN
-- Inserta filas en la tabla.
v_LastNames(43) := 'Mason';
v_LastNames(50) := 'Junebug';

50 VERSIÓN 1
 PL/SQL

v_LastNames(47) := 'Taller';
-- Asigna 43 a v_Index.
v_Index := v_LastNames.FIRST;
-- Asigna 50 a v_Index.
v_Index := v_LastNames.LAST;
END;
/

TABLAS DE REGISTROS

Una tabla puede contener datos de tipo registro:

DECLARE
TYPE t_StudentTable IS TABLE OF students%ROWTYPE
INDEX BY BINARY_INTEGER;
/* Cada elemento de v_Students es un registro */
v_Students t_StudentTable;
BEGIN
/* Obtiene el registro record con id = 10,001
lo almacena en v_Students(10001) */
SELECT *
INTO v_Students(10001)
FROM students
WHERE id = 10001;
v_Students(10001).first_name := 'Larry';
DBMS_OUTPUT.PUT_LINE(v_Students(10001).first_name);
END;
/

VERSIÓN 1 51
PL/SQL

52 VERSIÓN 1
 PL/SQL

EJERCICIO

EJERCICIO 13

EJERCICIO SOBRE REGISTROS

• Crear un cursor parametrizado que recupere los campos nombre, trabajo,

salario y comision de la tabla EMP del trabajo introducido por teclado.

• Recuperar los datos con un registro y mostrar por pantalla cada una de las
filas recuperadas en el cursor de la siguiente manera:

SMITH
------>CLERK 800
JAMES
------>CLERK 950
ADAMS
------>CLERK 1100
PEPE
------>CLERK 1300 750
PEDRO PEREZ
------>CLERK
Pedro Perez
------>CLERK

• Utilizar solamente una sentencia dbms_output.put_line.

VERSIÓN 1 53
 PL/SQL

EJERCICIO

EJERCICIO 14

EJERCICIO SOBRE TABLAS

• Crear un bloque PL/SQL que contenga un array de tipo varchar2(25).

Realizar las siguientes operaciones:

$ Insertar en la posición 1 la cadena ‘Uno’

$ Insertar en la posición 3 la cadena ‘Tres
$ Insertar en la posición –2 la cadena ‘Menos dos’
$ Insertar en la posición 0 la cadena ‘Cero’
$ Insertar en la posición 100 la cadena ‘Cien’
$ Mostrar un mensaje con el número total de filas de la tabla
$ Elimina la fila de la posición 100
$ Mostrar un mensaje con el número de filas de la tabla
$ Borrar las filas desde la posición 1 a la 3
$ Mostrar un mensaje con el número de filas de la tabla
$ Borrar el elemento que se encuentre en la primera posición
$ Mostrar el contenido la última posición
$ Mostrar el contenido de la penúltima posición
$ Mostrar el contenido de la posición –1
$ Borrar toda la tabla

54 VERSIÓN 1
 PL/SQL

EJERCICIO

EJERCICIO 15

EJERCICIO SOBRE TABLAS DE REGISTROS

• Crear una tabla (en el núcleo) VENDEDORES, compuesta por tres

columnas:

Empno - number(4),

sal – number(7, 2) ,

comm – number(7, 2).

• Se trata de hacer un bloque PL/SQL capaz de cargar en un array de

registros todos aquellos empleados de la tabla emp que tengan comisión no
nula. Una vez realizada la carga del array se extraerán en orden inverso al
que fueron introducidos y se insertarán en la tabla vendedores.

Utilizar un cursor para recuperar los campos empno, sal y comm

de la tabla EMP.

El registro de constará de los campos empno, sal y comm.

Sería interesante guardar los resultados en un fichero de texto

para comparar lo que devuelve la SELECT con lo que se ha
obtenido con el cursor.

Utilizar un cursor implícito.

VERSIÓN 1 55
 PL/SQL

8. SENTENCIAS TRANSACCIONALES

IMPORTANTE

♦ Una transacción es una serie de órdenes SQL que se completan o fallan

como una unidad. Es decir, todas las órdenes se ejecutan o bien todas
fallan.

♦ Una transacción termina con la orden COMMIT o ROLLBACK.

• Savepoint:

Marcas que pone el usuario durante la transacción para deshacer los cambios
por partes en vez de deshacer toda la transacción.

SAVEPOINT <nombre_del_punto_salvaguarda>;

• Commit:

Realiza todas las transacciones pendientes de la actual transacción

haciéndolas visibles para los demás usuarios.

COMMIT [WORK];

• Rollback:

Deshace todos los cambios pendientes de la transacción actual.

ROLLBACK [WORK] [ TO [SAVEPOINT] punto_salvaguarda; ]

56 VERSIÓN 1
 PL/SQL

DECLARE
v_NumIterations NUMBER;
BEGIN
-- Loop from 1 to 500, inserting these values into temp_table.
-- Commit every 50 rows.
FOR v_LoopCounter IN 1..500 LOOP
INSERT INTO temp_table (num_col) VALUES (v_LoopCounter);
v_NumIterations := v_NumIterations + 1;
IF v_NumIterations = 50 THEN
COMMIT;
v_NumIterations := 0;
END IF;
END LOOP;
END;

PRÁCTICA

EJERCICIO 16

a) Crear una tabla temp, compuesta por estas columnas:

Codigo - number(4),
Mensaje – varchar2(80)

b) Actualizar los vendedores con una comisión mayor que 350$ con un
incremento del 15% de su salario. Si la operación afecta a más de
tres empleados, deshacer la transacción, en cualquier otro caso
validar la transacción. Introducir en la tabla TEMP la operación que
se ha realizado.

VERSIÓN 1 57
 PL/SQL

PRÁCTICA

EJERCICIO 17

EJERCICIO SOBRE UTILIZACIÓN DE SENTENCIAS TRANSACCIONALES

• Crear un bloque PL/SQL que modifique el sal de los empleados que no

tienen comisión a 1000.

• Dentro del mismos bloque PL/SQL actualizar los empleados con una
comisión mayor de 350 $ con un incremento del 15% de su salario. Si la
operación afecta a más de tres empleados, deshacer la transacción, en
cualquier otro caso validar dicha transacción.

• Utilizar sólo un commit en el bloque PL/SQL.

58 VERSIÓN 1
 PL/SQL

9. CONTROL DE ERRORES

IMPORTANTE

♦ El control de errores se realiza en la zona de excepciones del bloque

PL/SQL que es la parte final del bloque PL/SQL anónimo o del
procedimiento almacenado.

WHEN NOMBRE_EXCEPCION1 [OR NOMBRE_EXCEPCION2....]

THEN SENTENCIAS;

NOTA

La zona de excepciones no es obligatoria.

TIPOS DE EXCEPCIONES:

EXCEPCIONES PREDEFINIDAS:

NO_DATA_FOUND,

TOO_MANY_ROWS,

DUP_VAL_ON_INDEX....

VERSIÓN 1 59
PL/SQL

DEFINIDAS POR EL USUARIO:

Mediante:

a) Exception_init, basadas en errores ORACLE

PRAGMA EXCEPTION_INIT (nombre_excepción, número_error);

NOTA

Nombre_excepción hay que definirlo antes con EXCEPTION.

El número de error es un sqlcode que no se halla definido para otra excepción

de Oracle , (p. Ej. NO DATA FOUND).

b) Declarándola como excepción con el tipo de dato EXCEPTION en el DECLARE.

EJECUCIÓN DE EXCEPCIONES

La ejecución de excepciones se produce:

• Automáticamente: Cuando ORACLE detecta un error, para el proceso y

pasa automáticamente a la zona de EXCEPTION (si existe).

• Manualmente: Cuando el proceso llega a un punto en que interesa que

halla un error. La sentencia utilizada es el RAISE.

RAISE <nombre_excepción>;

60 VERSIÓN 1
 PL/SQL

FUNCIONES

La función SQLCODE nos devuelve el número de error producido. Sólo tiene valor
cuando ocurre un error ORACLE

Sólo se habilita en la zona de excepciones.

La función SQLERRM nos devuelve el mensaje del error del valor actual del
SQLCODE.

NOTA

Se puede emplear la función predefinida RAISE_APPLICATION_ERROR para

la creación de mensajes de error por parte del usuario.

Su sintaxis es:

RAISE_APPLICATION_ERROR (código, ‘Mensaje’).

Donde código es un número entre –20000 y –20999.

EJEMPLOS Y TRATAMIENTO

PARA VER LOS ERRORES INTERNOS DE ORACLE E INSERTARLOS EN

UNA TABLA:

PREVIO: Creación de tabla donde almacenarlos:

VERSIÓN 1 61
PL/SQL

REM

REM Creación de tabla de errores

REM

create table errors (err_code number(6), err_msg varchar2(100));

DECLARE

err_msg VARCHAR2(100);

BEGIN

FOR err_num IN 1 .. 20000

LOOP

err_msg := SQLERRM (-err_num);

IF SQLERRM NOT LIKE '%MESSAGE %'

THEN

BEGIN

INSERT INTO errors

VALUES (-err_num, err_msg);

EXCEPTION

WHEN DUP_VAL_ON_INDEX

THEN

NULL;

END;

END IF;

END LOOP;

END;

62 VERSIÓN 1
 PL/SQL

USO HABITUAL DE LAS EXCEPCIONES:

DECLARE

pe_ratio NUMBER(3,1);

BEGIN

SELECT price / earnings

INTO pe_ratio

FROM stocks

WHERE symbol = 'XYZ'; -- puede causar error de división–by–zero

INSERT INTO stats (symbol, ratio)

VALUES ('XYZ', pe_ratio);

COMMIT;

EXCEPTION -- COMIENZA EL BLOQUE DE EXCEPCIÓN

WHEN ZERO_DIVIDE THEN -- Maneja el error de división por cero

INSERT INTO stats (symbol, ratio)

VALUES ('XYZ', NULL);

COMMIT;

...

WHEN OTHERS THEN -- maneja todos los demás errores

ROLLBACK;

END; -- fin de las excepciones y del bloque.

VERSIÓN 1 63
PL/SQL

CREACIÓN DE UN PRAGMA PARA EL MANEJO DE ERRORES:

DECLARE

insufficient_privileges EXCEPTION;

PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031);

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

Oracle devuelve el error number -1031 si, por ejemplo,

intentas MODIFICAR una tabla para la que sólo

tienes privilegios de consulta

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

BEGIN

...

EXCEPTION

WHEN insufficient_privileges THEN

Maneja el error

...

END;

ELEVACIÓN DE MENSAJES DE ERROR PROPIOS DE USUARIO.

DECLARE

...

null_salary EXCEPTION;

/* Número de Error mapeado devuelto por raise_application_error

de una excepción definida por el usuario. */

PRAGMA EXCEPTION_INIT(null_salary, –20101);

64 VERSIÓN 1
 PL/SQL

BEGIN

...

raise_salary(:emp_number, :amount);

EXCEPTION

WHEN null_salary THEN

INSERT INTO emp_audit

VALUES (:emp_number, ...);

...

END;

PRÁCTICA

EJERCICIO 18

EJERCICIO SOBRE MANEJO DE EXCEPCIONES

• Crear un bloque PL/SQL con una única sentencia SELECT sobre la tabla
EMPLEADOS, de tal forma que va a ir recuperando el salario del empleado
del trabajo que se introduzca por teclado.

• En el caso de recuperar un solo empleado elevará una excepción que se va

a llamar SOLO_UN_EMPLEADO mostrando un mensaje por pantalla.

• En el caso de recuperar más utilizaremos la excepción TOO_MANY_ROWS

y mostraremos un mensaje indicativo en la pantalla.

• En el caso de no recuperar ninguno utilizaremos las excepción

NO_DATA_FOUND y mostraremos el respectivo mensaje.

VERSIÓN 1 65
PL/SQL

10. RESUMEN

# PL/SQL, (Procedural Language / SQL), es un lenguaje de programación

procedural estructurado en bloques que amplia la funcionalidad del lenguaje
standard SQL a través de estructuras como:

• Variables y tipos, (predefinidos y definidos por el usuario)

• Estructuras de control, (bucles y condiciones)

• Procedimientos y funciones

• Tipos de objetos y métodos, (a partir de versión 8)

# El Bloque PL/SQL es la unidad básica en todo programa PL/SQL.

# Un programa PL/SQL será un conjunto de bloques PL/SQL, situados de

manera secuencial o anidados

# Existen diversas funciones predefinidas a las que se puede llamar desde

una orden SQL.

# PL/SQL puede incluir sentencias de manejo de datos:

SELECT INTO

INSERT

UPDATE

DELETE

# Para procesar sentencias DML, Oracle abre un cursor implícito por cada
sentencia SQL que tenga que procesar. Nos referiremos a él como SQL%.

66 VERSIÓN 1
 PL/SQL

# Un cursor es un área de trabajo que utiliza ORACLE para consultas que

devuelven más de una fila, permitiendo la lectura y manipulación de cada
una de ellas.

# Declaración de un cursor:

CURSOR NOMBRE_CURSOR IS SENTENCIA SELECT;

Para recuperar los datos de un cursor primero hay que abrirlo (OPEN),
luego leerlo (FETCH), y por último cerrarlo (CLOSE).

# Los registros PL/SQL son estructuras de datos complejas que permiten

tratar variables diferentes, pero relacionadas, como si fuera una unidad.

# Una tabla PL/SQL es similar a una tabla de base de datos pero como si
tuviera dos columnas una de ellas es el índice y la otra el valor.

La clave es siempre BINARY_INTEGER y el tipo valor es el tipo que

hubiera especificado en la definición.

El máximo número de filas viene especificado por el número máximo de

valores que se pueden representar mediante el tipo BINARY_INTEGER.

# Una transacción es una serie de órdenes SQL que se completan o fallan

como una unidad. Es decir, todas las órdenes se ejecutan o bien todas
fallan.

Una transacción termina con la orden COMMIT o ROLLBACK.

# El control de errores se realiza en la zona de excepciones del bloque

PL/SQL que es la parte final del bloque PL/SQL anónimo o del
procedimiento almacenado.

VERSIÓN 1 67
PL/SQL

11. EVALUACIÓN

1 En PL/SQL, pueden incorporarse órdenes DDL

a) Siempre

b) Sólo a través de SQL Dinámico

c) Nunca

2 En un bloque PL/SQL, la declaracióon de variables se hace en:

a) En la zona declarativa

b) Tanto en la zona declarativa como en la zona de proceso

c) Solamente en el bloque de excepciones

3 ¿Qué valor nos daría la variable resultado en la siguiente consulta?

declare a number:=2;
resultado varchar2(20);
begin
select decode(a,3,'Menor',4,'Siguiente',5,'Mayor','Otro') into resultado
from dual;
dbms_output.put_line(resultado);
end;
/
a) No compilaría porque en la declaración no puede asignarse
una variable a un número

b) Mayor

c) Otro

68 VERSIÓN 1
 PL/SQL

4 La sentencia ROLLBACK sirve para:

a) Volver al bloque BEGIN / END anterior al que pertenece

b) Reenvía de nuevo la transacción al usuario

c) Deshace todos los cambios pendientes de la transacción actual

5 Defina bloque PL/SQL anónimo:

...............................................................................................................
...............................................................................................................
...............................................................................................................
...............................................................................................................

6 Definición y usos del tipo PLS_Integer:

...............................................................................................................
...............................................................................................................
...............................................................................................................
...............................................................................................................

7 Definición, usos y declaración de un cursor:

...............................................................................................................
...............................................................................................................
...............................................................................................................
...............................................................................................................

VERSIÓN 1 69
PL/SQL

PL/SQL II

70 VERSIÓN 1
 PL/SQL

INTRODUCCIÓN

En la unidad didáctica PL/SQL I se estudiaron los bloques PL/SQL

anónimos que se compilaban cada vez que eran ejecutados.

Las estructuras que se verán en esta unidad didáctica, (procedimientos,

funciones, paquetes y disparadores), son bloques nominados y pueden
ser almacenados en la base de datos para ser ejecutados tantas veces
como sea necesario.

PL/SQL sólo puede contener órdenes DML y no DDL. Sin embargo esta
restricción se supera a partir de la versión 2.1 con el paquete
DBMS_SQL. Este paquete implementa código PL/SQL y SQL dinámico,
al que se llama desde otros bloques PL/SQL.

En el capítulo 4º de esta unidad se verá cómo utilizar el paquete

DBMS_SQL y sus implicaciones.

VERSIÓN 1 71
PL/SQL

CONTENIDO:

1. PROCEDIMIENTOS Y FUNCIONES

PROCEDIMIENTOS

FUNCIONES

SUBPROGRAMAS LOCALES.

CONTROL DE PROCEDIMIENTOS Y FUNCIONES.

2. DESARROLLO Y UTILIZACIÓN DE PAQUETES

PAQUETES (PACKAGES)

FUNCIONES DENTRO DE PAQUETES.

3. DISPARADORES (TRIGGERS)

CONCEPTO DE DISPARADORES (TRIGGERS)

CREACIÓN DE DISPARADORES

CONCEPTO DE TABLA MUTANTE

4. PL/SQL DINÁMICO

CONCEPTO

MÉTODOS DE IMPLEMENTACIÓN

5. EJERCICIO DE DESARROLLO

6. RESUMEN

7. EVALUACIÓN

72 VERSIÓN 1
 PL/SQL

OBJETIVOS

El alumno tras el estudio de esta unidad didáctica podrá:

! Conocer y utilizar los procedimientos y funciones almacenados a

través del lenguaje PL/SQL.

! Crear y utilizar los paquetes, packages.

! Conocer y utilizar los disparadores.

! Entender los fundamentos del PL/SQL dinámico.

EXPECTATIVAS

• ··························································································································

························································································································

• ··························································································································

························································································································

• ··························································································································

························································································································

VERSIÓN 1 73
 PL/SQL

12. PROCEDIMIENTOS Y FUNCIONES

CONCEPTO DE PROCEDIMIENTOS Y FUNCIONES

Los bloques PL/SQL forman una estructura de bloques anónimos, es decir, se

compilan contra la Base de Datos y una vez ejecutados se pierden.

Se hace necesario tener estructuras de bloques nominados, tal que puedan

almacenarse en la Base de Datos y ser llamados cuando se necesiten.

IMPORTANTE

♦ Un procedimiento es un conjunto de instrucciones PL/SQL que puede ser

llamado usando el nombre que se le haya asignado.

♦ Una función es un conjunto de instrucciones en PL/SQL, que pueden ser

llamados usando el nombre con que se le haya creado.

Se diferencian de los procedimientos, en que las funciones retornan un

valor al ambiente desde donde fueron llamadas.

NOTA

Una vez creados un procedimiento o una función pueden llamarse desde

multitud de lados: SQL*, Forms o cualquier otra herramienta ORACLE.

74 VERSIÓN 1
 PL/SQL

PROCEDIMIENTOS

CREACIÓN DE PROCEDIMIENTOS

Utilizaremos la siguiente sentencia:

CREATE [OR REPLACE] PROCEDURE nombre_procedimiento

[(argumento1 TIPO tipo_dato1,..., argumentoN TIPO tipo_datoN)] IS O AS

Bloque PL/SQL

Su estructura es semejante a:

CREATE OR REPLACE PROCEDURE PROCEDIMIENTO IS

tmpVar NUMBER;
/******************************************************************************
NAME: PROCEDIMIENTO
PURPOSE: To calculate the desired information.
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 10/09/99 1. Created this procedure.
******************************************************************************/
BEGIN

tmpVar := 0;

EXCEPTION

WHEN NO_DATA_FOUND THEN

Null;

WHEN OTHERS THEN

Null;

END PROCEDIMIENTO;
/

VERSIÓN 1 75
PL/SQL

NOTA

El uso de OR REPLACE permite sobreescribir un procedimiento existente.

Si se omite, y el procedimiento ya existe, se producirá un error.

Es conveniente utilizar al principio sólamente CREATE, ya que de existir el

procedimiento en el núcleo podemos destruirlo.

• Los tipos de los argumentos son: IN, OUT, IN OUT

Indican si el parámetro es de entrada, salida o ambos.

Si no se pone nada se supone que es IN.

• IS o AS llevan implícito el DECLARE, así que las variables se declaran

inmediatamente después de IS o AS.

• Un procedimiento termina con END o con END seguido del nombre del
procedimiento.

NOTA

No puede haber declaración de argumentos, es decir, en las declaraciones de

tipo NUMBER, VARCHAR2 o DATE no puede figurar ni la escala ni la precisión.

Es aconsejable utilizar los atributos %TYPE y %ROWTYPE para definir

argumentos y variables.

76 VERSIÓN 1
 PL/SQL

BORRADO

Para borrar un procedimiento se utiliza:

DROP PROCEDURE Nombre_procedimiento;

EJECUCIÓN

IMPORTANTE

♦ Un procedimiento se invoca con la sentencia EXEC[UTE] desde SQL*.

♦ Desde un bloque PL/SQL simplemente es necesario nombrarlo para

ejecutarlo.

Los procedimientos (o funciones) que se declaran como locales a un bloque PL/SQL

se situarán al final del DECLARE.

Las formas de llamar a un procedimiento son las siguientes:

• Exec nombre_procedimiento(var1, var2)

• Exec nombre_procedimiento(var2 => ‘xxx’, var1 => ‘yyy’)

NOTA

Como argumentos en un procedimiento puede haber registros o tablas.

VERSIÓN 1 77
PL/SQL

78 VERSIÓN 1
 PL/SQL

PRÁCTICA

EJERCICIO SOBRE PROCEDIMIENTOS

EJERCICIO 1

• Crear un tabla, EMPLEADOS, con los campos: nombre, trabajo, salario.

• Crear un bloque PL/SQL que extraiga de la tabla EMP aquellos empleados

con comisión nula (utilizando un cursor). Aumentar su salario un 10% e
introducir cada uno de ellos en la tabla EMPLEADOS utilizando un
procedimiento cuyos argumentos van a ser nombre, salario y trabajo.

EJERCICIO 2

• Ampliar el procedimiento creado para el ejercicio anterior de tal manera que

si el empleado tiene como trabajo ‘CLERK’ y su número de departamento es
el 20 se insertará en la tabla EMP con una comisión = 1000 y no se insertará
en la tabla EMPLEADOS. En cualquier otro caso introducir el empleado en
la tabla EMPLEADOS.

• Modificar el número de argumentos pasados al procedimiento añadiendo

uno que sea insertados el cual va a indicar el número de elementos que se
han insertado en la tabla empleados. Mostrar dicho número desde el bloque
PL/SQL con un mensaje.

VERSIÓN 1 79
PL/SQL

FUNCIONES

CREACIÓN DE FUNCIONES

Utilizaremos la siguiente sentencia:

CREATE [OR REPLACE] FUNCTION nombre_función

[(argumento1 TIPO tipo_dato1,...., argumentoN TIPO tipo_datoN)]

RETURN tipo_dato IS O AS

Bloque PL/SQL

Su estructura es semejante a:

CREATE OR REPLACE FUNCTION FUNCION RETURN NUMBER IS

tmpVar NUMBER;
/******************************************************************************
NAME: FUNCION
PURPOSE: To calculate the desired information.
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 10/09/99 1. Created this function.
******************************************************************************/
BEGIN

tmpVar := 0;

EXCEPTION

WHEN NO_DATA_FOUND THEN

Null;

WHEN OTHERS THEN

Null;

RETURN tmpVar;

END FUNCION;

80 VERSIÓN 1
 PL/SQL

• Como TIPO: IN, OUT, IN OUT

NOTA

Los argumentos OUT o IN OUT son válidos dentro de las funciones pero se
usan raramente.

• Return de una función no lleva ni escala ni precisión.

• Return puede llevar expresiones.

• El tipo de dato no puede incluir escala o precisión.

• Una función puede tener más de un return.

• Si declaramos una sin return da un error.

BORRADO

Para borrar una función se utiliza:

DROP FUNCTION Nombre_procedimiento;

EJECUCIÓN

IMPORTANTE

♦ Una función se invoca con la sentencia EXECUTE, como ocurre en los

procedimientos, pero hay que tener en cuenta que tiene que asignarse a
alguna variable del mismo tipo que se devuelve.

VERSIÓN 1 81
PL/SQL

• Una función se puede invocar desde una sentencia SELECT, WHERE, HAVING,
cláusula VALUES de un INSERT y SET de un UPDATE.

• Pueden pasarse y devolver REGISTROS y TABLAS.

FUNCIONES PL/SQL DENTRO DE SENTENCIAS SQL

Para incluir una función dentro de una sentencia SQL:

• Debe de ser una función de fila única (devuelven un único valor). No pueden ser ni
registros ni tablas.

• Todos los argumentos deber ser de tipo IN.

• Los tipos de datos: BOOLEAN, RECORD o TABLE no están permitidos (no son
tipo SQL).

• El valor resultante tiene que ser: NUMBER, CHAR, VARCHAR2, DATE.

• Las funciones no pueden modificar datos en las tablas. No se puede utilizar

INSERT, UPDATE, DELETE dentro de ellas.

NOTA

Las funciones pueden incluirse tanto en el bloque WHERE de una SELECT

como directamente en una VISTA.

82 VERSIÓN 1
 PL/SQL

SUBPROGRAMAS LOCALES.

Al igual que los procedimientos y funciones, es posible incluir en un bloque PL/SQL un

subprograma.

IMPORTANTE

♦ Los subprogramas se incluyen en el DECLARE del bloque y es necesario

incluir el subprograma entero.

PRÁCTICA

EJERCICIOS SOBRE FUNCIONES

EJERCICIO 3

• Crear una función que nos permita realizar una consulta del salario del
empleado de la tabla EMP que se le pasa como parámetro desde SQL* (se
le pasa el número del empleado). Si la consulta ha sido satisfactoria la
función devuelve un SI, en caso contrario devuelve un NO.

• Utilizar desde el SQL para llamar a la función: dbms_output.put_line(‘¿Existe

el empleado?’||di_si_existe(&empleado));

VERSIÓN 1 83
PL/SQL

EJERCICIO 4

• Utilizar una sentencia SELECT sobre la tabla EMP que nos permita ver los
campos empno, ename y el salario de los empleados. Si el empleado tiene
una salario múltiplo de 5 y mayor de 1500 mostrar su salario multiplicado por
15, en caso contrario su salario permanece invariable.

EJERCICIO 5

• Utilizar una SELECT para extraer de la tabla EMP cada uno de los
empleados y su máximo jefe.

• Obtener su máximo jefe utilizando una función.

EJERCICIO 6

• Crear un bloque PL/SQL que sea capaz de cargar un array de registros

cuyos campos son nombre_empledo y ranking y muestre el contenido de
dicho array ordenado.

• El campo ranking lo obtendremos a través de un procedimiento a partir del

cual se va a obtener el puesto que ocupa cada empleado en la empresa en
cuestión de salario + comisión.

• Utilizar un cursor para cargar la tabla.

84 VERSIÓN 1
 PL/SQL

CONTROL DE PROCEDIMIENTOS Y FUNCIONES.

DOCUMENTACIÓN DE PROCEDIMIENTOS Y FUNCIONES.

A través de la vista del diccionario de datos USER_OBJECTS se pueden obtener los

nombres de los siguientes elementos de un esquema.

• DATABASE LINK

• FUNCTION

• INDEX

• PACKAGE

• PACKAGE BODY

• PROCEDURE

• SEQUENCE

• SYNONYM

• TABLE

• TRIGGER

• VIEW

A través de la vista del diccionario de datos USER_SOURCE se puede obtener el

código de los siguientes elementos dentro de un esquema:

• FUNCTION

• PACKAGE

• PACKAGE BODY

• PROCEDURE

VERSIÓN 1 85
 PL/SQL

• Para visualizar el nombre de un procedimiento o función y su lista de argumentos

utilizaremos el comando DESCRIBE.

DESC[RIBE] Nombre_procedimiento o Nombre_función;

• Para mostrar el texto de los errores de compilación utilizaremos la vista

USER_ERRORS.

• Para visualizar los errores de compilación utilizaremos la siguiente sentencia:

SHOW ERRORS PROCEDURE o FUNCTION o PACKAGE o

PACKAGE BODY o TRIGGER nombre_objeto;

DEPURACIÓN DE PROCEDIMIENTOS Y FUNCIONES.

IMPORTANTE

♦ Para la depuración de procedimientos y funciones se utiliza el

procedimiento suministrado por Oracle: DBMS_OUTPUT

• Para activar este procedimiento utilizaremos la opción

SET SERVEROUTPUT ON

• Para preparar el texto del mensaje de salida se utiliza PUT.

DBMS_OUTPUT.PUT (Texto);

86 VERSIÓN 1
 PL/SQL

• Para visualizar por pantalla el mensaje utilizamos NEW_LINE.

DBMS_OUTPUT.NEW_LINE;

• Para preparar y visualizar el texto se utiliza PUT_LINE (opción más utilizada)

• Estos procedimientos están sobrecargados y pueden mostrar directamente datos

de los siguientes formatos:

DBMS_OUTPUT.PUT_LINE (varchar2)

DBMS_OUTPUT.PUT_LINE (number)

DBMS_OUTPUT.PUT_LINE (date)

NOTA

No hace falta transformar un entero a varchar2 para utilizarlo en la sentencia

DBMS_OUTPUT.

VERSIÓN 1 87
PL/SQL

Este sería el diagrama en el que se explica el funcionamiento del DBMS_OUTPUT:

NOTA

La salida DBMS_OUTPUT es independiente para cada sesión.

NOTA

Para que no nos dé un error debido a que el buffer es muy pequeño, conviene
inicializarlo mediante el comando:

SET SERVEROUTPUT ON size 1000000

88 VERSIÓN 1
 PL/SQL

13. DESARROLLO Y UTILIZACIÓN DE PAQUETES

PAQUETES (PACKAGES)

IMPORTANTE

♦ Un paquete es un grupo de identificadores y rutinas agrupadas dentro de la

misma construcción.

Estructura de un paquete:

VERSIÓN 1 89
PL/SQL

Variable Pública

Declaración del Procedimiento A

Procedimiento Público
PACKAGE

PÚBLICA
Zona de Especificación

Variable Privada

Definición del Procedimiento B Procedimiento Privado PACKAGE

BODY
Definición del Procedimiento A
Procedimiento Público
Variable local

PRIVADA
Cuerpo del paquete

CREACIÓN DE PAQUETES

Para crear un paquete:

CREATE [OR REPLACE] PACKAGE Nombre_paquete IS O AS

Declaración variable

Declaración cursor

Declaración excepción

Declaración procedimiento

Declaración función

END Nombre_paquete

90 VERSIÓN 1
 PL/SQL

Para la creación del cuerpo del paquete:

CREATE [OR REPLACE] PACKAGE BODY Nombre_paquete IS O AS

Declaración variable
Declaración cursor
Declaración excepción
Código del procedimiento
Código de la función
[BEGIN Código de inicialización del paquete]

END Nombre_paquete

• Para borrar un paquete y el cuerpo del paquete se utiliza.

DROP PACKAGE Nombre_paquete

• Para borrar el cuerpo del paquete se utiliza.

DROP PACKAGE BODY Nombre_paquete

• Para invocar un procedimiento o función de un paquete desde fuera del mismo se

utiliza la orden EXECUTE desde SQL*, (desde un form no es necesario el
EXECUTE).

EXEC[UTE] Nombre_paquete.Nombre_función ó
Nombre_procedimiento

NOTA

• Cualquier objeto de la cabecera de un paquete está dentro del ámbito y

visible desde fuera.

NOMBRE_PAQUETE.NOMBRE_OBJETO

VERSIÓN 1 91
PL/SQL

• Siempre es posible declarar una variable en un bloque PL/SQL del mismo tipo que
uno que figure dentro del package:

DECLARE

V_hist ClassPackage.t_lista;

NOTA

Dentro del cuerpo del paquete se puede hacer referencia a los objetos sin
necesidad del "."

• Los packages disponen de la opción de sobrecarga de procedimientos y funciones,

es decir, con el mismo nombre y distintos argumentos.

DECLARE
FUNCTION value_ok (date_in IN DATE)
RETURN BOOLEAN IS
BEGIN
RETURN date_in <= SYSDATE; Date Version
END;

FUNCTION value_ok (num_in IN NUMBER)

RETURN BOOLEAN IS
BEGIN
RETURN number_in > 0; Number Version
END;
BEGIN
IF value_ok (SYSDATE) THEN ... END IF;
IF value_ok (total_sales) THEN ... END IF;
END;

92 VERSIÓN 1
 PL/SQL

NOTA

$ No se pueden sobrecargar si difieren sólo en el modo (IN OUT).

$ No se pueden sobrecargar si difieren sólo en el tipo de dato devuelto.

$ No se pueden sobrecargar si difieren sólo en la familia del tipo de dato.

Podemos cambiar el cuerpo sin cambiar la cabecera.

Si cambiamos la cabecera y no el cuerpo éste queda inválido, siendo necesario

volver a compilarlo.

• Para compilar los objetos inválidos utilizaremos la siguiente sentencia:

select

'alter '||

decode ( object_type, 'PACKAGE BODY', 'PACKAGE', object_type )||

' '||

object_name||

' compile'||

decode ( object_type, 'PACKAGE BODY', ' body;', ';' )

from

user_objects

where

status = 'INVALID'

VERSIÓN 1 93
PL/SQL

FUNCIONES DENTRO DE PAQUETES.

• Es necesario indicar el nivel de depuración de una función dentro de un paquete

cuando se crea un paquete (en la especificación).

• El nivel de depuración determina qué operaciones no van a ser realizadas por la

función del paquete.

• No se puede utilizar una función que está dentro de un paquete en una sentencia
SQL. Es necesario indicar el nivel de depuración.

Para ello utilizaremos la sentencia:

PRAGMA RESTRICT_REFERENCES (NOMBRE_FUNCION,

WNDS [,WNPS] [,RNDS] [,RNPS]);

El pragma se indica en la especificación del paquete detrás de todas las

funciones.

El argumento WNDS debe indicarse SIEMPRE que se utilice

RESTRICT_REFERENCES.

WNDS -> (Writes No Database State) Indica que la función no va

modificar tablas de la base de datos

WNPS -> (Writes No Package Status) Indica que la función no va a

cambiar los valores de variables de un paquete público.

RNDS -> (Reads No Database State) Indica que la función no va

aconsultar ninguna tabla de la base de datos.

RNPS-> (Reads No Package Status) Indica que la función no va a

referenciar los valores de las variables de un paquete público.

CREATE OR REPLACE PACKAGE PAQUETE IS

FUNCTION MyFuncName ( inVal Number ) Return Number;

PROCEDURE MyProcName ( inVal Number, JobId VARCHAR2 );

94 pragma restrict_references ( MyFuncName, WNDS, WNPS, RNDS );

VERSIÓN 1

END PAQUETE;

/
 PL/SQL

VERSIÓN 1 95
PL/SQL

CREATE OR REPLACE PACKAGE BODY PAQUETE AS

FUNCTION MyFuncName ( inVal Number ) Return Number IS

TmpVar NUMBER;

BEGIN

tmpVar := 0;

EXCEPTION

WHEN NO_DATA_FOUND THEN

Null;

WHEN OTHERS THEN

Null;

RETURN tmpVar;

END MyFuncName;

PROCEDURE MyProcName ( inVal Number, JobId VARCHAR2 ) IS

TmpVar NUMBER;

BEGIN

tmpVar := 0;

EXCEPTION

WHEN NO_DATA_FOUND THEN

Null;

WHEN OTHERS THEN

Null;

END MyProcName;

END PAQUETE;

96 VERSIÓN 1
 PL/SQL

PRÁCTICA

EJERCICIOS SOBRE PAQUETES

EJERCICIO 7

• Crear un paquete con los siguientes elementos: una tabla para almacenar
registros con los campos nombre, salario, un procedimiento
pr_insertar(nombre, salario) y un índice.

• Se trataría de recuperar desde un bloque PL/SQL los elementos de la tabla

EMP cuya suma de su salario + comisión > 2000 e ir introduciéndolos en la
tabla del paquete recién creado a partir de la función pr_insertar.

• Consultar desde el bloque PL/SQL el índice del paquete y obtener el número

de registros introducidos en dicha tabla.

EJERCICIO 8

• Ampliar el paquete creado en el ejercicio con las funciones fu_recupera_sal

y fu_inicializa.

• La nueva función, fu_recupera_sal, nos va a permitir recuperar el salario de

un empleado si se le pasa el nombre como argumento. En el caso de halla
varias personas con el mismo nombre recuperar la primera. En el caso de
que no hubiera ninguna mostrará por pantalla el mensaje de error ORA-
20001 ‘Persona no encontrada. Probar esta función desde SQL*.

• La función fu_inicializa borra la tabla y pone el índice a 0.

VERSIÓN 1 97
PL/SQL

EJERCICIO 9

• Crear un paquete con tres funciones que se van a llamar (las tres)
fu_recupera.

♦ Si se le pasa un número me va a devolver el salario de la persona

que se encuentra en la posición que se le está pasando como
argumento de la tabla del paquete anterior. En caso de no existir
dicho elemento en la tabla mostrará un mensaje por pantalla.

♦ Si se pasa una cadena de caracteres tiene que hacer una

consulta sobre la tabla EMP de tal forma que nos tiene que contar
el número de personas con el mismo nombre que la cadena que
se le está pasando.

♦ Si se le pasa una fecha tiene que introducir en una tabla (no

pública) las personas con una antigüedad mayor que la pasada
como parámetro de forma ordenada por fecha . Se trata de una
tabla de registros con los campos Nombre, antigüedad. Extraer
cada uno de esos empleados de la tabla, visualizando el nombre
del empleado y el número de días que lleva en la empresa.

EJERCICIO 10

• Ampliar el primer paquete creado con la función fu_media_comision la cual

nos va a dar la media de las comisiones de todos los empleados que tienen
tabajo ‘CLERK’.

• Realizar una consulta en SQL* sobre la tabla EMP de forma que extraiga los
empleados cuya comisión sea mayor que el valor devuelto por la función
fu_media_comision.

98 VERSIÓN 1
 PL/SQL

14. DISPARADORES EN BASE DE DATOS

DISPARADORES (TRIGGERS)

IMPORTANTE

♦ Un disparador es un bloque PL/SQL asociado a una tabla que se ejecuta

cuando una determinada instrucción SQL se va a ejecutar sobre dicha
tabla.

Se asemejan con los procedimientos y funciones en que son bloques PL/SQL

nominados con secciones declarativa, ejecutable y de manejo de excepciones y en
que se almacenan en la base de datos.

Se diferencian en que se ejecutan implícitamente cuando se ejecuta una sentencia de

manipulación de datos sobre una tabla (INSERT, UPDATE, DELETE), y en que el
disparador no admite argumentos.

Los disparadores se utilizan principalmente para:

$ Para el mantenimiento de restricciones de integridad compleja, que no sean

posibles con las restricciones declarativas definidas en el momento de crear la
tabla. (Común en desnormalizaciones).

$ En la auditoría de la información contenida en la tabla, registrando los cambios

realizados y la identidad de quien lo llevó a cabo.

VERSIÓN 1 99
PL/SQL

$ El aviso automático a otros programas de que hay que llevar a cabo una
determinada acción, cuando se realiza un cambio en la tabla.

Tipos de disparadores:

$ Disparadores de sentencia: Se ejecutan una sola vez por cada sentencia.

$ Disparadores de fila: Se activan una vez por cada fila afectada por la orden que
provocó el disparo.

Se pueden combinar diferentes acontecimientos en un único disparador, utilizando los

predicados condicionales INSERTING, UPDATING y DELETING dentro del cuerpo del
disparador (devuelven un boolean TRUE o FALSE cuando se consultan).

Se puede visualizar el contenido de un disparador a través de la vista del diccionario

de datos USER_TRIGGERS.

Clasificación:

*BEFORE
Según el momento en el que salta el disparador
* AFTER
* INSERT
Según el tipo de operación que provoca su actuación * UPDATE
* DELETE
* Sentencia
Según las veces que se ejecuta el cuerpo del disparador
* Fila

En la figura de abajo se disparan los triggers para la operación que contiene la

cláusula WHERE: WHERE ENAME LIKE 'J%':

100
NOTA VERSIÓN 1

• Dentro de un disparador están prohibidas sentencias transaccionales:

COMMIT, ROLLBACK y SAVEPOINT.
 PL/SQL

• Para borrar un trigger utilizaremos:

DROP TRIGGER Nombre_trigger

CREACIÓN DE DISPARADORES

DISPARADORES DE SENTENCIA

Se utiliza las siguientes sentencia para los disparadores de sentencia:

CREATE [OR REPLACE] TRIGGER Nombre_disparador

MOMENTO ACONTECIMIENTO ON Nombre_tabla

BLOQUE PL/SQL

DISPARADORES DE FILA

Para los disparadores de fila se utiliza esta otra sentencia:

CREATE [OR REPLACE] TRIGGER Nombre_disparador

MOMENTO ACONTECIMIENTO ON Nombre_tabla

FOR EACH ROW [WHEN Condición_restricción]

[REFERENCING OLD O NEW AS Nombre_sustituto_old_o_new]

• Dentro de un disparador de fila, se puede referenciar el valor de la columna antes

de ser modificado anteponiendo el cualificador :OLD y referenciar el nuevo valor de
la columna anteponiendo el cualificador :NEW.

• Durante la inserción el valor antiguo es NULL, durante el borrado el valor nuevo es

NULL.

VERSIÓN 1 101
PL/SQL

• El cualificador :NEW puede aparecer a la izquierda de una asignación en el cuerpo

PL/SQL solamente en disparadores de fila BEFORE. No puede aparecer en la
parte izquierda de una asignación en el cuerpo PL/SQL de un disparador AFTER.
Ni en el predicado condicional DELETING.

• El cualificador :OLD no puede aparecer nunca en la parte izquierda de una

sentencia de asignación.

NOTA

Aunque no surga un error de compilación, hay que tener en cuenta que :OLD no
está definido para las órdenes INSERT, así como no lo estará :NEW para el
DELETE.

NOTA

A pesar de que :NEW y :OLD sean tratados sintácticamente como registros del
tipo tabladisparo%Rowtype, en realidad no son registros (por tanto no se
pueden asignar como registros completos); sino que son variables de
acoplamiento.

ACTIVACIÓN DE DISPARADORES

Para activar un disparador:

ALTER TRIGGER Nombre_disparador ENABLE;

Para desactivar un disparador:

ALTER TRIGGER Nombre_disparador

102 VERSIÓN 1
 PL/SQL

ORDEN DE EJECUCIÓN DE LOS DISPARADORES

Los disparadores se ejecutan según el siguiente orden:

1. Ejecutar, si existe, el disparador de tipo BEFORE (disparador previo) de tipo

sentencia.

2. Para cada fila a la que afecte la orden.

a) Ejecutar, si existe, el disparador BEFORE a nivel de fila.

b) Ejecutar la propia orden.

c) Ejecutar, si existe, el disparador de tipo AFTER con nivel de fila.

3. Ejecutar, si existe, el disparador AFTER, de tipo sentencia.

CONCEPTO DE TABLA MUTANTE

IMPORTANTE

♦ Una tabla mutante es una tabla que está modificándose actualmente por
una orden DML (INSERT, UPDATE, DELETE).

Para un disparador, ésta es la tabla sobre la que está definido.

Un trigger de sentencia nunca provoca el error de tabla mutante.

VERSIÓN 1 103
PL/SQL

Supongamos un trigger for each row que se dispara cuando se modifiquen datos de
una tabla. El trigger a su vez realiza una modificación sobre la misma tabla. Esto
provoca que la tabla quede mutante por la incosistencia de la información en ese
instante y da un error por pantalla.

La solución del error de la tabla mutante consistiría en crear un disparador de

sentencia BEFORE en el que se van almacenando los datos (no se consulta ni
modifica la misma tabla), y otro de fila AFTER que utilizarán los valores almacenados
por el disparador de sentencia:

BEFORE -> Inicializa

BEFORE EACH ROW -> Almacena

AFTER -> Procesa

NOTA

Si una operación INSERT afecta a una única fila, entonces los disparadores de
fila previo y posterior por dicha fila no tratarán a la tabla como mutante.

CREATE OR REPLACE TRIGGER DISPARADOR

BEFORE INSERT OR UPDATE ON MyTableName

FOR EACH ROW

tmpVar NUMBER;

BEGIN

tmpVar := 0;

Select MySeq.NextVal into tmpVar from dual;

:NEW.SequenceColumn := tmpVar;

104 VERSIÓN 1
 PL/SQL

:NEW.CreatedDate := Sysdate;

:NEW.CreatedUser := User;

EXCEPTION

WHEN OTHERS THEN

Null;

END DISPARADOR;

PRÁCTICA

EJERCICIOS SOBRE TRIGGERS

EJERCICIO 11

• Crear un trigger sobre EMP de forma que cuando se vaya a insertar un

nuevo registro verifique que no haya más de 20 registros ya insertados. Si
ya los hubiera, mostrar un mensaje de error indicando la imposibilidad de
insertar el nuevo registro.

EJERCICIO 12

• Hacer un disparador de base de datos sobre la tabla DEPT, en el cual

cuando se inserte o se actualice el número de departamento, compruebe
que este número sea múltiplo de diez.

VERSIÓN 1 105
PL/SQL

EJERCICIO 13

• Crear la tabla CONTABILIDAD con los campos departamento, movimiento y

dinero.

• Crear un disparador de fila de base de datos en el cual cada vez que se

hace una operación sobre la tabla EMP (inserción, modificación,borrado),
realice una inserción sobre la tabla CONTABILIDAD con el movimiento
realizado sobre EMP. Los movimientos a insertar son de tres tipo:
INSERCION, MODIFICACION, BORRADO.

EJERCICIO 14

• Realizar un disparador de fila sobre la tabla EMP que se ejecute cuando se

actualiza o se inserte sobre la tabla EMP, comprobar que el salario se
encuentra entre el máximo y el mínimo de ese trabajo para un
departamento.

En el caso de que no exista ninguna fila en la tabla EMP cuyo

departamento sea igual al insertado, hay que comprobar que existe el
departamento en la tabla DEPT. En el caso de existir, insertar la fila en
EMP sin más comprobaciones. En caso de no existir insertar el
departamento en la tabla DEPT con el campo dname a NULL y el campo
loc a NULL.

• Si se modifica el departamento de un director hay que cambiar el

departamento a sus empleados.

EJERCICIO 15

• Crear VIGENCIAS con los campos: empleado, f_desde y f_hasta.

• Realizar un disparador sobre la tabla VIGENCIAS del manera que

compruebe que cada fila tratada no se solape con otra ya insertada.

106 VERSIÓN 1
 PL/SQL

EJERCICIO 16

• Realizar un disparador de fila sobre la tabla emp que se ejecute cuando se

actualiza sobre la tabla emp el campo trabajo (JOB) y que compruebe que si
se trata de un vendedor (‘SALESMAN’) su comisión no sea mayor que la
del jefe con menor comisión.

• Modificar los empleados de la tabla emp que tienen trabajo ‘CLERK’ y

sustituirlo por ‘SALESMAN’. Observar lo que pasa.

• Sustituir el trigger de fila por dos, uno antes de modificar (for each row) y
otro after (de sentencia) y un paquete que solucione el error de la tabla
mutante.

VERSIÓN 1 107
 PL/SQL

15. PL/SQL DINÁMICO

CONCEPTO

IMPORTANTE

♦ El PL/SQL dinámico nace de la necesidad de implementar órdenes DDL

dentro de un bloque de PL/SQL.

Se implementa en el paquete DBMS_SQL

Mediante el DBMS_SQL podemos manejar también órdenes DML.

Las órdenes SQL Dinámicas tienen muy pocas restricciones, porque si se desea, la
orden completa SQl se puede determinar en el momento de la ejecución, en lugar de
en la compilación.

NOTA

En ocasiones, la necesidad de incluir órdenes DML dinámicamente surge al

hacer referencia a una tabla, que se creará en ejecución; por lo que si la orden
que la refiere estuviera en SQL estático, no compilaría.

Desventajas:

• El SQL Dinámico no se ejecuta con la misma eficiencia que el estático.

108 VERSIÓN 1
 PL/SQL

• Aquellas tablas a las que se hace referencia no aparecerán en

USER_DEPENDENCES.

• Cuando se borra una tabla dinámicamente, es labor del usuario el cambiar el

estado de los paquetes que hagan referencia a dicha tabla.

PROCEDIMIENTOS DE SQL DINÁMICO:

• Función DBMS_SQL.OPEN_CURSOR RETURN INTEGER;

Abre el puntero para el cursor

• Procedure DBMS_SQL.PARSE (cursor IN INTEGER, sentencia_a_ejecutar IN

VARCHAR2, DBMS_SQL.NATIVE IN INTEGER);

Asocia el puntero con la sentencia SQL

• Función DBMS_SQL.IS_OPEN (c IN INTEGER) RETURN BOOLEAN;

Comprueba si el cursor ya está abierto.

• Procedure DBMS_SQL.BIND_VARIABLE

(cursor IN INTEGER, ‘:variable_usada’ IN VARCHAR2,

dato a incluir IN <datatype>);

Asigna las variables a la sentencia.

• Procedure DBMS_SQL.DEFINE_COLUMN

(cursor IN INTEGER, position_en_la_select_de_la_columna IN INTEGER,

columna_variable IN <column_datatype>);

Tipos de datos de las columnas para crear variables locales.

• Función DBMS_SQL.EXECUTE (cursor IN INTEGER) RETURN INTEGER;

VERSIÓN 1 109
 PL/SQL

Ejecuta la sentencia asociada al cursor.

• FUNCTION DBMS_SQL.FETCH_ROWS

(cursor IN INTEGER) RETURN INTEGER;

Recoge la siguiente fila del cursor

• PROCEDURE DBMS_SQL.COLUMN_VALUE

(cursor IN INTEGER, posicion IN INTEGER, valor OUT <datatype>,

[, column_error OUT NUMBER]

[, actual_length OUT INTEGER ]);

Introduce el valor del cursor en variables locales

• PROCEDURE DBMS_SQL.CLOSE_CURSOR

(cursor IN OUT INTEGER);

Cierra el cursor.

MÉTODOS DE IMPLEMENTACIÓN

IMPORTANTE

♦ Existen varios métodos distintos de implementar SQL Dinámico:

• Ejecución de órdenes DML

• Ejecución de órdenes DDL que no sean consultas

• Ejecución de consultas

110 VERSIÓN 1
 PL/SQL

EJECUCIÓN DE ÓRDENES DML

Estructura de la orden:

• Abir el cursor (OPEN_CURSOR)

• Analizar la orden (PARSE)

• Cerrar el cursor
(CLOSE_CURSOR)

DECLARE t_owner varchar2(30) := 'ACADEMIA';

CURSOR all_obj_cur IS
SELECT table_name ,trigger_name
FROM user_triggers
WHERE STATUS='DISABLED'
order by table_name,trigger_name;
disable_cursor INTEGER;
out_str VARCHAR2(1000);
BEGIN
FOR obj_rec IN all_obj_cur
LOOP
disable_cursor := DBMS_SQL.OPEN_CURSOR;
out_str := 'ALTER TRIGGER ' || obj_rec.trigger_name
|| ' ENABLE';
DBMS_SQL.PARSE (disable_cursor, out_str,
DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR (disable_cursor);
END LOOP;
END;
/
VERSIÓN 1 111
PL/SQL

EJECUCIÓN DE ÓRDENES DDL QUE NO SEAN CONSULTAS

Estructura de la orden:

• Abir el cursor (OPEN_CURSOR)

• Analizar la orden (PARSE)

• Acoplar todas las variables de

entrada (BIND_VARIABLE)

• Ejecutar la orden (EXECUTE)

• Cerrar el cursor
(CLOSE_CURSOR)

CREATE OR REPLACE PROCEDURE UpdateSal(

p_Department IN emp.deptno%TYPE,
p_NewSal IN emp.sal%TYPE,
p_RowsUpdated OUT INTEGER) AS
v_CursorID INTEGER;
v_UpdateStmt VARCHAR2(100);
BEGIN
-- Open the cursor for processing.
v_CursorID := DBMS_SQL.OPEN_CURSOR;
--Determine the SQL string.
v_UpdateStmt :=
'UPDATE emp
SET sal = :nc
WHERE deptno = :dept';
--Parse the statement.
DBMS_SQL.PARSE(v_CursorID, v_UpdateStmt,
DBMS_SQL.V7);
--Bind p_NewSal to the placeholder :nc.

112 VERSIÓN 1
 PL/SQL

DBMS_SQL.BIND_VARIABLE(v_CursorID, ':nc', p_NewSal);

--Bind p_Department to the placeholder :dept.
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':dept',
p_Department);
-- Execute the statement.
p_RowsUpdated := DBMS_SQL.EXECUTE(v_CursorID);
-- Close the cursor.
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
EXCEPTION
WHEN OTHERS THEN
-- Close the cursor, then raise the error again.
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
RAISE;
END UpdateSal;
/

EJECUCIÓN DE CONSULTAS

Estructura de la orden:

• Abir el cursor (OPEN_CURSOR)

• Analizar la orden (PARSE)

• Acoplar todas las variables de

entrada (BIND_VARIABLE)

• Definir las variables de salida

(DEFINE_COLUMN)

• Ejecutar la consulta (EXECUTE)

VERSIÓN 1 113
PL/SQL

• Extraer las filas

(FETCH_ROWS)

• Devolver los resultados a

variables (COLUMN_VALUE)

• Cerrar el cursor
(CLOSE_CURSOR)

114 VERSIÓN 1
 PL/SQL

CREATE OR REPLACE FUNCTION obtener_nombre

(empno_in IN emp.empno%TYPE)
RETURN VARCHAR2
IS
cur INTEGER;
out_str varchar2(1000);
fdbk INTEGER;
return_value emp.ename%TYPE := NULL;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
out_str := ('SELECT ename FROM emp WHERE empno = :id');
DBMS_SQL.PARSE (cur, out_str, DBMS_SQL.NATIVE);
/* Define the first/only column as a string. */
DBMS_SQL.DEFINE_COLUMN (cur, 1, return_value, 100);
/* Bind the specified contact ID into the SELECT. */
DBMS_SQL.BIND_VARIABLE (cur, 'id', empno_in);
/* Execute and fetch from the cursor. */
fdbk := DBMS_SQL.EXECUTE (cur);
fdbk := DBMS_SQL.FETCH_ROWS (cur);
/* Fetch the row. If feedback is 0, no match found */
IF fdbk = 1
THEN
/* A record was fetched. Retrieve the name. */
DBMS_SQL.COLUMN_VALUE (cur, 1, return_value);
END IF;
DBMS_SQL.CLOSE_CURSOR (cur);
RETURN return_value;
END obtener_nombre;
/

VERSIÓN 1 115
PL/SQL

NOTA

Procurar siempre cerrar todos los cursores una vez ejecutada la sentencia,
incluso a través de las excepciones.

NOTA

No utilice comentario especificados con – dentro de un bloque PL/SQL

ejecutado con DBMS_SQL.

El símbolo de doble guión hace que se considere como comentario todo lo que
sigue, hasta encontrar un carácter de avance de linea. Sin embargo, dado que
el bloque completo se encuentra en una cadena, todo el resto del bloque sería
considerado un comentario.

Utilice los comentarios de estilo C delimitados por /* y */ en su lugar.

116 VERSIÓN 1
 PL/SQL

PRÁCTICA

EJERCICIO SOBRE CURSORES DINÁMICOS

EJERCICIO 17

• Desactivar todas las constraints de la base de datos teniendo en cuenta que

en primer lugar hay que desactivar las Primary y Unique y en segundo lugar
las Foreign y Check constraints.

NOTA: La notación del campo CONSTRAINT_TYPE es la siguiente:

P Primary Key
U Unique Key
R Foreign Key
C Check constraint

• Crear el procedimiento contrario siguiendo el orden inverso.

EJERCICIO 18

• Crear un procedimiento que nos obtenga dinámicamente el nombre del

departamento y la localización del número del departamento que le
pasemos.

EJERCICIO 19

• Codificar un procedimiento que cree una tabla copia de la de empleado y a

través de un parámetro en que le pasamos el nombre del empleado, copiar
los datos del empleado requerido en la nueva tabla.

VERSIÓN 1 117
PL/SQL

1.-Crear una tabla TMP+Nombre_usuario que sea una copia de la de Empleado

1.1- Añadirle los campos Usuario y Fecha modificación.

1.2- Crear un disparador que audite el usuario y la fecha en que se

inserta o se modifica dicha tabla.

2.- Mediante un cursor Modificar dicha tabla y subir el sueldo a los empleados
que pertenezcan al departamento SALES

3.- Crear una función que devuelva la media de sueldo de los empleados que
tengan el mismo puesto (JOB) pasándole como parámetro en formato texto
dicho cargo.

4.- Crear un disparador tal que cuando modifiquemos el sueldo del presidente,
se encargue de recalcular el resto de los sueldos de todos los empleados en el
mismo porcentaje.

5.- Crear un procedimiento que actualice la fecha HIREDATE al primer día del
siguiente mes del día de la semana que coincida con la fecha anterior.

6.- Crear un procedimiento dinámico que borre todos los objetos de la base de
datos del tipo que nosotros le pasemos como parámetro del usuario que está
conectado.

Si no se le pasa ningún parámetro, debe borrar todos. (Excepto el propio

procedimiento creado).

16. EJERCICIO DE DESARROLLO

118 VERSIÓN 1
 PL/SQL

# Un procedimiento es un conjunto de instrucciones PL/SQL que puede ser

llamado usando el nombre que se le haya asignado.

# Una función es un conjunto de instrucciones en PL/SQL, que pueden ser

llamados usando el nombre con que se le haya creado.

Se diferencian de los procedimientos, en que las funciones retornan un

valor al ambiente desde donde fueron llamadas.

# Un procedimiento se invoca con la sentencia EXEC[UTE] desde SQL*.

Desde un bloque PL/SQL simplemente es necesario nombrarlo para

ejecutarlo.

# Una función se invoca con la sentencia EXECUTE, como ocurre en los

procedimientos, pero hay que tener en cuenta que tiene que asignarse a
alguna variable del mismo tipo que se devuelve.

# Los subprogramas se incluyen en el DECLARE del bloque y es necesario

incluir el subprograma entero.

# Para la depuración de procedimientos y funciones se utiliza el

procedimiento suministrado por Oracle: DBMS_OUTPUT

# Un paquete es un grupo de identificadores y rutinas agrupadas dentro de la

misma construcción.

17. RESUMEN

VERSIÓN 1 119
PL/SQL

# Un disparador es un bloque PL/SQL asociado a una tabla que se ejecuta

cuando una determinada instrucción SQL se va a ejecutar sobre dicha
tabla.

# Una tabla mutante es una tabla que está modificándose actualmente por
una orden DML (INSERT, UPDATE, DELETE).

Para un disparador, ésta es la tabla sobre la que está definido.

# El PL/SQL dinámico nace de la necesidad de implementar órdenes DDL

dentro de un bloque de PL/SQL.

Se implementa en el paquete DBMS_SQL

# Existen varios métodos distintos de implementar SQL Dinámico:

Ejecución de órdenes DML

Ejecución de órdenes DDL que no sean consultas

Ejecución de consultas

120 VERSIÓN 1
 PL/SQL

8 Para ejecutar un procedimiento desde SQLPLUS utilizaremos la

sentencia:

a) START procedimiento

b) EXEC procedimiento

c) RUN procedimiento

9 ¿Cual de estas sentencias es incorrecta?

a) Las funciones no pueden llevar argumentos, sólo devuelven valores

b) Los procedimientos pueden llevar parámetros IN y OUT

c) Las funciones PL/SQl que están dentro de sentencias SQL no

pueden modificar datos en las tablas.

10 ¿Cual de estas sentencias es incorrecta?

a) La sobrecarga de los packages surge a partir del décimo

procedimiento

b) Los packages disponen de la opción de sobrecarga de

procedimientos y funciones

c) Para que no haya sobrcarga en un package debemos indicar en la

18. EVALUACIÓN
VERSIÓN 1 121
PL/SQL

cabecera el nivel de depuración

11 Un disparador ..

a) Permite argumentos

b) No permite sentencias transaccionales (Commit, rollback…)

c) Permite ejecutarse desde un procedimiento mediante la orden EXEC

TRIGGER nombre_de_trigger

12 Una tabla mutante es:

a) Una tabla que se altera cuando dos usuarios modifican los datos de
una tabla a la vez.

b) Una tabla que se está modificándose actualmente por una orden

DML debido a un trigger FOR_EACH_ROW

c) Un tabla que se modifica porque un usuario ejecuta un procedimiento

a la vez que otro ejecuta una sentencia DML

13 ¿Qué tipo de error puede dar lugar en un cursor?

a) NO_DATA_FOUND

b) TOO_MANY_ROWS

c) VALUE_ERROR

122 VERSIÓN 1
 PL/SQL

ANEXOS A CURSO PL/SQL

VERSIÓN 1 123
PL/SQL

ANEXO 1: 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.

Conceptos previos antes de conectarse a SQL*Plus:

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

1 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

124 VERSIÓN 1
 PL/SQL

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 inserció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'.

2 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
sobreescribe 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;

VERSIÓN 1 125
 PL/SQL

• / que ejecuta la sentencia sin visualizarla.

SQL*Plus también nos permite editar la sentencia SQL almacenada 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 Lnó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 Lmn 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.

126 VERSIÓN 1
 PL/SQL

SQL> define_editor=vi
SQL> edit

3 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

4 ALMACENAMIENTO DE FICHEROS DE COMANDOS

Un fichero de comandos es una secuencia de órdenes SQL y SQL*Plus almacenados

en un fichero del sistema operativo.

VERSIÓN 1 127
PL/SQL

• Comando SAVE: Almacena en un fichero en disco el contenido del búffer activo.

SAVE nombre_fichero [APPEND|CREATE|REPLACE]

• Comando GET: Para recuperar un fichero de comandos.

GET nombre_fichero [LIST|NOLIST]

• START: Ejecuta el fichero del sistema operativo que se indique, admite hasta
nueve parámetros posicionales indicados en la línea de comandos (los parámetros
se definen como un número entero comprendidos entre 1 y 9).

START nombre_fichero [parámetro1, parámetro2, …]

• DESCRIBE o DESC: Este comando da una breve descripción de las

características de cada columna de la tabla indicada. Por cada columna se
muestra el nombre, si se admiten o no valores nulos, tipos de datos, ancho de
columna.

DESCRIBE nombre_tabla

• HOST: Pasa al servidor para su ejecución la orden escrita a continuación y ejecuta

la orden sin que el usuario abandone su sesión de SQL*Plus.

HOST orden

• SPOOL: Envía los resultados de una orden SQL a un fichero para imprimirlas
posteriormente.

SPOOL [fichero|OFF|OUT] ON

• EDIT: Llama al editor activo del sistema operativo.

• Variables de sustitución: Es una variable definida por el usuario.

&nombre_variable

• Cuando SQL*Plus encuentra una variable de sustitución en un comando lo

ejecuta como si contuviese el valor de esa variable, y si no se ha definido su
valor, pregunta el valor que se le desea dar. Si se desea qué sólo pregunte una

128 VERSIÓN 1
 PL/SQL

vez por su valor en una variable que aparece varias veces se la marcará con
&&.

5 FICHEROS DE COMANDOS

En un fichero de comandos se pueden incluir:

• líneas de comentarios,
• líneas de ejecución,
• líneas de comandos SQL, y
• líneas de comandos SQL*Plus.

LÍNEAS DE COMENTARIOS

Se pueden introducir comentarios en una archivo de comandos de tres maneras:

• Utilizando del comando REM del SQL*Plus.

• Utilizando los delimitadores de comentario de SQL /* y */.
• Utilizando los símbolos de comentario PL/SQL "__".

LÍNEAS DE EJECUCIÓN

Constan de una única barra inclinada, "/", y se introducen a continuación de cada

sentencia SQL indicando su ejecución.

Sustituyen al punto y coma, ";" al final de las sentencias SQL.

LÍNEAS DE COMANDOS SQL

Se puede introducir cualquiera de los comandos SQL enumerados en este curso, y se

ejecutarán de manera secuencial.

VERSIÓN 1 129
PL/SQL

Se permite el anidamiento de los ficheros de comandos.

LÍNEAS DE COMANDOS SQL*PLUS

SQL*Plus aporta una serie de posibilidades al lenguaje SQL que le acerca un poco
mas a lo que entendemos como un lenguaje de programación.

Se pueden definir constantes y variables, capturar datos del teclado, introducir

parámetros en la llamada de un archivo de comandos, y alguna cosa más.

Variables de Usuario

Se pueden definir Variables de usuario con el comando DEFINE

DEFINE Variable = valor

Para borrar una variable se utiliza el comando UNDEFINE

UNDEFINE variable

Como ejemplo se puede definir la variable OFICIO

SQL> define oficio=analista

Variables de Sustitución

Las variables de sustitución son un nombre de variable de usuario con el símbolo &
delante. Cuando SQL*Plus detecta una variable de sustitución en un comando, ejecuta
el comando tomando el valor de la variable.

Esto se puede ver en el ejemplo, donde preguntamos por los empleados que son
analistas:

SQL> define oficio=Analista

SQL> define tabla=emp
SQL> select nombre, oficio from &tabla where oficio='&oficio';
old 1: select nombre, oficio from &tabla where oficio='&oficio'
new 1: select nombre, oficio from emp where oficio='Analista'

130 VERSIÓN 1
 PL/SQL

NOMBRE OFICIO
---------- -----------
Sastre Analista
Recio Analista

Captura de Datos desde el Terminal

En muchas ocasiones es necesario recoger datos desde un terminal, que luego serán
utilizados en el archivo de comandos. Para realizarlo se pueden utilizar dos medios:
las variables de sustitución o los parámetros en la línea de comandos.

Cuando SQL*Plus reconoce una variable de sustitución sin valor asignado se lo pide al
usuario:

SQL> select * from dep where nombre='&nombredep';

Enter value for nombredep: I+D
old 1: select * from dep where nombre='&nombredep'
new 1: select * from dep where nombre='I+D'

COD_DEP NOMBRE LOC

---------- --------------- ----------
200 I+D Boecillo

Si se desea que SQL*Plus pregunte por el valor de la variable al usuario sólo la

primera vez que se encuentra con ella, se colocará "&&" delante de la variable de
usuario.

También se pueden utilizar hasta nueve parámetros en la línea de comandos cuando

se llama a la ejecución de un archivo de comandos.

En el archivo de comandos nos referiremos a los parámetros con las variables &1, &2,
... ,&9 que se corresponden posicionalmente con ellos.

VERSIÓN 1 131
PL/SQL

Desde el archivo de comandos se puede hacer referencia a los parámetros cualquier

número de veces y en cualquier orden.

Comandos de Comunicación con el Usuario

Los siguientes comandos proporcionan un medio de comunicación con el usuario:

• PROMPT: presenta un mensaje en la pantalla.

• ACCEPT: Solicita un valor y lo almacena en la variable de usuario que se

especifique.

• PAUSE: Obliga al usuario a pulsar Return después de leer un mensaje.

Para ver cómo funcionan sirve el siguiente ejemplo:

prompt Buscar los datos de un empleado.

pause Pulse Return.
accept nombremp prompt 'Empleado? '
select * from emp where nombre='&nombremp';

Otros Comandos

Los siguientes comandos también se pueden incluir en un archivo de comandos:

• CONNECT: para conectarse como otro usuario.

• HELP: para obtener ayuda en línea.

• EXIT: para dejar SQL*PLus y salir al Sistema Operativo.

• DESCRIBE ó DESC: para obtener información sobre la estructura de una tabla.

• HOST o !: para ejecutar un comando del Sistema Operativo.

6 GENERACIÓN DE INFORMES

Con SQL*Plus podemos dar forma a los resultados de las consultas para producir un
informe. Podremos:

132 VERSIÓN 1
 PL/SQL

• Cambiar las cabeceras de las columnas.

• Dar forma a las columnas de tipo number, varchar2, date y long.

• Copiar y listar atributos de presentación de las columnas.

• Suprimir valores duplicados e introducir espacios para mejorar la presentación.

• Realizar y mostrar cálculos (totales, medias, mínimos, máximos, etc.).

• Definir las dimensiones de las páginas.

• Ubicar títulos en la cabecera y pie de las páginas.

• Introducir la fecha o el número de página en los títulos.

Básicamente, el formato con el que se van a presentar los resultados de las consultas
dependen de unos parámetros y de unos comandos.

PARÁMETROS

• SET LINESIZE: pone el número máximo de caracteres por línea. Por defecto
vale 80 y el máximo es 999.

• SET PAGESIZE: pone el número de filas de la salida antes de empezar una

nueva página. Por defecto es 25. Incluye el título y las líneas de pausa.

• SET HEADING [ON | OFF]: Activa/desactiva la utilización de encabezados de

columnas. Por defecto está activado.

• SET NULL texto: Indica la cadena de caracteres que hay que colocar en
sustitución de los valores NULL. Por defecto es "".

• SET ECHO [ON | OFF]: Activa/desactiva la visualización de los comandos que

SQL*Plus ejecuta según van siendo tratados. Por defecto está desactivada.

• SET FEEDBACK [ n | ON | OFF]: Muestra el número de registros recuperados

en cada consulta cuando se recuperan n o más registros. ON se pueden
considerar como n=1, y OFF como n=0.

VERSIÓN 1 133
PL/SQL

• SET VERIFY [ON | OFF]: Controla la salida de confirmación para los valores de
las variables de sustitución. Por defecto está activado.

COMANDOS

• TTITLE: formación del encabezado de página.

• BTITLE: formación del pie de página.

• COLUMN: formatear cada columna.

• BREAK: puntos de ruptura en los listados.

• COMPUTE: realizar cálculos con las columnas.

134 VERSIÓN 1
 PL/SQL

ANEXO 2: SQL DE ORACLE

19. DQL. DATA QUERY LANGUAGE.

SINTAXIS GENERAL DE SENTENCIA SELECT.

La sentencia SELECT recupera datos de una base de datos y los devuelve en forma
de resultados de la consulta. Conviene diferenciar de forma clara la cláusula y la
sentencia SELECT. Mientras que la cláusula solamente específica los columnas que
se quieren listar la sentencia engloba a toda la sintaxis que tiene como objetivo
recuperar ciertos datos.

SELECT lista de ítems separados por comas

FROM lista de tablas separadas por comas

(o/y):

WHERE condiciones

GROUP BY criterio de agrupamiento [ DESC | ASC]

ORDER BY criterio de ordenación

HAVING criterio de selección por grupos

La cláusula SELECT específica los ítems que se desea recuperar, que puede ser.

• Nombre de columna.

VERSIÓN 1 135
PL/SQL

• Una constante.

• Expresiones que calcula SQL.

El especificador * como sustituto de la lista de ítems separados por comas indica todas
las columnas. ORACLE no permite combinaciones con * y expresiones pero en otros
productos esto es posible.

La cláusula FROM indica la tabla o las tablas de las que se toman los datos
solicitados. A dichas tablas se las llama tablas fuentes de las consultas.

La cláusula WHERE dice a SQL que incluya solo ciertas filas de datos en los
resultados de la consulta.

La cláusula GROUP BY específica la consulta sumaria. En vez de producir una fila de

resultados por cada fila de datos de la base de datos, una consulta sumaria agrupa
todas las filas similares y luego produce una fila sumaria de resultados para cada
grupo.

La cláusula ORDER BY ordena los resultados de la consulta en base a los datos de

uno o más ítems. Si se omite, los resultados de la consulta no aparecen ordenados.

La cláusula HAVING dice a SQL que incluya sólo ciertos grupos producidos por la
cláusula GROUP BY en los resultados de las consultas.

La estructura tabular hace que los datos sean tablas al igual que las consultas.

Se entiende por columna calculada la que se genera a partir de otras columnas o

constantes por medio de expresiones de SQL.

Precediendo a la lista de ítems en la cláusula SELECT puede aparecer el

especificador DISTINCT que indica la eliminación de las filas duplicadas.

SQL> SELECT DISTINCT job FROM emp;

136 VERSIÓN 1
 PL/SQL

LISTA DE CAMPOS A RECUPERAR (SELECT)

La cláusula SELECT va seguida de una lista de campos a recuperar. Estos campos

pueden pertenecer a una/varias tablas y/o vistas. Las posibilidades de declaración de
estos campos se recogen en el siguiente esquema:

<columna> ["<alias_columna>']

<tabla>.<columna>["<alias~columna>"]

<alias_tabla>.<columna>["<alias_columna>"]

<usuario>.<tabla>.<columna>["<alias-columna>"]

donde:

<columna> Nombre de la columna de la que queremos obtener valores.

<tabla> Nombre de la tabla a la que pertenece la columna.

<alias_tabla> Alias dado a la tabla.

<usuario> Nombre del usuario de Base de Datos al que pertenece la tabla.

<alias columna> Alias de la columna.

SQL> SELECT d.deptno , d.dname Departamento, d.loc

2 FROM dept d;

Además de una lista de campos simple, la lista de campos SELECT se puede "ampliar'
introduciendo funciones u "operadores". Pasamos a exponer la lista de funciones
permitidas así como los operadores. En general, las funciones permitidas son
funciones columna y el operador al que nos referimos es DISTINCT.

FUNCIONES COLUMNA

VERSIÓN 1 137
PL/SQL

Una función columna toma a una columna como argumento y produce un único dato
que sumariza la columna. SQL ofrece seis funciones de columna diferentes:

• SUM() calcula el total de una columna.

• AVG() calcula el valor promedio de una columna.

• MIN() encuentra el valor más pequeño de una columna.

• MAX() encuentra el valor mayor de una columna.

• COUNT() cuenta el numero de valores de una columna

• COUNT(*) cuenta el número de filas de una columna.

CLAUSULA FROM

La cláusula FROM permite especificar de dónde vamos a obtener los diferentes

resultados. En la lista FROM podremos incluir nombres de tablas, sinónimos de las
mismas o nombres de vistas En general, la lista que prosigue a FROM es
<tabla>[<alias_tabla>] donde:

<tabla> Nombre de la tabla, sinónimo o view

<alias tabla> Alias dado a la tabla; sinónimo o vista por el cual podemos
referirnos a la misma en la sentencia SQL

SQL> SELECT prodid, descrip

2 FROM product;

138 VERSIÓN 1
 PL/SQL

SELECCIÓN DE FILA (WHERE)

La cláusula WHERE consiste en la palabra clave WHERE seguida de una condición

de búsqueda. Si la condición es cierta la fila se incluye, si es falsa se excluye y si es
NULL se excluye.

CONDICIONES DE BÚSQUEDA SIMPLE

TEST DE COMPARACIÓN

Compara el valor de una expresión con otra; para lo cual utiliza los siguientes símbolos
de comparación:

=, <, >, <=, >= y <> (también != y ¬=)

Si la comparación es cierta, el test produce como resultado TRUE y por tanto la fila
será incluida. Si por el contrario es falsa producirá un FALSE y será excluida al igual
que si alguno de los términos de la comparación es NULL.

WHERE expresión símbolo expresión

SQL> SELECT prodid, descrip

2 FROM product

3 WHERE prodid = 101860;

Si alguna de las columnas ha sido definida a la hora de crear la tabla como clave
primaria esto implica que no puede haber valores duplicados en dicha columna con lo
cual esta servirá para diferenciar unívocamente cada fila.

TEST DE RANGOS (BETWEEN)

VERSIÓN 1 139
PL/SQL

El test de rangos comprueba si un valor de dato se encuentra entre las dos

expresiones especificadas. Between incluye los puntos extremos del rango.

WHERE expresion BETWEEN extremo1 AND extremo2

SQL> SELECT ename, job

2 FROM emp

3 WHERE sal BETWEEN 100000 AND 200000;

PERTENENCIA A UN CONJUNTO

Examina si la expresión coincide con algún dato de la lista de valores. Si la expresión

es NULL devuelve NULL.

WHERE expresión IN (lista de valores)

WHERE expresión NOT IN (lista de valores)

SQL> SELECT *

2 FROM emp

3 WHERE job IN ( 'ANALYST', 'MANAGER');

TEST DE CORRESPONDENCIA CON UN PATRÓN.

Compara la expresión con otra obtenida partir de comodines, el patrón. Se utiliza el %

como carácter comodín para indicar el patrón “cualquier cadena de caracteres” y el
carácter _ como patrón “cualquier carácter simple”. Devuelve NULL si la expresión es
NULL.

140 VERSIÓN 1
 PL/SQL

WHERE expresión LIKE patrón

SQL> SELECT ename, sal

2 FROM emp

3 WHERE ename LIKE 'J%';

SQL> SELECT ename

2 FROM emp

3 WHERE ename LIKE '_A%';

Dado que los caracteres _ y % tienen significados especiales, para hacer referencia a
ellos literalmente se puede definir para cada LIKE un carácter de escape añadiendo al
final la cláusula ESCAPE 'char'. Cuando en la expresión aparece el carácter char el
carácter que le precede se considera de forma literal.

SQL> SELECT table_name

2 FROM user_tables

3 WHERE table_name LIKE '_\_%' ESCAPE ‘\’;

TEST DE VALOR NULO

Chequea si la expresión en NULL o si no lo es.

WHERE expresión IS NULL

WHERE expresión IS NOT NULL

VERSIÓN 1 141
PL/SQL

SQL> SELECT ename

2 FROM emp WHERE mgr IS NULL;

CONDICIONES DE BÚSQUEDA COMPUESTAS.

Las condiciones de búsqueda simples se pueden combinar con OR, AND y NOT
originando lo que se llaman condiciones de búsqueda compuestas.

SQL> SELECT *

2 FROM cities

3 WHERE population<200000

4 AND state = ‘CA’;

SQL> SELECT *

2 FROM cities

3 WHERE population BETWEEN 500000 AND 1000000

4 OR state = ‘TX’;

ORDENACIÓN DE RESULTADOS DE UNA CONSULTA (ORDER BY)

ORDER BY lista de items [DESC|ASC]

Por defecto ordena las filas seleccionadas en orden ascendente considerando el orden
léxico - gráfico que genera la lista de ítems. Con DESC este orden se hace
descendente y con ASC ascendente.

142 VERSIÓN 1
 PL/SQL

SQL> SELECT city, state, population

2 FROM cities

3 ORDER BY city DESC, population ASC;

Cuando se quiere ordenar utilizando alguna columna no propia de la tabla sino que se
ha generado a través de una expresión en la cláusula SELECT esta se puede indicar a
través de la posición que ocupa en la lista de Items de la cláusula SELECT.

SQL> SELECT prodid “producto”,

2 (stdprice-minprice) “diferencia de precios”

3 FROM price

4 ORDER BY 2 DESC;

MANIPULACIÓN DE DATOS DE CONJUNTOS (GROUP BY, HAVING)

Una consulta que incluya la cláusula GROUP BY se denomina consulta agrupada, ya

que agrupa los datos de las tablas fuente y produce una única fila sumaria por cada
grupo de filas. Los ítems indicados en la cláusula GROUP BY se denominan ítems o
columnas de agrupación de la consulta, ya que ellas son las que determinan como se
dividen las filas en grupos. Los grupos se forman con todas aquellas filas que tienen
los mismos valores en las columnas de agrupación.

SQL> SELECT state, sum(population)

2 FROM cities

3 GROUP BY state;

VERSIÓN 1 143
PL/SQL

La agrupación se puede hacer por medio de múltiples ítems.

SQL> SELECT symbol, company, MAX(number_traded_today)

2 FROM stocks

3 GROUP BY symbol, company;

Cuando el agrupamiento se efectúa a través de varios items los grupos se establecen

a través de las filas que contengan los mismos datos para cada ítem que figura en la
cláusula GROUP BY.

Hay que destacar que a NULL para los efectos de agrupamiento se le considera otro
valor más que constituiría otro grupo como sucede en el primer ejemplo aunque este
aspecto depende del producto SQL en cuestión.

En el SELECT de una columna agrupada pueden aparecer:

• Constantes.

• Funciones de columna.

• Cualquier columna que tenga el mismo dato para todas las filas que
integran un grupo.

• Una expresión que afecte a combinaciones de las anteriores.

La cláusula HAVING sirve para rechazar o seleccionar grupos de filas. Su formato es

análogo al de la cláusula WHERE consistiendo en la palabra clave HAVING seguida
de una condición de búsqueda.

SQL> SELECT sname, AVG(revenue)

2 FROM sales_revenue

3 GROUP BY sname

144 VERSIÓN 1
 PL/SQL

4 HAVING SUM(revenue) between 40000 and 50000;

La condición de búsqueda dentro de una cláusula HAVING puede ser:

• Una constante.

• Una función columna, que produzca un único valor que sumarice las filas del
grupo.

• Una columna de agrupación, que por definición tiene el mismo valor para todas
las filas del grupo.

• Una expresión combinación de las anteriores.

HAVING produce uno de estos tres resultados:

TRUE si la condición de búsqueda es cierta por lo cual se retiene el grupo de filas.

FALSE si la condición de búsqueda es falsa por lo cual se excluye el grupo de filas.

NULL si la condición de búsqueda en NULL por lo cual el grupo de filas se excluye.

Si en una sentencia SELECT se tiene una cláusula HAVING sin una cláusula GROUP
BY se considera que todas las filas forman un único grupo rechazando o aceptándole
en su totalidad.

RECUPERACIÓN JERÁRQUICA (CONNECT BY ... START WITH ...)

Esta es una característica específica del SQL proporcionado por ORACLE. Permite la
recuperación jerárquica de filas siguiendo una estructura tipo árbol. Las cláusulas
asociadas a este tipo de recuperación de datos son CONNECT BY...START WITH... y
van siempre a continuación de la cláusula WHERE.

SELECT * | expresion [tabla.]columna,...

VERSIÓN 1 145
PL/SQL

FROM [usuario.]tabla,...

WHERE predicado

CONNECT BY condición [START WITH predicado]

ORDER BY expresión [ASC|DESC],...;

El objetivo de cada una de las cláusulas componentes se puede indicar diciendo:

• WHERE: Es la cláusula que permite filtrar las filas

• CONNECT BY: Criterio que define la estructura del árbol

• START WITH: Identifica la raíz del árbol

SQL> SELECT level, ename

2 FROM emp

3 CONNECT BY PRIOR emp_no = mgr

4 START WITH job = ‘PRESIDENT’;

EXPRESIONES SENTENCIAS SELECT (UNION, INTERSECT, MINUS)

Entendemos expresiones con sentencias SELECT la "reunión" de un conjunto de

sentencias SELECT unidas por medio de ciertos operadores. Los operadores
disponibles para realizar esta tarea son tres: UNION, INTERSECT, MINUS.

<selección_sin_ORDER_BY>

UNION|INTERSECT|MINUS

<seleccion sin ORDER_BY>

146 VERSIÓN 1
 PL/SQL

ORDER BY <predicado>.

Existen unas consideraciones a tener en cuenta antes de utilizar estas sentencias:

• Rendimiento muy bajo.

• No utiliza índices.

• Se evalúa de izquierda a derecha.

• No existe jerarquía en el uso de estos operadores; se puede forzar con

paréntesis.

UNION

Se pueden reunir varias sentencias select con el fin de seleccionar las filas que cada
una de las sentencias considera. Esto se hace gracias al comando UNION.

sentencia SELECT 1

UNION

sentencia SELECT 2

UNION

...

sentencia SELECT n

Sin embargo, hay restricciones que limitan este uso:

• Mismo número de ítems en cada SELECT.

• Mismo tipo de datos.

VERSIÓN 1 147
PL/SQL

• Los SELECT no pueden tener la cláusula ORDER BY.

• El ANSI/ISO establece que los ítems solo pueden ser columnas pero los
productos SQL suelen relajar esta restricción.

Las columnas de resultados no tienen títulos e incluso algunos productos SQL no

soportan UNION. Por omisión, UNION no selecciona las filas duplicadas para evitarlo
hay que utilizar UNION ALL. El orden de las uniones es indiferente siempre que todas
se hagan de la forma UNION o UNION ALL pero si se mezclan pueden dar resultados
distintos.

INTERSECT

sentencia SELECT 1

INTERSECT

sentencia SELECT 2

INTERSECT

...

sentencia SELECT n

El método de acceso a los registros que ha de encontrar es el siguiente: examina los

registros de los conjuntos de entrada (cada uno de los conjuntos de datos obtenidos
con cada sentencia SELECT) y devuelve aquellas que aparezcan en ambas. Además,
todas las filas duplicadas serán eliminadas antes de la generación del conjunto
resultante.

SQL> SELECT deptno FROM dept

2 INTERSECT

3 SELECT deptno FROM emp;

148 VERSIÓN 1
 PL/SQL

MINUS

sentencia SELECT 1

MINUS

sentencia SELECT 2

MINUS

...

sentencia SELECT n

Este tipo de combinación de sentencias SELECT devuelve aquellas filas que están en
el primer conjunto (datos obtenidos y vía la primera sentencia SELECT) pero no en el
segundo. Las filas duplicadas del primer conjunto se reducirán a una fila única antes
de que empiece la comparación con el otro conjunto.

SQL> SELECT deptno FROM dept

2 MINUS

3 SELECT deptno FROM emp;

CONSULTAS MULTITABLA (COMPOSICIONES-COMBINACIONES)

Se llama componer tablas (join) al proceso de formar parejas de filas que se

caracterizan por tener común el contenido de las columnas relacionadas. La tabla
resultante se llama composición.

WHERE columnal=columna2

VERSIÓN 1 149
PL/SQL

SQL no requiere que las columnas de emparejamiento sean incluidas en los

resultados de una consulta multitabla.

SQL> SELECT ename, dname

2 FROM emp, dept

3 WHERE emp.deptno = dept.deptno;

En este ejemplo where EMP.DEPTNO=DEPT.DEPTNO establece una relación entre

la columna DEPTNO de la tabla EMP y la columna DEPTNO de la tabla DEPT. Con
esto para cada ENAME busca su DEPTNO en la misma tabla EMP y le relaciona con
el DNAME de la fila de la tabla DEPT cuyo valor de DEPTNO es el mismo. Cuando
hay un NULL en la columna de conexión la fila que le contiene se excluye.

En este ejemplo la conexión se realiza a través de una relación simple pero esta
puede ser múltiple utilizando AND con el WHERE. En este caso la conexión se
establecería si se verificaran todas las relaciones.

Generalmente la columna relacionada en la segunda tabla es una clave primaria con lo

cual se evita que haya una conexión de una fila de la primera tabla con varias de la
segunda.

Hasta ahora hemos tratado relaciones que se basaban en la igualdad de los datos de
dos columnas de dos tablas pero esto no tiene por que ser necesariamente así. La
relación la puedo establecer con los siguientes símbolos: =, <, >, <>, <=, >=.

SQL> SELECT a.ssn, f.fund_id, a.pct_deferral, f.pct_dist

2 FROM accts a, fund_contrib f

3 WHERE a.ssn = f.ssn

4 AND a.pct_deferral < f.pct_dist;

Dado que puede haber nombres de columnas iguales aunque en tablas diferentes es
recomendable para evitar ambigüedades el uso de columnas cualificadas.

150 VERSIÓN 1
 PL/SQL

Se pueden establecer también relaciones entre columnas de una misma tabla de la

misma forma que se establecen entre tablas distintas. Para ello SQL utiliza la
estrategia de tabla duplicada o sea duplica la tabla original y lo establece como si se
tratara de tablas distintas.

SQL> SELECT e.ename empleado, e2.ename jefe

2 FROM emp e, emp e2

3 WHERE e.mgr = e2.empno;

La palabra que precede al nombre de las tablas en la cláusula FROM es el ALIAS de

dicha tabla utilizado para diferenciarla de su copia que obviamente tendría si no es por
el ALIAS tendría el mismo nombre. Si no se utiliza el ALIAS no es necesario repetir
columnas en el FROM.

Cuando la relación se establece por un valor NULL la fila o filas resultantes se

desprecian. A este tipo de emparejamiento se llama externo. Para forzar el
emparejamiento de NULL se utiliza el símbolo *=* en la cláusula WHERE. También se
pueden utilizar los seis símbolos de comparación en lugar del =. Hay que destacar que
esta no es una especificación ANSI/ISO ya que el estándar no trata este caso.

ORACLE utiliza (+) detrás de las columnas que se emparejan.

SQL> SELECT e.ename nombre, e2.ename jefe

2 FROM emp e, emp e2

3 WHERE e.mgr = e2.empno(+);

SUBCONSULTAS.

La característica de subconsulta de SQL permite utilizar los resultados de una consulta

como parte de otra. Esta característica hace que a SQL se le considere un lenguaje

VERSIÓN 1 151
PL/SQL

estructurado. Hay consultas en SQL que no se pueden expresar sino es utilizando una
subconsulta.

Una subconsulta es simplemente una consulta que aparece dentro del WHERE o
HAVING de otra sentencia SQL encerrada entre paréntesis.

Las restricciones que deben verificar las subconsultas son:

• Una subconsulta debe producir una única columna de datos como resultado.
Por lo cual la cláusula SELECT de la subconsulta debe de tener un único ítem.

• Una subconsulta no debe incluir la cláusula ORDER BY.

• Una subconsulta no puede estar integrada por UNION.

Una subconsulta forma parte siempre de una condición de búsqueda en la cláusula

WHERE o HAVING. SQL ofrece estas condiciones de búsqueda en subconsultas.

TEST DE COMPARACIÓN DE SUBCONSULTA.

Es una forma modificada de test de comparación simple. Compara el valor de una

expresión con el valor producido por una subconsulta, y devuelve un resultado TRUE
si la comparación es cierta. La subconsulta debe de producir un valor único en caso
contrarío SQL informa del error. Si la subconsulta no devuelve ningún valor o produce
un NULL, el test de comparación devuelve NULL.

SQL> SELECT ename

2 FROM emp

3 WHERE sal >= (SELECT min(sal)

4 FROM emp

5 WHERE deptno = 30);

152 VERSIÓN 1
 PL/SQL

El test de comparación subconsulta ofrece los mismos seis operadores de

comparación (=, <, >, <=, >=, <>).

TEST DE PERTENENCIA A UN CONJUNTO.

Compara un único valor de datos con una columna de valores producida por una
subconsulta y devuelve un resultado TRUE si el valor coincide con uno de los valores
de la columna. Utiliza el operador IN.

SQL> SELECT ename

2 FROM emp

3 WHERE deptno IN (SELECT deptno

4 FROM dept

5 WHERE loc=’MADRID’);

TEST DE EXISTENCIA.

El test de existencia (EXISTS o NOT EXISTS) comprueba si una subconsulta produce

alguna fila de resultados o no.

SQL> SELECT dname

2 FROM dept

3 WHERE EXISTS ( SELECT deptno

4 FROM emp);

VERSIÓN 1 153
PL/SQL

TESTS CUALIFICADOS

EL TEST ANY

Se utiliza conjuntamente con los seis operadores de comparación para comparar un

único valor de test con una columna de valores producidos por una subconsulta. Para
efectuar la comparación se utiliza el operador de comparación especificado. Si alguna
de las comparaciones individuales produce un resultado TRUE, el test ANY devuelve
un resultado TRUE.

SQL> SELECT *

2 FROM cities

3 WHERE population < ANY ( SELECT population

4 FROM cities

5 WHERE state = ‘CA’);

Sí la subconsulta produce una columna vacía de resultados el test ANY devuelve

FALSE. Si el test de comparación no es TRUE para ningún valor de datos en la
columna pero es NULL para uno o más de los valores, entonces la condición de
búsqueda ANY devuelve NULL.

EL TEST ALL

La sintaxis es igual que el test ANY aunque la palabra clave utilizada es ALL. Si todas
las comparaciones individuales dan como resultado TRUE el test da como resultado
TRUE.

SQL> SELECT *

2 FROM cities

3 WHERE population < ALL ( SELECT population

154 VERSIÓN 1
 PL/SQL

4 FROM cities

5 WHERE state = ‘TX’);

Si la subconsulta produce una columna vacía de resultados el test ALL devuelve

TRUE. Si el test de comparación no es FALSE para ningún valor de datos en la
columna, pero es NULL para uno o más de ellos, entonces la condición de búsqueda
ALL devuelve NULL.

ANIDAMIENTO DE SUBCONSULTAS

Se pueden anidar subconsultas dentro de subconsultas sin ningún tipo de restricción

salvo en numero de niveles de anidamiento de límite el fabricante del producto SQL, el
inconveniente de su utilización radica en el excesivo tiempo necesario para realizarlas.

SQL> SELECT *

2 FROM customer

3 WHERE city NOT IN (SELECT city

4 FROM cities

5 WHERE population < ANY ( SELECT c.population

6 FROM cities c, usa u

7 WHERE c.state = u.state

8 AND u.data > 10000000));

VERSIÓN 1 155
PL/SQL

SUBCONSULTAS CORRELACIONADAS

Conceptualmente, SQL efectúa una subconsulta una y otra vez, una por cada fila de la
consulta principal. Algunos productos detectan esta circunstancia optimizando así el
tiempo de este tipo de consultas.

RESUMEN FINAL DE LA ORDEN SELECT

1. Si la sentencia es una UNION de sentencias select, aplicar los pasos 2 hasta 7 a

cada una de las sentencias, para generar sus resultados de consulta individuales.

2. Forma el producto de las tablas indicadas en la cláusula FROM. Si la cláusula

FROM designa una única tabla, el producto es esa tabla.

3. Si hay una cláusula WHERE, aplicar su condición de búsqueda a cada fila de la

tabla producto, reteniendo aquellas filas para las cuales la condición de búsqueda
es TRUE (y descartando aquellas para las cuales es FALSE o NULL). Si la
cláusula WHERE contiene una subconsulta, la subcosulta se efectúa por cada fila
conforme es examinada.

4. Si hay una cláusula GROUP BY, disponer las filas restantes de la tabla producto
en grupos de filas, de modo que las filas de cada grupo tengan valores idénticos
en todas las columnas de agrupación.

5. Si hay una cláusula HAVING, aplicar su condición de búsqueda a cada grupo de

filas, reteniendo aquellos grupos para los cuales la condición de búsqueda es
TRUE (y descartando aquellas para las cuales es FALSE o NULL). Si la cláusula
HAVING contiene una subconsulta, la subconsulta se efectúa para cada grupo de
filas conforme es examinada.

6. Para cada fila (o grupo de filas) restante, calcula el valor de cada elemento en la
lista de selección para producir una única fila de resultados. Para una referencia
de columna simple, utilizar el valor de la columna en la fila (o grupo de filas) actual.
Para una función de columna, utilizar como argumento el grupo de filas actual si se
especifico GROUP BY; en caso contrario, utilizar el conjunto entero de filas.

156 VERSIÓN 1
 PL/SQL

7. Si hubiera SELECT DISTINCT, eliminar las filas duplicadas de los resultados que
se hubieran producido.

8. Si la sentencia es una UNION de sentencias SELECT, mezclar los resultados de

consulta para las sentencias individuales en una única tabla de resultados.
Eliminar las filas duplicadas a menos que se haya especificado UNION ALL.

9. Si hay una cláusula ORDER BY, ordenar los resultados de la consulta según se
haya especificado.

Las filas generadas por este procedimiento forman los resultados de la consulta.

VERSIÓN 1 157
PL/SQL

20. DML: DATA MANIPULATION LANGUAGE.

Las sentencias de SQL que modifican los contenidos de una base de datos son
sencillas, sin embargo presentan algunas complejidades al sistema de gestión de
bases de datos como son:

• Proteger la integridad de los datos almacenados, durante los cambios,

asegurándose que sólo se introduzcan datos válidos y que la base de datos
permanezca autoconsistente, incluso en caso de fallos del sistema.

• Coordinar las actualizaciones simultaneas por parte de múltiples usuarios,

asegurándose que los usuarios y sus modificaciones no interfieran unos con otros.

ADICCIÓN DE DATOS A LA BASE DE DATOS (INSERT)

SENTENCIA INSERT DE UNA FILA

INSERT INTO nombre-de-tabla (nombre-de-columna, ...)

VALUES (constante-o-NULL, ...)

Donde nombre-de-tabla especifica la tabla que recibe la nueva fila, a continuación

(entre paréntesis) se especifica una lista de nombres de columna de esa tabla, y
después tras la palabra VALUES los valores que se dan a cada una de esas
columnas, en el mismo orden que la lista de nombres de columnas.

INSERT INTO libro (titulo, autor, signatura)

VALUES ('APLIQUE SQL', 'JAMES R.GROFF', 10234);

158 VERSIÓN 1
 PL/SQL

Por supuesto los valores de los datos tras la cláusula VALUES deben ser válidos
según el tipo de datos de la columna que les corresponde.

Como podemos apreciar no es necesario asignar valores a todas las columnas de la

nueva fila, cuando SQL inserta una nueva fila de datos en una tabla, automáticamente
asigna un valor NULL a cualquier columna cuyo nombre falte de la lista de columnas
especificada.

SQL permite omitir la lista de columnas de la sentencia INSERT, entonces SQL genera
automáticamente una lista formada por todas las columnas de la tabla, en secuencia
de izquierda a derecha (es la misma secuencia de columnas que la generada por un
SELECT *).

Este tipo de actualizaciones se realizan habitualmente en aplicaciones diarias y suelen

hacerse mediante un programa de formularios, no obstante el formato de la sentencia
INSERT utilizando SQL interactivo o programado es la misma.

Nota: SQL requiere que las filas sean tratadas como conjuntos en operaciones de
inserción, supresión y actualización, con lo que no existe la noción de insertar la fila 'al
comienzo', 'al final' o 'entre dos filas' de la tabla, después de ejecutar la sentencia
INSERT la nueva fila simplemente forma parte del conjunto de la tabla.

SENTENCIA INSERT MULTIFILA

INSERT INTO nombre-de-tabla (nombre-de-columna, ...) consulta

Ahora INSERT añade múltiples filas de datos a la tabla destino, los valores de datos
para las nuevas filas no son especificados explícitamente dentro del texto de la
sentencia, sino que la fuente de las nuevas filas es una consulta de base de datos
especificada en la sentencia. Así pues los nuevos datos se obtienen de la propia base
de datos.

INSERT INTO libros_prestados_este_mes (titulo, autor. signatura)

VERSIÓN 1 159
PL/SQL

SELECT titulo, autor, signatura

FROM libros

WHERE fecha_prestamo >'01-ENE-90';

Conceptualmente esta sentencia SQL se efectúa así:

• SQL realiza primero la consulta SELECT sobre la tabla LIBRO.

• Luego inserta los resultados de esa consulta, fila a fila, en la tabla

libros_prestados_este_mes.

Sí se van a realizar varias consultas a distintas tablas grandes, puede también ser útil
el uso de la sentencia INSERT multifila para coger en una tabla distinta las filas de
esas tablas con las que se va a trabajar y sobre ella realizar las consultas.

Notas: El estándar SQL ANSI/ISO especifica varias restricciones sobre la consulta que
aparece dentro de la sentencia INSERT multifila:

• La consulta no puede contener una cláusula ORDER BY.

• La consulta no puede ser la UNION de varias sentencias SELECT

diferentes. Sólo puede especificarse una única sentencia SELECT ¿?.

• La tabla destino de la sentencia INSERT no puede aparecer en la cláusula

FROM de la consulta o de ninguna subconsulta que esta contenga. Esto
prohibe insertar parte de la tabla en sí misma.

UTILIDADES DE CARGA MASIVA

Los datos a insertar en una base de datos son frecuentemente extraídos de otro
sistema informático o recolectados de otros lugares y almacenados en archivos
secuenciales. Para cargar los datos en una tabla, se podría escribir un programa que
leyera cada registro del archivo y utilizara la sentencia INSERT de una fila para añadir
la fila a la tabla. Sin embargo, el recargo de hacer que el RDBMS ejecute
repetidamente sentencias INSERT de una fila puede ser bastante alto, por esta razón

160 VERSIÓN 1
 PL/SQL

todos los productos RDBMS comerciales incluyen una capacidad de carga masiva que
carga los datos desde un archivo a una tabla a alta velocidad.

Nota: El estándar SQL ANSI/ISO no considera esta función, y suele ser suministrada
como un programa de utilidad autónomo, en lugar de formar parte del lenguaje SQL.

BORRADO DE DATOS DE LA BASE DE DATOS (DELETE).

DELETE FROM nombre-de-tabla WHERE condición-de-busqueda

La cláusula FROM especifica la tabla destino que contiene las filas, la cláusula
WHERE especifica que filas de la tabla van a ser suprimidas.

Las condiciones de búsqueda que pueden especificarse en la cláusula WHERE de la

sentencia DELETE son las mismas disponibles en la cláusula WHERE de la sentencia
SELECT.

Conceptualmente SQL aplica la cláusula WHERE a cada una de las filas de la tabla
especificada, suprimiendo aquellas para las cuales la condición de búsqueda produce
un resultado TRUE.

Supongamos que queremos suprimir de nuestra tabla de libros el libro "El péndulo de
Foucault", cuya signatura es 10234 para ello hacemos:

DELETE FROM libros

WHERE signatura = 10234;

La cláusula WHERE es opcional en una sentencia DELETE, si se omite se suprimirán

todas las filas de la tabla destino. La tabla aún existe, pero está vacía.

VERSIÓN 1 161
PL/SQL

A veces la selección de las filas a suprimir debe efectuarse en base a datos

contenidos en otras tablas. El modo de manejar una petición de este tipo es una
condición de búsqueda subconsulta.

DELETE FROM libros

WHERE autor = (SELECT nombre

FROM escritores

WHERE edad>80);

En el ejemplo suprimimos de la tabla de LIBROS las filas correspondientes a libros

cuyo autor tiene más de 8O años.

Las subconsultas pueden anidarse unas sobre otras y pueden contener referencias
externas a la tabla destino de la sentencia DELETE, con la única limitación (en una
sentencia DELETE) de que la tabla destino no puede aparecer en la cláusula FROM
de una subconsulta ni en ninguna de sus subconsultas anidadas.

MODIFICACIÓN DE DATOS EN LA BASE DE DATOS (UPDATE)

UPDATE nombre-de-tabla

SET nombre-de-columna = expresión,

...

WHERE condición~de~busqueda

O bien:

UPDATE nombre-tabla

SET nombre-columna = (consulta),

162 VERSIÓN 1
 PL/SQL

...

WHERE condición-de-busqueda

La cláusula WHERE es opcional, si se omite se actualizan todas las filas de la tabla.

Para el segundo formato, en el que aparece una subconsulta en la cláusula SET, esta
debe devolver una única fila por cada fila que se va a actualizar.

La cláusula SET especifica que columnas se van a actualizar y calcula los nuevos
valores, y la cláusula WHERE selecciona las filas de la tabla a modificar.

UPDATE libros

SET precio = 1 .12 * precio;

WHERE EDITORIAL = 'Acme Editorial'

En el ejemplo indicamos que vamos a actualizar la tabla LIBROS, cambiando la

columna PRECIO de aquellas filas en las que la columna EDITORIAL tiene un valor
'Acme Editorial'. El cambio en la columna PRECIO es aumentar su valor en un 12%.

Si una expresión en la lista de asignación referencia a una de las columnas de la tabla

destino, el valor utilizado para calcular la expresión es el valor de esa columna en la
fila actual antes de que se aplique ninguna actualización, lo mismo es aplicable a las
referencias de columna que se producen en la cláusula WHERE. Veámoslo con un

UPDATE oficinas

SET cuota = 40000.00, ventas = cuota

WHERE cuota < 40000.00;

VERSIÓN 1 163
PL/SQL

Aquí WHERE selecciona sólo aquellas OFICINAS cuya columna CUOTA tenía un
valor menor de 40000 antes de la sentencia UPDATE y la columna VENTAS toma el
valor de la columna CUOTA también antes del UPDATE.

La cláusula WHERE en la sentencia UPDATE es opcional, si se omite entonces se

actualizan todas las filas de la tabla destino.

Al igual que para la sentencia DELETE, en UPDATE también se pueden usar

subconsultas, que permiten seleccionar filas a actualizar en base a información
contenida en otras tablas. Las limitaciones de las subconsultas son las mismas que
para los DELETES.

UPDATE clientes

SET rep_clie = 106

WHERE rep_clie IN (SELECT num_empl

FROM repventas

WHERE ventas < (0.8 * cuota));

164 VERSIÓN 1
 PL/SQL

21. DDL. DATA DEFINITION LANGUAGE

CREACIÓN DE UNA TABLA (CREATE TABLE)

SINTAXIS GENERAL.

CREATE TABLE <nombre de la tabla>

( <nombre de la columna> <tipo de dato> [NOT NULL] [DEFAULT ----],

... )

El nombre de la tabla debe ser único al igual que el nombre de la columna dentro de la
tabla (puede ser igual al nombre de una columna que pertenece a otra tabla).

Opcionalmente puede añadirse:

• NOT NULL: impide que existan valores nulos (NULL) dentro de la columna. Si se
omite, se permiten NULL.

• DEFAULT: especifica un valor por omisión para la columna.

CREATE TABLE oficinas

(oficina NUMBER(8) NOT NULL,

ciudad VARCHAR(15) NOT NULL,

region VARCHAR(10) NOT NULL WITH DEFAULT 'Este',

dir NUMBER(8) DEFAULT 106,

VERSIÓN 1 165
PL/SQL

objetivo NUMBER(12,2) DEFAULT NULL,

ventas NUMBER(12,2) NOT NULL DEFAULT 0.00)

CREATE TABLE varia de un producto RDBMS a otro en cuanto a:

• tipos de datos.

• palabras claves para identificar tipos de datos.

• manejo de los valores NULL: Según el estándar una columna puede contener
un valor NULL a menos que específicamente se declare NOT NULL. En
algunos productos el defecto es NOT NULL y hay que especificar NULL para
que puedan darse valores nulos.

Una sintaxis alternativa sería:

CREATE TABLE <nombre de la tabla>

(<columna]> <tino de dato> [NOT NULL] [DEFAULT], ...

.....

) [ STORAGE <storage> ]

[AS <consulta>]

DEFINICIONES DE LA CLAVE PRIMARIA Y FORÁNEA

PRIMARY KEY: define la clave primaria. Sirve como identificador único para cada fila
de la tabla. Especifica la/s columna/s que forman la clave primaria de la tabla. Se exige
que los valores de la clave primaria para cada fila sean únicos y no nulos (NOT NULL
para la/s columna/s de la clave primaria).

FOREIGN KEY: define la clave foránea de la tabla. Específica la relación que crea
con otra tabla de la base de datos. Especifica:

166 VERSIÓN 1
 PL/SQL

• columnas dentro de la tabla que forman dicha clave.

• la tabla padre que es referenciada por la clave (hijo).

• nombre opcional para la relación. Este nombre cuando se produce un error.

• una regla de supresión opcional para la relación (RESTRICT, CASCADE o SET

NULL). Si no se especifica esta regla de supresión se utiliza la regla RESTRICT.

El RDBMS se asegura de que la clave foránea y la clave primaria de la tabla

referenciada concuerdan en número de columnas y tipo de dato.

CREATE TABLE <nombre de la tabla>

(<col 1> <tipo de dato>...,

...

PRIMARY KEY ( <coli>, <colj>,...),

FOREIGN KEY <nombre de la relación>( <cola>, <colb>...)

REFERENCES <nombre de la tabla padre>

[ON DELETE { RESTRIC, CASCADE)] )

CREATE TABLE pedidos

(num_pedido NUMBER(8) NOT NULL,

fecha_pedido DATE NOT NULL,

clie NUMBER(8) NOT NULL,

rep NUMBER(8),

fab CHAR(3) NOT NULL,

product CHAR(5) NOT NULL,

VERSIÓN 1 167
PL/SQL

cant NUMBER(10) NOT NULL,

importe NUMBER(12,2) NOT NULL,

PRIMARY KEY (num_pedido),

FOREIGN KEY pedidopor(clie) REFERENCES clientes,

FOREIGN KEY tomadopor(rep) REFERENCES repventas,

FOREIGN KEY espor(fab,producto) REFERENCES productos

ON DELETE RESTRIC)

NUM_PEDIDO es la clave primaria de la tabla PEDIDOS. CLIE, REP y (FAB,

PRODUCTO) son las claves foráneas. CLIE esta relacionado con la tabla padre
CLIENTES a través de la referencia PEDIDOPOR. REP esta relacionado con la tabla
REPVENTAS a través de la referencia TOMADOPOR. FAB y PRODUCTO esta
relacionado con la tabla PRODUCTOS a través de la referencia ESPOR.

ORACLE: Soporta estas dos claves:

CREATE TABLE <nombre de la tabla>

(<col 1> <tipo de dato> [NOT NULL] [DEFAULT],

...

CONSTRAINT <nombre de la relación> PRIMARY KEY ( <coli>, <colj>,

...),

CONSTRAINT <nombre de la relación> FOREIGN KEY ( <cola>,

<colb>, ...)

REFERENCES <nombre de tabla padre>

168 VERSIÓN 1
 PL/SQL

Con la cláusula CONSTRAINT se específica el nombre de la relación tanto para la

clave primaria como para la clave foránea. Esta cláusula es opcional. La clave foránea
debe referenciar a la clave primaria o a un grupo de columnas de la misma o a un
grupo de columnas con la especificación UNIQUE. SI no se especifica ninguna
columna de la tabla padre entonces la clave foránea referencia a la clave primaria de
la misma.

RESTRICCIONES DE UNICIDAD

UNIQUE: especifica que los valores de una columna sean únicos.

Si una clave primara, clave foránea o una restricción de unicidad afecta a una sola
columna, el estándar ANSI/ISO permite una forma abreviada de la definición: permite
que estas claves se añadan al final de la definición de la columna.

ELIMINACIÓN DE UNA TABLA (DROP TABLE)

DROP TABLE <nombre de la tabla>

DROP TABLE clientes;

De no ser el propietario, se debe tener permiso para borrarla:

DROP TABLE <propietario>.<nombre de la tabla>

DROP TABLE sam.clientes;

DROP borra la tabla de la base de datos, su definición y todos sus contenidos. Todo
esto se pierde y no hay forma de recuperarlo.

VERSIÓN 1 169
PL/SQL

MODIFICACIÓN DE LA DEFINICIÓN DE TABLA (ALTER TABLE)

ALTER TABLE: permite modificar la estructura de la tabla y se emplea normalmente

sobre tablas propias. Cada RDBMS posee una forma. Se emplea para:

• añadir una definición de columna a una tabla.

• definir una clave primaria para una tabla.

• definir una clave foránea para una tabla.

• eliminar una clave foránea o primaria existente para una tabla.

ADICIÓN DE UNA COLUMNA

ALTER <nombre de la tabla>

ADD <nombre de la columna> <tipo de dato>

La nueva columna se añade al final de la tabla. El RDBMS asume un valor NULL para
la columna recién añadida en todas las filas existentes en la tabla. Puede ponerse
NOT NULL WITH DEFAULT con lo que el RDBMS pone el valor de defecto
especificado. Todos estos cambios se realizan cuando se intenta visualizar, no
inmediatamente.

ALTER TABLE productos

ADD cant_min NUMBER(10) NOT NULL WITH DEFAULT;

SUPRESIÓN DE UNA COLUMNA

No se puede suprimir una columna con ALTER. Los pasos que hay que dar son:

1. descargar todos los datos de la tabla.

170 VERSIÓN 1
 PL/SQL

2. utilizar la sentencia DROP TABLE cara borrar la definición de la tabla.

3. utilizar la sentencia CREATE TABLE para redefinir la tabla sin la columna no

deseada.

4. volver a cargar todos los datos que fueron anteriormente descargados.

Existen programas para cargar y descargar datos que facilitan esta labor de supresión.

MODIFICACIÓN DE CLAVES PRIMARIA Y FORÁNEA.

INSERTAR CIAVE FORÁNEA

ALTER TABLE <nombre de la tabla hijo>

ADD CONSTRAINT <nombre de la relación> FOREIGN KEY (

<columna/s>)

REFERENCES <nombre de la tabla padre>

ALTER TABLE oficinas

ADD CONSTRAINT enregion FOREIGN KEY (region)

REFERENCES region;

BORRAR CLAVE FORÁNEA

Para ello la relación que se crea entre la tabla padre e hijo debe tener un nombre.

ALTER TABLE <nombre de la tabla hijo>

DROP CONSTRAINT <nombre de la relación padre/hijo>

VERSIÓN 1 171
PL/SQL

ALTER TABLE oficinas

DROP CONSTRAINT enregion;

OTRAS MODIFICACIONES.

En ORACLE la sentencia ALTER TABLE permite:

• añadir columnas o relaciones (CONSTRAINT) mediante ADD.

• modificar tipos de datos de columnas usando MODIFY.

• borrar restricciones con DROP.

• activar y desactivar restricciones con ENABLE y DISABLE.

• modificar el futuro almacenamiento de datos con STORAGE.

ALTER TABLE <nombre de la tabla>

[ADD ( <col1><tipo de dato>, ...)]

[MODIFY ( <col2> <nuevo tipo de dato>, ...)]

[DROP CONSTRAINT <nombre de la relación>]

[DISABLE CONSTRAINT <nombre de la relación>]

[ENABLE CONSTRAINT <nombre de la relación>]

ALTER TABLE emp

MODIFY ename VARCHAR2(75);

ALTER TABLE emp

DISABLE CONSTRAINT emp_pk;

172 VERSIÓN 1
 PL/SQL

CREACIÓN DE VISTAS (CREATE VIEW)

Una vista es una tabla virtual en la base de datos cuyos contenidos están definidos por
una consulta. De cara al usuario aparece igual que una tabla real, con un conjunto de
columnas designadas y filas de datos. A diferencia de una tabla real, una vista no
existe en la base de datos como un conjunto almacenado de valores. La vista esta
formada por un conjunto de tablas fuente de las cuales se selecciona un conjunto de
columnas (o todas).

Una vez definida la vista, se puede utilizar en una sentencia SELECT, lo mismo que en
una tabla real. En algunas vistas también se puede utilizar las sentencias INSERT,
DELETE y UPDATE para modificar datos a través de ella, corno si fuera una tabla real.
Por tanto, a todos los efectos prácticos, se puede utilizar como si fuera una tabla real.

Cuando el RDBMS se encuentra con la referencia de una vista en una sentencia,

determina la definición de la misma y luego traduce la petición que referencia a la
vista a una petición equivalente con respecto a las tablas fuentes de la vista y lleva a
cabo la petición. Para vistas muy complejas, define la vista y la almacena en una tabla
temporal. Sobre ella realiza las peticiones borrándola cuando ya no es necesaria.

Ventajas de las vistas

• Seguridad: cada usuario esta autorizado para acceder a una serie de vistas.

• Simplicidad en la consulta: se puede eliminar consultas multitabla.

• Simplicidad estructurada: dan una visión personalizada de la estructura de la base

de datos.

• Aislamiento frente al cambio: puede presentar una imagen consistente inalterada

de la estructura de la base de datos aunque las tablas cambien de estructura.

• Integridad de datos: al acceder e introducir datos a través de la vista el RDBMS

comprueba automáticamente los datos para asegurarse de que cumplen las
restricciones de integridad especificadas.

Desventajas de las vistas

VERSIÓN 1 173
PL/SQL

• menor rendimiento.

• restricciones de actualización: existen vistas que pueden ser actualizadas pero

otras no, es decir, son de solo lectura.

Para crear una vista se debe tener permiso para acceder a todas las tablas
referenciadas en la consulta.

Se puede asignar opcionalmente un nombre a cada columna de la vista creada.

Solamente se especifican los nombres de las columnas. El tipo de datos, la longitud, y
otras características de la columna se deducen de la definición de la columna de la
tabla fuente.

a) Vistas horizontales

Una vista horizontal divide horizontalmente la tabla fuente para crear la vista. Todas
las columnas de la tabla fuente participan en la vista, pero solo algunas de sus filas
son visibles a través de la vista.

CREATE VIEW <nombre de la vista> AS

SELECT *

FROM <nombre de la tabla>

WHERE <condición>

La vista se deriva de una única tabla fuente. WHERE determina qué filas van a formar
parte de la vista.

CREATE VIEW repeste AS

SELECT *

FROM repventas

WHERE oficina_rep IN (11, 12, 13 );

174 VERSIÓN 1
 PL/SQL

b) Vistas verticales

Una vista vertical divide la tabla fuente verticalmente para crear la vista. Todas las filas
de la tabla fuente participan en la vista, pero solo algunas de sus columnas son
visibles a través de ella.

CREATE VIEW <nombre de la vista> AS

SELECT <col 1>, <col2>,...

FROM <nombre de la tabla>

La vista se deriva de una única tabla fuente. SELECT determina que columnas van a

tomar parte de la vista.

CREATE VIEW infooficina AS

SELECT oficina, ciudad, region

FROM oficinas;

c) Vistas con subconjuntos fila/columna

La vista esta formada por un conjunto de filas y columnas de una única tabla real.

CREATE VIEW <nombre de la vista> AS

SELECT <col 1>, <col2>,...

FROM <nombre de la tabla>

WHERE <condición>

SELECT selecciona las columnas y WHERE las filas de la tabla fuente.

VERSIÓN 1 175
PL/SQL

CREATE VIEW cliebill AS

SELECT num_clie, empresa, limite_credito

FROM clientes

WHERE rep_clie=109;

d) Vistas agrupadas

Una vista agrupada es aquella en la que los datos visibles a través de ella son el
resultado de una consulta agrupada. Las vistas agrupadas toman una columna de la
tabla, le aplican una función de grupo, producen una fila resultado y se le asigna a un
nombre de columna.

CREATE VIEW <nombre de la lista> ( <col 1>, <coI2>, ... ) AS

SELECT <coln>, <funl>, <fun2>, ...

FROM <nombre de la tabla>

GROUP BY <coln>

Las vistas agrupadas funcionan como vistas de solo lectura, pueden participar en
consultas, pero no en actualizaciones.

CREATE VIEW ped_por_rep (quien, cuantos, total, inf, sup, medio) AS

SELECT rep, COUNT(*), SUM(Importe),

MIN(importe),MAX(importe),AVG(importe)

FROM pedidos

GROUP BY rep;

176 VERSIÓN 1
 PL/SQL

e) Vistas compuestas

Una vista compuesta extrae sus datos de dos o tres tablas diferentes y presentan los
resultados de la consulta como única tabla virtual.

CREATE VIEW <nombre de la vista> (<col 1>, <co2>, ... ) AS

SELECT <cola>, <colb>, ...

FROM <tabla 1>, <tabla2>, ...

WHERE <condicion1>

AND <condicion2>

Donde:

col 1, col 2, ...nombres de las columnas de la vista.

cola, colb ... nombres de las columnas de las tablas especificadas por FROM.

Sobre esta nueva vista puede hacerse todo tipo de consultas y accesos a datos.

CREATE VIEW info_pedido (num_pedido, empresa, nombre_rep, importe)

AS SELECT num_pedido, empresa, nombre_rep, importe

FROM pedidos, clientes, repventas

WHERE clie=num_clie AND rep_clie = 1O3;

ACTUALIZACIÓN DE UNA VISTA

En una vista se puede insertar una fila de datos, suprimirla y actualizarla. Cualquier
tipo de modificación realizada en la vista implica una modificación en la/s tabla/s
original/es.

EL ESTÁNDAR ANSI/ISO PARA ACTUALIZACIONES DE VISTAS

VERSIÓN 1 177
PL/SQL

Bajo el estándar ANSI/ISO una vista puede ser actualizada si la consulta que define
satisface las siguientes restricciones.

• las filas duplicadas no deben ser eliminadas de los resultados de la consulta.

• la vista debe tener una única tabla fuente para la cual el usuario tiene los
privilegios requeridos. Si la tabla fuente es una vista debe cumplir estos criterios.

• la lista de selección no puede contener expresiones, columnas calculadas o

funciones de columna.

• las condiciones de búsqueda especificadas en WHERE no pueden incluir una

subconsulta. Solo pueden aparecer condiciones de búsqueda simples fila a fila.

• la consulta no debe incluir una cláusula GROUP BY o HAVING.

Estas reglas sobre actualizaciones de vistas son muy restrictivas. En realidad las
reglas específicas que determinan si una vista puede ser actualizada o no varían de un
producto RDBMS a otro, y suelen estar bastante detalladas.

En ORACLE una vista puede ser actualizada si la consulta de la que deriva contiene
cumple las siguientes restricciones:

• no contiene una unión de tablas.

• no contiene la cláusula GROUP BY.

• no contiene la cláusula DISTINT.

COMPROBACIÓN DE ACTUALIZACIÓN DE VISTAS

Existe una opción de comprobación de actualización de vistas: CHECK OPTION. Esta

opción se especifica en la sentencia CREATE VIEW de la siguiente forma:

CREATE VIEW <nombre de la vista> AS

SELECT *

178 VERSIÓN 1
 PL/SQL

FROM <nombre de la tabla>

WHERE <condición>

WITH CHECK OPTION

Con esta opción, se comprueba automáticamente cada operación INSERT y UPDATE

sobre la vista para asegurarse de que las filas resultantes satisfagan el criterio de
búsqueda de la definición de vista. Si una fila insertada o modificada no satisface la
condición, la sentencia INSERT o UPDATE fallaría y la operación no se llevaría a
cabo. Con esto se consigue la integridad de las bases de datos, pues si se omitiera
esta opción y se intentara actualizar una fila en la vista que no satisficiera el criterio de
búsqueda, el RDBMS la actualizaría en la/s tabla/s fuente pero no en la vista. Si
posteriormente se realiza una consulta dicha fila no aparecería en la vista. Con la
opción de comprobación se impide que modificaciones efectuadas a través de la vista
afecten a datos que no son accesibles al usuario en primera instancia.

CREATE VIEW repeste AS

SELECT *

FROM repventas

WHERE oficina_rep IN(11, 12, 13)

WITH CHECK OPTION;

SUPRESIÓN DE VISTAS (DROP VIEW)

DROP VIEW <nombre de la view>;

VERSIÓN 1 179
PL/SQL

donde <nombre de la view> es el nombre dado por nosotros a esta VIEW en la

sentencia de creación CREATE VIEW.

DROP VIEW oficinas1;

CREACIÓN DE SINÓNIMOS (CREATE SYNONYM)

Un sinónimo es un nombre definido para representar el nombre de una tabla que

pertenece a otro usuario. Si se tiene permiso para acceder a la tabla de otro usuario se
debe poner usuario.nombre de la tabla.nombre de la columna

Para simplificar esto se puede sustituir usuario.nombre de la tabla por un sinónimo:

CREATE [PUBLIC] SYNONYM <nombre sinónimo>

FOR <usuario>.<nombre de la tabla>

PUBLIC especifica que el sinónimo puede ser empleado por todos los usuarios de la
base de datos. Si se omite PUBLIC, por defecto el sinónimo solo es accesible a su
creador.

CREATE SYNONYM OFICIAS

FOR ADMIN_PP.OFICINAS

SUPRESIÓN DE SINÓNIMOS (DROP SYNONYM)

DROP SYNONYM <nombre del sinónimo>

DROP SYNONYM OFICINAS

180 VERSIÓN 1
 PL/SQL

CAMBIOS DE NOMBRE (RENAME)

Existe un comando o sentencia SQL que permite la modificación del nombre de una
tabla, vista o sinónimo. Este comando es específico del SQL de ORACLE. La
sentencia a la que nos referimos es RENAME.

RENAME nombre_antiguo TO nuevo_nombre;

donde:

nombre_antiguo Es el nombre que poseía la tabla, sinónimo o vista.

nuevo_nombre Es el nombre que desearnos asignar.

CREACIÓN DE ÍNDICES (CREATE INDEX)

INDICE: es una estructura que proporciona un acceso rápido a las filas de una tabla
en base a los valores de una o más columnas. Almacena valores y punteros a las filas
en donde se producen los valores. En el índice los valores dados están dispuestos en
orden creciente o decreciente de modo que el RDBMS puede buscar rápidamente el
índice para encontrar un valor particular. A continuación puede seguir el puntero para
localizar la fila que contiene al valor. El índice informa al RDBMS en que lugar del
disco esta localizada la fila. Todo esto acelera enormemente la ejecución de las
sentencias SQL con condiciones de búsqueda.

Desventajas de !os índices:

• el índice consume espacio en el disco.

• debe actualizarse cada vez que se añade una fila a la tabla y cada vez que la
columna indexada se actualiza en una fila existente.

Utilidad:

VERSIÓN 1 181
PL/SQL

• para condiciones de búsqueda.

• para consultas a tabla más frecuentes que inserciones y actualizaciones.

El RDBMS siempre establece un índice para la clave primaria, ya que presupone que
el acceso a la tabla se efectuará más frecuentemente a través de la clave primaria.

CREATE [UNIQUE] INDEX <nombre del índice>

ON <nombre de la tabla> ( col l, col2, ...)

UNIQUE es opcional. Indica que los valores de las columnas deben ser únicos.

CREATE UNIQUE INDEX ped_prod_idx

ON pedidos(fab, producto);

ELIMINACIÓN DE ÍNDICES (DROP INDEX)

DROP INDEX <nombre del índice>

DROP INDEX ped_prod_idx;

ORACLE: Soporta la misma sintaxis para crear y borrar índices. Entre otras cosas se
puede especificar en qué TABLESPACE se quiere almacenar el índice:

CREATE [UNIQUE] INDEX <nombre del índice> ON

<nombre de la tabla> (col1 [ASC | DESC], col2 [ASC|DESC], ...)

[TABLESPACE <tablespace>]

182 VERSIÓN 1
 PL/SQL

ASC especifica que el índice recorre valores en orden ascendente.

DESC especifica que el índice recorre valores en orden descendente.

Si no se especifica nada se toma ASC por defecto.

VERSIÓN 1 183
PL/SQL

22. DCL. DATA CONTROL LANGUAGE.

SEGURIDAD DEL SISTEMA.

Solamente los usuarios autorizados pueden acceder a la BD. Cuando un usuario se

conecta emplea un nombre de usuario y una clave. En ese momento se verifica si se
trata de un usuario autorizado y en caso afirmativo cuales son sus privilegios de
acceso.

Los privilegios de un usuario se agrupan en dos familias:

• Privilegios de sistema: aquellos que indican si el usuario tiene permiso para

realizar ciertas acciones sobre un conjunto de objetos (p.e. el derecho a crear
tablespaces o a borrar filas de cualquier tabla).

• Privilegios de objetos: aquellos que indican si el usuario tiene permiso para realizar
ciertas acciones sobre un objeto concreto (p.e. borrar filas de la tabla EMP).

La cantidad de distintos privilegios que se pueden dar a un usuario es muy grande, por
ello, para evitar tener que dar los privilegios de uno en uno a los usuarios se usan los
roles. Un role es un conjunto de privilegios.

Los roles predefinidos por ORACLE son:

1. CONNECT: permite conectase y acceder a las tablas públicas y a aquellas para las
que se le hayan otorgado permisos. Contiene entre otros los privilegios “alter
session”, “create database link”, “create sequence”, “create session”, “create
synonym”, “create table”, “create view”.

2. RESOURCE: permite al usuario crear sus propios objetos. Los privilegios son
“create procedure”, “create sequence”, “create table”, “create trigger”.

3. DBA: el administrador de sistema. Tiene todos los privilegios de sistema.

184 VERSIÓN 1
 PL/SQL

ACCESO A LOS OBJETOS.

Los objetos de una base de datos son propiedad del usuario que los creo y sólo son
visibles por su dueño inicialmente. Para que otros usuarios puedan acceder a ellos, el
propietario debe conceder a estos usuarios unos privilegios.

CONCESIÓN DE PRIVILEGIOS.

Para conceder privilegios a un usuario se emplea la sentencia GRANT:

GRANT {privilegio [,privilegio,...] | ALL} ON objeto

TO {usuario [,usuario...] | PUBLIC} | WITH GRANT OPTION

Donde privilegio es el comando que se autoriza que el usuario ejecute sobre el objeto.
Estos pueden ser: ALTER, DELETE, INDEX, SELECT, UPDATE, DROP,...

La opción ALL otorga todos los privilegios y PUBLIC hace referencia al conjunto de
todos los usuarios. Finalmente, WITH GRANT OPTION permite al usuario que recibe
estos privilegios transmitirlos a otros usuarios.

RETIRADA DE PRIVILEGIOS.

El propietario de los objetos pueda retirar los privilegios concedidos sobre sus objetos
a otros usuarios empleando la sentencia REVOKE:

REVOKE {privilegio [,privilegio,...] | ALL} ON objeto

TO {usuario [,usuario...] | PUBLIC}

Quitar el privilegio a un usuario también significa quitárselo a los usuarios a los que
este hubiera transmitido dichos privilegios.

VERSIÓN 1 185
PL/SQL

Nota: la concesión y retirada de roles a un usuario o de privilegios a un role sigue las

mismas reglas sintácticas que acabamos de indicar.

186 VERSIÓN 1
 PL/SQL

ANEXO 3: TABLAS DEL DICCIONARIO.

TABLAS DEL DICCIONARIO.

Toda la información de las tablas está registrada en el diccionario del sistema (Data
Dictionary), que son tablas especiales que se crean en la instalación de ORACLE (que
son administradas por el sistema).

Conjunto de tablas y vistas que contienen información sobre la base de datos (se
crean automáticamente cuando se crea esa base). Se pueden consultar las tablas y
vistas del diccionario con la sentencia SELECT.

Tablas:

• DICTIONARY

Contiene una descripción de las tablas que constituyen el diccionario.

• ALL_CATALOG

Contiene todas las tablas, vistas y sinónimos accesibles por el usuario.

• ALL_OBJECTS

Contiene todos los objetos (tablas, vistas, índices, …) propiedad del usuario
o a los que puede acceder.

• USER_CATALOG

Contiene todas las tablas, vistas, sinónimos y secuencias propiedad del

usuario.

VERSIÓN 1 187
PL/SQL

• USER_OBJECTS

Contiene todos los objetos propiedad del usuario.

• USER_SOURCE

Contiene toda la información sobre paquetes, cuerpos de paquetes,

procedimientos y funciones.

• ALL_TAB_COLUMNS

Muestra nombres y definiciones de las columnas a las que el usuario puede

acceder.

• USER_TAB_COLUMNS

Muestra nombres y definiciones de las columnas propiedad del usuario.

• USER_INDEXES

Contiene información sobre los índices creados por el usuario.

• ALL_SYNONYMS

Muestra información sobre los sinónimos accesibles por el usuario.

• USER_SYNONYMS

Muestra información sobre los sinónimos creados por el usuario.

• USER_TAB_GRANTS

Describe los privilegios de accesos a tablas otorgados por el propietario a

los demás usuarios. También registra los privilegios concedidos al usuario
por los demás usuarios.

• ALL_USERS

Contiene la lista de los usuarios de la base.

• ALL_VIEWS

188 VERSIÓN 1
 PL/SQL

Contiene el nombre de las vistas accesibles por el usuario.

• USER_VIEWS

Contiene las sentencias utilizadas en la definición de las vistas creadas por

el usuario.

VERSIÓN 1 189
PL/SQL

ANEXO 4: OPERADORES

OPERADORES

Se utilizan para manipular datos de forma individual y devolver un valor. Suelen estar
representados por caracteres especiales o palabras claves.

OPERADOR DESCRIPCIÓN

() Modificar prioridad de operadores.

+, - Sumar y restar.

*, / Multiplicar y dividir.

|| Concatenar cadenas de caracteres.

= Igual.

<>,!=, ¬= Distinto.

>, < Mayor y menor.

>=, <= Mayor o igual y menor o igual.

[NOT] IN Pertenencia a un conjunto.

ANY, ALL Permiten realizar comparaciones con subconsultas.

[NOT] BETWEEN c1 AND c2 Mayor o igual que c1 y menor igual que c2.

[NOT] EXISTS Verdadero si la subconsulta devuelve filas.

190 VERSIÓN 1
 PL/SQL

[NOT] LIKE Verdadero si es similar al patrón de referencia.

IS [NOT] NULL Comprueba si el valor es nulo.

NOT Negación lógica.

AND Y lógica.

OR O lógica.

UNION Une los resultados de varias consultas.

INTERSECT Filtra el resultado de dos consultas a aquellas filas que se

encuentran en ambas.

MINUS Filtra el resultado de dos consultas a aquellas filas que se

encuentran en la primera pero no en la segunda.

OPERADORES LÓGICOS

Para construir la condición de la consulta necesitamos conocer los operadores

lógicos, por eso a continuación damos una lista de los operadores más usados,
agrupados en cuatro grupos:

1. Valor único: Comprueban un valor simple.

2. Lista de valores: Comprueban más de un valor.

3. Combinaciones lógicas: Combinan expresiones lógicas.

4. Negación: Invierte el resultado de la expresión con operadores de valor único

o de lista de valores.

VERSIÓN 1 191
PL/SQL

VALOR ÚNICO

> < >= <= =

Operadores clásicos de comparación:

mayor, menor, mayor e igual, menor e igual, igual a.

!= <> ^=

Operador "Distinto de" en sus tres formas.

IS NULL

Comprueba la ausencia de datos (valor nulo).

No se puede usar la comparación = NULL.

LIKE

Selecciona registros según el reconocimiento de un patrón de consulta.

LISTA DE VALORES

BETWEEN valor AND valor

Comprueba que el valor se encuentre en el rango de valores.

IN (valor,...,valor)

Verifica si el valor pertenece a la lista de valores.

Combinaciones lógicas.

AND

Retorna Verdadero si todas las condiciones son verdaderas.

OR

192 VERSIÓN 1
 PL/SQL

Retorna Verdadero si alguna de las condiciones es verdadera.

NEGACIÓN

NOT

Invierte el resultado de una expresión lógica, por ejemplo.

IS NOT NULL

NOT BETWEEN valor AND valor

NOT IN (valor,...,valor)

NOT LIKE

PATRÓN DE CONSULTA

Una de las herramientas lógicas más poderosas de SQL es el reconocimiento de un

patrón de consulta, instrumento éste que permite la búsqueda por nombre, dirección u
otro dato parcialmente recordado. Los patrones de consulta juegan un papel
importante en el momento de realizar consultas, ya que es común que necesitemos
encontrar un texto y no recordemos exactamente cómo fue ingresado. Con el uso del
operador LIKE podemos comparar patrones y ubicar un texto, independientemente de
la posición en que se encuentre.

Para la definición del patrón de consulta existen dos tipos de caracteres especiales:

% (signo de porcentaje) llamado comodín, representa cualquier cantidad de espacios

o caracteres en esa posición. Significa que se admite cualquier cosa en su lugar: un
carácter, cien caracteres o ningún carácter.

_ (signo de subrayado) llamado marcador de posición, representa exactamente

una posición e indica que puede existir cualquier carácter en esa posición.

VERSIÓN 1 193
PL/SQL

Listar los clientes que tengan la palabra LIBRO a partir de la 5ª posición en el nombre.

SELECT nombre,direccion,telefono

FROM clientes

WHERE nombre LIKE '____LIBRO%';

194 VERSIÓN 1