Package Calculadora

create or replace package pkgCalculadora is
function suma(p1 number, p2 number) return number;
function resta(p1 number, p2 number) return number;
function multiplica(p1 number, p2 number) return number;
function divide(p1 number, p2 number) return number;
function potencia(p1 number, p2 number) return number;
function factorial(p1 number) return number;
function raiz(p1 number, p2 number) return number;
end pkgCalculadora;

/*dbms_output.put_line('2 + 3 = ' || pkgCalculadora.suma(2,3));*/
create or replace package body pkgCalculadora is
procedure imprime (p1 number, p2 number, x char, pvalor number) is
begin
dbms_output.put_line(p1 || ' ' || x || ' ' || p2 || ' = ' || pvalor);
end imprime;

procedure imprime2 (p1 number, pvalor number) is
begin
if p1 > 0 then
dbms_output.put_line(p1 || '!' || ' = ' || pvalor);
else
dbms_output.put_line('el resultado no existe');
end if;
end imprime2;

procedure imprime3 (p1 number, p2 number, pvalor number) is
begin
dbms_output.put_line(p1 || '^' || '1/' || p2 || ' = ' || pvalor);
end imprime3;

function suma(p1 number, p2 number) return number is
Begin
imprime(p1, p2, '+', p1+p2);
return p1+p2;
End suma;

function resta(p1 number, p2 number) return number is
Begin
imprime(p1, p2, '-', p1-p2);
return p1-p2;
End resta;

function multiplica(p1 number, p2 number) return number is
Begin
imprime(p1, p2, '*', p1*p2);
return p1*p2;
End multiplica;

function divide(p1 number, p2 number) return number is
Begin
imprime(p1, p2, '/', p1/p2);
return p1/p2;
EXCEPTION
when Zero_divide then
dbms_output.put_line ('Error !! Division por Cero');
End divide;

function potencia(p1 number, p2 number) return number is
begin
imprime(p1, p2, '^', power(p1,p2));
return power(p1,p2);
end potencia;

function factorial(p1 number)
return number is
resultado number := 1;
temp number := p1;
begin
if p1 > 0 then
while (temp > 0)
loop
resultado := resultado *temp;
temp := temp -1;
end loop;
else
resultado := null;
end if;
imprime2(p1,resultado);
return resultado;
/*dbms_output.put_line(resultado);*/
end factorial;

function raiz(p1 number, p2 number) return number is
resultado number := 1;
begin
resultado := round(power(p1,1/p2),37);
imprime3(p1, p2, resultado);
return resultado;
end raiz;
end;

declare
r number(10);
begin
r := pkgCalculadora.suma(2, 3);
end;

Ejercicios propuestos pl/sql de cursores

Ejercicios sobre pl/sql
en la tercera parte se encuentran ejercicios de cursores

http://www.docstoc.com/docs/23962524/Ejercicios-PLSQL-de-ORACLE-1-Variables-y-Entrada-y

Ejercicio de Cursor

--Desarrolle un Cursor, que permita Estudiar los empleados
--cuyo cargo sea finance_manager o programmer.
--Para aquellos que tienen una renta Menor o igual que el promedio
--entre el valor min y max, definido en la tabla jobs
-- debe indicar "estudiar un aumento".
--Para los que tienen una renta superior al promedio
--hay que indicar "evaluar carga de trabajo".
--Generando un registro en la tabla "estudioSueldo" para cada empleado
--En dicha tabla se debe grabar:
--el ID del Empleado, su Nombre, el Apellido
--La renta, la diferencia con el promedio y el comentario


declare
cursor cr_empleado is
select first_name, last_name,
e.job_id, employee_id, salary

from employees e, jobs j
where e.job_id=j.job_id
and (lower(job_title)='finance manager'
or upper(job_title)='PROGRAMMER');
--variables
vnombre employees.first_name%type;
vapellido employees.last_name%type;
vjob_id employees.job_id%type;
vempleado_id employees.employee_id%type;
vsueldo employees.salary%type;
vsueldomin employees.salary%type;
vsueldomax employees.salary%type;
vpromedio number(8,2);
vmensaje varchar2(50);
vdiferncia number(8,2);

