Mysql存储过程学习笔记

最新推荐文章于 2023-03-03 20:55:18 发布

小飞鹤

最新推荐文章于 2023-03-03 20:55:18 发布

阅读量955

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/he90227/article/details/41746545

版权

数据库专栏收录该内容

27 篇文章 0 订阅

订阅专栏

MySQL存储过程基础知识

1，前提 ：需要MySQL 5

2，一个最简单的Hello world 程序

CREATE PROCEDURE HelloWorld() 
BEGIN  
    SELECT "Hello World!";  
END;

3，变量
使用DECLARE来声明，DEFAULT赋默认值，SET赋值

DECLARE counter INT DEFAULT 0;  
SET counter = counter+1; 

4，参数
IN为默认类型，值必须在调用时指定，值不能返回（值传递）
OUT值可以返回（指针传递）
INOUT值必须在调用时指定，值可以返回
CREATE PROCEDURE test(a INT, OUT b FLOAT, INOUT c INT)


5，条件判断
IF THEN、ELSEIF、ELSE、END IF

CREATE PROCEDURE discunted_price(normal_price NUMERIC(8, 2), OUT discount_price NUMERIC(8, 2))
BEGIN
    IF (normal_price > 500) THEN
        SET discount_price = normal_price * .8;
    ELSEIF (normal_price > 100) THEN
        SET discount_price = normal_price * .9;
    ELSE
        SET discount_price = normal_price;
    END IF;
END;

6，循环
LOOP、END LOOP

CREATE PROCEDURE simple_loop(OUT counter INT)
BEGIN
    SET counter = 0;
    my_simple_loop: LOOP
        SET counter = counter+1;
        IF counter = 10 THEN
            LEAVE my_simple_loop;
        END IF;
    END LOOP my_simple_loop;
END;

WHILE DO、END WHILE

CREATE PROCEDURE simple_while(OUT counter INT)
BEGIN
    SET counter = 0;
    WHILE counter != 10 DO
        SET counter = counter+1;
    END WHILE;
END ;

REPEAT、UNTILL

CREATE PROCEDURE simple_repeat(OUT counter INT)
BEGIN
    SET counter = 0;
    REPEAT
        SET counter = counter+1;
    UNTIL counter = 10 END REPEAT;
END;

7，异常处理
如果用cursor获取SELECT语句返回的所有结果集时应该定义NOT FOUND error handler来防止存储程序提前终结
如果SQL语句可能返回constraint violation等错误时应该创建一个handler来防止程序终结


8，数据库交互
INTO用于存储单行记录的查询结果

DECLARE total_sales NUMERIC(8, 2);
SELECT SUM(sale_value) INTO total_sales FROM sales WHERE customer_id=in_customer_id;

CURSOR用于处理多行记录的查询结果

CREATE PROCEDURE cursor_example()
    READS SQL DATA
BEGIN
    DECLARE l_employee_id INT;
    DECLARE l_salary NUMERIC(8,2);
    DECLARE l_department_id INT;
    DECLARE done INT DEFAULT 0;
    DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

    OPEN cur1;
    emp_loop: LOOP
        FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
        IF done=1 THEN
            LEAVE emp_loop;
        END IF;
    END LOOP emp_loop;
    CLOSE cur1;
END;

unbounded SELECT语句用于存储过程返回结果集

CREATE PROCEDURE sp_emps_in_dept(in_employee_id INT)
BEGIN
    SELECT employee_id, surname, firstname, address1, address2, zipcode, date_of_birth FROM employees WHERE department_id=in_employee_id;
END;

UPDATE、INSERT、DELETE、CREATE TABLE等非查询语句也可以嵌入存储过程里

CREATE PROCEDURE sp_update_salary(in_employee_id INT, in_new_salary NUMERIC(8,2))
BEGIN
    IF in_new_salary < 5000 OR in_new_salary > 500000 THEN
        SELECT "Illegal salary: salary must be between $5000 and $500, 000";
    ELSE
        UPDATE employees SET salary=in_new_salary WHERE employee_id=in_employee_id;
    END IF:
END ;

9，使用CALL调用存储程序

CREATE PROCEDURE call_example(employee_id INT, employee_type VARCHAR(20))
    NO SQL
