How to use mysql procedure function

1. Basic knowledge of MySQL procedure functions

1. MySQL procedure

MySQL procedure consists of multiple SQL statements Composed, receives parameters and performs specific operations on the input parameters. MySQL procedure is a method of encapsulating multiple SQL statements to achieve specific functions.

The MySQL process has the following characteristics:

(1) The MySQL process is executed on the server side.

(2) MySQL process can receive input parameters and return output parameters.

(3) MySQL procedures can create and update tables, insert and update data in the database.

(4) MySQL procedures can contain control flow statements, loops, branches and exception handlers.

(5) MySQL process supports user-defined functions and sub-processes.

2. MySQL function

MySQL function is a set of single query statements defined in MySQL. They accept one or more input parameters and return one or more values. Functions can be used to calculate, compare, transform, and manipulate data.

MySQL functions have the following characteristics:

(1) MySQL functions are executed on the server side.

(2) MySQL function can receive input parameters and return output parameters.

(3) The MySQL function can only return a single value and cannot perform operations such as insert, update, and delete.

(4) MySQL functions cannot contain control flow statements, loops, branches and exception handlers.

(5)MySQL function supports user-defined functions and sub-functions.

2. How to use MySQL process functions

In MySQL, users can use the CREATE PROCEDURE and CREATE FUNCTION statements to create procedures and functions.

1. Create MySQL procedure

The following is the syntax to create a MySQL procedure:

CREATE PROCEDURE procedure_name ([parameters])
BEGIN

  [SQL statements]

END;

Among them, procedure_name is the name of the procedure, parameters is the parameter list of the procedure, and SQL statements are the MySQL statements to be executed during the procedure.

For example, we create a simple MySQL procedure that receives two input parameters and returns their sum:

CREATE PROCEDURE add(IN a INT, IN b INT)
BEGIN

  SELECT a+b;

END;

2. Create MySQL function

The following is the syntax for creating a MySQL function:

CREATE FUNCTION function_name([parameters]) RETURNS data_type
BEGIN

  [SQL statements]

END;

Among them, function_name is the name of the function, parameters is the parameter list of the function, and data_type is what the function returns Data type, SQL statements are the MySQL statements to be executed in the function.

For example, we create a simple MySQL function that takes an input parameter and returns its square:

CREATE FUNCTION square(x INT) RETURNS INT
BEGIN

  RETURN x*x;

END;

3. Examples of MySQL process functions

1. MySQL process examples

The following is a simple An example of a MySQL procedure that outputs each table name and row count in the current database to the console:

CREATE PROCEDURE table_count()
BEGIN

  DECLARE done INT DEFAULT FALSE;
  DECLARE t_name CHAR(32);
  DECLARE t_count INT;
  DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE();
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur;
  repeat
        FETCH cur INTO t_name;
        IF NOT done THEN
              SET @sql = CONCAT('SELECT COUNT(*) INTO "', t_name, '_count" FROM ', t_name);
              PREPARE stmt FROM @sql;
              EXECUTE stmt;
              DEALLOCATE PREPARE stmt;
              SET @out = CONCAT(t_name, ': ', t_name, '_count');
              SELECT @out;
        END IF;
  until done END REPEAT;
  CLOSE cur;

END;

In the above process, we use cursors to traverse all tables in the database, and use dynamic SQL to get the number of rows in each table and output it on the console.

2. MySQL function example

The following is a simple MySQL function example that receives a string and returns the first word of each word in it Letter:

CREATE FUNCTION get_initials(str VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN

  DECLARE len INT DEFAULT 0;
  DECLARE i INT DEFAULT 0;
  DECLARE initial CHAR(1);
  DECLARE initials VARCHAR(255) DEFAULT '';
  SET len = LENGTH(str);
  WHILE i<=len DO
        SET initial = SUBSTRING(str, i, 1);
        IF i = 1 OR SUBSTRING(str, i-1, 1) = &#39; &#39; THEN
              SET initials = CONCAT(initials, initial);
        END IF;
        SET i = i + 1;
  END WHILE;
  RETURN initials;

END;

In the above function, we iterate Enter a string and split each word based on spaces and get its first letter and concatenate them together as function return value.

The above is the detailed content of How to use mysql procedure function. For more information, please follow other related articles on the PHP Chinese website!