Sentencias Ejecutables

Objetivos:

• Identificar las unidades léxicas en un bloque PL/SQL.
• Utilizar funciones de SQL en PL/SQL.
• Describir cuando usar las conversiones explícitas e implícitas.
• Escribir bloques anidados y identificar las variables con etiquetas/alias.
• Utilizar secuencias en expresiones de PL/SQL.
• Escribir código legible con Indentación apropiada.

NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

____________________________________________________________________________________

Unidades Léxicas en Bloques PL/SQL.

Las unidades léxicas:

• Son conjuntos de instrucciones en cualquier bloque PL/SQL.
• Son secuencias de caracteres, incluyendo letras, números, espacios, tabulaciones, retornos y símbolos.
• Se pueden clasificar como:
--Identificadores: v_fname, c_percent
--Delimitadores:; , +, -
--Los literales: Juan, 428, True
--Comentarios: -, /* */

Las unidades léxicas incluyen letras, números, caracteres especiales, espacios, tabulaciones, retornos y símbolos.
• Identificadores: identificadores son los nombres dados a los objetos PL/SQL. En publicaciones anteriores vimos los identificadores válidos y no válidos. Recordemos que las palabras clave no se pueden usar como identificadores.
• Identificadores con Comillas Dobles:
--Son sensibles a mayúsculas y minúsculas.
--Soportan caracteres especiales tales como espacios.
--Pueden contener palabras reservadas.
Ejemplos:
    "Fecha Inicio"             DATE;
    "fecha Fin"                  DATE;
    "Excepción lanzada?"   BOOLEAN DEFAULT TRUE;
Todo el posterior uso de estas variables debe tener comillas dobles. Sin embargo, no se recomienda el uso de identificadores entre comillas.
• Delimitadores: son símbolos que tienen un significado especial. Ya vimos que el punto y coma (;) se utiliza para terminar una sentencia de SQL o PL/SQL. Por lo tanto, el (;) es un ejemplo de un delimitador.

A continuación un subconjunto de delimitadores disponibles en PL/SQL.

Delimitadores Simples:

Signo	Definición
+	Operator de Suma
-	Operator de Sustracción/Negación.
*	Operator de Multiplicación
/	Operator de División
=	Operator de Igualdad
@	Indicador de Acceso Remoto
;	Terminador de Sentencia

Delimitadores Compuestos:

Signo	Definición
<>	Operator de Desigualdad
!=	Operator de Desigualdad
||	Operator de Concatenación
--	Indicador para Comentario de una Linea
/*	Indicador para Inicio de Comentario(Varias Lineas)
*/	Indicador para Fin de Comentario(Varias Lineas)
:=	Operador de Asignación

• Los Literales: Es cualquier valor que se asigna a una variable. Cualquier valor de carácter, número, booleano, o una fecha que no es un identificador es un literal. Los literales se clasifican en:

--Literales de Caracteres: Son todas las cadenas con tipo de dato CHAR o VARCHAR2 (por ejemplo, 'Juan', y '12C').

--Literales Numéricos: Un literal numérico representa un valor real o entero (por ejemplo, 428 y 1.276).

--Literales Booleanos: Son los valores que se asignan a las variables booleanas. Ejemplo: TRUE, FALSO, y NULL.

• Comentarios: Es una buena práctica de programación para explicar lo que una pieza de código está tratando de lograr. Es bueno aclarar que el compilador no puede interpretar los /*Comentarios*/, ellos solo sirven de referencia para el programador.

--Los dos guiones (--) se utilizan para comentar una sola línea.

--Los delimitadores de comienzo y fin de comentario (/* y */) se utilizan para comentar varias líneas.

Uso de Literales.

Un literal es un valor numérico explícito, cadena de caracteres, fecha o valor booleano que no está representado por un identificador.

• Los literales de Carácter y las Fecha deben ir entre comillas simples.

• Los literales de caracteres incluyen todos los caracteres imprimibles en el juego de caracteres  de PL/SQL: letras, números, espacios y símbolos especiales.

