Bloques Anonimos

Dentro de la seccion DECLARE de un bloque anónimo podemos declarar funciones y procedimientos almacenados y ejecutarlos desde el bloque de ejecución del script.

Este tipo de subprogramas son menos conocidos que los procedimientos almacenados, funciones y triggers, pero son enormemente útiles.

El siguiente ejemplo declara y ejecuta utiliza una funcion (fn_multiplica_x2).

 

DECLARE       idx NUMBER;        FUNCTION fn_multiplica_x2(num NUMBER)        RETURN NUMBER        IS          result NUMBER;        BEGIN          result := num *2;          return result;        END fn_multiplica_x2; BEGIN      FOR idx IN 1..10      LOOP        dbms_output.put_line ('Llamada a la funcion ... '||TO_CHAR(fn_multiplica_x2(idx)));      END LOOP;      END;

 

 Notese que se utiliza la funcion TO_CHAR para convertir el resultado de la función fn_multiplica_x2 (numérico) en alfanumérico y poder mostrar el resultado por pantalla.

Los bloques PL/SQL son unidades de código que se envían al servidor para que sean procesadas conjuntamente, obviamente esto es mejor para el rendimiento del sistema que si se envían las sentencias SQL separadamente una a una. Es decir, cuando se requiere la ejecución de un bloque PL/SQL, este es enviado al servidor PL/SQL donde se ejecuta el código PL/SQL y las sentencias SQL son enviadas al servidor Oracle, como el servidor PL/SQL reside en el servidor Oracle, el código se ejecuta eficientemente.

Un bloque de código PL/SQL contiene las siguientes secciones:

 

   1. La sección de declaraciones donde se definen los distintos objetos que se van a utilizar en el bloque (variables, constantes, cursores, etcétera). Esta sección es opcional.
   2. La sección de instrucciones. Sección obligatoria que contiene las sentencias ejecutables de SQL y PL/SQL.
   3. La sección de excepciones. Es una sección opcional donde se tratan todos los errores que puedan surgir a la hora de ejecutar las sentencias SQL y PL/SQL de la sección de instrucciones.

Ejemplo de bloque:

 
  DECLARE IS / AS
     [declaraciones]
  BEGIN
     [instrucciones]
  EXCEPTION
     [tratamiento de excepciones]
  END;

Por otro lado, hay cuatro tipos de bloques PL/SQL pero esto será objeto de otro artículo. Como adelanto del mismo diré que los cuatro tipos son:

   1. Bloques anónimos.
   2. Procedimientos.
   3. Funciones.
   4. Triggers.

 

 

EJERCICIOS RESUELTOS

 

Ejecutar el comando en el lab04_1.sql archivo para crear la tabla de mensajes. Escriba un PL / SQL
bloque para insertar números en la tabla de mensajes.
a. Introduzca los números 1 a 10, excepto 6 y 8.
b. Comprometerse antes de la final de la cuadra.
c. Seleccione de la tabla de mensajes para comprobar que el bloque PL / SQL trabajado
*/

DECLARE
v_country_id locations.country_id%TYPE := ’CA’;
v_location_id locations.location_id%TYPE;
v_city locations.city%TYPE := ’Montreal’;
BEGIN
SELECT MAX(location_id) INTO v_location_id
FROM locations
WHERE country_id = v_country_id;
FOR i IN 1..3 LOOP
INSERT INTO locations(location_id, city, country_id)
VALUES((v_location_id + i), v_city, v_country_id );
END LOOP;
--2
/*
Crear un bloque que calcula el importe de la comisión de un empleado determinado, basándose en el salario del empleado.
a. Utilice el comando DEFINE para proporcionar la identificación de empleado. 
DEFINE p_empno = 100
b. Si el sueldo del empleado es menor de $ 5,000, mostrar la cantidad de la bonificación para el empleado el 10% del sueldo.
c. Si el sueldo del empleado es de entre $ 5,000 y $ 10,000, aparecen con la cantidad de bonificación para el empleado el 15% del sueldo.
d. Si el salario del trabajador supera los $ 10.000, mostrar la cantidad de la bonificación para el empleado como 20% del sueldo.
e. Si el sueldo del empleado es NULL, la pantalla del importe de la bonificación para el empleado como 0.
f. Pruebe el bloque PL / SQL para cada caso con los casos de prueba siguiente, y comprobar cada bono de cantidad.
*/
set serveroutput on
define p_empno =100;
declare
sal number;
cursor salarios is
select salary
from employees;
salario employees.salary%type:='&p_empno';
begin
open salarios;
fetch salarios into salario;
if 
salario < 5000 then
sal:=(salario * 0.1);
elsif salario < 5000 and salario >=1000 then
sal:= salario * 0.15;
elsif salario > 1000 then
sal:= salario * 0.20;
elsif salario is null then
sal:= 0;
end if;
dbms_output.put_line (salario|| '      ' ||sal);
close salarios;
end;



--3
/*
Crear una tabla EMP que es una réplica de la tabla EMPLEADOS. Agregar una nueva columna, ESTRELLAS, de VARCHAR2 tipo de datos y la 
duración del 50 al EMP tabla para almacenar asterisco (*).
*/
create table EMP as
 select * from emp;
commit;
alter table emp 
add  (ESTRELLAS varchar2(50));
desc emp;

/*
--4
Crear un bloque que recompensa a un empleado si se añade un asterisco en los STARS
columna por cada $ 1000 de salario del empleado. 
a. Utilice el comando DEFINE para proporcionar la identificación de empleado. Pasar el valor al bloque PL / SQL
a través de un iSQL * Plus variable de sustitución.
b. Inicializar una variable v_asterisk que contiene un valor NULL.
c. Anexar un asterisco a la cadena por cada $ 1000 de la cantidad de sueldos. Por ejemplo, si el empleado
tiene una cantidad de sueldos de $ 8000, la cadena de asteriscos que contienen ocho asteriscos. Si el empleado
tiene una cantidad salario de 12.500 dólares, la cadena de asteriscos que contienen 13 asteriscos.
d. Actualización de la columna ESTRELLAS para el empleado con la cadena de asteriscos.
e. Commit.
f. Prueba del bloque para los siguientes valores:
DEFINE p_empno = 104
DEFINE p_empno = 174
DEFINE p_empno = 176
*/

set serveroutput on

declare
emp employees.employee_id%TYPE;
sal employees.salary%TYPE;
est varchar2 (1000);
begin 
  select employee_id, salary , to_char (rpad(' ', trunc (salary/1000) + 1, '*'))
  into emp, sal, est
  from employees
  where employee_id = 204;
  dbms_output.put_line (emp|| ' '|| sal|| ' '|| est);
  
end;