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