begin
open cr_empleado;
loop
FETCH cr_empleado INTO vnombre, vapellido, vjob_id, vempleado_id,
vsueldo;
exit when cr_empleado%NOTFOUND;
DBMS_output.put_line(vnombre ||' '|| vapellido ||' '|| vjob_id
||' '|| vempleado_id ||' '|| vsueldo);
--traer el valor min y max para ver si el promedio coincide
select min_salary, max_salary into vsueldomin, vsueldomax
from jobs

Más ejemplos de Cursores

Clase 23 de agosto de 2011

SELECT DEPARTMENT_NAME as "Departamento",count(*) as "Total empleados"

FROM employees E, departments D



WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

GROUP BY DEPARTMENT_NAME

desc departments

desc employees





SELECT DEPARTMENT_NAME, count(EMPLOYEE_ID)

FROM employees E, departments D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

GROUP BY DEPARTMENT_NAME

ORDER BY DEPARTMENT_NAME



SELECT DEPARTMENT_NAME, count(EMPLOYEE_ID)

FROM employees E, departments D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

HAVING count(EMPLOYEE_ID)> 3

GROUP BY DEPARTMENT_NAME

ORDER BY DEPARTMENT_NAME





SELECT DEPARTMENT_ID as "Departamento", count(*) as "Total empleados"

FROM employees

GROUP BY DEPARTMENT_ID

ORDER BY DEPARTMENT_ID





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





DECLARE



CURSOR myCursor is



SELECT DEPARTMENT_ID, count(*)

FROM employees

GROUP BY DEPARTMENT_ID

ORDER BY DEPARTMENT_ID;

vDepartamento employees.employee_id%type;

vTotalEmpleados integer;

BEGIN

OPEN myCursor;

LOOP

FETCH myCursor INTO vDepartamento, vTotalEmpleados;

EXIT WHEN myCursor%NOTFOUND;

DBMS_output.put_line(vDepartamento ||' '|| vTotalEmpleados);

END LOOP;

CLOSE myCursor;

END



DECLARE

CURSOR myCursor is

SELECT DEPARTMENT_ID, count(*)

FROM employees

WHERE DEPARTMENT_ID is not NULL

GROUP BY DEPARTMENT_ID

ORDER BY DEPARTMENT_ID;

vDepartamento employees.employee_id%type;

vTotalEmpleados integer;

BEGIN

OPEN myCursor;

LOOP

FETCH myCursor INTO vDepartamento, vTotalEmpleados;

EXIT WHEN myCursor%NOTFOUND;

DBMS_output.put_line(vDepartamento ||' '|| vTotalEmpleados);

END LOOP;

CLOSE myCursor;

END





DECLARE

CURSOR myCursor is

SELECT DEPARTMENT_ID, count(*)

FROM employees

WHERE DEPARTMENT_ID is not NULL

GROUP BY DEPARTMENT_ID

ORDER BY DEPARTMENT_ID;

vDepartamento employees.employee_id%type;

vTotalEmpleados integer;

BEGIN

OPEN myCursor;

DBMS_output.put_line('Departamento	Total empleados');

LOOP

FETCH myCursor INTO vDepartamento, vTotalEmpleados;

EXIT WHEN myCursor%NOTFOUND;

DBMS_output.put_line(vDepartamento ||'              '|| vTotalEmpleados);

END LOOP;

CLOSE myCursor;

END



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





declare

vNumDepto integer := 10;

vNomDepto varchar2(30);

BEGIN

SELECT DEPARTMENT_NAME INTO vNomDepto

FROM DEPARTMENTS

WHERE DEPARTMENT_ID = vNumDepto;

DBMS_output.put_line('El codigo del departamento es : ' || vNumDepto);

DBMS_output.put_line('El codigo del departamento es : ' || vNomDepto);  END





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





