Mysql stored procedure function trigger, mysql Stored Procedure
Mysql stored procedure and function Stored Procedure download demo
Mysql> delimiter // -- here // modify the default 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; // -- the default delimiter here is changed;
Call trial call here
mysql> CALL simpleproc(@a); mysql> select @a;
Function demo
No input/output parameters
mysql> CREATE FUNCTION hello (s CHAR(20))mysql> RETURNS CHAR(50) DETERMINISTIC -> RETURN CONCAT(‘Hello, ‘,s,‘!‘);
Here we call the trial select statement, which is the same as the normal function.
mysql> SELECT hello(‘world‘);+----------------+| hello(‘world‘) |+----------------+| Hello, world! |+----------------+
Use of Variables
declare last_month_start DATE;DECLARE my_sql INT DEFAULT 10 ;set var_name = 2;
Process Control
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
Use of cursors
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
Trigger
Must be built on a real table, suitable for some initialization data
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);
View Indexes
show index from table