sábado, 19 de mayo de 2012

Actualizar departamento empleado con procedure


Procedimiento almacenado que alctualiza la tabla empleados

create or replace procedure actualizarDepartamento(pempleadoId in char,pdepartamentoId in char)
is
total integer;
begin
  total := 0;
  select count(*) into total from empleado;
  if total > 0 then
    update empleado set numoficina = pdepartamentoId where numempleado = pempleadoId;
  end if;
end actualizarDepartamento;


begin
  actualizarDepartamento('SL21','B006');
  dbms_output.put_line('Actualizado');
end;

Repaso funciones


Funcion para contar cuantos empleados por departamento hay.


create or replace function empleadosPorDepartamento(departamento number)
return number
is
resultado number;
begin
  select count(*) as contador into resultado  from employees where department_id = departamento;
  return resultado;
end empleadosPorDepartamento;


set serveroutput on
declare
  resultado number;
begin
  resultado := empleadosPorDepartamento(50);
  dbms_output.put_line('El resultado es: '||resultado);
end;

Forma para mostrar los datos y la cantidad de empleados llamando a la funcion creada anteriormente.

select employee_id,department_id,empleadosPorDepartamento(50) from employees

miércoles, 16 de mayo de 2012

Estructura de funciones

Estructura de funciones

create or replace function miFuncion (pnumero in number)
return number
is
  resultado number;
begin
  resultado := pnumero*2;
  return(resultado);
end;

set serveroutput on
declare
  numero number := 1;
begin
  numero := miFuncion(numero);
  dbms_output.put_line('Numero: '||numero);
end;


Funcion par o impar


create or replace function miFuncion (pnumero number)
return varchar2
is
  msg varchar2(20);
begin
  if mod(pnumero,2) = 0  then
    msg := 'Par';
  else
    msg := 'Impar';
  end if;
  return(msg);
end;
show err function miFuncion;
set serveroutput on
declare
  numero number := 1;
  mensaje varchar2(20);
begin
  mensaje := miFuncion(numero);
  dbms_output.put_line('Numero: '||mensaje);
end;

Procedimientos almacenados Estructura


Estructura del procedimiento con out
create or replace procedure miProcedimiento(ptexto out varchar2) is
begin
  ptexto := 'Este es mi mensaje del procedimiento';
end miProcedimiento;

Llamando al procedimiento


declare
  vtexto varchar2(50) := 'Hola a todos';
begin
  miProcedimiento(vtexto);
  dbms_output.put_line('Mi mensaje : '||vtexto);
end;

Procedimiento con in y out


create or replace procedure miProcedimiento(ptexto in out varchar2) is
begin
  dbms_output.put_line('Mi mensaje : '||ptexto);
  ptexto := 'Este es mi mensaje del procedimiento';
end miProcedimiento;

set serveroutput on
declare
  vtexto varchar2(50) := 'Hola a todos';
begin
  miProcedimiento(vtexto);
  dbms_output.put_line('Mi mensaje : '||vtexto);
end;

Procedimiento con solo salida


create or replace procedure miProcedimiento(ptexto out varchar2) is
begin
  dbms_output.put_line('Mi mensaje : '||ptexto);
  ptexto := 'Este es mi mensaje del procedimiento';
end miProcedimiento;

set serveroutput on
declare
  vtexto varchar2(50) := 'Hola a todos';
begin
  miProcedimiento(vtexto);
  dbms_output.put_line('Mi mensaje : '||vtexto);
end;

Procedimiento concatenado


create or replace procedure Concatenar(ptexto1 varchar2,ptexto2 varchar2) is
begin
  dbms_output.put_line(ptexto1||' '||ptexto2);
end Concatenar;

set serveroutput on
declare
  vtexto varchar2(50) := 'Hola a todos';
begin
  Concatenar('Buenos','Dias');
  Concatenar(ptexto2=>'Buenos',ptexto1=>'Dias');
end;




miércoles, 9 de mayo de 2012

Solucion Control Cursores



set serveroutput on

Problema 1

declare
  cursor Uno is select first_name,last_name,salary from employees order by last_name;
  recEmpleado Uno%rowtype;
  contador integer := 0;
begin
  open Uno;  
      loop
        fetch Uno into recEmpleado;
        exit when Uno%NOTFOUND;
        contador := contador + 1;
        dbms_output.put_line('Nombre: '||recEmpleado.first_name||' Apellido: '||recEmpleado.last_name||' Salario: '||recEmpleado.salary);
      end loop;
  close Uno;
  dbms_output.put_line('Total de empleados: '||contador);
  exception
    when others then
    dbms_output.put_line('Error');
end;

Problema 2






set serveroutput on
declare
  cursor Uno is select first_name,last_name,salary,hire_date from employees order by last_name;
  recEmpleado Uno%rowtype;
  contador integer := 0;
  edad integer;
  msg varchar2(10);
