MySQL stored procedure details mysql stored procedure and mysql stored procedure details

MySQL stored procedure details mysql stored procedure and mysql stored procedure details

/* Create a stored procedure */

/* Specify an ending mark before creating a stored procedure */

DELIMITER $

CREATE PROCEDURE test_pro1()

BEGIN

SELECT * FROM address;

END $

/* CALL the Stored Procedure */CALL test_prol ();

DELIMITER $

CREATE PROCEDURE test_pro1()

BEGIN

SELECT * FROM address;

SELECT * FROM book;

END $

CALL test_pro2();

/* Stored procedures with Parameters

IN: indicates the input parameter, which can carry data to the stored procedure.

OUT: return parameters. results can be returned from stored procedures.

INOUT: indicates the input and output parameters. Both parameters and results can be returned.

*/

DELIMITER $

CREATE PROCEDURE test_pro_in(IN id INT)

BEGIN

select * from address where id=id;

END $

CALL test_pro_in(7369);

/* Stored procedure with multiple input parameters

*/\

DELIMITER $

CREATE PROCEDURE test_pro_inmore(IN id INT,IN id2 INT)

BEGIN

SELECT * FROM address WHERE empno=id OR empno=id2；

END $

 CALL test_pro_inmore(7369,7499);

DELIMITER $

CREATE PROCEDURE test_pro_out(OUT num INT)

BEGIN

/* Assign a value to the output parameter. The keyword is set */

SET num=10;

END $ 

CALL test_pro_out (@ val); // The local variable is the session variable, and the session variable starts with the @ symbol.

SELECT @val;

/* Mysql database VARIABLES: 1. Global VARIABLES 2. Local VARIABLES 3. Session VARIABLES */show variables; // query all global VARIABLES

Show variables like 'Char % '// fuzzy query global VARIABLES

Select character_set_client; // global variable of a single query

Set @ vail = 'Peter '; // you must specify a value when defining a session variable!

select @vail;

// The Value of the local variable is DELIMITER between begin and end $

CREATE PROCEDURE test_pro_loc()

BEGIN

DECLARE i INT DEFAULT 1;

END $

// Stored procedure with input and output parameters, keyword inout

DELIMITER $

CREATE PROCEDURE test_pro_inout(INOUT n INT)

BEGIN

SET n=100;

END $

SET @aaaaa=10000;

SELECT  @aaaaa;

CALL test_pro_inout(@aaaaa);

SELECT @aaaaa;

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.