데이터베이스 처리 속도를 향상시키는 강력한 도구 - MySQL 저장 프로시저에 대한 자세한 설명

저장 프로시저 소개

우리가 일반적으로 사용하는 운영 데이터베이스 언어인 SQL 문은 먼저 컴파일한 후 실행 시 실행해야 하며, 저장 프로시저(Stored Procedure)는 완료를 위해 설계된 프로시저 그룹입니다. 특정 함수. 사용자는 이름을 지정하고 매개변수를 제공하여 저장 프로시저를 호출하고 실행합니다(저장 프로시저에 매개변수가 있는 경우).

저장 프로시저는 데이터베이스에 생성되고 저장되는 프로그래밍 가능한 기능입니다. 이는 SQL 문과 일부 특수 제어 구조로 구성될 수 있습니다. 저장 프로시저는 다양한 애플리케이션이나 플랫폼에서 동일한 기능을 수행하려는 경우 또는 특정 기능을 캡슐화하려는 경우에 유용합니다. 데이터베이스의 저장 프로시저는 프로그래밍의 객체 지향 접근 방식을 시뮬레이션한 것으로 볼 수 있습니다. 이를 통해 데이터에 액세스하는 방법을 제어할 수 있습니다.

저장 프로시저에는 일반적으로 다음과 같은 장점이 있습니다.

1. 저장 프로시저는 SQL 언어의 기능과 유연성을 향상시킵니다. 저장 프로시저는 흐름 제어 문을 사용하여 작성할 수 있고 유연성이 뛰어나며 복잡한 판단과 더 복잡한 작업을 완료할 수 있습니다.

2. 저장 프로시저를 사용하면 표준 구성 요소를 프로그래밍할 수 있습니다. 저장 프로시저가 생성된 후에는 저장 프로시저의 SQL 문을 다시 작성할 필요 없이 프로그램에서 여러 번 호출할 수 있습니다. 그리고 데이터베이스 전문가는 애플리케이션 소스 코드에 영향을 주지 않고 언제든지 저장 프로시저를 수정할 수 있습니다.

3. 저장 프로시저는 더 빠른 실행 속도를 얻을 수 있습니다. 작업에 많은 양의 Transaction-SQL 코드가 포함되어 있거나 여러 번 실행되는 경우 저장 프로시저는 일괄 처리보다 훨씬 빠르게 실행됩니다. 저장 프로시저가 미리 컴파일되어 있기 때문입니다. 저장 프로시저가 처음 실행되면 최적화 프로그램이 쿼리를 분석하고 최적화하며 최종적으로 실행 계획이 시스템 테이블에 저장됩니다. 일괄 Transaction-SQL 문은 실행될 때마다 컴파일하고 최적화해야 하며 속도가 상대적으로 느립니다.

4. 저장 프로시저는 네트워크 트래픽을 줄일 수 있습니다. 동일한 데이터베이스 개체에 대한 작업(예: 쿼리, 수정)의 경우 이 작업에 포함된 Transaction-SQL 문이 저장 프로시저로 구성되면 클라이언트 컴퓨터에서 저장 프로시저가 호출될 때 호출만 전송됩니다. 네트워크 문을 삭제하여 네트워크 트래픽을 크게 늘리고 네트워크 부하를 줄입니다.

5. 저장 프로시저는 보안 메커니즘으로 완벽하게 활용될 수 있습니다. 시스템 관리자는 특정 저장 프로시저의 실행 권한을 제한함으로써 해당 데이터에 대한 접근 권한을 제한하고, 승인되지 않은 사용자가 데이터에 접근하는 것을 방지하며 데이터의 보안을 확보할 수 있습니다.

저장 프로시저는 데이터베이스 저장의 중요한 기능이지만 MySQL 5.0 이전에는 저장 프로시저를 지원하지 않았기 때문에 MySQL 적용이 크게 줄었습니다. 다행스럽게도 MySQL 5.0에서는 마침내 저장 프로시저를 지원하기 시작했습니다. 이는 데이터베이스 처리 속도를 크게 향상시키고 데이터베이스 프로그래밍의 유연성도 향상시킬 수 있습니다.

저장 프로시저 생성

1. 형식

MySQL 저장 프로시저에서 생성된 형식:

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. 저장 프로시저에는 필요에 따라 입력, 출력, 입력 및 출력 매개변수가 있을 수 있습니다. 매개변수가 여러 개인 경우 유형은 int입니다. 그들을 분리하기 위해.

3. 프로세스 본문의 시작과 끝은 BEGIN과 END로 표시됩니다.

이렇게 하면 MySQL 저장 프로시저 중 하나가 완성됩니다. 이해가 안 되셔도 상관없습니다. 다음으로 자세히 설명드리겠습니다.

2. 선언 구분자

사실 선언 구분자에 관해서는 위의 주석이 매우 명확하게 작성되어 있습니다. 더 말할 필요도 없지만, 꼭 필요합니다. 예: MySQL 관리자 관리 도구를 사용하는 경우 직접 생성할 수 있으며 더 이상 선언할 필요가 없습니다.

3. 매개변수

저장 프로시저의 매개변수는 IN, OUT, INOUT의 세 가지 형식이 사용됩니다.

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

IN 입력 매개변수: 저장 프로시저 호출 시 이 매개변수의 값을 지정해야 함을 나타냅니다. 저장 프로시저 중에 수정된 이 매개변수의 값은 반환될 수 없으며 기본값입니다. 🎜>

OUT 출력 매개변수 : 저장 프로시저 내에서 값을 변경하고 반환할 수 있음

INOUT 입출력 매개변수 : 호출 시 지정하고, 변경하여 반환할 수 있음

1) 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 값에 영향을 미칩니다.

2) 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 |
+-------+

3) 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. 변수

1）变量定义

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';

2）变量赋值

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

3）用户变量

在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 ;

调用存储过程

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

查询存储过程

我们像知道一个数据库下面有那些表，我们一般采用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 数据库.存储过程名;

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

修改存储过程

ALTER PROCEDURE

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

删除存储过程

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

DROP PROCEDURE

从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 ;

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为小数位数]

注：返回类型并非均为整数，如：

默认变为整形值

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)

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

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）！