DECLARE CURSOR myCursor is SELECT DEPARTMENT_ID, count(*) FROM employees WHERE DEPARTMENT_ID is not NULL GROUP BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID; vDepartamento employees.employee_id%type; vTotalEmpleados integer; vNomDepto char(30); BEGIN OPEN myCursor; DBMS_output.put_line('Departamento	  Total empleados'); LOOP FETCH myCursor INTO vDepartamento, vTotalEmpleados; EXIT WHEN myCursor%NOTFOUND;                  SELECT DEPARTMENT_NAME INTO vNomDepto

FROM DEPARTMENTS

WHERE DEPARTMENT_ID = vDepartamento;

DBMS_output.put_line(vNomDepto||''|| vTotalEmpleados);

END LOOP;

CLOSE myCursor;

END





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





DECLARE



CURSOR myCursor is

SELECT DEPARTMENT_ID, count(*)

FROM employees

WHERE DEPARTMENT_ID is not NULL

GROUP BY DEPARTMENT_ID

ORDER BY DEPARTMENT_ID;

vDepartamento employees.employee_id%type;

vTotalEmpleados integer;

vNomDepto char(20);

vMensaje varchar2(20);

BEGIN

OPEN myCursor;

DBMS_output.put_line('Departamento	  Total empleados');

LOOP

FETCH myCursor INTO vDepartamento, vTotalEmpleados;

EXIT WHEN myCursor%NOTFOUND;               

SELECT DEPARTMENT_NAME INTO vNomDepto

FROM DEPARTMENTS

WHERE DEPARTMENT_ID = vDepartamento;  IF vTotalEmpleados > 5 THEN

vMensaje  := 'Departamento Grande';

ELSE

vMensaje  := 'Departamento Chico';

END IF;  DBMS_output.put_line(vNomDepto||''||               

vTotalEmpleados||'      '||vMensaje);

END LOOP;

CLOSE myCursor;

END

-- algunos select
-- listar cuantos empleados hay por departamento

select DEPARTMENT_ID, count(*)
from employees
group by DEPARTMENT_ID
order by count(*) desc, DEPARTMENT_ID;



________________________________________________________________________________________________________________________________

-- cursores
-- cursor que lista la cantidad de empleados por dpto
declare
cursor curCtaEmpleados is
select DEPARTMENT_ID, count(*)
from employees
group by DEPARTMENT_ID
order by count(*) desc, DEPARTMENT_ID;
vIdDepartamento employees.DEPARTMENT_ID%type;
vTotalEmpleados integer;
begin
open curCtaEmpleados;
loop
fetch curCtaEmpleados into vIdDepartamento, vTotalEmpleados;
exit when curCtaEmpleados%NOTFOUND;
dbms_output.put_line(vIdDepartamento || ' ' ||vTotalEmpleados);
end loop;
close curCtaEmpleados;
end


______________________________________________________________________________________________________________________________


-- cursores
-- cursor que lista los nombres de empleados por dpto
declare
cursor curCtaEmpleados is
select DEPARTMENT_ID, count(*)
from employees
group by DEPARTMENT_ID
order by count(*) desc, DEPARTMENT_ID;
vIdDepartamento employees.DEPARTMENT_ID%type;
vTotalEmpleados integer;
vNombreDepartamento departments.department_name%type;
begin
open curCtaEmpleados;
loop
fetch curCtaEmpleados into vIdDepartamento, vTotalEmpleados;
exit when curCtaEmpleados%NOTFOUND;
select department_name into vNombreDepartamento
from DEPARTMENTS
where department_id = vIdDepartamento;
dbms_output.put_line(vIdDepartamento || ' ' ||vTotalEmpleados);
end loop;
close curCtaEmpleados;
end

declare
resto integer;
contador integer := 0;
x integer := 1;
begin
while x <= 50
if mod(x,2) = 0 then
contador := contador +1;
end if
x := x+
end;

-- calcular la funcion f(x) en el rango -10,10

declare
x integer;
fx integer;

begin
for i in -10.. 100
loop
fx:= power(x,2)+2*x+2;
dbms_output.put_line('f('||x||')= '||fx);
end loop;
end;