我的第一个存储过程

一、操作系统：win10 pro
工具：naviat Premium 12.1
创建步骤：
1、点击函数–新建函数


2、在begin……end之间输入过程体，我的整个存储过程可以进行参考：

CREATE DEFINER=`root`@`%` PROCEDURE `proc_test1`()
BEGIN
	set @i = 20;
	-- name字段数组
	set @array_name="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
	-- 年级字段数组
	set @array_grade="one two three four five six";
	-- 得出年级数组的成员个数，为空格数+1
	set @count=CHAR_LENGTH(@array_grade)-CHAR_LENGTH(REPLACE(@array_grade,' ',''))+1;
	while @i<50 do
		-- name字段从字段数组中随机取6个值
		set @name=substring(@array_name,round(1+RAND()*47),6);
		-- 随机取出年级数组成员的个数
		set @left_num=FLOOR(1+rand()*@count);
		set @grade=SUBSTRING_INDEX(SUBSTRING_INDEX(@array_grade,' ',@left_num),' ',-1);
		insert into test (id,name,grade)value(@i,@name,@grade);
		set @i=@i+1;
  END while;
END

3、执行存储过程，执行前必须要先保存，保存成功后，【运行】按钮才是可执行状态

二、Linux版本存储过程，格式会有些不一样

系统：Centos7
数据库： mysql5.7
1、首先，要重新定义结束符，若未重新定义分隔符，Linux会以分号“;”作为结束符，编写存储过程时会报错
2、复制多行存储过程代码到Linux系统中后，会提示：Display all 769 possibilities?(y or n)，这是由于tab键导致的，把代码的tab键都删掉后再复制调用就ok了

去掉tab键的存储过程代码：

drop  PROCEDURE if EXISTS `proc_test1`;
delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_test1`()
BEGIN
set @i = 1;
set @array_name="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
set @array_grade="one two three four five six";
set @count=CHAR_LENGTH(@array_grade)-CHAR_LENGTH(REPLACE(@array_grade,' ',''))+1;
while @i<20 do
set @name=substring(@array_name,round(1+RAND()*45),7);
set @left_num=FLOOR(1+rand()*@count);
set @grade=SUBSTRING_INDEX(SUBSTRING_INDEX(@array_grade,' ',@left_num),' ',-1);
insert into test (id,name,grade)value(@i,@name,@grade);
set @i=@i+1;
END while;
END $$
delimiter ;

把改段代码复制到Linux中：
---------------------1、root连接mysql-----------------------------------

[root@wangxj lotus]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> drop  PROCEDURE if EXISTS `proc_test1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

---------------------------2、用哪个库-----------------------------------------

mysql> use test;
Database changed

-----------------3、把上述的存储过程代码复制进来--------------------------------

mysql> drop  PROCEDURE if EXISTS `proc_test1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter $$
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_test1`()
    -> BEGIN
    -> set @i = 1;
    -> set @array_name="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
    -> set @array_grade="one two three four five six";
    -> set @count=CHAR_LENGTH(@array_grade)-CHAR_LENGTH(REPLACE(@array_grade,' ',''))+1;
    -> while @i<20 do
    -> set @name=substring(@array_name,round(1+RAND()*45),7);
    -> set @left_num=FLOOR(1+rand()*@count);
    -> set @grade=SUBSTRING_INDEX(SUBSTRING_INDEX(@array_grade,' ',@left_num),' ',-1);
    -> insert into test (id,name,grade)value(@i,@name,@grade);
    -> set @i=@i+1;
    -> END while;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

----------------4、结束符充值为分号并调用存储过程------------------------------

mysql> delimiter ;
mysql> call `proc_test1`();
Query OK, 0 rows affected (0.29 sec)