1.MySQL存储过程的查询:
show procedure status where db=‘数据库名’;
2.查询某个存储过程的详细:
SHOW CREATE PROCEDURE 数据库.存储过程名;
例如: SHOW CREATE PROCEDURE BJPOWERNODE.P1;
3.目前,MySQL还不提供对已存在的存储过程的代码修改 如果,一定要修改存储过程的代码,必须,先将存储过程删除之后,在重新编写代码,或者创建一个新的存储过程
4.删除一个存储过程
DROP PROCEDURE [IF EXISTS] 存储过程名称
例如:DROP PROCEDURE IF EXISTS P1;
5.清空TEMP表中的记录
DELETE FROM TEMP;
mysql存储过程详解
我们现在MySql中创建一个bjpowernode的数据库,然后倒入备份表
- mysql> create database bjpowernode;
- Query OK, 1 row affected (0.00 sec)
- mysql> use bjpowernode;
- Database changed
- mysql> source d:/bjpowernode.sql;
- Query OK, 0 rows affected, 1 warning (0.00 sec)
- Query OK, 0 rows affected, 1 warning (0.00 sec)
- Query OK, 0 rows affected, 1 warning (0.02 sec)
- Query OK, 0 rows affected (0.08 sec)
- Query OK, 0 rows affected (0.05 sec)
- Query OK, 0 rows affected (0.05 sec)
- Query OK, 1 row affected (0.03 sec)
查询数据库中的表,会显示三个表,是我们刚刚导入的备份表
-
mysql> show tables;
-
±----------------------+
-
| Tables_in_bjpowernode |
-
±----------------------+
-
| dept |
-
| emp |
-
| salgrade |
-
±----------------------+
-
3 rows in set (0.00 sec)
-
存储过程简介
我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。
一个存储过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
存储过程通常有以下优点:
(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
(2).存储过程允许标准组件是编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
(4).存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织程存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。
(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
-
关于MySQL的存储过程
存储过程是数据库存储的一个重要的功能,但是MySQL在5.0以前并不支持存储过程,这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程,这样即可以大大提高数据库的处理速度,同时也可以提高数据库编程的灵活性。
3. MySQL存储过程的创建
(1). 格式
MySQL存储过程创建的格式:CREATE PROCEDURE 过程名 ([过程参数[,…]])
[特性 …] 过程体
这里先举个例子:
- mysql> DELIMITER //
- mysql> CREATE PROCEDURE proc1(OUT s int)
-
-> BEGIN
-
-> SELECT COUNT(*) INTO s FROM user;
-
-> END ;
-
-> //
- Query OK, 0 rows affected (0.01 sec)
- mysql> DELIMITER ;
注:
(1)这里需要注意的是DELIMITER //和DELIMITER ;两句,DELIMITER是分割符的意思,因为MySQL默认以";“为分隔符,如果我们没有声明分割符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错,所以要事先用DELIMITER关键字申明当前段分隔符,这样MySQL才会将”;"当做存储过程中的代码,不会执行这些代码,用完了之后要把分隔符还原。
例如:MySql默认的情况下以分号作为语句的结束,输入SQL语句,在末尾加上分号,这样MySql会执行这行代码
我们可以改变MySql的分隔符,把分号改成 // ,输入语句
mysql> delimiter //
这时候MySql的结束语句分隔符就改变成了// ,原来的分号就不起作用了
在结束语句的时候需要加上 //,这样SQL语句才会执行
- mysql> select * from emp//
- ±------±-------±----------±-----±-----------±--------±--------±-------+
- | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
- ±------±-------±----------±-----±-----------±--------±--------±-------+
- | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
- | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
- | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
- | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
- | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
- | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
- | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
- | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
- | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
- | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
- | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
- | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
- | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
- | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
- ±------±-------±----------±-----±-----------±--------±--------±-------+
- 14 rows in set (0.00 sec)
最后,我们把MySql的结束分隔符恢复成 ; ,这样的话,在结束语句的末尾还是用分号结束,SQL语句才会被执行
- mysql> delimiter ;
- mysql> select * from emp;
- ±------±-------±----------±-----±-----------±--------±--------±-------+
- | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
- ±------±-------±----------±-----±-----------±--------±--------±-------+
- | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
- | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
- | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
- | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
- | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
- | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
- | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
- | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
- | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
- | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
- | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
- | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
- | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
- | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
- ±------±-------±----------±-----±-----------±--------±--------±-------+
- 14 rows in set (0.00 sec)
(2)存储过程根据需要可能会有输入、输出、输入输出参数,这里有一个输出参数s,类型是int型,如果有多个参数用","分割开。
(3)过程体的开始与结束使用BEGIN与END进行标识。
这样,我们的一个MySQL存储过程就完成了,是不是很容易呢?看不懂也没关系,接下来,我们详细的讲解。
(2). 声明分割符
其实,关于声明分割符,上面的注解已经写得很清楚,不需要多说,只是稍微要注意一点的是:如果是用MySQL的Administrator管理工具时,例如NaviCat for MySql,可以直接创建,不再需要声明。
NaviCat for MySql工具的使用:我们下载的是绿色版,解压缩之后,输入注册码就可直接使用,非常方便,使用界面如下:
我们需要建立和数据库服务器的连接.点击 文件->新建连接,填入数据库的相关信息之后,点击确定按钮.
建立好的连接如下,双击本机,打开连接,我们可以直接看到MySql中的数据库
双击我们要使用的数据库,然后点击查询->新建查询,弹出窗口,我们可以在这个创建中编辑SQL语句,非常方便
在编辑窗口中编辑SQL语句,然后选中该语句,点击右键->运行已选择的,即可执行选中的SQL语句,并且显示查询结果
在NaviCat中编辑存储过程非常方便,不需要在声明分隔符,直接编辑代码,然后选中代码 ,即可执行,是不是被命令行客户端爽多了_
(3). 参数
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:
CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形…])
IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
OUT 输出参数:该值可在存储过程内部被改变,并可返回
INOUT 输入输出参数:调用时指定,并且可被改变和返回
Ⅰ. 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的值
Ⅱ.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的结构
执行结果如下:
Ⅲ. INOUT参数例子
创建:
– 注意:INOUT之间没有空格
CREATE PROCEDURE PROC3(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;
执行结果如下:
(4). 变量
Ⅰ. 变量定义
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;
Ⅱ. 变量赋值
SET 变量名 = 表达式值 [,variable_name = expression …]
Ⅲ. 用户变量
ⅰ. 在MySQL客户端使用用户变量
SELECT “Hello World” INTO @S;
SELECT @S;
SET @S = “Hello World”;
SELECT @S;
SET @RES = 1+2+3;
SELECT @RES;
ⅱ. 在存储过程中使用用户变量
– 声明存储过程
CREATE PROCEDURE PROC5()
BEGIN
SELECT CONCAT(@STR1,“World”);
END;
– 首先定义一个变量,然后调用存储过程
SET @STR1 = “Hello”;
CALL PROC5();
执行效果如下
ⅲ. 在存储过程间传递全局范围的用户变量
– 声明存储过程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();
执行结果如下:
注意:
用户变量名一般以@开头
(5). 注释
MySQL存储过程可使用两种风格的注释
双模杠:–
该风格一般用于单行注释
c风格: /一般用于多行注释/
例如:
-
MySQL存储过程的调用
用call和你过程名以及一个括号,括号里面根据需要,加入参数,参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。
5. MySQL存储过程的查询
我们想知道一个数据库下面有那些表,我们一般采用show tables;进行查看。那么我们要查看某个数据库下面的存储过程,是否也可以采用呢?答案是,我们可以查看某个数据库下面的存储过程,但是是另一种方式。
show procedure status where db=‘数据库名’;
具体语句:
SHOW PROCEDURE STATUS WHERE DB=“bjpowernode”;
执行结果如下:
进行查询。
如果我们想知道,某个存储过程的详细,那我们又该怎么做呢?是不是也可以像操作表一样用desc 表名进行查看呢?
答案是:我们可以查看存储过程的详细,但是需要用另一种方法:
SHOW CREATE PROCEDURE 数据库.存储过程名;
就可以查看当前存储过程的详细。
具体语句如下:
SHOW CREATE PROCEDURE BJPOWERNODE.P1;
执行结果如下:
-
MySQL存储过程的修改
目前,MySQL还不提供对已存在的存储过程的代码修改 如果,一定要修改存储过程的代码,必须,先将存储过程删除之后,在重新编写代码,或者创建一个新的存储过程
-
MySQL存储过程的删除
删除一个存储过程比较简单,和删除表一样:
DROP PROCEDURE [IF EXISTS] 存储过程名称
从MySQL的表格中删除一个或多个存储过程。
DROP PROCEDURE IF EXISTS P1;
DROP PROCEDURE IF EXISTS P2;
-
MySQL存储过程的控制语句
(1). 变量作用域
内部的变量在其作用域范围内享有更高的优先权,当执行到end时,内部变量消失,此时已经在其作用域外,变量不再可见了,因为在存储过程外再也不能找到这个声明的变量,但是我们可以通过out参数或者将其值指派给会话变量来保存其值。
– 创建存储过程
CREATE PROCEDURE PROC6()
BEGIN
DECLARE X1 VARCHAR(5) DEFAULT “OUTER”;
– ----------嵌套块-------------------------
BEGIN
DECLARE X1 VARCHAR(5) DEFAULT “INNER”;
SELECT X1; – 结果是"INNER"
END;
SELECT X1; – 结果是"OUTER"
END;
– 调用存储过程
CALL PROC6();
执行结果如下:
(2). 条件语句
Ⅰ. if-then -else语句
创建一个表
CREATE TABLE TEMP (ID INT(4));
创建存储过程
CREATE PROCEDURE PROC7(IN PARAM INT)
BEGIN
DECLARE VAR INT;
SET VAR = PARAM + 1;
– ----------IF语句---------------
– 注意:判断var是否等于1,要使用=,而不是==;
IF VAR = 1 THEN
INSERT INTO TEMP VALUES (100);
END IF;
– ----------IF ELSE语句---------------
IF PARAM = 0 THEN
UPDATE TEMP SET ID = ID + 1;
ELSE
UPDATE TEMP SET ID = ID + 2;
END IF;
END;
调用存储过程
CALL PROC7(0);
查询结果:
SELECT * FROM TEMP;
语句执行结果如下:
Ⅱ. case语句:
清空TEMP表中的记录
DELETE FROM TEMP;
创建存储过程
CREATE PROCEDURE PROC8(IN PARAM INT)
BEGIN
DECLARE VAR INT;
SET VAR = PARAM + 1;
CASE VAR
WHEN 1 THEN
INSERT INTO TEMP VALUES (1);
WHEN 2 THEN
INSERT INTO TEMP VALUES (2);
ELSE
INSERT INTO TEMP VALUES (100);
END CASE;
END;
调用存储过程
CALL PROC8(10);
查询结果:
SELECT * FROM TEMP;
语句执行结果如下
(3). 循环语句
Ⅰ. while •••• end while:
清空TEMP表中的记录
DELETE FROM TEMP;
创建存储过程
CREATE PROCEDURE PROC9()
BEGIN
DECLARE VAR INT DEFAULT 0;
WHILE VAR < 10 DO
INSERT INTO TEMP VALUES (VAR);
SET VAR = VAR + 1;
END WHILE;
END;
调用存储过程
CALL PROC9();
查询结果:
SELECT * FROM TEMP;
语句执行结果如下:
Ⅱ. repeat•••• end repeat:
它在执行操作后检查结果,而while则是执行前进行检查。
清空TEMP表中的记录
DELETE FROM TEMP;
创建存储过程
CREATE PROCEDURE PROC10()
BEGIN
DECLARE VAR INT DEFAULT 0;
REPEAT
INSERT INTO TEMP VALUES (VAR);
SET VAR = VAR + 1;
UNTIL VAR >= 10 – 末尾没有分号
END REPEAT;
END;
调用存储过程
CALL PROC10();
查询结果:
SELECT * FROM TEMP;
语句执行结果:
Ⅲ. loop •••••end loop:
loop循环不需要初始条件,这点和while 循环相似,同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。
创建存储过程
CREATE PROCEDURE PROC11()
BEGIN
DECLARE VAR INT DEFAULT 0;
LOOP1:LOOP
INSERT INTO TEMP VALUES (VAR);
SET VAR = VAR + 1;
IF VAR > 10 THEN – 循环终止条件
LEAVE LOOP1;
END IF;
END LOOP;
END;
CALL PROC11(); – 调用存储过程
SELECT * FROM TEMP; – 查询结果
语句执行结果如下: