trigger con variables

create or replace
TRIGGER LIQUIDACIONPROCESADA
after insert on liquidacion
for each row
declare
  vRut empleado.rut%type;
  vIdEmpleado empleado.idempleado%type;
begin
select idempleado,rut into vIdEmpleado,vRut from empleado where idempleado = :new.idempleado;
insert into funcionariosprocesados(rut,glosa,idempleado) values(vRut,'Funcionario procesado',vIdEmpleado);
end;

Codigo Control, package con Funcion - Rodrigo Rojas

create or replace package pkDreamHome is
function CtaPropiedad (pNumEmpleado char) return number;
end pkDreamHome;
create or replace package body pkDreamHome is
 FUNCTION CtaPropiedad (pNumEmpleado char)
 RETURN NUMBER is
total char;
BEGIN
  select count(*) into total
  from Propiedad
  where numempleado = pNumEmpleado;
  RETURN total;
END CtaPropiedad;
end pkDreamHome;
select pkDreamHome.CtaPropiedad('SL21') from dual;

Funcion De Control - Propiedad por Empleado - Rodrigo Rojas

create or replace FUNCTION CtaPropiedad (pNumEmpleado char)
return integer is
total integer :=0;
begin
select count (numPropiedad)into total
from  Propiedad
where numEmpleado = pNumEmpleado;
return total;
end CtaPropiedad;
select CtaPropiedad('SL21')from dual;

Triggers y Sequence

create sequence seqLiquidacion
start with 1
increment by 1
nomaxvalue;

create trigger liquidacionIncr
before insert on liquidacion
for each row
begin
select seqLiquidacion.nextval into :new.idliquidacion from dual;
end;

Package Luis en clases

create or replace package pkgLuis is
  function cantidadEmpleados(pDepto IN employees.department_id%type) return number;
end pkgLuis;

create or replace
package body pkgLuis is
  FUNCTION cantidadEmpleados(
 pDepto IN employees.department_id%type
)
  RETURN NUMBER AS
 
  totalEmpleados integer;
  BEGIN
    select count(*) into totalEmpleados
    from Employees
    where department_id = 90;
    RETURN totalEmpleados;
  END cantidadEmpleados;
end pkgLuis;

select * from employees where department_id = 90;

set serveroutput on
begin
  dbms_output.put_line('Cantidad :'||pkgLuis.cantidadEmpleados(90));
end;
set serveroutput on
declare
  cantidad number;
begin
  select pkgLuis.cantidadEmpleados(90) into cantidad from dual;
  dbms_output.put_line('Cantidad :'||cantidad);
end;

select pkgLuis.cantidadEmpleados(90) from dual;

package - 06-06-2012

create or replace package pkg_uno is
function CUENTAEMPLEADOS (pDepto IN employees.department_id%type) return number;
end pkg_uno;

create or replace package body pkg_uno is
 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;
end pkg_uno;



    select pkg_uno.CUENTAEMPLEADOS(90) from dual;

Clase Trigger

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;

Trigger

create or replace trigger IngresoDetalleCompra
after insert on DetalleCompra
for each row
begin
  update Producto set StockActual = StockActual + :new.Cantidad where idProducto = :new.idProducto;
end;

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

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;

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;

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;

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;

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;

Cursores

set serveroutput on;
declare
  primo char(2) := 'si';
  num integer;
begin
  num := to_number('&IngreseNumero');
  dbms_output.put_line('El umero ingresado es: '||num);
  for  i in 2..num - 1
    loop
    if mod(num,i)=0 then
      primo := 'no';
    end if;
  end loop;
  if primo = 'si' then
    dbms_output.put_line('El numero es primo');
  else
    dbms_output.put_line('El numero no es primo');
  end if;
  exception
    when VALUE_ERROR then
    dbms_output.put_line('Error el dato ingresado tiene que ser un numero');
end;


set serveroutput on
declare
  cursor mirPrimerCursor is select employee_id,first_name, from employees;
  vid employees.employee_id%TYPE;
  vnombre employees.first_name%TYPE;
begin
  open  mirPrimerCursor;
  loop
    fetch mirPrimerCursor into vid,vnombre;
    exit when mirPrimerCursor%NOTFOUND;
    dbms_output.put_line('ID: '||vid||' Nombre: '||vnombre);
  end loop;
  close mirPrimerCursor;
end;

set serveroutput on
declare
  cursor mirPrimerCursor is select employee_id,first_name,hire_date from employees ORDER BY hire_date DESC;
  vid employees.employee_id%TYPE;
  vnombre employees.first_name%TYPE;
  vFechaContrato employees.hire_date%TYPE;
  antiguedad integer;
begin
  open  mirPrimerCursor;
  loop
    fetch mirPrimerCursor into vid,vnombre,vFechaContrato;
    exit when mirPrimerCursor%NOTFOUND;
    antiguedad := trunc((sysdate - vFechaContrato)/365);
    if antiguedad= 12 then
      dbms_output.put_line('ID: '||vid||' | Nombre: '||vnombre||' | Antiguedad: '||antiguedad);
    end if;
  end loop;
  close mirPrimerCursor;
end;


set serveroutput on
declare
  cursor miSegundoCursorTabla is select table_name from all_tables where owner = 'HR';
  cursor miSegundoCursorColumnas is select owner,table_name,column_name from all_tab_columns where owner = 'HR';
  vNombreTabla all_tables.table_name%TYPE;
  vOwner all_tab_columns.owner%TYPE;
  vTablaNombre all_tab_columns.table_name%TYPE;
  vNombreColumna all_tab_columns.column_name%TYPE;
begin
  open  miSegundoCursorTabla;
  loop
    fetch miSegundoCursorTabla into vNombreTabla;
    exit when miSegundoCursorTabla%NOTFOUND;
      open  miSegundoCursorColumnas;
      loop
        fetch miSegundoCursorColumnas into vOwner,vTablaNombre,vNombreColumna;
        exit when miSegundoCursorColumnas%NOTFOUND;
        dbms_output.put_line('Tabla: '||vNombreTabla||' Columna: '||vNombreColumna);
      end loop;
      close miSegundoCursorColumnas;
  end loop;
  close miSegundoCursorTabla;
end;

Clase Viernes 14 de abril

create table EMPLEADO (
NUMEMPLEADO CHAR(4) not null,
NOMBRE CHAR(30),
APELLIDO CHAR(30),
CARGO CHAR(35),
SEXO CHAR(1),
FECHNAC DATE,
SALARIO FLOAT,
NUMOFICINA CHAR(4),
constraint PK_EMPLEADO primary key (NUMEMPLEADO)
);

insert into empleado values('SL21','Jhon','White','Gerente','M','01/10/45',300000,'B005');
insert into empleado values('SG37','Peter','Denver','Asistente','M','10/11/60',120000,'B006');
insert into empleado values('SG14','David','Ford','Supervisor','M','09/09/58',180000,'B003');
insert into empleado values('SA9','Mary','Lee','Asistente','F','17/09/59',90000,'B007');
insert into empleado values('SG5','Susan','Sarandon','Gerente','F','21/03/60',240000,'B003');
insert into empleado values('SL41','Julie','Roberts','Asistente','F','13/06/63',90000,'B005');
insert into empleado values('SL22','Juan','Blanco','Gerente','M','01/10/44',300000,'B005');
insert into empleado values('SG36','Luis','Jara','Asistente','M','10/11/61',120000,'B003');
insert into empleado values('SG13','David','Gates','Supervisor','M','09/09/58',180000,'B003');
insert into empleado values('SA8','Maria','Bombal','Asistente','F','17/09/59',90000,'B007');
insert into empleado values('SG4','Susana','Sarandons','Gerente','F','21/03/60',240000,'B003');
insert into empleado values('SL40','James','Bond','Asistente','F','13/06/63',90000,'B005');
insert into empleado values('SL50','Juan','Perez','Vendedor','M','13/06/63',151000,'B015');
insert into empleado values('SL60','Jaime','Soto','Vendedor','M','14/06/83',350000,'B115');
insert into empleado values('SL70','Julia','Berne','Vendedor','F','23/01/53',200000,'B215');
insert into empleado values('SL55','Jorge','Fernandez','Vendedor','M','13/06/63',151000,'B015');
insert into empleado values('SL65','Jose','Isla','Vendedor','M','14/06/83',350000,'B115');

select *
from empleado

select nombre, fechnac, (sysdate -fechnac) / 365 as edad
from empleado
where numempleado = 'SL40'

select trim(nombre), fechnac, trunc((sysdate -fechnac) / 365) as edad
from empleado
where upper(numempleado) = upper('SL40')

declare
  vnumEmpleadoTmp char(4);
  vnumEmpleado char(4);
  vnombre varchar(30);
  vfecha date;
  vedad integer(2);


set serveroutput on
declare
  vnumEmpleado empleado.numempleado%TYPE;
  vnombre empleado.nombre%TYPE;
  vfecha empleado.fechnac%TYPE;
  vedad integer;
begin
  vnumEmpleado := '&IngreseNumeroEmpleado';
  select trim(nombre), fechnac, trunc((sysdate -fechnac) / 365) as edad into vnombre,vfecha,vedad
  from empleado
  where upper(numempleado) = upper(vnumEmpleado);
  dbms_output.put_line('Nombre: '||trim(vnombre)||' Fecha Nacimiento: '||vfecha||' Edad: '||vedad);
end;

begin
  vnumEmpleado := '&vnumEmpleadoTmp';
  select trim(nombre), fechnac, trunc((sysdate -fechnac) / 365) as edad into vnombre,vfecha,vedad
  from empleado
  where upper(numempleado) = upper(vnumEmpleado);
  dbms_output.put_line('Nombre: '||vnombre||' Fecha Nacimiento: '||vfecha||' Edad: '||vedad);
