cr PL-SQL Para Principiantes

PROYECTO

Universidad Tecnológica Costarricense

Contenido Conceptos Básicos y definición de PL/SQL ......................................... ...................................................... ............. 3 Que es PL/SQL ................................................... ............................................................................. ............................................... ..................... 3 Características de PL/SQL .............................. ........................................................ ................................................... ......................... 4 Bloques PL/SQL .......................................... .................................................................... ................................................... ............................. .... 4 Tipos de Bloques................................................. ........................................................................... ............................................... ..................... 5 Tipos de Datos Oracle/PL-SQL ........................... ..................................................... ............................................... ..................... 6 Identificadores ................................................. ........................................................................... ................................................... ......................... 8 Operadores ................................................. ........................................................................... .................................................... ............................. ... 8 Clave primaria ................................................. ........................................................................... ................................................... ......................... 9 Llaveforánea................................................................. .......................................................................................... .................................... ........... 10 Unique ................................................. .......................................................................... .................................................. .................................... ........... 11 Índices ................................................. .......................................................................... .................................................. .................................... ........... 11 Secuencias .................................................. ............................................................................ .................................................... ............................ 12 Funciones y Procedimientos ............................................................. ............................................................................ ............... 13 Procedimientos............................................................. ...................................................................................... .................................... ........... 13 Funciones ................................................ .......................................................................... ................................................... ............................... ...... 14 Cursores .................................................. ............................................................................ ................................................... ............................... ...... 17 Vistas .................................................. ........................................................................... .................................................. .................................... ........... 19 Paquetes ................................................. ........................................................................... ................................................... ............................... ...... 21 Triggers ................................................... ............................................................................. ................................................... ............................... ...... 22 Ejemplos varios en PL/SQL................................................... PL/SQL............................................................................ ........................... .. 25 Resuelva los siguientes ejercicios: ................................................ ................................................................... ................... 31 Solucionario.............................................................. ....................................................................................... ........................................ ............... 33

2


Conceptos Básicos y definición de PL/SQL Que es PL/SQL

PL-SQL es el lenguaje de programación que viene incrustado en la Base de Datos Oracle,

sus

siglas

en

inglés

corresponden

a

“Procedural

Language

–

StructuredQueryLanguage” y al español se puede traducir como “Lenguaje de

Procedimiento – Lenguaje de Consulta Estructurado”. El lenguaje PL-SQL permite soportar todas las consultas a la Base de Datos Oracle, ya que para manipular los datos se usan las mismas instrucciones del lenguaje SQL estándar usado en las Bases de Datos. PL-SQL es un lenguaje que está estructurado en bloques de código, los que son utilizados para construir PL-SQL Packages en Oracle, PL-SQL Procedures en Oracle, PL-SQL Function en Oracle y scripts de código. Los desarrolladores pueden construir sus bloques PL-SQL y almacenarlos como objetos en el entorno de la Base de Datos Oracle. Estos programas o paquetes en PL-SQL son ejecutados en el servidor lo que permite ahorrar recursos en el entorno del cliente. Los programas o PL-SQL Packages en Oracle solo pueden ser accesados o ejecutados por los usuarios debidamente autorizados.

3


Características de PL/SQL



Bloques PL-SQL



Tipos de Bloques



Tipos de Datos Oracle/PL-SQL



Identificadores



Operadores



Variables



Constantes

Bloques PL/SQL

Un programa de PL/SQL está compuesto por bloques. Un programa está compuesto como mínimo de un bloque. Los bloques de PL/SQL pueden ser de los siguientes tipos: 

Bloques anónimos



Subprogramas

Estructura de un Bloque Los bloques PL/SQL presentan una estructura específica compuesta de tres partes bien diferenciadas: 

La sección declarativa: en donde se declaran todas las constantes y variables que se van a utilizar en la ejecución del bloque.



La sección de ejecución: que incluye las instrucciones a ejecutar en el bloque PL/SQL.



