10 Functions, Triggers

create function deptCountFunc(deptName varchar(30)) returns integer
begin
    declare dCount integer;

    select count(*) into dCount
    from instructor
    where instructor.deptName = deptCountFunc.deptName;

    return dCount;
end

select deptName
from instructor
where deptCountFunc("Physics") > 5;

create function instructorOf(deptName char(20)) returns table (
  id varchar(5),
  name varchar(20),
  deptName varchar(20),
  salary numeric (10, 2)
)
    return table(
    select id, name, deptName, salary
    from instructor
    where instructor.deptName = instructorOf.deptName;
  );

select name
from instructorOf("Physics");

create procedure deptCountProc (in deptName varchar(20),
                                out dCount integer)
begin
    select count(*) into dCount
    from instructor
    where instructor.deptName = deptCountProc.deptName;
end

declare dCount integer;
call deptCountProc("Physics", dCount);

while <booleanExpression> do
    ; statements
end while

repeat
    ; statements
until <booleanExpression>
end repeat

declare totalBudget integer default 0;
for
    i as
        select budget from department
    do
        set totalBudget = totalBudget + i.budget
end for

show triggers;

create trigger setnullTrigger
before update of takes
referencing new row as nrow
for each row
when(nrow.grade = "")
begin atomic
  set nrow.grade = null
  set nrow.attendance = 0
end;

create trigger creditsEarned
after update of takes on(grade)
referencing new row as nrow
referencing old row as orow
for each row
when nrow.grade != 'F' and nrow.grade is not null
 and (orow.grade = 'F' or orow.grade is null)
begin atomic
    update student
    set totCred = totCred + (
    select credits
    from course
    where course.cid = nrow.cid
  )
  where student.id = nrow.id;
end;