【mysql 存储过程】数据库从菜鸟走向大神系列1

1. 关于MySQL的存储过程

存储过程是数据库存储的一个重要的功能,但是MySQL在5.0以前并不支持存储过程,这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程,这样即可以大大提高数据库的处理速度,同时也可以提高数据库编程的灵活性。

2. MySQL存储过程的创建

(1). 格式

MySQL存储过程创建的格式:CREATE PROCEDURE 过程名 ([过程参数[,…]])
[特性 …] 过程体

这里先举个例子:

mysql> DELIMITER // 
mysql> CREATE PROCEDURE proc1(OUT s int) 
-> BEGIN 
-> SELECT COUNT(*) INTO s FROM user; 
-> END 
-> // 
mysql> DELIMITER ; 

注:

(1)这里需要注意的是DELIMITER //和DELIMITER ;两句,DELIMITER是分割符的意思,因为MySQL默认以”;”为分隔符,如果我们没有声明分割符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错,所以要事先用DELIMITER关键字申明当前段分隔符,这样MySQL才会将”;”当做存储过程中的代码,不会执行这些代码,用完了之后要把分隔符还原。

(2)存储过程根据需要可能会有输入、输出、输入输出参数,这里有一个输出参数s,类型是int型,如果有多个参数用”,”分割开。
(3)过程体的开始与结束使用BEGIN与END进行标识。
这样,我们的一个MySQL存储过程就完成了,是不是很容易呢?看不懂也没关系,接下来,我们详细的讲解。**

(2). 声明分割符

其实,关于声明分割符,上面的注解已经写得很清楚,不需要多说,只是稍微要注意一点的是:如果是用MySQL的Administrator管理工具时,可以直接创建,不再需要声明。

(3). 参数

MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:

CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形…])

IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值

OUT 输出参数:该值可在存储过程内部被改变,并可返回

INOUT 输入输出参数:调用时指定,并且可被改变和返回

Ⅰ. IN参数例子

创建:

mysql > DELIMITER // 
mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int) 
-> BEGIN 
-> SELECT p_in; 
-> SET p_in=2; 
-> SELECT p_in; 
-> END; 
-> // 
mysql > DELIMITER ; 

执行结果:

mysql > SET @p_in=1; 
mysql > CALL demo_in_parameter(@p_in); 
+------+ 
| p_in | 
+------+ 
| 1 | 
+------+ 

+------+ 
| p_in | 
+------+ 
| 2 | 
+------+ 

mysql> SELECT @p_in; 
+-------+ 
| @p_in | 
+-------+ 
| 1 | 
+-------+ 

以上可以看出,p_in虽然在存储过程中被修改,但并不影响@p_id的值

Ⅱ.OUT参数例子

创建:

mysql > DELIMITER // 
mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int) 
-> BEGIN 
-> SELECT p_out; 
-> SET p_out=2; 
-> SELECT p_out; 
-> END; 
-> // 
mysql > DELIMITER ; 

执行结果:

mysql > SET @p_out=1; 
mysql > CALL sp_demo_out_parameter(@p_out); 
+-------+ 
| p_out | 
+-------+ 
| NULL | 
+-------+ 

+-------+ 
| p_out | 
+-------+ 
| 2 | 
+-------+ 

mysql> SELECT @p_out; 
+-------+ 
| p_out | 
+-------+ 
| 2 | 
+-------+ 

Ⅲ. INOUT参数例子

创建:

mysql > DELIMITER // 
mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int) 
-> BEGIN 
-> SELECT p_inout; 
-> SET p_inout=2; 
-> SELECT p_inout; 
-> END; 
-> // 
mysql > DELIMITER ; 

执行结果:

mysql > SET @p_inout=1; 
mysql > CALL demo_inout_parameter(@p_inout) ; 
+---------+ 
| p_inout | 
+---------+ 
| 1 | 
+---------+ 

+---------+ 
| p_inout | 
+---------+ 
| 2 | 
+---------+ 

mysql > SELECT @p_inout; 
+----------+ 
| @p_inout | 
+----------+ 
| 2 | 
+----------+ 

(4). 变量

Ⅰ. 变量定义

DECLARE variable_name [,variable_name…] datatype [DEFAULT value];

其中,datatype为MySQL的数据类型,如:int, float, date, varchar(length)

例如:

DECLARE l_int int unsigned default 4000000; 
DECLARE l_numeric number(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'; 

Ⅱ. 变量赋值

SET 变量名 = 表达式值 [,variable_name = expression …]

Ⅲ. 用户变量

ⅰ. 在MySQL客户端使用用户变量


mysql > SELECT 'Hello World' into @x; 
mysql > SELECT @x; 
+-------------+ 
| @x | 
+-------------+ 
| Hello World | 
+-------------+ 
mysql > SET @y='Goodbye Cruel World'; 
mysql > SELECT @y; 
+---------------------+ 
| @y | 
+---------------------+ 
| Goodbye Cruel World | 
+---------------------+ 

mysql > SET @z=1+2+3; 
mysql > SELECT @z; 
+------+ 
| @z | 
+------+ 
| 6 | 
+------+ 

ⅱ. 在存储过程中使用用户变量

mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); 
mysql > SET @greeting='Hello'; 
mysql > CALL GreetWorld( ); 
+----------------------------+ 
| CONCAT(@greeting,' World') | 
+----------------------------+ 
| Hello World | 
+----------------------------+ 

ⅲ. 在存储过程间传递全局范围的用户变量

