DECLARE
vPropietario varchar2(40);
vNombreTabla varchar2(40);
vNombreColumna varchar2(100);
/* Primer cursor */
cursor obtieneTablas is
select distinct t.owner, t.table_name
from all_tables t
where t.owner = '158196506';
/* 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;
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;
miércoles, 21 de diciembre de 2011
Paso de Parametros en Cursores (resuelto)
DECLARE
vPropietario varchar2(40);
vNombreTabla varchar2(40);
vNombreColumna varchar2(100);
/* Primer cursor */
cursor obtieneTablas (pPropietario varchar2) is --en ()se declara el parametro pPropietario
select distinct t.owner, t.table_name
from all_tables t
where t.owner = pPropietario; -- se hace llamado al parametro
/* 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 ('158196506'); --en () se ingresa el valor para el parametro
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;
vPropietario varchar2(40);
vNombreTabla varchar2(40);
vNombreColumna varchar2(100);
/* Primer cursor */
cursor obtieneTablas (pPropietario varchar2) is --en ()se declara el parametro pPropietario
select distinct t.owner, t.table_name
from all_tables t
where t.owner = pPropietario; -- se hace llamado al parametro
/* 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 ('158196506'); --en () se ingresa el valor para el parametro
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;
sábado, 17 de diciembre de 2011
Sabado 17/12/2011
PL/SQL -> Lenguaje de programacion
Estructura
----------
Declare
vTipo Propiedad.Tipo%type
begin
excepcion
end
Loop
-----
declare
I integer;
begin
I:=1;
LOOP
DBMS_OUTPUT.PUT_LINE('Hola Mundo ' || I);
I:=I+1;
exit when I >10;
end loop;
end;
Arreglar, no esta funcionando
-----------------------------
declare
c numeric(15,0);i
begin
cnumeric(15,0); :=0;i
for:=1; i in reverse 15loopc
end
end;=iDBMS_OUTPUT.PUT_LINE(i); loop;
Ejemplo de cursor
============
DECLAREvNombre empleado.nombre%TYPE;vSexo empleado.sexo%TYPE;vTexto varchar2(25);
CURSOR micursor IS SELECT nombre, sexo FROM empleado;BEGINOPEN micursor;LOOPFETCH micursor INTO vNombre, vSexo;EXIT WHEN micursor%NOTFOUND;
if vSexo = 'M' thenvTexto := 'Hombre' ;elsif vSexo = 'F' thenvTexto := 'Mujer';elsevTexto := 'Ambiguo';end if;
dbms_output.put_line (vNombre || ' ' || vSexo || ' ' || vTexto);END LOOP;
CLOSE micursor; END;
Estructura
----------
Declare
vTipo Propiedad.Tipo%type
begin
excepcion
end
Loop
-----
declare
I integer;
begin
I:=1;
LOOP
DBMS_OUTPUT.PUT_LINE('Hola Mundo ' || I);
I:=I+1;
exit when I >10;
end loop;
end;
Arreglar, no esta funcionando
-----------------------------
declare
c numeric(15,0);i
begin
cnumeric(15,0); :=0;i
for:=1; i in reverse 15loopc
end
end;=iDBMS_OUTPUT.PUT_LINE(i); loop;
Ejemplo de cursor
============
DECLAREvNombre empleado.nombre%TYPE;vSexo empleado.sexo%TYPE;vTexto varchar2(25);
CURSOR micursor IS SELECT nombre, sexo FROM empleado;BEGINOPEN micursor;LOOPFETCH micursor INTO vNombre, vSexo;EXIT WHEN micursor%NOTFOUND;
if vSexo = 'M' thenvTexto := 'Hombre' ;elsif vSexo = 'F' thenvTexto := 'Mujer';elsevTexto := 'Ambiguo';end if;
dbms_output.put_line (vNombre || ' ' || vSexo || ' ' || vTexto);END LOOP;
CLOSE micursor; END;
viernes, 16 de diciembre de 2011
Clase Viernes 16/12/2011 (ensayo càtedra)
Lo que hay que saber para la Càtedra
--------------------------------------
insert
constraint
select
delete
dml / ddl
null
drop
in
dual
group
distinct
trim
create table
create sequence
like
where
count
order by
round
desc
between
upper / lower
max / min / avg
Ejercicio
---------
Listar las propiedades que "arrienda" un cliente, listar (nombre, apellido (cliente), numprop, f.inico, (arriendo)
select
c.nombre, c.apellido, a.numpropiedad, a.finrenta from cliente c, arriendo awhere c.numcliente = a.numcliente and UPPER(c.nombre) = 'MIKE' and UPPER(c.apellido) = 'RITCHIE'
select
c.nombre, c.apellido, a.numpropiedad, a.finrenta, e.nombre from cliente c, arriendo a, propiedad p, empleado ewhere c.numcliente = a.numcliente and UPPER(c.nombre) = 'MIKE' and UPPER(c.apellido) = 'RITCHIE'
and a.numpropiedad = p.numpropiedad and p.numempleado = e.numempleadonull -> valor especial para atributos que nunca han tenido contenido
Query forma 1
---------------
select
numoficina from oficinawhere ciudad ='Valdivia'
Query forma 2
---------------
select
numempleado, nombre, apellido from empleado
where numoficina in (select numoficina from oficina where ciudad ='Valdivia')
Ejemplo Subquery
-------------------
select
numempleado, nombre, apellido from empleado
where numoficina in (select numoficina from oficina where ciudad ='Valdivia') and salario > ( select max(salario) from empleado
Ejemplo de create sequence
-------------------------------------
create sequence seqt1start with 10increment by 10;
select seqt1.nextval from dual (comienza en 10)insert
obtiene (nombre, apellido, salario, prmedio, diferencia)
------------------------------------------------------------------------
select
apellido
salario
round nombre, , , ((select avg(salario) as prom from empleado),1) as avg , round
Saca el promedio por oficina
-------------------------------------
select
count
avg numoficina, (*) as empleados, (salario) as promedio, max(salario) as mayor_renta, min
group by numoficina
select b.ciudad, a
count
avg.numoficina, (*) as empleados, (a.salario) as promedio, max(a.salario) as mayor_renta, min
(a.salario) as menor_rentafrom empleado a, oficina bwhere a.numoficina = b.numoficinagroup by b.ciudad, a.numoficina(salario) as menor_renta from empleado
muestra solo los *
------------------------
select
select
numoficina, avg(salario) from empleado
group by numoficinahaving avg(salario) = (select max(avg(salario)) from empleado group by numoficina) '**'|| trim(' ') ||'**' from dual(salario - (select avg(salario) as prom from empleado),1) as Dif from empleado into t1 values (seqt1.nextval, 'hola') (comienza en 20)
where sexo = 'F' )
--------------------------------------
insert
constraint
select
delete
dml / ddl
null
drop
in
dual
group
distinct
trim
create table
create sequence
like
where
count
order by
round
desc
between
upper / lower
max / min / avg
Ejercicio
---------
Listar las propiedades que "arrienda" un cliente, listar (nombre, apellido (cliente), numprop, f.inico, (arriendo)
select
c.nombre, c.apellido, a.numpropiedad, a.finrenta from cliente c, arriendo awhere c.numcliente = a.numcliente and UPPER(c.nombre) = 'MIKE' and UPPER(c.apellido) = 'RITCHIE'
select
c.nombre, c.apellido, a.numpropiedad, a.finrenta, e.nombre from cliente c, arriendo a, propiedad p, empleado ewhere c.numcliente = a.numcliente and UPPER(c.nombre) = 'MIKE' and UPPER(c.apellido) = 'RITCHIE'
and a.numpropiedad = p.numpropiedad and p.numempleado = e.numempleadonull -> valor especial para atributos que nunca han tenido contenido
Query forma 1
---------------
select
numoficina from oficinawhere ciudad ='Valdivia'
Query forma 2
---------------
select
numempleado, nombre, apellido from empleado
where numoficina in (select numoficina from oficina where ciudad ='Valdivia')
Ejemplo Subquery
-------------------
select
numempleado, nombre, apellido from empleado
where numoficina in (select numoficina from oficina where ciudad ='Valdivia') and salario > ( select max(salario) from empleado
Ejemplo de create sequence
-------------------------------------
create sequence seqt1start with 10increment by 10;
select seqt1.nextval from dual (comienza en 10)insert
obtiene (nombre, apellido, salario, prmedio, diferencia)
------------------------------------------------------------------------
select
apellido
salario
round nombre, , , ((select avg(salario) as prom from empleado),1) as avg , round
Saca el promedio por oficina
-------------------------------------
select
count
avg numoficina, (*) as empleados, (salario) as promedio, max(salario) as mayor_renta, min
group by numoficina
select b.ciudad, a
count
avg.numoficina, (*) as empleados, (a.salario) as promedio, max(a.salario) as mayor_renta, min
(a.salario) as menor_rentafrom empleado a, oficina bwhere a.numoficina = b.numoficinagroup by b.ciudad, a.numoficina(salario) as menor_renta from empleado
muestra solo los *
------------------------
select
select
numoficina, avg(salario) from empleado
group by numoficinahaving avg(salario) = (select max(avg(salario)) from empleado group by numoficina) '**'|| trim(' ') ||'**' from dual(salario - (select avg(salario) as prom from empleado),1) as Dif from empleado into t1 values (seqt1.nextval, 'hola') (comienza en 20)
where sexo = 'F' )
viernes, 9 de diciembre de 2011
Clase Viernes 09/12/2011 II
DROPBOX
DIIGO
http://www.diigo.com/user/efischer
Create table
=========
CREATE TABLE PERSONA
(
ID INTEGER ,
NOMBRE VARCHAR2(35) ,
APELLIDO VARCHAR2(40) ,
FECHANAC DATE ,
RENTA NUMERIC(5,1) ,
CONSTRAINT pk_persona primary key (ID)
);
Cantidad de tablas de la BD
===================
select * from TAB
select * from CAT
Insert
====
insert into PERSONA
values (1,'Pedro','PicaPiedra',to_date('01/12/1960','dd/mm/yyyy'), 200.0)
Secuencia (autoincremento) por default comienza de cero
======================================
create sequence seqPersona
start with 1
Increment by 1
drop sequence seqPersona
create sequence seqPersona
start with 2
Increment by 1;
insert into PERSONA
values (seqPersona.nextval,'Pedro II','PicaPiedra II',to_date('01/12/1960','dd/mm/yyyy'), 200.0)
Para martes 13/12/2011 debe estar actualizado el TORPEDO......chaaaaaaa...........
Listar numero de propiedad (numPropiedad) de todas las propiedades que han sido visitadas
===============================================================
select numpropiedad
from visita
order by numpropiedad
(11 registros)
select distinct numpropiedad
from visita
order by numpropiedad
(4 registros)
Listar el salario mensual para todos los empleados, mostrando el numero de empleado, nombre, apellido, y el detalle del salario.
=======================================================================
select numempleado, nombre, apellido, salario, salario /12 as salario_mensual
from empleado
round
=====
select numempleado, nombre, apellido, salario, round((salario /12),5) as salario_mensual
from empleado
Listar todas las oficinas ubicadas en la ciudad de Castellon o Santiago
===============================================
select * from oficina
where upper(ciudad) = 'ARICA'
or lower(ciudad) = 'santiago'
select * from oficina
where upper(ciudad) in ('ARICA','SANTIAGO')
select * from oficina
where upper(ciudad) not in ('ARICA','SANTIAGO')
DIIGO
http://www.diigo.com/user/efischer
Create table
=========
CREATE TABLE PERSONA
(
ID INTEGER ,
NOMBRE VARCHAR2(35) ,
APELLIDO VARCHAR2(40) ,
FECHANAC DATE ,
RENTA NUMERIC(5,1) ,
CONSTRAINT pk_persona primary key (ID)
);
Cantidad de tablas de la BD
===================
select * from TAB
select * from CAT
Insert
====
insert into PERSONA
values (1,'Pedro','PicaPiedra',to_date('01/12/1960','dd/mm/yyyy'), 200.0)
Secuencia (autoincremento) por default comienza de cero
======================================
create sequence seqPersona
start with 1
Increment by 1
drop sequence seqPersona
create sequence seqPersona
start with 2
Increment by 1;
insert into PERSONA
values (seqPersona.nextval,'Pedro II','PicaPiedra II',to_date('01/12/1960','dd/mm/yyyy'), 200.0)
Para martes 13/12/2011 debe estar actualizado el TORPEDO......chaaaaaaa...........
Listar numero de propiedad (numPropiedad) de todas las propiedades que han sido visitadas
===============================================================
select numpropiedad
from visita
order by numpropiedad
(11 registros)
select distinct numpropiedad
from visita
order by numpropiedad
(4 registros)
Listar el salario mensual para todos los empleados, mostrando el numero de empleado, nombre, apellido, y el detalle del salario.
=======================================================================
select numempleado, nombre, apellido, salario, salario /12 as salario_mensual
from empleado
round
=====
select numempleado, nombre, apellido, salario, round((salario /12),5) as salario_mensual
from empleado
Listar todas las oficinas ubicadas en la ciudad de Castellon o Santiago
===============================================
select * from oficina
where upper(ciudad) = 'ARICA'
or lower(ciudad) = 'santiago'
select * from oficina
where upper(ciudad) in ('ARICA','SANTIAGO')
select * from oficina
where upper(ciudad) not in ('ARICA','SANTIAGO')
"Buenas Practicas" en código PL/SQL
Para verificar si se aplican "Buenas Practicas" de programación y para contribuir a realizar código mas robusto y menos propenso a que se generen errores en tiempo de ejecución, a partir de 10g R1, se introdujo un nuevo mecanismo que permite advertir en tiempo de compilación sobre potenciales problemas (WARNINGS), que si bien dejan compilada la unidad de código, pueden darnos dolores de cabeza y conducir a que las aplicaciones que utilizan dicho código generen errores imprevistos o peor aún, que no se obtengan los datos correctos alterando la semántica pretendida y siendo, en muchas ocasiones, muy complicados de detectar.
Existe 4 categorias de WARNINGS:
SEVERE : Pueden causar acciones inesperadas, errores que hagan cancelar una operatoria o resultados erroneos.
PERFORMANCE : Pueden causar problemas de rendimiento.
INFORMATIONAL: No afectan el rendimiento ni altera los resultados pero advierte sobre complicaciones en el mantenimiento del codigo a futuro.
ALL : Contempla todos los casos anteriores.
Para activar los mensajes de warning se puede usar el parametro PLSQL_WARNINGS a nivel sesion o a nivel instancia (cosa que no recomendada), tambien se puede usar el paquete DBMS_WARNING para setear el nivel de warning deseado a nivel de código PL en procedures, packages, triggers, etc.
Consultando la vista [USER | ALL | DBA]_WARNING_SETTINGS se puede saber que objetos tienen activado el warning y consultando la vista [USER | ALL | DBA]_ERRORS, filtrando por el campo ATTRIBUTE='WARNING' se ven todos los warnings generados.
Ahora que ya hice una introduccion rapida al tema, vayamos a los ejemplos:
Habilito para detectar todas las categorias:
rop@DESA10G> alter session set plsql_warnings='ENABLE:ALL';
Sesión modificada.
Creo una tabla sencila
rop@DESA10G> create table t (x int,y varchar(5));
Tabla creada.
rop@DESA10G> insert into t
2 select rownum,
3 trunc(dbms_random.value(1,99999))
4 from dual
5 connect by rownum <= 100000;
100000 filas creadas.
rop@DESA10G> commit;
Confirmación terminada.
Voy a crear un procedimiento P_PRUEBA1 de forma tal de que se detecte un warning:
rop@DESA10G>ed
1 create or replace procedure p_prueba1 (p_val int)
2 is
3 l_cnt int;
4 begin
5 select count(1) into l_cnt
6 from t
7 where y = p_val;
8 if (l_cnt > 0) then
9 dbms_output.put_line ('El valor existe en la
tabla');
10 else
11 dbms_output.put_line ('El valor NO existe en la
11 dbms_output.put_line ('El valor NO existe en la
tabla');
12 end if;
13* end;
13* end;
rop@DESA10G> /
SP2-0804: Procedimiento creado con advertencias de compilación
rop@DESA10G> select text from user_errors where name = 'P_PRUEBA1';
SP2-0804: Procedimiento creado con advertencias de compilación
rop@DESA10G> select text from user_errors where name = 'P_PRUEBA1';
TEXT----------------------------------------------------------------------
PLW-07204: puede que la conversión que no sea de tipo de columna dé como resultado un plan de consulta subóptimo
PLW-07204: puede que la conversión que no sea de tipo de columna dé como resultado un plan de consulta subóptimo
En el caso de arriba detectó un potencial problema de performance, ya que al comparar la columna "y" de tipo varchar2 con el parametro "p_val" de tipo number se hace una conversión implicita TO_NUMBER() de la columna "y". Oracle siempre pasa a number cuando se comparan los tipos number y char/varchar2.
Veamos otros ejemplos:
rop@DESA10G> ed
Escrito file afiedt.buf
1 create or replace procedure p_prueba2 (p_val int)
2 is
3 l_cnt int;
4 begin
5 select count(1) into l_cnt
6 from t
7 where y = to_char(p_val);
8 if ( 0 = 0) then
9 if (l_cnt > 0) then
10 dbms_output.put_line ('El valor existe en la
tabla');
11 else
12 dbms_output.put_line ('El valor NO existe en
12 dbms_output.put_line ('El valor NO existe en
la tabla');
13 end if;
14 else
15 null;
16 end if;
17* end;
rop@DESA10G> /
SP2-0804: Procedimiento creado con advertencias de compilación
rop@DESA10G> select text from user_errors where name = 'P_PRUEBA2';
14 else
15 null;
16 end if;
17* end;
rop@DESA10G> /
SP2-0804: Procedimiento creado con advertencias de compilación
rop@DESA10G> select text from user_errors where name = 'P_PRUEBA2';
TEXT
----------------------------------------------------------------------PLW-06002: Código inaccesible
----------------------------------------------------------------------PLW-06002: Código inaccesible
Este es una advertencia informativa. Ahora voy a crear una función:
rop@DESA10G> ed
Escrito file afiedt.buf
1 create or replace function f_prueba1
2 return int
3 is
4 l_val int;
5 begin
6 l_val := dbms_random.value(1,10);
7* end;
rop@DESA10G> /
SP2-0806: Función creada con advertencias de compilación
rop@DESA10G> select text from user_errors where name = 'F_PRUEBA1';
TEXT
----------------------------------------------------------------------
PLW-05005: la función F_PRUEBA1 se devuelve sin valor en la línea 7
----------------------------------------------------------------------
PLW-05005: la función F_PRUEBA1 se devuelve sin valor en la línea 7
Al no retornar valor se podría generar un problema más grave
rop@DESA10G> ed
Escrito file afiedt.buf
1 create or replace procedure p_prueba3 (p_val varchar2)
2 is
3 begin
4 insert into t (x) values (p_val);
5* end;
rop@DESA10G> /
SP2-0804: Procedimiento creado con advertencias de compilación
rop@DESA10G> select text from user_errors where name = 'P_PRUEBA3';
TEXT
----------------------------------------------------------------------
PLW-07202: el tipo de enlace daría como resultado una conversión lejos del tipo de columna
rop@DESA10G>
El warning para el procedimiento P_PRUEBA3, aunque la traducción al español no es muy clara, da un posible problema en la conversión de tipos. Así podríamos seguir probando otros tantos casos.
La idea fue mostrarles que con esta herramienta se puede mejorar la calidad del software pl/sql y detectar en forma automática y proactiva posibles problemas en tiempo de ejecución.
Para bajar el Archivo en PDF, pincha aquí.
Suscribirse a:
Entradas (Atom)