Mysql and mysql download

Mysql and mysql download

In database operations, especially when encountering complex systems, it is inevitable that functions, user-defined functions, or stored procedures are used.

In actual projects, the fewer user-defined functions and stored procedures, the better. Because there are more things, it is also a very difficult place to maintain.

I. User-Defined Functions

1. Example

The functions provided by mysql are not mentioned in this Article. Here we mainly post the user-defined functions. There is a zTree in the front-end js plug-in. I don't know if you know it. The effect is as follows:

In the database, I usually design the data in this structure into a table.

Create table ztree (id int (11) not null PRIMARY key auto_increment, name varchar (20) not null comment 'node name', pid int (11) not null comment 'parent node id') comment 'tree table ';

Insert data:

If you get A node A and want to get the node under A (not just A subnode), it is much easier to use A custom function.

delimiter $DROP FUNCTIONIF EXISTS GetChildNodes ;
CREATE FUNCTION `GetChildNodes` (`rootId` INT) RETURNS VARCHAR (1000)BEGIN    DECLARE res VARCHAR (1000) DEFAULT '-1';     DECLARE temp VARCHAR (1000) DEFAULT CAST(rootId AS CHAR);    -- SET res = '' ;    -- SET DECLARE = CAST(rootId AS CHAR) ;    WHILE DECLARE IS NOT NULL DO        SET res = CONCAT(res, ',', temp) ;         SELECT GROUP_CONCAT(id) INTO temp FROM ztree WHERE FIND_IN_SET(pid, temp) > 0 ;            END WHILE ;     RETURN res ;    END$delimiter ;

Here, I will default the res value to-1. In this way, we can splice this result into SQL during the query, which is more convenient.

In mysql, the user-defined function call uses select. Next, let's take a look at the previous results:

SELECT GetChildNodes (2);

 

2. Syntax

One obvious difference between a user-defined function and a stored procedure is that a user-defined function has a return value and needs to be returned through return. the stored procedure has no return value. however, when a program executes a stored procedure, it can actually get a result set.

Syntax:

Create function Name (parameter type) returns parameter type

Begin

Return result;

End

1) User-Defined Function transfer is involved in different stored procedures. You do not need to specify in/out.

2) user-defined functions can be used in other SQL statements. They can be used independently or mixed into other SQL statements.

 

Ii. Stored Procedure

Since the syntax of the User-Defined function has been discussed earlier, the stored procedure syntax is used to compare

1. Syntax

Create procedure stored PROCEDURE name (IN parameter name parameter type, OUT parameter name parameter type)

Begin

End

All the parameters here are optional and can be IN/OUT.

In terms of syntax format, it is roughly the same as the framework of a custom function, but the details are different.

1) The stored procedure does not return a value, but can modify the input parameter in the OUT mode. It can be treated as a return value,

2) before the end of the stored procedure, you can add a select statement so that the program can read the result set. Therefore, more values can be returned by the stored procedure.

3) it cannot be used in other SQL statements. It can only be used separately by calling.

 

2. Example

In the previous project, I encountered a function to generate a sequential number. At that time, I used a database to generate a sequential number.

The serial number here is composed of three parts: prefix, time, and serial number.

Create a sequential number table first

Create table 'serialno' ('id' int (11) not null AUTO_INCREMENT COMMENT 'id', 'pre' varchar (10) not null comment 'number ', 'description' varchar (10) default null comment' description', 'res' varchar (20) default null comment 'sequential number (without serial number )', primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 2 default charset = utf8 COMMENT = 'sequential number table'

With this table, you can start the stored procedure.

delimiter $drop PROCEDURE if EXISTS p_GetSerialNo;CREATE PROCEDURE `p_GetSerialNo`(IN preValue VARCHAR(10), IN preDate VARCHAR(10),IN des varchar(20), in length int)BEGIN    DECLARE  t_error INT DEFAULT 0;    DECLARE  resValue VARCHAR(20) DEFAULT NULL;    -- DECLARE    --     CONTINUE HANDLER FOR SQLEXCEPTION,    --     SQLWARNING,    --     NOT FOUND    -- SET t_error = 1;        START TRANSACTION;    SELECT Res INTO resValue FROM serialno WHERE Pre=preValue;    IF resValue IS NULL  THEN        SET resValue= CONCAT(preDate, LPAD(1, length, '0'));        INSERT INTO serialno (Pre, Description, Res) VALUES (preValue, des, resValue);    ELSE        IF preDate =  (SUBSTRING(resValue,1,8) + '0') THEN            SET resValue = CAST(resValue AS SIGNED) + 1;            if preDate <> SUBSTRING(resValue,1,8) THEN                set t_error = -1;            end if;        ELSE            SET resValue= CONCAT(preDate, LPAD(1, length, '0'));        END IF;        UPDATE serialno SET Res = resValue WHERE Pre = preValue;    END IF;        #IF t_error = 1 then     IF @@error_count <> 0 | t_error <> 0 THEN        ROLLBACK;        select t_error;    ELSE         COMMIT;        SELECT CONCAT(preValue, resValue);    END IF;END $delimiter ;

Here, the parameter preValue is the prefix, And the preDate is an 8-bit date. The format is as follows: "20161227". The parameter des is a description. It is not involved in the logic here, but only updates a field. the last length field, indicating the number of bits. the number of bits cannot be set too small, depending on the business. when the serial number overflows,-1 is returned.

OK. Let's take a look at the effect:

call p_GetSerialNo( 'b', '20170101',  'b', 4);