• Los literales numéricos se pueden representar ya sea por un valor simple (por ejemplo, -32,5) o en notación científica (por ejemplo, 2E5 significa  2 * 105 = 200.000).

____________________________________________________________________________________

Funciones de SQL en PL/SQL.

SQL provee varias funciones predefinidas que se pueden utilizar en las instrucciones SQL. La mayoría de estas funciones (como funciones de una sola fila(numéricas y de caracteres), funciones de conversión y funciones de fecha) son válidas en expresiones PL/SQL.

Las siguientes funciones no están disponibles en sentencias de PL/SQL:

• DECODE

• NVL2

• Las funciones de grupo: AVG, MIN, MAX, COUNT, SUM, STDDEV, y VARIANCE. Las funciones de grupo se aplican a grupos de filas en una tabla y están, por lo tanto, disponible sólo en instrucciones SQL en un bloque PL/SQL. Las funciones mencionadas anteriormente son sólo un subconjunto de la lista completa.

Ejemplo:

SET SERVEROUTPUT ON
DECLARE
    v_fecha_entrada       DATE;
    v_tiempo              NUMBER;
    v_comision            NUMBER(3,2);
    v_nombre              VARCHAR2(50);
    v_longitud_nom        NUMBER(2);
    "Obtiene Comision?"   CHAR(2);
BEGIN
     SELECT
            first_name||' '||last_name,
            commission_pct,
            hire_date
     INTO   v_nombre,
            v_comision,
            v_fecha_entrada
     FROM employees
     WHERE employee_id = 100;
     
     --Determinamos la longitud del nombre completo del empleado:
     v_longitud_nom := LENGTH(v_nombre);
     --Determinamos si recibe comisión:
     "Obtiene Comision?" := NVL(TO_CHAR(v_comision), 'NO');
     --Determinamos los años que tiene en la empresa:
     v_tiempo := TRUNC(MONTHS_BETWEEN(SYSDATE, v_fecha_entrada)/12);
     
     --Imprime las Variables:
     DBMS_OUTPUT.PUT_LINE('Nombre: '||v_nombre||', Longitud del Nombre: '||v_longitud_nom||', Comisión: '||"Obtiene Comision?");
     DBMS_OUTPUT.PUT_LINE('Fecha Contratación: '||v_fecha_entrada||', Años en la Empresa: '||v_tiempo);
END;
/*Este ejemplo nos muestra como usar las funciones de SQL en un Bloque de PL/SQL; Luego de extraer los datos necesarios(En el SELECT), procedemos a aplicar las funciones SQL de la siguiente manera: 
• Usamos LENGTH para determinar la longitud de la cadena contenida en la variable v_nombre, y el resultado se lo asignamos a la variable v_longitud_nom.
• Usamos TO_CHAR para convertir a CHAR el valor contenido en v_comision y así hacerlo compatible con la cadena 'NO', esto porque los dos parámetros usados en la función NVL deben ser del mismo tipo; Si la expresión  TO_CHAR(v_comision) resulta nula la función NVL retorna 'NO' y este valor es asignado a la variable: "Obtiene Comision?".
• Usamos MONTHS_BETWEEN para determinar la cantidad de meses desde la fecha de contratación(hire_date) y la fecha actual(SYSDATE), dividimos la cantidad de meses entre 12 para determinar los años y por último usamos la función TRUNC para eliminar los decimales y ese valor es asignado a la variable: v_tiempo.
*/
---OUTPUT:

____________________________________________________________________________________

Secuencias en PL/SQL.
En Oracle Database 11g, es posible utilizar las pseudocolumnas NEXTVAL y CURRVAL en cualquier contexto PL/SQL, donde sea necesario una expresión del tipo de dato NUMBER. Aunque aun es posible utilizar un SELECT para consultar una secuencia, el uso directo es mas recomendable.

Antes era necesario escribir una sentencia SQL para poder usar algún valor(NEXTVAL o CURRVAL) de una secuencia en un bloque PL/SQL. 

