mysql存储过程的基本使用

声明变量-- declare 变量名 变量类型 [default 变量值]

分号不能忘！！！！！！！！！！

CREATE PROCEDURE p1()	
	BEGIN
		DECLARE pm1 INT DEFAULT 10;
		DECLARE pm2 INT DEFAULT 20;
		SELECT CONCAT('pm1为',pm1,'pm2为',pm2);
	END

CALL p1
DROP PROCEDURE IF EXISTS p1

变量运算 SET 变量名 := 表达式或者值

CREATE PROCEDURE p2()	
	BEGIN
		DECLARE pm1 INT DEFAULT 10;
		SET pm1 := pm1+pm1;
		SELECT CONCAT('pm1为',pm1);
	END

CALL p2()

if

CREATE PROCEDURE p3()	
	BEGIN
		DECLARE age INT DEFAULT 10;
		
		IF age >18 THEN 
		SELECT "成年了";
		ELSE
		SELECT "未成年";
		END IF;
	END 
CALL p3()
DROP PROCEDURE IF EXISTS p3

存储过程传参

CREATE PROCEDURE p4(width INT,height INT)
	BEGIN
		SELECT CONCAT("你的面积是：",width*height);
		IF width >height THEN
		SELECT "比较宽";
		ELSEIF height > width THEN
		SELECT "比较高";
		ELSE
		SELECT "比较方";
		END IF;
	END
CALL p4(20,10)
CALL p4(0,0)
CALL p4(10,20)
DROP PROCEDURE IF EXISTS p4

循环 1-100相加

CREATE PROCEDURE p5()
	BEGIN
		DECLARE totle INT DEFAULT 0;
		DECLARE num INT  DEFAULT 0;
		WHILE num <=100 DO
		SET totle := totle+num;
		SET num := num+1;
		END WHILE;
		SELECT totle;
	END
CALL p5

自定义区间的求和

CREATE PROCEDURE p6(n1 INT , n2 INT)
	BEGIN
		DECLARE totle int DEFAULT 0;
		WHILE n1 <=n2 DO
		SET totle := totle+n1;
		SET n1 := n1+1;
		END WHILE;
		SELECT totle;
	END
CALL p6(1,100)

OUT IN

CREATE PROCEDURE p7(IN n1 INT ,IN n2 INT ,OUT result INT)
	BEGIN
		SET result :=0;
		WHILE n1 <=n2 DO
		SET result := result+n1;
		SET n1 := n1+1;
		END WHILE;
	END
CALL p7(1,100,@result);
SELECT @result;
DROP PROCEDURE p7

INOUT

CREATE PROCEDURE p8(INOUT age INT)
	BEGIN
		SET age = age+10;
	END
SET @age = 18;
CALL p8(@age);
SELECT @age;

CASE

CREATE PROCEDURE p9()
	BEGIN
		DECLARE con INT DEFAULT 0;
		SET con = FLOOR(RAND()*5);
		CASE con 
		WHEN 1 THEN SELECT "成功1";
		WHEN 2 THEN SELECT "成功2";
		WHEN 3 THEN SELECT "成功3";
		ELSE SELECT "其他";
		END CASE;
	END
CALL p9();

repeat 循环

CREATE PROCEDURE p10()
	BEGIN
		DECLARE n1 INT DEFAULT 0;
		DECLARE result INT DEFAULT 0;
		REPEAT
			SET result := n1 +result;
			SET n1 := n1 + 1;
		UNTIL n1>10 END REPEAT;
		SELECT result;
	END
CALL p10
DROP PROCEDURE IF EXISTS p10

/* 
	游标:一条sql对应多个结果集的资源，取出资源的接口
	open 游标名    打开游标名
	fetch 游标名   取值
	close 游标名   关闭游标名
*/
CREATE PROCEDURE p11()
	BEGIN
		DECLARE row_id INT;
		DECLARE row_name VARCHAR(255);
		DECLARE row_age INT;
		DECLARE getItems CURSOR FOR SELECT id,name,age FROM person;
		OPEN getItems;

		FETCH getItems INTO row_id,row_name,row_age;
		SELECT row_id,row_name,row_age;		
		FETCH getItems INTO row_id,row_name,row_age;
		SELECT row_id,row_name,row_age;
		FETCH getItems INTO row_id,row_name,row_age;
		SELECT row_id,row_name,row_age;	
		
		CLOSE getItems;
	END
CALL p11();
DROP PROCEDURE IF EXISTS p11

游标取出所有的行数

