Mysql Stored Procedure example

Mysql Stored Procedure example

DELIMITER $
Create procedure generate_Equipment (district INT, warehouseNO VARCHAR (10 ))
BEGIN
DECLARE l_no VARCHAR (50 );
DECLARE l_address VARCHAR (20 );
DECLARE l_name VARCHAR (20 );
DECLARE l_city VARCHAR (20) DEFAULT "Tianjin ";
DECLARE l_long1_double;
DECLARE l_latitude DOUBLE;
DECLARE l_money BIGINT;
DECLARE l_type tinyint default 2;
DECLARE l_rand DOUBLE;
DECLARE l_init LONG;
DECLARE l_actual LONG;
DECLARE I INT DEFAULT 0;

WHILE I & lt; 100 DO
SELECT longpolling INTO l_longpolling FROM warehouse where no = warehouseNO AND district = district;
SELECT latitude INTO l_latitude FROM warehouse where no = warehouseNO AND district = district;
SET l_address = "address ";
SET l_name = "equipment ";
SET l_rand = RAND ();
SET l_no = CONCAT ('03q', ROUND (l_rand * 1000000000000 ));
SET l_address = CONCAT (l_address, ROUND (l_rand * I ));
SET l_name = CONCAT (l_name, ROUND (l_rand * I ));
SET l_longpolling = l_longpolling + l_rand;
SET l_latitude = l_latitude + l_rand;
SET l_init = ROUND (l_rand * 30000 );
SET l_actual = ROUND (l_rand * 13000 );
Insert into equipment (NO, NAME, city, district, address, longdistance, latitude, init, actual, TYPE) VALUES (l_no, l_name, l_city, district, l_address, l_longdistance, l_latitude, rochelle init, Rochelle actual, Rochelle type );
SET I = I + 1;
End while;
END;
$
DELIMITER;

An example of a MYSQL Stored Procedure

DELIMITER $

Drop procedure if exists 'proc _ test' $

CREATE
PROCEDURE 'carpo _ xianjin '. 'proc _ test' (IN user_name VARCHAR (50), IN amount VARCHAR (20 ))
BEGIN
DECLARE v_balance VARCHAR (20 );

SELECT balance INTO v_balance FROM userbalance where user = user_name;

IF v_balance> amount THEN
UPDATE userbalance SET balance = balance-amount where user = user_name;

Insert into log (TYPE) VALUES ('change balance ');
End if;

END $

DELIMITER;
Check it out. You didn't give the script, and I couldn't test it either.

Can I enter multiple parameters for a mysql stored procedure? For example

Mysql> DELIMITER //
Mysql> create procedure HelloWorld2 (
-> IN vUserName VARCHAR (10 ),
-> OUT vOutValue VARCHAR (10 ),
-> INOUT vInOutValue VARCHAR (10 ))
-> BEGIN
-> Select concat ('hello', vUserName );
-> SET vOutValue = 'a ';
-> SET vInOutValue = 'B ';
-> END //
Query OK, 0 rows affected (0.00 sec)

Mysql> call HelloWorld2 ('edward ', @ a, @ B )//
+ ----------------------------- +
| CONCAT ('hello', vUserName) |
+ ----------------------------- +
| Hello Edward |
+ ----------------------------- +
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Mysql> select @ //
+ ------ +
| @ A |
+ ------ +
| A |
+ ------ +
1 row in set (0.00 sec)

Mysql> select @ B //
+ ------ +
| @ B |
+ ------ +
| B |
+ ------ +
1 row in set (0.00 sec)
Reference: hi.baidu.com/...1.html