Entradas

Mostrando entradas de mayo 2, 2014

TRIGGER ORACLE

--tables
CREATE TABLE CLIENTE
(
CODCLIENTE NUMBER PRIMARY KEY,
RAZONSOCIAL VARCHAR2(50),
DIRECCION VARCHAR2(50),
RUC CHAR(11)
);

INSERT INTO CLIENTE VALUES(1,'Empresa1','Jr. Amazonas #2312','12045236014');
INSERT INTO CLIENTE VALUES(2,'Empresa2','Jr. Amazonas #2313','12045236011');
INSERT INTO CLIENTE VALUES(3,'Empresa3','Jr. Amazonas #2315','12045236023');
INSERT INTO CLIENTE VALUES(4,'Empresa4','Jr. Amazonas #2315','12045236023');
INSERT INTO CLIENTE VALUES(5,'Empresa5','Jr. Amazonas #2316','22045236021');
select * from cliente;


CREATE TABLE BITCLIENTE
(
CODCLIENTE NUMBER,
RUC CHAR(11),
FECHA DATE,
HORA CHAR(10)
);

--trigger
CREATE OR REPLACE TRIGGER TR_01
AFTER INSERT ON CLIENTE
FOR EACH ROW
BEGIN
     INSERT INTO BITCLIENTE(CODCLIENTE,RUC,FECHA,HORA)
     VALUES(:NEW.codcliente, :NEW.ruc, TO_DATE(SYSDATE,'DD/MM/YY'), TO_CHAR(SYSDATE,'HH24:MI'));
E…

Funciones de Cadena ORACLE

--funciones de cadena
select upper(ape || ' ' || nom)as nombres from tbemple;
select concat(ape || ' ',nom)as nombres from tbemple;
SELECT SUBSTR('PEPE PEREZ', 6) FROM DUAL;
SELECT SUBSTR('PEPE PEREZ',6,2) FROM DUAL;
select initcap('buenas tardes alumno') from dual;
select upper(nom) from tbemple;
select lower(nom) from tbemple;
select initcap(ape) from tbemple;
select substr(nom,1,4) from tbemple
select concat('Jhonatan',' Abal') from Dual;
select chr(65) from dual;
select lpad('alumno',8,'wa') from dual
select lpad('alumno',4,'xyz') from dual;
select trim(' hola a todos ')from dual;
select rpad('alumno',11,'xyz') from dual;
select rtrim('la casa lila','la') from dual;-- 'la casa li'
select rtrim('la casa lila ','la') from dual;-- no borra ningún caracter
select rtrim('la casa lila    ') from dual; --'la casa lila'
sel…

GROUP BY ORACLE

EJEMPLO GROUP BY ******************* select origen, sum(subtotal), sum(descuento), sum(total) from autorizacion group by origen;

select paciente, origen, sum(subtotal), sum(descuento), sum(total) from autorizacion where tipo_paciente IN('C','N') group by origen, paciente;
select origen, min(total) as minimo_total, max(total) as maximo_total from autorizacion group by origen having origen = 'CE';
select origen, sum(subtotal), sum(descuento), sum(total) from autorizacion group by origen, consultorio having origen='CE' OR origen='EM' order by origen desc;
select c.nombre, sum(a.subtotal), sum(a.descuento), sum(a.total) from autorizacion a, consultorio c where  (c.consultorio = a.consultorio) and a.fecha_registro >= to_date('01/03/2013','DD/MM/YYYY') and a.fecha_registro <= to_date('31/03/2013','DD/MM/YYYY') AND a.USUARIO='XXX' group by c.nombre;

--by Ing. Jhonatan Abal Mejia