exercico em pl/sql o que utilizar cursor funcao procedure ?

akyshow · Qua, 28 Nov 2007 10:08 am

tenho o seguinte exercicio para fazer

Desenvolva um PL/SQL para calcular o valor da conta de telefone mensal dos clientes, sendo que todo cliente possui um pacote grátis, ao mês, de 400 minutos válidos para ligações locais (mesmo DDD). O minuto excedente de ligações locais e de ligações para DDD diferente é calculado com base na tabela BD_CUSTO_DDD.
Inserir os registros referentes às contas na tabela BD_CONTAS_MENSAIS.

já tenho as tabelas criadas no sql

meu estou comecando em sql e o professor já passou esta bomba
li varias apostilas mais não consegui entender

sei que tem que fazer com um cursor mais não sei como comecar

alguém me ajude por favor

obrigado

Tineks · Qua, 28 Nov 2007 10:20 am

Opa, e ai beleza??

você poderia nos passar a estrutura de cada tabela que você tem?
a tabela de clientes e bd_custo_ddd são grandes? tem como você postar o conteudo dessas tabelas?

[]'s

akyshow · Qua, 28 Nov 2007 12:40 pm

fala amigos ai estao as estruturas das tabelas

Selecionar tudo

Create table BD_CLIENTES(
COD_CLIENTE     NUMBER(10)   NOT NULL constraint PK_BD_CLI primary key,
NOM_CLIENTE     VARCHAR2(50) NOT NULL,
NUM_CPF         NUMBER(11)   NOT NULL,
NUM_RG          VARCHAR2(15) NOT NULL,
NOM_CIDADE      VARCHAR2(15) NOT NULL,
NOM_BAIRRO      VARCHAR2(12) NOT NULL,
NOM_LOGRADOURO  VARCHAR2(40) NOT NULL,
SGL_UF          VARCHAR2(2)  NOT NULL,
NUM_CEP         VARCHAR2(8)  NOT NULL);
_____________________________________________________________

Create table BD_TELEFONES(
COD_CLIENTE     NUMBER(10)  NOT NULL,
DDD_TELEFONE    NUMBER(3)   NOT NULL,
NUM_TELEFONE    NUMBER(8)   NOT NULL,
DIA_VENCIMENTO  NUMBER(2)   NOT NULL,
STA_LINHA       VARCHAR2(1) NOT NULL constraint CHK_STATUS check (sta_linha in('A', 'I', 'C')),
  constraint PK_BD_TEL primary key (ddd_telefone, num_telefone),
  constraint FK_TEL_CLI foreign key (cod_cliente) references BD_CLIENTES(cod_cliente));

_____________________________________________________________

Create table BD_LIGACOES(
DDD_TELEFONE         NUMBER(3) NOT NULL,
NUM_TELEFONE         NUMBER(8) NOT NULL,
ANO_mês              NUMBER(6) NOT NULL,
SEQ_TELEFONE         NUMBER(5) NOT NULL,
DDD_TELEFONE_DESTINO NUMBER(3) NOT NULL,
NUM_TELEFONE_DESTINO NUMBER(8) NOT NULL,
DAT_INICIO           DATE      NOT NULL,
DAT_TERMINO          DATE      NOT NULL,
 constraint PK_LIG primary key (ddd_telefone, num_telefone, ano_mês, seq_telefone),
 constraint FK_LIG_TEL foreign key( ddd_telefone, num_telefone) references BD_TELEFONES( ddd_telefone, num_telefone));

_____________________________________________________________

Create table BD_CUSTO_DDD(
	DDD_ORIGEM        NUMBER(3) NOT NULL,
      DDD_DESTINO       NUMBER(3) NOT NULL,
      VAL_MINUTO  NUMBER(4,2),
  constraint PK_BD_CUSTO primary key( ddd_origem, ddd_destino));

_____________________________________________________________

