The basic question is the feeling of-mysql continue (custom functions & stored procedures), open-mysql_php Tutorial

The basic question is the feeling of-mysql continue (custom functions & Stored Procedures), open the question-mysql

Hi

The feeling of the paper is basically confirmed, Kai Sen

1. MySQL

-----Custom Function-----

----Basic

Two prerequisites: Parameters and return values (there is no definite link between the two, parameters are not necessarily, return must have)

function Body: A valid SQL statement, and a simple select or INSERT statement, or a begin when used for a composite structure. End Statement

----custom functions with no parameters

Convert the current moment 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 year%m month%d day%h point:%i min:%s seconds ');
+--------------------------------------------------+
| Date_format (now (), '%y year%m month%d day%h point:%i min:%s sec ') |
+--------------------------------------------------+
| November 11, 2015 07 point: 07 minutes: 39 seconds |
+--------------------------------------------------+
1 row in Set (0.00 sec)

Write this function as function F1 ()

Mysql> CREATE FUNCTION F1 () RETURNS VARCHAR (30)
--RETURN Date_format (now (), '%y year%m month%d day%h point:%i min:%s seconds ');
Query OK, 0 rows affected (0.05 sec)

Call

Mysql> SELECT F1 ();

----a function 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) |
+-----------+
| 65.00 |
+-----------+
1 row in Set (0.03 sec)

I don't explain it, I can read it.

----functions with complex structures

The function of a compound structure often means that there are multiple statements to be implemented. For example, to the following database, create a function to implement the insert parameter as a new username, 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 |
+----+----------+

Implementation of the time will find that if directly written, there will be two sentences are to score number, inappropriate, change!

Mysql> DELIMITER//

Change the closing symbol to//

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
//

Invoke check

mysql> SELECT adduser (' Rose ')//
+-----------------+
| AddUser (' Rose ') |
+-----------------+
| 3 |
+-----------------+

Of course, this time you can change the delimiter.

Mysql> DELIMITER;
mysql> SELECT adduser (' Rose2 ');
+------------------+
| AddUser (' Rose2 ') |
+------------------+
| 4 |
+------------------+

----Last point explains

Custom functions are not usually used, so it is seldom used, so it is better to use the self-brought function.

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

----INTRODUCTION

The general goal is to improve the efficiency of MySQL by removing or shrinking its own stored procedures.

A stored procedure is defined as a precompiled collection of SQL statements and control statements, stored as a single name and treated as a unit (the actual understanding is that a series, of course, can be one, the operation is merged/encapsulated as an operation, and because this is in MySQL, the database operation becomes the storage, So called stored procedures)

After the use of stored procedures, only after the first grammar check and compilation, and then the user will save the two steps, efficiency improvement

---

Advantages: Enhance the function and flexibility of SQL statements, faster execution speed (as above), reduce network traffic (that is, the length of the reduced command);

----Structure parsing/creation

Similar to creating custom functions, the parameters are not quite the same

---parameters

A parameter can be assigned a value type in the Out INOUT

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

Out means ~ ~ ~ can be changed by stored procedure, and can return

InOut = ~ ~ is specified at invocation and can be changed and returned

---structural body

Similar function body

Can be an arbitrary SQL statement composition

The compound structure also has to be used to begin ... END

Can declare, cycle, etc.

----stored procedures with no 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 is called on a stored procedure, and there are two methods of calling it-with or without parentheses

----A stored procedure with an in type parameter

Delete a stored procedure for 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 here that the Id=id, the former is the ID in the table, the latter is the parameter passed, it can be so written (?). ）

Also have to pay attention to the habit here, delimiter opening end +begin ... End Statement notation

Call

Mysql> Call Removeuserbyid (3);

Query OK, 4 rows affected (0.05 sec)

Note that there are parameters to the procedure, you cannot omit the parentheses

Here, all the records in the data are deleted. So the parameters of the general process should not be the same as the field names in the data table!

The modification here can only be done by deleting the process and rebuilding the correct one. DROP PROCEDURE Removeuserbyid;

----with in and out parameters

Procedure is defined as deleting a record of an ID, returning the remaining number of records

Similar to the process of writing regular expressions, consider requirements first: two operations, return a value, pass in a value, so two parameters, one in, 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 to 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)

The @nums here is the variable

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

This variable is called a user variable and is valid only for the current user with the @ symbol

----A procedure with multiple out parameters

Like a data table with a lot of fields.

Implementation process: Delete The field of an ID, return the deleted user, and return the remaining users

DELIMITER//

CREATE PROCEDURE removereturn2 (in P_age SMALLINT unsigned,out remove_user SMALLINT unsigned,out usercount SMALLINT unsign ED)

BEGIN

DELETE from Test WHERE age=p_age;

SELECT Row_count () into Remove_user;

SELECT COUNT (ID) from Test to UserCount;

END

//

DELIMITER;

Where Row_count is a self-contained function

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

SELECT @a,@b;

Note that since the creation of the process can not be modified, the first time to create as far as possible not wrong, or not afraid of trouble

----The difference between a stored procedure and a custom function

Stored procedures are more complex and often used to manipulate tables; functions generally do not operate on tables

~ ~ ~ can return multiple values; A function typically returns a value

~ ~ ~ generally independent to execute; functions can appear as part of other SQL statements

~ ~ ~ commonly used to encapsulate complex processes; functions are seldom used

2. PHP and MySQL

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

Bye

http://www.bkjia.com/PHPjc/1070040.html www.bkjia.com true http://www.bkjia.com/PHPjc/1070040.html techarticle Basic Open the feeling is the-mysql continue (custom function stored procedure), open question-mysql hi feeling thesis open question Basic determination, Kai Sen 1, MySQL-----custom Function---------base ...

