cr pl-sql para principiantes

8/19/2019 cr PL-SQL Para Principiantes

1/40

PROYECTO

MANUAL DE PL SQL PARA PRINCIPIANTES


2/40

Universidad Tecnológica Costarricense

2

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

Resuelva los siguientes ejercicios: ................................................................... 31

Solucionario ...................................................................................................... 33


3/40


3

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 elentorno del cliente.

Los programas o PL-SQL Packages en Oracle solo pueden ser accesados o

ejecutados por los usuarios debidamente autorizados.


4/40


4

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.


5/40


5

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.


6/40


6

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 dedatos (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.


7/40


7

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


8/40


8

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)

Aritméticos

+ (suma)

- (resta)

* (multiplicación)

/ (división)** (exponente)


9/40


9

Relacionales o de Comparación

= (igual a)

(distinto de)

< (menor que)

> (mayor que)>= (mayor o igual a)


10/40


10

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


11/40


11

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 unaclave 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:


12/40


12

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.


13/40


13

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 nodevuelve 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:


14/40


14

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:


15/40


15

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 dichopará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


16/40


16

necesario liberar áreas de la SGA, Oracle aplica el algoritmo „menos-usado-

recientemente‟. 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


17/40


17

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;


18/40


18

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:


19/40


19

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.


20/40


20

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.


21/40


21

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 cuerpocontiene 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


22/40


22

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 seejecuta como consecuencia de una determinada instrucción SQL (una operación

DML: INSERT, UPDATE o DELETE) sobre dicha tabla.


23/40


23

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


24/40


24

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.


25/40


25

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.


26/40


26

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


27/40


27

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.


28/40


28

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:


29/40


29

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. Elprocedimiento 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:


30/40


30

Creación de cuerpo:


31/40


31

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 unincremento 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 dedepartamento, 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


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.


dicha tabla que contendrá: Fecha, número de departamento, nombre

del departamento, y el tipo de operación realizada.


32/40


32

8. Crear un paquete completo para dar mantenimiento a los empleados de la

tabla EMP. El paquete se llamará mantenimientos_empleados y deberá


registrar_empleado: permite insertar un empleado nuevo. Elprocedimiento 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


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.


33/40


33

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.


34/40


34

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 losnúmeros de departamento son múltiplos de 10, si no es así daría un error


35/40


35

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


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 deque la acción a realizar se ejecute, utilizando un after.


dicha tabla que contendrá: Fecha, número de departamento, nombre

del departamento, y el tipo de operación realizada.


36/40


36

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á



37/40


37

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


Creación de la cabecera:

Creación del cuerpo:


38/40


38


39/40


39

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.


40/40


Salida:

cr pl-sql para principiantes

Documents