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或者错误代码其他的名字，这样可以在处理中使用自己定义的名字

-------------

cursors游标

as:

DECLARE cursor_name CURSOR FOR SELECT ...; /*声明游标*/

OPEN cursor-name;  /*打开游标*/

FETCH cursor-name INTO variable1[,variable2]; /*从游标中读取数据*/

CLOSE cursor-name; /*关闭游标*/

事务的基本流程：声明游标，打开游标，从游标中读取，关闭游标

as:

DELIMITER //

CREATE PROCEDURE p25(OUT return_val INT)

BEGIN

DECLARE a,b INT;

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

UNTIL b=1

END REPEAT;

CLOSE cur_1;

SET return_val = a;

END; //

CALL p25(@return_val);

SELECT @return_val;

结果是返回最后的一句数据值

这个过程开始声明了三个变量，声明的顺序十分重要，先进行变量声明，然后声明条件，随后声明游标，再才时声明错误处理，如果不按以上顺序，系统会提示错误

声明游标 cur_1，使用嵌入式的 SQL

执行语句 OPEN cur_1，它是与 SELECT s1 FROM t语句关联的，过程将执行 SELECT s1 FROM t,返回一个结果集

FETCH语句会获得一行从 select 产生的结果集中检索出来的值，然而 表 t中有多行，因此这个语句会被执行多次，这是在内部循环执行的

当 fetch没有获得行时， CONTINUE处理被触发，将变量 b赋值为 1

b=1时，循环结束，这里关闭游标，也可以由系统自动关闭，最后是手动加上关闭

为输出参数指派了一个局部变量，这样在过程结束后仍然可以使用

----------

游标的特征

READ ONLY 只读属性

NOT SCROLLABLE 顺序读取

ASENSITIVE 敏感

只可以从游标中取值，不能对其进行更新， 因为游标是只读的，但可以这样

FETCH cursor1 INTO variable1;

update t1 set column1='value1' where id=variable;

游标也是不可以滚动的，只允许逐一读取下一行，不能在结果集中前进和后退

同时也不允许在已打开游标进行操作的表上执行 update事务，因为游标是敏感的 (ASENSITIVE)

查看存储过程语句：show或者 select方法

show create procedure 过程名;

或者

show procedure status like '过程名';(更详细信息)

select * from mysql.proc where name='过程名';

查看有哪些例程

select * from information_schema.columns where table_name = 'routines';

可显示相关的例程字段

查询指定数据库中的所在例程数目：

select count(*) as cc from information_schema.routines where routine_schema = '数据库名';

查询定义的语句：

select routine_definition from information_schema.routines where routine_schema = '数据库名' and specific_name = '存储名'

------------------

FUNCTIONS 函数(没有存储过程强大),可直接作为函数在 sql语句中调用即可(同时也是保存到 mysql.proc表中)

as:

DELIMITER //

CREATE FUNCTION factorial(n decimal(3,0))

returns decimal(20,0) /*不能加分号,表示返回值的类型*/

DETERMINISTIC

BEGIN

declare factorial decimal(20,0) default 1;

declare counter decimal(3,0);

set counter = n;

factorial_loop: repeat

set factorial = factorial * counter;

set counter = counter -1;

until counter = 1

end repeat;

return factorial; /*返回值*/

end; //

引用函数

insert into t values (factorial(10));

删除函数

drop function factorial;