lunes, 18 de mayo de 2015

Escribiendo Sentencias Ejecutables PL/SQL

Unidades Léxicas en un Bloque PL/SQL

Las unidades léxicas son los componentes básicos de cualquier bloque PL/SQL. Incluyen letras, números, tabulaciones, espacios, saltos de línea y símbolos.
Se pueden clasificar como:

  • Identificadores: Son empleados para nombrar objetos de programas en PL/SQL. Son utilizados para nombrar unidades dentro de un programa PL/SQL que incluyen: Constantes, Cursores, Variables, Subprogramas, Excepciones y Paquetes.
  • Delimitadores: es un símbolo simple o compuesto que tiene una función especial en PL/SQL. Estos pueden ser: Operadores Aritméticos, Operadores Lógicos y Operadores Relacionales.
  • Literales: valor asignado a una variable en forma explícita o textual.
  • Comentarios: texto de aclaración o documentación que se incluye en el bloque.

Unidades Léxicas en un Bloque PL/SQL

Símbolos simples

+ Operador de suma
- Operador de resta o negación
* Operador de multiplicación
/ Operador de división
= Operador de igualdad
@ Indicador de acceso remoto
; Término de sentencia
' Delimitador de caracteres
% Indicador de atributo
(y) Expresión/delimitador de lista
: Indicador de variable host
, Separador de item
' ' Delimitador de un identificador entre comillas

Símbolos Compuestos

:= Operador de asignación
=> Operador de asociación
|| Operador de concatenación
/* Delimitador de inicio de comentario de varias líneas
*/ Delimitador de fin de comentarios de varias líneas
.. Operador de rango
<> Operador de distinto
!= Operador de distinto
>= Operador de mayor igual
<= Operador de menor igual -- Comentario de una línea

Un literal es cualquier valor que se asigna a una variable en forma textual. Es cualquier carácter, número, booleano o valor de fecha que se asigna en forma explícita. Los literales se clasifican en:

  • Los literales de caracteres: Todos los literales de tipo string tienen el tipo de datos CHAR o VARCHAR2 y son, por lo tanto, llamados literales de caracteres (por ejemplo, Juan, y 12C). Los literales de caracteres y fecha deben ir entre comillas simples.
  • Los literales numéricos: Un literal numérico representa un valor entero o real (por ejemplo, 428 y 1.276).
  • Literales booleanos: valores que se asignan a las variables booleanas son literales booleanos. TRUE, FALSE y NULL son literales booleanos. 
DECLARE
  v_comm  	NUMBER(3) :=50;
  v_fecha	DATE; 
  v_flag      BOOLEAN;
  v_nombre VARCHAR2(30) := 'MARÍA';
BEGIN
   v_fecha := '01/05/2020';
   IF v_comm = 0 THEN
       v_flag := TRUE;
  END IF;
END;

Los comentarios es una buena práctica de programación para explicar el código o parte del código de un bloque está tratando de hacer. Cuando se incluye un comentario en un bloque PL/SQL, el compilador no puede interpretar estas instrucciones. Los símbolos para definir un comentario indican al compilador que estas instrucciones no necesitan ser compilados. Por lo tanto, cualquier instrucción que se comenta no es interpretada por el compilador.

  • Un comentario de línea simple es precedido con 2 guiones (--).
  • Comentarios de múltiples líneas se coloca entre los símbolos /* y */.
DECLARE
   v_sal_anual   NUMBER (9,2);
BEGIN    -- Comienzo de la sección ejecutable /* La siguiente sentencia SQL obtiene desde tabla EMPLOYEES el salario anual del empleado con identificación igual a 100 */
     SELECT  SUM(salary * 12)
          INTO v_sal_anual
        FROM employees;
     DBMS_OUTPUT.PUT_LINE('Salario anual : ' ||  
     TO_CHAR(v_sal_anual, '$999,999,999'));
END;  -- Este es el fin del bloque

Funciones SQL en PL/SQL

Las funciones disponibles en sentencias procedimentales son aquellas funciones SQL que están disponibles para ser utilizadas en sentencias PL/SQL y en sentencias SQL que forman parte del bloque PL/SQL:

  • Funciones numéricas de una fila (single_row number): ROUND, TRUNC, MOD.
  • Funciones de carateres de una fila (single_row character): LOWER, UPPER, INITCAP, CONCAT, SUBSTR, etc
  • Conversiones de tipo de dato: TO_CHAR, TO_DATE etc
  • Funciones de fecha: SYSDATE, MONTH_BETWEEN, ADD_MONTHS, NEXT_DAY, etc
  • Funciones Timestamp.
  • GREATEST y LEAST
  • Funciones generales: COALESCE, NVL, NULLIF, etc.

