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 預存程序 函數 觸發器