-- 存储过程示例一 in
DROP DATABASE IF EXISTS tdemo;
CREATE DATABASE tdemo CHARACTER SET=utf8;
USE tdemo;
CREATE TABLE temp2(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
nodename VARCHAR(10)
)ENGINE=INNODB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;
DELIMITER $$
USE tdemo $$
DROP PROCEDURE IF EXISTS pro_demo1 $$
CREATE PROCEDURE pro_demo1(IN tid INT)
BEGIN
SELECT tid;
SELECT * FROM temp2 WHERE id=tid;
END $$
CALL pro_demo1(1);
-- 存储过程示例 out
DELIMITER $$
USE tdemo $$
DROP PROCEDURE IF EXISTS pro_demo2 $$
CREATE PROCEDURE pro_demo2(OUT tnodename VARCHAR(10))
BEGIN
DECLARE tname VARCHAR(10);
SET tname = 'tl';
SELECT nodename INTO tnodename FROM temp2 WHERE id=1;
SELECT tnodename;
SET tnodename=CONCAT(tname,tnodename);
SELECT tname;
SELECT tnodename;
END $$
CALL pro_demo2(@name);
SELECT @name;
-- 存储过程示例 in+out
DELIMITER $$
USE tdemo $$
DROP PROCEDURE IF EXISTS pro_demo3 $$
CREATE PROCEDURE pro_demo3(IN tid INT,OUT tnodename VARCHAR(10))
BEGIN
DECLARE tname VARCHAR(10) DEFAULT 'tl';
SELECT nodename INTO tnodename FROM temp2 WHERE id=tid;
SET tnodename = CONCAT(tname,tnodename);
SELECT tnodename;
SET tname = 'll';
SET tnodename = CONCAT(tname,tnodename);
END $$
CALL pro_demo3(1,@name);
SELECT @name;
-- 存储过程示例 inout
DELIMITER $$
USE tdemo $$
DROP PROCEDURE IF EXISTS pro_demo4 $$
CREATE PROCEDURE pro_demo4(INOUT temp VARCHAR(10))
BEGIN
DECLARE tname VARCHAR(10) DEFAULT 'tlll';
SELECT nodename INTO temp FROM temp2 WHERE id=CAST(temp AS SIGNED);
SELECT temp;
SET temp = CONCAT(tname,temp);
END $$
SET @temp=1;
CALL pro_demo4(@temp);
SELECT @temp;
-- 存储过程判断语句 if case
DELIMITER $$
USE tdemo $$
DROP PROCEDURE IF EXISTS pro_demo5 $$
CREATE PROCEDURE pro_demo5(IN tstr VARCHAR(10),IN tid INT)
BEGIN
DECLARE temp VARCHAR(100);
IF tid > 0 THEN
SELECT 'tid > 0 ';
ELSEIF tid = 0 THEN
SELECT 'tid = 0 ';
ELSEIF tid IS NULL THEN
SELECT 'tid is null';
ELSE
SELECT 'tid < 0';
END IF;
CASE WHEN tstr ='tl' THEN
SET temp = 'tstr = tl';
SELECT temp;
WHEN tstr IS NOT NULL THEN
SET temp = 'tstr is not null';
SELECT temp;
ELSE
SET temp = 'tstr is null';
SELECT temp;
END CASE;
END $$
CALL pro_demo5('a',2);
CALL pro_demo5('tl',0);
CALL pro_demo5(NULL,-1);
CALL pro_demo5(NULL,NULL);
-- 存储过程判断 条件 case if 嵌套
DELIMITER $$
USE tdemo $$
DROP PROCEDURE IF EXISTS pro_demo6 $$
CREATE PROCEDURE pro_demo6(IN str VARCHAR(10),IN id INT)
BEGIN
DECLARE temp VARCHAR(100);
CASE WHEN id>0 THEN
SELECT 'id > 0';
IF str IS NOT NULL THEN
SET temp = 'str is not null';
SELECT temp;
ELSE
SET temp = 'str is error';
SELECT temp;
END IF;
WHEN id<0 THEN
SELECT 'id < 0';
WHEN id IS NULL THEN
SELECT 'id is null';
ELSE
SELECT 'id is error';
END CASE;
END $$
CALL pro_demo6('a',2);
CALL pro_demo6('tl',0);
CALL pro_demo6(NULL,-1);
CALL pro_demo6(NULL,NULL);
-- 存储过程 循环 while END WHILE
DELIMITER $$
USE tdemo $$
DROP PROCEDURE IF EXISTS pro_demo7 $$
CREATE PROCEDURE pro_demo7()
BEGIN
DECLARE i INT;
DECLARE str VARCHAR(10) DEFAULT '$';
SET i = 0;
WHILE i<5 DO
SET str= CONCAT(str,',',CAST(i AS CHAR));
INSERT INTO temp2(id,nodename) VALUES(25+i,str);
SET i=i+1;
END WHILE;
SELECT str;
END $$
CALL pro_demo7();
-- 存储过程 循环 while repeat end repeat
DELIMITER $$
USE tdemo $$
DROP PROCEDURE IF EXISTS pro_demo8 $$
CREATE PROCEDURE pro_demo8()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE str VARCHAR(100) DEFAULT '$';
REPEAT
SET str= CONCAT(str,',',CAST(i AS CHAR));
SET i = i+1;
UNTIL i>=5
END REPEAT;
SELECT str;
END $$
CALL pro_demo8();
-- 存储过程 循环 loop end loop
DELIMITER $$
USE tdemo $$
DROP PROCEDURE IF EXISTS pro_demo9 $$
CREATE PROCEDURE pro_demo9()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE str VARCHAR(100) DEFAULT '$';
loop_label:LOOP
SET str = CONCAT(str,',',i);
SET i = i+1;
IF i>=5 THEN
LEAVE loop_label;
END IF;
END LOOP;
SELECT str;
END $$
CALL pro_demo9();
-- 存储过程 循环 loop end loop iterate
DELIMITER $$
USE tdemo $$
DROP PROCEDURE IF EXISTS pro_demo10 $$
CREATE PROCEDURE pro_demo10()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE str VARCHAR(100) DEFAULT '$';
loop_label:LOOP
IF i=3 THEN
SET i = i+1;
ITERATE loop_label; -- iterate 跳出当前循环
END IF;
SET str = CONCAT(str,',',i);
SET i = i+1;
IF i>=5 THEN
LEAVE loop_label;
END IF;
END LOOP;
SELECT str;
END $$
CALL pro_demo10();
-- 存储过程 游标
DELIMITER $$
USE tdemo $$
DROP PROCEDURE IF EXISTS pro_demo11 $$
CREATE PROCEDURE pro_demo11()
BEGIN
DECLARE done INT;
DECLARE id INT DEFAULT 0;
DECLARE str VARCHAR(10) DEFAULT '';
DECLARE cur_demo CURSOR FOR SELECT id,nodename FROM temp2 WHERE id=1; -- 声明一个游标变量
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 声明游标结束的标志位
SET done = 0;
OPEN cur_demo; -- 打开游标
loop_lable:LOOP
IF done=1 THEN
LEAVE loop_lable;
END IF;
FETCH cur_demo INTO id,str;
INSERT INTO temp2 VALUES(100,'tltl');
END LOOP loop_lable;
CLOSE cur_demo; -- 关闭游标
SELECT id;
SELECT str;
END $$
CALL pro_demo11();
-- 存储过程 游标
DELIMITER $$
USE tdemo $$
DROP PROCEDURE IF EXISTS pro_demo12 $$
CREATE PROCEDURE pro_demo12()
BEGIN
DECLARE done INT;
DECLARE id INT DEFAULT 0;
SET str2 VARCHAR(10);
DECLARE str VARCHAR(10) DEFAULT '';
DECLARE cur_demo CURSOR FOR SELECT id,nodename FROM temp2 WHERE id=1; -- 声明一个游标变量
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; -- 声明游标结束的标志位
SET done = 0;
OPEN cur_demo; -- 打开游标
FETCH cur_demo INTO id,str;
SET str2=str;
CLOSE cur_demo; -- 关闭游标
SELECT str2;
END $$
CALL pro_demo12();
-- 存储过程 游标 循环
DELIMITER $$
USE tdemo $$
DROP PROCEDURE IF EXISTS pro_demo13 $$
CREATE PROCEDURE pro_demo13()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE tid INT;
DECLARE tnodename VARCHAR(10);
DECLARE cur_demo CURSOR FOR SELECT * FROM temp2 WHERE id BETWEEN 1 AND 5;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
CREATE TEMPORARY TABLE tl(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
info VARCHAR(20) NOT NULL
);
OPEN cur_demo;
loop_label:LOOP
IF done=1 THEN
LEAVE loop_label;
END IF;
FETCH cur_demo INTO tid,tnodename;
INSERT INTO tl(info) VALUES(CONCAT(tnodename,CAST(tid AS CHAR)));
END LOOP loop_label;
CLOSE cur_demo;
SELECT * FROM tl;
DROP TABLE tl;
END $$
CALL pro_demo13();