存储过程
语法grammar
语法结构
创建
delimiter //
create procedure hello()
begin
declare hellow varchar(32) default 'hellow'; -- 定义hellow变量
select hellow;
set hellow = 'helloworld'; -- 赋值
select hellow;
end //
调用
call hello();
调用结果：
删除
drop procedure hello;
变量
局部变量
作用于begin end 里的变量。
用户变量
在每次连接时可定义存在的变量。例如，打开终端打开mysql后输入密码，输入set @nu = nummm，之后在此次连接中，可以随时查询这个变量，select @nu 将会出现前面赋予的nummm。
会话变量
由系统提供，当前会话（连接）有效。
语法：
@@session.var_name
show session variables; -- 查看会话变量
select @@session.unique_checks; -- 查看某会话变量
set @@session.unique_checks = 0; --修改会话变量
全局变量
由系统提供，整个mysql服务器有效。
语法：
@@global.var_name
赋值
set
set @nu = nummm
into
select d.loc into dept_loc from dept d where d.dname= dept_name;
入参出参（in|out|inout）
in
delimiter //
create procedure inout_in(in upname varchar(32))
begin
set @up_name = upname;
end //
call inout_in('zhuyidan');
select @up_name;
运行结果：
out
delimiter //
create procedure inout_out(in dept_name varchar(32),out dept_loc varchar(64))
begin
select d.loc into dept_loc from dept d where d.dname= dept_name;
end //
call inout_out('SALES',@dept_name);
select @dept_name;
运行结果：
inout
delimiter //
create procedure inout_inout(in dept_loc varchar(64),inout dept_name varchar(32))
begin
-- 出参 加上dept_name带过来的字符串
declare d_name VARCHAR(32);
set d_name = dept_name;
select d.dname into dept_name from dept d where d.loc = dept_loc;
-- 用concat函数来拼接
select concat(dept_name,'-',d_name);
end //
set @dept_name = 'Hello';
call inout_inout('NEW YORK',@dept_name);
select @dept_name;
运行结果：
流程控制—判断
if
delimiter //
create PROCEDURE time_if(in emp_id int)
BEGIN
DECLARE sub_time int;
DECLARE result VARCHAR(32) character set gbk; -- 用character 来设置一下格式就可以避免报类似乱码的错误
SELECT TIMESTAMPDIFF(YEAR,hiredate,'2020-05-06') into sub_time FROM emp WHERE empno = emp_id;
IF sub_time <20 THEN set result = '新手';
ELSEIF sub_time <30 THEN set result = '老手';
ELSE set result = '老员工';
END IF;
SELECT result;
END //
运行结果：
case
delimiter //
CREATE PROCEDURE time_case(in emp_id int)
BEGIN
DECLARE sub_time int;
DECLARE result VARCHAR(32) character set gbk;
SELECT TIMESTAMPDIFF(YEAR,hiredate,'2020-05-06') into sub_time FROM emp WHERE empno = emp_id;
CASE
WHEN sub_time < 20 THEN set result = '新手';
WHEN sub_time < 30 THEN set result = '老手';
ELSE set result = '老员工';
END CASE;
SELECT result;
end //
CALL time_case(7369);
运行结果：
流程判断—循环
loop
delimiter //
CREATE PROCEDURE test_loop()
BEGIN
DECLARE n_index int DEFAULT 1;
DECLARE result_str VARCHAR(30) DEFAULT 0;
test_loo: LOOP
set result_str = CONCAT(result_str,',',n_index);
IF n_index >10 THEN LEAVE test_loo;
END IF;
set n_index = n_index + 1;
END LOOP test_loo;
SELECT result_str;
end //
CALL test_loop();
运行结果：
repeat
delimiter //
CREATE PROCEDURE test_repeat()
BEGIN
DECLARE n_index int DEFAULT 1;
DECLARE result_str VARCHAR(30) DEFAULT '1';
count_index:REPEAT
set n_index = n_index + 1;
set result_str = CONCAT(result_str,',',n_index);
UNTIL n_index > 10
END REPEAT count_index;
SELECT result_str;
END //
运行结果：
while
CREATE PROCEDURE test_while()
BEGIN
DECLARE n_index int DEFAULT 1;
DECLARE result_str VARCHAR(30) DEFAULT '1';
WHILE n_index < 10
DO
SET n_index = n_index + 1;
SET result_str = CONCAT(result_str,',',n_index);
END WHILE;
SELECT result_str;
END //
CALL test_while();
运行结果：
游标
语法
-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement
-- 打开语法
OPEN cursor_name
-- 取值语法
FETCH cursor_name INTO var_name [, var_name] ...
-- 关闭语法
CLOSE cursor_name
注hander语法
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement
handler_action: {
CONTINUE
| EXIT
| UNDO
}
condition_value: {
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
}
CONTINUE: Execution of the current program continues.
EXIT: Execution terminates for the BEGIN ... END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block.
SQLWARNING: Shorthand for the class of SQLSTATE values that begin with '01'.
NOT FOUND: Shorthand for the class of SQLSTATE values that begin with '02'.
SQLEXCEPTION: Shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.
-- 注各种写法
DECLARE exit HANDLER FOR SQLSTATE '42S01' set @res_table = 'EXISTS';
DECLARE continue HANDLER FOR 1050 set @res_table = 'EXISTS';
DECLARE continue HANDLER FOR not found set @res_table = 'EXISTS';
tips
在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。
程序演示
delimiter //
CREATE PROCEDURE test_cursor(in dept_name VARCHAR(32))
BEGIN
DECLARE e_no int;
DECLARE e_name VARCHAR(32);
DECLARE e_sal DECIMAL(7,2);
-- 定义一个布尔值，用于后面的错误判断
DECLARE fl_flag BOOLEAN DEFAULT TRUE;
-- 定义游标
DECLARE t_cursor cursor for
SELECT e.empno,e.ename,e.sal from emp e,dept d WHERE e.deptno = d.deptno and d.dname = dept_name;
-- hander 句柄 注：1329 错误为在不使用hander下程序的循环报错，找不到数据，该查询总共只有两行数据，当fetch抓取了两行后抓第三行便开始报错，后无法走出循环。
DECLARE CONTINUE HANDLER for 1329 set fl_flag = false;
OPEN t_cursor;
cur_loop: LOOP
FETCH t_cursor into e_no,e_name,e_sal;
-- 用hander使fl——flag值为false，跳出循环
IF fl_flag = FALSE THEN
LEAVE cur_loop;
END IF;
SELECT e_no,e_name,e_sal;
END LOOP cur_loop;
-- 检验是否跳出循环
set @flag = 'end';
CLOSE t_cursor;
END //
call test_cursor('RESEARCH');
SELECT @flag;
运行结果：
1
2
存储函数
语法
--创建
CREATE FUNCTION <函数名>([<参数名> <数据类型>]) RETURNS <数据类型>
BEGIN
<SQL块>
RETURN <返回值>;
END;
--调用
SELECT <函数名>([<参数>]);代码示例
-- 定义一个存储函数，获取满足条件的总记数
delimiter //
CREATE FUNCTION fun1(in dept_no int)
RETURNS int
BEGIN
DECLARE d_no int;
SELECT count(*) INTO d_no FROM emp WHERE deptno = dept_no;
return d_no;
end //
select fun1(30);文末：附建表查询：
-- dept表
CREATE TABLE dept(
deptno int COMMENT '部门编号',
dname VARCHAR(32) COMMENT '部门名称',
loc VARCHAR(64) COMMENT '部门地址';)
-- emp表
create table emp(
empno int COMMENT'员工编号',
ename VARCHAR(32) COMMENT '员工姓名',
job VARCHAR(10) COMMENT '职位',
mgr int COMMENT '上级编号',
hiredate date COMMENT '入职时间',
sal DECIMAL(7,2) COMMENT '薪资',
comm DECIMAL(7,2) COMMENT '年终奖金',
deptno INT COMMENT '部门编号');
-- dept表数据插入
insert into dept VALUES (10,'ACCOUNTING','NEW YORK');
insert into dept VALUES (20,'RESEARCH','DALLAS');
insert into dept VALUES (30,'SALES','CHICAGO');
insert into dept VALUES (40,'OPERATIONS','BOSTON');
-- emp表数据插入
INSERT INTO emp VALUES (7369,'smith','clerk','7902','1980-12-17','800',NULL,20);
INSERT INTO emp VALUES (7499,'allen','salesman','7698','1981-02-20','1600','300',30);
INSERT INTO emp VALUES (7521,'ward','salesman','7698','1981-10-17','1250','500',30);
INSERT INTO emp VALUES (7566,'jones','manager','7839','1981-09-28','2975',NULL,10);
-- emp表数据插入
INSERT INTO emp VALUES (7369,'smith','clerk','7902','1980-12-17','800',NULL,20);
INSERT INTO emp VALUES (7499,'allen','salesman','7698','1981-02-20','1600','300',30);
INSERT INTO emp VALUES (7521,'ward','salesman','7698','1981-10-17','1250','500',30);
INSERT INTO emp VALUES (7566,'jones','manager','7839','1981-09-28','2975',NULL,10);
716
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
