Bloques PL/SQL básicos.
Esta lección presenta la estructura básica de un bloque de código
PL/SQL. Estos bloques de código son una especie de scripts, que se
transfieren por completo al SGBD y son ejecutados por completo, lo cual
permite una secuenciación entre las distintas consultas y acciones sobre la
base de datos. La estructura básica de un bloque de código SQL consta de las
siguientes dos partes principales que comienzan con las siguientes palabras
reservadas:
'DECLARE' (opcional): A Partir de esta se declaran las variables,
los cursores, y las excepciones (errores) declaradas por el usuario.
'BEGIN': Después de esta palabra, comienza la segunda parte y
en ella se incluyen los mandatos SQL y PL/SQL.
'EXCEPTION' (opcional): A partir de esta palabra reservada se
incluye la definición de las excepciones del bloque PL/SQL.
'END;' : Esta es la palabra final después de la cual, no hay más
código.
Se debe colocar un punto y coma (;) después de cada sentencia SQL o
PL/SQL y una barra invertida (/) para poder ejecutar el bloque.
Existen varios tipos de bloques PL/SQL, anónimos, procedimientos y
funciones. Los bloques anónimos son bloques sin nombre empotrados dentro
de una aplicación o emitidos para su ejecución interactivamente. El resto,
tanto los procedimientos, como las funciones, son bloques de código PL/SQL
que quedan grabados en el servidor de Oracle, pueden ser invocados
repetidamente por su nombre y pueden aceptar parámetros.
Comencemos describiendo las variables. Estas son utilizadas para
guardar datos temporalmente, manipular estos datos etc. Para su
manipulación, debemos declararlas, le asignamos un valor, pasamos los
valores y vemos los resultados. También se pueden declarar variables
globales, lo cual se realiza antes de la palabra reservada DECLARE. Para la
declaración de variables se usa la siguiente sintaxis:
nombre_variable tipo_de_dato;
Y para las variables globales (BIND), se realiza mediante la siguiente
sintaxis:
VARIABLE nombre_variable tipo_de_dato
Este tipo de variables, se declaran fuera del bloque de código PL/SQL,
y se pueden referenciar dentro de este poniendo dos puntos antes del nombre
de la variable.
El operador de asignación de valores a una variable es ':=', poniendo
primero el nombre de la variable a la cual queremos asignar el valor, después
el símbolo ':=' y después el valor que queremos asignarle, que puede ser el
valor de otra variable por ejemplo.
Para imprimir por pantalla, siempre hay que declarar variables globales,
y estas se referencian dentro del bloque PL/SQL añadiendo dos puntos (:)
antes del nombre de la variable, ej: ':v_ejemplo'. El resto de las variables no
pueden ser aceptadas como parámetro de la orden 'PRINT' (La orden PRINT,
seguida de un nombre de variable y un punto y coma muestra el contenido de
la variable por pantalla, y el nombre de la variable que posee dicho valor.).
El uso de las variables esta restringido únicamente por el tipo de datos
al que pertenece una determinada variable. Es decir, es importante asignar a
una variable, datos del tipo al cual pertenecen, por lo que es útil la utilización
de funciones antes comentadas como 'to_char()', para pasar un tipo de dato
fecha ('date') a cadena por ejemplo si vamos a asignarlo a una cadena. Por
ejemplo, si declaramos la variable 'v_cadena' de tipo varchar2 así:
DECLARE
v_cadena varchar2(30);
.........
Y a continuación escribimos en la parte de código ejecutable:
BEGIN
v_cadena:='Anderson'||':'||sysdate;
...........
Esto daría un error, ya que sysdate, es de tipo 'date', con lo que el
bloque no se ejecutaría correctamente. Así, la correcta formulación que
deberíamos realizar para esta acción sería:
BEGIN
v_cadena:='Anderson'||':'||to_char(sysdate);
...........
Nótese que se han utilizado comillas simples para delimitar los valores
de cadenas constantes, el operador '||' para concatenar cadenas, y un punto y
coma al final de la sentencia.
Hasta aquí se ha visto lo mas importante que debemos saber sobre las
variables, a partir de ahora describiremos las acciones básicas que se pueden
realizar con un bloque de código PL/SQL.
Los bloques de código se pueden anidar. Es decir podemos incluir un
bloque de PL/SQL dentro de otro, pero hay que tener cuidado con el ámbito
de las variables que declaramos, es decir, las variables declaradas en el bloque
más exterior, se 'ven' dentro de los bloques de código interiores, pero esto no
es cierto al contrario, es decir, las variables de los bloques interiores, no
pueden referenciarse por los bloques externos.
La orden para pedir datos por pantalla se ha explicado en temas
anteriores, esta es la orden 'accept' que debe ponerse fuera de los bloques
PL/SQL, con la siguiente sintaxis:
ACCEPT nombre_variable PROMPT 'mensaje_introducción';
De esta manera se introducen datos en una variable de entorno, que
podemos utilizar luego. Para borrar todas las variables de entorno que
tengamos creadas, deberemos introducir la orden 'set verify off'. Para
referenciarlas, se incluye un '&' antes del nombre de dicha variable.
Para la salida de datos por pantalla, se utiliza la orden
dbms_output.put_line con la siguiente sintaxis:
dbms_output.put_line({'cadena_a_mostrar',variable_de_cadena})
;
Dentro de los bloques de PL/SQL, podemos escribir diferentes tipos de
consultas. El mandato select que podemos introducir dentro de un bloque de
código debe incluir después de la lista de columnas a mostrar (SELECT *) la
palabra reservada INTO nombre_variable, para introducir los datos dentro de
la variable que se especifique. Así la sintaxis de un mandato select básico
queda de la siguiente manera:
SELECT [DISTINCT] {*, columna [alias],...}
INTO {lista_de variables}
FROM nombre_tabla
[WHERE condition(s)];
Así, podemos deducir que la lista de variables, debe corresponderse con
el número y el tipo de las columnas que estemos seleccionando, para que sean
asignadas a las variables correctamente. También debemos notar que estos
mandatos select, deben devolver tan solo una fila, es decir que debemos
incluir una condición que haga que la consulta devuelva una sola fila en el
momento de ser ejecutada.
Ahora ya estamos preparados para ver el primer ejemplo de bloque
PL/SQL mediante el cual obtendremos el número y la localización de el
departamento de ventas (sales) de la tabla dept así:
DECLARE
v_deptno [Link]%TYPE;
v_loc [Link]%TYPE;
BEGIN
select deptno,loc
into v_deptno,v_loc
from dept
where dname='SALES';
dbms_output.put_line(v_deptno);
dbms_output.put_line(v_loc);
END;
/
Que ofrece a la salida el siguiente mensaje:
SQL> @a:/[Link]
30
CHICAGO
PL/SQL procedure successfully completed.
SQL>
Se debe de destacar que el tipo de dato al cual pertenecen las variables
declaradas v_deptno y v_loc, es el tipo de dato de las variables detpno y loc de
la tabla dept.
Vamos a realizar ahora una inserción de datos en una tabla mediante un
bloque PL/SQL para ver más ejemplos:
DECLARE
v_empno [Link]%TYPE;
BEGIN
select empno_secuence.NEXTVAL
into v_empno
from dual;
insert into emp(empno, ename, job, deptno)
values (v_empno,'Harding','clerk', 10);
END;
/
Suponiendo que ya tenemos creada la secuencia empno_sequence, que
nos devuelve el valor correcto de número de empleado. Es de notar el uso de
la variable declarada como valor a introducir en la orden insert.
Dentro de las ordenes de un bloque PL/SQL también existen sentencias
de control del flujo del programa y dentro de ellas vamos a comenzar con el
mandato IF. Este mandato tiene la siguiente sintaxis:
IF condición THEN
sentencias_1;
[ELSIF condición THEN
sentencias_2;]
[ELSE
sentencias_3;]
ENDIF;
Siendo el control de flujo el mismo para todos los lenguajes que poseen
una sentencia de bifurcación condicional, es decir , se ejecutarán la(s)
sentencia(s) 1 en caso de cumplirse las primeras condiciones, o las sentencias
2 en caso de que se cumplan las segundas condiciones, o si no se cumplen
ningunas de las condiciones, se ejecutaran las sentencias 3.
Así, podemos realizar bloques de código que realicen acciones en
función de el cumplimiento de ciertas condiciones.
Otro tipo de sentencias de control del flujo de ejecución son los bucles,
dentro de los cuales podemos encontrar bucles tales como LOOP, FOR y
WHILE. La sintaxis de estos bloques de código es la siguiente:
LOOP
sentencias:
...
EXIT [WHEN condición];
END LOOP;
Para la orden FOR tenemos la siguiente sintaxis:
FOR contador IN [REVERSE] limite_inferior..limite_superior
LOOP
sentencias;
...
END LOOP;
Y para la orden WHILE tenemos la siguiente sintaxis:
WHILE condicion LOOP
sentencias;
...
LOOP;