DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE 數(shù)據(jù)庫名.存儲(chǔ)過程名([in變量名 類型,out 參數(shù) 2，...])
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
	BEGIN
		[DECLARE 變量名 類型 [DEFAULT 值];]
		存儲(chǔ)過程的語句塊;
	END$$

DELIMITER ;

DELIMITER $$

CREATE
    PROCEDURE `demo`.`demo1`()
	-- 存儲(chǔ)過程體
	BEGIN
		-- DECLARE聲明 用來聲明變量的
		DECLARE de_name VARCHAR(10) DEFAULT '';
		
		SET de_name = "jim";
		
		-- 測試輸出語句（不同的數(shù)據(jù)庫，測試語句都不太一樣。
		SELECT de_name;
	END$$

DELIMITER ;

CALL demo1();

DELIMITER $$

CREATE
    PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT)
	-- 存儲(chǔ)過程體
	BEGIN
		-- 把SQL中查詢的結(jié)果通過INTO賦給變量
		SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;
		SELECT s_count;
		
	END$$
DELIMITER ;

-- @s_count表示測試出輸出的參數(shù)
CALL demo2 ('男',@s_count);

DELIMITER $$
CREATE
    PROCEDURE `demo`.`demo3`(IN `day` INT)
	-- 存儲(chǔ)過程體
	BEGIN
		IF `day` = 0 THEN
		SELECT '星期天';
		ELSEIF `day` = 1 THEN
		SELECT '星期一';
		ELSEIF `day` = 2 THEN
		SELECT '星期二';
		ELSE
		SELECT '無效日期';
		END IF;
		
	END$$
DELIMITER ;

CALL demo3(2);

DELIMITER $$
CREATE 
    PROCEDURE demo4(IN num INT)
	BEGIN
		CASE -- 條件開始
	
		WHEN num<0 THEN 
			SELECT '負(fù)數(shù)';
		WHEN num>0 THEN 
			SELECT '正數(shù)';
		ELSE 
		SELECT '不是正數(shù)也不是負(fù)數(shù)';
	
		END CASE; -- 條件結(jié)束
	END$$
DELIMITER;

CALL demo4(1);

DELIMITER $$
CREATE 
    PROCEDURE demo5(IN num INT)
	BEGIN
		CASE num  -- 條件開始
		WHEN 1 THEN 
			SELECT '輸入為1';
		WHEN 0 THEN 
			SELECT '輸入為0';
		ELSE 
		SELECT '不是1也不是0';
		END CASE; -- 條件結(jié)束
	END$$
DELIMITER;

CALL demo5(0);

DELIMITER $$
CREATE 
    PROCEDURE demo6(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     WHILE num<10 DO -- 循環(huán)開始
	         SET num = num+1;
	         SET SUM = SUM+num;
	         END WHILE; -- 循環(huán)結(jié)束
	END$$
DELIMITER;

-- 調(diào)用函數(shù)
CALL demo6(0,@sum);

-- 查詢函數(shù)
SELECT @sum;

-- 創(chuàng)建過程
DELIMITER $$
CREATE 
    PROCEDURE demo7(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     REPEAT-- 循環(huán)開始
		SET num = num+1;
		SET SUM = SUM+num ;
		UNTIL num>=10
		END REPEAT; -- 循環(huán)結(jié)束
	END$$
DELIMITER;

CALL demo7(9,@sum);

SELECT @sum;

DELIMITER $$
CREATE 
    PROCEDURE demo8(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     demo_sum:LOOP-- 循環(huán)開始
		SET num = num+1;
		IF num > 10 THEN
		    LEAVE demo_sum; -- 結(jié)束此次循環(huán)
		ELSEIF num < 9 THEN
		    ITERATE demo_sum; -- 跳過此次循環(huán)
		END IF;
		
		SET SUM = SUM+num;
		END LOOP demo_sum; -- 循環(huán)結(jié)束
	END$$
DELIMITER;

CALL demo8(0,@sum);

SELECT @sum;

DELIMITER $$
CREATE 
    PROCEDURE demo9(IN s_student VARCHAR(10),IN s_sex CHAR(1),OUT s_result VARCHAR(20))
	BEGIN
	   -- 聲明一個(gè)變量 用來決定這個(gè)名字是否已經(jīng)存在
	   DECLARE s_count INT DEFAULT 0;
	   -- 驗(yàn)證這么名字是否已經(jīng)存在
	   SELECT COUNT(*) INTO s_count FROM student WHERE `name` = s_student;	
	   IF s_count = 0 THEN
	        INSERT INTO student (`name`, sex) VALUES(s_student, s_sex);
		SET s_result = '數(shù)據(jù)添加成功';
	   ELSE
                SET s_result = '名字已存在，不能添加';
                SELECT s_result;
	   END IF;
	END$$
DELIMITER;

CALL demo9("Jim","女",@s_result);

CALL demo9("Jim","女",@s_result)

SHOW PROCEDURE STATUS

SHOW PROCEDURE STATUS WHERE db = 'db名字' AND NAME = 'name名字';

SHOW PROCEDURE STATUS WHERE NAME LIKE '%mo%';

SHOW CREATE PROCEDURE 存儲(chǔ)過程名;

DROP PROCEDURE 存儲(chǔ)過程名;

<parameterMap type="savemap" id=“usermap"> 
	<parameter property="name" jdbcType="VARCHAR" mode="IN"/>
	<parameter property="sex" jdbcType="CHAR" mode="IN"/>
	<parameter property="result" jdbcType="VARCHAR" mode="OUT"/>
</parameterMap>

<insert id="saveUserDemo" parameterMap="savemap" statementType="CALLABLE"> 
{call saveuser(?, ?, ?)} 
</insert >

HashMap<String, Object> map = new HashMap<String, Object>(); 
	map.put("name", "Jim"); 
	map.put("sex","男");
	userDao.saveUserDemo(map); 
	map.get(“result”);//獲得輸出參數(shù)