MYSQL 存储过程

存储过程
CREATE PROCEDURE p7 ()
BEGIN
SET @a = 5;
SET @b = 5;
INSERT INTO t VALUES (@a);
SELECT s1 * @a FROM t WHERE s1 >= @b;
END; //              /* I won't CALL this. 这个语句将不会被调用*/

CREATE PROCEDURE p9 ()
BEGIN
DECLARE a INT /* there is no DEFAULT clause */;
DECLARE b INT /* there is no DEFAULT clause */;
SET a = 5;        /* there is a SET statement */
SET b = 5;        /* there is a SET statement */
INSERT INTO t VALUES (a);
SELECT s1 * a FROM t WHERE s1 >= b;
END; //              /* I won't CALL this */

-------------

CREATE PROCEDURE p8 ()
BEGIN
DECLARE a INT;
DECLARE b INT;
SET a = 5;
SET b = 5;
INSERT INTO t VALUES (a);
SELECT s1 * a FROM t WHERE s1 >= b;
END; //       /* I won't CALL this */

--循环语句
WHILE ... END WHILE

CREATE PROCEDURE p14 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t VALUES (v);
SET v = v + 1;
END WHILE;
END; //

LOOP ... END LOOP

CREATE PROCEDURE p16 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; //

CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1;
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //

-------------------

SET @a = 'tmp_xf_test';
set @sqlstr=concat('select * from ','',@a);
prepare stmt from @sqlstr;
execute stmt;
@a 是表名

CREATE PROCEDURE `bbc`.`tmp_xf_proc` ()
BEGIN
DECLARE stmt varchar(2000);
declare p_table varchar(50);

SET @a = 'tmp_xf_test';
set @sqlstr=concat('select * from ','',@a);
prepare stmt from @sqlstr;
execute stmt;
END;

set @sqlstr=concat('insert into users(name,password,age,adddate) values(');
set @sqlstr=concat(@sqlstr,'''',name2,'''',',','''',password2,'''',',',age2,',','''',adddate2,'''',')');
prepare stmt from @sqlstr;

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
mysql> SET @a = 'select * from table1 ';
mysql> SET @b = 'where column1>10 limit 1';
mysql> SET @a = concat(@a,@b);
mysql> PREPARE stmt1 FROM @a;