PRESENTACIÓN DE IMÁGENES SCRIPTS PL/SQL

DESCARGAS

Descarga los scripts de todos los ejemplos que presentamos, estan en carpetas comprimidas, asi como la descargas de la galeria de fotos que se incertaron en cada Post. Son Gratis!!

Documentacion de la Web

Ver documento en linea( GoogleDocs)

Conclusión General:

Con la creación de este blog llegamos a la conclusión que es importante para los estudiantes de Sistemas Informáticos, público en general o simplemente quienes les agraden estos temas tener los conocimientos básicos y suficientes para el manejo, control y creación de una base de datos utilizando el lenguaje PL/SQL, en este blog les explicamos claramente y con ejemplos aquellos temas importantes y básicos para poder ayudarlos en caso de que tengan dudas en la materia de Base de Datos, esperando sea de su agrado y utilizad la información que aquí se da a conocer.

La creación de este blog muestra claramente ciertos temas importantes que se deben conocer para el majeo de una base de datos con el lenguaje PL/SQL, se explican  ejemplos claros y reales de cómo ir definiendo cada bloque según la acción que se quiera realizar, ya sean básicos,  anónimos, procedimientos, funciones, cursores implícitos como explícitos, para que quien ingrese pueda entender y ayudarse con cualquier ejercicio que le den en clase.

Se muestra una introducción y fundamentos básicos y generales que se deben de tener para poder manejar el lenguaje PL/SQL

Los temas que manejamos en general son cursores, bloques anónimos, procedimientos y funciones, cada tema esta explicado claramente y con ejemplos para que se le sea más fácil la comprensión a los visitantes de nuestro blog.

CREDITOS

• Presento para ustedes:

Equipo ConfigCOM! ©2011 Derechos Reservados

El equipo se conforma por:

• Lupytha A. 
• Christian R. 
• K. Sofia

Desarrollado para Bases de Datos II

Universidad Tecnológica Fidel Velázquez

Tecnologías de la Información Área Sistemas Informáticos

3er Cuatrimestre TSU

Bibliografía extra

INNOV@ Desarrollos informáticos, ©2009

Bases de Datos II

3.4 Cursor FOR…LOOP

Se utilizan para simplificar el manejo del cursor, es decir, disminuir el código y los paso que ya habíamos tocado en el tema 3.1, la estructura de este cursor es la siguiente:

Se declara la información cursor en la sección correspondiente.

Se procesa el cursor utilizando el siguiente formato:

FOR nombrevareg IN nombrecursor LOOP
….END LOOP;

Al entrar al bucle, se abre el cursor de manera automática, se declara implícitamente la variable nombrevareg de tipo nombrecursor%ROWTYPE y se ejecuta el primer FETCH, el cual, el resultado se guardara en nombrevareg. Despues se seguirán realizando las acciones que correspondan hasta llegar al END LOOP, que sube de nuevo al FOR…LOOP ejecutandoce el siguiente FETCH y deposita nuevamente el resultado en nombrevareg y asi asta que el ciclo termine, lo que dara salida al bucle y se cerrara automáticamente el cursor.

Aquí podemos ver un ejemplo de este caso:

3.3 Entre el SQL y el PL/SQL

A veces vamos a tener la necesidad de hacer una consulta con una condición, Cuando hacemos esto con SQL se hace de la siguiente forma:

Ejemplo de EquiJoin En una consulta:

En el caso de PL/SQL se usan estructuras un poco más elaboradas, veamos el mismo ejemplo pero en un PROCEDURE:

3.2 Atributos de los cursores:

Es importante conocer estos atributos para poder manejar mejor los cursores, tenemos cuatro atributos para las consultas:

• %FOUND: Este atributo devuelve verdadero si el último FETCH ha recuperado algún valor; en caso contrario, devuelve falso.  Si el cursor no estaba abierto devuelve error, y si estaba abierto pero no se había ejecutado aun ningún FETCH, devuelve NULL, tenemos el siguiente ejemplo:

