Oracle SQL Function

Asked

Viewed 88 times

1

How do I perform the function that returns the number of credits for a given course by blocking the code? As the example at the end.

TB_DISCIPLINA

COD_DISCI DESCRICAO                     CREDITOS   TB_CURSO_COD_CURSO
---------- --------------------------- ---------- ------------------
         1 REDES                                6                  1
         2 BANCO DE DADOS                       6                  3
         3 ALGORITMO                            4                  2
         4 SISTEMAS OPERACIONAIS                4                  3

TB_CURSO

 COD_CURSO DESCRICAO                                                                                                                                                                                                                                                      
---------- --------------
         1 REDES         
         2 ENGENHARIA    
         3 SISTEMAS

Function to be created

CREATE OR REPLACE FUNCTION <nome do função>
(parâmetros – variável e tipo de dado)
RETURN <tipo de dado retornado>
BEGIN
<implementação da função>
RETURN <variável>
END;;
sql oracle

  • I got it wrong or is the course-discipline relationship 1-n? Shouldn’t it be n-n?

  • Relationship is TB_DISCIPLINA (1,n) course Tb_course (1,1). I think so becomes clearer.

  • I agree with Ronaldo.

  • Your model is a little strange and that’s why they’re weirding you out. By the example you listed each course has a single discipline, it is not strange?

  • I was reviewing my model and I believe that it is correct, because there is the matricula table, where it stores the COD_ALUNO and also the COD_DISCIPLINA. I’ll print this table.

  • @Marcianolazari This does not change the fact that the course is linked to a single discipline...?

  • @Ronaldoaraújoalves No, a course can be linked to several disciplines.

  • 1

    @Ronaldoaraújoalves I believe I have now become correct, I changed the structure of the tables, I believe they were wrong anyway! see now!

Show 3 more comments

1 answer

3


Functional example. I tried to put in the http://sqlfiddle.com, but as it has function I could not.

Tables

create table TB_DISCIPLINA (
  COD_DISCI number(10),
  DESCRICAO varchar2(255),
  CREDITOS number(10),
  TB_CURSO_COD_CURSO number(10)
);

create table TB_CURSO (
  COD_CURSO number(10),
  DESCRICAO varchar2(255)
);

Records

insert into TB_DISCIPLINA values (1, 'REDES', 6, 1);
insert into TB_DISCIPLINA values (2, 'BANCO DE DADOS', 6, 3);
insert into TB_DISCIPLINA values (3, 'ALGORITMO', 4, 2);
insert into TB_DISCIPLINA values (4, 'SISTEMAS OPERACIONAIS', 4, 3);

insert into TB_CURSO values (1, 'REDES');
insert into TB_CURSO values (2, 'ENGENHARIA');
insert into TB_CURSO values (3, 'SISTEMAS');

Function

CREATE OR REPLACE FUNCTION QTDE_CREDITOS_CURSO (curso integer)
return integer 
as
  creditos integer;
begin
  select sum(CREDITOS) into creditos from TB_DISCIPLINA where TB_CURSO_COD_CURSO = curso;
  return creditos;
end;
/

Example of Use

select 
  Descricao as Curso,
  QTDE_CREDITOS_CURSO(cod_curso) as Creditos
from 
  TB_CURSO
where cod_curso = 2;

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.