mysql 存储过程 函数 触发器

mysql存储过程与函数

存储过程下载  demo

mysql> delimiter // -- 这里//为修改默认分隔符；mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)  -> BEGIN  ->  SELECT COUNT(*) INTO param1 FROM t;  -> END//Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;   // -- 改回来这里的默认分隔符为；

这里调用试用call

mysql> CALL simpleproc(@a); mysql> select @a;

函数demo

参数没有输入输出参数

mysql> CREATE FUNCTION hello (s CHAR(20))mysql> RETURNS CHAR(50) DETERMINISTIC  -> RETURN CONCAT(‘Hello, ‘,s,‘!‘);

这里调用试用 select，和普通函数一样了

mysql> SELECT hello(‘world‘);+----------------+| hello(‘world‘) |+----------------+| Hello, world! |+----------------+

变量的使用

declare last_month_start DATE;DECLARE my_sql INT DEFAULT 10 ;set var_name = 2;

流程控制

CREATE DEFINER=`root`@`localhost` PROCEDURE `student_insert`()BEGIN  set @x=0;  ins :LOOP    set @x = @x +1;    if @x=100 then      leave ins;    end if;    insert into student (stuname) values (CONCAT("name",@x));  END LOOP ins;ENDCREATE DEFINER=`root`@`localhost` PROCEDURE `student_insert2`()BEGIN  set @x=100;  ins :LOOP    set @x = @x +1;    if @x=120 then      leave ins;    elseif mod(@x,2) = 0 then      iterate ins;    end if;    insert into student (stuname) values (CONCAT("name",@x));  END LOOP ins;ENDCREATE DEFINER=`root`@`localhost` PROCEDURE `loop_demo`()BEGIN  set @x=1 ,@x1=1;  repeat    set @x = @x +1;  until @x > 0 end repeat;    while @x1 < 2 do    set @x1=@x1+1;  end while;END

游标的使用

CREATE DEFINER=`root`@`localhost` PROCEDURE `cursor_demo`()BEGIN  declare i_stuid int;  declare i_stuname varchar(20);  declare cur_stu cursor for select stuid,stuname from student;  declare exit handler for not found close cur_stu;    set @x1 = 0;  set @x2 = 0;    open cur_stu;    repeat    fetch cur_stu into i_stuid,i_stuname;    select i_stuid,i_stuname;  until 0 end repeat;    close cur_stu;ENDCREATE DEFINER=`root`@`localhost` PROCEDURE `cursor_demo3`()BEGIN DECLARE done INT DEFAULT 0; DECLARE a CHAR(16); DECLARE b,c INT; DECLARE cur1 CURSOR FOR SELECT stuname,stuid FROM `student`; DECLARE cur2 CURSOR FOR SELECT subid FROM `subject`; DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET done = 1;  OPEN cur1; OPEN cur2;  REPEAT  FETCH cur1 INTO a, b;  FETCH cur2 INTO c;  IF NOT done THEN    SELECT a,b,c;  END IF; UNTIL done END REPEAT;  CLOSE cur1; CLOSE cur2;END

触发器

必须建立在真实表之上，适合一些初始化数据

CREATE TABLE test1(a1 INT);CREATE TABLE test2(a2 INT);CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);CREATE TABLE test4( a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  b4 INT DEFAULT 0); DELIMITER | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN  INSERT INTO test2 SET a2 = NEW.a1;  DELETE FROM test3 WHERE a3 = NEW.a1;   UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END| DELIMITER ; INSERT INTO test3 (a3) VALUES  (NULL), (NULL), (NULL), (NULL), (NULL),  (NULL), (NULL), (NULL), (NULL), (NULL); INSERT INTO test4 (a4) VALUES  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

查看索引

show index from table

原标题：mysql 存储过程 函数 触发器

关键词：MYSQL