• %NOTFOUND: Hace lo contrario qué %FOUND. Se usa en condición de salida de bucles por ejemplo:

• %ROWCOUNT: Devuelve el número de filas recuperadas hasta el momento por el cursor (número de FECTH realizados satisfactoriamente)

Podemos ver el sig ejemplo:

• %ISOPEN: devuelve verdadero si el cursor está abierto.

Para explicar mejor la funcionalidad de estos atributos, ponemos la siguiente tabla para su entendimiento más preciso y comparar las diferencias entre atributos:

		%FOUND	%ISOPEN	%NOTFOUND	%ROWCOUNT
OPEN	Antes	Invalid_cursor	f	Invalid_cursor	Invalid_cursor
	Después	NULL	T	NULL	0
PRIMER FETCH	Antes	NULL	T	NULL	0
	Después	T	T	F	1
SIGTES	Antes	T	T	F	1
	Después	T	T	F	…
ULTIMO FETCH	Antes	T	T	F	N
	Después	F	T	T	N
CLOSE	Antes	F	T	T	N
	Después	Invalid_cursor	F	Invalid_cursor	Invalid_cursor

3.1 Cursores Explícitos

Estos son usados para consultas que deben devolver más de una fila. Tenemos cuatro operaciones básicas para trabajar con un cursor explícito:

• Declaración del Cursor: El cursor se declara en la zona de declaraciones con el siguiente formato:

• Apertura del curso: Es la zona de instrucciones donde se abre el cursor:

Cuando hacemos esto se ejecuta automáticamente la sentencia SELECT asociada y sus datos se almacenan en las estructuras internas de memoria manejadas por el cursor. No obstante a la información debemos ir a lo que sigue…

• Recogida de información: Para recoger información que vamos a almacenar en el cursos utilizamos este formato:

El INTO indica que la variable recogerá la información de todas las columnas. Podemos utilizar el %ROWTYPE.

Esto funciona de la siguiente forma, cada vez que se recoge una columna correspondiente de la cláusula SELECT, estas serán del mismo tipo que las columnas, y cada FETCH recupera una fila y el cursor avanza automáticamente a la fila que sigue.

• Cierre del Cursor: Cuando el cursor no se va a utilizar hay que cerrarlo de la siguiente forma:

Un ejemplo de un cursor puede ser:

3.0 CURSORES PL/SQL

Existen dos tipos de cursores, los Implícitos y los Explícitos, Los cursores implícitos son muy fáciles, pero tienen varias desventajas, una de ellas y la más común es cuando en una subconsulta no devuelven más que solo una fila, de lo contrario marca error. Por eso, se tienen también los cursores explícitos. En este tema hablaremos de los cursores Implícitos y Explícitos y daremos algunos ejemplos aplicados.

Menú de subtemas de esta unidad

• 3.4 Cursor FOR…LOOP

• 3.3 Entre el SQL y el PL/SQL

• 3.2 Atributos de los cursores:

• 3.1 Cursores Explícitos

2.7 Subprogramas: ¿Que son los procedimientos y las funciones?

Un subprograma en PL/SQL son los bloques que escribimos en el código como ya lo habíamos mencionado anteriormente. Estos programas se pueden guardar en la base de datos y los podemos ejecutar de nuevo en otros programas o herramientas.

En los subprogramas de PL/SQL tenemos estas partes que son:

La cabecera o especificación del subprograma: que es la parte en la que donde encontramos la definición de los parámetros con sus tipos (y el valor de retorno en el caso de las funciones).

El cuerpo del subprograma: Es un bloque que incluye las declaraciones instrucciones y manejo de las excepciones.

PROCEDIMIENTOS:

Su estructura es la siguiente:

Debemos comenzar con la palabra PROCEDURE y termina después del ultimo parámetro. El cuerpo inicia con la palabra IS o también con AS y termina con la palabra END y opcional mente con la nombre del procedimiento.

Ejemplo de procedure:

Para Invocar el procedure es indispensable dos cosas, una de ellas es activar el paquete de interacción con el usurario del paquete DBMS_OUTPUT, e incluimos en los programas el procedimiento PUT_LINE para poder visualizar los textos en la pantalla. DBMS_OUTPUT.

Y además la variable de entorno SERVEROUTPUT deberá estar en ON, de caso contrario los programas no mostraran algún dato, y solo mostrara el texto. 

“Procedimiento terminado con éxito” entonces escribimos

Y después de activar la variable de entorno, escribimos el código para ejecutar el procedimiento:

Otro ejemplo tenemos:

Ejemplo de otro procedure:

En este ejemplo, cómo podemos ver en la línea 9, el numero del empleado “EMPNO” no tiene un valor numérico dado, este se introduce por teclado, es decir, cuando se ejecute el programa, el programa nos pedirá que ingresemos una “clave” que es este caso el numero de un empleado para poder mostrar su nombre. Esto es posible poniendo como vemos la siguiente expresión:

WHERE id=‘&clave;

Otro ejemplo es este, es como el caso anterior, pero la diferencia es que aquí si damos un valor a “EMPNO”:

FUNCIONES:

Estas tienen una estructura muy similar a los procedimientos, la diferencia es que estas devuelven un valor.

Veamos su anatomía:

La cláusula RETURN de la cabecera significa que el valor retorna una función, En el cuerpo del programa se hará efectivo ere retorno utilizando dicha cláusula junto con la expresión el cual dará el valor de retorno.

NOTA: podemos crear o reemplazar una función escribiendo:

CREATE OR REPLACE FUNCTION <Nombre_funcion>

Un ejemplo de una funciones la siguiente:

Otro ejemplo tenemos:

2.6 Estructuras de control en PL/SQL

Así como en lenguajes como Java, C#, C++, .NET entre otros más, tienen las llamadas estructuras de control que influyen en el flujo de las instrucciones de la ejecución del programa, también en PL/SQL los podemos ejecutar. Consiste en dar una expresión, y de ser verdadera (TRUE) pasara a hacer una proceso 1, de ser falsa (falce) procederá  a hacer proceso 2., o de ser un caso anidado o múltiple, hará n número de procesos hasta terminar con la sentencia o llegar al fin del programa.

Estructura “SI”, ENTONCES…, SINO: es muy usado para comparaciones y sus diferentes formas de expresión. Alternativa simple, Alternativa doble, Alternativa múltiple.

Interar… fin interar salir: Se trata de un bucle que se repetirá hasta encontrar la cláusula EXIT y es de la forma siguiente:

Mientras: El bucle se ejecuta mientras se cumpla la condición:

Para: Se usa para indicar el número de veces en el que se debe ejecutar el bucle:

2.5 Operadores en PL/SQL

Como en cualquier otros lenguajes de programación, aquí en PL/SQL también hay operadores que los utilizamos para hacer expresiones, concatenación, comparaciones y de procedencia. Explicaremos los más importantes y frecuentes.

Valor de asignación: cuando queremos asignar una constante por ejemplo, se utiliza el operador := que indica que le estamos asignando un valor (eje valor_pi:=3.1416)

Valores lógicos: Estos son, como en todos los lenguajes de programación AND, OR, y NOT, pero como sabemos hay campos de las tablas que pueden ser nulos (Null) así que a continuación se describen los valores y su resultado en la siguiente tabla:

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	TRUE
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	TRUE

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

NOT	TRUE	FALSE	NULL
	FALSE	TRUE	NULL

Concatenación: Algunas veces es necesario unir cadenas de mensaje para que nuestro resultado tenga más sentido, por ejemplo, cuando hacemos una consulta del nombre del empleado con su salario, cuyo ID es el 20 por ejemplo. Nos mostrara algo así: JUAN 10000, pero no es lo mismo leerlo así, que de esta otra forma: El salario de JUAN es de 10000 pesos, para eso se usa el operador “||” Ejemplo:

