MySQL创建存储过程
格式
MySQL存储过程创建的格式：CREATE PROCEDURE 过程名 ([过程参数[,...]])
[特性 ...] 过程体
这里先举个例子：
1.mysql> DELIMITER //
2.mysql> CREATE PROCEDURE proc1(OUT s int)
3. -> BEGIN
4. -> SELECT COUNT(*) INTO s FROM user;
5. -> END ;
6. -> //
7.Query OK, 0 rows affected (0.01 sec)
8.mysql> DELIMITER ;
注：
1、这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
例如:MySql默认的情况下以分号作为语句的结束,输入SQL语句,在末尾加上分号,这样MySql会执行这行代码。
我们可以改变MySql的分隔符,把分号改成 // ,输入语句mysql> delimiter //。
这时候MySql的结束语句分隔符就改变成了// ,原来的分号就不起作用了，在结束语句的时候需要加上 //,这样SQL语句才会执行。
1.mysql> select * from emp//
2.+-------+--------+-----------+------+------------+---------+---------+--------+
3.| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
4.+-------+--------+-----------+------+------------+---------+---------+--------+
5.| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
6.| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
7.| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
8.| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
9.| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
10.| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
11.| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
12.| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
13.| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
14.| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
15.| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
16.| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
17.| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
18.| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
19.+-------+--------+-----------+------+------------+---------+---------+--------+
20.14 rows in set (0.00 sec)
最后,我们把MySql的结束分隔符恢复成 ; ,这样的话,在结束语句的末尾还是用分号结束,SQL语句才会被执行。
1.mysql> delimiter ;
2.mysql> select * from emp;
3.+-------+--------+-----------+------+------------+---------+---------+--------+
4.| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
5.+-------+--------+-----------+------+------------+---------+---------+--------+
6.| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
7.| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
8.| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
9.| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
10.| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
11.| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
12.| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
13.| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
14.| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
15.| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
16.| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
17.| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
18.| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
19.| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
20.+-------+--------+-----------+------+------------+---------+---------+--------+
21.14 rows in set (0.00 sec)
2、存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用","分割开。
3、过程体的开始与结束使用BEGIN与END进行标识。
这样，我们的一个MySQL存储过程就完成了，是不是很容易呢?看不懂也没关系，接下来，我们详细的讲解。
声明分割符
其实，关于声明分割符，上面的注解已经写得很清楚，不需要多说，只是稍微要注意一点的是：如果是用MySQL的Administrator管理工具时,例如NaviCat for MySql，可以直接创建，不再需要声明。
NaviCat for MySql工具的使用:我们下载的是绿色版,解压缩之后,输入注册码就可直接使用,非常方便,使用界面如下:
我们需要建立和数据库服务器的连接.点击 文件->新建连接,填入数据库的相关信息之后,点击确定按钮。
建立好的连接如下,双击本机,打开连接,我们可以直接看到MySql中的数据库。
双击我们要使用的数据库,然后点击查询->新建查询,弹出窗口,我们可以在这个创建中编辑SQL语句,非常方便。
在编辑窗口中编辑SQL语句,然后选中该语句,点击右键->运行已选择的,即可执行选中的SQL语句,并且显示查询结果。
在NaviCat中编辑存储过程非常方便,不需要在声明分隔符,直接编辑代码,然后选中代码 ,即可执行,是不是被命令行客户端爽多了。
参数
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])。
IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值。
OUT 输出参数:该值可在存储过程内部被改变，并可返回。
INOUT 输入输出参数:调用时指定，并且可被改变和返回。
1、IN参数例子
在NaviCat中创建存储过程:
CREATE PROCEDURE PROC2(P_IN INT)
BEGIN
SELECT P_IN; -- 查询P_IN参数的值
SET P_IN = 3; -- 设置P_IN参数的值
SELECT P_IN ; -- 查询P_IN参数的值
END;
语句执行的过程如下图:
调用存储过程,查询结果：
SET @P_IN = 10; -- 设置@P_IN =10
CALL PROC2(@P_IN); -- 调用存储过程,并且传递@P_IN的参数
SELECT @P_IN; -- 查询@P_IN的结果
语句的执行过程如下图所示：
以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值。
2、OUT参数例子
创建:
CREATE PROCEDURE PROC3(OUT P_OUT INT)
BEGIN
SELECT P_OUT;
SET P_OUT = 10;
SELECT P_OUT;
END;
执行过程如下:
调用存储过程,查询结果
SET @P_OUT = 1000; -- 设置@P_OUT变量为1000
CALL PROC3(@P_OUT); -- 调用存储过程并且传递@P_OUT参数
SELECT @P_OUT; -- 查询P_OUT的结构
执行结果如下:
3、 INOUT参数例子
创建:
-- 注意:INOUT之间没有空格
CREATE PROCEDURE PROC4(INOUT P_IN_OUT INT) BEGIN
SELECT P_IN_OUT;
SET P_IN_OUT = 10;
SELECT P_IN_OUT;
END;
执行结果如下:
调用存储过程:
SET @P_IN_OUT = 50;
CALL PROC3(@P_IN_OUT);
SELECT @P_IN_OUT;
执行结果如下:
变量
1、变量定义
DECLARE variable_name [,variable_name...] datatype [DEFAULT value]；其中，datatype为MySQL的数据类型，如:int, float, date, varchar(length)
例如:
CREATE PROCEDURE PROC4()
BEGIN
-- 声明变量.注意:声明变量的语句只能写在BEGIN END之间
DECLARE SCHOOL VARCHAR(100) DEFAULT "乐学网";
DECLARE L_INT INT DEFAULT 40;
DECLARE L_NUMERIC DOUBLE(8,2) DEFAULT 9.95;
DECLARE L_DATE DATE DEFAULT '1999-12-31';
DECLARE L_DATETIME DATETIME DEFAULT '1999-12-31 23:59:59';
DECLARE L_VARCHAR VARCHAR(255) DEFAULT 'THIS WILL NOT BE PADDED';
SELECT SCHOOL ;-- 查询变量的值
SET SCHOOL = "乐学网北京北部校区",L_INT =100;-- 给变量重新赋值
SELECT SCHOOL;-- 再次查询变量
END;
2、变量赋值
SET 变量名 = 表达式值 [,variable_name = expression ...]
3、 用户变量
（1）在MySQL客户端使用用户变量
SELECT "Hello World" INTO @S;
SELECT @S;
SET @S = "Hello World";
SELECT @S;
SET @RES = 1+2+3;
SELECT @RES;
（2）在存储过程中使用用户变量
-- 声明存储过程
CREATE PROCEDURE PROC5()
BEGIN
SELECT CONCAT(@STR1,"World");
END;
-- 首先定义一个变量,然后调用存储过程
SET @STR1 = "Hello";
CALL PROC5();
执行效果如下
（3）在存储过程间传递全局范围的用户变量
-- 声明存储过程P1,在P1中定义变量@LAST_PROC;
CREATE PROCEDURE P1()
BEGIN
SET @LAST_PROC = "P1";
END;
-- 声明存储过程P2,在P2中使用P1中定义的变量@LAST_PROC
CREATE PROCEDURE P2()
BEGIN
SELECT CONCAT("最后一个存储过程是",@LAST_PROC);
END;
-- 调用存储过程P1(),P2();
CALL P1();
CALL P2();
执行结果如下:
注意：用户变量名一般以@开头
注释
MySQL存储过程可使用两种风格的注释
双模杠：--
该风格一般用于单行注释
c风格： /*一般用于多行注释*/