MySQL编程，函数，存储过程，触发器

65.函数
(
-- 1、mysql的变量 
/*系统变量 ：系统自定义的，只能读取，不能修改。show variables like 'char%';  */
(
show variables like 'version%';
select version();
)
-- 2.用户自定义变量： 
/*   
 定义一个变量：set
    变量赋值 : set 变量名 = 变量值。

   注意：为了区分 系统变量和字段与用户自定义变量，需要在用户变量前，增加@标识符。

    使用变量 ：通过 select 语句可以获得当前的变量的值。

   注意：这种方式创建的变量是 全局变量 ,能通过变量传递到查询语句。
 */
(
set @nam = 'tom';
select @nam;
-- 案例：
 /*查询名字叫tom的员工工资*/
select epay from emp where ename=@nam;
select * from emp;
)
-- 3.自定义函数
/*
自定义函数的要素：
函数名  --必须要有
参数列表 -- 可有可没有
函数体  -- 要有
返回值 -- 必须要有

语法：
定义：
returns -- 必须写的
create function 函数名 (参数列表) returns 返回值类型 
begin
函数体
end

调用：select 函数名(参数);
注意：函数是与当前的数据库绑定的，可以使用库名.函数名的形式调用；
*/
(
-- 案例：
/* 1、自定义函数，输出 hello world! */
	drop function if exists hw;
	create function hw() returns varchar(20)
	begin 
		return 'hello world';
	end
	select hw();
)
-- 4.sql中的流程控制：
/*
-- 分支判断
if 条件1 then -- {   
条件1满足执行的语句
elseif 条件2 then
条件2满足执行的语句
….
else 
上面的条件全都不满足，执行的语句
end if; -- }
注意：elseif 和 else 都是可以省略的。 
*/
(
--  案例：
/*1、判断当前的时间，大于18点，输出'早就放学了'；小于等于18点，输出'继续学习吧'。*/
create function fangxue() returns varchar(20)
begin
set @time = hour(now());
	if( @time > 18 ) then 
	return '早就放学啦';
	else 
	return '继续学习吧';
	end if;
end  

select fangxue();

/*2、如果存在某些员工的工资小于5000，
			则将这些员工的工资加500*/
create function gongzi() returns varchar(20)
begin
	if( (select min(epay) from emp) < 5000) then
	update emp set epay=epay+500 where epay<5000;
	return '工资添加成功'; 
	else
	return '没有工资<5000';
	end if;
end

select gongzi();
)
-- 5.循环
/*
   while 条件 do 
     循环体
    end while;
*/
(
-- 案例：
/*1、计算1-100的和*/

create function jisuan() returns int
begin
declare i int default 1;
declare sum int default 0;
	while i<=100 do
	set sum = sum+i;
	set i = i+1;
	end while;
return sum;
end

select jisuan();

/*2、计算10的阶乘*/

create function jiechen() returns int
begin
declare i int default 1;
declare sum int default 1;
	while i<=10 do
		set sum = sum*i;
		set i = i+1;
	end while;
return sum;
end

select jiechen();
)
-- 6.循环的提前终止 : leave 和 iterate
/*
leave   相当于  break   终止循环
iterate 相当于  continue   终止当前循环,继续下次循环
 
注意，不是根据leave和iterate所在的位置来决定终止哪个循环，而是由循环的标签来决定的。
 
循环的标签，给循环起名字。
标签 : while
end while 标签;
*/
(
/*案例：如果员工的平均工资小于7000，则将每个员工
			工资加100，直到平均工资大于7000为止。如果
			在加薪过程中，最高工资超过9300则停止本次
			加薪过程*/
create function jgz() returns varchar(20)
begin
w:while (select avg(epay) from emp) <7000 do
	update emp set epay=epay+100;
	if (select max(epay) from emp) >9300 then
		leave w;
	end if;
	end while;
return '添加工资结束';
end

select jgz();
)
-- 7.函数内使用的变量：
/*
1、全局变量
    @变量名 的形式，函数内和函数外通用。
 
2、局部变量，有2种方式实现：
   <1>
   函数的参数：
    参数，同样需要确定类型。
    语法： 参数名 类型

    一个函数，可以有多个参数，使用 逗号分割。
 
   <2>
    函数声明的局部变量：
      使用 declare声明局部变量。 需要指定类型，可以指定默认值 default。
      语法：declare 变量名 类型 default 默认值;

*/
(
-- 案例：
/*1、自定义函数，实现输入员工的姓名来查询员工的入职时间*/
drop function if exists csj;
create function csj(iname varchar(20)) returns date
begin
return (select estartime from emp where ename = iname);
end

select * from emp;
select csj('jack');

select 

/*2、使用声明局部变量的方式，计算10的阶乘。*/
create function jiechen2() returns int
begin
declare i int default 1;
declare sum int default 1;
	while i<=10 do
		set sum = sum*i;
		set i = i+1;
	end while;
return sum;
end

select jiechen2();
)
-- 8.case-end多分支语句: 可以使用自定义的方式显示结果列。
/*
语法：
  case 
   when 条件1 then  结果1
   when 条件2 then  结果2
    ……
    else 其他结果
  end

  注意：case表示函数开始，end表示函数结束。如果条件1成立，则返回结果1,如果条件2成立，
         则返回结果2，当全部不成立则返回其他结果，而当有一个成立之后，后面的就不执行了。
*/
(
-- 案例：
/*查询员工表姓名和性别，性别男显示‘m’，女显示‘f’*/
select ename,
case
 when esex='男' then 'm'
 when esex='女' then 'f'
end as '性别'
from emp;
)
)
-- 66.mysql存储过程和触发器
(
/*
  1、什么是存储过程？
     简单的说，就是一组带有逻辑的sql语句，功能强大，可以实现一些比较复杂的逻辑功能，
        类似于java语言中的方法；
 
  2、特性：
    1>有输入输出参数，可以声明变量，有if/else, case,while等控制语句，
       通过编写存储过程，可以实现复杂的逻辑功能；
    2>函数的普遍特性：模块化，封装，代码复用；
    3>速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

 
  2、存储过程与自定义函数的区别:
   1>存储过程实现的过程要复杂一些,而函数的针对性较强;
   2>存储过程可以有多个返回值,而自定义函数只有一个返回值;
   3>存储过程一般是作为一个独立的部分来执行（ execute 语句执行），
      而函数可以作为查询语句的一个部分来调用（select调用），由于函数可以返回一个表对象，
      因此它可以在查询语句中位于from关键字的后面。 sql语句中不可用存储过程，而可以使用函数。
*/
/*
 创建存储过程：
   create procedure pro_test()           --存储过程名称(参数列表)
begin                                    -- 开始
	-- 可以写多个sql语句;                  -- sql语句+流程控制
	select * from employee;
end                                      -- 结束 结束符


-- 执行存储过程
call pro_test();                         -- call 存储过程名称(参数);

-- 删除存储过程
       语法：drop procedure 存储过程名称;

参数：
in：表示输入参数，可以携带数据带存储过程中
    语法：in  参数名 数据类型

out： 表示输出参数，可以从存储过程中返回结果
    如何获取返回结果？
    1)定义一个全局变量  @变量名
    2)使用该全局变量接收存储过程的返回值
    3)查看返回值  select @变量名

inout： 表示输入输出参数，既可以输入功能，也可以输出功能

存储过程可以直接使用  select 返回值   返回结果。 

 */
(
-- 案例1：
/*传入一个员工的id，查询员工信息*/
use 
create procedure pro_idx(in e_id int)
begin
select * from emp where eid = e_id;
end

call pro_idx(5);

/*使用存储过程，输出'hello world!!'*/

create procedure pro_hw()
begin
select 'hello world';
end

call pro_hw();



/*输入员工的编号id,查询员工的部门id*/
create procedure pro_ep(inout i int)
begin
set i = (select did from emp where eid = i);
end

set @i=1;
call pro_ep(@i);
select @i;

/*输入一个整数，如果1，则返回“星期一”,如果2，返回“星期二”,
     如果3，返回“星期三”。其他数字，返回“错误输入”;*/
create procedure pro_week(in con int,out str varchar(20))
begin
	if con=1 then
		set str = '星期一';
	elseif con = 2 then
		set str = '星期二';
	elseif con = 3 then
		set str = '星期三';
	else
		set str = '错误输入';
	end if;
end

set @str = '';
call pro_week(5,@str);
select @str;

/*输入一个整数，累加求和。*/

create procedure pro_sum(inout shu int)
begin
declare i int default 0;
declare j int default 0;
while i<=shu do 
set j = j+i;
set i = i+1;
end while;
set shu = j;
end

set @shu = 100;
call pro_sum(@shu);
select @shu;

/*使用查询的结果赋值给变量（into）*/
-- 案例:
/*输入员工姓名，查询员工入职时间。*/
drop procedure pro_etime 
create procedure pro_etime(in e_name varchar(20), out e_time date)
begin
select estartime into e_time from emp where ename=e_name;
end

set @e_time = '';
call pro_etime('tom',@e_time);
select @e_time;
)
(
-- 案例
/*删除存储过程*/
drop procedure pro_etime;
)

)