begin
  open Uno;
  fetch Uno into recEmpleado;
      while Uno%FOUND loop      
        contador := contador + 1;
        edad := round((sysdate - recEmpleado.hire_date)/365);
        if edad <= 25 then
          msg := 'Junior';
        elsif edad > 25 and edad < 40 then
          msg := 'Senior';
        else
          msg := 'Master';
        end if;
        fetch Uno into recEmpleado;
        dbms_output.put_line('Nombre: '||recEmpleado.first_name||' Apellido: '||recEmpleado.last_name||' Salario: '||recEmpleado.salary||' Mensaje : '||msg);
      end loop;
  close Uno;
  dbms_output.put_line('Total empleados '||contador);
  exception
    when others then
    dbms_output.put_line('Error');
end;





desc departments
set serveroutput on
declare
  cursor Uno is
  select employees.first_name,employees.last_name,employees.salary,departments.DEPARTMENT_NAME
  from employees, departments
  where departments.department_id = employees.department_id
  order by DEPARTMENT_NAME;
  recEmpleado Uno%rowtype;
  contador integer := 0;
  edad integer;
  msg varchar2(10);
begin
  for recEmp in Uno loop
   dbms_output.put_line('Nombre:  '||recEmp.first_name);
  end loop;

  exception
    when others then
    dbms_output.put_line('Error');
end;




viernes, 4 de mayo de 2012

Tarea 3 - Cursores Explicitos en PL/SQL


Declaración de cursores explícitos.

1.    Declaración del cursor: lo tenemos que declarar en la zona de declaraciones, con el siguiente formato: CURSOR <nombrecursor> IS <sentencia SELECT>;
2.    Apertura del cursor: Deberá colocarse en la zona de instrucciones, con el siguiente formato: OPEN <nombrecursor>; 
Al hacerlo se ejecuta automáticamente la sentencia select y sus resultados se almacenan en las estructuras internas de memoria manejadas por el cursor.
3.    Recogida de información: Para recuperar la información anteriormente guardada en las estructuras de memoria interna tenemos que usar el siguiente formato: 
FETCH <nombrecursor> INTO {<variable> | <listavariables>}; 
Si tenemos una única variable que recoge los datos de todas las columnas, el formato de la variable seria el siguiente:
  <variable> <nombrecursor>%ROWTYPE; 
Si tenemos una lista de variables, cada una recogerá la columna correspondiente de la cláusula select, por lo que serán del mismo tipo que las columnas.
4.    - Cierre del cursor:  CLOSE <nombrecursor>;
OPEN nombre_cursor;
    LOOP
        FETCH nombre_cursor INTO lista_variables;
        EXIT WHEN nombre_cursor%NOTFOUND;
        /* Procesamiento de los registros recuperados */
    END LOOP;
CLOSE nombre_cursor;

Atributos del cursor


Detalles de la situación del cursor tenemos 4 atributos:
  • %FOUND: devuelve verdadero di el último FETCH ha recuperado algún valor; en caso contrario devuelve falso; si el cursor no está abierto nos devuelve error.
  • %NOTFOUND: hace justamente lo contrario al anterior.
  • %ROWCOUNT: nos devuelve el número de filas recuperadas hasta el momento.
  • %ISOPEN: devuelve verdadero si el cursor está abierto.

Manejo del cursor 

Bucle LOOP con una sentencia EXIT condicionada

DECLARE

  cursor CursorLoop IS SELECT employee_id,first_name,email FROM employees;

  vid employees.employee_id%TYPE;

  vfirst_name employees.first_name%TYPE;

  vemail employees.email%TYPE;

BEGIN

  OPEN CursorLoop;

    LOOP

      FETCH CursorLoop INTO vid,vfirst_name,vemail;

      EXIT WHEN CursorLoop %NOTFOUND;

      dbms_output.put_line('Id: '||vid||' |Nombre: '||vfirst_name||' |Email:'||vemail);

    END LOOP;

  CLOSE CursorLoop;

  EXCEPTION

    WHEN OTHERS THEN

      dbms_output.put_line('Error: '||SQLERRM);

END;

Bucle WHILE LOOP 

DECLARE
  cursor CursorWhile IS SELECT employee_id,first_name,email FROM employees;
  vid employees.employee_id%TYPE;
  vfirst_name employees.first_name%TYPE;
  vemail employees.email%TYPE;
BEGIN
  OPEN CursorWhile;
    FETCH CursorWhile INTO vid,vfirst_name,vemail;
    WHILE CursorWhile%FOUND
      LOOP       
        dbms_output.put_line('Id: '||vid||' |Nombre: '||vfirst_name||' |Email:'||vemail);
        FETCH CursorWhile INTO vid,vfirst_name,vemail;
      END LOOP;
  CLOSE CursorWhile;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Error: '||SQLERRM);
END;

Bucle FOR LOOP 

DECLARE
  cursor CursorFor IS SELECT employee_id,first_name,email FROM employees;
