看到个文章,有一些简单的存储过程知识,转载过来以备不时之需:
转载自:https://blog.csdn.net/yanluandai1985/article/details/83656374
使用可视化 工具创建存储过程:
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `jixiu_db`.`test_procedure`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
END$$
DELIMITER ;
使用SQL语句创建:
基本SQL:
CREATE TABLE t_student
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT(11) NOT NULL
);
INSERT INTO t_student VALUES(NULL,'大宇',22),(NULL,'小宇',20);
存储过程写法:
DROP PROCEDURE IF EXISTS getStuById;
DELIMITER // -- 定义存储过程结束符号为//
CREATE PROCEDURE getStuById(IN stuId INT(11),OUT stuName VARCHAR(255),OUT stuAge INT(11)) -- 定义输入与输出参数
COMMENT 'query students by their id' -- 提示信息
SQL SECURITY DEFINER -- DEFINER指明只有定义此SQL的人才能执行,MySQL默认也是这个
BEGIN
SELECT name ,age INTO stuName , stuAge FROM t_student WHERE id = stuId; -- 分号要加
END // -- 结束符要加
DELIMITER ; -- 重新定义存储过程结束符为分号
调用存储过程:CALL getStuById(2,@name,@age);
存储函数:
使用可视化工具创建:
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION `jixiu_db`.`test`()
RETURNS TYPE
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
END$$
DELIMITER ;
使用SQL语句:
DROP FUNCTION IF EXISTS getStuNameById;
DELIMITER //
CREATE FUNCTION getStuNameById(stuId INT) -- 默认是IN,但是不能写上去。stuId视为输入的临时变量
RETURNS VARCHAR(255) -- 指明返回值类型
RETURN (SELECT name FROM t_student WHERE id = stuId); // -- 指明SQL语句,并使用结束标记。注意分号位置
DELIMITER ;
使用存储函数:
SELECT getStuNameById(1);
从上述存储函数的写法上来看,存储函数有一定的缺点。首先与存储过程一样,只能返回一条结果记录。另外就是存储函数只能指明一列数据作为结果,而存储过程能够指明多列数据作为结果。
流程控制的使用:
(1)IF语句的使用。
DROP PROCEDURE IF EXISTS testIf;
DELIMITER //
CREATE PROCEDURE testIf(OUT result VARCHAR(255))
BEGIN
DECLARE val VARCHAR(255);
SET val = 'a';
IF val IS NULL
THEN SET result = 'IS NULL';
ELSE SET result = 'IS NOT NULL';
END IF;
END //
DELIMITER ;
CALL testIf(@result);
SELECT @result;
(2)CASE语句
DROP PROCEDURE IF EXISTS testCase;
DELIMITER //
CREATE PROCEDURE testCase(OUT result VARCHAR(255))
BEGIN
DECLARE val VARCHAR(255);
SET val = 'a';
CASE val IS NULL
WHEN 1 THEN SET result = 'val is true';
WHEN 0 THEN SET result = 'val is false';
ELSE SELECT 'else';
END CASE;
END //
DELIMITER ;
CALL testCase(@result);
SELECT @result;
(3)LOOP
LOOP用于重复执行SQL。LEAVE 用于退出循环。
DROP PROCEDURE IF EXISTS testLoop;
DELIMITER //
CREATE PROCEDURE testLoop(OUT result VARCHAR(255))
BEGIN
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id + 1;
IF id>10 THEN LEAVE add_loop; -- 可在此处修改成批量插入
END IF;
SET result = id;
END LOOP add_loop;
END //
DELIMITER ;
CALL testLoop(@result);
SELECT @result;
批量插入的例子:
DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT(11) NOT NULL
);
DROP PROCEDURE IF EXISTS testLoop;
DELIMITER //
CREATE PROCEDURE testLoop(IN columnCount INT(11))
BEGIN
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id + 1;
IF id>columnCount THEN LEAVE add_loop;
END IF;
INSERT INTO t_student(id,name,age) VALUES(id,'dayu',22);
END LOOP add_loop;
END //
DELIMITER ;
CALL testLoop(15);
(4)WHILE
DROP PROCEDURE IF EXISTS testWhile;
DELIMITER //
CREATE PROCEDURE testWhile(IN myCount INT(11),OUT result INT(11))
BEGIN
DECLARE i INT DEFAULT 0 ; -- 定义变量
WHILE i < myCount DO -- 符合条件就循环
-- 核心循环SQL;
SET i = i + 1 ; -- 计数器+1
END WHILE; -- 当不满足条件,结束循环 --分号一定要加!
SET result = i; -- 将变量赋值到输出
END //
存储过程必须使用CALL语句来调用。如果要调用其它数据库的存储过程,需要指定数据库名称。例如 CALL dbname.spname
使用SHOW STATUS 查看存储过程或函数的状态
存储过程
SHOW PROCEDURE STATUS LIKE 'C%';
存储函数
SHOW FUNCTION STATUS LIKE 'C%';
删除:
删除存储过程语法:DROP PROCEDURE [ IF EXISTS ] sp_name;
删除存储函数语法:DROP FUNCTION [ IF EXISTS ] function_name;