Clase RECUPERATIVA II - Miércoles 28 de Diciembre de 2011

/* MANEJO DE FECHAS */

select sysdate from dual;

/* Ver como cambia de minuscula mayuscula */
select to_char(sysdate,'Day') from dual;

/* Agrega un mes al actual */
select ADD_MONTHs(sysdate,1) from dual;


/*Identificar en que dia TERMINA el mes */
select last_day(to_date('20/02/2012','dd/mm/yyyy')) from dual;


select months_between(sysdate,to_date('20/02/2012')) from dual;

/*Siguiente dia */
select next_day(sysdate,'lunes') from dual;

/*------------------------------*/
/* --> Pregunta de PRUEBA <--*/
/* Se puede contenar con: || y con CONCAT (cad1, cad2)= Devuelve "cad1" concatenada con "cad2".*/
/*------------------------------*/

/*Rellenar cadena*/
Select lpad('hola mundo',20,'+') from dual;
Select lpad('hola mundo',2,'+') from dual;

Select Rpad('hola mundo',20,'+') from dual;
Select Rpad('hola mundo',2,'+') from dual;

/*Reemplazo de caracteres */
select replace('kkkkkhhh','k','x') from dual;

/*Busca una cadena dentro de otra*/
select substr('toy cagao de sueño',5,5) from dual;

/*Transforma o cambia una caracter por otro*/
select translate('toy cagao de sueño','o','x') from dual;

/*obtiene el entero siguiente*/
Select ceil(3.7) from dual;

/*El resto de un numero*/
Select mod(10,2) from dual;

/*Validar el NULO de un numero*/
Select nvl(null,2) from dual;
Select nvl(trim(' '),1) from dual;

/*Potencia*/
Select power(2,4) from dual;

/*Redondea*/
Select round(2.5) from dual;

/*Acerca a la parte entera*/
Select trunc(2.5) from dual;

/*Funcion DECODE */
select * from empleado
select sexo, decode(sexo,'F','femenino','M','Macho') "Tipo de sexo" from empleado

/*Uso de CASE */
select salario,
case
when salario > 1000 then 0
else 1
end
from empleado




/*------------------------------------------------*/
/* FUNCIONES */
/*------------------------------------------------*/

/* Crear funciones */

/*CREATE [OR REPLACE] FUNCTION function_name.   
[ (parameter [,parameter]) ].   
RETURN return_datatype.IS |
AS.    [declaration_section].
BEGIN.    executable_section.
[EXCEPTION.    exception_section].END [function_name];*/


CREATE or replace function Par_Impar(pnumero integer)
return varchar2
AS
vtexto varchar2(10);
BEGIN
if mod(pnumero,2)=0 then
vtexto:='Es par';
else
vtexto:='Impar';
end if;

Return vtexto;
END Par_Impar;


select salario, par_impar(salario) from empleado;

Clase RECUPERATIVA - Miércoles 21 de Diciembre de 2011

Tablas all_tables y all_tab_columns



select distinct owner, table_name
    from all_tables tablas
    where owner = '141565168';




select distinct column_name
    from all_tab_columns col
    where upper(owner) = 'HR'
    and lower(table_name) = '141565168';



select * from all_tables;

/********************************************************************************************/


tablas all_tables y all_tab_columns asociadas a un usuario

select owner, table_name 
from all_tables 
where owner ='141565168';


select column_name, owner, table_name 
from all_tab_columns;



/********************************************************************************************/


Realizar un cursor con catalogo del usuario



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;

/********************************************************************************************/


Funciones



Usando dual


select sysdate from dual;

¿Qué día de la semana nació usted?