mysql> CREATE PROCEDURE p1() SET @last_procedure='p1'; 
mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc); 
mysql> CALL p1( ); 
mysql> CALL p2( ); 
+-----------------------------------------------+ 
| CONCAT('Last procedure was ',@last_proc | 
+-----------------------------------------------+ 
| Last procedure was p1 | 
+-----------------------------------------------+ 

注意:

①用户变量名一般以@开头
②滥用用户变量会导致程序难以理解及管理

(5). 注释

MySQL存储过程可使用两种风格的注释

双模杠:–

该风格一般用于单行注释
c风格: 一般用于多行注释
例如:

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc1 --name存储过程名 
-> (IN parameter1 INTEGER) 
-> BEGIN 
-> DECLARE variable1 CHAR(10); 
-> IF parameter1 = 17 THEN 
-> SET variable1 = 'birds'; 
-> ELSE 
-> SET variable1 = 'beasts'; 
-> END IF; 
-> INSERT INTO table1 VALUES (variable1); 
-> END 
-> // 
mysql > DELIMITER ; 
  1. MySQL存储过程的调用

用call和你过程名以及一个括号,括号里面根据需要,加入参数,参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。

  1. MySQL存储过程的查询

我们像知道一个数据库下面有那些表,我们一般采用show tables;进行查看。那么我们要查看某个数据库下面的存储过程,是否也可以采用呢?答案是,我们可以查看某个数据库下面的存储过程,但是是令一钟方式。

我们可以用

select name from mysql.proc where db='数据库名'; 

或者

select routine_name from information_schema.routines where routine_schema='数据库名'; 

或者

show procedure status where db='数据库名'; 

进行查询。

如果我们想知道,某个存储过程的详细,那我们又该怎么做呢?是不是也可以像操作表一样用describe 表名进行查看呢?

答案是:我们可以查看存储过程的详细,但是需要用另一种方法:

SHOW CREATE PROCEDURE 数据库.存储过程名; 

就可以查看当前存储过程的详细。

6.MySQL存储过程的修改

ALTER PROCEDURE 

更改用CREATE PROCEDURE 建立的预先指定的存储过程,其不会影响相关存储过程或存储功能。

  1. MySQL存储过程的删除

删除一个存储过程比较简单,和删除表一样:

DROP PROCEDURE 

从MySQL的表格中删除一个或多个存储过程。

  1. MySQL存储过程的控制语句

(1). 变量作用域

内部的变量在其作用域范围内享有更高的优先权,当执行到end。变量时,内部变量消失,此时已经在其作用域外,变量不再可见了,应为在存储
过程外再也不能找到这个申明的变量,但是你可以通过out参数或者将其值指派
给会话变量来保存其值。

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc3() 
-> begin 
-> declare x1 varchar(5) default 'outer'; 
-> begin 
-> declare x1 varchar(5) default 'inner'; 
-> select x1; 
-> end; 
-> select x1; 
-> end; 
-> // 
mysql > DELIMITER ; 

(2). 条件语句

Ⅰ. if-then -else语句

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc2(IN parameter int) 
-> begin 
-> declare var int; 
-> set var=parameter+1; 
-> if var=0 then 
-> insert into t values(17); 
-> end if; 
-> if parameter=0 then 
-> update t set s1=s1+1; 
-> else 
-> update t set s1=s1+2; 
-> end if; 
-> end; 
-> // 
mysql > DELIMITER ; 

Ⅱ. case语句:

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc3 (in parameter int) 
-> begin 
-> declare var int; 
-> set var=parameter+1; 
-> case var 
-> when 0 then 
-> insert into t values(17); 
-> when 1 then 
-> insert into t values(18); 
-> else 
-> insert into t values(19); 
-> end case; 
-> end; 
-> // 
mysql > DELIMITER ; 

(3). 循环语句

Ⅰ. while ···· end while:

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc4() 
-> begin 
-> declare var int; 
-> set var=0; 
-> while var<6 do 
-> insert into t values(var); 
-> set var=var+1; 
-> end while; 
-> end; 
-> // 
mysql > DELIMITER ; 

Ⅱ. repeat···· end repeat:

它在执行操作后检查结果,而while则是执行前进行检查。

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc5 () 
-> begin 
-> declare v int; 
-> set v=0; 
-> repeat 
-> insert into t values(v); 
-> set v=v+1; 
-> until v>=5 
-> end repeat; 
-> end; 
-> // 
mysql > DELIMITER ; 

Ⅲ. loop ·····end loop:

loop循环不需要初始条件,这点和while 循环相似,同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc6 () 
-> begin 
-> declare v int; 
-> set v=0; 
-> LOOP_LABLE:loop 
-> insert into t values(v); 
-> set v=v+1; 
-> if v >=5 then 
-> leave LOOP_LABLE; 
-> end if; 
-> end loop; 
-> end; 
-> // 
mysql > DELIMITER ; 

Ⅳ. LABLES 标号:

标号可以用在begin repeat while 或者loop 语句前,语句标号只能在合法的语句前面使用。可以跳出循环,使运行指令达到复合语句的最后一步。

(4). ITERATE迭代

Ⅰ. ITERATE:

通过引用复合语句的标号,来从新开始复合语句

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc10 () 
-> begin 
-> declare v int; 
-> set v=0; 
-> LOOP_LABLE:loop 
-> if v=3 then 
-> set v=v+1; 
-> ITERATE LOOP_LABLE; 
-> end if; 
-> insert into t values(v); 
-> set v=v+1; 
-> if v>=5 then 
-> leave LOOP_LABLE; 
-> end if; 
-> end loop; 
-> end; 
-> // 
mysql > DELIMITER ; 
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页