Como esto creaba un problema de usabilidad, a partir de la version 11g es posible usar una secuencia en cualquier bloque PL/SQL.

Ejemplo:

DECLARE

    v_nuevo_emp     NUMBER(6);

BEGIN

  v_nuevo_emp :=  EMPLOYEES_SEQ.NEXTVAL;

END;

/*Este ejemplo muestra como usar una secuencia en un bloque PL/SQL.*/

____________________________________________________________________________________

Conversiones de Tipo de Dato.

En cualquier lenguaje de programación, la conversión de un tipo de dato a otro es un requisito común. Es por ello que PL/SQL ofrece conversiones con los tipos de datos escalares. Las conversiones de tipos de datos pueden ser de dos tipos:
Conversiones Implícitas: PL/SQL intenta convertir tipos de datos dinámicamente si es necesario en una sentencia.

Ejemplo:
DECLARE
      v_salario NUMBER(6):=6000;
      v_aumento VARCHAR2(5):='1000';
      v_salario_total v_salario%TYPE;
BEGIN
      v_salario_total :=  v_salario+v_aumento;
END;

/*En este ejemplo, la variable v_aumento es del tipo VARCHAR2. Cuando se calcula el salario total, PL/SQL primero convierte v_aumento a número, y luego realiza la operación. El resultado es del tipo de número.*/

---

Conversiones Explícitas: Las usamos para convertir valores de un tipo de datos a otro, para ello usamos las funciones incorporadas en SQL. Por ejemplo, para convertir un valor CHAR a un valor de fecha o número, utilice TO_DATE y TO_NUMBER, respectivamente.

Funciones:

TO_CHAR

TO_DATE

TO_NUMBER

TO_TIMESTAMP

Ejemplo:

DECLARE

      v_fecha_inicio DATE;

BEGIN

      v_fecha_inicio  :=  TO_DATE('February 02,2000','Month DD, YYYY');

END;

/*En este ejemplo, la función TO_DATE se utiliza para convertir de forma explícita la fecha dada en un formato determinado y asignarlo a la variable v_fecha_inicio del tipo de dato DATE.*/

____________________________________________________________________________________

Bloques Anidados.

PL/SQL provee la capacidad de anidar bloques. Puede anidar bloques en cualquier lugar que una sentencia ejecutable sea permitida(sección ejecutable, sección de excepciones), por lo cual el bloque anidado se convierte en una sentencia. Si su sección ejecutable contiene código de forma lógica para muchas funcionalidades relacionadas y con ellos respaldar múltiples requerimientos del negocio, entonces puede dividir la sección ejecutable en bloques más pequeños. Como ya apuntamos, la sección de excepciones también puede contener bloques anidados.

Ejemplo:

SET SERVEROUTPUT ON

DECLARE

    v_variable_externa VARCHAR2(20) := 'VARIABLE GLOBAL';

BEGIN

  DECLARE

    v_variable_interna VARCHAR2(20) :=  'VARIABLE LOCAL';

  BEGIN

    DBMS_OUTPUT.PUT_LINE(v_variable_interna);

    DBMS_OUTPUT.PUT_LINE(v_variable_externa);

  END;

  DBMS_OUTPUT.PUT_LINE(v_variable_externa);

END;

/*El ejemplo mostrado tiene un bloque externo (padre) y un bloque anidado (hijo). La variable v_variable_externa se declara en el bloque exterior y la variable v_variable_internase declara en el bloque interno.

v_variable_externa es local en el bloque exterior y global al bloque interior. Cuando se accede a esta variable en el bloque interior, PL/SQL primero busca una variable local en el bloque interno con ese nombre. No hay una variable con el mismo nombre en el bloque interior, por lo cual PL/SQL busca la variable en el bloque exterior. Por lo tanto, v_variable_externa se considera como variable global para todos los bloques adjuntos o sub-bloques. Se puede acceder a esta variable en el bloque interno como se muestra en el ejemplo. Las variables declaradas en un bloque PL/SQL se consideran como locales en ese bloque y global a todos sus sub-bloques.

v_variable_interna es local en el bloque interno y no es global, porque el bloque interno no tiene ningún bloque anidado. Esta variable sólo se puede acceder dentro del bloque interior. Si PL/SQL no encuentra la variable definida localmente, busca hacia arriba en la parte declarativa de los bloques padres. PL/SQL no busca hacia abajo en los bloques hijos.*/

