declare @id varchar(50); set @id='4028e4962c3df257012c3df3b4850001'; select * from sims_sample_detect where ID= @id;
在MySQL中的寫法：
復制代碼 代碼如下:
set @a = 189; select * from bc_article where id = @a //不用declare
在Orcale中的寫法：
5)MySQL存儲過程：
復制代碼 代碼如下:
DELIMITER $$ DROP PROCEDURE IF EXISTS `SIMS`.`transaction_delSampleInfo`$$ CREATE DEFINER=`root`@`%` PROCEDURE `transaction_delSampleInfo`(in sampleInfoId varchar(50)) BEGIN start transaction; update sims_sample_info set del='1' where ID = sampleInfoId; update sims_sample_detect set del='1' where SAMPLE_ID_PARENT = sampleInfoId; update sims_sample_detect_info set del='1' where DETECT_ID in( select ID from sims_sample_detect where SAMPLE_ID_PARENT = sampleInfoId ); commit; END$$ DELIMITER ;
變量名不能跟列名相同，否則效果為1=1，且MySQL不區分大小寫。
6）mysql 游標 mysql沒有像orcale的動態游標，只有顯示游標，例子如下：
復制代碼 代碼如下:
DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`liyukun`$$ CREATE DEFINER=`ids`@`localhost` PROCEDURE `liyukun`(out z int) BEGIN declare count1 int; DECLARE done INT DEFAULT 0; declare v_haoma varchar(50); declare v_yingyeting varchar(100); DECLARE cur1 CURSOR FOR select haoma,yingyeting from eryue where id<2; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; //這里和oracle有區別，Oracle的PL/SQL的指針有個隱性變 量%notfound，Mysql是通過一個Error handler的聲明來進行判斷的 OPEN cur1; cur1: LOOP FETCH cur1 INTO v_haoma,v_yingyeting; IF done=1 THEN //如果沒有數據了，則離開 LEAVE cur1; ELSE select count(*) into count1 from year2012 where haoma=v_haoma ; if(count1=0) then insert into year2012(haoma, yingyeting) values(v_haoma,v_yingyeting); else set z = z+1; update year2012 set eryue = ‘100' where haoma=v_haoma; end if; END IF; END LOOP cur1; CLOSE cur1; END$$ DELIMITER ;