Las funciones No Disponibles en sentencias PL/SQL son funciones SQL que sólo pueden ser utilizadas en sentencias SQL que forman parte del bloque PL/SQL y no en sentencias propias de PL/SQL:

  • DECODE.
  • Funciones de grupo: AVG, MIN, MAX, COUNT, etc.
DECLARE
  v_apell	       employees.last_name%TYPE;
  v_nombre       employees.first_name%TYPE;
  v_comision     employees.commission_pct%TYPE;
BEGIN
     SELECT last_name, UPPER(first_name), NVL(commission_pct,0)
          INTO v_apell, v_nombre, v_comision
        FROM employees
      WHERE employee_id = 110;
     DBMS_OUTPUT.PUT_LINE('Apellido empleado 110: ' || v_apell);
     DBMS_OUTPUT.PUT_LINE('Largo apellido empleado 110: ' || LENGTH(v_apell));  
     DBMS_OUTPUT.PUT_LINE('Nombre empleado 110 en mayúscula: ' || v_nombre);
     DBMS_OUTPUT.PUT_LINE('Porcentaje_comisión empleado 110: ' || v_comision);  
END;

En el bloque PL/SQL del ejemplo, se muestra el apellido en mayúscula, el largo del primer nombre y el porcentaje de comisión (si es nulo se mostrará el valor cero con la función NVL) del empleado 110. Las funciones LENGTH, UPPER y NVL pueden ser usadas en sentencias SQL y también en sentencias PL/SQL.

DECLARE
  v_sal_prom      PLS_INTEGER := 0;
  v_sal_mayor    NUMBER(8) := 0;
BEGIN
    SELECT ROUND(AVG(salary)), MAX(salary)
       INTO v_sal_prom, v_sal_mayor
     FROM employees;
    DBMS_OUTPUT.PUT_LINE('Salario promedio es: ' || TO_CHAR(v_sal_prom, '$999,999'));
    DBMS_OUTPUT.PUT_LINE('Salario mayor es: ' || TO_CHAR(v_sal_mayor, '$999,999'));  
END;

En el bloque del ejemplo, se obtiene el salario promedio (redondeado) y el salario máximo entre todos los empleados. Las funciones AVG y MAX sólo se puede utilizar en la sentencia SQL del bloque PL/SQL. Las funciones ROUND y TO_CHAR se pueden utilizar en sentencias SQL dentro del bloque y también en sentencias PL/SQL.

DECLARE
  v_apell        employees.last_name%TYPE;
  v_largo_apell NUMBER(2);
  v_nombre       employees.first_name%TYPE;
  v_comision     employees.commission_pct%TYPE;
BEGIN
  SELECT last_name, LENGTH(last_name), first_name, commission_pct
       INTO v_apell, v_largo_apell, v_nombre, v_comision
     FROM employees
   WHERE employee_id = 111;
  DBMS_OUTPUT.PUT_LINE('Apellido empleado 111: ' || v_apell);
  DBMS_OUTPUT.PUT_LINE('Largo apellido empleado 111: ' || v_largo_apell);  
  DBMS_OUTPUT.PUT_LINE('Nombre empleado 111 en mayúscula: ' || UPPER(v_nombre));
  DBMS_OUTPUT.PUT_LINE('Porcentaje_comisión empleado 111: ' || NVL(v_comision,0));    
END;

En este bloque PL/SQL, la función LENGTH ahora es usada en la sentencia SQL del bloque y las funciones UPPER y NVL son usadas en las sentencias PL/SQL.


Usar Secuencias en Expresiones PL/SQL

-- antes de 11g

DECLARE
  v_nuevo_id   NUMBER(6};
BEGIN
  SELECT nombre_secuencia.NEXTVAL 
      INTO v_nuevo_id 
    FROM dual;
END;

-- a contar de 11g

DECLARE
  v_nuevo_id   NUMBER(6);
BEGIN
  v_nuevo_id := nombre_secuencia.NEXTVAL;
END;

En Oracle Database 11g, se puede utilizar las pseudo-columnas NEXTVAL y CURRVAL en cualquier contexto PL/SQL donde exista una expresión de tipo de datos NUMBER. Aunque el viejo estilo de la utilización de una instrucción SELECT para consultar una secuencia sigue siendo válido, se recomienda que no usarlo.
Antes de Oracle Database 11g, era obligación a escribir una sentencia SQL con el fin de utilizar un valor de objeto de secuencia en un subprograma PL/SQL. Por lo general, era una instrucción SELECT para hacer referencia a los pseudo-columnas de NEXTVAL y CURRVAL para obtener un número de secuencia (Ejemplo)
En Oracle Database 11g, la limitación que obligaba a escribir una declaración de SQL para recuperar un valor de secuencia ya no existe (Ejemplo).


