首页 > 代码库 > 存储过程/游标/mysql 函数
存储过程/游标/mysql 函数
存储过程和函数（存储在 mysql数据库中的 proc表，所以检查有没有这个表）
存储过程是一种存储程序（如正规语言里的子程序一样）,mysql支持有两种：存储过程，在其他SQL语句中可以返回值的函数（使用起来和 mysql预装载的函数一样,如 pi()）
一个存储过程包括名字，参数列表，以及可以包括很多SQL语句的SQL语句集。
as:(复合语句块)
CREATE PROCEDURE producedure1 /*name存储过程名*/
(in parameter1 interger) /*parameters参数*/
BEGIN /*start of block 语句块头*/
DECLARE varialbe1 CHAR(10); /* variables 变量声明*/
IF parameter1 = 100 THEN /*start of IF if条件*/
SET variable1 = ‘birds‘; /*assignment 赋值*/
ELSE
SET variable1 = ‘beasts‘; /*assignment 赋值*/
END IF; /*end of if if结束*/
INSERT INTO table1 values(variable1); /*statement SQL语句*/
......
END; /*end of block 语句块结束*/
DELIMITER // /*设置分隔符，默认为 ;*/
CREATE PROCEDURE p1 () select * from t; //
DELIMITER ; /*还原原来的分隔符 ; */
CALL p1 ();
调用： CALL 存储过程名();
则 CALL p1(); 相当规模于执行了 select * from t;
合法的存储过程（curd, drop table, set, commit, rollback()）
删除存储过程：
DROP PROCEDURE 存储过程名;
Charayeristics Clauses 特征子句
CREATE PROCEDURE p2()
LANGUAGE SQL -->不起作用，只是作说明以下的句子是用SQL写的,系统默认的，可以不用声明(为了兼容，最好声明)
NOT DETERMINISTIC -->是传递给系统的信息。这里一个确定过程的定义就是那些每次输入一样输出也一样的程序。在这里，既然主体中SELECT语句，那返回肯定是未知的，因此称其为 NOT DETERMINISTIC
SQL SECURITY DEFINER -->此句可以定义为 SQL SECURITY INVOKER, 这是进入权限控制的领域了，SQL SECURITY DEFINER 意味着在调用时检查创建过程用户的权限，使用时告诉MYSQL服务器检查创建过程的用户就可以了，当过程已经被调用，就不检查执行调用过程的用户了，另一个 INVOKER 则是告诉服务器在这一步(即使用)仍然要检查调用者的权限
COMMENT ‘A Procedure‘ -->一个可选的注释说明
SELECT * FROM t; //
所以以上相与下面的等价:
CREATE PROCEDURE p2()
SELECT * FROM t; //
特征子句也有默认值，如果省略，则相当于：
LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT
CREATE PROCEDURE p4()
SELECT "hi"; //
CALL p4();
则显示了字段名为 hi,值也为 hi
Parameters 参数
CREATE PROCEDURE p5()
.....
CREATE PROCEDURE p5([IN] name data-type)
.....
CREATE PROCEDURE p5(OUT name data-type)
.......
CREATE PROCEDURE p5(INOUT name data-type)
.........
IN:表示输入参数，默认为 IN(input)
OUT：输出参数
INOUT:既能作为输入也可以作为输出
IN:
DELIMITER //
CREATE PROCEDURE pp(p INT)
SET @X=P; //
CALL pp(100);
SELECT @X;
表示把 p的值给变量X;即 @X = 100;
OUT:
DELIMITER //
CREATE PROCEDURE pp(OUT p int)
set p = -100; //
CALL pp(@y);
SELECT @y;
这次 p是输出参数
新SQL语句：
varaibles 变量
在复合语句中声明变量的指令是 DECLARE;
as:
DELIMITER //
CREATE PROCEDURE p8()
BEGIN
DECLARE a INT;
DECLARE b INT;
SET a = 5;
SET b = 10;
INSERT INTO t VALUES(a);
SELECT s1 FROM t WHERE s1 >= b;
END; //
being/end中,一定要声明变量名和其类型，不能用 @修饰
没有默认子句和设定语句的例子
DELIMITER //
CREATE PROCEDUER p9()
BEGIN
DECLARE a INT; /*没有默认子句*/
DECLARE b INT; /*没有默认子句*/
SET a = 5; /*设定值*/
SET b = 10; /*设定值*/
INTER INTO t VALUES (a);
SELECT s1 FROM t WHERE s1 >= b;
END; //
有很多初始化变量的方法，如果没有默认的子句，那么变量的初始值为 NULL,你可以在任何时候使用 set 语句给变量赋值
含有 DEFAULT子句：
DELIMITER //
CREATE PROCEDURE p10()
BEGIN
DECLARE a,b INT DEFAULT 5;
INSERT INTO t VALUES (a);
SELECT s1 FROM t WHERE s1 >= b;
END; //
这里使用了 DEFAULT 子句来设定初始值，这就不需要把 DECLARE 和 SET 语句的实现分开了
Scope作用域
DELIMITER //
CREATE PROCEDURE p11()
BEGIN
DECLARE x1 CHAR(5) DEFAULT ‘outer‘;
BEGIN
DECLARE x1 CHAR(5) DEFAULT ‘inner‘;
SELECT x1;
END;
SELECT x1;
END; //
CALL p11();
句中嵌套了 begin/end,是合法的,内部的优先权高，所以先执行内部的，随后内部的变量消失，然后是外部的，所以有两个结果，一个是 inner,一个是outer;
条件式和 if-then-else
DELIMITER //
CREATE PROCEDURE p12(IN parameter1 INT)
BEGIN
DECLARE variable INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (100);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1+1;
ELSE
UPDATE t set s1 = s1+2;
END IF;
END; //
用 = 表示值是否相等及赋值
CASE 指令
DELIMITER //
CREATE PROCEDURE p13(IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
CASE variable1
WHEN 0 THEN INSERT INTO t VALUES (555);
WHEN 1 THEN INSERT INTO t VALUES (888);
ELSE INSERT INTO t VALUES (199);
END CASE;
END; //
循环语句
WHILE ... END WHILE
LOOP ... END LOOP
REPEAL ... END REPEAT
GOTO(尽量少用,和别的语言一样，结果混乱)
WHILE ... END WHILE
CREATE PROCEDURE p14()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO v VALUES (v);
SET v = v+1;
END WHILE;
END; //
以上只会返回 one row affected,因为只对最后一个 insert动作计数
REPEAT ... END REPEAT: UNTIL
DELIMITER //
CREATE PROCEDURE p15()
BEGIN
DECLARE v INT;
SET v = 0;
REPEAT
INSERT INTO t VALUES (v);
SET v = v+1;
UNTIL v >= 5 /*不能加分号，不然报错*/
END REPEAT;
END; //
其实就是 do ...while
loop ... end loop
as:
DELIMITER //
CREATE PROCEDURE p16()
BEGIN
DECLARE v INT;
SET v = 0;
label1: LOOP
INSERT INTO t VALUES (v);
SET v = v+1;
IF v >= 5 THEN
LEAVE label1; -->离开循环
END IF;
END LOOP label1;
END; //
label标号
DELIMITER //
CREATE PROCEDURE p17()
label_1: BEGIN
label_2:
WHILE 0=1
DO LEAVE label_2;
END WHILE;
label_3: REPEAT
LEAVE label_3;
UNTIL 0=0 -->不能有分号
END REPEAT;
label_4: LOOP
LEAVE label_4;
END LOOP;
END [label_1]; // label_1可选
LEAVE and Labels 跳出和标号(LEAVE语句使程序跳出复合语句)
DELIMITER //
CREATE PROCEDURE p19(parameter1 CHAR)
label_1: BEGIN
label_2: BEGIN
label_3: BEGIN
IF parameter1 IS NOT NULL THEN
IF parameter1 = ‘a‘ THEN
LEAVE label_1;
ELSE BEGIN
IF parameter1 = ‘b‘ THEN
LEAVE label_2;
ELSE
LEAVE label_3;
END IF;
END;
END IF;
END IF;
END;
END;
END; //
ITERATE 迭代
如果目标是 ITERATE语句的话，就必须用到 LEAVE语句
iterate(迭代)语句和 LEAVE语句一样也是在循环内部的循环引用，有点像C语言的 "Continue",同样它可以出现在复合语句中，引用复合语句标号， ITERATE（）意思是重新开始复合语句
DELIMITER //
CREATE PROCEDURE p20()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP /*循环标号*/
IF v = 3 THEN
SET v = v+1;
ITERATE loop_label; /*开始迭代,使循环又回到开始*/
END IF;
INSERT INTO t VALUES (v);
SET v=v+1;
IF v >= 5 THEN
LEAVE loop_label; /*跳出循环,使指令跳到最后一步*/
END IF;
END LOOP;
END; //
CALL p20();
------------------
异常处理
CREATE TABLE t2(
s1 int primary key
) engine = innodb;
CREATE TABLE t3(
s1 int primary key references t2(s1)
) engine = innodb;
一个主键表，以及一个外键表
insert into t3 values(5);
如果系统提示：error 1216(2300) Cannot add or update a child row: a foreign key ..
是因为主表没有这个值
创建一个错误日志表：
create table error_log(error_message char(100));
DELIMITER //
CREATE PROCEDURE p22(parameter1 INT)
BEGIN
DECLARE EXIT HANDLER FOR 1216 /*插入出错数字*/
INSERT INTO error_log VALUES (contact(‘time:‘, current_date, ‘xxx‘));
INSERT INTO t3 VALUES (parameter1);
END; //
DECLARE EXIT HANDLER 是用来处理异常的，如果错误1216发生了，则错误记录表中插入一行，exit意思是当动作成功提交后退出这个复合语句,此时 t3不会插入数据
声明异常处理的语法
DECLARE
{EXIT | CONTINUE}
HANDLER FOR
{error-number| {SQLSTATE error-string} | condition}
SQL statement
当程序出错后，自动触发代码，mysql允许两种处理，一是 exit处理，一种是 continue处理,它执行后，原主程序仍然继续运行,那国这个复合语句就没有了出口了
as:
DELIMITER //
CREATE PROCEDURE p23()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000‘ SET @x2 = 1; /*插入出错代码*/
SET @x=1;
INSERT INTO t VALUES (1);
SET @x=2;
INSERT INTO t VALUES (1);
SET @x=3;
END; //
开始执行时，插入为1；接着 set @x=2,此时再插入，因为 1已经存在，不能重复，所以会执行 declare语句，然后再执行 set @x=3
所以执行
select @x,@x2,则为 3,1值
----------------
自定义名称
CREATE PROCEDURE p24()
BEGIN
DECLARE `sql_1`
CONDITION FOR SQLSTATE ‘23000‘;
DECLARE EXIT HANDLER FOR
`sql_2` ROLLBACK;
START TRANSACTION;
INSERT INTO t2 VALUES(1);
INSERT INTO t2 VALUES(1);
COMMIT;
END; //
可以给SQLSTATE或者错误代码其他的名字，这样可以在处理中使用自己定义的名字
存储过程/游标/mysql 函数