26 de enero de 2015

C# - Oracle: Bloques Anonimos

Los bloques PL/SQL son unidades de código que se envían al servidor para que sean procesadas conjuntamente, este tipo de subprogramas son menos conocidos que los procedimientos almacenados, funciones y triggers, pero son enormemente útiles.

La estructura de un bloque dentro de un procedimiento:

PROCEDURE nombreProcedimiento
IS
[DECLARE]
BEGIN
  -- instrucciones
[EXCEPTION]
END;

La estructura de un bloque dentro de una función:

FUNCTION nombreFuncion
RETURN tipodedato
IS
[DECLARE]
BEGIN
  -- instrucciones
  RETURN valor;
[EXCEPTION]
END;

El siguiente es un ejemplo simple de bloque anónimo

DECLARE
  v_apellido empleados.apellido%TYPE;

BEGIN
  SELECT apellido
    INTO v_apellido
    FROM empleados
   WHERE id_empleado = 138;

  DBMS_OUTPUT.put_line(v_apellido);
END;

Queremos mostrar los datos de un empleado cuyo DNI sea 23564390, del cual se tiene que  mostrar nombre y el apellido, además que utiliza excepciones

DECLARE
v_nom   empleados.nombre%type;
v_ape    empleados.apellido%type;

BEGIN
Select nombre,apellido
into v_nombre,v_ape
from Empleados
where dni=23564390;

Dbms_output.put_line('Nombre: '||v_nombre|| 'Apellido: '||v_apellido );
                      
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    Dbms_output.put_line('No  existe empleado con ese DNI');

   WHEN OTHERS THEN
   Dbms_output.put_line('otros errores');

 END;


Ejemplo 2

En este ejemplo se utiliza la tabla DUAL

SET SERVEROUTPUT ON;

DECLARE
  A VARCHAR(10) := '';

BEGIN
  SELECT TO_CHAR(SYSDATE) INTO A FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('LA FECHA ACTUAL ES : ' || A);

EXCEPTION
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('HOLA');
END;


Ejemplo 3

En este ejemplo se utiliza & para que aparezca un popup que pide el valor al usuario

DECLARE
V_DEPTO  EMP.DEPTNO%TYPE;
V_ENAME  EMP.ENAME%TYPE;

BEGIN
SELECT ENAME INTO V_ENAME
FROM EMP
WHERE SAL=(SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO=&DEPTNO);

DBMS_OUTPUT.PUT_LINE(V_ENAME);
END;

Ejemplo 4

Las transacciones son un conjuntos de operaciones en la base de datos, que se tratan como una unidad. Se realizan todas o se descartan todas. Para confirmar las transacciones pendientes se utiliza la sentencia COMMIT y para deshacer las transacciones pendientes se realizaría con ROLLBACK.

Ejemplo de manejo de transacciones:

DECLARE
    vValor NUMBER := 100;
    vOrigen VARCHAR2(1) :='A';
    vDestino VARCHAR2(1) := 'B';

BEGIN
    UPDATE tabla SET valor = vValor
     WHERE origen = vOrigen;

    INSERT INTO tabla2 (Origen, Destino, valor, FECHA)
                VALUES (vOrigen, vDestino, vValor, SYSDATE);

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error en la transaccion:'||SQLERRM);
        dbms_output.put_line('Se deshacen las modificaciones);
        ROLLBACK;
END;


Ejemplo 5

En este ejemplo se utiliza & para pedir el dato al usuario

DECLARE
                d_producto  producto.id_producto%type

BEGIN
                SELECT   id_producto
                               INTO  d_producto
                               FROM  producto
                               WHERE  id_producto = &id_inventario;
                DELETE  FROM   inventario
                               WHERE  id_inventario = d_producto;
         COMMIT ;

EXCEPTION
                WHEN OTHERS THEN
                               ROLLBACK ;
                               INSERT INTO exception_table (message) VALUES (‘ocurrió un error ’) ;
                               COMMIT ;
END ;

Ejemplo 6

En este ejemplo se utiliza un cursor

DECLARE
CURSOR CUR4 IS

SELECT ENAME, DNAME FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;
V_N VARCHAR2(20);
V_D VARCHAR2(20);

BEGIN
OPEN CUR4;
LOOP
FETCH CUR4 INTO V_N,V_D;
EXIT WHEN CUR4%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_N||'*'||'ESTA EN ELDEPNO'||V_D);
END LOOP
CLOSE;
END;

Ejemplo 7

SQL%ROWCOUNT es un atributo especial de cursor que retorna el número de tuplas modificadas por la última sentencia DML ejecutada en nuestra sesión.

• Modificar todos los empleados en el departamento 10 con un 20% de incremento salarial.

DECLARE
  v_id_departamento empleados.id_departamento%TYPE := 10;

BEGIN
  UPDATE empleados
    SET salario = salario * 1.2
   WHERE id_departamento = v_id_departamento;

  DBMS_OUTPUT.put_line(SQL%ROWCOUNT);
END;

Insertar un nuevo empleado en la tabla.

BEGIN
  INSERT INTO empleados (id_empleado
                       , apellido
                       , id_departamento
                       , salario)
       VALUES (100, 'Feuerstein' , 10, 200000);

  DBMS_OUTPUT.put_line(SQL%ROWCOUNT);
END;


Ejemplo 8

Bloques anidados

DECLARE
   l_mensaje1  VARCHAR2(100) := 'Hola';

BEGIN
   DECLARE
      l_mensaje2  VARCHAR2(5);
   BEGIN
      l_mensaje2 := ' Mundo!';
      DBMS_OUTPUT.put_line(l_mensaje1 || l_mensaje2);
  
EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
   END;
   DBMS_OUTPUT.put_line(l_mensaje1);
END;


Ejemplo 9

Son elementos, para poder gestionar los valores devueltos por una consulta a la base de datos.
Se pueden distinguir dos tipos:

Cursores implícitos: Se utilizan, cuando una consulta devuelve un registro
Cursores explícitos: Son controlados por el programador y suelen ser más eficientes

Ejemplo cursor implícito:

declare
    vprovincia VARCHAR2(50);

begin
    SELECT DESCRIPCION INTO vprovincia
      FROM PROVINCIAS
     WHERE CO_PROVINCIA = '47';

    dbms_output.put_line('La provincia es: ' || vprovincia);
end;


Ejemplo cursor explícito:

DECLARE
    CURSOR cprovincias IS
        SELECT CO_PROVINCIA, DESCRIPCION
          FROM PROVINCIAS;

    CO_PROVINCIA VARCHAR2(3);
    descripcion VARCHAR2(50);

BEGIN
    OPEN cprovincias;
    FETCH cprovincias INTO CO_PROVINCIA,descripcion;
    WHILE cprovincias %found LOOP
        dbms_output.put_line(descripcion);
        FETCH cprovincias INTO CO_PROVINCIA,descripcion;
    END LOOP;
    CLOSE cprovincias;
END;

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;


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;