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')