SELECT * FROM student;
CREATE OR REPLACE PROCEDURE AddStudent(
p_login CHAR,
p_fname VARCHAR2,
p_lname VARCHAR2,
p_tallness NUMBER) AS
BEGIN
    INSERT INTO student VALUES(p_login,p_fname,p_lname,p_tallness);
END AddStudent;

EXECUTE AddStudent('zid003','John','Doe',199);

CREATE OR REPLACE FUNCTION FAddStudent(p_login CHAR, p_fname VARCHAR2, p_lname VARCHAR2, p_tallness NUMBER)
RETURN VARCHAR AS
v_return VARCHAR(50);
BEGIN
    INSERT INTO student VALUES(p_login,p_fname,p_lname,p_tallness);
    v_return:='ok';
    RETURN v_return;
    EXCEPTION
    WHEN OTHERS THEN
     v_return:='error';
    RETURN v_return;
END;

EXECUTE DBMS_OUTPUT.put_line(FAddStudent('aho004','John','Doe',199));

SET serveroutput ON;


CREATE TABLE Teacher (
login CHAR(6) NOT NULL PRIMARY KEY,
fname VARCHAR2(30) NOT NULL,
lname VARCHAR2(50) NOT NULL,
department INT NOT NULL,
specialization VARCHAR2(30) NULL);



CREATE OR REPLACE PROCEDURE StudentBecomeTeacher(
p_login teacher.login%TYPE,
p_department teacher.department%TYPE) AS
/*v_fname teacher.fname%type;
v_lname teacher.lname%type;*/
BEGIN
    inser
    DELETE FROM student WHERE login = p_login;
    INSERT INTO Teacher(login,fname,lname,department) VALUES(p_login,v_fname,v_lname,p_department);
    COMMIT;
    WHEN OTHERS THEN
    ROLLBACK;
END StudentBecomeTeacher;

SELECT * FROM student;
SELECT * FROM teacher;
EXECUTE StudentBecomeTeacher('zid003',5);




CREATE OR REPLACE PROCEDURE AddStudent2(

p_fname VARCHAR2,
p_lname VARCHAR2) AS
v_name CHAR(6);
v_i int;
BEGIN
    v_i:=0;
    v_name:=SUBSTR(p_lname,0,4)||'0'||CAST(v_i AS CHAR);
    WHILE LoginExist(v_name)
    LOOP
        v_i:=v_i+1;
        v_name:=SUBSTR(p_lname,0,4)||'0'||CAST(v_i AS CHAR);
    END LOOP;
    INSERT INTO student(login,fname,lname) VALUES(v_name,p_fname,p_lname);
END AddStudent2;

EXECUTE AddStudent2('Stepan','Chvatik');
SELECT * FROM student;

ALTER TABLE student add isTall int;
ALTER TABLE student add CHECK(isTall=1 OR isTall=0);



CREATE OR REPLACE PROCEDURE IsStudentTall AS
CURSOR c_student IS SELECT * FROM student;
v_record student%ROWTYPE;
BEGIN
    OPEN c_student;
    LOOP
        FETCH c_student INTO v_record;
        EXIT WHEN c_student%NOTFOUND;
            UPDATE student SET isTall = 0 WHERE login = v_record.login;
            UPDATE student SET isTall = 1 WHERE login = v_record.login AND v_record.tallness > (SELECT AVG(tallness) FROM student);

    END LOOP;
    CLOSE c_student;
END IsStudentTall;

SELECT * FROM student;
EXECUTE IsStudentTall;
EXECUTE IsStudentTall('aho004');

CREATE OR REPLACE FUNCTION LoginExist(
p_login CHAR)
RETURN BOOLEAN AS
v_return BOOLEAN;
BEGIN
    SELECT login FROM student WHERE login=p_login;
    v_return:=TRUE;
    RETURN v_return;
    EXCEPTION
    WHEN OTHERS THEN
     v_return:=FALSE;
    RETURN v_return;
END;
BEGIN
IF(LoginExist('bush06'))
THEN
 DBMS_OUTPUT.put_line('je');
ELSE
    DBMS_OUTPUT.put_line('ne');
END IF;
END;
EXECUTE DBMS_OUTPUT.put_line(LoginExist('buh06'));