end;

set serveroutput on
declare
  vnumEmpleado empleado.numempleado%TYPE;
  vnombre empleado.nombre%TYPE;
  vfecha empleado.fechnac%TYPE;
  vedad integer(2);
begin
  vnumEmpleado := '&IngreseNumeroEmpleado';
  select trim(nombre), fechnac, trunc((sysdate -fechnac) / 365) as edad into vnombre,vfecha,vedad
  from empleado
  where upper(numempleado) = upper(vnumEmpleado);
  dbms_output.put_line('Nombre: '||vnombre||' Fecha Nacimiento: '||vfecha||' Edad: '||vedad);
end;


set serveroutput on
declare
  vnumEmpleado empleado.numempleado%TYPE;
  vnombre empleado.nombre%TYPE;
  vfecha empleado.fechnac%TYPE;
  vedad integer;
begin
  vnumEmpleado := '&IngreseNumeroEmpleado';
  select trim(nombre), fechnac, trunc((sysdate -fechnac) / 365) as edad into vnombre,vfecha,vedad
  from empleado
  where upper(numempleado) = upper(vnumEmpleado);
  dbms_output.put_line('Nombre: '||trim(vnombre)||' Fecha Nacimiento: '||vfecha||' Edad: '||vedad);
end;


set serveroutput on
declare
  vnumEmpleado empleado.numempleado%TYPE;
  vnombre empleado.nombre%TYPE;
  vfecha empleado.fechnac%TYPE;
  vedad integer;
begin
  vnumEmpleado := '&IngreseNumeroEmpleado';
  select trim(nombre), fechnac, trunc((sysdate -fechnac) / 365) as edad into vnombre,vfecha,vedad
  from empleado
  where upper(numempleado) = upper(vnumEmpleado);
  dbms_output.put_line('Nombre: '||trim(vnombre)||' Fecha Nacimiento: '||vfecha||' Edad: '||vedad);
end;

Tarea 2 - Tipos de datos

1) Tipos de datos de PL/SQL versión 10g, 


VARCHAR2 : Cadena de caracteres de longitud variable con un máximo de size bytes de longitud. Debe especificar el tamaño


NVARCHAR2 : De longitud variable de cadena nacional, el conjunto de caracteres que tiene un máximo tamaño de bytes de longitud. 
Debe especificar el tamaño

CHAR : Los datos de caracteres de longitud fija de bytes de tamaño de longitud. Esto se debe utilizar para los datos de longitud fija. 

Nchar : De caracteres de longitud fija nacional de conjunto de datos de tamaño bytes de longitud.Esto se debe utilizar para los datos de longitud fija.

NUMBER : Número de tener la precisión p y escala s.

PLS_INTEGER : enteros con signo 

Los valores PLS_INTEGER menos espacio de almacenamiento y ofrecer un mejor rendimiento que los valores numéricos.
Así que utilice PLS_INTEGER donde se puede

BINARY_INTEGER : enteros con signo (mayor versión más lenta de PLS_INTEGER)

DATE : Intervalo de fechas válido

TIMESTAMP : el número de dígitos en la parte fraccionaria del campo de fecha y hora SEGUNDO.

INTERVAL YEAR : Tiempo en años y meses, donde year_precision es el número de dígitos en el campo de fecha y hora AÑO.

INTERVAL DAY: El tiempo en días, horas, minutos y segundos. 

day_precision es el número máximo de dígitos en el 'DIA'  fractional_seconds_precisiones el número máximo de decimales en el segundo campo.

RAW : Datos binarios de bytes de tamaño de longitud.
Debe especificar el tamaño de un valor RAW.

ROWID : Cadena hexadecimal que representa la dirección única de una fila en su tabla.
(Sobre todo para los valores devueltos por el ROWID pseudocolumna.)

UROWID : Cadena Hex en representación de la dirección lógica de una fila de una tabla organizada por índices

MLSLABEL : Formato binario de un sistema de tipo de datos de funcionamiento label

CLOB : Carácter y objeto grande

NCLOB : Character Large Object Nacional

BLOB: Binary Large Object

BFILE: puntero a un archivo binario en el disco

XMLType: De datos XML

3)  Funciones de conversión entre tipos de datos. 


Aqui algunos ejemplos para adicionar y sustraer días y meses y encontrar la diferencia entre
fechas en Oracle. Estos ejemplos toman el resultado de la tabla "dual. La tabla Dual es una
tabla virtual que existe en todas las Bases de datos Oracle. Muchas veces hemos usado la
consulta SELECT sysdate FROM dual; la cual simplemente nos retorna la fecha y hora
actual.


Ajustar Dias, Semanas, Horas y minutos


Para adicionar y sustraer días a una fecha, simplemente usamos los signos + o -
respectivamente. Algunos ejemplos:


SELECT sysdate + 7 FROM dual;


salida:
SYSDATE+7
18/04/12
------------------------------------------------------------------------
SELECT sysdate - 30 FROM dual;


