1、存储过程、函数：事先经过编译并存储在数据库中的一段SQL语句的集合。

a) 存储过程参数类型：IN、OUT、INOUT；函数参数类型：IN

b) 创建、修改：

CREATE PROCEDURE sp_name ([proc_parameter[,....]])

[characteristic。。] routine_body

CREATE FUNCTION sp_name ([func_parameter[,....]])

RETURNS type

[characteristic ...] routine_body

proc_parametr： [IN|OUT|INOUT] param_name type

func_parameter: param_name type

type: any valid MYSQL data type

Characteristic:

LANGUGE SQL

//下面的body是使用sql语言编写，系统默认

|[NOT] DETERMINISTIC

//deterministic 确定的，即每次输入一样输出也一样的程序，默认非确定

|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}

//提供子程序使用数据的内在信息；contains sql：子程序不包含读或写数据的语句；no sql：子程序不包含sql语句；reads sql data：子程序包含读数据的语句；modifies sql data：子程序包含写数据的语句。

|SQL SECURITY {DEFINER | INVOKER}

//指定子程序是由创建者的许可执行还是调用者的许可来执行，默认definer，使用前授权。

|CONMMENT ‘string’

//注释信息

Routine_body:

Valid sql procedure statement or statements

ALTER {PROCEDURE | FUNCTION} sp_name[characteristic ...]

Characteristic:

{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}

|SQL SECURITY {DEFINER|INVOKER}

|COMMENT ‘string’

EG：

DELINITER $$ //修改程序结束符

CREATE PROCEDURE film_in_stock(IN p_film_id INT，IN p_store_id INT，OUT p_film_count INT)

READS SQL DATA

BRGIN

SELECT inventory_id

FROM inventory

WHERE film_id = p_film_id

AND store_id = p_store_id

AND inventory_in_stock(inventory_di)；

SELECT FOUND_ROWS() INTO p_film_count；

END $$

DELINITER ;

调用：

CALL film_in_stock(2,2，@a); //count结果返回到@a中

Select @a；

c) 删除、查看

DROP {PROCEDURE | FUNCTION}[IF EXISTS] sp_name;

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’];

SHOW CREATE {PROCEDURE | FUNCTION} sp_name；

select * form routines where ROUTINE_NAME = ‘film_in_stock’G;

2、变量的使用

a) 定义：DECLARE var_name[,...] type [DEFAULT value];(局部或全局变量)

b) 赋值：SET var_name = expr [,.....]...

3、定义条件和处理(与java中异常处理相似)

a) 定义：DECLARE condition_name CONDITION FOR condition_value

conditon_value:

SQLSTATE [VALUE]

sqlstate_vlaue | mysql_error_code

b) 处理：DECLARE handler_type HANDLER FOR condition_value[,....] sp_statement

handler_type:

CONTINUE | EXIT | UNDO

condition_value:

SQLSTATE [VALUE]

4、光标使用

a) 声明：DECLARE cursor_name CURSOR FOR select_statement

b) OPEN：OPENcursor_name

c) FETCH（提取）：FETCH cursor_name INTO var_name [,...]

d) CLOSE：CLOSE cursor_name

EG：

Delimiter $$

CREATE PROCEDURE payment_stat ()

BEGIN

DECLARE i_staff_id int;

DECLARE d_amount decimal(5,2);

DECLARE cur_payment CURSOR FOR select staff_di,amount from payment;

DECLARE EXIT HANDLER FOR NOT FOUND　CLOSE cut_payment;

Set @x1=0;

Set @x2=0;

OPEN cut_payment;

REPEAT

FETCH cut_payment INTO i_staff_id,d_amount;

if i_staff_id = 2 then

Set @x1 = @x1 + d_mount;

else

Set @x2 = @xa + d_mount;

end if;

INTIL 0 END REPEAT;

CLOSE cur_payment;

END;

$$

Delimiter ;

5、流程控制

a) IF ： if ... then ... ;[elseif ...then...;] [else ....;] end if;

b) CASE ： case when ... then ...; else ...; end case; 或 case var_name when var_value then ...; else ...; end case;

c) LOOP ： [begin_label:] LOOP statement_list END LOOP [end_label]

d) LEAVE ：从标注的流程构造中退出，通常和BEGIN。。。END或循环一起使用（leava [label(循环的标识)]）。

e) ITERATE ：必须用在循环中，跳过当前循环的剩下语句，直接进入下一循环（iterate [label(循环标识)]）。

f) REPEAT ：有条件循环语句，满足条件时推出循环。

[begin_label:]REPEAT .....UNTIL ...... END REPEAT [end_label]

g) WHILE ：满足条件则执行循环。

[begin_label:] WHILE .... DO ..... END WHILE [end_label]

ITPub博客

十、存储过程和函数 w 夏籽