Conversión de Tipo de Datos

Cada constante o variable posee un tipo de dato el cual especifica su forma de almacenamiento, restricciones y rango de valores válidos. Se convierte un
valor desde un tipo a otro para tener tipos de datos comparables.

En PL/SQL existen dos tipos de conversiones:

Implícita: convierte tipos de datos automáticamente si son variados en una sentencia. Las conversiones implícitas pueden ocurrir entre caracteres y números ó caracteres y fechas:

  • De VARCHAR2 o CHAR a NUMBER.
  • De VARCHAR2 o CHAR a DATE.
  • De NUMBER a VARCHAR2.
  • DATE a VARCHAR2.

Explícita: es aquella que convierte un valor de un tipo a otro utilizando funciones predefinida:

  • TO_CHAR
  • TO_DATE
  • TO_NUMBER
  • TO_TIMESTAMP
DECLARE
v_fecha_reunion    DATE:= '09-Mar-2009';
v_fecha_contrato  DATE:= TO_DATE('Febrero 02,2000','Month DD, YYYY');
v_fecha_actual      VARCHAR2(20);
v_salario                NUMBER(6):=6000; 
v_bono                   VARCHAR2(5):='1000'; 
v_total_salario       v_salario%TYPE; 
BEGIN 
   SELECT TO_CHAR(sysdate,'Month DD, YYYY')
         INTO v_fecha_actual 
       FROM dual;
v_total_salario := v_salario + v_bono;
END;

En el bloque del ejemplo, valor asignado a la variable v_fecha_de_reunion es un string que tiene un formato de fecha válido, por lo tanto Oracle efectúa una conversión implícita. La variable v_fecha_contrato es de tipo de dato DATE, por lo tanto se usa la función TO_DATE para convertir explícitamente la fecha dada en un formato particular. La variable v_bono es de tipo VARCHAR2, por lo tanto cuando se efectúa el cálculo en la variable v_total_salario PL/SQL primero convierte v_bono a NUMBER (conversión implícita) y luego se realiza la sumatoria (el resultado de la operación será entonces de tipo NUMBER). En la sentencia Select se utiliza la función TO_CHAR para convertir en forma explícita la fecha actual en un formato tipo caracter.


Bloques PL/SQL Anidados

Una de las ventajas que PL/SQL tiene sobre SQL es la posibilidad de anidar sentencias. Si la sección ejecutable (BEGIN …. END) tiene código de muchas
funcionalidades de lógica relacionadas, se puede separar la sección ejecutable en bloques más pequeños. La sección de excepción también puede contener
bloques anidados. 

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;

En el ejemplo, existen dos bloques PL/SQL. En el principal se declara la variable v_variable_externa que puede ser usada por ambos bloques. En el bloque interno se declara la variable v_variable_interna que solo puede ser usada en el bloque interno.


Ámbito y Visibilidad de la Variable

En PL/SQL las referencias a una variable son resueltas de acuerdo a su alcance y visibilidad dentro de un programa. El ámbito de una variable es aquella
parte del programa PL/SQL en la cual la variable es declarada y es accesible. La visibilidad de la variable es la parte del programa donde la
variable puede ser accesada sin utilizar un identificador o etiqueta (qualified).
Las variables declaradas en un bloque PL/SQL se consideran locales a ese bloque y globales para todos sus sub-bloques. Si una variable global es
re-declarada en un sub-bloques ambas pertenecen al mismo ámbito, sin embargo en el sub-bloque tan sólo la variable local es visible porque se debe utilizar
un qualified para referenciar a la global.

DECLARE
 nombre_padre       VARCHAR2(20):='Patricio';
 fecha_de_cumple  DATE:='20-Abr-1972';
BEGIN
   DECLARE
   nombre_hijo           VARCHAR2(20):='Miguel';
   fecha_de_cumple   DATE:='13-May-1992';
   BEGIN 
        DBMS_OUTPUT.PUT_LINE('Nombre del hijo: ' || ' ' || nombre_hijo);
        DBMS_OUTPUT.PUT_LINE('Dia de Cumpleaño: '||fecha_de_cumple);
        DBMS_OUTPUT.PUT_LINE(' Nombre del padre: ' || nombre_padre);
   END;
