--NUMERO PRIMO
set serveroutput on
declare
primo char(2) := 'si';
num integer;
begin
num := to_number('&ingreseElNumero');
dbms_output.put_line('El numero 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('El valor ingresado no es valido');
end;
---------------------------------------------------------------------------------------------------------
CURSOR 1.0
set serveroutput on
declare
cursor miPrimerCursor is
select EMPLOYEE_ID, FIRST_NAME from employees;
vnombre employees.first_name%Type;
vid employees.employee_id%Type;
begin
open miPrimerCursor;
loop
fetch miPrimerCursor into vid,vnombre;
exit when miPrimerCursor%NOTFOUND;
dbms_output.put_line('ID: '||vid||' - ' ||'Nombre: '||vnombre);
end loop;
close miPrimerCursor;
end;
---------------------------------------------------------------------------------------------------------
CURSOR 2.0
set serveroutput on
declare
cursor miPrimerCursor is
select EMPLOYEE_ID, FIRST_NAME, HIRE_DATE from employees order by hire_date desc;
vnombre employees.first_name%Type;
vid employees.employee_id%Type;
vFechaContrato employees.HIRE_DATE%Type;
antiguedad integer;
begin
open miPrimerCursor;
loop
fetch miPrimerCursor into vid,vnombre,vFechaContrato;
exit when miPrimerCursor%NOTFOUND;
antiguedad := round((sysdate - vFechaContrato)/365);
if(antiguedad = 12) then
dbms_output.put_line(antiguedad||' * ' || ' ID: '||vid||' - ' ||'Nombre: '||vnombre);
end if;
end loop;
close miPrimerCursor;
end;
---------------------------------------------------------------------------------------------------------
DOS CURSORES
--select TABLE_NAME from all_tables where owner= 'HR'; --obtener tablas
--select OWNER,TABLE_NAME,column_name from all_tab_columns where TABLE_NAME = 'JOBS' AND owner = 'HR'; -- obtener columnas
set serveroutput on -- para que se vean los dbms :D
declare
nomTabla all_tables.table_name%Type;
nomColumna all_tab_columns.column_name%Type;
cursor CursorTabla is select TABLE_NAME from all_tables where owner ='HR';
cursor CursorColumna is select column_name from all_tab_columns where owner ='HR' ;
BEGIN
open CursorTabla;
loop
fetch CursorTabla into nomTabla;
exit when CursorTabla%NOTFOUND;
open CursorColumna;
loop
fetch CursorColumna into nomColumna;
exit when CursorColumna%NOTFOUND;
dbms_output.put_line(nomTabla || ' - ' || nomColumna);
end loop;
close CursorColumna;
end loop;
close CursorTabla;
end;
No hay comentarios:
Publicar un comentario