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