La sección de excepciones: en donde se definen los manejadores de errores que soportará el bloque PL/SQL.

4


Cada una de las partes anteriores se delimita por una palabra reservada, de modo que un bloque PL/SQL se puede representar como sigue:

Tipos de Bloques

Los bloques PL/SQL son de varios tipos: 

Anónimos (Anonymous blocks): Se construyen de forma dinámica y se ejecutan una sola vez.



Con nombre (Named blocks): Son bloques con nombre, que al igual que el anterior se construyen, generalmente, de forma dinámica y se ejecutan una sola vez.



Subprogramas: Procedimientos, paquetes o funciones almacenados en la BD. No suelen cambiar después de su construcción y se ejecutan múltiples veces mediante una llamada call.

5




Disparadores (Triggers): Son bloques con nombre que también se almacenan en la BD. Tampoco suelen cambiar después de su construcción y se ejecutan varias veces. Se ejecutan de forma automática ante algún suceso de disparo, que será una orden del lenguaje de manipulación de datos (INSERT, UPDATE o DELETE) que se ejecuta sobre una tabla de la BD.

Tipos de Datos Oracle/PL-SQL

Cada constante y variable tiene un tipo de dato en el cual se especifica el formato de almacenamiento, restricciones y rango de valores validos. PL/SQL proporciona una variedad predefinida de tipos de datos. Casi todos los tipos de datos manejados por PL/SQL son similares a los soportados por SQL.

Los tipos de datos más comunes: 

VARCHAR2: Almacena datos de tipo carácter empleando sólo la cantidad necesaria aún cuando la longitud máxima sea mayor.



BOOLEAN: Se emplea para almacenar valores TRUE o FALSE.

6




DATE: Almacena datos de tipo fecha. Las fechas se almacenan internamente como datos numéricos, por lo que es posible realizar operaciones aritméticas con ellas.



NUMBER: Almacena números enteros o de punto flotante, virtualmente de cualquier longitud, aunque puede ser especificada la precisión (Número de dígitos) y la escala que es la que determina el número de decimales.



CHAR: Almacena datos de tipo carácter con una longitud máxima de 32767 y cuyo valor de longitud por default es.



Atributos de tipo: Un atributo de tipo PL/SQL es un modificador que puede ser usado para obtener información de un objeto de la base de datos. El atributo %TYPE permite conocer el tipo de una variable, constante o campo de la base de datos. El atributo %ROWTYPE permite obtener los tipos de todos los campos de una tabla de la base de datos, de una vista o de un cursor.

7


Identificadores

