mysql存储过程

DELIMITER //
#因为mysql识别是以分号结束为标志。如果存储过程主体有多个sql语句，这样肯定不能执行第一个分号#后面的语句。所以需要用$$ 或//表示分号还没结束。

DELIMITER //
create procedure zxx_add1(in abc varchar(64),in a tinyint(2))
BEGIN

    declare a tinyint(20);
    DECLARE abc varchar(64);
    set a=0;
    while a<10 do
        #DECLARE abc varchar(64);
        SET abc='zxx';
        INSERT INTO tb_user (name,phone,email,password,state,sex,head_img,entity,create_date,update_date)
        VALUES (abc,'1582','allah@qq.com','gesge',1,1,null,null,null,null);
        set a=a+1;
    end while;
END //

DELIMITER //
create procedure zxx_add(in name1 varchar(64)  )
BEGIN
    DECLARE name1 varchar(64);
    SET name1='zxx13';
       INSERT INTO tb_user (name,phone,email,password,state,sex,head_img,entity,create_date,update_date)
        VALUES (name1,'1582','allah@qq.com','gesge',1,1,null,null,null,null);
END
//
call zxx_add('ddd')
drop procedure zxx_add


DELIMITER //
create procedure chaxun()
BEGIN
select * from tb_user;
END
//

#从一个表查询的结果作为输入插入另外一个表

DELIMITER //
create procedure add_employee(in name1 varchar(64),IN name_suffix_Start INT )
BEGIN
    #DECLARE name1 varchar(64);
    Declare _ecode varchar(8) CHARACTER SET utf8;
    declare userId bigint;
    declare a bigint;

      #从tb_user表中取user_id存入游标
        declare cur cursor for select id from dh_account.tb_user where id not in(select user_id from dh_enterprise.employee where del_flag=1 and project_id=82);
        open cur;
        set a=0;
        while a<2 do
            #取出游标值至变量中
            fetch cur into userId;
            INSERT INTO dh_enterprise.employee (code,name,phone,portrait,sex,age,identity_card,working_unit,construction_site,work_type,
                                            expire_date,user_id,
                                           department_id,project_id,del_flag,change_dept_time,update_time,create_time)
            VALUES (upper(left(uuid(),8)),concat(name1,name_suffix_Start),null,'gesge',1,20,null,null,null,null,null,userId,635,82,1,null,null,null);
            SET name_suffix_Start = name_suffix_Start + 1;
            set a=a+1;
        end while;

END
//

call add_employee(‘西瓜’,1)
drop procedure add_employee
删除存储过程 drop procedure zxx_add
调用存储过程
call chaxun()
call zxx_add(‘zxx1’) 实际上，生成数据是用的zxx。无论传参啥，生成的数据都是zxx1