I have the following exercise to do
Develop an PL / SQL to calculate the value of the monthly clients phone account, and every customer has a free package, a month, 400 minutes valid for Local links (even DDD). The surplus minute of local connections and connections for different DDD is calculated based on the BD_CUST_DDD table.
Insert the records relating to the accounts in the BD_CONTO_MENSAL table.
I already have the tables created in SQL
mine I'm starting in sql and the teacher has passed this pump
I read several handouts but I could not understand
I know you have to do with a cursor but I do not know how to start
Someone help me please
Thanks
exercico in pl / sql to use the cursor procedure function?
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
Oops, and ai beleza ??
Could you pass us the structure of each table that you have?
The client table and bd_custo_ddd are large? How can you post the contents of these tables?
[] 's
Could you pass us the structure of each table that you have?
The client table and bd_custo_ddd are large? How can you post the contents of these tables?
[] 's
Speak friends there are the structures of the tables
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));
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
And my friend, beleza ??
Well, I think it's cool to help yes because you're learning, for similar situation everyone has passed and many still pass, because the day-to-day ends up demanding new things, so we always have to run after , always grating but I think this is the best way to learn something ...
Well, as you are doing the course I do not think it's interesting to pass everything ready for you here, so it would be easy , despite the difficulty of the exercise it is very good for you to learn ..
Let's leave here the path of stones for you beleza ??
Well, you asked how you could mount this exercise, if you would use a cursor, funcao or procedure, since it is an exercise because you do not use the 3? Below is a way for you, try to give continuity, there if doubts will come back to post here ...
Well, I think it's cool to help yes because you're learning, for similar situation everyone has passed and many still pass, because the day-to-day ends up demanding new things, so we always have to run after , always grating but I think this is the best way to learn something ...
Well, as you are doing the course I do not think it's interesting to pass everything ready for you here, so it would be easy , despite the difficulty of the exercise it is very good for you to learn ..
Let's leave here the path of stones for you beleza ??
Well, you asked how you could mount this exercise, if you would use a cursor, funcao or procedure, since it is an exercise because you do not use the 3? Below is a way for you, try to give continuity, there if doubts will come back to post here ...
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;
Hello friend
obigaced by being helping me with this exercise so that there are many things that you put there that I did not learn I know that I must run after more the more I seek more doubts I have
I tried to rotate this code more is giving error in line 8
In addition to showing for the teacher I will have to explain to him what I did problem is that I had the material of procedure function and separate cursors and not together that makes it difficult
more thanks for the gallows I'll see what I can do
more I think not There will be a lot
More Thank you again
obigaced by being helping me with this exercise so that there are many things that you put there that I did not learn I know that I must run after more the more I seek more doubts I have
I tried to rotate this code more is giving error in line 8
In addition to showing for the teacher I will have to explain to him what I did problem is that I had the material of procedure function and separate cursors and not together that makes it difficult
more thanks for the gallows I'll see what I can do
more I think not There will be a lot
More Thank you again
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
Oops and ai beleza ??
How are you doing to run there? It may be that this error is for you to try to run the funcao and not the procedure.
The call you have to mount it would be thus
[]
How are you doing to run there? It may be that this error is for you to try to run the funcao and not the procedure.
The call you have to mount it would be thus
begin
pck_cristiano.prc_GERA_CONTAS;
end;
I copied your code Paste in SQL
now is giving this error
You did procedure within Function?
now is giving this error
ERROR at line 1:
ORA-00900: invalid SQL statement
You did procedure within Function?
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
So
this code is the body (body) of a package, in this case you have to create the package and then call her procedure.
Here goes the complete package (Spec + Body).
just run it in your tool
then you create the package you can call it thus
[] 's
[]' s
this code is the body (body) of a package, in this case you have to create the package and then call her procedure.
Here goes the complete package (Spec + Body).
just run it in your tool
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;
/
begin
pck_cristiano.prc_GERA_CONTAS;
end;
[]' s
my I'm sorry for my ignorance I created the package
and then it was to run look at the error that is giving
and then it was to run look at the error that is giving
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
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
In addition to cursors and selects you need routines to do the calculation of accounts and then burn them in your table. for this you will need to give insert ..
the way I set up I separated some consultations for the purpose of facilitating, making it easier to understand what was mounted .. would give to reduce this code, but the selects vao complicating ..
Well, if I were you I would not worry about delivering this exercise working, but in understanding what you did in it, even if it is not fully mounted ...
Now imagine if your teacher asks you to change something, do some grouping, etc ... You would suffer a lot to try to change something ...
the way I set up I separated some consultations for the purpose of facilitating, making it easier to understand what was mounted .. would give to reduce this code, but the selects vao complicating ..
Well, if I were you I would not worry about delivering this exercise working, but in understanding what you did in it, even if it is not fully mounted ...
Now imagine if your teacher asks you to change something, do some grouping, etc ... You would suffer a lot to try to change something ...
I was giving a researched and analyzing the code that you sent me there I decided to do with only Select and cursor
I made only a select to test look at it as
Now I need to play inside a cursor and do the correct accounts or am I wrong?
I made only a select to test look at it as
Now I need to play inside a cursor and do the correct accounts or am I wrong?
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
Oops, and my friend beleza ??
So, with this query you can get the information you need, but you need to set up a way to read these results and apply the calculation rule. To set up this rule the ideal would be to use a procedure, because in it you would have your selects, cursor, variable and rule ..
well, I had the impression that you are confusing a little relation to the use of cursors, take a look at this example below.
Example 1 - Select Using cursor
Select without the cursor utilization
above we have 2 examples, in the 2 cases we use a select to get the current date, but in the first case this was done through the cursor ....
obs: you can not put in the body of the cursor nothing different from a select.
So you have 2 ways to set up your queries and can be with the use of cursors or simply running your select directly.
Then you will not be able to ride your calculating rule Thinking only in cursor or select .. maybe the easiest form is you riding a procedure, and within this procedure create your cursors, or selects and its rules ..
[] 's
So, with this query you can get the information you need, but you need to set up a way to read these results and apply the calculation rule. To set up this rule the ideal would be to use a procedure, because in it you would have your selects, cursor, variable and rule ..
well, I had the impression that you are confusing a little relation to the use of cursors, take a look at this example below.
Example 1 - Select Using cursor
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
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
obs: you can not put in the body of the cursor nothing different from a select.
So you have 2 ways to set up your queries and can be with the use of cursors or simply running your select directly.
Then you will not be able to ride your calculating rule Thinking only in cursor or select .. maybe the easiest form is you riding a procedure, and within this procedure create your cursors, or selects and its rules ..
[] 's
Ola Cristiano beleza
As I call this first example with cursor, so I can print the date on the screen
being that she appears me
PROCEDURE SuccessFully completed
More and the date where is it?
Thanks
As I call this first example with cursor, so I can print the date on the screen
being that she appears me
PROCEDURE SuccessFully completed
More and the date where is it?
Thanks
-
- Information
-
Who is online
Users browsing this forum: Bing [Bot] and 16 guests