BEGIN
    DECLARE l_bonus_amount NUMERIC(8,2);

    IF employee_type='MANAGER' THEN
        CALL calc_manager_bonus(employee_id, l_bonus_amount);
    ELSE
        CALL calc_minion_bonus(employee_id, l_bonus_amount);
    END IF;
    CALL grant_bonus(employee_id, l_bonus_amount);
END;

10，一个复杂的例子

CREATE PROCEDURE putting_it_all_together(in_department_id INT)
    MODIFIES SQL DATA
BEGIN
    DECLARE l_employee_id INT;
    DECLARE l_salary NUMERIC(8,2);
    DECLARE l_department_id INT;
    DECLARE l_new_salary NUMERIC(8,2);
    DECLARE done INT DEFAULT 0;

    DECLARE cur1 CURSOR FOR
        SELECT employee_id, salary, department_id
        FROM employees
        WHERE department_id=in_department_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

    CREATE TEMPORARY TABLE IF NOT EXISTS emp_raises
        (employee_id INT, department_id INT, new_salary NUMERIC(8,2));

    OPEN cur1;
    emp_loop: LOOP
        FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
        IF done=1 THEN    /* No more rows */
            LEAVE emp_loop;
        END IF;
        CALL new_salary(1_employee_id, l_new_salary); /* Get new salary */
        IF (l_new_salary <> l_salary) THEN  /* Salary changed */
            UPDATE employees
                SET salary=l_new_salary
            WHERE employee_id=l_employee_id;
            /* Keep track of changed salaries */
            INSERT INTO emp_raises(employee_id, department_id, new_salary)
                VALUES (l_employee_id, l_department_id, l_new_salary);
        END IF:
    END LOOP emp_loop;
    CLOSE cur1;
    /* Print out the changed salaries */
    SELECT employee_id, department_id, new_salary from emp_raises
        ORDER BY employee_id;
END;

11，存储方法
存储方法与存储过程的区别
1，存储方法的参数列表只允许IN类型的参数，而且没必要也不允许指定IN关键字
2，存储方法返回一个单一的值，值的类型在存储方法的头部定义
3，存储方法可以在SQL语句内部调用
4，存储方法不能返回结果集
例子：

CREATE FUNCTION f_discount_price
    (normal_price NUMERIC(8,2))
    RETURNS NUMERIC(8,2)
    DETERMINISTIC
BEGIN
    DECLARE discount_price NUMERIC(8,2);

    IF (normal_price > 500) THEN
        SET discount_price = normal_price * .8;
    ELSEIF (normal_price >100) THEN
        SET discount_price = normal_price * .9;
    ELSE
        SET discount_price = normal_price;
    END IF;

    RETURN(discount_price);
END;


12，触发器
触发器在INSERT、UPDATE或DELETE等DML语句修改数据库表时触发
触发器的典型应用场景是重要的业务逻辑、提高性能、监控表的修改等
触发器可以在DML语句执行前或后触发

CREATE TRIGGER sales_trigger
    BEFORE INSERT ON sales
    FOR EACH ROW
BEGIN
    IF NEW.sale_value > 500 THEN
        SET NEW.free_shipping = 'Y';
    ELSE
        SET NEW.free_shipping = 'N';
    END IF;

    IF NEW.sale_value > 1000 THEN
        SET NEW.discount = NEW.sale_value * .15;
    ELSE
        SET NEW.discount = 0;
    END IF;
END;

案例一：

CREATE TABLE Employee(  -- 创建普通表
	          id            int,
	          first_name    VARCHAR(15),
	          last_name     VARCHAR(15),
	          start_date    DATE,
	          end_date      DATE,
	          salary        FLOAT(8,2),
	          city          VARCHAR(10),
	          description   VARCHAR(15)
	      );

	 
	 
	insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
	                   values (1,'Jason',    'Martin',  '19960725',  '20060725', 1234.56, 'Toronto',  'Programmer');
	 

	 
	insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
	                    values(2,'Alison',   'Mathews',  '19760321', '19860221', 6661.78, 'Vancouver','Tester');
	 

	 
	insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
	                    values(3,'James',    'Smith',    '19781212', '19900315', 6544.78, 'Vancouver','Tester');
	 

	 
	insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
	                    values(4,'Celia',    'Rice',     '19821024', '19990421', 2344.78, 'Vancouver','Manager');
	 

	 
	insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
	                    values(5,'Robert',   'Black',    '19840115', '19980808', 2334.78, 'Vancouver','Tester');
	 

	 
	insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
	                    values(6,'Linda',    'Green',    '19870730', '19960104', 4322.78,'New York',  'Tester');
	 

	 
	insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
	                    values(7,'David',    'Larry',    '19901231', '19980212', 7897.78,'New York',  'Manager');
	 

	 
	insert into Employee(id,first_name, last_name, start_date, end_Date,   salary,  City,       Description)
	                    values(8,'James',    'Cat',     '19960917',  '20020415', 1232.78,'Vancouver', 'Tester');

	 
	select * from Employee; //查询数据

