CREATE TABLE accounts
(
    ID               VARCHAR2(10),
    date_of_creation DATE,
    account_type     VARCHAR2(20),
    balance          NUMBER
);


SET serverout ON;
CREATE OR REPLACE FUNCTION GEN_ID(date_created IN DATE)
    RETURN VARCHAR2
    IS
    sequence_number NUMBER;
    str_date        VARCHAR2(20);
    sequence_string VARCHAR2(20);
BEGIN
    SELECT COUNT(*) INTO sequence_number FROM accounts;
    sequence_number := sequence_number + 1;
    IF (sequence_number < 10) THEN
        sequence_string := CONCAT('00', TO_CHAR(sequence_number));
    ELSIF (sequence_number < 100) THEN
        sequence_string := CONCAT('0', TO_CHAR(sequence_number));
    ELSE
        sequence_string := TO_CHAR(sequence_number);
    END IF;

    str_date := TO_CHAR(date_created);
    RETURN CONCAT(SUBSTR(str_date, -2, 2), CONCAT('AC', TO_CHAR(sequence_string)));
END;
/


-- checking
DECLARE
    str VARCHAR2(20);
BEGIN
    str := GEN_ID(SYSDATE);
    DBMS_OUTPUT.PUT_LINE(str);
END;
/
-- checking


-- Trigger
CREATE OR REPLACE TRIGGER before_insert_accounts
    before INSERT
    ON
        accounts
    FOR each ROW
DECLARE
    ID VARCHAR2(10);
BEGIN
    :NEW.date_of_creation := SYSDATE;
    ID := GEN_ID(:NEW.date_of_creation);
    :NEW.ID := ID;
END;
/

INSERT INTO accounts(account_type, balance) VALUES ('current', 5000);
INSERT INTO accounts(account_type, balance) VALUES ('savings', 15000);
INSERT INTO accounts(account_type, balance) VALUES ('savings', 50000);