The basic question is-MySQL continues (user-defined functions & amp; stored procedures).-mysql_PHP tutorial

The basic question is-MySQL continues (user-defined functions & amp; stored procedures), and-mysql. The basic question opening feeling is-MySQL continues (custom function stored procedure), and-mysqlhi feels that the question opening is basically fixed, kessen 1, MySQL ----- user-defined functions --------- the basic question is-MySQL continues (user-defined functions & stored procedures), open question-mysql

Hi

I feel that the paper is fixed, Kesson.

1. MySQL

----- User-defined function -----

---- Basic

Two necessary conditions: parameters and return values (the two are not necessarily related, the parameters are not necessarily there, and the return value must be)

Function body: a legal SQL statement, and a simple SELECT or INSERT statement. if it is a composite structure, use the BEGIN... END statement.

---- Custom functions without parameters

Convert the current time to Chinese display. The effect is as follows:

Mysql> set names gbk;
Query OK, 0 rows affected (0.05 sec)

Mysql> SELECT DATE_FORMAT (NOW (), '% Y, % m, % D, % h: % I: % s s ');
+ -------------------------------------------------- +
| DATE_FORMAT (NOW (), '% Y % m % d % h: % I: % s s') |
+ -------------------------------------------------- +
| November 11, 2015: 39 seconds |
+ -------------------------------------------------- +
1 row in set (0.00 sec)

Write this function as the f1 () function ()

Mysql> create function f1 () returns varchar (30)
-> RETURN DATE_FORMAT (NOW (), '% y, % m, % D, % h: % I: % s s ');
Query OK, 0 rows affected (0.05 sec)

Call

Mysql> SELECT f1 ();

---- Functions with parameters

Mysql> create function f2 (num1 smallint unsigned, num2 smallint unsigned)
-> Returns float (10, 2) UNSIGNED
-> RETURN (num1 + num2 );
Query OK, 0 rows affected (0.00 sec)

Mysql> SELECT f2 (32, 33 );
+ ----------- +
| F2 (32, 33) |
+ ----------- +
| 1, 65.00 |
+ ----------- +
1 row in set (0.03 sec)

I won't explain it anymore. I can understand it all.

---- Functions with composite struct

A function with a composite structure usually requires multiple statements to be implemented. For example, in the following database, create a function to insert parameters as the new username, and return the id of the last inserted field.

Mysql> DESC test;
+ ---------- + ------------------- + ------ + ----- + --------- + ------------------ +
| Field | Type | Null | Key | Default | Extra |
+ ---------- + ------------------- + ------ + ----- + --------- + ------------------ +
| Id | tinyint (3) unsigned | NO | PRI | NULL | auto_increment |
| Username | varchar (20) | YES | NULL |
+ ---------- + ------------------- + ------ + ----- + --------- + ------------------ +

Mysql> SELECT * FROM test;
+ ---- + ---------- +
| Id | username |
+ ---- + ---------- +
| 1 | 111 |
| 2 | JOHN |
+ ---- + ---------- +

During implementation, you will find that if you write it directly, there will be two sentences with a score number, which is not suitable. change it!

Mysql> DELIMITER //

Change the end symbol //

The actual function is

Mysql> create function adduser (username VARCHAR (20 ))
-> RETURNS INT UNSIGNED
-> BEGIN
-> INSERT test (username) VALUES (username );
-> RETURN LAST_INSERT_ID ();
-> END
-> //

Call check

Mysql> SELECT adduser ('Rose ')//
+ ----------------- +
| Adduser ('Rose ') |
+ ----------------- +
| 3 |
+ ----------------- +

Of course, you can change the delimiters at this time.

Mysql> DELIMITER;
Mysql> SELECT adduser ('rose2 ');
+ ------------------ +
| Adduser ('rose2') |
+ ------------------ +
| 4 |
+ ------------------ +

---- Last Note

Generally, user-defined functions are not used and rarely used. it is good to use built-in functions.

----- MySQL stored procedure -----

---- Introduction

The general purpose is to improve the efficiency of MySQL, remove or reduce its own stored procedures

A stored procedure is defined as a set of pre-compiled SQL statements and control statements. it is stored as a name and processed as a unit (actually, it refers to a series, of course, it can also be a specific operation. operations can be merged/encapsulated into one operation. because this is in MySQL, database operations are generally stored, so they are called stored procedures)

After a stored procedure is used, only syntax check and compilation are performed for the first time. if you call the stored procedure later, the two steps will be skipped, improving the efficiency.