select  
to_char(to_date(‘09-SEP-1958','dd-mon-yyyy'),'day') 
from dual;


select to_char(sysdate,'day') 
from dual;


dia de nacimiento


select to_char(to_date('14-01-1981','dd-mm-yyyy'),'day') 
from dual;



/********************************************************************************************/


%TYPE



create procedure update_emp (empid in number) 
is 
 v_empid number; 
 v_fname varchar2(20); 
 v_lname varchar2(30); 
 v_hire date; 
 v_sal number; 
begin 
end; 


create procedure update_emp (empid in number) 
is 
 v_empid employees.employee_id%type; 
 v_fname employees.first_name%type; 
 v_lname employees.last_name%type; 
 v_hire employees.hire_date%type; 
 v_sal employees.salary%type; 
begin 
end; 


begin 
 select employee_id,first_name, 
 last_name, hire_date, salary into v_empid, v_fname, v_lname, v_hire, v_sal 
 from employees 
 where employee_id = empid; 
end;


create procedure update_emp (empid in number) is 
--declare 
TYPE emp_record_type IS RECORD 
(v_empid employees.employee_id%type;
 v_fname employees.first_name%type; 
 v_lname employees.last_name%type; 
 v_hire employees.hire_date%type; 
 v_sal employees.salary%type; 
); 
emp_record emp_record_type; 
begin ...

/********************************************************************************************/


Creando tablas para pruebas



create table tx (x int,y varchar(5));

insert into tx
select rownum, trunc(dbms_random.value(1,99999))
from dual
connect by rownum <= 10;

select * from tx;


/********************************************************************************************/

Pregunta de prueba


TO_CHAR: 
Transforma un tipo DATE ó NUMBER en una cadena de caracteres. Ejemplos:
to_char(45.31, '99.9') -> retorna '45.3' 
to_char(9,125.33, '9,999.99') -> retorna '9,125.33'
to_char(77, '0099') -> retorna '0077'
TO_DATE: 
Transforma un tipo NUMBER ó CHAR en DATE. 
Ejemplo:
to_date('31/10/2007','DD/MM/YYYY‘) -> retorna ‘31/10/2007’
TO_NUMBER: 
Transforma una cadena de caracteres en NUMBER. Ejemplo:
to_number('123') -> retorna 123

/********************************************************************************************/

Funciones que devuelven valores numéricos


ASCII(cad)= Devuelve el valor ASCII de la primera letra de la cadena "cad". Ejemplo:
ASCII('R') -> Retorna 82
INSTR (cad1, cad2 [, comienzo [,m]])= Permite una búsqueda de un conjunto de caracteres en una cadena pero no suprime ningún carácter después. Ejemplo:
INSTR('CORPORATE FLOOR','OR', 3, 2) ->El resultado obtenido es 14, busca la segunda ocurrencia de la cadena OR a partir de la tercer posición
LENGTH (cad)= Devuelve el numero de caracteres de cad. Ejemplo:
LENGTH(‘HOLA’) ->retorna 4

/********************************************************************************************/


Funciones para el manejo de fechas 


SYSDATE= Devuelve la fecha del sistema. 
ADD_MONTHS (fecha, n)= Devuelve la fecha "fecha" incrementada en "n" meses. 
LAST_DAY (fecha)= Devuelve la fecha del último día del mes que contiene "fecha". 
MONTHS_BETWEEN (fecha1, fecha2)= Devuelve la diferencia en meses entre las fechas "fecha1" y "fecha2". 
NEXT_DAY (fecha, “cad”= Devuelve la fecha del primer día de la semana indicado por "cad" después de la fecha indicada por "fecha“, donde cad corresponde al día de la semana EJ: ‘sábado’

Clase RECUPERATIVA 2 - Sábado 17 de Diciembre de 2011

PL/SQL


Declare
        i     := 1;
Begin

   Loop
            dbms_output.putline ('Hola' || i );
            i:= i+1
    Exit  when  (i>5000)

   EndLoop


/********************************************************************************************/

FOR

For  i  in  ['Hola' || i );

  Loop

     dbms_output.putline ('Hola' || i );


  EndLoop


FOR (REVERSE)


For i in reverse 1..5000


 Loop

     dbms_output.putline ('Hola' || i );


  EndLoop



/********************************************************************************************/

WHILE

  i := 1;

  While  ( i<5000)

  Loop
     
       dbms_output.putline ('Hola' || i );

    EndLoop


/********************************************************************************************/

CURSOR (Consulta en memoria asignado a un nombre)

Declare
  vnombre         empleado.nombre%type
  vsexo              sexo.nombre%type
  cursor             ListaGeneroEmpleado  is
  vgenero           varchar2 (10)

     Select  nombre, sexo   FROM empleado;

    Begin
         Open       ListaGeneroEmpleado ;
         Loop

          Fetch ListaGeneroEmpleado   INTO vnombre, vsexo;
          When   ListaGeneroEmpleado %NOTFOUND;
 
         IF vsexo := 'M'  Then
              vgenero := 'Hombre';
         Else  IF  vsexo = 'F' Then
               vgenero := 'mujer'
         Else
               vgenero := 'ambiguo'
         EndIF

       dbms_output.putline  (VNOMBRE ||'', VSEXO ||'',vgenero  );
       EndLoop

Close ListaGeneroEmpleado;

/********************************************************************************************/

UPDATE

Update  empleado
     Set  sexo = Upper(sexo);

Sube toda la letra en la tabla a MAYUSCULA

Clase 5 - Viernes 16 de Diciembre de 2011

Cátedra

7 preguntes de alternativa
2 de desarrollo (select
/********************************************************************************************/

INSERT

listar las propiedades que  "arrienda" un cliente (nombre, apellido,numpro)(join)

SELECT      c.nombre, c.apellido, a.numpropiedad, a.fechainicio
from             cliente c, arriendo a
where           c.numclinete = a.numcliente
AND            c.nombre = 'Juan'
AND            c.apellido = 'Perez'

INSERT con 3 tablas


SELECT c.nombre, c.apellido, a.numpropiedad, a.fechainicio
from cliente c, arriendo a, propiedad p, empleado e
where c.numclinete = a.numcliente
AND            a.numpropiedad = p.numpropiedad
AND            p.numempleado = e.numempleado


-----------------------------
ddl    creando tabla
dml   agregando registro
----------------------------

NULL    es un valor especial para un atributo que nunca tuvo valor.


/********************************************************************************************/


Mejor manera para buscar

que usar AND y IN
SELECT
numempleado, nombre, apellido FROM EMPLEADO WHERE numoficina IN(SELECT numoficina FROM OFICINA WHERE ciudad = 'London')
/********************************************************************************************/

SELECT max(salario) from empleado where sexo = 'F'

/********************************************************************************************/

Clase 4 - Sabado 10 de Diciembre de 2011

create table T1 (
e INTEGER,
F INTEGER
);
Begin
  delete from T1;
  insert into T1 values (1, 3);
  insert into T1 values (2, 4);
end

select * from t1;
/* lo de arriba es SQL; debajo es el programa PL/SQL*/
DECLARE
     a number;
      b number;
BEGIN
  select e,f into a,b /*Cursor implicito, acepta solo un registro*/
  from  T1
  where e > 1; /* Este select asigna el atributo e->a y f ->b, de todos aquellos registros cuyo atributo e->1 */
  insert into T1 values (b,a);
END;

/****************************************/

Set serveroutput on; /* habilita salida output */
/* habilitar en Toad, seleccionar pestaña DBMS Output, seleccionar Turn Output On (circulo rojo inferior izquierdo )*/
BEGIN
  dbms_output.put_line('Hola mundo');
END;

/****************************************/

DECLARE
    Precio integer :=18;
BEGIN
  if precio >=15 then
   dbms_output.put_line('El precio es : ' || precio);
  end if;
END;

/****************************************/

DECLARE
    Precio integer :=18;
    texto varchar2(5);
BEGIN
  if precio >=15 then
   texto:='Mayor';
    
  else
   texto:='Menor';
  end if;
 
  dbms_output.put_line('Es precio es ' || Texto || ' y es' || Precio);
 
END;

/****************************************/
/* Queda en un ciclo hasta que se cumpla la condicion */

DECLARE
    i integer :=0;
   
BEGIN
  LOOP
 
    i:= i + 1;
   dbms_output.put_line(i || '- Hola mundo');
   exit when i =10;
  
  END LOOP;
 
END;    

Clase RECUPERATIVA 1 - Sabado 10 de Diciembre de 2011.

-- // Querys \\ --

Select *
From empleado
Where salario between 200000 and 300000

Select nombre, ciudad
From empleado e, oficina o
Where e.numoficina = o.numoficina

Select *
From Cliente
Where lower(direccion) like '%glasgon%'

Select *
From Cliente
Where lower(nombre) like 'mari_'

Clase 3 Viernes 9 de Diciembre de 2011.

-- // Funciones \\ --

Sysdate: Entrega fecha actual.

Ejemplo: Select sysdate From Dual



==============================================================

-- // Repaso creacion de tabla \\ --

Create table PERSONA
(Id_Persona int,
Nombre varchar2 (35),
Apellido varchar2 (35),
FechaNac Date,
Renta numeric(5,1),
constraint PK_Persona primary key (Id_Persona));

==============================================================

-- // Comando para ver las tablas creadas en la BD \\ --

Select * From Tab
Select * From Cat

-- // Comando para ver la descripcion de la tabla \\ --

Desc nombre_tabla

==============================================================
-- // Comando para crear un autoincremental \\ --


Create sequence sec_persona
Start with 1
Increment by 1;

Para insertar:
insert into persona values (sec_persona.NextVal,'Pedro','Picapiedra','1/12/1960',2222)

==============================================================

select round(renta/12) as sueldo, tipo, comentario
from visita v inner join propiedad p on v.numpropiedad = p.numpropiedad;

Select nombre, round(salario/12) as "salario mensual"
from Empleado

Select nombre, salario as "salario mensual"
from Empleado
Where salario >= 10000

Select *
From oficina
Where lower(ciudad) in ('santiago','arica')

Select *
From oficina
Where lower(ciudad) not in ('santiago','arica')

Select *
from Empleado
Where lower(cargo) not in ('gerente')

Trabajo Nª1: Buenas practicas para Oracle.

Descripción de las mejores practicas para Oracle PL/SQL.

Para ver el documento haz click aca


El grupo esta conformado por:

- Luis Aranda
- Oscar Maureira
- Jaime Pineda

Clase 2. Sabado 3 de Diciembre del 2011.

Llaves primarias
Llaves foraneas.
Tabla Dual,

Creacion de tabla en SQL
create table empleado(
numEmpleado char(4),
apellido varchar2(     "espacio que ocupa es variable hasta el numero que indica"
APELLIDO CHAR(30),
CARGO CHAR(35),
SEXO CHAR(1),
FECHNAC DATE,
SALARIO FLOAT,
NUMOFICINA CHAR(4),
constraint PK_EMPLEADO primary key (NUMEMPLEADO)

integer- entero
char-caracter
varchar- caracter largo variable
number-numerico
float -numerico con punto decimal
date- fecha

select from dual (tabla virtual permite hacer operaciones, practicar sin modificar tablas ya creadas)
creacion llave primaria
create table empleado(
numEmpleado char(4) Primary key

create table ARRIENDO (
NUMARRIENDO INTEGER not null,(para definir llave primaria)
NUMPROPIEDAD CHAR(4),
NUMCLIENTE CHAR(4),
RENTA FLOAT,
FORMAPAGO CHAR(10),
DEPOSITO FLOAT,
PAGADO CHAR(1),
INICIORENTA DATE,
FINRENTA DATE,
constraint PK_ARRIENDO primary key (NUMARRIENDO) ---> esta asignando llave primaria a NUMARRIENDO
);

Proximo sabado traer los mejores practicas de trabajos de investigacion de pl/sql

como se define llave primaria
1. Se agrega solo primery key al lado del campo
2. con un constraint.Ej: constraint PK_ARRIENDO primary key (NUMARRIENDO) ---> esta asignando llave primaria a NUMARRIENDO
);
3. ej:create table T1(
Id1 integer Primary key,
Nombre varchar2(25),    
APELLIDO varchar2(30),
FECHNAC DATE
);

DDL: create table   lenguaje de definicion de datos
DML: insert, delete lenguaje de manipulacion de datos
sysdate---> fecha del sistema que utiliza oracle
agregar registros a la tabla
insert into T1 values (1,'Diego','Aranda','17/06/2011');

Clase 1, Viernes, 02 de Diciembre de 2011

Repaso de sentencias SQL

Ejemplos, SELECT:


select cargo, count(numempleado) as cantidad
from empleado
group by cargo
having count(*) >= 5

select cargo, count(numempleado) as cantidad from empleado group by cargo order by count(*) desc
select cargo, count(numempleado) as cantidad from empleado group by cargo order by cargo desc
select * from empleado where lower(sexo) = 'f'


select
cargo, count(numempleado) as cantidad from empleado group by cargo order by cargo desc
　
select
cargo, count(numempleado) as cantidad from
empleado
where
lower(sexo) = 'f' and lower(cargo) = 'asistente' group
by cargo
　
select
ciudad, count(*) as "Numero de oficinas"
from
oficina
group
by ciudad