Clase 30 de Mayo

create table Autor(
idAutor int,
nombre varchar(30),
constraint Pk_idAutor primary key (idAutor) 
);

begin
insert into Autor values(7);
insert into Autor VALUES(1);
insert into Autor VALUES(2);
insert into Autor VALUES(3);
insert into Autor VALUES(4);
insert into Autor VALUES(5);
end;

create table Libro(
IdLibro integer,
IdAutor integer,
constraint Pk_idLibro primary key (IdLibro),
constraint Fk_IdAutor foreign key (IdAutor) references Autor
);

begin
insert into Libro values(1, 1);
insert into Libro values(2, 1);
insert into Libro values(3, 2);
insert into Libro values(4, 3);
end;

create sequence seqAutor
start with 1 increment by 10
nocache
nocycle;

create or replace trigger MiPrimerTrigger
after delete on Autor
for each row
begin
  DELETE FROM Libro
  WHERE IdAutor = :old.IdAutor;
end;

begin
delete from Autor
where idAutor = 1;
end;

select * from Libro;

26 de Mayo Ejercicio 1 Solemne 2

-- ejercicio 1 solemne 2
set serveroutput on;
Declare
Cursor Ldepto is
select d.department_id, department_name, count(employee_id) as total
from employees e, departments d
where e.department_id = d.department_id
group by d.department_id, department_name
order by department_name;
msg char(1);
cnt integer := 0;
Begin
For reg in Ldepto Loop --el reg toma el department_id, department_name y el total, del cursor Ldepto
  if mod(reg.total,2) = 0 then
    msg := '*';
    cnt := cnt+1;
  else
    msg := '';
  End if;
  dbms_output.put_line(reg.department_id || ' ' || reg.department_name || ' ' || reg.total || ' ' || msg);
End Loop;
  dbms_output.put_line('El total de departamentos con cantidad de empleados PAR es: ' || cnt);
End;

Clase 26 de Mayo

-- El Cursor

set serveroutput on
declare
cursor crCuentaEmpleados is
  select department_id, department_name, count(*) as total
  from employees natural join departments
  group by department_id, department_name
  order by department_name;
msg char(1);
cnt integer := 0;
begin
  for recCur in crCuentaEmpleados loop
      if recCur.total mod 2 = 0 then
         msg := '*';
         cnt := cnt + 1;
      else
        msg := ' ';
      end if;
      dbms_output.put_line (recCur.department_id||' '||recCur.department_name||' '||recCur.total||' '||msg);
  end loop;
  dbms_output.put_line ('El Total de departamentos con cantidad de empleados par es: '||cnt);
exception
when others then
  dbms_output.put_line ('Error desconocido');
end;


-- El Procedimiento
Create or Replace PROCEDURE actualizaFono (pid Employees.employee_id%type, pfono Employees.phone_number%type) is

cnt integer;
EMPLEADO_NO_EXISTE EXCEPTION;

begin

   select count(*) into cnt
   from employees
   where employee_id = pid;
   if cnt = 0 then
      raise EMPLEADO_NO_EXISTE;
   end if;

   update employees
   set phone_number = pfono
   where employee_id = pid;
   dbms_output.put_line ('Empleado '||pid||' Actualizado');
  
exception

    when EMPLEADO_NO_EXISTE then
        dbms_output.put_line ('Empleado '||pid||' No existe');

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

-- La funcion

CREATE OR REPLACE FUNCTION CUENTAEMPLEADOS
(
  pDepto IN employees.department_id%type 
) RETURN NUMBER AS

totalEmpleados integer;
BEGIN
  select count(*) into totalEmpleados
  from Employees
  where department_id = pDepto;
  RETURN totalEmpleados;
END CUENTAEMPLEADOS;

Clase 16 de Mayo

create or replace FUNCTION parImpar (pNumero integer) return varchar2 is
msg varchar2(5):='imPar';
begin
if mod(pnumero,2) = 0 then
   msg := 'Par';
end if;
return msg;
end;
drop procedure concatenar
create or replace FUNCTION concatenar (ptexto1 varchar2,ptexto2 varchar2 ) return varchar2 is
begin
return ptexto1 ||' '||ptexto2;
end;
select concatenar ('7',parimpar(7)) from dual
declare
var varchar2(10);
begin
 var := parimpar(7);
 dbms........
create or replace PROCEDURE Concatenar (ptexto1 varchar2, ptexto2 varchar2) is
begin
  dbms_Output.put_line(ptexto1||' ' ||ptexto2);
end Concatenar;
show err procedure Concatenar
set serveroutput on
Declare
 vtexto varchar2(50) := 'Hola a Todos';
Begin
  Concatenar('Buenos','Dias');
  Concatenar(ptextitoo2=>'Buenos',ptexto1=>'Dias');
End;

Clase 12 de Mayo

--practicando uso de join--

desc employees;
desc departments;

select e.first_name, e.last_name, d.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;

select e.first_name, e.last_name, d.department_id, d.department_name
from employees e join departments d on
(d.department_id = e.department_id)
order by department_id;

select d.department_id, count (*)
from employees e join departments d on
(d.department_id = e.department_id)
group by d.department_id
order by department_id;

09 de Mayp Ejercicio 3 Control 3