Create table BD_CONTAS_MENSAIS(
COD_CLIENTE        NUMBER(10)    NOT NULL,
ANO_mês            NUMBER(6)     NOT NULL,
DDD_TELEFONE       NUMBER(3)     NOT NULL,
NUM_TELEFONE       NUMBER(8)     NOT NULL,
TOT_MINUTOS_LOCAIS NUMBER(6)     NULL,
VAL_MINUTOS_LOCAIS NUMBER(11,2)  NULL,
TOT_MINUTOS_INTER  NUMBER(6)     NULL,
VAL_MINUTOS_INTER  NUMBER(11,2)  NULL,
DAT_VENCIMENTO     DATE          NOT NULL,
VAL_PAGO           NUMBER(11,2)  NULL,
DAT_PAGAMENTO      DATE          NULL,
VAL_ACRESCIMO      NUMBER(11,2)  NULL,
  constraint PK_CONTAS  primary key (cod_cliente, ddd_telefone, num_telefone, ano_mês),
  constraint FK_CON_TEL foreign key (ddd_telefone, num_telefone) references BD_TELEFONES( ddd_telefone, num_telefone),
  constraint FK_CON_CLI foreign key (cod_cliente) references BD_CLIENTES(cod_cliente));

akyshow · Qui, 29 Nov 2007 9:42 am

meus amigos não sei mais o que fazer neste exercicio já procurei varios foruns apostilas que fala desse cursor mais ele é muito complicado

e agora o que eu faco

me ajudem por favor

akyshow · Qui, 29 Nov 2007 10:32 am

pessoal estou tentando fazer um select para ver se funciona ai depois

vou tentar jogar o meu select dentro de um cursor

o que vocês me dizem

vamos la me ajudem

Tineks · Qui, 29 Nov 2007 10:37 am

E ai amigo, beleza??

bem, acho legal ajudar sim pois você está aprendendo, por situação parecida todos já passaram e muitos ainda passam, pois o dia-a-dia acaba exigindo coisas novas, então sempre temos que correr atrás, sempre ralando mas acho q essa é a melhor forma pra se aprender alguma coisa...

Bem, como você está fazendo o curso não acho interessante passar tudo pronto pra você aqui, assim seria fácil, apesar da dificuldade do exercicio ele é muito bom pra você aprender..

Vamos deixar aqui o caminho das pedras pra você beleza??
Bem, você perguntou como você poderia montar esse exercicio, se você utilizaria um cursor, funcao ou procedure, já que é um exercicio porque não utilizar os 3? Abaixo segue um caminho pra você, tenta dar continuidade, ai se surgir duvidas você volta a postar aqui...

Selecionar tudo

PACKAGE BODY PCK_CRISTIANO IS

FUNCTION fun_BUSCA_TARIFA (prc_DDD_ORIGEM    BD_CUSTO_DDD.DDD_ORIGEM%TYPE,
                           prc_DDD_DESTINO   BD_CUSTO_DDD.DDD_DESTINO%TYPE) RETURN NUMBER IS
   CURSOR cur_TARIFA IS
   SELECT VAL_MINUTO
     FROM BD_CUSTO_DDD
    WHERE DDD_ORIGEM  = prc_DDD_ORIGEM
      AND DDD_DESTINO = prc_DDD_DESTINO;

   v_CUSTO NUMBER(14,4);
BEGIN
   OPEN  cur_TARIFA;
   FETCH cur_TARIFA INTO v_CUSTO;
   CLOSE cur_TARIFA;
   RETURN (v_CUSTO);
END fun_BUSCA_TARIFA;

PROCEDURE prc_GERA_CONTAS (prc_ANO_mês NUMBER) IS

   CURSOR cur_CLIENTES IS
   SELECT DISTINCT C.COD_CLIENTE
     FROM BD_LIGACOES      A,
          BD_TELEFONES     B,
          BD_CLIENTES      C
     WHERE A.DDD_TELEFONE = B.DDD_TELEFONE
       AND A.NUM_TELEFONE = B.NUM_TELEFONE
       AND B.COD_CLIENTE  = C.COD_CLIENTE
       AND A.ANO_mês      = prc_ANO_mês;
       
   CURSOR cur_LIGACOES (param_COD_CLIENTE  BD_CLIENTES.COD_CLIENTE%TYPE) IS
   SELECT A.*,
          FLOOR(((DAT_TERMINO-DAT_INICIO)*24*60)) MINUTOS
     FROM BD_LIGACOES      A,
          BD_TELEFONES     B,
          BD_CLIENTES      C
     WHERE A.DDD_TELEFONE = B.DDD_TELEFONE
       AND A.NUM_TELEFONE = B.NUM_TELEFONE
       AND B.COD_CLIENTE  = C.COD_CLIENTE
       AND A.ANO_mês      = prc_ANO_mês
       AND C.COD_CLIENTE  = param_COD_CLIENTE;
   
   v_QTDE_MINUTOS_LOCAL      NUMBER (8);
   v_QTDE_COBRAR_LOCAL       NUMBER (8);
   v_VALOR_não_LOCAL         NUMBER (14,4);