BEGIN
  FOR employee_rec IN CursorFor
    LOOP
      dbms_output.put_line('Id: '||employee_rec.employee_id||' |Nombre: '||employee_rec.first_name||' |Email: '||employee_rec.email);
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Error: '||SQLERRM);
END;

miércoles, 2 de mayo de 2012

Cursor Par - Impar 2


set serveroutput on
declare
  cursor LuisUno is select employee_id,first_name,last_name,email from employees;
  vid employees.employee_id%TYPE;
  vnombre employees.first_name%TYPE;
  vapellido employees.last_name%TYPE;
  vemail employees.email%TYPE;
  vtipo varchar2(5);
begin
  open LuisUno;
    loop
      fetch LuisUno into vid,vnombre,vapellido,vemail;
      exit when LuisUno%NOTFOUND;
        if mod(vid,2)=0 then
          vtipo := 'Par';
        else
          vtipo := 'Impar';
        end if;
        update employees set tipoid = vtipo where employee_id = vid;
        --dbms_output.put_line('Id: '||vid||' | Nombre: '||vnombre||' |Apellido: '||vapellido||' |Email: '||vemail);
    end loop;
  close LuisUno;
  exception
    when others then
     dbms_output.put_line('Error: '||SQLERRM);
end;

martes, 1 de mayo de 2012

Cursores y procedure


set serveroutput on
DECLARE
 vPropietario all_tables.owner%TYPE;
 vNombreTabla varchar2(40);
    vNombreColumna varchar2(100);

    /* Primer cursor */
    cursor obtieneTablas(pPropietario all_tables.owner%TYPE) is
    select distinct t.owner, t.table_name
    from all_tables t
    where t.owner = pPropietario;

    /* Segundo cursor */
    cursor obtieneColumnas is
    select distinct c.column_name
    from all_tab_columns c
    where c.owner = vPropietario
    and c.table_name = vNombreTabla;
begin
   open obtieneTablas('SYSTEM');
dbms_output.put_line('Abriendo Cursor - obtieneTablas');
loop fetch obtieneTablas into vPropietario, vNombreTabla;
   exit when obtieneTablas%NOTFOUND;
   dbms_output.put_line('Tabla : '||vPropietario||'.'||vNombreTabla);
       open obtieneColumnas;
       loop fetch obtieneColumnas into vNombreColumna;
     exit when obtieneColumnas%NOTFOUND;
         dbms_output.put_line('='||vNombreTabla||'.'||vNombreColumna);
       end loop;
       close obtieneColumnas;
   end loop;
close obtieneTablas;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Se ha detectado un error - '||SQLCODE||' -ERROR- '||SQLERRM);
end;

CREATE OR REPLACE PROCEDURE CATUSER(pOwner all_tables.owner%type) AS

    vPropietario all_tables.owner%TYPE;
 vNombreTabla varchar2(40);
    vNombreColumna varchar2(100);

    /* Primer cursor */
    cursor obtieneTablas(pPropietario all_tables.owner%TYPE) is
    select distinct t.owner, t.table_name
    from all_tables t
    where t.owner = pPropietario;

    /* Segundo cursor */
    cursor obtieneColumnas is
    select distinct c.column_name
    from all_tab_columns c
    where c.owner = vPropietario
    and c.table_name = vNombreTabla;
begin
   open obtieneTablas(pOwner);
dbms_output.put_line('Abriendo Cursor - obtieneTablas');
loop fetch obtieneTablas into vPropietario, vNombreTabla;
   exit when obtieneTablas%NOTFOUND;
   dbms_output.put_line('Tabla : '||vPropietario||'.'||vNombreTabla);
       open obtieneColumnas;
       loop fetch obtieneColumnas into vNombreColumna;
     exit when obtieneColumnas%NOTFOUND;
         dbms_output.put_line('='||vNombreTabla||'.'||vNombreColumna);
       end loop;
       close obtieneColumnas;
   end loop;
close obtieneTablas;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Se ha detectado un error - '||SQLCODE||' -ERROR- '||SQLERRM);
END CATUSER;

set serveroutput on
begin
  CATUSER('SYSTEM');
end catuser;

Cursor Par - Impar

set serveroutput on
declare
  cursor ParImpar is select employees.employee_id,employees.tipoid from employees;
  vid employees.employee_id%type;
  vtipo employees.tipoid%type;
begin
  open ParImpar;
    loop
      fetch ParImpar into vid,vtipo;
      exit when ParImpar%NOTFOUND;      
      if mod(vid,2) = 0 then
        dbms_output.put_line('Id: '||vid||' Par');
        update employees SET tipoid = 'Par' where employee_id=vid;
      else
        dbms_output.put_line('Id: '||vid||' Impar');
        update employees SET tipoid = 'Impar' where employee_id=vid;
      end if;
    end loop;
  close ParImpar;
end;