mysql-8.0 函数和存储过程

函数和存储过程

1.创建一个存储过程

delimiter //

create procedure s1(in x int,out y int)

begin

select count(*) into y from students where sid >x;

end

//

delimiter ;

创建一个存储过程,并且定义两个参数,

x是输入参数.在进行存储过程调用的时候需要输入相应的参数.

y是输出参数.存储过程执行完成后输出一个返回值.存储到y中.

其中x和y都需要人为进行定义.一般在调用的时候输入参数可以是任意整数值,输出参数为一个变量,

​

call s1(1,@a);

调用s1存储过程.将1传入存储过程中,并且将输出结果存到@a中.

select @a;

查看输出结果@a;

2.存储过程中可以传入和输出多个参数.

 show create procedure cc;

+-----------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| Procedure | sql_mode                                                                                                              | Create Procedure                                                                                                                                                                                                                             | character_set_client | collation_connection | Database Collation |

+-----------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| cc        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`%` PROCEDURE `cc`(IN `a` int,IN `aa` int,OUT `b` int,OUT `bb` int)

BEGIN

	select count(*) into b from students WHERE sid > a;#Routine body goes here...

	select count(*) into bb from students WHERE sid > aa;

END | utf8                 | utf8_general_ci      | utf8mb4_0900_ai_ci |

+-----------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

1 row in set (0.00 sec)

调用格式 

call cc(1,2,@b,@bb);

查看

select @b,@bb;

 

2.创建一个函数

delimiter //

create function hello(s char(20))

returns char(50)

begin

return concat('hello',s,'!');

end

//

delimiter ;

注意:因为输入参数是字符串类型.所以在进行调用的时候应该添加引号"",来进行传参.

mysql> select hello("hello");

+----------------+

| hello("hello") |

+----------------+

| hellohello!    |

+----------------+

1 row in set (0.00 sec)

3.创建一个带有case when 语句的 存储过程.

delimiter //

create procedure exp_case(v_sid int)

begin

declare v int default 1;

select gender into v from students where sid=v_sid;

case v

when 0 then update students set gender=1 where sid= v_sid;

when 1 then update students set gender=0 where sid = v_sid;

else

update students set gender = -1 where sid = v_sid;

end case;

end;

//

delimiter ;

注意:创建的时候case when 是配对出现的.

或者使用下面的方式将v卸载case内部进行条件的筛选,

delimiter //

create procedure exp_case(v_sid int)

begin

declare v int default 1;

select gender into v from students where sid=v_sid;

case 

when v = 0 then update students set gender=1 where sid= v_sid;

when v = 1 then update students set gender=0 where sid = v_sid;

else

update students set gender = -1 where sid = v_sid;

end case;

end;

//

delimiter ;

当v = 1的时候和当v = 0 的时候然后做哪些操作.

4.流程控制语句 if语句

在存储过程或者函数中可以是用if流程控制语句.用来进行输入参数的匹配.

if语句中的开始和结束是成对出现的.

if和 end if;

delimiter //

create procedure dd1 ( in s int);

BEGIN

if s = 3 then select * from students where sid <= s;

ELSEIF s = 4 then select  * from students where sid = s;

ELSEIF s = 5 then select  * from students where sid >= s;

else select * from  students ;

end if;

END

//

delimiter ;

流程控制语句用来根据数据条件参数进行匹配.

5.使用存储过程插入100000条测试数据.

delimiter //

create procedure proc1();

BEGIN

DECLARE n int DEFAULT 1;

定义默认n的初始值是1,

while n <= 100000 DO

如果n< 100000那么就插入一条数据.

INSERT into students1 VALUES(n,'mike',1,1);

插入指定数据

set n = n+1;

数据插入后 n=n+1

end WHILE;

结束while循环

END

//

delimiter ;

使用while 语句循环插入100000条测试语句;

6.生成带有随机数的存储过程.

delimiter //

create crocedure proc2();

begin

declare n int default 1;

declare v_gender_id int;

declare v_dept_id int;

while n < 10000 do

set v_gender_id = round(rand());

set v_dept_id = floor(rand()*3+1);

insert into students2 values(n,'mike',v_gender_id,v_dept_id);

set n=n+1;

end while;

end;

//

delimiter ;

​

定义n初始值是1

定义v_gender_id=int类型

定义v_dept_id = int 类型

创建循环 当n< 10000的时候循环如下内容.

将v_gender_id 赋值成0或1的数值.其中rand()表示随机任意0-1之间的小数.round(rand()),表示将随机出来的小数进行四舍五入到整数部分.

同理将v_dept_id 赋值成小于等于(rand()*3+1)的最大整数值.其中rand()最大的小数会小于1,那么*3就会无限接近于3,+1的话就会接近于4,

如果取出小于等于这个数字的最大整数的话就是3.

使用insert 插入数据(这个就不用多说了.)

数据插入完成后将n赋值成 n+1.

结束while循环.

结束存储过程.

​

7. 定义多少个变量就可以在存储过程中 into 多少个被赋值变量.

delimiter //

create procedure p1(v_sid int)

begin

select sum(score),avg(score) into @x,@y from score where sid = v_sid;

select @x,@y;

end

//

delimiter ;

查询出来的值,可以对应进行赋值操作.

8.

 

9.