Menu de navegação

[romario30Participante]

Bom dia pessoal,

Sou novato em Oracle e gostaria da ajuda de vocês para tentar criar uma função. Tenho uma tabela uma tabela cliente e uma tabela histórico_cliente, na ficam armazenado todo a movimentação de cliente. Tipo toda vez que um cliente é ativado ou desativado, fica armazenado a data que ocorreu o evento bem como o STATUS para qual ele mudou.
Conforme demonstrado abaixo.

TABELA CLIENTE

CODIGO – NOME
1 – Maria
2 – João

TABELA HISTORICO_CLIENTE

COD_CLIENTE – DATA – STATUS
1 – 01/05/2011 – I
1 – 15/05/2011 – A
1 – 30/05/2011 – I
1 – 30/07/2011 – A
2 – 02/06/2011 – I

Tendo esses dados. Preciso criar uma função que retorne o STATUS de um cliente em determinada data, ou seja, para saber se uma data que eu passar como parâmetro, o cliente estava ativo ou não.

Gostaria de uma ajuda de qual a logica devo seguir para obter esse resultado. Preciso usar cursor, array??

Desde já agradeço a colaboração.

[leandrolbsParticipante]

Cara a função seria isto:


create or replace function FU_RETORNA_STS_CLI(vCliente,vData) return char(1) is
Result char(1);
vStatus Char(1);
begin
select hc.status into vStatus from historico_cliente hc
where hc.cliente = vCliente
and hc.data = vData;
Result := vSatus;
return(Result);
end FU_RETORNA_STS_CLI;


Mas pode existir duas alterações na mesma data…etc.. isso trará problemas na função… mas já da pra começar com ela..

[rmanParticipante]

[quote=”leandrolbs”:2vppksx9]Cara a função seria isto:


create or replace function FU_RETORNA_STS_CLI(vCliente,vData) return char(1) is
Result char(1);
vStatus Char(1);
begin
select hc.status into vStatus from historico_cliente hc
where hc.cliente = vCliente
and hc.data = vData;
Result := vSatus;
return(Result);
end FU_RETORNA_STS_CLI;


Mas pode existir duas alterações na mesma data…etc.. isso trará problemas na função… mas já da pra começar com ela..[/quote]

Não é tao simples quanto parece…

SELECT FU_RETORNA_STS_CLI(1,’2011-05-10′) FROM DUAL;

Deve retornar ‘I’

Da forma que foi implementado retorna NULL

romario30

Começei a implementar aqui na hora do almoço, mas só vou poder mexer nisso a noite…

[romario30Participante]

RMAN,

realmente, verifiquei que não é tão fácil, existem vários fatores a considerar. ficarei aguardando.

Agradeço a sua ajuda.
obrigado.

[leandrolbsParticipante]

pessoal, função pronta, na mão é de mais…rssss
digitei a função na mão, não determinei nem os tipos das variaveis..etccc

[leandrolbsParticipante]

Para os preguiça de plantão, segue todo o script FUNCIONANDO!.

Só acho valido, que isso não traz aprendizado algum!…


create table historico_cliente(
COD_CLIENTE integer,
Data date not null,
STATUS char(1) not null);
--==
insert into historico_cliente(cod_cliente,data,status) values (1,'01/05/2011','I');
insert into historico_cliente(cod_cliente,data,status) values (1,'15/05/2011','A');
insert into historico_cliente(cod_cliente,data,status) values (1,'30/05/2011','I');
insert into historico_cliente(cod_cliente,data,status) values (1,'30/07/2011','A');
insert into historico_cliente(cod_cliente,data,status) values (1,'02/06/2011','I');
commit;
--==
create or replace function jr.FU_RETORNA_STATUS_DT (P_Cliente IN INTEGER, P_Data IN DATE) return char is
vStatus char(1);
begin
select hc.status into vStatus from historico_cliente hc
where hc.cod_cliente = P_Cliente
and hc.data = P_Data;
return(vStatus);
end FU_RETORNA_STATUS_DT;
/
--===
select jr.FU_RETORNA_STATUS_DT(1,'30/07/2011') from dual;


Caso, tbm é legal implementar EXCEPTION WHEN, to_date(XX,’dd/mm/yyyy’)…. COISAS BASICAS..etcccc

[rmanParticipante]

leandrolbs

Não entendi por que você ficou bravo…

Eu apenas alertei sobre um caso que a função não iria se comportar da forma esperada…

Me desculpe…

