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;
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.
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.
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;
declare
cursor ParImpar is select employees.employee_id,
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;
Suscribirse a:
Entradas (Atom)