SQL存储过程与存储函数

看到个文章,有一些简单的存储过程知识,转载过来以备不时之需:

转载自: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；