sql存储过程和函数

存储过程和数据库

show global variables like '_O%';
show session variables;
set @username = '刘禅';
-- set可以定义全局变量和用户变量，也可以给全局变量，用户变量和局部变量赋值
-- declare定义局部变量
-- declare 变量名列表 类型 default值

-- if条件表达式
/* 
if(条件) then 语句序列1;
elseif(条件) then 语句序列2;
else 语句序列;
end if;
每个语句序列后面都要加分号;
if elseif else endif
endif 后面也要加分号
*/
delimiter $$
begin
	declare v_avgscore float;
    select round(avg(score),2) into v_avgscore from sc where sno = 's3';
    if(v_avgscore >= 80.0) then 
		select '该生成绩好';
	elseif(v_avgscore < 80 and v_avgscore >= 60) then
		select '一般';
	else
		select 'abab';
	end if;
end $$

begin
	declare v_avgscore float;
    select round(avg(score),2) into v_avgscore from sc where sno='s3';
    if() then 语句1;
    elseif() then 语句2;
    elseif() then 语句3;
    else 语句4;
    end if;
end $$ 

/*
case 选择变量名
    when 表达式1 then 语句序列1;
    when 表达式2 then 语句序列2;
    else 语句序列n+1;
end case;
*/

begin
	declare v_avgscore float;
    declare v_grade int;
    select round(avg(score),2) into v_avgscore from sc where sno='s1';
    set v_grade = truncate(v_avgscore/10.0);
    case v_grade
		when 10 then 语句序列1;
        when 9 then 语句序列2;
        when 8 then 语句序列3;
        else 语句序列最后一个;
        end case;
end;

-- case和if判断都有else和end case或者end if

begin
	declare v_count,v_sum int default 0;
	while v_count < 100 do
		set v_count = v_count + 1;
        set v_sum = v_sum + v_count;
	end while;
    select v_sum as 1~100的和
end $$

/*
while 条件表达式 do
循环体语句
end while;
*/

/*
repeat
	循环体语句;
until 条件表达式 until后面没有分号结尾
end repeat;
*/

begin
	declare v_count,v_sum int default 0;
    repeat
		set v_count = v_count + 1;
        set v_sum = v_sum + v_count;
        until v_count >= 100 -- until后面没有分号
	end repeat;
    select v_sum,v_count;
end $$



-- while do end while; repeat until end repeat;

-- mysql中使用declare handler语句处理异常
-- declare handle_action handler for condition_value statement

declare continue handler for 1062 set info='插入失败，不能插入重复的数据';
declare exit handler for not found set info='查找失败';
declare continue handler for 1064 set info = '语法错误';


-- 存储过程，函数，游标，触发器

-- 存储过程
/*
create procedure 存储过程名
(
	in/out/inout 参数名 参数类型
    in/out/inout 参数名2 参数类型2
    in/out/inout 参数名3 参数类型3
)
begin
	声明部分;
    执行部分;
end;
*/
delimiter $$
create procedure user_time()
begin
	select current_user as 当前用户 ,current_date as 当前日期;
end $$
delimiter ;

use school;
create procedure p_sum()
begin
	declare v_count int default 1;
    declare v_sum int default 0;
	while v_count < 100 do
		set v_sum = v_sum + v_count;
        set v_count = v_count + 2;
	end while;
    select v_sum as '1~100的和';
end $$
delimiter ;
call p_sum();


delimiter $$
create procedure p_sum();
begin
	declare real_sum int default 0;
    declare real_count int default 1;
    while real_count <= 100 do
		real_sum = real_sum + real_count;
        real_count = real_count + 1;
	end while;
    select real_sum,real_count;
end $$
delimiter $$

delimiter $$
create procedure p_summ()
begin
	declare real_sum int default 0;
    declare real_count int default 1;
    repeat
		real_sum = real_sum + real_count;
        real_count = real_count + 1;
	until real_count > 100
    end repeat;
end $$
delimiter ;
call user_time();

-- 调用存储过程 call 存储过程名([实参1,实参2,实参3,。。。])
-- 如果存储过程有参数，可以将参数放在小括号里，如果没有参数，后面的小括号也不能省略。
-- 参数：参数名 类型
-- 带参数的存储过程
delimiter $$
create procedure insert_department(
	p_dno char(2),
    p_dname varchar(30);
    p_office varchar(4)
    )
begin
	declare info varchar(20) default '插入成功';
    declare continue handler for 1062 set info = '插入失败，不能插入重复的数据';
    insert into department values(p_dno,p_dname,p_office);
    select info;
end $$
delimiter ;
call insert_department('D5','美术','','');

-- 带输入参数的存储过程
delimiter $$
create procedure delete_department(p_dno char(2))
begin
	delete from department where dno=p_dno;
    select * from department;
end $$
delimiter ;

call delete_department('D5');

-- 带输出参数的存储过程
-- mysql存储过程参数如果不指定in,out,inout,则默认为in
delimiter $$
create procedure search_department(
	p_dno char(2),
    out p_dname varchar(30) -- 最后一个参数后面不加逗号，类似于select最后一个参数后面不加逗号
)
begin
	declare info varchar(30) default '查找成功';
    declare continue handler for not found set info = '查找失败';
    select dname into p_dname from department where dno = p_dno;
    select info;
end $$
delimiter ;

-- 带输入输出参数的存储过程
delimiter $$
create procedure swap(
	inout p_num1 int,
    inout p_num2 int -- p_num1和p_num2既作为输入也作为输出，所以是inout
)
begin
	declare v_temp int;
    set v_temp = p_num1;
    set p_num1 = p_num2;
    set p_num2 = v_temp;
end $$
delimiter ;
set @n1=12;
set @n2=56;
call swap(@n1,@n2);
select @n1,@n2;

-- 删除存储过程
drop procedure if exists swap;
drop procedure if exists swap;
drop procedure if exists p_name;


-- 函数只可以使用输入类型的数据，但参数前不可以指定IN;
-- 参数必须通过return语句来返回一个值，但参数方程没有返回值
-- 函数通常作为表达式的一部分杯调用，而存储过程的调用使用的是call

-- 创建函数的语法
/*
	create function 函数名（
		参数名1 参数类型1.
        参数名2 参数类型2，
        参数名n 参数类型n
    ）
    returns 数据类型
    begin
		声明部分
        return 表达式
	end $$
*/
delimiter $$
create function get_avgscore(f_sno char(2))
returns float
begin
	return(select round(avg(score),2))
    from sc where sno=f_sno;
end $$
delimiter ;

delimiter $$
create function get_avgscore(f_sno char(2))
returns float
begin
	return(select round(avg(score),2) from sc where sno = f_sno);
end $$
delimiter ;

drop function if exists get_avgscore;