mysql 存储过程

有三种类型的数据库存储程序

存储过程

    能够接受输入输出参数，并能够在请求时执行的程序单元。

存储函数

    与存储过程相像，但是执行结果会返回一个结果。可以被用来充当标准的sql语句

触发器

 

与sql集成

 

HelloWorld

创建helloworld.sql文件内容如下：

DELIMITER$$

DROP PROCEDURE IF EXISTS HelloWorld$$

CREATE PROCEDURE HelloWorld()

BEGIN

   SELECT "HELLO WORLD";

END$$

用DELIMITER命令使结束符变为$$，因为存储过程中多出用到分号。

CREATE PROCEDURE表示创建一个存储过程的开始，所有存储过程必须至少用一个BEGIN-END来标记存储过程的开始和结束。

调用

Source e:\stored procedure\helloworld.sql 导入新创建的存储过程。

CALL HelloWorld()$$ 调用我们创建的存储过程。

DELIMITER；恢复原来分号作为结束符。

 

变量

本地变量可用DECLARE来声明并可以用mysql内建的任何数据类型。可用DEFAULT给变量一个初始值，并可用SET给变量赋一个新值。

mysql中数据类型的简单介绍：

INT型

Mysql中支持的5个数据类型分别为TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,可分别存储不同大小的数值。UNSIGNED关键字表示只保留正值，因而在存储是可以利用节省下的符号位来扩大能存储值的范围。

FOLAT，DOUBGLE，DECIMAL类型

FOLAT(7,3)表示显示的值不会超过7位，小数点后带有三位数字。对于小数点后超过规定的位数的情况mysql会自动将值四舍五入为最接近的数然后保存。

DECIMAL类型用于存储精度要求非常高的计算中，这种类型允许指定数值的精度和计数方法作为选择参数，精度指这个数值保留的有效位总个数，计数方法指小数点后的位数。忽略时mysql会默认将精度设为10，计数方法设为0.

字符串类型

    CHAR类型用于存储定长字符串，并且必须在一个圆括号内定义其长度。长度范围为（0-255）比指定长度大的值将截短，比指定长度小的值将用空格补充。这里括号内的数值指字符所以英文和汉字在这里是同等对待的。

VARCHAR变长字符串，可以根据实际内容动态改变存储值的长度

TEXT BLOB

对于字段长度超过255的情况，Mysql提供了TEXT BLOB两种类型

DATA TIME YEAR

    存储简单的日期值

DATEYIME 和 TIMESTAMP 类型

他们可以把日期和事件当做单个值进行存储

参数

存储过程可以接受参数，将参数放到紧跟存储过程名的括号内如下：

DELIMITER $$

DROP PROCEDURE IF EXISTS mysqr$$

CREATE PROCEDURE mysqr(input_num INT)/*input_num为变量名，INT变量类型*/

BEGIN

  DECLARE f FLOAT; /*声明变量*/

  SET f=SQRT(input_num); /*设置变量值*/

  SELECT f;

END$$

DELIMITER ;

运行结果如下：

 

数据类型还有可选的输入输出模式，有效的模式包括IN（只读），INOUT（可读可写），OUT（可写）其中IN做为缺省的参数模式，因而没有出现在上例中，详解如下：

IN：参数可被传入存储程序内部，但是对于该参数的任何修改都不会返回给调用它的程序。

OUT：参数可以被赋值，并且这个修改的值会返回给调用它的程序。

INOUT：参数可被传入程序内部，并且任何对该参数的修改对调用它的程序而言都是可见的。

对上例做如下修改：

DELIMITER $$

DROP PROCEDURE IF EXISTS mysqr$$

CREATE PROCEDURE mysqr(input_num INT,OUT out_num FLOAT)

BEGIN

  SET out_num=SQRT(input_num);

  SELECT out_num;

END$$

DELIMITER ;

运行如下：

 

条件执行

可以用IF或CASE来控制存储程序的执行流程。例：

DELIMITER $$

DROP PROCEDURE IF EXISTS discount $$

CREATE PROCEDURE discount(org DOUBLE(8,2),OUT aft DOUBLE(8,2))

BEGIN

    IF(org>500)THEN

      SET aft=org*.8;

    ELSEIF(org>100)THEN

      SET aft=org*.9;

    ELSE

      SET aft=org;

    END IF ;

END

DELIMITER ;

运行结果：

 

循环

Mysql存储程序语言提供了三种类型的循环。

1.LOOP和END LOOP简单循环

2.WHILE和END WHILE

3.REPEAT和UNTIL

以上三种循环均可用LEAVE退出循环，用IETRATE跳过本循环遗留下的语句，重新从循环头部执行。例：

DELIMITER $$

DROP PROCEDURE IF EXISTS simple_loop$$

CREATE PROCEDURE simple_loop()

BEGIN

    DECLARE counter INT DEFAULT 0;

    my_loop:LOOP

     SET counter=counter+1;

     IF counter>10 THEN

       LEAVE my_loop;

     END IF;

    END LOOP my_loop;

    SELECT "I'm out!";

END$$

DELIMITER ;

运行结果：

 

和数据库交互

1.将一个sql返回的单个数据放到本地变量中。

2.创建一个“游标”迭代记录sql返回的结果集。

3.执行一个sql，将结果集返回给调用它的程序。

4.内嵌一个不返回结果的sql，如INSERT UPDATE DELETE等

存储函数

与存储过程的区别有以下几点：

1.  其参数模式只能为IN，不能为OUT INOUT

2.  函数必须返回一个值，返回值类型在函数开头。

3.  函数能被sql语句调用。

4.  函数可能不返回任何结果集。

存储程序的块结构：

每个存储程序都由一个或多个块构成（唯一的例外就是只有单个语句构成的存储过程），每个块都有BEGIN开始END结束，在一个块内可以定义不能被外面块访问的变量。

定义游标：

游标的声明必须在所有变量声明之后。

DECLARE cursor_name CURSOR FOR SELECT_statment;

游标语句：

OPEN cursor_name;/* 初始化游标结果集，必须在从游标中获取结果之前打开游标。*/

FETCH cursor_name INTO variable list;/*获取游标中的下一条结果，并将指针下移*/

CLOSE cursos_name;/*结束游标，并释放游标所占用的内存*/

从游标中获取单条记录：

OPEN cursor1

  FETCH cursor1 INTO variable1,variable2;

CLOSE cursor1;

获取所有记录集：

简单游标循环：

DECLAR CONTINUE HANDLER FOR NOT FOUND SET 1_last_row_fetched=1;

SET 1_last_row_fetched=0;

OPEN cursor1;

    cursor_loop:LOOP

       FETCH cursor1 INTO variable1,variable2;

       IF 1_last_row_fetched=1 THEN

          LEAVE cursor_loop;

       END IF;

    END LOOP cursor_loop;

CLOSE cursor1;

SET 1_last_row_fetched=0;

 

完整：

DELIMITER $$

DROP PROCEDURE IF EXISTS simple_cursor$$

CREATE PROCEDURE simple_cursor()

BEGIN

       DECLARE no_more INT DEFAULT 0;

       DECLARE user_id BIGINT;

    DECLARE id_cursor CURSOR FOR

    select id from user;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more=1;

    OPEN id_cursor;

        dept_loop:REPEAT

                    FETCH id_cursor INTO user_id;

            IF no_more THEN

            LEAVE dept_loop;

            END IF;

        select user_id;

        UNTIL no_more

        END REPEAT dept_loop;

    CLOSE id_cursor;

     SET no_more=0;

END$$

DELIMITER ;