집 > 기사 > 데이터 베이스 > MySQL 사용자 정의 기능 및 저장 프로시저에 대한 자세한 소개(코드 포함)

MySQL 사용자 정의 기능 및 저장 프로시저에 대한 자세한 소개(코드 포함)

不言앞으로: 2019-04-15 11:48:392113검색

이 기사는 MySQL 사용자 정의 기능 및 저장 프로시저(코드 포함)에 대한 자세한 소개를 제공합니다. 이는 특정 참조 가치가 있으므로 도움이 될 수 있습니다.

1. 전제 조건

MySQL 데이터베이스에는 user_info 테이블의 구조와 데이터가 다음과 같습니다.

mysql> desc  user_info;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| id        | int(10)  | NO   | PRI | NULL    |       |
| name      | char(20) | NO   |     | NULL    |       |
| passwd    | char(40) | NO   |     | NULL    |       |
| email     | char(20) | NO   |     | NULL    |       |
| phone     | char(20) | NO   |     | NULL    |       |
| role      | char(10) | NO   |     | NULL    |       |
| sex       | char(10) | NO   |     | NULL    |       |
| status    | int(10)  | NO   |     | NULL    |       |
| createAt  | datetime | NO   |     | NULL    |       |
| exprAt    | datetime | NO   |     | NULL    |       |
| validDays | int(10)  | NO   |     | NULL    |       |
| delAt     | datetime | YES  |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
12 rows in set (0.10 sec)

mysql> select  * from  user_info;
+----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+
| id | name         | passwd   | email      | phone       | role   | sex  | status | createAt            | exprAt              | validDays | delAt |
+----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+
|  1 | StephenWang7 | py123456 | 123@qq.com | 15103887470 | admin  | male |    200 | 2019-04-12 20:11:30 | 2019-04-19 20:11:30 |        30 | NULL  |
|  2 | StephenWang8 | 123456   | 123@qq.com | 15103887470 | viewer | male |    200 | 2019-04-12 20:11:30 | 2019-04-19 20:11:30 |        30 | NULL  |
+----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+
2 rows in set (0.00 sec)

2. 함수: 특정 기능을 완성할 수 있는 SQL 집합입니다. MySQL은 특정 비즈니스 기능을 완료하기 위해 사용자 정의 기능을 지원합니다.

사용자 정의 함수(사용자 정의 함수(줄여서 UDF))를 생성하는 구문은 다음과 같습니다.

create function <函数名称> （[参数1] [类型1], [参数N] [类型N]）
returns <类型>
return 
<函数主体>

UDF를 호출하는 구문은 다음과 같습니다.

select  <函数名称> ([参数])

매개변수 없이 UDF 생성

예 1: 쿼리 방법 user_info 테이블에 많은 레코드가 있습니다

#定义函数
mysql> create function  user_info_count()
    -> returns int(10)
    -> return
    -> (select  count(*) from user_info);

user_info_count()

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

함수를 호출하여 매개변수가 있는 UDF를 생성하세요

예 2: ID를 기준으로 사용자 이름을 쿼리합니다.

#定义函数
mysql> create function  queryNameById(uid int(10))
    -> returns char(20)
    -> return
    -> (select  name  from   user_info  where id=uid);
Query OK, 0 rows affected (0.01 sec)

ID가 1인 사용자 이름을 쿼리하는 함수를 호출하세요.

mysql> select  queryNameById(1);
+------------------+
| queryNameById(1) |
+------------------+
| StephenWang7     |
+------------------+
1 row in set (0.00 sec)

View UDF

시스템의 모든 UDF 쿼리

show function status;

지정된 UDF 쿼리

#
show   create function   函数名称;
mysql> show  function   queryNameById;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;queryNameById&#39; at line 1
mysql> show  function   queryNameById();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;queryNameById()&#39; at line 1
mysql> show   create function   queryNameById();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;()&#39; at line 1
mysql> show   create function   queryNameById;
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function      | sql_mode                                                                                                                                  | Create Function                                                                                                                                               | character_set_client | collation_connection | Database Collation |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| queryNameById | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `queryNameById`(uid int(10)) RETURNS char(20) CHARSET latin1
return (select  name  from   user_info  where id=uid) | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec

Modify UDF

함수 내용을 수정하려면 먼저 삭제한 후 다시 생성하세요.

Delete UDF

UDF 삭제 구문은 다음과 같습니다.

drop function  <函数名称>;

예제 3: queryNameId 함수를 삭제한 후 다시 호출하여 현상을 관찰합니다.

mysql> drop  function queryNameById;
Query OK, 0 rows affected (0.45 sec)

mysql> select queryNameById(1);
ERROR 1305 (42000): FUNCTION rms.queryNameById does not exist
mysql>

3. 저장 프로시저

저장 기능은 특정 기능을 완성하는 SQL 문 집합이기도 합니다. 복잡하거나 자주 호출되는 SQL을 미리 작성하고 이름을 지정합니다. 사용하고 싶을 때는 직접 전화하면 됩니다.

저장 프로시저를 정의하는 구문은 다음과 같습니다.

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>
#语法定义来自：http://c.biancheng.net/view/2593.html

매개 변수 없이 저장 프로시저 만들기

예 4: 사용자 이름을 쿼리합니다.

mysql> DELIMITER //
mysql> craete procedure queryName()
    -> begin
    -> select  name from user_info;
    -> end //

DELIMITER 명령과 관련하여 MySQL end 명령의 문자를 수정합니다. 기본 종료 명령 문자는 세미콜론입니다. 저장 프로시저에 여러 문이 포함되어 있는 경우 처음 발견되는 세미콜론은 저장 프로시저의 끝을 나타내는 기호로 사용됩니다. 이는 예상한 것과 다르므로 기본 종료 명령 문자를 수정해야 합니다. DELIMITER //끝 명령 문자를 //로 변경하면 됩니다. 저장 프로시저를 호출하는 명령은 호출 저장 프로시저 이름입니다.

#此时的命令的结束符号为// 不是;
mysql> call  queryName()//
+--------------+
| name         |
+--------------+
| StephenWang7 |
| StephenWang8 |
+--------------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

매개변수를 사용하여 저장 프로시저 만들기

예 5: ID를 기반으로 한 쿼리 이름.

mysql> create procedure  queryNameById
    -> (In uid int(15))
    -> begin
    -> select  name  from  user_info where id=uid;
    -> end
    -> //
Query OK, 0 rows affected (0.03 sec)

저장 프로시저 queryNameById 호출

mysql> call queryNameById(1);
    -> //
+--------------+
| name         |
+--------------+
| StephenWang7 |
+--------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

저장 프로시저 수정

저장 프로시저의 내용을 생성하려면 저장 프로시저를 삭제한 후 다시 생성하면 됩니다.

저장 프로시저 보기

show create procedure <过程名称>

mysql> show create procedure queryNameById; -> // +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | queryNameById | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `queryNameById`(In uid int(15)) begin select name from user_info where id=uid; end | utf8 | utf8_general_ci | latin1_swedish_ci | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1 row in set (0.04 sec)

저장 프로시저 삭제

drop procedure <过程名称>

저장 프로시저 삭제 queryNameById

mysql> drop procedure  queryNameById//
Query OK, 0 rows affected (0.02 sec)


mysql> call  queryNameById(1)//
ERROR 1305 (42000): PROCEDURE rms.queryNameById does not exist

4. 요약

사용자 정의 함수와 저장 프로시저 모두 특정 기능을 완성하는 SQL 컬렉션인데, 둘의 차이점은 무엇인가요?

a. 호출 방법이 다릅니다

#自定义函数
select  <函数名>
#存储过程
call <存储过程名>

b. 사용자 정의 함수는 출력 매개변수를 가질 수 없지만 저장 프로시저는 가능합니다.

c. 사용자 정의 함수에는 return 문이 포함되어야 하지만 저장 프로시저는 포함되지 않습니다.

【관련 권장 사항:

MySQL 튜토리얼

】

위 내용은 MySQL 사용자 정의 기능 및 저장 프로시저에 대한 자세한 소개(코드 포함)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명：

이 기사는 segmentfault.com에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제

이전 기사：MySql의 트랜잭션 격리 수준에 대한 자세한 소개(코드 포함)다음 기사：MySql의 트랜잭션 격리 수준에 대한 자세한 소개(코드 포함)