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;
參數沒有輸入輸出參數
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; END CREATE 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; END CREATE 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; END CREATE 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