---

Advantages: Enhanced SQL statement functions and flexibility; faster execution speed (as shown above); reduced network traffic (that is, reduced command length );

---- Structure parsing/creation

Similar to creating a user-defined function, parameters are not the same

--- Parameters

You can assign a value to a parameter of the in out inout type.

IN indicates that the value of this parameter must be specified when the stored procedure is called and cannot be returned.

OUT indicates ~~~ Can be changed by the stored procedure and can return

INOUT indicates ~~~ It is specified during the call and can be changed and returned

--- Struct

Similar function bodies

Can be composed of any SQL statement

The composite structure must also use BEGIN... END.

Can be declared, loop, etc.

---- Stored procedure without parameters

Mysql> create procedure sp1 () select version ();
Query OK, 0 rows affected (0.00 sec)

Mysql> SELECT sp1 ();
ERROR 1305 (42000): FUNCTION test. sp1 does not exist
Mysql> CALL sp1 ();
+ ----------- +
| VERSION () |
+ ----------- +
| 5.6.17 |
+ ----------- +

CALL a stored procedure, and there are two CALL methods-with or without parentheses

---- Stored procedure with IN parameters

Deletes the stored procedure of a record by id.

Mysql> DELIMITER //
Mysql> create procedure removeUserById (IN id int unsigned)
-> BEGIN
-> Delete from test WHERE id = id;
-> END
-> //
Query OK, 0 rows affected (0.04 sec)

Mysql> DELIMITER;

Note that the id = id here, the former is the id in the table, and the latter is the passed parameter, which can be written like this (?)

Pay attention to the habit of writing DELIMITER statements at the beginning and END + BEGIN... END.

Call

Mysql> CALL removeUserById (3 );

Query OK, 4 rows affected (0.05 sec)

Note that parentheses cannot be omitted when parameters exist.

All records in the data are deleted. So averageThe process parameters should not be the same as the field names in the data table!

The modification here can only be correct after the deletion process is rebuilt. Drop procedure removeuserbyid;

---- With the IN and OUT parameters

The process is defined as: delete records of an id and return the number of remaining records

Similar to the process of writing regular expressions, consider the requirements first: two operations, return a value and pass it into a value, so two parameters, one IN and one OUT

Mysql> DELIMITER //
Mysql> create procedure removeidreturnlength (IN p_id int unsigned, OUT usernums int unsigned)
-> BEGIN
-> Delete from test WHERE id = p_id;
-> SELECT count (id) FROM test INTO usernums;
-> END
-> //
Query OK, 0 rows affected (0.02 sec)

Mysql> DELIMITER;

Call

Mysql> call removeidreturnlength (3, @ NUMS );
Query OK, 1 row affected (0.03 sec)

Mysql> SELECT @ NUMS;
+ ------- +
| @ NUMS |
+ ------- +
| 4 |
+ ------- +
1 row in set (0.00 sec)

Here @ nums is a variable

Mysql> SET @ QQ = 2;
Query OK, 0 rows affected (0.00 sec)

This variable is called a user variable. it is only valid for the current user and has the @ symbol.

---- Process with multiple OUT parameters

For example, a data table with many fields

Implementation process: delete the field of an id, return the deleted user, and return the remaining User

DELIMITER //

Create procedure removereturn2 (IN p_age smallint unsigned, OUT remove_user smallint unsigned, OUT usercount smallint unsigned)

BEGIN

Delete from test WHERE age = p_age;

SELECT ROW_COUNT () INTO REMOVE_USER;

Select count (ID) FROM test into usercount;

END

//

DELIMITER;

ROW_COUNT is a built-in function.

CALL REMOVERETURN2 (20, @ A, @ B );

SELECT @ A, @ B;

Note that the process cannot be modified after it is created. do not make any mistakes when creating the process for the first time, or be afraid of troubles.

---- Differences between stored procedures and user-defined functions

Stored Procedure functions are complex and often used for table operations. functions generally do not need to perform table operations.

~~~~ Multiple values can be returned. a function generally returns one value.

~~~~ It is generally executed independently. functions can be used as components of other SQL statements.

~~~~ Frequently used to encapsulate complex processes; rarely used functions

2. PHP and MySQL

Start learning MySQL functions commonly used in PHP tomorrow (?)

Bye

Startup (user-defined function stored procedure), question-mysql hi I feel that the paper is basically fixed, Kesson 1, MySQL ----- user-defined function ----- ---- basis...