sql 存储过程 if_A.004.8.SQL存储过程

• SQL存储过程含义

• 存储过程语法

• 存储过程举例

SQL存储过程含义

存储过程 (Stored Procedure) 可以封装SQL语句集，可以用来完成特定的业务逻辑，可以设置出参入参。

存储过程语法

CREATE
    [DEFINER = { user | CURRENT_USER }]
 PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
  Valid SQL routine statement
 
[begin_label:] BEGIN
  [statement_list]
    ……
END [end_label]

存储过程举例

举例1

--声明语句结束符
DELIMITER $$
--创建存储过程及名字
CREATE PROCEDURE sp_jssdba001()
--存储过程开始与结束
BEGIN
  SELECT 'jssdba001';
END $$
--将结束符号恢复为分号
DELIMITER;
--调用
CALL sp_jssdba001();

举例2

-- 定义变量set
DELIMITER $$
CREATE PROCEDURE sp_jssdba001()
BEGIN
  DECLARE stu_from_date DATE DEFAULT '2021-01-20';
  SET stu_from_date = '2021-09-01';
  SELECT stu_from_date;
END $$
DELIMITER;
CALL sp_jssdba001();
-- 局部变量@
DELIMITER $$
CREATE PROCEDURE sp_user_host()
BEGIN
SELECT USER,HOST INTO @login_user,@login_host FROM mysql.user;
SELECT @login_user,@login_host;
END $$
DELIMITER ;

CALL sp_user_host();
SELECT @login_user,@login_host;

--全局变量@@
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

举例3

--in,out,inout参数
DELIMITER //

CREATE PROCEDURE sp_login_user_host (
  IN js_empno INT (10), --入参
  OUT js_fname VARCHAR (30) --出参
)
BEGIN
  SELECT
    first_name INTO js_fname
  FROM
    employees
  WHERE emp_no = js_empno;
END //

DELIMITER;

CALL sp_login_user_host (10001, @js_fname);

SELECT  @js_fname;

举例4

-- 员工表中最高、最低工资
DELIMITER $$
CREATE PROCEDURE sp_sal_max_min(OUT sal_max INT(11),OUT sal_min INT(11))
BEGIN
 SELECT MAX(salary) INTO sal_max FROM salaries;
 SELECT MIN(salary) INTO sal_min FROM salaries;
END $$
DELIMITER ;

CALL sp_sal_max_min(@sal_max,@sal_min);

SELECT @sal_max;
SELECT @sal_min;

举例5-if条件控制

DELIMITER $$
CREATE PROCEDURE sp_emp_sel(IN em_name VARCHAR(20))
BEGIN
    IF em_name IS NULL OR em_name='' THEN
        SELECT * FROM employees LIMIT 5;
    ELSE
        SELECT * FROM employees WHERE first_name LIKE em_name;
    END IF;
END
$$
DELIMITER ;

CALL sp_emp_sel(NULL);
CALL sp_emp_sel('%arv%');

举例6-case

DELIMITER $$
CREATE PROCEDURE sp_sal_case(IN sp_par VARCHAR(10))
BEGIN
CASE sp_par
WHEN 'min' THEN
SELECT MIN(salary) FROM salaries;
WHEN 'max' THEN
SELECT MAX(salary) FROM salaries;
WHEN 'avg' THEN
SELECT AVG(salary) FROM salaries;
END CASE;

END
$$
DELIMITER ;
CALL sp_sal_case('min');
CALL sp_sal_case('max');
CALL sp_sal_case('avg');

举例7-repeat

DELIMITER $$
CREATE PROCEDURE sp_repeat ()
BEGIN
  DECLARE n INT;
  SET n = 1;
  REPEAT
    INSERT INTO jss_emp
    VALUES
      (n, CONCAT('jss',n), n + 100);
    COMMIT;
    SET n = n + 1 ;
    UNTIL n > 1000  END REPEAT;
END $$
DELIMITER ;

CALL sp_repeat();
SELECT COUNT(1) FROM jss_emp;
SELECT * FROM jss_emp;

举例8-while ... do

DELIMITER $$
CREATE PROCEDURE sp_while ()
BEGIN
  DECLARE n INT;
  SET n = 1;
WHILE n<=1000 DO
    INSERT INTO jss_emp
    VALUES
      (n, CONCAT('jss',n), n + 100);
    COMMIT;
    SET n = n + 1 ;
    END WHILE;
END $$
DELIMITER ;

CALL sp_repeat();
SELECT COUNT(1) FROM jss_emp;
SELECT * FROM jss_emp;

关注公众号，查阅数据库与Linux知识不迷路。公众号：数据库 BOSS

添加个人微信，获取更多学习教程