--------------  Task 1 --------------------------

alter table student add tallness int;
alter table student modify email varchar2(40) null;

--------------  Task 2 --------------------------

--------------  Task 3 --------------------------

Nejrozumnější je vložit druhý záznam se stejným primárním
klíčem a dát ROLLBACK při SET AUTOCOMMIT ON/OFF

--------------  Task 4 --------------------------

create or replace
PROCEDURE AddStudent(
p_login IN VARCHAR2,
p_fname IN VARCHAR2,
p_lname IN VARCHAR2,
p_tallness IN NUMBER) AS
BEGIN
  insert into student (login, fname, lname, tallness) values (p_login, p_fname, p_lname, p_tallness);
END AddStudent;

execute AddStudent('nov12', 'Jarda', 'Novak', 170);
execute AddStudent('nov90', 'Lada', 'Novakova', 173);
execute AddStudent('hla10', 'Uhor', 'Hladky', 185);
select * from student;

------------

create or replace
FUNCTION FAddStudent(
p_login IN VARCHAR2,
p_fname IN VARCHAR2,
p_lname IN VARCHAR2,
p_tallness IN NUMBER)
RETURN VARCHAR2 AS
BEGIN
  insert into student (login, fname, lname, tallness) values (p_login, p_fname, p_lname, p_tallness);
  return 'ok';
exception
  when others then
    return 'error';
END FAddStudent;

set serveroutput on;
execute dbms_output.put_line(FAddStudent('bon007', 'James', 'Bond', 190));
execute dbms_output.put_line(FAddStudent('bac27', 'Radim', 'Baca', 175));


--------------  Task 5 --------------------------

create or replace
PROCEDURE StudentBecomeTeacher( p_login IN student.login%TYPE, p_department IN teacher.department%TYPE)
AS
  v_fname student.fname%TYPE;
  v_lname student.lname%TYPE;
BEGIN
  SELECT fname, lname INTO v_fname, v_lname FROM student WHERE login = p_login;
  DELETE FROM student WHERE login = p_login;
  INSERT INTO teacher VALUES (p_login, v_fname, v_lname, p_department, NULL);
  commit;
EXCEPTION
  WHEN OTHERS THEN
    rollback;
END StudentBecomeTeacher;

execute StudentBecomeTeacher('sob28', 100);

------------

create or replace
PROCEDURE AddStudent2(
p_fname IN VARCHAR2,
p_lname IN VARCHAR2,
p_tallness IN NUMBER) AS
v_login VARCHAR2(5);
BEGIN
  v_login := substr(p_lname, 0, 3) || '00';
  insert into student (login, fname, lname, tallness) values (v_login, p_fname, p_lname, p_tallness);
END AddStudent2;

execute AddStudent2('Bill','Gates', 166);