CREATE PROCEDURE p12()
	BEGIN
		DECLARE n INT DEFAULT 0;
		DECLARE itemcount INT DEFAULT 0;
		DECLARE row_id INT;
		DECLARE row_name VARCHAR(255);
		DECLARE row_age INT;
		DECLARE cou CURSOR FOR SELECT COUNT(*) FROM person;
		DECLARE getItems CURSOR FOR SELECT id,name,age FROM person;

		OPEN cou;
		FETCH cou INTO itemcount;
		
		OPEN getItems;
		
		WHILE n<itemcount DO
		FETCH getItems INTO row_id,row_name,row_age;
		SELECT row_id,row_name,row_age;		
		SET n := n+1;
		END WHILE;

		CLOSE getItems;
		CLOSE cou;
	END

CALL p12;
DROP PROCEDURE IF EXISTS p12

游标取出所有的行数

CREATE PROCEDURE p13()
	BEGIN
		DECLARE n INT DEFAULT 0;
		DECLARE itemcount INT DEFAULT 0;
		DECLARE row_id INT;
		DECLARE row_name VARCHAR(255);
		DECLARE row_age INT;
		
		DECLARE getItems CURSOR FOR SELECT id,name,age FROM person;
	
		OPEN getItems;
		
		SELECT COUNT(*) FROM person INTO itemcount;
		WHILE n<itemcount DO
		FETCH getItems INTO row_id,row_name,row_age;
		SELECT row_id,row_name,row_age;
			SET n := n + 1;
		END WHILE;

		CLOSE getItems;

	END

CALL p13;
DROP PROCEDURE p13;

游标越界时，有没有标志？利用标志结束

在mysql中可以什么一个continue handler 来操作游标越界的标志

# declare continue handler for not found set you = 0
# 最后获取到两次：fetch过界之后还是会continue
CREATE PROCEDURE p14()
	BEGIN
		
		DECLARE row_id INT;
		DECLARE row_name VARCHAR(255);
		DECLARE row_age INT;
		DECLARE you INT DEFAULT 1;

		DECLARE getItems CURSOR FOR SELECT id,name,age FROM person;
		declare continue handler for not found set you = 0;
		
		OPEN getItems;
		
		WHILE you > 0 DO
			FETCH getItems INTO row_id,row_name,row_age;
			SELECT row_id,row_name,row_age;
		
		END WHILE;

		CLOSE getItems;

	END

CALL p14();
DROP PROCEDURE IF EXISTS p14;


/*
    continue   还会继续执行后面的语句
		exit  后面的语句不在执行，结束当前的begin  end
		undo  是出发后，前面的语句取消，但是目前mysql还不支持
*/

解决办法，换成exit

CREATE PROCEDURE p15()
	BEGIN
		
		DECLARE row_id INT;
		DECLARE row_name VARCHAR(255);
		DECLARE row_age INT;
		DECLARE you INT DEFAULT 1;

		DECLARE getItems CURSOR FOR SELECT id,name,age FROM person;
		declare EXIT handler for not found set you = 0;
		
		OPEN getItems;
		
		WHILE you > 0 DO
			FETCH getItems INTO row_id,row_name,row_age;
			SELECT row_id,row_name,row_age;
		
		END WHILE;

		CLOSE getItems;

	END

CALL p15();
DROP PROCEDURE IF EXISTS p15;

解决办法 先fetch在循环

CREATE PROCEDURE p16()
	BEGIN
		
		DECLARE row_id INT;
		DECLARE row_name VARCHAR(255);
		DECLARE row_age INT;
		DECLARE you INT DEFAULT 1;

		DECLARE getItems CURSOR FOR SELECT id,name,age FROM person;
		declare continue handler for not found set you = 0;
		
		OPEN getItems;
		
		FETCH getItems INTO row_id,row_name,row_age;
		
		WHILE you > 0 DO
			SELECT row_id,row_name,row_age;
			FETCH getItems INTO row_id,row_name,row_age;
		END WHILE;

		CLOSE getItems;

	END

CALL p16;


/*
	mysql权限分为两个阶段:
		1、有没有权连接上来
		2、有没有权执行此操作，如select  update
	对于1:服务器如何判断用户有没有连接上来
		依据三个参数：你从哪来？host   你是谁？user   你的密码？password
		用户的这3个信息存在mysql.user表里面
	对于2:
*/

SELECT * FROM mysql.user

SELECT HOST,`user`,password FROM mysql.user

冲刷权限

FLUSH PRIVILEGES 

SELECT DATABASE()；