Estamos aqui para nós ajudar…

[leandrolbsParticipante]

rman, acho que entendi errado…. mas soluções prontas não são viáveis para aparender….
coloquei a função no ponto de partida, a onde ele poderia correr atras. Ai vem vc falando que não é simples… que dps vc irá fazer…etccc
acho que um TO_DATE é o mínimo… mas blz.. bola pra frente… o importante é ajudar… e espero ter lhe ajudado romario30

[romario30Participante]

Pessoal

Agradeço o a colaboração de todos. Desculpe por ter causado esse transtorno nesse tópico. O meu objetivo era apenas ter uma noção de como criar esta função, quais as estrutura de dados que poderia usar. Não verdade não entendo quase nada de banco de dados. Trabalho como java.

De qualquer forma, peço desculpas e agradeço o ajuda mais uma vez.

Obrigado a todos.

[IshiiParticipante]

Olá,

Ok, a intenção dos fóruns é justamente “mostrar o caminho das pedras” mas a trilha, é sempre você quem faz…. aprendendo, isso é o que realmente importa.

Leandro, não se preocupe, muitas vezes eu também deixo o tolerance mode = OFF e entendo que tem pessoas que nunca querem apenas o “caminho” e sim que o façamos por eles….mas, felizmente acho que isso é uma coisa que nesse fórum ocorre com menos frequência que em outros…

[]s Ishii

[rmanParticipante]

leandrolbs,

O romario30 em momento algum pediu a função pronta na mão, eu apenas achei interessante o problema, e me propus em resolver…

[rmanParticipante]

Segue a minha implementação

Qualquer dúvida, é só postar…


CREATE TABLE HISTORICO_CLIENTE (
COD_HISTORICO_CLIENTE NUMBER NOT NULL,
COD_CLIENTE NUMBER NOT NULL,
DATA DATE NOT NULL,
STATUS CHAR(1) NOT NULL
);

ALTER TABLE HISTORICO_CLIENTE ADD CONSTRAINTS PK_HISTORICO_CLIENTE PRIMARY KEY(COD_HISTORICO_CLIENTE);

INSERT INTO HISTORICO_CLIENTE(COD_HISTORICO_CLIENTE,COD_CLIENTE,DATA,STATUS) VALUES(1,1,'01/05/2011','I');
INSERT INTO HISTORICO_CLIENTE(COD_HISTORICO_CLIENTE,COD_CLIENTE,DATA,STATUS) VALUES(2,1,'15/05/2011','A');
INSERT INTO HISTORICO_CLIENTE(COD_HISTORICO_CLIENTE,COD_CLIENTE,DATA,STATUS) VALUES(3,1,'30/05/2011','I');
INSERT INTO HISTORICO_CLIENTE(COD_HISTORICO_CLIENTE,COD_CLIENTE,DATA,STATUS) VALUES(4,1,'30/07/2011','I');
INSERT INTO HISTORICO_CLIENTE(COD_HISTORICO_CLIENTE,COD_CLIENTE,DATA,STATUS) VALUES(5,2,'02/06/2011','I');
INSERT INTO HISTORICO_CLIENTE(COD_HISTORICO_CLIENTE,COD_CLIENTE,DATA,STATUS) VALUES(6,3,'01/04/2011','I');
INSERT INTO HISTORICO_CLIENTE(COD_HISTORICO_CLIENTE,COD_CLIENTE,DATA,STATUS) VALUES(7,3,'01/04/2011','A');
INSERT INTO HISTORICO_CLIENTE(COD_HISTORICO_CLIENTE,COD_CLIENTE,DATA,STATUS) VALUES(8,3,'01/04/2011','I');

COMMIT;

CREATE OR REPLACE FUNCTION GET_STATUS_CLIENTE(ACOD_CLIENTE NUMBER,ADATA DATE) RETURN CHAR IS
DATA_LIMITE_SUPERIOR DATE := NULL;
DATA_LIMITE_INFERIOR DATE := NULL;
STATUS_SUPERIOR CHAR(1);
STATUS_INFERIOR CHAR(1);
L_COD_CLIENTE NUMBER;
BEGIN
SELECT MAX(COD_CLIENTE) INTO L_COD_CLIENTE
FROM HISTORICO_CLIENTE
WHERE COD_CLIENTE = ACOD_CLIENTE;

SELECT MIN(DATA) INTO DATA_LIMITE_SUPERIOR
FROM HISTORICO_CLIENTE
WHERE COD_CLIENTE = ACOD_CLIENTE
AND DATA >= ADATA;