Comparación: Como su nombre lo indica, compara datos. Igual =, distinto !=, menos que <, mayor que >, menor o igual que <=, mayor o igual que >=.

Aritméticos: Tenemos unos que son iguales a los que se usan en SQL, como son IS NULL, BETWEN, LIKE, IN. Y tenemos los operadores aritméticos, estos son los que siempre utilizamos para hacer operaciones (+,-,*,/,**)

2.4 Usos de atributos %TYPE y %ROWTYPE

En PL/SQL podemos indicar el tipo de dato que es y su longitud escribiendo la palabra reservada y cualquier tipo de dato descrito en el tema anterior. [nombre VARCHAR2(20);] pero imaginemos que el tipo de dato que usaremos el mismo como el que tenemos en nuestra tabla. Entonces utilizamos %TYPE para especificar que esta variable, es del mismo tipo y longitud que de tipo de dato que tenemos en la base de datos.

Por ejemplo, si creamos esta tabla en SQL:

Tenemos que estamos creando un campo con el nombre “n_prest” Que es el ID de nuestros registros, una llamada “monto” que es de tipo “number” y de longitud de cinco “5” y es “not null”, así como la de intereses que es parecido al de “monto” y la de “fecha” que es un tipo de dato “date” .

Cuando nosotros creamos el programa en PL/SQL y vamos a crear variables parecidas a los datos que tenemos en la tabla, podemos escribir:

Donde (línea 1) id_prestamo es el nombre de nuestra nueva variable que estamos declarando préstamo.n_prest es el nombre de la tabla procedido del nombre de la columna de la tabla que creamos anteriormente y %type nos está indicando que la variable que estamos creando id_prestamo es de la misma longitud y tipo que la columna n_prest que se encuentra en la tabla llamada préstamo.

El atributo %ROWTYPE crea una variable de registro cuyos campos corresponden con las columnas de una tabla.

Algo importante que se debe mencionar es que estos atributos estamos heredando el tipo y la longitud de nuestra columna a nuestras variables, pero NO heredamos los atributos NOT NULL ni otros valores por defecto.

2.3 Identificadores, variables, constantes.

Los identificadores son  expresiones en programación para nombrar a los objetos que vamos a utilizar en un programa, son en su caso, las variables, las constantes, cursores, excepciones, procedimientos, funciones, entre otras cosas. Pueden tener hasta 30 caracteres y deben de empezar siempre por una letra. Y después pueden ir caracteres como más letras, números, el signo del dólar ($) de almohadilla y el guion bajo (_).

Ejemplo:

Caso permitido: v_variable#$, x2_constante, anio_nac, v__1_nombre

Caso no permitido:  _v, #aA, 2x_const, v-1nombre.

Una ventaja es que en PL/SQL no se identifican entre letras mayúsculas y/o minúsculas:

V_NOMBRE = v_nombre = V_Nombre

Variables

Todas las variables deben de declararse en PL/SQL, porque, de no ser así, el programa no sabe a dónde asignar loa información, o en su efecto no sabrá el tipo de dato y uso específico dentro del programa.

Su estructura es la siguiente:

Constantes

Son valores que ya tienen un valor dado, o las creamos para asignrle uno en especial que cumplirica con alguna función en el programa. Y sean de tipo enteros o cadenas (NUMBER ó VARCHAR2).

2.1 Los datos de PL/SQL

PL/SQL dispone además de los datos de SQL (como NUMBER, VARCHAR2, CHAR, DATE, etc.) también tiene otros propios de PL/SQL.

A continuación se muestran los distintos tipos de datos de PL/SQL:

• NUMBER (Numérico): Almacena números enteros o flotantes.

·         saldo NUMBER(16,2);

• CHAR (Carácter): Almacena datos de tipo carácter con una longitud máxima de 32767

·         nombre CHAR(20);

• VARCHAR2 (Carácter de longitud variable): Almacena datos de tipo carácter empleando sólo la cantidad necesaria aun cuando la longitud máxima sea mayor.

·         nombre VARCHAR2(20);