-- 创建存储过程， 带参存储过程
create procedure myProc(in_id int)          --- in_id  参数，int类型
begin
declare l_employee_id int;
declare l_salary numeric(8,2);
declare l_new_salary numeric(8,2);
declare done int default 0;

declare cur cursor for
   select id, salary from employee 
   where id = in_id;

declare continue handler for not found set done = 1;
 create temporary table if not exists emp_raises(
employee_id int,
new_salary numeric(8,2)
);

open cur;
emp_loop:loop
fetch cur into l_employee_id,l_salary;

if done = 1 then 
leave emp_loop;
end if;

set l_new_salary = 1233.12;

    update employee set salary = l_new_salary 
    where id = l_employee_id;
  
insert into emp_raises(employee_id,new_salary) 
   values(l_employee_id,l_new_salary);

end loop emp_loop;
close cur;
end;

</pre><pre code_snippet_id="543195" snippet_file_name="blog_20141205_3_1066333" name="code" class="sql">show databases;

use test;                     -- 使用test数据库

call myProc(1)                -- 调用存储过程

学习笔记

一个存储过程包括名字、参数列表，以及可以包括很多SQL语句的SQL语句集合
eg:
  create procedure proc_name(int parameter int) 
  begin
  
  declare variable varchar(30);
  if parameter = 1 then 
  set variable = 'MySQL';
  else 
  set variable= 'PHP';
  end if;
  
  insert into tb(name) values(variable);
  
  end;
  
  说明：
    mysql的存储过程以关键字：create procedure 开始，后 + 存储过程名字(参数)
    
    存储过程的参数一般由3部分组成：
    第一部分： in、out或inout 
        in    -- 表示向存储过程中传入参数
        out   -- 表示向外传出参数
        inout -- 可传入存储过程，并可以被存储过程修改后传出存储过程
                        注意： 存储过程默认为  in  传入参数
     第二部分：参数名
     第三部分：参数类型   -- 可为MySQL数据库中所有可用字段类型，如果多个参数，参数间以逗号分隔
  

MySQL存储过程的语句块以 begin开始，end结束  -- 语句体中可以包含 ：变量的声明、控制语句、SQL查询语句等

删除存储过程： 
drop procedure proc_name;

显示数据库中所有存储的存储过程的基本信息： 所属数据库、存储过程名称、创建时间等
show procedure status;

显示某个存储过程的详细信息
show create procedure proc_name;


mysql存储过程中使用游标的几个实例详解： 从简单游标使用到游标循环跳出的方法等
-- 简单实例：
drop procedure if exists getUserInfo;
create procedure getUserInfo(in date_day datetime)
-- 存储过程名：getUserInfo  参数： date_day 日期格式：2014-12-05
begin
declare _userName varchar(12);  -- 用户名
declare _chinese int;  -- 语文
declare -macth int;   -- 数学
declare done int default 0;
-- 定义游标
declare rs_cursor cursor for 
select username,chinese,math from userInfo where datediff(createDate,date_day) = 0;

declare continue handler for not found set done = 1;

-- 获取昨天的日期
if date_day is null then 
  set date_day = date_add(now(),interval-1 day);
end if;

-- 打开游标
open rs_cursor
-- 遍历游标数据
cursor_loop:loop;
-- 循环取数据
fetch rs_cursor into _userName, _chinese, _math;
-- 如果done = 1 表示没有数据，退出遍历循环
if done = 1 then 
leave cursor_loop;
end if;
-- 更新表
update infoSum set total = _chinese+_math 
  where username = _userName;

end loop cursor_loop;
close ra_cursor;

end;

