The feeling of basic topic opening is here - MySQL continues (custom functions & stored procedures), topic opening - mysql_PHP tutorial

The basic feeling of opening the topic is - MySQL continues (custom functions & stored procedures), opening the topic - mysql

　hi

I feel like the topic of the thesis has been basically decided, Kaisen

1. MySQL

-----Custom function-----

----Basic

Two necessary conditions: parameters and return values ​​(there is no necessary connection between the two, parameters may not be present, but returns must be present)

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

----Custom function without 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 minute:%s second');
----------- ---------------------------------------
| DATE_FORMAT(NOW(),' %Y year %m month %d day %h point: %I minute: %s second') |
-------------------------- --------------------------
| November 11, 2015 07:07:39|
-- ---------------------------------------------
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 minute:%s second' );
Query OK, 0 rows affected (0.05 sec)

Call

mysql> SELECT f1();

----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 won’t explain it, everyone can understand it

----Function with composite structure

Function of compound structure often means multiple statements to be implemented. For example, in the following database, create a function to insert the parameter 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 |
---- ----------

When you implement it, you will find that if you write it directly, there will be two sentences that require semicolons. If it is inappropriate, change it!

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

Call check

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

Of course you can change the delimiter back at this time

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

----Last note

Custom functions are generally not used, they are rarely used, just use the built-in functions

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

----Introduction

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

The definition of a stored procedure is: it is a precompiled collection of SQL statements and control statements, stored with a name and processed as a unit (the actual understanding is to merge/encapsulate a series, or of course, a certain one, of operations is an operation; and because this is in MySQL, the general operation of the database becomes storage, so it is called a stored procedure)

After using stored procedures, syntax checking and compilation are only performed for the first time. Users will save these two steps when calling them in the future, which improves efficiency

---

Advantages: Enhance the functionality and flexibility of SQL statements; faster execution speed (as above); reduce network traffic (i.e. reduce the length of commands);

----Structure analysis/creation

Similar to creating a custom function, but the parameters are different

---Parameters

The parameter can be assigned a value type IN OUT INOUT

IN means that the value of this parameter must be specified when calling the stored procedure and cannot be returned

OUT means ~~~ can be changed by the stored procedure and can return

INOUT means ~~~ is specified when calling and can be changed and returned

---Structure

Similar function body

can be composed of any SQL statement

Composite structures must also use BEGIN...END

Can declare, 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 |
-----------

The stored procedure is called CALL, and there are two calling methods - with or without parentheses

----Stored procedure with IN type parameters

Stored procedure to delete records, delete 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 id=id here, the former is the id in the table, and the latter is the passed parameter, which can be written like this (?)

Also pay attention to the habits here, how to write DELIMITER at the beginning and end of BEGIN...END statement

Call

mysql> CALL removeUserById(3);

Query OK, 4 rows affected (0.05 sec)

Note that for procedures with parameters, parentheses cannot be omitted

Here, all records in the data are deleted. Therefore, generally the parameters of the 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 then rebuilding it correctly. DROP PROCEDURE REMOVEUSERBYID;

----With IN and OUT parameters

The process is defined as: delete the record of a certain id and return the number of remaining records

The process is similar to writing regular expressions, etc. First consider the requirements: two operations, return a value and pass in a value, so there are 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)

@nums here is a variable

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

This kind of variable is called a user variable, which is only valid for the current user and has the @ symbol

----Procedure with multiple OUT parameters

For example, a data table with many fields

Implementation process: delete the field of a certain 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 UNSIGNED)

BEGIN

DELETE FROM test WHERE age=p_age;

SELECT ROW_COUNT() INTO REMOVE_USER;

SELECT COUNT(ID) FROM test INTO USERCOUNT;

END

//

DELIMITER ;

Among them, ROW_COUNT is a built-in function

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

SELECT @A,@B;

It should be noted that since the process cannot be modified after it is created, try not to make mistakes the first time you create it, otherwise don’t be afraid of trouble

----The difference between stored procedures and custom functions

The stored procedure function is more complex and is often used to operate tables; functions generally do not need to operate tables

~~~~can return multiple values; functions generally return one value

~~~~ are generally executed independently; functions can appear as components of other SQL statements

~~~~ is commonly used to encapsulate complex processes; functions are rarely used

2. PHP and MySQL

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

bye

http://www.bkjia.com/PHPjc/1070040.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1070040.htmlTechArticleThe feeling of basic topic opening is - MySQL continues (custom function stored procedure), topic opening - mysql hi feeling paper The topic is basically confirmed, Kaisen 1. MySQL -----Custom function----- ----Basic...