• BOOLEAN (lógico): Se emplea para almacenar valores verdaderos o falsos (TRUE o FALSE.)

·         hay_error BOOLEAN;

• DATE (Fecha): 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.

A continuacion se mostrara una tabla con los datos escalares de PL/SQL y su descripción:

dec(p, e):	Donde p es la precisión y e la escala.
decimal(p, e):	Donde p es la precisión y e la escala.
float	Numero de punto flotante, es decir con decimales
int	Numeos enteros
integer	Numeros enteros
char (tamaño):	Donde tamaño es el número de caracteres a almacenar. Son cadenas de ancho fijo. Se rellena con espacios.
varchar2 (tamaño):	Donde tamaño es el número de caracteres a almacenar. Son cadenas de ancho variable.
Long:	Son cadenas de ancho variable.
Raw:	Son cadenas binarias de ancho variable.
long raw:	Son cadenas binarias de ancho variable.
rowid:	Datos binarios de ancho fijo. Cada registro de la base de datos tiene una dirección física
urowid [tamaño]:	Rowid universal. Donde tamaño es opcional.
nchar (tamaño):	Donde tamaño es el número de caracteres a almacenar. Cadena NLS de ancho fijo.
nvarchar2 (tamaño):	Donde tamaño es el número de caracteres a almacenar. Cadena NLS de ancho variable.
Bfile:	Localizadores de archivo apuntan a un objeto binario de sólo lectura fuera de la base de datos.
Blob:	Localizadores LOB apuntan a un gran objeto binario dentro de la base de datos.
Clob:	Localizadores LOB apuntan a un gran objeto de caracteres dentro de la base de datos.
Nclob:	Localizadores LOB apuntan a un gran objeto NLS de caracteres dentro de la base de datos.

2.0 FUNDAMENTOS DEL LENGUAJE PL/SQL

Como pudimos ver, en los temas anteriores se dio una explicación de lo que es PL/SQL y pudimos ver parte de sus estructuras, y la forma en la que se componen. Ahora es momento de ver lo necesario para poder desarrollar los lenguajes de programación.

**Menú de subtemas de esta unidad**

• 2.7 Subprogramas: ¿Que son los procedimientos y las funciones

• 2.6 Estructuras de control en PL/SQL

• 2.5 Operadores en PL/SQL

• 2.4 Usos de atributos %TYPE y %ROWTYPE

• 2.3 Identificadores, variables, constantes.

• 2.1 Los datos de PL/SQL

1.2 ¿Que datos son compatibles con PL/SQL?

PL/SQL dispone de tipos de datos que son compatibles con los tipos utilizados para las columnas de las tablas, como son por ejemplo: NUMBER, VARCHAR2, DATE y otros como son BOOLEAN.

Estas se declaran en la sección de declaraciones como ya lo habíamos mencionado en el tema anterior.

1.1 Características del lenguaje PL/SQL

Como ya mencionamos en el post anterior, PL/SQL  es un lenguaje desarrollado por ORACLE y para trabajar con bases de datos. Soporta todos los comandos de consulta y manipulaciones de datos. Su unidad de trabajo es el bloque, que lo conformamos por líneas de código, los cuales pueden ser declarados, pueden ser instrucciones y mecanismos de error y excepciones.

Los programas que podemos hacer con PL/SQL son:

Procedimientos >>Link para ver tema

Funciones >>Link para ver tema

Todos estos programas tienen en común su estructura, a esa estructura se le conoce como BLOQUE

Anatomía del BLOQUE

> Zona de declaraciones: Donde se declaran los objetos (varíales y constantes) dentro del programa. Se inician con la cláusula DECLARE y se usa IS o AS en los procedimientos y funciones. Sépase que esta parte es opcional.

> El conjunto de las instrucciones, procedidas  de la cláusula BEGIN

> La zona de las excepciones, en esta zona se procede por la cláusula EXEPTION

En si forma estructural es:

Tenemos el ejemplo como el que sigue.