posted 7 years ago
Hi all! I made some MySQL courses and i took all the exams everything.Theoreticaly i am good but to finish with everything i had to make a projects.I came here because after lots of hours of thinking what is wrong i gave up ....So if you can help me i would be very happy especially i don't have so much time to finish the project.
I must create a procedure that create 5 tables.And one tables intermediant.In the intermediant table i must load records from a files and after to populate every tables.FIrst i must use a loop and a cursor and the other one a trigger.
The procedure to create the tables is
create schema if not exists pfinal;
use pfinal;
delimiter $
drop procedure init$
create procedure init ()
BEGIN
drop table if exists Intermediar,Vizite,Medici,Pacienti,Cabinete;
create table Medici(id int unsigned not null primary key ,
nume varchar (30),prenume varchar (30),
statut enum ( 'primar','specialist'), specialitate varchar (30), unique (nume,prenume))Engine=InnoDB;
create table Pacienti(id int unsigned not null primary key,
nume varchar (30) , prenume varchar (30) ,data_nasteri date, unique (nume,prenume))Engine=InnoDB;
create table Cabinete(id int unsigned not null primary key,
nume varchar (30) unique)Engine=InnoDB;
create table if not exists Vizite(id int unsigned not null primary key,
DataVizita datetime, Durata time,
id_medic int unsigned not null ,
id_pacient int unsigned not null ,
id_cabinet int unsigned not null ,
foreign key (id_medic) references Medici(id),
foreign key ( id_pacient ) references Pacienti(id),
foreign key (id_cabinet) references Cabinete(id))Engine=Innodb;
create table if not exists Intermediar(id int auto_increment primary key,
Data_Vizita varchar(30) , Ora_intrare time,Nume_pacient varchar (30),Prenume_pacient varchar (30),
Nume_medic varchar (30), Prenume_medic varchar (30), Cabinet varchar (30),
id_medic int,id_pacient int,id_cabinet int);
END$
delimiter ;
call init();
I load data from the files
load data local infile 'C:\\Users\\Nely\\Desktop\\sql_pfinal_date (1)\\medici.txt'
into table Medici
fields terminated by ','
lines terminated by '\n'
ignore 1 lines
(nume,prenume,statut,specialitate);
-- Data_Vizita date , Ora_intrare time,Nume_pacient varchar (30),Prenume_pacient varchar (30),
-- Nume_medic varchar (30), Prenume_medic varchar (30), Cabinet varchar (30));
load data local infile 'C:\\Users\\Nely\\Desktop\\sql_pfinal_date (1)\\vizite.txt'
into table Intermediar
fields terminated by ','
lines terminated by '\n'
ignore 1 lines
(Data_Vizita,Ora_intrare,Nume_pacient,Prenume_pacient,Nume_medic,Prenume_medic,Cabinet);
update intermediar set Data_vizita=str_to_date(Data_vizita,'%d/%m/%Y');
After i make a procedure to populate the tables with a cursor and
drop procedure if exists POP_TAB_cursor ;
delimiter //
CREATE PROCEDURE POP_TAB_cursor()
begin
declare data_vizita,ora_vizita,nume_pac,prenume_pac,nume_med,prenume_med,cabinet varchar(30);
declare id_medic,id_pacient,id_cabinet int;
declare stop_cursor condition for 1329;
declare pop_tab cursor for select (Data_Vizita,Ora_intrare,Nume_pacient,Prenume_pacient,Nume_medic,Prenume_medic
,Cabinet,id_medic,id_pacient,id_cabinet) from intermediar;
declare exit handler for stop_cursor -- definire handler de tip exit
begin
end;
set foreign_key_checks = 0;
open pop_tab;
loop
fetch pop_tab into data_vizita,ora_vizita,nume_pac,prenume_pac,nume_med,prenume_med
,cabinet,id_medic,id_pacient,id_cabinet;
insert ignore into Pacienti(id,nume,prenume) values(id_pacient,nume_pac,prenume_pac);
insert ignore into Medici(id,nume,prenume) values (id_medic,med,prenume_med);
insert ignore into Cabinet(id,nume) values (id_cabinet,cabinet);
insert ignore into Vizite(id_medic,id_pacient,id_cabinet) values(id_medic,id_pacient,id_cabinet);
set foreign_key_checks = 1;
end loop;
close pop_tab;
END //
DELIMITER ;
call POP_TAB_cursor();
it told me
11:00:13 call POP_TAB_cursor() Error Code: 1241. Operand should contain 1 column(s) 0.000 sec
For the trigger part i found much harder they said to use last_insert_id or select.But why tu use last insert id? because the id for medic ,pacient,cabinet is load from a files.