BEGIN
   FOR reg_CLIENTES IN cur_CLIENTES LOOP
      /* CURSOR COM TODAS AS LIGACOES DO mês INFORMADO */   
      v_QTDE_MINUTOS_LOCAL := 0;
      v_VALOR_não_LOCAL    := 0;
      FOR reg_LIGACOES IN cur_LIGACOES(reg_CLIENTES.COD_CLIENTE) LOOP
         /* SE FORM O MESMO DDD */
         IF reg_LIGACOES.DDD_TELEFONE = reg_LIGACOES.DDD_TELEFONE_DESTINO THEN
            v_QTDE_MINUTOS_LOCAL     := reg_LIGACOES.MINUTOS;
         ELSE
            /* SE FOR OUTRO DDD COBRA TODOS OS MINUTOS */
            v_VALOR_não_LOCAL := v_VALOR_não_LOCAL + 
                              ( reg_LIGACOES.MINUTOS * fun_BUSCA_TARIFA(reg_LIGACOES.DDD_TELEFONE, reg_LIGACOES.DDD_TELEFONE_DESTINO) );
         END IF;
      END LOOP;
      /* APOS SOMAR TODOS OS MINUTOS É PRECISO VER O Q EXCEDEU */
      IF v_QTDE_MINUTOS_LOCAL > 400 THEN
         v_QTDE_COBRAR_LOCAL  := v_QTDE_MINUTOS_LOCAL-400;
      END IF;

      /* ATÉ ESSE PONTO você TEM O VALOR TOTAL DOS MINUTOS PRA OUTRO DDD E TEM TAMBEM A QUANTIDADE DE MINUTOS LOCAIS QUE ULTRAPASSOU OS 400 MIN 
         TALVES você PRECISE MUDAR ALGUMA COISA NA ROTINA ACIMA, 
         POIS TEMOS OS TOTAIS EM MINUTOS E você VAI PRECISAR FAZER A GRAVACAO DOS VALORES INDIVIDUALMENTE, POR NRO DE TELEFONE..
         MAS COMO EU DISSE, O INTERESSANTE É você TENTAR, ESSE CODIGO ACIMA PODE TE AJUDAR COM ISSO..         
         .
         .
         .
      */
      
   END LOOP;
END prc_GERA_CONTAS;

END;

[]'s

akyshow · Qui, 29 Nov 2007 12:19 pm

ola amigo

obigado por estar me ajudando com este exercicio so que tem muitas coisas que você colocou ai que eu não aprendi sei que devo correr atraz mais quanto mais eu procuro mais duvidas eu tenho

tentei rodar este codigo mais esta dando erro na linha 8

alem de mostrar para o professor vou ter que explicar para ele o que eu fiz o problema é que eu tive a materia de procedure function e cursores separados e não juntos isso que me dificulta

mais valeu pela forca vou ver o que eu posso fazer
mais acho que não vai sair muita coisa

mais obrigado novamente

Tineks · Qui, 29 Nov 2007 12:42 pm

Opa e ai beleza??

como você está fazendo pra executar ai? pode ser q esse erro seja por você tentar executar a funcao e não a procedure.
a chamada você tem q montar seria assim

Selecionar tudo

begin
   pck_cristiano.prc_GERA_CONTAS;
end;

[]'s

akyshow · Qui, 29 Nov 2007 12:52 pm

copiei seu codigo colei no sql

agora esta dando este erro

Selecionar tudo

ERROR at line 1:
ORA-00900: invalid SQL statement

você fez procedure dentro de function ?

Tineks · Qui, 29 Nov 2007 12:58 pm

Então,

esse código ai é o corpo (body) de uma package, nesse caso você tem criar a package e depois chamar a procedure dela.

