Atualizar Saldo de Conta em um determinado tempo

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
Dj_William
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Ter, 01 Out 2013 10:41 am

Olá amigos, preciso de ajuda, não entendo nada de PL/SQL e tenho a seguinte situação: Preciso criar um controle para calcular e atualizar o saldo de uma conta bancária a partir da movimentação realizada em um certo intervalo de tempo.
Tenho as tabelas abaixo:

Selecionar tudo

CREATE TABLE CONTA_CORRENTE (
  NUM_CONTA_CORRENTE NUMBER(8) NOT NULL,
  NUM_BANCO CHAR(5) NOT NULL,
  NUM_AGENCIA NUMBER(4) NOT NULL,
  SALDO_CC NUMBER (12,2),
  PRIMARY KEY (NUM_CONTA_CORRENTE,NUM_BANCO,NUM_AGENCIA),
  FOREIGN KEY (NUM_CONTA_CORRENTE,NUM_BANCO,NUM_AGENCIA) REFERENCES CONTA_BANCARIA(NUM_CONTA,NUM_BANCO,NUM_AGENCIA) ON DELETE CASCADE
);

CREATE TABLE CONTA_POUPANCA (
  NUM_CONTA_POUP NUMBER(8) NOT NULL,
  NUM_BANCO CHAR(5) NOT NULL,
  NUM_AGENCIA NUMBER(4) NOT NULL,
  SALDO_POUP NUMBER (12,2),
  PRIMARY KEY (NUM_CONTA_POUP,NUM_BANCO,NUM_AGENCIA),
  FOREIGN KEY (NUM_CONTA_POUP,NUM_BANCO,NUM_AGENCIA) REFERENCES CONTA_BANCARIA(NUM_CONTA,NUM_BANCO,NUM_AGENCIA) ON DELETE CASCADE
);

 CREATE TABLE MOVIMENTACAO (
  NUM_LANCAMENTO INTEGER PRIMARY KEY,
  NUM_CONTA NUMBER(8) NOT NULL,
  NUM_BANCO CHAR(5) NOT NULL,
  NUM_AGENCIA NUMBER(4) NOT NULL,
  TIPO INTEGER NOT NULL,
  DATA_HORA DATE NOT NULL,
  DEBITO_CREDITO CHAR(7) NOT NULL,
  VALOR NUMBER (12,2)NOT NULL,
  FOREIGN KEY (NUM_CONTA,NUM_BANCO,NUM_AGENCIA) REFERENCES CONTA_BANCARIA (NUM_CONTA,NUM_BANCO,NUM_AGENCIA) ON DELETE CASCADE  
);
ALTER TABLE MOVIMENTACAO 
  MODIFY DATA_HORA DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE MOVIMENTACAO 
  ADD FOREIGN KEY(TIPO)
  REFERENCES  TIPO_MOVIMENTACAO(COD_MOVI);
INSERT INTO MOVIMENTACAO VALUES (NUM_LANCAMENTO_MOV.NEXTVAL,12345678,'246',1111,1,'19/03/2013','CREDITO',4000);
INSERT INTO MOVIMENTACAO VALUES (NUM_LANCAMENTO_MOV.NEXTVAL,14124354,'246',7777,1,'22/03/2013','CREDITO',300.99);
INSERT INTO MOVIMENTACAO VALUES (NUM_LANCAMENTO_MOV.NEXTVAL,15874621,'230',5555,5,'17/03/2013','CREDITO',300.99);

CREATE TABLE TIPO_MOVIMENTACAO (
  COD_MOVI INTEGER PRIMARY KEY,
  DESCRICAO CHAR(20) NOT NULL
  );
INSERT INTO TIPO_MOVIMENTACAO VALUES (1,'DEPOSITO');
INSERT INTO TIPO_MOVIMENTACAO VALUES (2,'SAQUE');
INSERT INTO TIPO_MOVIMENTACAO VALUES (3,'TRANSFERENCIA');
INSERT INTO TIPO_MOVIMENTACAO VALUES (4,'INVERTIMENTO');
INSERT INTO TIPO_MOVIMENTACAO VALUES (5,'PAGAMENTO');


é trabalho de faculdade...
Tinho
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 317
Registrado em: Seg, 16 Nov 2009 4:50 pm
Localização: São Paulo - SP

Olá amigo tudo bem?

Se bem entendi o seu problema. Você tem alguns recursos disponíveis ao seu favor. Mas por se tratar de um trabalho de faculdade a intenção é o aprendizado. Por isso, para você entender melhor a solução a ser aplicada, você deve entender primeiramente a modelagem de dados o DER (Diagrama Entidade Relacionamento). Isto permite você tangenciar de uma forma visível o relacionamento e as cardinalidades de suas tabelas, principalmente se você tiver uma ferramenta de case de modelagem que lhe permita fazer engenharia reversa, como por exemplo o CA-Erwin, DBDesigner e um muito utilizado para estudos o BRModelo, dentre outros.