DBMS_OUTPUT.PUT_LINE('Dia de Cumpleaño: '||fecha_de_cumple); 
END;

En el ejemplo, en ambos bloques se declara la variable fecha_de_cumple. Por lo tanto, el bloque interno mostrará el valor asignado a su variable
13-May-1992 y el bloque externo mostrará el valor asignado a su variable 20-Abr-1972.

<>
DECLARE
 nombre_padre     VARCHAR2(20):='Patricio';
 fecha_de_cumple DATE:='20-Abr-1972';
BEGIN
   DECLARE
   nombre_hijo     VARCHAR2(20):='Miguel';
   fecha_de_cumple DATE:='13-May-1992';
   BEGIN 
      DBMS_OUTPUT.PUT_LINE('Nombre del padre:' || ' ' || nombre_padre);
      DBMS_OUTPUT.PUT_LINE('Dia de Cumpleaño: '|| padre.fecha_de_cumple);
      DBMS_OUTPUT.PUT_LINE('Nombre del hijo :' || ' ' || nombre_hijo);
      DBMS_OUTPUT.PUT_LINE('Dia de Cumpleaño: '|| fecha_de_cumple);
   END;
END;

Un calificador es un nombre dado a un bloque. Se puede utilizar un calificador para acceder a las variables que tienen un ámbito, pero no son visibles.
Basados en el mismo ejemplo anterior, si en el bloque interno se desea utilizar la variable fecha_de_cumple del bloque principal se debe utilizar una
etiqueta que identifique al bloque, que en este caso es padre, por lo tanto ahora en el bloque interno al referenciar padre.fecha_de_cumple mostrará el
valor de la variable del bloque principal y al referencia sólo fecha_de_cumple mostrará el valor de la variable del bloque interno.


Operadores PL/SQL

- Lógicos
- Aritméticos
- Comparaciones
- Concatenación
Paréntesis para controlar el orden de las operaciones

- Operador exponencial (**)

Las operaciones dentro de una expresión son realizadas en un orden particular dependiendo de su precedencia (prioridad) el que puede ser cambiado
utilizando paréntesis. El orden predeterminado de las operaciones de alta a baja prioridad es la siguiente:

  • ** : Exponenciación
  • + , - : Identificación, negación
  • *, / : Multiplicación, división
  • + , -, || : suma, resta, cocatenación
  • =, <, >, <=, >=, <>, !=, IS NULL, LIKE, BETWEEN, IN : Comparación
  • NOT : Negación
  • AND : Conjunción
  • OR : inclusión
DECLARE
v_loop_cuenta  NUMBER(3):=0;
v_buen_sal     BOOLEAN;
v_mensaje      VARCHAR2(40);
v_salario      NUMBER(8):=8000;
BEGIN
  v_loop_cuenta  :=  v_loop_cuenta + 1;
  v_buen_sal := v_salario BETWEEN 50000 AND 150000;
  v_mensaje := 'El nuevo salario es: ' || v_salario * 1.15;
END;

En el ejemplo, se incrementa el valor actual de la variable v_loop_cuenta en 1, se setea el valor de un Flag Boolean a la variable v_buen_salario
dependiendo del valor de la variable v_salario. Finalmente, la variable mensaje contendrá el mensaje El nuevo salario es: concatenado al resultado de
multiplicar el v_salario por 1.15.


Buenas Prácticas de Programación

Documentar el código con comentarios.

  • Escribir en mayúsculas:
  • Las sentencias SQL.
  • Las palabras reservadas de PL/SQL.
  • Los tipos de datos.

Escribir en minúsculas:

  • Los identificadores de variables.
  • Los parámetros.
  • Los nombres de tablas y columnas.

Indentar el código.

DECLARE
  v_id_emp                 employees.employee_id%TYPE;
  v_fecha_contrato    VARCHAR2(20);
  v_salario                  NUMBER(6):=6000; 
BEGIN 
    SELECT employee_id, TO_CHAR(hire_date,'Month DD, YYYY'), salary
    INTO v_id_emp, v_fecha_contrato, v_salario
    FROM employees
    WHERE  employee_id = 200;
    DBMS_OUTPUT.PUT_LINE('El empleado ' || v_id_emp || ' se contrató en ' ||
                    v_fecha_contrato || ' . Su salario mensual  es de $ ' || v_salario);
END;
Publicado por en
Etiquetas:

No hay comentarios:

Publicar un comentario

Suscribirse a: Enviar comentarios (Atom)