Aqui vai a package completa (spec+body).
é só executar ela na sua ferramenta

Selecionar tudo

CREATE OR REPLACE 
PACKAGE pck_cristiano IS

   PROCEDURE prc_GERA_CONTAS (prc_ANO_mês NUMBER);
   
END;
/


CREATE OR REPLACE 
PACKAGE BODY pck_cristiano IS

FUNCTION fun_BUSCA_TARIFA (prc_DDD_ORIGEM    BD_CUSTO_DDD.DDD_ORIGEM%TYPE,
                           prc_DDD_DESTINO   BD_CUSTO_DDD.DDD_DESTINO%TYPE) RETURN NUMBER IS
   CURSOR cur_TARIFA IS
   SELECT VAL_MINUTO
     FROM BD_CUSTO_DDD
    WHERE DDD_ORIGEM  = prc_DDD_ORIGEM
      AND DDD_DESTINO = prc_DDD_DESTINO;

   v_CUSTO NUMBER(14,4);
BEGIN
   OPEN  cur_TARIFA;
   FETCH cur_TARIFA INTO v_CUSTO;
   CLOSE cur_TARIFA;
   RETURN (v_CUSTO);
END fun_BUSCA_TARIFA;

PROCEDURE prc_GERA_CONTAS (prc_ANO_mês NUMBER) IS

   CURSOR cur_CLIENTES IS
   SELECT DISTINCT C.COD_CLIENTE
     FROM BD_LIGACOES      A,
          BD_TELEFONES     B,
          BD_CLIENTES      C
     WHERE A.DDD_TELEFONE = B.DDD_TELEFONE
       AND A.NUM_TELEFONE = B.NUM_TELEFONE
       AND B.COD_CLIENTE  = C.COD_CLIENTE
       AND A.ANO_mês      = prc_ANO_mês;
       
   CURSOR cur_LIGACOES (param_COD_CLIENTE  BD_CLIENTES.COD_CLIENTE%TYPE) IS
   SELECT A.*,
          FLOOR(((DAT_TERMINO-DAT_INICIO)*24*60)) MINUTOS
     FROM BD_LIGACOES      A,
          BD_TELEFONES     B,
          BD_CLIENTES      C
     WHERE A.DDD_TELEFONE = B.DDD_TELEFONE
       AND A.NUM_TELEFONE = B.NUM_TELEFONE
       AND B.COD_CLIENTE  = C.COD_CLIENTE
       AND A.ANO_mês      = prc_ANO_mês
       AND C.COD_CLIENTE  = param_COD_CLIENTE;
   
   v_QTDE_MINUTOS_LOCAL      NUMBER (8);
   v_QTDE_COBRAR_LOCAL       NUMBER (8);
   v_VALOR_não_LOCAL         NUMBER (14,4);
BEGIN
   FOR reg_CLIENTES IN cur_CLIENTES LOOP
      /* CURSOR COM TODAS AS LIGACOES DO mês INFORMADO */   
      v_QTDE_MINUTOS_LOCAL := 0;
      v_VALOR_não_LOCAL    := 0;
      FOR reg_LIGACOES IN cur_LIGACOES(reg_CLIENTES.COD_CLIENTE) LOOP
         /* SE FORM O MESMO DDD */
         IF reg_LIGACOES.DDD_TELEFONE = reg_LIGACOES.DDD_TELEFONE_DESTINO THEN
            v_QTDE_MINUTOS_LOCAL     := reg_LIGACOES.MINUTOS;
         ELSE
            /* SE FOR OUTRO DDD COBRA TODOS OS MINUTOS */
            v_VALOR_não_LOCAL := v_VALOR_não_LOCAL + ( reg_LIGACOES.MINUTOS * fun_BUSCA_TARIFA(reg_LIGACOES.DDD_TELEFONE, reg_LIGACOES.DDD_TELEFONE_DESTINO) );
         END IF;
      END LOOP;
      /* APOS SOMAR TODOS OS MINUTOS É PRECISO VER O Q EXCEDEU */
      IF v_QTDE_MINUTOS_LOCAL > 400 THEN
         v_QTDE_COBRAR_LOCAL  := v_QTDE_MINUTOS_LOCAL-400;
      END IF;
      DBMS_OUTPUT.put_line(' Qde de minutos : '||v_QTDE_MINUTOS_LOCAL);
      DBMS_OUTPUT.put_line(' Valor a cobrar : '||v_QTDE_COBRAR_LOCAL);
      /* ATÉ ESSE PONTO você TEM O VALOR TOTAL DOS MINUTOS PRA OUTRO DDD E TEM TAMBEM A QUANTIDADE DE MINUTOS LOCAIS QUE ULTRAPASSOU OS 400 MIN 
         TALVES você PRECISE MUDAR ALGUMA COISA NA ROTINA ACIMA, 
         POIS TEMOS OS TOTAIS EM MINUTOS E você VAI PRECISAR FAZER A GRAVACAO DOS VALORES INDIVIDUALMENTE, POR NRO DE TELEFONE..
         MAS COMO EU DISSE, O INTERESSANTE É você TENTAR, ESSE CODIGO ACIMA PODE TE AJUDAR COM ISSO..         
         .
         .
         .
      */
      
   END LOOP;