IF DATA_LIMITE_SUPERIOR IS NOT NULL THEN
DBMS_OUTPUT.put_line('SUP: '||DATA_LIMITE_SUPERIOR||';');

SELECT STATUS INTO STATUS_SUPERIOR
FROM HISTORICO_CLIENTE
WHERE COD_HISTORICO_CLIENTE = (
      SELECT MAX(COD_HISTORICO_CLIENTE)
      FROM HISTORICO_CLIENTE
      WHERE COD_CLIENTE = ACOD_CLIENTE
      AND DATA = DATA_LIMITE_SUPERIOR       
);    

END IF;

SELECT MAX(DATA) INTO DATA_LIMITE_INFERIOR
FROM HISTORICO_CLIENTE
WHERE COD_CLIENTE = ACOD_CLIENTE
AND DATA <= ADATA;

IF DATA_LIMITE_INFERIOR IS NOT NULL THEN
DBMS_OUTPUT.put_line('INF: '||DATA_LIMITE_INFERIOR||';');

SELECT STATUS INTO STATUS_INFERIOR
FROM HISTORICO_CLIENTE
WHERE COD_HISTORICO_CLIENTE = (
      SELECT MAX(COD_HISTORICO_CLIENTE)
      FROM HISTORICO_CLIENTE
      WHERE COD_CLIENTE = ACOD_CLIENTE
      AND DATA = DATA_LIMITE_INFERIOR
);    

END IF;

IF DATA_LIMITE_INFERIOR IS NOT NULL AND DATA_LIMITE_SUPERIOR IS NOT NULL THEN
RETURN STATUS_INFERIOR;
ELSIF DATA_LIMITE_INFERIOR IS NULL AND DATA_LIMITE_SUPERIOR IS NULL THEN
RETURN 'A';
ELSIF DATA_LIMITE_INFERIOR IS NULL AND DATA_LIMITE_SUPERIOR IS NOT NULL THEN
RETURN 'A';
ELSIF DATA_LIMITE_INFERIOR IS NOT NULL AND DATA_LIMITE_SUPERIOR IS NULL THEN
RETURN STATUS_INFERIOR;
END IF;
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.put_line(SQLERRM);
RETURN NULL;
END;
END GET_STATUS_CLIENTE;

SELECT GET_STATUS_CLIENTE(1,'10/05/2011') STATUS FROM DUAL;
SELECT GET_STATUS_CLIENTE(1,'10/08/2011') STATUS FROM DUAL;
SELECT GET_STATUS_CLIENTE(1,'30/05/2011') STATUS FROM DUAL;


[romario30Participante]

Bom dia RMAN,

Desculpe-me pela demora em responder.

Gostaria de agradecer pelo seu apoio. Já verifiquei seu código e mesmo funcionou perfeitamente. Quero lhe parabenizar também pelo seu blog. Encontrei muita coisa interessante, principalmente para quem está aprendendo igual a eu.

Obrigado.

[rmanParticipante]

[quote=”romario30″:171htdh0]Bom dia RMAN,

Desculpe-me pela demora em responder.

Gostaria de agradecer pelo seu apoio. Já verifiquei seu código e mesmo funcionou perfeitamente. Quero lhe parabenizar também pelo seu blog. Encontrei muita coisa interessante, principalmente para quem está aprendendo igual a eu.

Obrigado.[/quote]

Obrigado, o blog ainda é novo, no momento publiquei apenas a parte de criar o ambiente, como instalação e configuração, mas pra frente, pretendo publicar algo mais avançado, coisas do dia a dia do DBA mesmo…

Qualquer dúvido é só comentar no blog…

[burgaParticipante]

@rman
Bacana mesmo o blog! Vou acompanhá-lo nos próximos posts… 🙂
Fora que vi que você é de Maringá, gosto mto da cidade!!! É formado na UEM?

@topic
Bom Romario, eu sei que já resolveram seu problema aí, mas só pra constar mais uma forma de resolver seu problema e, seguindo a idéia do Leandro de não dar uma resposta direta, mas fornecer um caminho, você poderia fazer isso usando somente SQL, através das funções analíticas LEAD ou LAG. Não é difícil… 😉

Eu particularmente prefiro sempre resolver as coisas usando somente SQL, e caso não seja possível aí sim partir pro PL. No geral o desempenho é melhor.

[Autor]

Posts