Gespeicherte Prozeduren der MySQL-Serie (7).

1. Einführung in gespeicherte Prozeduren

Unsere häufig verwendeten SQL-Anweisungen in der Betriebsdatenbank müssen zuerst kompiliert und dann ausgeführt werden, wenn sie ausgeführt werden. Die gespeicherte Prozedur (gespeicherte Prozedur) ist eine Eine Reihe von SQL-Anweisungen zum Ausführen einer bestimmten Funktion werden kompiliert und in der Datenbank gespeichert. Der Benutzer ruft sie auf und führt sie aus, indem er den Namen der gespeicherten Prozedur angibt und Parameter angibt (sofern die gespeicherte Prozedur über Parameter verfügt).

Eine gespeicherte Prozedur ist eine programmierbare Funktion, die erstellt und in der Datenbank gespeichert wird. Es kann aus SQL-Anweisungen und einigen speziellen Kontrollstrukturen bestehen. Gespeicherte Prozeduren sind nützlich, wenn Sie dieselbe Funktion auf verschiedenen Anwendungen oder Plattformen ausführen oder wenn Sie bestimmte Funktionen kapseln möchten. Gespeicherte Prozeduren in einer Datenbank können als Simulation des objektorientierten Ansatzes in der Programmierung angesehen werden. Es ermöglicht die Kontrolle darüber, wie auf Daten zugegriffen wird.

Gespeicherte Prozeduren haben normalerweise die folgenden Vorteile:

(1). Gespeicherte Prozeduren verbessern die Funktionalität und Flexibilität der SQL-Sprache. Gespeicherte Prozeduren können mithilfe von Flusskontrollanweisungen geschrieben werden, sind äußerst flexibel und können komplexe Beurteilungen und komplexere Operationen durchführen.

(2). Gespeicherte Prozeduren ermöglichen die Programmierung von Standardkomponenten. Nachdem eine gespeicherte Prozedur erstellt wurde, kann sie im Programm mehrmals aufgerufen werden, ohne dass die SQL-Anweisung der gespeicherten Prozedur neu geschrieben werden muss. Und Datenbankexperten können gespeicherte Prozeduren jederzeit ändern, ohne den Quellcode der Anwendung zu beeinträchtigen.

(3) Gespeicherte Prozeduren können eine schnellere Ausführungsgeschwindigkeit erreichen. Wenn eine Operation eine große Menge an Transaction-SQL-Code enthält oder mehrmals ausgeführt wird, wird die gespeicherte Prozedur viel schneller ausgeführt als die Stapelverarbeitung. Weil gespeicherte Prozeduren vorkompiliert sind. Wenn eine gespeicherte Prozedur zum ersten Mal ausgeführt wird, wird die Abfrage vom Optimierer analysiert und optimiert und schließlich wird ein Ausführungsplan in der Systemtabelle gespeichert. Die Batch-Transaction-SQL-Anweisung muss bei jeder Ausführung kompiliert und optimiert werden, und die Geschwindigkeit ist relativ langsamer.

(4) Gespeicherte Prozeduren können den Netzwerkverkehr reduzieren. Wenn bei Vorgängen für dasselbe Datenbankobjekt (z. B. Abfrage, Änderung) die an diesem Vorgang beteiligte Transaction-SQL-Anweisung in einer gespeicherten Prozedur organisiert ist, wird beim Aufruf der gespeicherten Prozedur auf dem Clientcomputer nur der Aufruf übertragen die Netzwerkanweisungen, wodurch der Netzwerkverkehr erheblich erhöht und die Netzwerklast verringert wird.

(5) Gespeicherte Prozeduren können vollständig als Sicherheitsmechanismus genutzt werden. Durch die Einschränkung der Berechtigungen zum Ausführen einer bestimmten gespeicherten Prozedur kann der Systemadministrator die Zugriffsberechtigungen für die entsprechenden Daten einschränken, den Zugriff unbefugter Benutzer auf die Daten verhindern und die Sicherheit der Daten gewährleisten.

2. Über die gespeicherten Prozeduren von MySQL

Gespeicherte Prozeduren sind eine wichtige Funktion der Datenbankspeicherung, aber MySQL unterstützte gespeicherte Prozeduren vor 5.0 nicht, was MySQL große Rabatte verschaffte Anwendungen. Glücklicherweise hat MySQL 5.0 endlich damit begonnen, gespeicherte Prozeduren zu unterstützen, was die Verarbeitungsgeschwindigkeit der Datenbank erheblich verbessern und auch die Flexibilität der Datenbankprogrammierung verbessern kann.

3. Erstellung gespeicherter MySQL-Prozeduren

(1). Format