END prc_GERA_CONTAS;

END;
/

depois você criar a package você poderá chama-la assim

Selecionar tudo

   begin
      pck_cristiano.prc_GERA_CONTAS;
   end;

[]'s
[]'s

akyshow · Qui, 29 Nov 2007 1:09 pm

meu me desculpe a minha ignorancia eu criei a package
e depois foi executar olha o erro q esta dando

Selecionar tudo

Package body created.

SQL> 
SQL> begin 
  2        pck_cristiano.prc_GERA_CONTAS; 
  3     end; 
  4  /
      pck_cristiano.prc_GERA_CONTAS;
      *
ERROR at line 2:
ORA-06550: line 2, column 7:
PLS-00306: wrong number or types of arguments in call to 'PRC_GERA_CONTAS'
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored

Tineks · Qui, 29 Nov 2007 1:17 pm

Opa,

desculpa, mas a falha foi minha.. esqueci de informar um parametro na procedure..

Selecionar tudo

   begin 
      pck_cristiano.prc_GERA_CONTAS(200711);  -- parametro ANO e mês
   end;

[]'s

akyshow · Qui, 29 Nov 2007 1:35 pm

OK criou o procedure com sucesso

mais como eu faco para executalo

seria assim

execute nome do procedure ()

obrigado

akyshow · Qui, 29 Nov 2007 1:38 pm

eu consigo modificar este seu codigo para apenas cursores e selects ?

pois assim ficaria muito mais facil para eu explicar para o prof

obrigado

Tineks · Qui, 29 Nov 2007 2:07 pm

Alem de cursores e selects você precisa de rotinas pra fazer o calculo das contas e depois gravá-las na sua tabela. pra isso você vai precisar dar insert..
da forma q eu montei eu separei algumas consultas com a finalidade de facilitar, ficando mais facil de você entender o que foi montado.. daria pra reduzir esse codigo, porem os selects vao se complicando..
bem, se eu fosse você eu não me preocuparia em entregar esse exercicio funcionando, e sim em entender o q você fez nele, mesmo q ele não esteja montado por completo...
agora imagina se seu professor pede pra você mudar algo, fazer algum agrupamento, etc... você iria sofrer muito pra tentar mudar algo...

[]'s

akyshow · Qui, 29 Nov 2007 2:14 pm

muito obrigado

pela forca cristiano

acho q você fez ate demais
agora é comigo
vou ver aqui o que eu posso fazer

valeuuu

Tineks · Qui, 29 Nov 2007 2:19 pm

Beleza!!

boa sorte ai!!
queima uns neuronios ai, essa é a melhor forma pra aprender...
mas se enroscar em alguma coisa posta aqui no fórum!!!

[]'s

akyshow · Sex, 30 Nov 2007 1:26 pm

fui dando uma pesquisada e analisando o codigo q você me mandou ai eu decidi fazer com somente select e cursor
fiz apenas um select para testar olha so como ficou

