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;
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