mysqlストレージ・プロシージャの基本的な使用

6789 ワード

java

宣言変数--declare変数名変数タイプ[default変数値]

分号は忘れられない!!!

変数演算SET変数名:=式または値

ストレージ・プロシージャ・パラメータ

サイクル1-100加算

カスタム区間の合計

OUT IN

INOUT

CASE

repeatサイクル

カーソルすべてのロー数を取り出す

カーソルが境界を越えたとき、標識はありますか?利用フラグ終了

mysqlでカーソル境界を操作できるcontinue handlerのフラグ

解決策、exit

に変更

解決策まずfetchは

サイクルで

ブラシ権限

宣言変数--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()

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

カーソルはすべてのローを取り出します

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

カーソルが境界を越えたとき、フラグはありますか?利用フラグ終了
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()；

常用微信集積(サードパーティsdk集積キット)--微信登録

Mac OS 10.13 Mysqlインストールレコード