salida
SYSDATE-30
12/03/12
--------------------------------------------------------------------------------
SQL> SELECT to_char(sysdate - 14, 'MM/DD/YYYY HH:MI AM') FROM dual;


salida
TO_CHAR(SYSDATE-14,'MM/DD/YYYYHH:MIAM')
03/28/2012 12:44 AM


--------------------------------------------------------------------------------
En el primer ejemplo, vemos que la consulta retorna la fecha siete días apartir de hoy. La segunda
retorna la fecha de hace 30 días. En la tercera, se ha usado la función de conversión to_char,
para ver las horas y minutos. Esto nos muestra que mientras la fecha, ha cambiado, el tiempo no
cambia.
Los ejemplos primero y tercero ademas muestran como el adicionar días, puede ser útil para
computar semanas. Si ud desea simplificar el computo de semanas, podías usar querys de este
tipo:
SQL> SELECT sysdate + (7 * 3) FROM dual;


salida
SYSDATE+(7*3) 02/05/12
--------------------------------------------------------------------------------


Para trabajar con meses y años (cada uno de los cuales puede variar en número de días)
Oracle ofrece la función numtoyminterval. Esta trabaja en forma similar a la función
numtodsinterval tomando un numero y una cadena string. Las opciones válidas para esta
función son: ‘YEAR’ or ‘MONTH’.


SELECT to_char(sysdate + numtoyminterval(5, 'MONTH'), 'MM/DD/YYYY') FROM dual;


salida
TO_CHAR(SYSDATE+NUMTOYMINTERVAL(5,'MONTH'),'MM/DD/YYYY')
09/11/2012
------------------------------------------------------------------------------------------


SELECT to_char(sysdate + numtoyminterval(2, 'YEAR'), 'MM/DD/YYYY') FROM dual;


salida
TO_CHAR(SYSDATE+NUMTOYMINTERVAL(2,'YEAR'),'MM/DD/YYYY')
04/11/2014
------------------------------------------------------------------------------------------


Comparar fechas


Suponga que deseamos comparar algunas fechas y encontrar el número de días entre ellas. Para
ver este resultado en días, simplemente basta con usar el operador -, de la siguiente manera:


SELECT TO_DATE('11/04/2006', 'MM/DD/YYYY') - sysdate FROM dual;


salida
TO_DATE('11/04/2006','MM/DD/YYYY')-SYSDATE
-1985,0386111111111111
------------------------------------------------------------------------------------------


El resultado es expresado en días, incluyendo horas fracción de horas. Por supuesto si se desea
semanas, se podría dividir el resultado entre 7. De manera similar, si se desea obtener horas, se
debería multiplicar por 24, pero si se desea meses, entonces se podría usar la función
months_between.


SELECT months_between(sysdate, to_date('01/01/2006', 'MM/DD/YYYY')) FROM dual;


salida
MONTHS_BETWEEN(SYSDATE,TO_DATE('01/01/2006','MM/DD/YYYY'))
75,323867607526881
------------------------------------------------------------------------------------------


Retornar la mayor y menor fecha dentro de un conjunto


Las funciones greatest y least pueden ser usadas en fechas para retornar la menor o la mayor
fecha.


