declare str varchar(100) default '2010';
declare n int default '2011';
declare newstr varchar(100);
declare newn int;


use HBI;
show variables like 'character_set_%';
set names utf8;


-- -------创建药品表-----------
DROP TABLE IF EXISTS yaopintb;
CREATE TABLE yaopintb (
  yaopinmc VARCHAR(100) NOT NULL,
  yaopinid varchar(10) NOT NULL,
  yaopindlid varchar(2) NOT NULL,
  yaopinxlid varchar(2),
  yapindlmc VARCHAR(100),
  yapinxlmc VARCHAR(100),
  jgzb VARCHAR(50),
  ypyt VARCHAR(100),
  PRIMARY KEY (yaopinid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- -------------创建过程事务------------

DROP PROCEDURE IF EXISTS pro_insert_yaopintb;   
CREATE PROCEDURE pro_insert_yaopintb  
(  
    cout INT,
    ypname varchar(100), 
    bcat varchar(2),
    bcatname varchar(100), 
    scat varchar(2), 
    scatname varchar(100) 

)

BEGIN  
    DECLARE j INT DEFAULT 0;
    declare bscat varchar(4);
    set bscat = concat(bcat,scat);
    WHILE j < cout DO  
        -- SELECT j ;

INSERT INTO yaopintb(yaopinmc,yaopinid,yaopindlid,yaopinxlid,yapindlmc,yapinxlmc,jgzb,ypyt) VALUES (concat(ypname,concat(bscat,j)), concat(bscat,j), bcat,bscat,concat(bcatname,bcat),concat(scatname,scat),'unknown','unknown');
        SET j = j + 1;  
    END WHILE;  
END;

-- --------------调用过程事务-------------
call pro_insert_yaopintb(50,'a sipi lin','A','kou fu','a','xiao yan yao');
call pro_insert_yaopintb(50,'bai jia hei','A','kou fu','b','gan mao yao');
call pro_insert_yaopintb(50,'jin huang gao','B','wai fu','c','gao yao');

call pro_insert_yaopintb(50,'tou gu xiao','B','wai fu','d','cao yao');

-- ----------------下面有两个例子：---------------------

-- ------------------例子一--------------------------------

-- ---------插入门诊数据------------
DROP PROCEDURE IF EXISTS pro_insert_menzhentb;   
CREATE PROCEDURE pro_insert_menzhentb  
(  
    cout INT,
    insertdt varchar(20),
    brid int 
)
BEGIN  
    DECLARE pnum INT DEFAULT 0;
    DECLARE ysid int;
    DECLARE ypid VARCHAR(10);
    DECLARE costrand int DEFAULT 0;
    DECLARE ysrand int DEFAULT 0;
    DECLARE yprand int DEFAULT 0;
    DECLARE tdt datetime;


 --   set insertdt='2008-4-2 15:3:28';
    set tdt=STR_TO_DATE(insertdt,'%Y-%m-%d %h:%i:%s');
  --  select tdt;
    
    WHILE pnum < cout DO  
        -- SELECT pnum ;



SET costrand = floor(rand()*301); 

SET ysrand = floor(rand()*31); 

SET yprand = floor(rand()*50);

set ysid =0;
-- select concat( concat( concat( concat(ysid,ysrand%2+1), ysrand%5+1), '0'), ysrand%3+1);
set ysid = cast( concat( concat( concat( concat(ysid,ysrand%2+1), ysrand%5+1), '0'), ysrand%3+1) as signed);




if yprand%4 = 0 then
set ypid='Aa';
elseif yprand%4 = 1 then
set ypid='Ab';
elseif yprand%4 = 2 then
set ypid='Bc';
else 
set ypid='Bd';
end if;

set ypid=concat(ypid,yprand);


INSERT INTO menzhentb(guahaoid,bingrenid,shijian,yearofmz,monthofmz,dayofmz,yishengid,keshiid,yuanquid,yaopinid,yongyaoje,yongyaosl,qtsr,tybs,yllbbs,yblxbs) VALUES (NULL,brid,tdt,EXTRACT(YEAR from tdt),EXTRACT(MONTH from tdt),EXTRACT(DAY from tdt),ysid,ksid,yqid,ypid,costrand,costrand%5+1,costrand%100,concat('',costrand%2),concat('',yprand%3+1),concat('',(ysrand%2 + yprand%3)%2));
      
if(EXTRACT(HOUR from (tdt + INTERVAL 10 MINUTE) ) > 20 ) then
set tdt = tdt + INTERVAL 12 HOUR;
else 
set tdt = tdt + INTERVAL 10 MINUTE;
end if; 
SET pnum = pnum + 1;
set brid = brid+1;
    END WHILE; 
END;


call pro_insert_menzhentb(100000,'2012-01-01 08:00:00',0);
-- call pro_insert_menzhentb(100000,'2013-01-01 08:00:00',0);


--  -----------------------------例子二-----------------------------------

-- -------新建住院表-----------
DROP TABLE IF EXISTS zhuyuantb;
CREATE TABLE zhuyuantb (
  zhuyuanid int NOT NULL auto_increment,
  bingrenid int NOT NULL,
  shijian DATETIME,
  yearofmz char(4),
  monthofmz char(2),
  dayofmz char(2),
  yishengid int NOT NULL,
  keshiid int NOT NULL,
  yuanquid int NOT NULL,
  yaopinid varchar(10) NOT NULL,
  yongyaoje DECIMAL(10,2),
  yongyaosl DECIMAL(10,2),
  qtsr DECIMAL(10,2),
  tybs smallint ,
  zylxbs char(2),
  yblxbs char(2),
  PRIMARY KEY (zhuyuanid,bingrenid,yishengid,keshiid,yuanquid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




DROP PROCEDURE IF EXISTS pro_insert_zhuyuantb;   
CREATE PROCEDURE pro_insert_zhuyuantb  
(  
    cout INT,
    insertdt varchar(20),
    brid int 
)
BEGIN  
    DECLARE pnum INT DEFAULT 0;
    DECLARE ysid int;
    DECLARE ypid VARCHAR(10);
    DECLARE costrand int DEFAULT 0;
    DECLARE ysrand int DEFAULT 0;
    DECLARE yprand int DEFAULT 0;
    DECLARE tdt datetime;


 --   set insertdt='2008-4-2 15:3:28';
    set tdt=STR_TO_DATE(insertdt,'%Y-%m-%d %h:%i:%s');
  --  select tdt;
    
    WHILE pnum < cout DO  
        -- SELECT pnum ;



SET costrand = floor(rand()*301); 

SET ysrand = floor(rand()*31); 

SET yprand = floor(rand()*50);

set ysid =0;
-- select concat( concat( concat( concat(ysid,ysrand%2+1), ysrand%5+1), '0'), ysrand%3+1);
set ysid = cast( concat( concat( concat( concat(ysid,ysrand%2+1), ysrand%5+1), '0'), ysrand%3+1) as signed);




if yprand%4 = 0 then
set ypid='Aa';
elseif yprand%4 = 1 then
set ypid='Ab';
elseif yprand%4 = 2 then
set ypid='Bc';
else 
set ypid='Bd';
end if;

set ypid=concat(ypid,yprand);


INSERT INTO zhuyuantb(zhuyuanid,bingrenid,shijian,yearofmz,monthofmz,dayofmz,yishengid,keshiid,yuanquid,yaopinid,yongyaoje,yongyaosl,qtsr,tybs,zylxbs,yblxbs) VALUES (NULL,brid,tdt,EXTRACT(YEAR from tdt),EXTRACT(MONTH from tdt),EXTRACT(DAY from tdt),ysid,ksid,yqid,ypid,costrand,costrand%5+1,costrand%100,concat('',costrand%2),concat('',yprand%2+1),concat('',(ysrand%2 + yprand%3)%2));
      
if(EXTRACT(HOUR from (tdt + INTERVAL 10 MINUTE) ) > 20 ) then
set tdt = tdt + INTERVAL 12 HOUR;
else 
set tdt = tdt + INTERVAL 10 MINUTE;
end if; 
SET pnum = pnum + 1;
set brid = brid+1;
    END WHILE; 
END;


call pro_insert_zhuyuantb(10000,'2012-01-01 08:00:00',0);