mysql存储过程例子

记一记之前写的存储过程，就当复习啦

#大写的in意思是入参，后面是入参的名称，再后面是类型

CREATE DEFINER=`mtldb`@`%` PROCEDURE `proc_TermOnLine_Insert`(
  IN p_termno VARCHAR (12),
  IN p_curip VARCHAR (16),
  IN p_type VARCHAR (2)
)
BEGIN

#declare 意思是设置一个变量，后面跟名称和类型还有字符长度，默认值
  DECLARE m_last_machine VARCHAR (12) DEFAULT '' ;
  DECLARE m_last_logintime VARCHAR (16) DEFAULT '' ;
  DECLARE msg VARCHAR (1024) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'database error' ;
  DECLARE errcode INT DEFAULT 19000 ;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION 
  BEGIN
    SIGNAL SQLSTATE 'HY001' SET MESSAGE_TEXT = msg,
    mysql_errno = errcode ;
  END ;
    #select.....into.....语法是查询某个值或者某些值，将查询出来的字段赋值到into里面的值
  SELECT 
    cur_machine,
    login_time INTO m_last_machine,
    m_last_logintime 
  FROM
    pro_termonlineb 
  WHERE termno = p_termno ;
  
  IF TRIM(p_type) = "0" 
  THEN IF m_last_machine <> "" 
  THEN 
  UPDATE 
    pro_termonlineb 
  SET
    last_machine = m_last_machine,
    last_login_time = m_last_logintime,
    cur_machine = p_curip,
    login_time = NOW(),
    heart_time = NOW(),
    state = '0' 
  WHERE termno = p_termno ;
  ELSE 
  INSERT INTO pro_termonlineb (
    termno,
    last_machine,
    last_login_time,
    cur_machine,
    login_time,
    heart_time,
    state
  ) 
  VALUES
    (
      p_termno,
      p_curip,
      NOW(),
      p_curip,
      NOW(),
      NOW(),
      '0'
    ) ;
  END IF ;
  END IF ;
  
  IF TRIM(p_type) = "1"  
  THEN 
  UPDATE 
    pro_termonlineb 
  SET
    heart_time = NOW(),
    state = '0' 
  WHERE termno = p_termno ;
  END IF ;
END