---OUTPUT:

____________________________________________________________________________________

Alcance y Visibilidad de las Variables.

Ejemplo:

SET SERVEROUTPUT ON

DECLARE

    v_nombre_padre VARCHAR2(20) :=  'Candelario';

    v_fecha_nacimiento DATE :=  '20-Apr-1972';

BEGIN

  DECLARE

    v_nombre_hijo VARCHAR2(20) :=  'Alfonso';

    v_fecha_nacimiento DATE:=  '12-Dec-2002';

  BEGIN

    DBMS_OUTPUT.PUT_LINE('Nombre del Hijo: '||v_nombre_hijo);

    DBMS_OUTPUT.PUT_LINE('Fecha de Nacimiento: '||v_fecha_nacimiento);

    DBMS_OUTPUT.PUT_LINE('Nombre del Padre: '||v_nombre_padre);

  END;

  DBMS_OUTPUT.PUT_LINE('Fecha de Nacimiento: '||v_fecha_nacimiento);

END;

/*Notar las fechas de nacimiento que se imprimen para el padre e hijo. La salida proporciona información correcta, porque el alcance y la visibilidad de las variables se aplican correctamente.*/

---OUTPUT:

• El alcance de una variable es la parte del programa en el que se declara la variable y es accesible.

• La visibilidad de una variable es la parte del programa donde la variable se puede acceder sin necesidad de utilizar un alias.

Alcance

• La variable v_nombre_padre y la primera aparición de la variable v_fecha_nacimiento se declaran en el bloque exterior. Estas variables tienen el alcance del bloque en el que se declaran y de los sub-bloques.

• Las variables v_nombre_hijo y v_fecha_nacimiento se declaran en el bloque interior o bloque anidado. Estas variables son accesibles solamente dentro del bloque anidado y no son accesibles en el bloque exterior. Cuando una variable está fuera de alcance, PL/SQL libera la memoria utilizada para almacenar la variable; por lo tanto, no puede hacer referencia a estas variables.

Visibilidad

• La variable v_fecha_nacimiento declarada en el bloque exterior tiene un alcance incluso en el bloque interior. Sin embargo, esta variable no es visible en el bloque interno debido a que el bloque interno tiene una variable local con el mismo nombre.

--Notar que puede imprimir el nombre del padre, el nombre del hijo, y la fecha de nacimiento del hijo en el bloque interno. Mientras que sólo el nombre del padre y su fecha de nacimiento pueden ser impresos en el bloque exterior.

Nota: No se puede tener variables con el mismo nombre en un bloque. Sin embargo, como se muestra en este ejemplo, se puede declarar variables con el mismo nombre en dos bloques diferentes (bloques anidados).

____________________________________________________________________________________

Uso de Alias en Bloques Anidados.
Un alias es un nombre dado a un bloque. Puede utilizar un alias para acceder a las variables que tienen alcance pero no son visibles.

Ejemplo:
SET SERVEROUTPUT ON
BEGIN <<externo>>
DECLARE
    v_nombre_padre VARCHAR2(20) :=  'Candelario';
    v_fecha_nacimiento DATE :=  '20-Apr-1972';
BEGIN  <<interno>>
  DECLARE
    v_nombre_hijo VARCHAR2(20) :=  'Alfonso';
    v_fecha_nacimiento DATE :=  '12-Dec-2002';
  BEGIN 
    DBMS_OUTPUT.PUT_LINE('Nombre del Hijo: '||interno.v_nombre_hijo);
    DBMS_OUTPUT.PUT_LINE('Fecha de Nacimiento: '||interno.v_fecha_nacimiento);
    DBMS_OUTPUT.PUT_LINE('Nombre del Padre: '||externo.v_nombre_padre);
    DBMS_OUTPUT.PUT_LINE('Fecha de Nacimiento: '||externo.v_fecha_nacimiento);
  END;