Selecionar tudo

 Select
	c.cod_cliente Codigo,
	substr(c.nom_cliente,1,20) Nome, 
	t.ddd_telefone DDD,
	t.num_telefone Telefone,
	l.ddd_telefone_destino Destino, 
	t.sta_linha linha, 
	round(round(sum(((l.dat_termino-l.dat_inicio)*24*60)),2)
	*d.val_minuto,2) val_ligacoes

 From   
	bd_clientes c, 
        bd_telefones t,
        bd_ligacoes l,
        bd_custo_ddd d

Where 
	c.cod_cliente = t.cod_cliente
and     t.ddd_telefone = l.ddd_telefone
and     t.num_telefone = l.num_telefone
and     t.ddd_telefone     = d.ddd_origem
and     l.ddd_telefone_destino = d.ddd_destino
and     sta_linha ='A'	

group by
	c.cod_cliente,
	substr(c.nom_cliente,1,20),
	t.ddd_telefone,
	t.num_telefone,
	t.sta_linha,
	d.val_minuto,
	l.ddd_telefone_destino

agora preciso jogar dentro de um cursor e fazer as devidas contas correto ou estou errado?

akyshow · Sex, 30 Nov 2007 1:34 pm

a já ia me esquecendo

eu utilizei os metodos como eu estou aprendendo

agora o que fazer daqui p frente variaveis e as devidas contas ?

obrigado

Tineks · Sex, 30 Nov 2007 1:49 pm

Opa, e ai amigo beleza??

então, com essa consulta você consegue obter as informações que você precisa, porem você precisa montar uma forma de ler esses resultados e aplicar a regra de calculo. Pra montar essa regra o ideal seria utilizar uma procedure, pois nela você teria seus selects, cursor, variavel e a regra..

bem, eu tive a impressão que você está se confundindo um pouco em relação ao uso de cursores, da uma olhada nesse exemplo abaixo.
Exemplo 1 - Select usando cursor

Selecionar tudo

SQL> DECLARE
  2     CURSOR cur_DATA IS
  3     SELECT SYSDATE
  4       FROM DUAL;
  5       
  6     v_DATA_ATUAL DATE;
  7  BEGIN
  8     OPEN  cur_DATA;
  9     FETCH cur_DATA INTO v_DATA_ATUAL;
 10     CLOSE cur_DATA;
 11     dbms_output.put_line('Data atual : '||v_DATA_ATUAL);
 12  END;
 13  /
Data atual : 30-NOV-07

Select sem a utilzacao de cursor

Selecionar tudo

SQL> DECLARE
  2     v_DATA_ATUAL DATE;
  3  BEGIN
  4     SELECT SYSDATE
  5       INTO v_DATA_ATUAL
  6       FROM DUAL;
  7  
  8     dbms_output.put_line('Data atual : '||v_DATA_ATUAL);
  9  END;
 10  /
Data atual : 30-NOV-07

acima temos 2 exemplos, nos 2 casos utilizamos um select pra obter a data atual, porem no primeiro caso isso foi feito através do cursor....
obs: você não pode colocar no corpo do cursor nada que seja diferente de um select.
Portanto, você tem 2 formas de montar suas consultas, podendo ser com a utilizacao de cursores ou simplemente executando diretamente seu SELECT.

então você não vai conseguir montar sua regra de calculo pensando somente em cursor ou select.. talves a forma mais facil seja você montando uma procedure, e dentro dessa procedure criar seus cursores, ou selects e suas regras..

[]'s

akyshow · Sex, 30 Nov 2007 2:28 pm

ola cristiano beleza

como eu chamo este primeiro exemplo com cursor, para eu imprimir a data na tela

sendo que ela me aparece

procedure successfully completed

mais e a data onde esta ?

obrigado

Tineks · Sex, 30 Nov 2007 2:31 pm

Se você estiver utilizando o SQLPlus você precisa digitar o comando abaixo.

Selecionar tudo

SQL> set serveroutput on;

[]'s

akyshow · Sex, 30 Nov 2007 3:16 pm

obrigado agora rodou

desculpe mais uma vez a minha ignorancia

mais você sabe como é vivendo e aprendendo

vou tentar fazer do jeito q você falou

Tineks · Sex, 30 Nov 2007 3:18 pm

Q isso, não é ignorancia não, você está aprendendo e isso faz parte.!
Boa sorte ai!

[]'s

exercico em pl/sql o que utilizar cursor funcao procedure ?

Quem está online