Format der Erstellung gespeicherter MySQL-Prozeduren: CREATE PROCEDURE Prozessname ([prozedur Parameter[,...]])

[Merkmale...] Prozesskörper

Hier ist ein Beispiel:

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

Hinweis :

(1) Was hier beachtet werden muss, ist DELIMITER // und DELIMITER; in zwei Sätzen bedeutet DELIMITER das Trennzeichen, da MySQL standardmäßig „;“ verwendet TrennzeichenDann behandelt der Compiler die gespeicherte Prozedur als SQL-Anweisung und der Kompilierungsprozess der gespeicherten Prozedur meldet einen Fehler. Sie müssen daher das Schlüsselwort DELIMITER verwenden, um das aktuelle Segmenttrennzeichen im Voraus zu deklarieren, damit MySQL es behandelt. ;“ als Code in der gespeicherten Prozedur. Diese Codes werden nicht ausgeführt und die Trennzeichen müssen nach der Verwendung wiederhergestellt werden.

(2) Die gespeicherte Prozedur kann je nach Bedarf Eingabe-, Ausgabe-, Eingabe- und Ausgabeparameter haben. Es gibt einen Ausgabeparameter s, der Typ ist int, verwenden Sie ",", um sie zu trennen .

(3) Der Anfang und das Ende des Prozesskörpers werden mit BEGIN und END markiert.

Auf diese Weise ist eine unserer gespeicherten MySQL-Prozeduren nicht einfach? Es macht nichts, wenn Sie sie nicht verstehen.

(2). Deklarationstrennzeichen

Tatsächlich wurde die obige Anmerkung in Bezug auf das Deklarationstrennzeichen sehr klar geschrieben und nicht mehr Beachten Sie jedoch, dass Sie es direkt erstellen können und es nicht mehr deklarieren müssen, wenn Sie das Administrator-Verwaltungstool von MySQL verwenden.

(3) Die Parameter gespeicherter MySQL-Prozeduren werden bei der Definition gespeicherter Prozeduren verwendet. Es gibt drei Parametertypen: IN, OUT, INOUT. in der Form:

CREATE PROCEDURE([[IN |OUT |INOUT ] Parametername Datentyp...])

IN Eingabeparameter: gibt an, dass der Wert dieses Parameters angegeben werden muss Beim Aufruf der gespeicherten Prozedur kann der Wert dieses Parameters, der während der gespeicherten Prozedur geändert wurde, nicht zurückgegeben werden und ist der Standardwert

OUT-Ausgabeparameter: Der Wert kann innerhalb der gespeicherten Prozedur geändert und zurückgegeben werden

INOUT-Eingabe- und Ausgabeparameter: Wird beim Aufruf angegeben und kann geändert und zurückgegeben werden

Ⅰ IN-Parameterbeispiel

Erstellung:

Ausführungsergebnis:

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 ;

 

4.      MySQL存储过程的调用

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

5.      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 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

 

7.      MySQL存储过程的删除

删除一个存储过程比较简单，和删除表一样：

DROP PROCEDURE

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

 

8.      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 ;

 

 

9.      MySQL存储过程的基本函数

 

(1).字符串类

CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,

注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1 

mysql> select substring('abcd',0,2);  
+-----------------------+  
| substring('abcd',0,2) |  
+-----------------------+  
|                       |  
+-----------------------+  
1 row in set (0.00 sec)  
 
mysql> select substring('abcd',1,2);  
+-----------------------+  
| substring('abcd',1,2) |  
+-----------------------+  
|     ab                |  
+-----------------------+  
1 row in set (0.02 sec)  
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) 
//去除指定位置的指定字符UCASE (string2 ) 
//转换成大写RIGHT(string2,length) 
//取string2最后length个字符SPACE(count)
 //生成count个空格

(2).数学类

ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]

注：返回类型并非均为整数，如：
(1)默认变为整形值

mysql> select round(1.23);  
+-------------+  
| round(1.23) |  
+-------------+  
|           1 |  
+-------------+  
1 row in set (0.00 sec)  
 
mysql> select round(1.56);  
+-------------+  
| round(1.56) |  
+-------------+  
|           2 |  
+-------------+  
1 row in set (0.00 sec)

(2)可以设定小数位数，返回浮点型数据

mysql> select round(1.567,2);  
+----------------+  
| round(1.567,2) |  
+----------------+  
|           1.57 |  
+----------------+  
1 row in set (0.00 sec) 
SIGN (number2 ) //

 

(3).日期时间类

ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符号,正负或0
SQRT(number2) //开平方

以上就是 Mysql系列（七）存储过程的内容，更多相关内容请关注PHP中文网（www.php.cn）！