mysql高级语法：变量声明， 游标， 函数， 存储过程， 循环体， 触发器

# 准备： 查看该库下面有哪些表

SELECT table_name,ENGINE FROM information_schema.TABLES WHERE table_schema='obd_message' ; 

#  1.1 自定义存储过程  游标与流程控制 

DELIMITER $$  # 自定义终止符 美元符号 
DROP PROCEDURE IF EXISTS changeEngineType ;
CREATE PROCEDURE  changeEngineType(IN DB_NAME VARCHAR(32),IN Engine_type VARCHAR(16))
# PARAMS DB NAME AND ENGINE TYPE
BEGIN
        DECLARE done INT DEFAULT 0 ;  # RECORD CURSOR POSITION
        DECLARE TB_NAME VARCHAR(64);  # TABLE NAME
        DECLARE COMMAND VARCHAR(64);  # CHANGE DB COMMAND
        DECLARE curl CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema=DB_NAME ;
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; #错误定义，标记循环结束
        OPEN curl;


        REPEAT # LOOP START
                FETCH curl INTO TB_NAME ;
                IF NOT done THEN
                        #SET COMMAND=CONCAT('ALTER TABLE ',DB_NAME,'.',TB_NAME,' ENGINE = ',Engine_type);
                        # 拼更改引擎的命令 ALTER TABLE test_3 ENGINE = MYISAM ;
                        SET COMMAND=CONCAT('select * from ', DB_NAME ,'.' ,TB_NAME ,' limit 1' );                       
                        SET @E=COMMAND;  
                        PREPARE stmt1 FROM @E;
                                EXECUTE stmt1;              # 执行命令
                                DEALLOCATE PREPARE stmt1;   # 释放对象 deallocate prepare
                END IF;
        UNTIL done END REPEAT;  # 循环结束
        CLOSE curl; #关闭游标  
END;
$$
DELIMITER ;

# 1.2 调用存储过程
CALL changeEngineType('obd_message','MYISAM');  

# 2 创建和删除自定义函数语法
# UDF可以实现的功能不止于此,UDF有两个关键点,一个是参数,一个是返回值,UDF可以没有参数,但UDF必须有且只有一个返回值

# 2.1 自定义无参函数
CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World!";
SELECT  obd_message.simpleFun();

# 2.2 自定义带参多步骤函数

desc test ;
id INT(11) NO PRI auto_increment
colA INT(11) NO PRI 0 


DELIMITER $$

DROP FUNCTION IF EXISTS countRow;
CREATE FUNCTION countRow(uid INT UNSIGNED) 
RETURNS VARCHAR(20) 
BEGIN
DECLARE count INT DEFAULT 0 ;  # RECORD CURSOR POSITION
INSERT INTO  test (colA) values (2) ,(4);
SELECT COUNT(id) as cnt INTo count FROM test where id< uid ;
# RETURN count ; # 使用局部变量
 RETURN (SELECT COUNT(id) as cnt FROM test where id< uid); # 直接返回
END $$

SELECT countRow(19) ;

# 3 全局变量
SET @params1 = 100 ;
SELECT @params1 ;

# 4. 循环体 一般在函数或者是存储过程中使用
# 4.1 loop 

DELIMITER $$
DROP FUNCTION IF exists fun_addStr;
create FUNCTION fun_addStr(str1 VARCHAR(100),str2 VARCHAR(10),num INT) RETURNs VARCHAR(200)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE result VARCHAR(200) DEFAULT '';
    SET result=str1;
    myloop:loop
        SET i=i+1;
        SET result=concat(result,str2);
        IF i>num
        THEN
        LEAVE myloop;
        END IF;
    END loop myloop; 
    RETURN result;
END $$
DELIMITER;


SELECT fun_addStr('1','0',100) ;

# 4.2 while 

DELIMITER $$
DROP FUNCTION IF exists fun_addStr_while;
create FUNCTION fun_addStr_while(str1 VARCHAR(100),str2 VARCHAR(10),num INT) RETURNs VARCHAR(200)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE result VARCHAR(200) DEFAULT '';
    SET result=str1;
    WHILE i<num DO
SET i=i+1;
        SET result=concat(result,str2);
    END WHILE;
 
    RETURN result;
END $$
DELIMITER;

SELECT fun_addStr_while('1','0',100) ;

 # 4.2 repeat 

DELIMITER $$
DROP FUNCTION IF EXISTS fun_addStr_repeat;
CREATE FUNCTION fun_addStr_repeat(str1 VARCHAR(100),str2 VARCHAR(10),num INT) RETURNs VARCHAR(200)
BEGIN


    DECLARE i INT DEFAULT 1;
    DECLARE result VARCHAR(200) DEFAULT '';
    SET result=str1;

REPEAT # LOOP START
SET i=i+1;
SET result=concat(result,str2);
#IF i > num SET done=1 ; 
UNTIL  i > num  END REPEAT;  # 循环结束

  RETURN result;
END $$
DELIMITER;


SELECT fun_addStr_repeat('1','0',3) ;

#5. ITERATE语句

ITERATE语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。

ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。

ITERATE语句的基本语法形式如下：

ITERATE label 

其中，label参数表示循环的标志。

下面是一个ITERATE语句的示例。代码如下：

add_num: LOOP 
SET @count=@count+1; 
IF @count=100 THEN 
LEAVE add_num ; 
ELSE IF MOD(@count,3)=0 THEN 
ITERATE add_num; 
SELECT * FROM employee ; 
END LOOP add_num ; 

该示例循环执行count加1的操作，count值为100时结束循环。如果count的值能够整除3，则跳出本次循环，不再执行下面的SELECT语句。

说明：LEAVE语句和ITERATE语句都用来跳出循环语句，但两者的功能是不一样的。

LEAVE语句是跳出整个循环，然后执行循环后面的程序。而ITERATE语句是跳出本次循环，然后进入下一次循环。

使用这两个语句时一定要区分清楚。

# 6 触发器 

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `colA` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`colA`) 
) ENGINE=InnoDB  AUTO_INCREMENT=1  CHARSET=utf8
;


DELIMITER |
drop table if exists trigger_test ;
CREATE TABLE IF NOT EXISTS trigger_test (id int PRIMARY KEY auto_increment , time TIMESTAMP) |
DROP TRIGGER IF EXISTS trig_test | 


CREATE TRIGGER trig_test AFTER INSERT  # 在 insert（delete 、update） 之后(or before )执行 
    ON test FOR EACH ROW
    BEGIN
        INSERT INTO trigger_test VALUES(NULL, NOW());
     END
|
DELIMITER ;

# 6.2 

INSERT INTO test (colA) VALUES (1000) ;  # 插入记录
SELECT * FROM obd_message.trigger_test;  # 查询触发器执行效果