ICA0016 Oracle programmeerimiskeeled SQL ja PLSQL 13. Ülesanne (0)

13. Ülesanne    1. Koostage tabel, mis sisaldab üliõpilaste ees-ja perekonnanimesid (10 nime ) ning algselt tühja 
veergu email.    CREATE TABLE yliopilased 
( 
eesnimi VARCHAR2(50), 
perenimi VARCHAR2(50), 
email VARCHAR2(50) 
); 
 
INSERT INTO yliopilased VALUES ('Anna','Rong', NULL); 
INSERT INTO yliopilased VALUES ('Sanne','Kanne', NULL); 
INSERT INTO yliopilased VALUES ('Reelika','Uus', NULL); 
INSERT INTO yliopilased VALUES ('Robin','Hood', NULL); 
INSERT INTO yliopilased VALUES ('Jaak','Mae', NULL); 
INSERT INTO yliopilased VALUES ('Toivo','Kalevipoeg', NULL); 
INSERT INTO yliopilased VALUES ('Kalev','Toivopoeg', NULL); 
INSERT INTO yliopilased VALUES ('Tiit','Vest', NULL); 
INSERT INTO yliopilased VALUES ('Peeter','Paan', NULL); 
INSERT INTO yliopilased VALUES ('Karlsson','Katuselt', NULL);’ 
 
 
SELECT * FROM yliopilased; 
       


2. Seejärel koostage PL/SQL-programm, mis moodustab igale üliõpilasele e-aadressi järgneva eeskirja 
kohaselt.  
a) üliõpilase eesnimest võetakse esimene täht ning perekonnanimi kogu pikkuses;  
b) kui nii kipub tekkima ühesuguseid kasutajanimesid, võetakse 2. kasutajatunnuse moodustamisel 
eesnimest ühe asemel kaks tähte;  
c) Kui moodustatud kasutajanimi on pikem kui 8 sümbolit, lõigatakse lõpust üleliigsed sümbolid ära  
d) kasutajatunnusele lisatakse juurde „@itcollege.ee“.  
Saadud e-aadressid lisatakse tabelisse. Kasutage programmi koostamisel protseduure ja tõrketöötlust 
(juhul kui nende reeglite alusel ei õnnestu unikaalset meiliaadressi moodustada, lõpetada töö 
tõrkega) 
 
CREATE OR REPLACE PROCEDURE email_generator 
AS 
 
uus_email VARCHAR2(21); 
duplicates_num NUMBER(2); 
erind EXCEPTION; 
CURSOR opilased IS 
   SELECT eesnimi, perenimi FROM yliopilased; 
 
BEGIN 
 
FOR opilane IN opilased LOOP 
 
uus_email := lower(substr(opilane.eesnimi, 0, 1)) || lower(substr(opilane.perenimi, 0, 7)) || '@itcollege.ee'; 
 
SELECT count(*) INTO duplicates_num FROM yliopilased WHERE email = uus_email; 
IF duplicates_num > 0 THEN 
 
   uus_email := lower(substr(opilane.eesnimi, 0, 2)) || lower(substr(opilane.perenimi, 0, 6)) || '@itcollege.ee'; 
    
   SELECT count(*) INTO duplicates_num FROM yliopilased WHERE email = uus_email; 
   IF duplicates_num > 0 THEN 
      RAISE erind; 
   END IF; 
 
END IF; 
 
UPDATE yliopilased SET email = uus_email WHERE eesnimi = opilane.eesnimi AND perenimi = opilane.perenimi; 
COMMIT; 
 
END LOOP; 
 
EXCEPTION 
WHEN erind THEN 
ROLLBACK; 
dbms_output.put_line('Ei ole võimalik moodustada unikaalset emaili!'); 
WHEN others THEN 
ROLLBACK; 
dbms_output.put_line('Mingi muu tõrge!'); 
 
END; 


Procedure EMAIL_GENERATOR compiled 
 
 
EXEC email_generator; 
 
PL/SQL procedure successfully completed.    
 
 
SELECT * FROM yliopilased;