Se emplean para dar nombre a los objetos PL/SQL, tales como variables, cursores, tipos y subprogramas. Los identificadores constan de una letra, seguida por una secuencia opcional de caracteres, que pueden incluir letras, números, signos de dólar ($), caracteres de subrayadoy símbolos de almohadilla (#). Los demás caracteres no pueden emplearse. La longitud máximade un identificador es de 30caracteres .

Ejemplosvalidos X,Y, CodEmpleado, V1, V2_, ES_UNA_VARIABLE_#

Operadores

TIPO DE OPERADOR

OPERADORES

Asignación

:= (dos puntos + igual) + (suma) - (resta)

Aritméticos

* (multiplicación) / (división) ** (exponente)

8


= (igual a) <> (distinto de) Relacionales o de Comparación

< (menor que) > (mayor que) >= (mayor o igual a) <= (menor o igual a) AND (y lógico)

Lógicos

NOT (negación) OR (o lógico)

Concatenación

||

Clave primaria

La clave primaria se utiliza para identificar en forma única cada línea en la tabla. Puede ser parte de un registro real, o puede ser un campo artificial (uno que no tiene nada que ver con el registro real). Una clave primaria puede consistir en uno

9


o más campos en una tabla. Cuando se utilizan múltiples campos como clave primaria, se los denomina claves compuestas. Las claves primarias pueden especificarse cuando se crea la tabla:

O cambiando la estructura existente de la tabla utilizando ALTER TABLE.

Llave foránea

Una llave externa es un campo (o campos) que señala la llave primaria de otra tabla. El propósito de la llave foránea es asegurar la integridad referencial de los datos. En otras palabras, sólo se permiten los valores que se esperan que aparezcan en la base de datos.

10


Unique Una restricción Unique (único) asegura que el campo o campos en los que dicha restricción se aplica para un registro tendrán un valor diferente con respecto a cualquier otro registro de la tabla. La restricción Unique es similar a una clave primaria, pero no es necesario que los campos con este tipo de restricciones tengan valores. Si la restricción está compuesta por más de un campo, algunos de ellos pueden tener valores nulos siempre y cuando la combinación de campos siga siendo única en la tabla.

Índices

Un índice es una estructura de datos que permite acceder a diferentes filas de una misma tabla a través de un campo (o campos clave).Un índice permite un acceso mucho más rápido a los datos.

Las sentencias de SQL para manipular índices son:

La sintaxis para la creación de índices es la siguiente:

11


La palabra clave UNIQUE especifica que no pueden existir claves duplicadas en el índice.ASC | DESC especifican el criterio de ordenación elegido, ascendente o descendente, por defecto es ascendente.

En la siguiente imagen se muestra como se crea o elimina un índex.

Los índices usados por las restricciones "primarykey" y "unique" no pueden eliminarse con "dropindex", se eliminan automáticamente cuando quitamos la restricción. Si eliminamos una tabla, todos los índices asociados a ella se eliminan.

Secuencias Las secuencias (sequences) son objetos que facilitan la generación automática de series numéricas. Los usos más frecuentes de las secuencias, son: 

La generación automática de claves primarias



Coordinar las claves de múltiples filas o tablas.

12


Las secuencias son independientes de las tablas; por tanto, una misma secuencia se puede usar para generar valores de columnas numéricas de una o más tablas. Ejemplo de creación de secuencia:

Funciones y Procedimientos

Procedimientos Un procedimiento es un subprograma que ejecuta una acción específica y que no devuelve ningún valor. Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código. Sintaxis de los procedimientos:

Ejemplo:

13


Funciones

Una función es un bloque nombrado PL/SQL que devuelve un valor, puede estar almacenada en la B.D., como objeto de la B.D., para repetidas ejecuciones. Una función puede ser llamada como parte de una expresión.

Sintaxis de las funciones:

Descripción de la sintaxis:

14




Nombre-parámetro: es el nombre que queramos dar al parámetro. Podemos utilizar múltiples parámetros. En caso de no necesitarlos, podemos omitir los paréntesis.



IN: especifica que el parámetro es de entrada y que por tanto dicho parámetro tiene que tener un valor en el momento de llamar a la función o procedimiento. Si no se especifica nada, los parámetros son por defecto de tipo entrada.



OUT: especifica que se trata de un parámetro de salida. Son parámetros cuyo valor es devuelto después de la ejecución el procedimiento al bloque PL/SQL que lo llamó. Las funciones PLSQL no admiten parámetros de salida.



IN OUT: Son parámetros de entrada y salida a la vez.



Tipo-de-dato: Indica el tipo de dato PLSQL que corresponde al parámetro (NUMBER, VARCHAR2, etc).

Ejemplo:

Cuando se crea un procedimiento o función, Oracle automáticamente compila el código fuente, guarda el código objeto en un área compartida de la SGA (System Global Area) y almacena tanto el código fuente como el código objeto en catálogos del diccionario de datos. El código objeto permanece en la SGA, por tanto, los procedimientos o funciones se ejecutan más rápidamente y lo pueden compartir muchos usuarios. Cuando es

15


necesario liberar áreas de la SGA, Oracle aplica el algoritmo „menos -usadorecientemente‟. Si en un momento determinado se libera el área SQL de un

procedimiento o función, la próxima vez que se ejecute se vuelve a cargar el código objeto, que está almacenado en catálogo, en la SGA.

Llamadas a procedimientos Desde otro procedimiento, función y triggers

Llamadas a funciones Desde otro procedimiento, función y triggers

Desde un bloque anónimo

Desde una instrucción SQL

16


Cursores

Los cursores permiten realizar operaciones sobre los registros devueltos por una sentencia Select. La utilización de cursores es necesaria cuando: 

Se necesita tratamiento fila a fila



En sentencias SELECT que devuelven más de una fila

Operaciones con cursores Declare 

Se declara el cursor asignándole nombre y asociándole a una consulta.

Open 

Abre el cursor y lo inicializa para que devuelva las filas.



Ejecuta la consulta asociada al cursor.

Fetch 

Lee los datos del cursor con la sentencia FETCH.



Devuelve la siguiente fila en el conjunto activo.



Los datos devueltos se almacenan en variables de control o en un registro. FETCH ... INTO ...

Close 

Desactiva el cursor y libera los recursos. CLOSE cursor_1;

17


Declaración del cursor: lo tenemos que declarar en la zona de declaraciones, con el siguiente formato:

Apertura del cursor: Deberá colocarse en la zona de instrucciones, con el siguiente formato:

Al hacerlo se ejecuta automáticamente la sentencia select y sus resultados se almacenan en las estructuras internas de memoria manejadas por el cursor.

Recogida de información: Para recuperar la información anteriormente guardada en las estructuras de memoria interna tenemos que usar el siguiente formato:

Si tenemos una única variable que recoge los datos de todas las columnas, el formato de la variable sería el siguiente:

Si tenemos una lista de variables, cada una recogerá la columna correspondiente de la cláusula select, por lo que serán del mismo tipo que las columnas.

Cierre del cursor:

18


Ejemplos de cursor:

Vistas Una vista es un objeto. Una vista es una alternativa para mostrar datos de varias tablas; es como una tabla virtual que almacena una consulta. Los datos accesibles a través de la vista no están almacenados en la base de datos, en la base de datos se guarda la definición de la vista y no el resultado de ella.

19


La sintaxis básica para crear una vista es la siguiente:

El contenido de una vista se muestra con un "select":

¿Por qué usar Vistas? 

Para restringir el acceso a la B.D.



Para realizar consultas complejas de manera fácil.



Para obtener una independencia de los datos



Para presentar diferentes vistas de los mismos datos.

20


Paquetes Un paquete es una estructura que agrupa objetos de PL/SQL compilados (procedures, funciones, variables, tipos, entre otros) en la base de datos. Esto nos permite agrupar la funcionalidad de los procesos en programas. Los Paquetes están divididos en 2 partes: especificación (obligatoria) y cuerpo (no obligatoria). La especificación o encabezado es la interfaz entre el Paquete y las aplicaciones que lo utilizan y es allí donde se declaran los tipos, variables, constantes, excepciones, cursores, procedimientos y funciones que podrán ser invocados desde fuera del paquete. .

Para crear la especificación de un paquete la sintaxis general es la siguiente:

En el cuerpo del paquete se implementa la especificación del mismo. El cuerpo contiene los detalles de implementación y declaraciones privadas, manteniéndose todo esto oculto a las aplicaciones externas, siguiendo el conocido concepto de “caja

negra”.

Sólo

las

declaraciones

hechas en la especificación del paquete son visibles y accesibles desde fuera del paquete (por otras aplicaciones o procedimientos almacenados) quedando los

21


detalles de implementación del cuerpo del paquete totalmente ocultos e inaccesibles para el exterior. La sintaxis general para crear el cuerpo de un paquete es muy parecida a la de la especificación, tan solo se añade la palabra clave BODY, y se implementa el código de los subprogramas.

Triggers Un trigger o disparador es un bloque PL/SQL asociado a una tabla, que se ejecuta como consecuencia de una determinada instrucción SQL (una operación DML: INSERT, UPDATE o DELETE) sobre dicha tabla.

22


Temporalidad del Evento: AFTER / BEFORE BEFORE: Se ejecutan la acción asociada antes de que la sentencia sea ejecutada. 



Decidir si la acción debe realizarse o no Utilizar valores alternativos para la sentencia

AFTER 

Ejecutan la acción asociada después de que se haya ejecutado la sentencia

Ejemplo creación de trigger

23


Utilización de :OLD y :NEW Dentro del ámbito de un trigger disponemos de las variables OLD y NEW. Estas variables se utilizan del mismo modo que cualquier otra variable PL/SQL, con la salvedad

de

que

no

es necesario

declararlas, son

de

tipo %ROWTYPE y contienen una copia del registro antes (OLD) y después(NEW) de la acción SQL (INSERT, UPDATE, DELTE) que ha ejecutado el trigger. Utilizando esta variable podemos acceder a los datos que se están insertando, actualizando o borrando.

24


Ejemplos varios en PL/SQL 1. Crear una vista que se llame DETALLE_PAGOS_CREDITO que contenga el nombre del cliente, apellidos, monto que adeuda y la mensualidad de todos los tipos de pago que sean a crédito.

2. Escribir un bloque PL/SQL que cuente el número de filas que hay en la tabla clientes, y guarde el resultado en la variable v_contador.

3. Realizar un procedimiento que permita sumar dos números y guardarlo en una variable llamada SUMA.

25


4. Crear una función que reciba una fecha y devuelva el año, en número, de la fecha proporcionada y lo registre en una tabla temp.

5. Crear un procedimiento que permita borrar un empleado, a partir del código del empleado como parámetro de entrada.

6. Crear un procedimiento que permita insertar un estado en una tabla llamada TM_ESTADO

26


7. Crear un procedimiento que muestre el nombre y la fecha de ingreso de los empleados ordenados por nombre, para ello utilice un cursor.

Salida al ejecutarlo:

8. Crear un trigger llamado AUDITAR_EMP_MOVIM, que permita llevar una bitácora de las operaciones de inserción o borrado de datos que se realicen en la tabla emp según las siguientes especificaciones: 

Crear una tabla llamada bitacora_emp con un campo tipo varchar llamado detalle



El trigger se debe crear para que registre en la bitácora antes de que la acción a realizar se ejecute, utilizando un before.

27




Cuando se produzca cualquier manipulación se insertará una fila en dicha tabla que contendrá: Fecha, número de empleado, y el tipo de operación realizada.

Creación de la tabla:

Creación del trigger:

Cuanto se ejecuta una acción de eliminación o inserción en la tabla emp, se dispara el trigger y registra en la tabla bitácora como se muestra en la imagen:

28


9. Crear un paquete completo para dar mantenimiento a los departamentos de la tabla DEPT. El paquete se llamará mantenimientos_deptos y deberá incluir, los siguientes subprogramas: registrar_depto: permite insertar un departamento nuevo. El



procedimiento recibe el nombre y la localidad del nuevo departamento. 

borrar_depto: permite borrar un departamento.



modificar_loc_depto: modifica la localidad del departamento. El procedimiento recibirá el número del departamento a modificar y la nueva localidad.



mostrar_depto: mostrara los datos de un departamento cuyo número se pasará como parámetro.

Creación de la cabecera:

29


Creación de cuerpo:

30


Resuelva los siguientes ejercicios: 1. Cree una secuencia llamada SEQ_TEMP, que inicie en 1 2. 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

3. Insertar en la tabla TEMP 100 filas. En la primera columna se insertará un índice secuencial (1, 2, 3...) y en la segunda columna un comentario indicando si el número generado es par o impar.

4. Crear la función BAJA_DEPT con un argumento. Dado un número de departamento, borrarlo.

5. Crear la función ALTA_DEPT con tres argumentos: número, nombre y localidad del departamento. Este procedimiento debe comprobar que los números de departamento son múltiplos de 10, si no es así daría un error

6. Crear un procedimiento utilizando cursores que muestre el nombre y el departamento de los empleados ordenándolos por departamento.

7. Crear un trigger llamado AUDITAR_DEPT_MOVIM, que permita llevar una bitácora de las operaciones de inserción o borrado de datos que se realicen en la tabla DEPT según las siguientes especificaciones: 

Crear una tabla llamada bitacora_dept con un campo tipo varchar llamado detalle



El trigger se debe crear para que registre en la bitácora despues de que la acción a realizar se ejecute, utilizando un after.



Cuando se produzca cualquier manipulación se insertará una fila en dicha tabla que contendrá: Fecha, número de departamento, nombre del departamento, y el tipo de operación realizada.

31


8. Crear un paquete completo para dar mantenimiento a los empleados de la tabla EMP. El paquete se llamará mantenimientos_empleados y deberá incluir, los siguientes subprogramas: registrar_empleado: permite insertar un empleado nuevo. El



procedimiento recibe los datos necesarios del empleado. 

borrar_empleado: permite borrar un departamento.



modificar_puesto_empleado: modificar la información del empleado. El procedimiento recibirá el número del empleado y modificara el puesto del mismo.



mostrar_empleado: mostrara los datos de un empleado cuyo número se pasará como parámetro.

9. Crear un programa que muestre el nombre y el monto del salario de los cinco empleados que tienen el salario más alto.

10. Crear un programa que muestre los dos empleados que ganan menos de cada puesto.

32


Solucionario 1. Cree una secuencia llamada SEQ_TEMP, que inicie en 1

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

3. Insertar en la tabla TEMP 100 filas. En la primera columna se insertará un índice secuencial (1, 2, 3...) y en la segunda columna un comentario indicando si el número generado es par o impar.

33


4. Crear la función BAJA_DEPT con un argumento. Dado un número de departamento, borrarlo.

5. Crear la función ALTA_DEPT con tres argumentos: número, nombre y localidad del departamento. Este procedimiento debe comprobar que los números de departamento son múltiplos de 10, si no es así daría un error

34


6. Crear un procedimiento utilizando cursores que muestre el nombre y el departamento de los empleados ordenándolos por departamento.

Salida al ejecutarlo:

7. Crear un trigger llamado AUDITAR_DEPT_MOVIM, que permita llevar una bitácora de las operaciones de inserción o borrado de datos que se realicen en la tabla DEPT según las siguientes especificaciones: 

Crear una tabla llamada bitacora_dept con un campo tipo varchar llamado detalle



El trigger se debe crear para que registre en la bitácora después de que la acción a realizar se ejecute, utilizando un after.



Cuando se produzca cualquier manipulación se insertará una fila en dicha tabla que contendrá: Fecha, número de departamento, nombre del departamento, y el tipo de operación realizada.

35


Creación de la tabla:

Creación del trigger:

Cuanto se ejecuta una acción de eliminación o inserción en la tabla DEPT, se dispara el trigger y registra en la tabla bitácora como se muestra en la imagen:

8. Crear un paquete completo para dar mantenimiento a los empleados de la tabla EMP. El paquete se llamará mantenimientos_empleados y deberá incluir, los siguientes subprogramas:

36


registrar_empleado: permite insertar un empleado nuevo. El



procedimiento recibe los datos necesarios del empleado. 

borrar_empleado: permite borrar un departamento.



modificar_puesto_empleado: modificar la información del empleado. El procedimiento recibirá el número del empleado y modificara el puesto del mismo.



mostrar_empleado: mostrara los datos de un empleado cuyo número se pasará como parámetro.

Creación de la cabecera:

Creación del cuerpo:

37


38


9. Crear un programa en PL/SQL que muestre el nombre y el salario de los cinco empleados que tienen el salario más alto.

Salida:

10. Crear un programa en PL/SQL que muestre los dos empleados que ganan menos de cada puesto.

39

cr PL-SQL Para Principiantes

Recommend Documents