SELECT greatest(sysdate,sysdate+1, to_date('09/11/2005','MM/DD/YYYY'), to_date('12/25/
2005','MM/DD/YYYY'))FROM dual;


salida


GREATEST(SYSDATE,SYSDATE+1,TO_DATE('09/11/2005','MM/DD/YYYY'),TO_DATE('12/25/2005','MM/DD/YYYY'))


12/04/12


------------------------------------------------------------------------------------------


SELECT least(sysdate,sysdate+1,to_date('09/11/2005','MM/DD/YYYY'), to_date('12/25/
2005','MM/DD/YYYY'))FROM dual;


salida


LEAST(SYSDATE,SYSDATE+1,TO_DATE('09/11/2005','MM/DD/YYYY'),TO_DATE('12/25/2005','MM/DD/YYYY'))


11/09/05
------------------------------------------------------------------------------------------


Cual es el último día de este mes?


La función last_day retorna el último día del mes pasado como parámetro.


select last_day(sysdate) from dual;


salida
LAST_DAY(SYSDATE) 30/04/12


------------------------------------------------------------------------------------------


Siguiente día de la semana despues de una fecha


La función next_day retorna la fecha de la próxima ocurrencia de un día de la
semana(’Monday’, ‘Tuesday’, etc.) después de una fecha dada. ejemplo: consultar la fecha del
próximo domingo:


select next_day(sysdate,'Domingo') from dual;


salida
NEXT_DAY(SYSDATE,'DOMINGO') 15/04/12


------------------------------------------------------------------------------------------


3)  Funciones para manipular fechas

TO_CHAR

Con esta función es posible convertir números a caracteres, otra versión de la misma función nos permite hacer lo mismo con los tipos de dato asociados con fechas y tiempo. De igual manera que con los números, TO_CHAR ofrecen un gran número de posibilidades para formatear fechas y que estas aparezcan tal y como queramos.

Ejemplo:

Convertir números:

Begin
Dbms_output.put_line (to_char (sysdate));

Dbms_output.put_line (to_char (systimestamp));
End;

Resultado:

11/04/12
11-ABR-12 01.10.57,711000000 AM -03:00



Devuelve dia de la "de"semana y el nombre del mes

Begin
dbms_output.put_line(to_char (sysdate,'day,dd "de" Month "de" YYYY' ));
end;

Resultado

miércoles,11 de Abril      de 2012

Statement processed.

Clase 30-03-12 pl/sql - exception

http://www.devjoker.com/contenidos/Tutorial-PLSQL/48/Excepciones-en-PLSQL.aspx

set serveroutput on

declare
  nombre varchar2(25);
begin
  dbms_output.put_line('Hola ' ||'&nombre');
end;

--

set serveroutput on

declare
  diaSemana integer;
  fechaIngresada char(2);
begin
  diasemana := to_number('&fechaIngresada');
  dbms_output.put_line('El numero ingresado es: ' || diaSemana);
EXCEPTION
  when VALUE_ERROR then
  dbms_output.put_line('Error el numero ingresado tiene que ser un numero');
end;

--

set serveroutput on

declare
  diaSemana integer;
  IngreseValorNumerico char(2);
begin
  diasemana := to_number('&IngreseValorNumerico');
  dbms_output.put_line('El numero ingresado es: ' || diaSemana);
EXCEPTION
  when VALUE_ERROR then
  dbms_output.put_line('Error el numero ingresado tiene que ser un numero');
  when others then
    rollback;
    raise;
end;

--

set serveroutput on

declare
  diaSemana integer;
  IngreseValorNumerico char(2);
begin
  diasemana := to_number('&IngreseValorNumerico');
  if diasemana > 0 and diasemana < 8 then
    dbms_output.put_line('El numero ingresado es: ' || diaSemana);
  else
    dbms_output.put_line('Error: numero ingresado fuera de rango.');
  end if;
EXCEPTION
  when VALUE_ERROR then
  dbms_output.put_line('Error: el numero ingresado tiene que ser un numero');
  when others then
    rollback;
    raise;
end;

--
set serveroutput on

declare
  numero integer;
  suma integer := 0;
  IngreseValorNumerico char(2);
begin
  numero := to_number('&IngreseValorNumerico');
  for i IN 1..numero
  loop
    suma := suma + i;
  end loop;
  dbms_output.put_line('Valor ingresado: '||numero||' la suma es:' || suma);
EXCEPTION
  when VALUE_ERROR then
  dbms_output.put_line('Error: el numero ingresado tiene que ser un numero');
  when others then
    rollback;
    raise;
end;

--

set serveroutput on

declare
  numero integer;
  suma integer := 0;
  i integer :=0;
  IngreseValorNumerico char(2);
begin
  numero := to_number('&IngreseValorNumerico');
  while i <= numero loop
    suma := suma + 1;
    i++;
    end loop;
  dbms_output.put_line('Valor ingresado: '||numero||' la suma es:' || suma);
EXCEPTION
  when VALUE_ERROR then
  dbms_output.put_line('Error: el numero ingresado tiene que ser un numero');
  when others then
    rollback;
    raise;

Buenas practicas PL / SQL

Pauta para variables y estructuras de datos

Declarar, definir el tipo, inicializar y asignar valor por defecto a las estructura de datos antes de trabajar con ellas

PL/SQL es un lenguaje fuertemente tipificado. Esto significa que antes de trabajar con cualquier tipo de estructura de datos, es necesario declarar las mismas, definir el tipo y opcionalmente inicializarlas o asignarles un valor por defecto. Las declaraciones deben realizarse en la sección de declaración de variables de un bloque anónimo, procedimiento, función o paquete.

En las declaraciones que se relacionan a tablas y columnas utilizar la cláusula

%TYPE y %ROWTYPE.

Esta forma permite al código adaptarse a cambios de estructuras de datos yautodocumentarse, ya que al leer el código se puede saber a qué tipo de dato hace referencia. Para el tipo VARCHAR2, Oracle separa la memoria necesaria según la longitud definida. Es muy común ‘Hardcodear’ la longitud del mismo a su máxima tamaño para evitar problemas, a costo de utilizar más memoria de la necesaria. Aquí nuevamente se hace conveniente el uso de%TYPE o %SUBTYPE para un mejor aprovechamiento de los recursos de memoria.

En los datos numéricos, definir la precisión.

Oracle soporta hasta 38 dígitos de precisión en los tipos NUMBER .

Si no se define en forma correcta se está derrochando memoria.

Siempre que sea posible, utilizar el tipo de dato RECORD para manejar estructuras.

La declaración de variables individuales o dispersión de datos, muchas veces, complica  la lectura del código. La agrupación de los mismos bajo estructuras, facilita la administración y el mantenimiento del código.

Los errores de un código solo pueden ser capturados en la sección ejecutable de un bloque. Si la inicialización de una variable en la sección de declaración falla, el error no se puede manejado. Para ello hay que asegurar que la inicialización lógica no falle y esto se asegura haciendo las inicializaciones al comienzo de la sección de ejecución. Si se produce un error, se puede capturar el error y decidir su tratamiento.

Reemplazar expresiones complejas con variables booleanas y funciones.

Las expresiones booleanas se pueden evaluar con tres valores: TRUE, FALSE o NULL

.Se pueden usar variables de este tipo para ocultar expresiones complejas. Como consecuencia se puede leer más fácilmente el código y es más simple su mantenimiento.

Tener cuidado con las conversiones implícitas de tipos de datos.

Si bien es sabido que PL/SQL maneja las conversiones implícitas, existen al menos dos grandes problemas con esto. Las conversiones no son intuitivas, a veces se realizan de formas no esperadas y ocurren problemas, especialmente dentro de sentencias SQL. Las reglas de conversión no están bajo el control de desarrollador. Pueden cambiar con el upgrade a una versión de Oracle o con el cambio de parámetros como NLS_DATE_FORMAT.

  

Se puede convertir con el uso explícito de funciones como son:

TO_DATE, TO_CHAR,TO_NUMBER y CAST.

Pauta para declaración y uso de variables de paquetes.

Agrupar los tipos de datos, evitar su exposición y controlar su manipulación.

Los paquetes requieren ciertos tipos de recaudos en la forma de declaración y uso de variables. Agrupar los tipos de datos, evitar su exposición y controlar su manipulación permiten obtener las ventajas del uso de los mismos.

Definir las constantes que son referenciadas por toda la aplicación en un paquete único.

Definir las constantes que corresponden a un área específica dentro de un paquete que encapsula esa funcionalidad nunca colocar literales ‘en duro’, como ‘SI’ o 150 en el código. Es conveniente crear un paquete para mantener estos nombres y valores publicados en reemplazo de los literales.

Este tipo de práctica permite que el código no luzca ‘hard codeado’, lo cual lo hace más legible y mantenible y además evita que los literales sean modificados.

Centralizar las definiciones de Types en las especificaciones del paquete. A medida que se usan los features del lenguaje, se definirán distintos TYPE entre los que podemos incluir:

SUBTYPEs que definen tipos específicos de la aplicación.

Collection TYPEs, como lista de números, fechas y records.

Cursores referenciados

Esto permite tener estandarizados los tipos de datos para que sean usados por múltiples programas. Los desarrolladores pueden escribir más rápidamente y disminuir los bugs. También simplifica el mantenimiento de los types, ya que solo es necesario modificarle/los paquetes donde están declarados.

Disminuir el uso de variables globales en paquetes y en caso de hacerlo, solo en el cuerpo del paquete. Una variable global es una estructura de datos que se puede referenciar fuera del alcance o bloque en donde está declarada. Cuando se declara una variable en un paquete, existe y retiene su valor durante la duración de la sesión. Son peligrosas porque crean dependencias ocultas o efectos laterales. El seguimiento delas mismas es complejo, ya que es necesario ver la implementación para ver sus instancias. Una solución general para esto es pasar estas variables globales como un parámetropara no referenciarlas directamente en el programa.

Exponer las variables globales de los paquetes usando ‘get and set’. Cualquier estructura de datos declarada en la especificación del paquete puede ser referenciada por cualquier programa con autorización de EXECUTE. Esto hace que en forma deliberada se pueda hacer uso de esas estructuras globales. Para evitar eso, es conveniente declarar estos datos en el cuerpo del paquete y proveer al paquete de métodos get y set, declarados en la especificación. Esta forma le permite a los desarrolladores, acceder a los datos a través de estos programas y manipular los datos según las reglas de estos procedimientos.

Pautas para el uso de estructuras de Control

Las estructuras de control pueden convertirse en un punto de complejidad de un código. La normalización del uso de RETURN y el EXIT garantiza rapidez en la comprensión de las lógicas escritas y facilidad en su mantenimiento.

Nunca se sale de una estructura repetitiva con RETURN o con EXIT. Cada estructura repetitiva tiene un punto de control por donde se debe producir su salida.

Un FOR loop itera desde el valor de comienzo hasta el valor de terminación (ciclo N).

Un WHILE loop itera mientras no se cumpla la condición de terminación. (Ciclo0).

Existe un LOOP con control de la condición al final de ciclo (ciclo 1). Es LOOP … EXIT WHEN condición lógica.

Combinando estos ciclos se puede hacer que las estructuras tengan un único punto de control para el ciclo repetitivo. Esto facilita la lectura, comprensión y mantenimiento del programa y permite hacer modificaciones en forma más simple.

Una función debe tener un único RETURN exitoso como última línea de la sección ejecutable. Normalmente, cada manejador de excepciones puede retornar un valor. En los programas largos donde se evalúan muchas condiciones, la existencia de múltiples salidas dificulta el entendimiento y mantenimiento del código.

Nunca declarar el índice de un FOR .. LOOP.

PL/SQL ofrece dos tipos de FOR LOOP: Numéricos y cursores.

Ambos tienen este formato general:

FOR indice_loop IN loop range LOOP

Loop body

END LOOP;

El índice del loop se declara implícitamente durante la ejecución. El ámbito de existencia está restringido al cuerpo del loop. Si se declarase el índice, se estaría generando otra variable completamente separada que en el mejor de los casos nunca será usada, pero si se usa fuera del loop, puede introducir errores.

Pautas para el manejo de excepciones

Aunque se escriba un código perfecto, que no contenga errores y que nunca realice operaciones inapropiadas, el usuario podría usar el programa incorrectamente, produciendo una falla que no estaba contemplada. El uso de excepciones permite capturar y administrar los errores que se pueden producir dentro del código. Su buen uso trae como resultado un código con menos bugsy  más fácil de corregir.

Establecer los lineamientos para el manejo de errores antes de comenzar a codificar. Es impráctico definir secciones de excepciones en el código después de que el programa fue escrito. La mejor forma de implementar un manejo de errores en toda la aplicación es usando paquetes que contengan al menos los siguientes elementos.

Procedimientos que realicen el manejo de tareas de excepciones, como por ejemplo escribir un log de errores.

Un programa que oculte la complejidad de RAISE_APPLICATION_ERROR y los números de aplicación-error.

Una función que retorne el mensaje de error para un código de error.

Utilizar el modelo por defecto de manejo de excepciones para propagar la comunicación de errores. Aprovechar la arquitectura de manejo de errores de PL/SQL y separar las excepciones en bloques.

La sección de código ejecutable debe estar limpia, simple y fácil de seguir. No es necesario controlar el estado después de cada llamada a un programa. Simplemente debe incluirse una sección de excepción, capturar el error y determinar la acción a realizar.

Capturar todas las excepciones y convertir el significado de los códigos de error en los retornos a programas que no son PL/SQL. Cuando los programas PL/SQL son llamados por otros lenguajes de programación (Java, Visual Basic, etc.), al menos es necesario regresar el estado de error (código y mensaje)para que administren los mismos .Una manera sencilla de hacer esto es sobrecargar el programa original con otro del mismo nombre y dos parámetros adicionales.

Los desarrolladores pueden llamar al procedimiento que deseen y chequear los errores de la manera más apropiada para sus programas.

Usar procedimientos propios de RAISE  en lugar de las llamadas explicitas a

RAISE_APPLICATION_ERROR. Cuando se está controlando excepciones de sistemas como

NO_DATA_FOUND, se usa RAISE, pero cuando se quiere controlar un error de aplicación específico, se usa RAISE_APPLICATION_ERROR. Para el último caso, se debe asignar un número de error y mensaje, lo cual termina en un ‘hardcodeo’.Para ello se puede utilizar constantes para especificar un número. Una manera más clara es proveer un procedimiento que automáticamente controle el número de error y determina la forma de ejecutar el error.

Los desarrolladores no tienen que determinar cuál es el número de error que tienen que usar, solo pasan la constante que desean usar y dejan que la rutina determine cuál es el RAISE correspondiente.

No sobrecargar una EXCEPTION con múltiples errores al menos que la pérdidade información sea intencional.

No declarar una excepción genérica como ERROR_GENERAL y luego ejecutar la excepción en diferentes circunstancias. La lectura del código traerá problemas alintentar cual fue la causa de problema. La excepción NO_DATA_FOUND se puede utilizar para indicar que no se encontraron filas en la ejecución de un query o para indicar que se llegó al final de un archivo.

No Manejar excepciones que no pueden evitarse, pero si anticiparse. Si cuando se está escribiendo un programa, se puede predecir que un error va a ocurrir, se debería incluir un manejador para ese caso.

Evitar exponer código en duro para el manejo de errores, reemplazar por procedimientos que administren los mismos. Evitar exponer código en duro para el manejo de errores, reemplazar por procedimientos que administren los mismos. La mejor manera de registrar en forma consistente y tener mantener la calidad del seguimiento de errores por toda la aplicación es ofreciendo un conjunto de procedimientos predefinidos que canalicen el manejo de los mismos. Es fundamental que el equipo de desarrollo use siempre solamente estos procedimientos en sus cláusulas WHEN.

Usar nombres constantes para flexibilizar los números y mensajes de errores de aplicación, Oracle define 1000 números de error, entre –20000 y –20999 para usar para nuestra aplicación. Se recomienda definir todos los errores en una tabla o un archivo del sistema operativo, construir paquetes para manipular esos datos y ejecutar el RAISE usando esos nombres y no códigos en duro.

Usar WHEN OTHERS solo para excepciones desconocidas que deben serejecutadas. No es conveniente usar WHEN OTHERS para cualquier error. Si se puede conocer el tipo de excepción que se quiere ejecutar, es conveniente administrarla en forma específica.

Pautas mínimas para uso de SQL dentro de PL/SQL

La escritura de SQL dentro del código es uno de los aspectos más flexibles de PL/SQL y también puede ser uno de los más peligrosos. El establecimiento de reglas claras para su inclusión permite asegurar beneficios y buenos resultados.

Usar transacciones autónomas para aislar los efectos del ROLLBACK y COMMIT Permite grabar o realizar rollback dentro de un código PL/SQL sin afectar la sesión principal.

No utilizar COMMIT o ROLLBACK dentro de los procedimientos PL/SQL que son utilizados por procesos en otros leguajes. El COMMIT o ROLLBACK debe utilizarse dentro del proceso que llama los procedimientos PL/SQL. Cuando los procedimientos PL/SQL son llamados por otros lenguajes de programación (Java, Visual Basic, etc.) no deben utilizar las sentencias de manejo de transaccionesCOMMIT o ROLLBACK de modo de posibilitar un ordenado control de transacciones.

Usar COUNT solo cuando es necesario saber el número de ocurrencias. No usar

COUNT para saber si hay una o más de una fila para una condición dada. Solo se debe usar

COUNT para saber cuántas filas hay.

Usar cursores implícitos para realizar operaciones sobre muchos registros este constructor realiza la apertura y cierre en forma automática. Es fácil de usar y su lectura es más simple.

Nunca usar cursores implícitos para una fila El FOR LOOP está diseñado para múltiples filas de un cursor. Para un solo registro es más eficiente un SELECT INTO o un cursor explícito.

Parametrizar cursores explícitos. Los cursores retornan información como lo haría una función, por lo cual también pueden aceptar parámetros. Si se define un cursor con parámetros, se puede hacer quesea reutilizable en diferentes circunstancias de un programa. Esto es mejor si se define en un paquete.

Usar RETURNING para obtener información sobre filas modificada

Referenciar atributos del cursor inmediatamente después de ejecutar la operación de SQL. Las sentencias INSERT, UPDATE y DELETE se ejecutan como cursores implícitos en PL/SQL. Es decir, no se puede declarar, abrir y procesar este tipo de operaciones en forma explícita. El motor de Oracle SQL toma el control y administración del cursor. Se puede obtener información acerca de los resultados de estas operaciones implícitas más recientemente utilizada consultando los siguientes atributos de cursor.

SQL%ROWCOUNT: Números de filas afectadas por la instrucción DML.

SQL%ISOPEN: Siempre es falso en los manejos de cursores implícitos.

SQL%FOUND: Retorna TRUE si la operación afecta al menos una fila.

SQL%NOTFOUND: Retorna FALSE si la operación afecta al menos una fila. Hay un solo set de atributos SQL% en una sesión, el cual refleja la última operación implícita realizada. Por lo cual el uso de estos atributos deberá hacerse con el mínimo uso de código entre la operación y la referencia al atributo. De otra manera el valor retornado por el atributo podría no corresponder al SQL deseado.

Usar BULK COLLECT para mejorar la performance de los querys multiregistros. En algunas oportunidades es necesario recuperar un gran número de filas desde la base de datos.

BULK COLLECT permite obtener el set de filas en una sola llamada al motor y recuperar el resultado en un arreglo (COLLECTION).Para usar este método es necesario declarar un arreglo para mantener los datos recuperados y luego anteponer al INTO la cláusula BULK COLLECT.

Esta forma de recuperación de datos mejora (en algunos casos enormemente) la performance del query .Se debe tener cuidado cuando el

SELECT retorna miles de filas ya que si el mismo programa está corriendo con diferentes usuarios en una misma instancia, puede haber problemas de memoria. Se podría restringir usando ROWNUM o LIMIT para cada instancia.

Usar FORALL para mejorar la performance de DML basados en arreglos.

  

Para los casos en que es necesario modificar (INSERT, DELETE o UPDATE) un gran número de filas en una base de datos dentro de un PL/SQL, se recomienda la sentencia FORALL

. La performance mejora notablemente ya que se reduce el número de llamadas entre PL/SQL y el SQL engine.

Reglas generales para performance

Para el manejo de datos dentro de un código PL/SQL es necesario tener en cuenta las siguientes premisas.

Una sentencia SQL es más rápida que un programa. Siempre es preferible utilizar la potencia del SQL antes que emular su funcionamiento con un PL/SQL largo y complejo.

Un programa pequeño es más rápido que uno extenso. El uso de programas largos agrega más tiempo de ejecución. El uso de paquetes pequeños permite al servidor disponer de ellos de manera más rápida y eficiente

Evitar llamadas repetitivas al sql engine Mientras menos solicitudes de servicios se hagan al sql engine, más rápido se ejecutará un programa. Para el caso de operaciones de SQL similares o repetitivas, utilizarFORALL, BULKS, variables BIND, etc.