MYSQL笔记（存储程序）

来源：互联网发布：win8无法激活windows 编辑：程序博客网时间：2024/05/21 01:29

存储程序即将SQL语句存储在服务器上，客户端无需重新发布单独语句。

遵循的语法：SQL2003

前提：需要在MYSQL数据库中有proc表，该表在安装MYSQL过程中创建

权限：

1.创建存储子程序：CREATE ROUTINE权限

2.提醒或移除子程序：ALTER ROUTINE权限

3.执行子程序：EXECUTE权限

一个子程序与特定的数据库相联系，在存储子程序内USE语句是不允许的。

创建语法

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

[characteristic ...] routine_body

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

RETURNS type

[characteristic ...] routine_body

proc_parameter:

[ IN | OUT | INOUT ] param_name type

func_parameter:

param_name type

type:

Any valid MySQL data type

characteristic:

LANGUAGE SQL

| [NOT] DETERMINISTIC

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

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

routine_body:

Valid SQL procedure statement or statements

例：

mysql> delimiter //

mysql> create procedure s_proc(OUT p INT)

-> BEGIN

-> SELECT count(*) INTO p FROM t;

-> END

-> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL s_proc(@a);

Query OK, 0 rows affected (0.03 sec)

mysql> SELECT @a;

+------+

| @a |

+------+

| 3 |

+------+

mysql> delimiter //

mysql> create function hello (s char(20)) returns char(50)

-> return concat('Helo',s,'!');

-> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> select hello ('zhaiwx1987');

+----------------------+

| hello ('zhaiwx1987') |

+----------------------+

| Helozhaiwx1987! |

+----------------------+

1 row in set (0.00 sec)

//////////////////////////////////////

BEGIN

[statement_list]

END

中间是一个或多个语句列表，每个语句使用“；”结尾，因此需要修改界定符，用delimiter命令来处理。

DECLARE被用来把不同项目局限到一个子程序中，仅用在begin...end语句内，且必须在开头

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

declare局部变量

如declare nn varchar(10) default NULL

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

declare条件

DECLARE condition_name CONDITION FOR condition_value

condition_value:

SQLSTATE [VALUE] sqlstate_value

| mysql_error_code

这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在DECLARE HANDLER语句中。

除了SQLSTATE值，也支持MySQL错误代码。

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

declare处理程序

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:

CONTINUE

| EXIT

| UNDO

condition_value:

SQLSTATE [VALUE] sqlstate_value

| condition_name

| SQLWARNING

| NOT FOUND

| SQLEXCEPTION

| mysql_error_code

这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句被执行。

对一个CONTINUE处理程序，当前子程序的执行在执行处理程序语句之后继续。对于EXIT处理程序，当前BEGIN...END复合语句的执行被终止。UNDO 处理程序类型语句还不被支持。

· SQLWARNING是对所有以01开头的SQLSTATE代码的速记。

· NOT FOUND是对所有以02开头的SQLSTATE代码的速记。

· SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

除了SQLSTATE值，MySQL错误代码被支持。

mysql> CREATE TABLE test.t (s1 int,primary key (s1));

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

mysql> CREATE PROCEDURE handlerdemo ()

-> BEGIN

-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;

-> SET @x = 1;

-> INSERT INTO test.t VALUES (1);

-> SET @x = 2;

-> INSERT INTO test.t VALUES (1);

-> SET @x = 3;

-> END;

-> //

Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()//

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//

+------+

| @x |

+------+

| 3 |

+------+

1 row in set (0.00 sec)

注意到，@x是3，这表明MySQL被执行到程序的末尾。如果DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; 这一行不在，第二个INSERT因PRIMARY KEY强制而失败之后,MySQL可能已经采取默认(EXIT)路径，并且SELECT @x可能已经返回2。

/////////////////////////////////////////

最后一个例子：使用存储程序来进行连续插入

mysql> delimiter ;

mysql> delimiter //

mysql> create procedure insert_p (IN loops INT)

-> BEGIN

-> DECLARE v1 INT;

-> SET v1 =loops;

-> WHILE v1 >0 DO

-> INSERT INTO people(last_name,first_name,gender) values(CONCAT('zz',v1),'ss','m');

-> SET v1 = v1 -1;

-> END WHILE

-> END

-> //

mysql> delimiter;

mysql> call insert_p(100);