Mysql存储过程学习笔记

MySQL存储过程基础知识

1，前提 ：需要MySQL 52，一个最简单的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 IFCREATE 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 LOOPCREATE 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 WHILECREATE PROCEDURE simple_while(OUT counter INT)BEGIN    SET counter = 0;    WHILE counter != 10 DO        SET counter = counter+1;    END WHILE;END ;REPEAT、UNTILLCREATE 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 DATABEGIN    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 SQLBEGIN    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 DATABEGIN    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)    DETERMINISTICBEGIN    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 ROWBEGIN    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类型begindeclare 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:loopfetch 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-05begindeclare _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 expressionend repeat;demo declare num int;declare my_string varchar(255);repeatset my_string = concat(my_string,num,'');set num = num+1;until num<5end repeat;2.while方式：while expression doStatements;end while;demodeclare num int;declare my_string varchar(255);set num = 1;set str = '';while num < 10 doset 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:loopif num<10 then leave loop_name;end if;set num = num+1;if(num mode 3) then iterate loop_name;elseset str = concat(str,num,'');end if;end loop loop_name;注意：iterate 可以理解为程序中常用的continueleave就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;repeatfetch cur_1 int tname,tpass;if not done then select tname,tpass;end if;until done end repeat;close cur_1;-- 特别注意这里：一定要重置done的值为0set done = 0 ;open cur_2;repeatfetch cur_2 into tid,tname;if not done then select tid ,tnameend 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来控制游标的结束。BEGINdeclare 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