END interno;
END externo;
/*Ahora notamos como con el uso de alias podemos imprimir todas nuestras variables desde el bloque interno, esto a su vez facilita la lectura del código.*/
---OUTPUT:

____________________________________________________________________________________

Uso de Operadores en PL/SQL.
Las operaciones en una expresión se realizan en un orden particular en función a su prioridad. La siguiente tabla muestra el orden predeterminado de las operaciones(de alta prioridad a baja prioridad):

Operador	Operación
**	Exponenciación
+, -	Suma/Adición, Resta/Negación
*, /	Multiplicación, División
||	Concatenación
=, <,  >,  <=,  >=,  <>,  !=,  ~=,  ^=, IS NULL, LIKE, BETWEEN, IN	Comparación
NOT	Negación Lógica
AND	Conjunción
OR	Inclusión

Cuando se trabaja con nulos, puede evitar algunos errores comunes teniendo en cuenta las siguientes reglas:

Las comparaciones con valores nulos siempre producen NULL.

Aplicando el operador lógico NOT a una nula devuelve NULL.

En las sentencias de control condicionales, si la condición contiene NULL, su asociada secuencia no se ejecuta.

Ejemplo:

SET SERVEROUTPUT ON

DECLARE

    contador  NUMBER :=  0;;

    num_emp   employees.employee_id%TYPE  :=  100;

    salario   employees.salary%TYPE;

    buen_salario  BOOLEAN;

    valido    buen_salario%TYPE;

BEGIN

    contador := contador + 1; --Se le suma  al valor actual de la variable: contador.

    buen_salario := salario BETWEEN 50000 AND 150000;  --Como esto es falto se le asigna FALSE a buen_salario.

    valido := (num_emp IS NOT NULL); --Como esto es cierto se le asigna TRUE a la variable: valido.

END;

/*Este ejemplo muestra el uso de algunos operadores en PL/SQL.*/

____________________________________________________________________________________

Directrices de programación.

Siga las directrices de programación mostradas a continuación para producir código claro y reducir el mantenimiento al desarrollar un bloque PL/SQL.

• Documentar el código con comentarios.

• Usar una nomenclatura constante para los identificadores y otros objetos.

• Usar buena indentación para facilitar la legibilidad.

La siguiente tabla proporciona pautas para escribir código en mayúscula o minúscula y así ayudar a distinguir las palabras claves de nombres de objetos.

Categoria	Modo	Ejemplos
Sentencias SQL	Mayúsculas	SELECT, INSERT
Palabras Claves de PL/SQL	Mayúsculas	DECLARE, BEGIN, IF
Tipos de Datos	Mayúsculas	VARCHAR2, BOOLEAN
Identificadores y Parametros	Minúsculas	v_sal, emp_cursor, g_sal, p_empno
Tablas de Base de Datos	Minúsculas, Plural	employees, departments
Columnas de Base de Datos	Minúsculas, Singular	employee_id, department_id

La Indentación.

Para mayor claridad y legibilidad, es recomendable indentar cada nivel de código. Para mostrar la estructura, se puede dividir el codigo mediante el uso de líneas de retorno de carro y se puede indentar las líneas mediante el uso de los espacios y tabulaciones.

Ejemplos:

--Código no Indentado:

IF x>y THEN max:=x;ELSE max:=y;END IF;

--Código Indentado:

IF x > y THEN

    max := x;

ELSE 

    max := y;

END IF;

/*Los dos Códigos hacen lo mismo pero es indiscutible que el segundo es mucho mas entendible gracias a la Indentacion;*/

_____________________________________________________________________________________

Fuente: Oracle Database: PL/SQL Fundamentals