mysql存过学习

来源：互联网发布：中美贸易战知乎编辑：程序博客网时间：2024/05/10 16:55

new:

DROP PROCEDURE IF EXISTS badboy.new_bb_test_user_InsertDB;
CREATE PROCEDURE badboy.`new_bb_test_user_InsertDB`()
begin
declare test_k int default 1;
declare selet_id int default 6;
declare var_char varchar(255);
declare test_char varchar(255);

insertDB: while(test_k<100) do
if(test_k%5=0)then
set selet_id=10;
else if(test_k%5=1) then
set selet_id=6;
else if(test_k%5=2) then
set selet_id=7;
else if(test_k%5=3) then
set selet_id=8;
else if(test_k%5=4) then
set selet_id=9;
end if;
end if;
end if;
end if;
end if;

set var_char= convert(test_k,char);
insert into bb_test_user (ID,Email_Address,Name_id,selectid) VALUES (test_k,concat('testusernew100',var_char,'@test.com'),concat('bbuser',var_char),selet_id);
commit;
set test_k=test_k+1;
end while insertDB;

end;

update:

create procedure bb_ssid_updateDB()
begin
declare test_k int default 1;
declare var_char varchar(255);

updateDB: while(@test_k<5) do
set @var_char=(select substring(VHMEmail,1,12) from bb_test_ssid where id=@test_k);
select @var_char;
update bb_test_ssid set VHMName=@var_char whereid=@test_k;
commit;
set @test_k=@test_k+1;
select @test_k;
end while updateDB;

end