-- 存储过程游标循环跳出  ： 三种方式：
                         repeat方式
                         while方式
                         loop方式

1.repeat方式：
repeat 
     statements;
     until expression
end repeat;

demo 

declare num int;
declare my_string varchar(255);
repeat
set my_string = concat(my_string,num,'');
set num = num+1;
until num<5
end repeat;


2.while方式：
while expression do
Statements;
end while;

demo

declare num int;
declare my_string varchar(255);
set num = 1;
set str = '';
while num < 10 do
set my_string = concat(my_string,num,'');
set num = num+1;
end while;

3.loop方式：(注意其中的iterate，leave方式)

declare num int;
declare str varchar(255);
set num =1;
set str = '';

loop_name:loop
if num<10 then 
leave loop_name;
end if;
set num = num+1;
if(num mode 3) then 
iterate loop_name;
else
set str = concat(str,num,'');
end if;
end loop loop_name;


注意：

iterate 可以理解为程序中常用的continue
leave就break



在存储过程中使用多游标：

先创建一张表，插入一些测试数据：
DROP TABLE IF EXISTS netingcn_proc_test;
CREATE TABLE `netingcn_proc_test` (
  `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20),
  `password` VARCHAR(20),
  PRIMARY KEY (`id`)
)ENGINE=InnoDB;
insert into netingcn_proc_test(name, password) values
('procedure1', 'pass1'),
('procedure2', 'pass2'),
('procedure3', 'pass3'),
('procedure4', 'pass4');

-- 下面就是一个简单存储过程的例子：
drop procedure IF EXISTS test_proc;
delimiter //    --  使用delimiter关键字， 定义了 // 表示行语句结束符
create procedure test_proc()
begin
 -- 声明一个标志done， 用来判断游标是否遍历完成
 DECLARE done INT DEFAULT 0;
 -- 声明一个变量，用来存放从游标中提取的数据
 -- 特别注意这里的名字不能与由游标中使用的列明相同，否则得到的数据都是NULL
 DECLARE tname varchar(50) DEFAULT NULL;
 DECLARE tpass varchar(50) DEFAULT NULL;
 -- 声明游标对应的 SQL 语句
 DECLARE cur CURSOR FOR
  select name, password from netingcn_proc_test;
 -- 在游标循环到最后会将 done 设置为 1
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 -- 执行查询
 open cur;
 -- 遍历游标每一行
 REPEAT
  -- 把一行的信息存放在对应的变量中
  FETCH cur INTO tname, tpass;
  if not done then
   -- 这里就可以使用 tname， tpass 对应的信息了
   select tname, tpass;
  end if;
  UNTIL done END REPEAT;
 CLOSE cur;
end
//
delimiter ;
-- 执行存储过程
call test_proc();

特别注意：
      1.这里的变量的声明、游标的声明和handler声明的顺序不能搞错
      2.必须先声明变量，再声明游标，最后声明handler
      
eg: 一个存储过程中使用多游标:

drop procedure if exists test_proc_1;
delimiter //
create procedure test test_proc_1(){
declare done int default 0;
declare tid int(11) default 0;
declare tname varchar(50) default null;
declare tpass varchar(50) default null;
-- 声明游标
declare cur_1 cursor for 
select name, password from proc_test_tbl;
declare cur_2 cursor for 
select id, name from proc_test_tbl;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

open cur_1;
repeat
fetch cur_1 int tname,tpass;
if not done then 
select tname,tpass;
end if;
until done end repeat;
close cur_1;
-- 特别注意这里：一定要重置done的值为0
set done = 0 ;

open cur_2;
repeat
fetch cur_2 into tid,tname;
if not done then 
select tid ,tname
end if;
until done end repeat;
close cur_2;
end
//
}

-- 调用存储过程 
delimiter;
call test_proc_1();

特别注意：
                  在遍历第二个游标前使用了set done = 0, 因为第一个游标遍历完后，气质被handler设置为1了，
                  如果不set done 为0，那么第二个游标就不会遍历了。
                
                  好习惯 -- 每个开口游标的操作前，都使用set done = 0; 确保游标能真正遍历


当然还可以使用begin语句块嵌套的方式来处理多个游标：
eg：
drop procedure IF EXISTS test_proc_2;
delimiter //
create procedure test_proc_2()
begin
 DECLARE done INT DEFAULT 0;
 DECLARE tname varchar(50) DEFAULT NULL;
 DECLARE tpass varchar(50) DEFAULT NULL;
 DECLARE cur_1 CURSOR FOR
  select name, password from netingcn_proc_test;
 DECLARE cur_2 CURSOR FOR
  select id, name from netingcn_proc_test;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 open cur_1;
 REPEAT
  FETCH cur_1 INTO tname, tpass;
  if not done then
   select tname, tpass;
  end if;
  UNTIL done END REPEAT;
 CLOSE cur_1;
 begin
  DECLARE done INT DEFAULT 0;
  DECLARE tid int(11) DEFAULT 0;
  DECLARE tname varchar(50) DEFAULT NULL;
  DECLARE cur_2 CURSOR FOR
   select id, name from netingcn_proc_test;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  open cur_2;
  REPEAT
   FETCH cur_2 INTO tid, tname;
   if not done then
    select tid, tname;
   end if;
   UNTIL done END REPEAT;
  CLOSE cur_2;
 end;
end
//
delimiter ;
call test_proc_2();

案例二

写mysql存储过程应注意的几点：

1、声明变量（declare）时要注意字符集，用变量存储表字段时，表字段与变量的字符编码要一致。

2、mysql的字符合并不能用‘+’号，必须用concat函数。

3、每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束。

BEGIN

declare rt VARCHAR(100) CHARACTER SET gbk DEFAULT NULL;

declare done tinyint(1) default 0;

DECLARE ttname VARCHAR(60) CHARACTER SET gbk DEFAULT NULL;

DECLARE tsqltxt VARCHAR(512) CHARACTER SET gbk DEFAULT NULL;

DECLARE tremarks VARCHAR(60) CHARACTER SET gbk DEFAULT NULL;

DECLARE tfield VARCHAR(60) CHARACTER SET gbk DEFAULT NULL;

 

DECLARE curtable CURSOR FOR

      SELECT distinct TABLE_name

       FROM information_schema.TABLEs where TRIM(TABLE_COMMENT)<>'' and TRIM(TABLE_COMMENT)<>'VIEW'  order by TABLE_name;

 

declare continue handler for not found set done=1;

 set NAMES 'utf8';

drop table if EXISTS GetTableSQL;

CREATE TABLE `gettablesql` (

  `tbname` varchar(60) CHARACTER SET gbk DEFAULT NULL,

  `sqltxt` varchar(4096) CHARACTER SET gbk DEFAULT NULL,

  `tabletitle` varchar(51) CHARACTER SET gbk DEFAULT NULL

) ENGINE=InnoDB;

open curtable;

   tableloop:

  LOOP

      set tsqltxt='select';

      FETCH curtable

      INTO ttname;

      IF done = 1 THEN

         LEAVE tableloop;

      END IF;

      -- select ttname;

 BEGIN

       declare done1 tinyint(1) default 0;

       DECLARE curfield CURSOR FOR

      
SELECT  COLUMN_NAME,COLUMN_COMMENT

            FROM information_schema.COLUMNS  where upper(TABLE_name)=upper(ttname) and (COLUMN_COMMENT<>'') ;

       declare continue handler for not found set done1=1;

      OPEN curfield;

         fieldloop:

       LOOP

        FETCH curfield

        INTO tfield,tremarks;

        IF done1 = 1 THEN

         LEAVE fieldloop;

        END IF;

        if tsqltxt='select' THEN

           set tsqltxt=CONCAT(tsqltxt,' ',tfield,' ','''',tremarks,'''');

        ELSE

           set tsqltxt=CONCAT(tsqltxt,',',tfield,' ','''',tremarks,'''');

        END IF;

      

       END LOOP fieldloop;

       close curfield;

       set tsqltxt=concat(tsqltxt,' from ',ttname);

       insert into GetTableSQL values(ttname,tsqltxt,'');

END;

   END LOOP tableloop;

   close curtable;

  update GetTableSQL as G set tabletitle=(select TABLE_COMMENT from information_schema.TABLEs  s

   where (trim(s.TABLE_COMMENT)<>'') and g.tbname=s.table_name );

  select cast(count(*) as char) into rt from GetTableSQL;

  set rt=concat('成功更新',rt,'个表的表名注释和字段注释到字典库GetTableSQL中！');

  SELECT rt;

END