本文详细介绍了在MySQL中遇到存储过程和函数定义变量时的报错情况，包括解决方法如更改delimiter、调整DECLARE的位置。同时讲解了处理程序的不同方式，如根据sqlstate_value和sql_error_code进行捕获。还讨论了光标的使用规则，以及流程控制语句如IF、CASE、LOOP等。最后，提到了如何调用、修改、查看和删除存储过程和函数的实践操作。
定义变量报错
BEGIN
DECLARE test_sql int;
set test_sql = 30;
end;
报错：
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE test_sql int;
- 原因一：
没有进行delimiter定义，加上delimiter
delimiter $$
BEGIN
DECLARE test_sql int;
set test_sql = 30;
end;
$$
delimiter ;
还是报错。。。。。。但是解决了一部分吧，继续ing
- 原因二：
手册中规定DECLARE必须出现在BEGIN 和 EDN之间，且在其它所有语句之前，且必须在存储函数或过程中设置。
delimiter $$
CREATE PROCEDURE proc_test()
BEGIN
DECLARE test_sql int default 10;
set test_sql = 30;
END;
$$
delimiter ;
定义处理程序
delimiter $$
CREATE PROCEDURE proc_test2()
BEGIN
方法一：declare continue handler for sqlstate '42s02' set @info='not found';
方法二：declare continue handler for 1146 set @info='not found';
方法三：declare not_found condition for 1146;
declare continue handler for not_found set @info='not found';
方法四：declare exit handler for sqlwarning set @info='not found';
方法五：declare exit handler for sqlexception set @info='not found';
END;
$$
delimiter ;
方法一：捕获sqlstate_value的值，如果遇到值是42s02，执行continue操作，并输出“not found”的信息。
方法二：捕获sql_error_code的值，如果遇到值是1146，执行continue操作，并输出“not found”的信息。
方法三：先定义条件，然后调用条件。
方法四：使用sqlwarning捕获以01开头的sqlstate_value值，然后执行exit操作，并输出“not found”的信息。
方法五：使用sqlexception捕获不是01和02开头的sqlstate_value值，然后执行exit操作，并输出“not found”的信息。
光标的使用
光标必须声明在处理程序之前，并声明在变量和条件之后
- 声明光标
delimiter $$
CREATE PROCEDURE proc_test2()
BEGIN
declare cur_emplyee cursor
for select name,age from t_employee;
END;
$$
delimiter ;
- 打开光标
open cur_emplyee;
- 打开光标
fetch cur_emplyee into emp_name,emp_age;
- 关闭光标
close cur_emplyee;
- 完整使用
CREATE PROCEDURE proc_test2()
BEGIN
DECLARE emp_name varchar(10);
DECLARE emp_age int(10);
declare cur_emplyee cursor
for select name,age from t_employee;
open cur_emplyee;
fetch cur_emplyee into emp_name,emp_age;
close cur_emplyee;
END;
$$
delimiter ;
流程控制的使用
- IF语句:条件判断
if age>20 then set @count1=@count1+1;
elseif age = 20 then @count2 = @count2+1;
else @count3=@count+1;
end if;
- case语句：条件判断
case age
when 20 then set @count1=@count1+1;
else set @count2=@count2+1;
end case;
- loop语句：循环，必须遇到leave才能停止循环
add_num:LOOP
set @count = @count+1;
if @count = 100 then
leave add_num;
end loop add_num;
- iterate语句：跳出本次循环直接进入下一次循环
add_num:Loop
set @count = @count+1;
if @count = 100 then
leave add_num;
else IF MOD(@count,3) = 0 then
iterate add_num;
select * from employee;
end loop add_num;
- repeat：条件控制循环
repeat
set @count = @count+1;
until @count = 100
end repeat;
- while：当满足条件时，执行循环内的语句
while @count<100 do
set @count=@count+1;
end while;
调用存储过程
delimiter $$
create procedure proc_employee_sp(in empid int,out sal int)
comment'查询某个员工薪水'
BEGIN
select salary
from t_employee
where id = empid;
end;
$$
delimiter ;
call proc_employee_sp(1001,@sal)
调用存储函数
delimiter $$
create function func_employee_sp(id int)
RETURNS int
BEGIN
return (select salary
from t_employee
where t_employee.id=id);
end;
$$
delimiter ;
select func_employee(1002);
查看存储函数和过程
show procedure status like 'proc_employee_sp' ;
show function status like 'func_employee_sp' ;
show create procedure proc_employee_sp;
show create function func_employee_sp;
select * from information_schema.Routines
where routine_name = 'proc_employee_sp';
select * from information_schema.Routines
where routine_name = 'func_employee_sp';
修改存储过程和函数
- 修改存储过程
把存储过程proc_employee的读写权限改为MODIFIES SQL DATA，并指明调用者（SQL SECURITY invoker）可以执行
alter procedure proc_employee
MODIFIES SQL DATA SQL SECURITY invoker;
测试下是否进行了修改
select specific_name,sql_data_access,security_type
from information_schema.ROUTINES
where routine_name = 'proc_employee';
可以发现数据权限已经改为MODIFIES SQL DATA，安全类型也变为了invoker
- 修改存储函数
将该存储函数读写权限改为reads SQL DATA，并加上注释信息
alter function func_employee
reads SQL data comment 'find name';
测试下是否进行了修改
select specific_name,sql_data_access,routine_comment
from information_schema.ROUTINES
where routine_name = 'func_employee';
删除存储过程和函数
drop procedure proc_employee;
drop function func_employee;
综合案例
数据源
create table t_employee(
id int(4),
name varchar(20),
gender varchar(6),
age int(4),
salary int(6),
deptno int(4));
insert into t_employee(id,name,gender,age,salary,deptno)
values
(1001,'Alicia Florric','Female',33,10000,1),
(1002,'Kalinda Sharma','Female',31,9000,1),
(1003,'Cary Agos','Male',27,8000,1),
(1004,'Eli Gold','Male',44,20000,2),
(1005,'Peter Florric','Male',34,30000,2),
(1006,'Diane Lockhart','Female',43,50000,3),
(1007,'Maia Rindell','Female',27,9000,3),
(1008,'Will Gardner','Male',36,9000,3),
(1009,'Jackquiline Florriok','Female',57,7000,4),
(1010,'Zach Florriok','Male',17,5000,5),
(1011,'Grace Florriok','Female',14,4000,5);
- 【案例】创建一个存储函数：统计员工人数
delimiter $$
create function count_employee()
RETURNS int
BEGIN
return (select count(*) from t_employee);
END;
$$
delimiter ;
select count_employee() as count;
delimiter $$
create procedure count_salary()
BEGIN
select count_employee() as count;
select AVG(salary) as avgage from t_employee;
END;
$$
delimiter ;
call count_salary();
-
【练习】
-
第一题
delimiter $$
create procedure pro_employee(in param varchar(10),out result varchar(10))
begin
if (param = 'a') then
select AVG(age) from t_employee;
ELSEIF (param = 'b') THEN
select AVG(salary) from t_employee;
ELSEIF (param = 'c') THEN
select AVG(salary) from t_employee where gender = 'Female';
ELSEIF (param = 'd') THEN
select AVG(salary) from t_employee where gender = 'Male';
end if;
end;
$$
delimiter ;
call pro_employee('d',@result);
- 第二题
delimiter $$
create function fun_employee(param varchar(10))
RETURNS double(10,4)
BEGIN
if (param = 'a') then
return (select AVG(age) from t_employee);
ELSEIF (param = 'b') THEN
return (select AVG(salary) from t_employee);
ELSEIF (param = 'c') THEN
return (select AVG(salary) from t_employee where gender = 'Female');
ELSEIF (param = 'd') THEN
return (select AVG(salary) from t_employee where gender = 'Male');
end if;
END;
$$
delimiter ;
SELECT fun_employee('a');
- 第三题
drop PROCEDURE pro_employee;
drop FUNCTION fun_employee;
扫一扫
648
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
