I'm writing a procedure to fill up a child table from a parent table. The child table however has more fields than the parent table ( as it should be ). I've conjured a cursor which point to a selection, which is essentially a join of multiple tables. Here's the code I got so far :
CREATE OR REPLACE PROCEDURE Pop_occ_lezione
AS
x Lezione%rowtype;
CURSOR cc IS
WITH y as(
SELECT Codice_corso,
nome_modulo,
Data_inizio_ed_modulo diem,
Giorno_lezione,
ora_inizio_lezione o_i,
ora_fine_lezione o_f,
anno,
id_cdl,
nome_sede,
locazione_modulo loc
FROM lezione
join ( select id_cdl, anno, codice_corso from corso ) using (codice_corso)
join ( select codice_corso, locazione_modulo from modulo ) using (codice_corso)
join ( select nome_sede, id_cdl from cdl ) using (id_cdl)
WHERE
case
when extract (month from Data_inizio_ed_modulo) < 9 then extract (year from Data_inizio_ed_modulo) - 1
else extract (year from Data_inizio_ed_modulo)
end = extract (year from sysdate+365)
)
SELECT *
FROM y
WHERE sem_check(y.diem,sysdate+365) = 1;
--
BEGIN
FETCH cc into x;
EXIT when cc%NOTFOUND;
INSERT INTO Occr_lezione
VALUES (
x.Codice_corso,
x.Nome_modulo,
x.diem,x.giorno_lezione,
x.Ora_inizio_lezione,
to_date(to_char(next_day(sysdate,x.Giorno_lezione),'DD-MM-YYYY') || to_char(x.Ora_inizio_lezione,' hh24:mi'),'dd-mm-yyyy hh24:mi'),
to_date(to_char(next_day(sysdate,x.Giorno_lezione),'DD-MM-YYYY') || to_char(x.Ora_fine_lezione,' hh24:mi'),'dd-mm-yyyy hh24:mi'),
x.nome_sede,
0,
x.loc
);
END LOOP;
END;
/
But of course it won't work, because the variable x has the type of my initial table row, which has less columns then my selection. Unfortunately As far as I know a rowtype variable is needed to cycle trough a cursor, in order to fetch data from it. Can you see the contradiction? How can I change the code? Is there a certain type of variable which can be crafted to reflect a row from my query result? Or maybe a way to cycle trough the data in the cursor without using a support variable? Or maybe something entirely different? Please let me know.
Ok, so as suggested I tried something like this:
INSERT INTO Occr_lezione(
Codice_corso,
Nome_modulo,
Data_inizio_ed_modulo,
Giorno_lezione,
Ora_inizio_lezione,
Ora_fine_lezione,
Anno,
Id_cdl,
Nome_sede,
Locazione_modulo
)
WITH y as(
SELECT Codice_corso,
Nome_modulo,
Data_inizio_ed_modulo,
Giorno_lezione,
Ora_inizio_lezione,
Ora_fine_lezione,
Anno,
Id_cdl,
Nome_sede,
Locazione_modulo
FROM Lezione
join ( select Id_cdl, Anno, Codice_corso from Corso ) using (codice_corso)
join ( select Codice_corso, Locazione_modulo from Modulo ) using (Codice_corso)
join ( select Nome_sede, Id_cdl from Cdl ) using (id_cdl)
WHERE
case
when extract (month from Data_inizio_ed_modulo) < 9 then extract (year from Data_inizio_ed_modulo) - 1
else extract (year from Data_inizio_ed_modulo)
end = extract (year from sysdate+365)
)
SELECT *
FROM y
WHERE sem_check(y.Data_inizio_ed_modulo,sysdate+365) = 1;
END;
/
But it says PL/SQL: ORA-00904: "LOCAZIONE_MODULO": invalid identifier
which isn't true, because the query return a table in which such column is present... am I missing something? The code is compiled with no errors, it occurs when I try to fire the procedure. In the table Occr_lezione as you can see:
CREATE TABLE Occr_lezione (
Codice_corso varchar2(20) NOT NULL,
Nome_modulo varchar2(50) NOT NULL,
Data_inizio_ed_modulo date NOT NULL,
Giorno_lezione number(1) NOT NULL,
Ora_inizio_lezione date NOT NULL,
Data_inizio_occr_lezione date,
Data_fine_occr_lezione date NOT NULL,
Nome_sede varchar2(30) NOT NULL,
Num_aula varchar2(3) NOT NULL,
Tipo_aula varchar2(20) NOT NULL,
--
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
there is not a column named Locazione_modulo, however the last column Tipo_aula as the same type and size of Locazione modulo :
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'))
);
So it should be irrelevant, right?
If you really want to use explicit cursors, you can declare x to be of type cc%rowtype
CREATE OR REPLACE PROCEDURE Pop_occ_lezione
AS
CURSOR cc IS ...
x cc%rowtype;
...
Unless you are using explicit cursors because you want to be able to explicitly fetch the data into local collections that you can leverage later on in your procedure, code using implicit cursors tends to be preferrable. That eliminates the need to FETCH and CLOSE the cursor or to write an EXIT condition and it implicitly does a bulk fetch to minimize context shifts.
BEGIN
FOR x IN cc
LOOP
INSERT INTO Occr_lezione ...
END LOOP;
END;
Of course, in either case, I would hope that you'd choose more meaningful names for your local variables-- x and cc don't tell you anything about what the variables are doing.
If all you are doing is taking data from one set of tables and inserting it into another table, it would be more efficient to write a single INSERT statement rather than coding a PL/SQL loop.
INSERT INTO Occr_lezione( <<column list>> )
SELECT <<column list>>
FROM <<tables you are joining together in the cursor definition>>
WHERE <<conditions from your cursor definition>>