MySQL中使用用户自定义的函数

函数定义基本公式如下

create function function_name(function_param param_type)
returns return_type
begin 
	function_body
end

过程定义的基本公式（与函数类似）

create procedure procedure_name(in in_param_name param_type, out out_param_name param_type)
begin
	procedure_body
end

注： 包含在begin与end中间是作为一个单独的事务来执行

其他语法

while 布尔表达式 do
	语句序列;
end while;

repeat 	语句序列;
util 布尔表达式
end repeat

if 布尔表达式
	then 语句或复合语句
elseif 布尔表达式
	then 语句或复合语句
else 语句或复合语句
end if

实例

向表中插入一个学生，完成课程登记，对应《数据库系统概念》p99

# 函数实例
create function dept_count(dept_name varchar(20))
    returns integer
    begin
        declare d_count integer;
        select count(*) into d_count
        from instructor
            where instructor.dept_name = dept_name;
        return d_count;
    end;

select dept_name, budget
from department
where dept_count(dept_name) > 0;

# 过程实例
create procedure instructor_of (dept_name varchar(20))
    begin
            select ID,name,dept_name,salary
            from instructor
            where instructor.dept_name = dept_name;
    end

call instructor_of('Finance');

# 插入学生
create function registerStudent5(
        s_id varchar(5),
        s_courseid varchar(8),
        s_secid varchar(8),
        s_semester varchar(6),
        s_year numeric(4, 0),
        errorMsg varchar(100)
) returns integer
begin
        declare currEnrol integer;
        declare relLimit integer;

        select count(*) into currEnrol
        from takes
        where course_id = s_courseid and sec_id = s_secid
                and semester = s_semester and year = s_year;

        select capacity into relLimit
        from classroom natural join section
        where course_id = s_courseid and sec_id = s_secid
                and semester = s_semester and year = s_year;

        if(currEnrol < relLimit) then
            begin
                insert into takes values
                    (s_id, s_courseid, s_secid, s_semester, s_year, null);
                return (0);
            end;
        else
            set errorMsg = 'Error';
            return (-1);
        end if;
end;