【SQL报错】存储过程和函数中的定义变量报错、条件、光标

本文详细介绍了在MySQL中遇到存储过程和函数定义变量时的报错情况,包括解决方法如更改delimiter、调整DECLARE的位置。同时讲解了处理程序的不同方式,如根据sqlstate_value和sql_error_code进行捕获。还讨论了光标的使用规则,以及流程控制语句如IF、CASE、LOOP等。最后,提到了如何调用、修改、查看和删除存储过程和函数的实践操作。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

定义变量报错

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;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值