A tabela "TIPO_MOVIMENTACAO" é uma tabela do tipo "lookup", ou uma tabela de consulta padronizada para os tipos de movimentação permitidos para efetuar a movimentação em si.

Já a tabela de "MOVIMENTACAO" é a ação a ser executada, onde serão registrados as movimentações do proprietário da conta. Onde este proprietário terá os dados das suas contas cadastrados nas tabelas "CONTA_POUPANCA" e "CONTA_CORRENTE" respectivamente.

A tabela "CONTA_BANCARIA" por sua vez é uma tabela associativa, isto quer dizer que ela tem a função de ligar, conectar, relacionar os registros da conta poupança e corrente e associá-las a movimentação correspondente permitindo que uma conta tenha nenhuma ou muitas movimentações.

Por isso, para você aplicar uma solução, sugiro que você alimente as tabelas com dados fictícios e faça uma query primeiramente para consultar e observar como os dados são apresentados. Exemplo:

Selecionar tudo


SELECT M.* -- DEFINA AQUI OS DADOS RELEVANTES QUE VOCÊ DESEJA VISUALIZAR
  FROM CONTA_BANCARIA CB
 INNER JOIN CONTA_POUPANCA CP
    ON CB.NUM_BANCO   = CP.NUM_BANCO
   AND CB.NUM_AGENCIA = CP.NUM_AGENCIA
 INNER JOIN CONTA_CORRENTE CC
    ON CB.NUM_BANCO   = CC.NUM_BANCO
   AND CB.NUM_AGENCIA = CC.NUM_AGENCIA
  LEFT JOIN MOVIMENTACAO M
    ON CB.NUM_CONTA   = CC.NUM_CONTA
   AND CB.NUM_BANCO   = CC.NUM_BANCO
   AND CB.NUM_AGENCIA = CC.NUM_AGENCIA
 INNER JOIN TIPO_MOVIMENTACAO TM
    ON M.COD_MOVI = TM.COD_MOVI;
   /*
   -- NESTE CASO EU ESTOU RETORNANDO APENAS OS REGISTROS DE DEPÓSITO 
   -- QUE OCORRERAM ENTRE OS DIAS 17 E 19 DO MÊS DE MARÇO
   WHERE TM.COD_MOVI = 1
     AND TO_CHAR(M.DATA_HORA,'DD/MMM/YYYY') BETWEEN '17/03/2013' 
	                                            AND '19/03/2013'
   */

Assim você conseguirá visualizar todas as contas que tiveram ou não movimentação uma vez que a cardinalidade da tabela "MOVIMENTACAO" é (0:N), ou seja, não existe a obrigatoriedade de haver um registro de movimentação para determinada conta, apesar de ser muito improvável. Caso você queira ver apenas as contas que tiveram movimentação troque o "LEFT" por "INNER JOIN", dessa forma só serão retornados os dados que possuirem registros correspondentes em comum entre as tabelas "CONTA_BANCARIA" X "MOVIMENTACAO".

Obviamente conforme for crescendo a quantidade de registros, você deve determinar filtros para restringir o resultado, exemplo, filtrar o resultado por tipo de registro, período, determinada conta específica (se for o caso), além de avaliar o uso de índices ou não (exemplo comentado).

Agora, como você tem que fazer uma atualização do saldo, esta atualização ocorrerá de acordo com o tipo de movimentação, conforme segue:

DEPOSITO -> somatório (adiciona um valor ao saldo). Você pode especificar ou não para qual conta que vai (corrente ou poupança);
INVESTIMENTO -> contabiliza o juros de determinada aplicação no período (para efeito de teste pode usar o rendimento médio da poupança de aproximadamente 0.49/a.m). Isto também implicará em um acréscimo no saldo, isto também sem considerar que a aplicação não tenha nenhuma taxa, encargos, emolumentos, que é o caso da poupança para aplicações até R$50.000,00. Geralmente é utilizada uma conta específica para investimentos, mas se resolver considerar a poupança como investimento, o saldo a ser alterado será da tabela "CONTA_POUPANCA";

As demais, são contas de subtração, ou seja, você terá que tirar, descontar do saldo final. Que são os casos do: SAQUE, TRANSFERENCIA, PAGAMENTO;

Estas transações tem regras para acontecer, mas não deve estar dentro do contexto deste trabalho. Assim sendo, você terá que simular uma transação.

