First understanding of MySQL stored procedures, mysql stored procedures

First understanding of MySQL stored procedures, mysql stored procedures
Stored Procedure is a set of SQL statements for specific functions. It is compiled and Stored in the database, you can call and execute a stored procedure by specifying its name and giving a parameter (if the stored procedure has a parameter. The MySQL stored procedure is a new feature added since MySQL 5.0. This greatly improves the processing speed of the database and the flexibility of database programming.
Process: Several encapsulated statements that are executed by these encapsulated bodies when called. Store the stored procedure in the database.
Function: A "process" with a returned value ".
Process: A function without return values.
Advantages of stored procedures:1. Stored Procedures enhance the functions and flexibility of the SQL language (SQL programming, use of variables, expressions, and control struct) 2. stored procedures can achieve fast execution speed. (Stored procedures are much faster than batch processing because they are pre-compiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it and provides an execution plan that is finally stored in the system table. The Transaction-SQL statement of batch processing needs to be compiled and optimized each time it is run, which is relatively slow .) 3. stored procedures can reduce network traffic. For operations (such as queries and modifications) on the same database object, if the Transaction-SQL statement involved in this operation is organized by the stored procedure, when the stored procedure is called on the client's computer, the call statement is transmitted on the network, which greatly increases network traffic and reduces network load. 4. stored procedures can be fully utilized as a security mechanism. By limiting the permissions of a stored procedure, the system administrator can restrict the data access permissions to avoid unauthorized users accessing data, this ensures data security. 5. The Stored Procedure allows standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure. Database professionals can modify the stored procedure at any time without affecting the application source code. Create a stored procedure:

DELIMITER $ // define the separator create procedure p () // p for the process name BEGIN -- SQL statement // encapsulate the statement body END $

View existing stored procedures: show procedure status \ G; call stored procedure: call stored procedure name (); delete stored procedure: drop procedure stored procedure name;
Variable Declaration: declare is used in the stored procedure to declare the variable format: declare variable name variable type [default value] In the stored procedure, variables can be legally calculated in the SQL statement, for example, +-*/how to assign values to the set variable: = expression stored procedure parameters: parameters can be declared in brackets of stored procedures. Syntax: create procedure p ([in/out/inout] parameter name parameter type...) in out inout for detailed analysis, see blog (MySQL Stored procedure Parameter usage in, out, inout)

Instance 1: Create an operation value assignment

Create procedure p1 () begin declare age int default 18; DECLARE height int default 180; SET age: = age + 20; select concat ('Age yes', age, 'height is ', height); END $

Example 2: if/else control structure

Create procedure p2 () begin declare age int default 18; DECLARE height int default 180; IF age> = 18 then select "adult"; else select "minor"; end if; END $

Example 3: Calculate the sum of 1-In the while/do control structure

CREATE PROCEDURE p3()BEGIN  DECLARE total INT DEFAULT 0;  DECLARE num INT DEFAULT 0;  WHILE num<=100 DO    SET total := num + total;    SET num := num + 1;   END WHILE;   SELECT total;END$$

Example 4: case Control Structure

CREATE PROCEDURE p4()BEGIN  DECLARE num INT DEFAULT 0;  SET num := FLOOR(4*RAND());  CASE num  WHEN 1 THEN SELECT "cat";  WHEN 2 THEN SELECT "dog";  WHEN 3 THEN SELECT "sheep";  ELSE SELECT "pig";  END CASE;END$$

Example 5: repeat loop

CREATE PROCEDURE p5()BEGIN  DECLARE num INT DEFAULT 0;  DECLARE total INT DEFAULT 0;  REPEAT    SET total = num + total;    SET num := num + 1;  UNTIL num>100 END REPEAT;  SELECT total;END$$

Mysql stored procedure:

Dayofyear is not a mysql function. It depends on how your stored procedure is defined...
_ Date should be a variable, and now () should be used to obtain the current time, which can be guessed.
Dayofyear (_ date) is to format the Variable _ date into the defined dayofyear format.
Then, the (now ()-dayofyear (_ date) is formatted as dayofyear.

I don't know. Do you understand this? Or paste the complete stored procedure and you will understand it.

Mysql Stored Procedure Problems

It depends on how your total stored procedure is defined. If @ t is the output parameter, you need to use the keyword out. The following is an example:
Create procedure sp_add (a int, B int, out c int)
Begin

Set c = a + B;

End;
Call process:
Call sp_add (1, 2, @ );
Select @;