存储过程
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
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