set serveroutput on
declare
cursor crEmpleado is
  select e.first_name, e.last_name, d.department_id, d.department_name
  from employees e join departments d on
  (d.department_id = e.department_id)
  order by d.department_id;
 
  cntDept integer;
  cntTotal integer := 0;
  deptoAnterior employees.department_id%type;
  vNombredepto departments.department_name%type;
begin
cntDept:= 0;
deptoAnterior := 9999;
for recEmp in crEmpleado loop
  if deptoAnterior <> recEmp.department_id then
     if deptoAnterior <> 9999 then
      dbms_output.put_line('Total Empleados en '|| deptoAnterior||
                        ' es: '||cntDept);                     
      dbms_output.put_line('     ');     
     end if;
    
    
    dbms_output.put_line(recEmp.department_id||' - '||vNombredepto);
      cntTotal := cntTotal + cntDept;
      cntDept := 0;        
      cntTotal := cntTotal +1;
      cntDept := cntDept +1;
    deptoAnterior := recEmp.department_id;
    vNombredepto := recEmp.department_name;
  end if;   
 
   dbms_output.put_line('     '||recEmp.first_name);
end loop;
   dbms_output.put_line('Total Empleados en '|| deptoAnterior||
                        ' es: '||cntDept);
   dbms_output.put_line('Total general de Empleados: '||cntTotal);
exception
  when others then
  dbms_output.put_line('Error en el Cursor');
end;

09 de Mayo Ejercicio 2 Control 2

set serveroutput on;

declare

cursor crListaEmpleado is
  select FIRST_NAME, LAST_NAME, HIRE_DATE from employees
  order by LAST_NAME;
 
  recEmpleado crListaEmpleado%ROWTYPE;
  cnt integer := 0;
  edad integer;
  msg varchar2(50);

begin
open crListaEmpleado;
fetch crListaEmpleado into recEmpleado;
while crListaEmpleado%FOUND loop
 
  exit when crListaEmpleado%NOTFOUND;
 
  cnt := cnt + 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;
dbms_output.put_line(recEmpleado.FIRST_NAME || ' ' || recEmpleado.LAST_NAME || ' ' || msg);
fetch crListaEmpleado into recEmpleado;
end loop;
close crListaEmpleado;
dbms_output.put_line('El total de empleados es: ' || cnt);

exception
  when OTHERS then
  dbms_output.put_line('esta malo');

end;

09 de Mayo . Ejercicio 1 Control 1

set serveroutput on;

declare

cursor crListaEmpleado is
  select FIRST_NAME, LAST_NAME, SALARY from employees
  order by LAST_NAME;
 
  recEmpleado crListaEmpleado%ROWTYPE;
  cnt integer := 0;

begin
open crListaEmpleado;
loop
  fetch crListaEmpleado into recEmpleado;
  exit when crListaEmpleado%NOTFOUND;
  dbms_output.put_line(recEmpleado.FIRST_NAME || ' ' || recEmpleado.LAST_NAME || ' ' || recEmpleado.SALARY);
  cnt := cnt + 1;
end loop;
close crListaEmpleado;
dbms_output.put_line('El total de empleados es: ' || cnt);

exception
  when OTHERS then
  dbms_output.put_line('esta malo aweonao');

end;

Tarea 3: Cursores Explicitos PL/SQL

Cursores Explicitos.

Un cursor explicito lo tenemos que declarar de la siguiente manera

 CURSOR <nombrecursor> IS <sentencia SELECT>;

Bucle LOOP con una sentencia EXIT condicionada.

set serveroutput on

Declare

cursor Lista is select employee_id, first_name, last_name from employees;

_id employees. employee_id%TYPE;

_nombre employees.first_name%TYPE;

_apellido employees.last_name%TYPE;

Begin

open Lista;

loop

     fetch Lista into _id,_nombre,_apellido;

     exit when Lista%NOTFOUND;

     dbms_output.put_line('ID: ' ||_id|| ' NOMBRE: ' ||_nombre|| ' APELLIDO: ' ||_apellido);

end loop;

close Lista;

exception

when others then

dbms_output.put_line(SQLERRM||'  Error en  la ejecución del programa'); 

end;

 **************************************************************************

Bucle WHILE LOOP

set serveroutput on

declare

Cursor Lista is select employee_id, first_name from employees;

_id employees.employee_id%TYPE

_nombre employees.first_name%TYPE;

_apellido employees. last_name%TYPE;

begin

open Lista;

  fetch Lista into _id,_nombre,_apellido;

  while Lista%FOUND

loop

     fetch Lista into _id,_nombre,v+_apellido;

     exit when Lista%NOTFOUND;

     dbms_output.put_line('ID: ' ||_id|| ' NOMBRE: ' ||_nombre|| ' APELLIDO: ' ||_apellido);

end loop;

close Lista;

exception

when others then

  dbms_output.put_line(SQLERRM||'  Error en  la ejecución del programa');

end;

 ********************************************************************

 Bucle FOR LOOP

set serveroutput on 

declare

Cursor Lista is select employee_id, first_name, last_name from employees;

Begin

For Empleado in Lista

Loop

   dbms_output.put_line('id: ' ||empleado.employee_id|| ' name: ' ||empleado.first_name|| ' lastName' ||empleado.last_name);

End Loop;

Exception

when others then

  dbms_output.put_line(SQLERRM||'  Error en  la ejecución del programa');

End;