mysql：存储过程样例

来源：互联网发布：网络装修平台有哪些编辑：程序博客网时间：2024/06/04 18:56

drop PROCEDURE if EXISTS PK_out_patient_test;
CREATE PROCEDURE PK_out_patient_test()

begin

---定义变量并赋初值

DECLARE num int DEFAULT 0;
DECLARE name2 int DEFAULT 1;
DECLARE idno int DEFAULT 43000000;
DECLARE tel_no int DEFAULT 1350000000;
DECLARE patientid int DEFAULT 2000;
DECLARE devicesn int DEFAULT 10100;
DECLARE teamid VARCHAR(32);

DECLARE done int ;

--定义游标

DECLARE c_outpat_teamid cursor for select team_id from sys_team;
/* 异常处理 */
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
truncate table BG_OUTPAT_MASTER_INDEX;
while num<100 do
open c_outpat_teamid;---打开游标
set done=0;

while done<>1 do

---打开游标

fetch c_outpat_teamid into teamid;

set @dates= CONCAT(FLOOR(2016 + (RAND() * 1)),'-',LPAD(FLOOR(1 + (RAND() * 11)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0));
set @times= CONCAT(LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0));

---循环插入数据
insert into ……;--插入数据，变量需要用@符号

set devicesn=devicesn+1;
set name2=name2+1;
set idno=idno+1;
set tel_no=tel_no+1;
set patientid=patientid+1;
end while;
set num=num+1;
CLOSE c_outpat_teamid;
end while;
end

0 0