CODEWITHSUNDEEP
oracle-databaseplsql
darkpirate
darkpirate

Reputation: 722

Is it possible to associate values to rows in oracle in a procedure?

I have a procedure which of course works on a selection, now I want to add to each row a score in order to sort them according to it. Is there a way to do such a thing in Oracle?

I have this table:

CREATE TABLE Occr_lezione (
Codice_corso                varchar2(20),
Nome_modulo                 varchar2(50),
Data_inizio_ed_modulo       date,
Giorno_lezione              number(1),
Ora_inizio_lezione          date,
Data_inizio_occr_lezione    date,
Data_fine_occr_lezione      date    NOT NULL,
Nome_sede                   varchar2(30),
Num_aula                    varchar2(3),
Tipo_aula                   varchar2(20),
--
CONSTRAINT fk_Occr_lezione_lezione  FOREIGN KEY (Codice_corso,Nome_modulo,Data_inizio_ed_modulo,Giorno_lezione,Ora_inizio_lezione) REFERENCES Lezione(Codice_corso,Nome_modulo,Data_inizio_ed_modulo,Giorno_lezione,Ora_inizio_lezione) ON DELETE CASCADE,
CONSTRAINT fk_Occr_lezione_aula     FOREIGN KEY (Nome_sede,Num_aula,Tipo_aula)  REFERENCES Aula(Nome_sede,Num_aula,Tipo_aula) ON DELETE SET NULL,
CONSTRAINT pk_Occr_lezione          PRIMARY KEY (Codice_corso,Nome_modulo,Data_inizio_ed_modulo,Giorno_lezione,Ora_inizio_lezione,Data_inizio_occr_lezione),
CHECK       ( trunc(Data_inizio_occr_lezione) = trunc(Data_fine_occr_lezione) ), -- data inizio = data fine // prenotazione giornaliera
CHECK       ( Data_inizio_occr_lezione < Data_fine_occr_lezione ) -- ora inizio < ora fine // coerenza temporale
);

Which contains basically the lesson of a university college. The last 3 values are the Name of the building the number of the room and the type of the room which are the foreign key of a table that contains the classrooms, in particular, this one:

CREATE TABLE Aula(
Nome_sede       varchar2(30),
Num_aula        varchar2(3),
Tipo_aula       varchar2(15),
Locazione       varchar2(20)    NOT NULL,
Capienza        NUMBER(4)       NOT NULL,
--
CONSTRAINT  fk_Nome_sede    FOREIGN KEY (Nome_sede) REFERENCES sede(Nome_sede) ON DELETE CASCADE,
CONSTRAINT  pk_Aula     PRIMARY KEY (Nome_sede,Num_aula,Tipo_aula),
CHECK       (Tipo_aula IN ('Conferenze','Laboratorio','Aula'))
);

now as the classes are inserted in the first table (Occr_lezione) the foreign key of the classroom is inserted as null. This because I need to create a procedure that assign each class to a classroom. How you say?

Glad you asked.

As you can see in the second table there's an attribute, the last one precisely, which is basically the capacity of the classroom ( in terms of people that could contain ). Great. Now to assign each lesson to the respective class I need to know how many people are going to attend to it. Let's say I have a function that returns that value. This is not all tough, because there are different types of classrooms: Regular and laboratory. Of course some lessons are taken in the regular rooms and others in the labs. But that's not big of a deal because i have this table:

CREATE TABLE Modulo (
Codice_corso        varchar2(20)    NOT NULL,
Nome_modulo         varchar2(50),
Locazione_modulo    varchar2(20)    NOT NULL,
--
CONSTRAINT fk_Modulo_Corso  FOREIGN KEY(Codice_corso) REFERENCES Corso(Codice_corso) ON DELETE CASCADE,
CONSTRAINT pk_Modulo        PRIMARY KEY(Codice_corso,Nome_modulo),
CHECK       (Locazione_modulo IN ('Aula','Laboratorio','Conferenze'))
);

In which the third attribute defines exactly that and that I can easily join to the classes table using the first attribute.

Ok with this in mind what I'm trying to achieve is to associate each class with the classroom of the correct type and the closest capacity to minimize the waist of free seats. So basically assign via an update to each row of the first table the last 2 attributes (that translated are room_number and room_type). To do so my idea was to give each row a score (which is basically the affluence, that again I have a function to calculate) adding a temporary column (if that make sense) and then order them by type and score (using order by). After that I can do the same thing with the classroom table in which I already have the type and capacity and then somehow (I still don't know how to do this last part) assign to each row of the classes ordered table the corresponding from the classrooms ordered table. I don't know if there is a better way to do so, in that case I'm very interested in learning how.

Upvotes: 1

Views: 101

Answers (1)

psaraj12
psaraj12

Reputation: 5072

You can use the below select in your procedure

select function_score(input_data) score,a.* from your_table a
Order by a.type,score

Update 1:- you can create ENUM using plsql table like the below

create or replace package my_classroom_enum as
      type classrooms is table of varchar2(100);
      set_of_classrooms classrooms := classrooms('conference',
                                                 'Lab',
                                                 'Standard');
end;


declare
begin
for i in 1..3 
loop
dbms_output.put_line(my_classroom_enum.set_of_classrooms(i));
end loop;
end;

OUTPUT:-

conference
Lab
Standard

Upvotes: 1

Related Questions