Cacher is the code snippet organizer for pro developers

create or replace synonym my_books;

create or replace package main_package as
    function find_book(str varchar2) return varchar2;
    function total_books(genre_name varchar2) return number;
    procedure ban_user(u_id number);
    function genre_books(g_id number) return varchar2;
    procedure update_salary(exp number, percent number);
    procedure add_to_salary(worker_id number, new_salary number);
end main_package;

create or replace package body main_package as
    function find_book(str varchar2) return varchar2 as
        result varchar2(1024);
        cursor finded is
            select id from books where title like str;
        begin

        for book in finded loop
          result := result||book.id||', ';
        end loop;

        return result;
        end;

    function total_books(genre_name varchar2) return number
        is
            result number;
            begin
                select count(title) into result from books, books_genres, genres where genres.name=genre_name and books_genres.books_id = books.id and books_genres.genres_id = genres.id;
                return result;
            end;

    procedure ban_user(u_id number) as
        begin
            update users set banned=1 where id=u_id;
        end;

    function genre_books(g_id number) return varchar2 as
        result varchar2(1024);
        cursor c1(g_id number) is
            select title, genres.name from books, genres, books_genres where genres.id=1 and books.id=books_genres.books_id and books_genres.genres_id=genres.id;
        begin

        for book in c1(g_id) loop
          result := result||book.title||',';
        end loop;

        return result;
        end;

    procedure update_salary(exp number, percent number) as
        cursor get_workers(exp number) is
            select id from workers where exp_month > exp for update;
        begin
            for worker in get_workers(exp) loop
                update workers set salary = salary * percent where current of get_workers;
            end loop;
        commit;
        end;

    procedure add_to_salary(worker_id number, new_salary number) as
        negative_salary exception; pragma exception_init(Negative_salary, -20001);
        too_big_increase exception; pragma exception_init(Too_big_increase, -20002);
        to_big_decrease exception; pragma exception_init(To_big_decrease, -20003);
        old_salary number := 0;
        exp number := 0;

    begin
        select workers.salary into old_salary from workers where workers.id = worker_id;
        select workers.exp_month into exp from workers where workers.id = worker_id;

        if (new_salary - old_salary < 0) then Raise_application_error(-20001, 'A salary could not be negative.');
        elsif (new_salary > old_salary * 0.5) then Raise_application_error(-20002, 'The salary increase is more than 50%.');
        elsif (-new_salary > old_salary * 0.5) then Raise_application_error(-20003, 'The salary decrease is more than 50%.');
        end if;

        #update workers set workers.exp_month = 'abc'
        update workers set workers.salary = old_salary + new_salary
        where workers.id = worker_id;
        commit;

        exception
        when Negative_salary Then DBMS_OUTPUT.put_line(SQLERRM);
        when Too_big_increase Then DBMS_OUTPUT.put_line(SQLERRM);
        when To_big_decrease Then DBMS_OUTPUT.put_line(SQLERRM);
        when VALUE_ERROR then DBMS_OUTPUT.put_line(SQLERRM);

    end add_to_salary;
end main_package;

create sequence check_seq;
create sequence books_seq;

create or replace trigger copy_each_second before delete on books for each row
begin
 if (Mod(check_seq.Nextval, 2) = 0) then
   insert into journal(title, info, created_at) values(books_seq.Nextval, 'Delete', sysdate);
 end if;
end;

create or replace trigger copy_each_third before update on books for each row
begin
  if (Mod(check_seq.Nextval, 3) = 0) then
    insert into journal(title, info, created_at) values(books_seq.Nextval, 'Insert', sysdate);
  end if;
end;

create or replace trigger forbid_lending before update on users for each row
begin
  if (:old.banned = 1) then
    raise_application_error(-20000, 'This user is banned');
  end if;
end;

create or replace trigger forbid_books_title_changes before update on books for each row
begin
  if (:new.title <> :old.title) then
    raise_application_error(-20000, 'You cannot change the book title');
  end if;
end;

create or replace trigger set_minimum_salary before update on workers for each row
begin
  if (:new.salary < 50) then
    raise_application_error(-20000, 'You cannot set the salary less than 50');
  end if;
end;


CREATE trigger FOR dml_event_clause ON view
COMPOUND TRIGGER
INSTEAD OF EACH ROW IS BEGIN
  statement;
END INSTEAD OF EACH ROW;

create or replace trigger tr_turtles_ue
  for update of essence
  on turtles
  compound trigger
    bUpdPainters  boolean;
 
  before each row is
  begin
    if :new.name = 'Сплинтер' and :old.essence = 'Крыса' and :new.essence = 'Сэнсэй' then
      bUpdPainters := true;
    end if;
  end before each row;
  
  after statement is
  begin
    if bUpdPainters then
      update Turtles
         set essence = 'Ниндзя'
       where essence = 'Художник';
    end if;
  end after statement;
end tr_turtles_ue;