学习Mysql存储过程的一些程序

DELIMITER $$
DROP PROCEDURE IF EXISTS HelloWorld$$
CREATE PROCEDURE HelloWorld ()
BEGIN
	SELECT 'Hello World';
END$$
DELIMITER;
CALL HelloWorld();

DELIMITER $$
DROP PROCEDURE IF EXISTS Variable$$
CREATE PROCEDURE Variable (input_number INT)
	BEGIN
		DECLARE my_int INT;
		SET my_int =SQRT(input_number);
		   select my_int;
	END$$
DELIMITER ;

CALL Variable(100);


DELIMITER $$
DROP PROCEDURE IF EXISTS discounted$$
CREATE PROCEDURE discounted (IN price NUMERIC(8,2), OUT discountPrice NUMERIC(8,2))
	BEGIN
		IF (price >= 500.0) THEN
				SET discountPrice = price  *  0.8;
 		ELSEIF(price >= 100) THEN
			SET discountPrice = price *  0.9;
		ELSE
			SET discountPrice = price;
		END IF;
	END$$
DELIMITER ;

CALL discounted(500,@newPrice);
SELECT @newPrice;


使用 LOOP 和 END LOOP 字句的简单循环 
当条件为真时继续执行的循环，使用 WHILE和 END WHILE 字句 
循环直至条件为真，使用 REPEAT 和 UNTIL 字句 

DELIMITER $$
DROP PROCEDURE IF EXISTS simple_loop$$
CREATE PROCEDURE simple_loop ()
	BEGIN
		DECLARE counter INT DEFAULT 0;

	my_loop : LOOP
		SET counter = counter + 1;
		IF counter = 10 THEN
			LEAVE my_loop;
		END IF;
	END LOOP my_loop;

	SELECT "I can count to 10.";
	END$$
DELIMITER ;

CALL simple_loop();

学习Mysql存储过程的一些程序