Até aqui foi relativamente simples. Para este caso, como solução você pode usar uma "TRIGGER" (que eu acredito que seja o que o professor quer que vocês utilizem). Esta "TRIGGER" você deve colocar na tabela "MOVIMENTACAO" para que ela monitore as ocorrências de registro e dispare a ação correspondente com a operação realizada de acordo com os critérios citados (intervalo de tempo) alterando o valor do saldo, exemplo:

"UPDATE":

CONTA_CORRENTE -> SALDO_CC;
CONTA_POUPANCA -> SALDO_POUP;

Partindo do pressuposto que o professor já tenha passado para vocês o conceito do uso de triggers. Para não ficar muito "fácil" e dar um pouquinho de "graça", agora sugiro que você dê uma estudada nos seguintes links, caso tenha alguma dúvida, poste aqui novamente.

Documentação oficial da Oracle:
http://docs.oracle.com/cd/B19306_01/ser ... s_7004.htm

Techonthenet:
http://www.techonthenet.com/oracle/triggers/

Psoug
http://psoug.org/definition/TRIGGER.htm

Obs.: Eu não testei os códigos do exemplo, por isso, por favor, considere o fato de poder haver algum equivoco de sintaxe;

Espero ter ajudado.

Abç.,
Dj_William
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Ter, 01 Out 2013 10:41 am

Rapaz... uma verdadeira aula, obrigado Tinho, estou verificando os links e agradeço muito o tempo e atenção dispensada, na verdade eu estou retornando ao curso que tinha parado após 10 anos e a materia em Banco de Dados é uma das que não tenho nenhum conhecimento, estou aprendendo meio que na raça pois a galera da sala tem muita diferença de idade em relação a minha (tenho 41) e é difícil se enturmar e então estou tentando aprender por meios alternativos..
Tinho
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 317
Registrado em: Seg, 16 Nov 2009 4:50 pm
Localização: São Paulo - SP

Olá William, você já e profissional da área de TI? Tem conhecimentos técnicos? Ou equivalentes? Idade não é um problema e conhecimento é libertador e a solução. Banco de Dados na verdade é a base estrutural de um sistema sólido, confiável e eficiente, etc.

Eu sinceramente, se pudesse inverter a ordem cronologica de como somos incinados a programar. Acho que antes de sairmos programando deveríamos aprender bem o conceito de banco de dados.

Qualquer dúvida poste ai.

Abç.,
Dj_William
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Ter, 01 Out 2013 10:41 am

Infelizmente não trabalho na área, por isso a dificuldade maior... mas preciso terminar o curso para não perder todo o investimento feito, faltam poucas materias... acho que se tivesse trabalhando na área poderia ser menos complicado... mas beleza..

tenho outra dúvida sim, na tabela movimentação foi me passados os valores abaixo para inserir mas dá um erro de número invalido, sabe dizer se esses parâmetros estão corretos?

Selecionar tudo

INSERT INTO MOVIMENTACAO VALUES (NUM_LANCAMENTO_MOV.NEXTVAL,12345678,'246',1111,1,'19/03/2013','CREDITO',4000);
INSERT INTO MOVIMENTACAO VALUES (NUM_LANCAMENTO_MOV.NEXTVAL,14124354,'246',7777,1,'22/03/2013','CREDITO',300.99);
INSERT INTO MOVIMENTACAO VALUES (NUM_LANCAMENTO_MOV.NEXTVAL,15874621,'230',5555,5,'17/03/2013','CREDITO',300.99);
Tinho
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 317
Registrado em: Seg, 16 Nov 2009 4:50 pm
Localização: São Paulo - SP

Olá William.

Eu verifiquei os dados do seu INSERT e aparentemente eles são compatíveis com os datatypes da tabela. Por favor, seja mais específico em relação ao erro para que possamos melhor analisar. Tente pegar o código e a mensagem.

Att.,
Dj_William
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Ter, 01 Out 2013 10:41 am

valeu Tinho, tem mais exercicios e no decorrer tive que alterar a tabela inserindo novos campos e o erro desapareceu, valeu! :D
Tinho
Rank: DBA Sênior
Rank: DBA Sênior
Mensagens: 317
Registrado em: Seg, 16 Nov 2009 4:50 pm
Localização: São Paulo - SP

Neste caso, por se tratar de um exercício. Muito provavelmente o professor deve estar ensinando uma série de scripts sequenciais para demonstrar a utilização e o efeito de cada um. Por isso, pode ser que quando você estava fazendo o INSERT a estrutura da tabela poderia estar diferente, ou seja, numero de colunas diferentes em relação aos argumentos passados no comando ou mesmo datatype diferentes.

Qualquer dúvida posta ai.

Abç.,
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 17 visitantes