Usage of input, out, And inout parameters in MySQL stored procedures, mysqlinout

Usage of input, out, And inout parameters in MySQL stored procedures, mysqlinout
Stored procedure parameters: You can declare parameters in the brackets of the stored procedure. Syntax: create procedure p ([in/out/inout] parameter name parameter type ..) in: input a value to the parameter, and the defined parameter gets the value out: the parameter defined in the mode can only be assigned within the process body, this parameter can pass a value back to the calling process (in the stored procedure, the initial value of this parameter is null, regardless of whether the caller sets a value for the stored procedure parameter) inout: the caller can also pass the value to the stored procedure through the inout parameter, or pass the value to the caller from the stored procedure.
If you only want to pass data to the MySQL stored procedure, use the "in" type parameter. If you only return values from the MySQL stored procedure, use the "out" type parameter; if you need to pass the data to the MySQL stored procedure, you need to pass it back to us after some computation. In this case, you need to use the "inout" type parameter. If you do not explicitly specify "in", "out", and "inout" For MySQL stored procedure parameters, the default value is "in ".
Example 1: stored procedure parameters in

DELIMITER $$CREATE PROCEDURE p1(IN num INT)BEGIN  DECLARE i INT DEFAULT 0;  DECLARE total INT DEFAULT 0;  WHILE i<=num DO    SET total := i + total;    SET i := i+1;  END WHILE;  SELECT total;END$$

Example 2: stored procedure parameter passing out

CREATE PROCEDURE p2(OUT num INT)BEGIN SELECT num AS num_1; IF (num IS NOT NULL) THEN SET num = num + 1; SELECT num AS num_2; ELSE SELECT 1 INTO num; END IF; SELECT num AS num_3;END$$SET @num = 10$$CALL p2(@num)$$SELECT @num AS num_out$$

Example 3: Stored Procedure Parameter inout

CREATE PROCEDURE p3(INOUT age INT)BEGIN  SET age := age + 20;END$$set @currage =18$$call p3(@currage)$$select @currage$$

Mysql Stored Procedure Error

Mysql> create PROCEDURE sp_GetPageRecord (IN TableName VARCHAR (100), IN Primar
YKey VARCHAR (50), IN Col1 VARCHAR (255), IN WhereSql VARCHAR (255), IN Sort V
ARCHAR (200), IN PageSize INT, IN CurrPage INT, IN IsDesc BIT (1) begin end;
Query OK, 0 rows affected (0.00 sec)

Of course, you must first confirm the MySQL version. MySQL 5.0 and later versions support stored procedures
1. The VARCHAR type supports a maximum of 255 characters. For more than 255 characters, you can use the TEXT variable. The TEXT variable supports up to 65536 bytes.
2. The DEFAULT value in the parameter is not supported. That is to say, remove =.
3. A process body must be included. the start and END of the Process body must be identified by BEGIN and END.
4. BIT storage supports 1-64-BIT storage.
5. The var_list parameter in SELECT... INTO var_list In the stored procedure cannot be used.
6. the following two sentences are DELIMITER // and DELIMITER. DELIMITER indicates the DELIMITER, because MySQL uses the separator ";" by default. If we do not declare the DELIMITER, then the compiler treats the stored procedure as an SQL statement, and the compilation process of the stored procedure reports an error. Therefore, you must first declare the current segment separator with the DELIMITER keyword so that MySQL can "; "As the code in the stored procedure, the code will not be executed. After it is used up, the separator should be restored.
VII. The stored procedure may have input, output, and input and output parameters as needed. Here there is an output parameter s, whose type is int type. If there are multiple parameters, separate them.
Parameters
MySQL stored procedure parameters are used IN the definition of stored procedures. There are three parameter types: IN, OUT, And INOUT. The format is as follows:
Create procedure ([[IN | OUT | INOUT] Parameter Name Data class...])
IN input parameter: indicates that the value of this parameter must be specified when the stored procedure is called. Modifying the value of this parameter IN the stored procedure cannot be returned, which is the default value.
OUT output parameter: The value can be changed within the stored procedure and can be returned.
INOUT input and output parameters: this parameter is specified during the call and can be changed or returned.

Enter the following statement line by line at the mysql command prompt.
Delimiter //
Create procedure 'demo _ out_total '(IN tbl_name varchar (255), IN whereClause varchar (255), OUT
Total_tbl int)
BEGIN
DECLARE t_status int default 0;
DECLARE v_ SQL VARCHAR (500 );
DECLARE continue handler for sqlstate '200' SET t_status = 1;
DECLARE continue handler for sqlexception set t_status = 2;
Set v_ SQL = CONCAT ('select count (*) into @ total from', tbl_name, 'where', wher ...... remaining full text>

Mysql Stored Procedure

1. exec stored procedure name
2. exec stored procedure name parameter 1, parameter 2, parameter 3 ......
Or exec stored procedure name parameter 1 = '', parameter 2 ='', parameter 3 = ''......