PL/SQL Practice Tuesday

----------- PRACTICE 01 -----------
CREATE OR REPLACE FUNCTION factorial(x IN NUMBER)
RETURN NUMBER
IS
    y NUMBER := 1;
    i NUMBER;

BEGIN

    IF x < 0 THEN
        DBMS_OUTPUT.put_line('Factorial cant be less than 0!');

    ELSE
        FOR i IN 1 .. x LOOP
            y := y * i;

        END LOOP;

    END IF;

    RETURN y;
END;
/

DECLARE
    fact NUMBER;
BEGIN
     DBMS_OUTPUT.put_line('Factorial:' || factorial(5));
END;

----------- END PRACTICE 01 -----------

----------- PRACTICE 02 -----------
CREATE OR REPLACE TRIGGER emp_update
BEFORE UPDATE ON emp

FOR EACH ROW

DECLARE
name VARCHAR(64);
BEGIN


    DBMS_OUTPUT.put_line('Old sal was ' || :OLD.sal || ' euro and became ' || :NEW.sal || ' (' || (:NEW.sal - :OLD.sal) || ')');


END;
/

SELECT * FROM emp;
UPDATE emp SET sal = 7100 WHERE empno = 7698;

----------- END PRACTICE 02 -----------

----------- PRACTICE 03 -----------
-- CREATE TABLE users (
-- id integer,
-- username varchar(16),
-- password varchar(32),

-- CONSTRAINT user_primary PRIMARY KEY (id)
-- );

INSERT INTO users VALUES (1, 'geochareas', 'incorrect');

CREATE OR REPLACE TRIGGER users_ins
BEFORE INSERT ON users

FOR EACH ROW
DECLARE
    same_pass NUMBER;
BEGIN
    SELECT COUNT(*) INTO same_pass
    FROM users
    WHERE password = :NEW.password;

    DBMS_OUTPUT.put_line(same_pass || ' users have the same password (' || :NEW.password || ')');
END;
/

SELECT * FROM USER;

INSERT INTO users VALUES (2, 'geochareas2', 'incorrect');

----------- END PRACTICE 03 -----------