mysql存储过程返回结果集_面试再问MySQL存储过程和触发器就把这篇文章给他

Mysql存储过程及触发器trigger

存储过程

一、一个简单的存储过程

1,一个简单的存储过程

delimiter $$create procedure testa()begin Select * from emp; Select * from dept; End;$$;delimiter ;-- 调用存储过程call testa();

存储过程的结构组成：

1，创建格式:create procedure 存储过程名

2，包含一个以上代码块，代码块使用begin和end之间

3，在命令行中创建需要定义分隔符 delimiter $$

2,存储过程的特点

1，能完成复杂的判断和运算

2，可编程性强，灵活

3，SQL编程的代码可重复使用

4，执行速度相对快

5，减少网络之间数据传输，节省开销

二、存储过程变量

1，存储过程中的变量

需求：编写存储过程，使用变量取empno=7369的用户名

Delimiter $$;Create procedure testa();BEGIN DECLARE my_uname varchar(32) default ""; -- 定义变量my_uname SET my_uname='smith'; -- 为变量my_uname赋值 -- 查询empno=7369的用户名，并将值赋给my_uname select ename into my_uname from emp where empno=7369; -- 为变量赋值 select my_uname;-- 返回my_uname的值END;$$;Delimiter ;

特点：

1，变量的声明使用declare,一句declare只声明一个变量，变量必须先声明后使用。

2，变量具有数据类型和长度，与mysql的SQL数据类型保持一致，还能指定默认值、字符集和排序规则等。

3，变量可以通过set来赋值，也可以通过select into的方式赋值。

4，变量需要返回，可以使用select语句，如：select 变量名

2，存储过程变量应用示例

需求：统计表emp、dept的行数和emp表中最早，最晚的入职日期。

Delimiter $$;Create procedure stats_emp();BEGIN-- 统计emp和dept表中的记录数 BEGIN DECLARE emp_sum int default 0; DECLARE dept_sum int default 0; select count(*) into emp_sum from emp;  select count(*) into dept_sum from dept;  select emp_sum,dept_sum; END;-- 统计最早、最晚入职日期 BEGIN DECLARE max_time TIMESTAMP ; DECLARE min_time TIMESTAMP; select max(hiredate),min(hiredate) into max_time,min_time from emp; select max_time,min_time; END;END$$;Delimiter ;

三、存储过程的参数

1，存储过程的传入参数IN

需求：编写存储过程，传入empno,返回该用户的ename.

Delimiter $$;Create procedure test_param(IN my_empno int);--------BEGIN DECLARE my_ename varchar(32) default ''; select ename into my_ename from emp where empno=my_empno; select my_ename;END;$$Delimiter ;-- 调用Call test_param(7369);

提示：

1，传入参数：类型为IN,表示该参数的值必须在调用存储过程时指定，如果不显示指定为IN，那么默认就是IN类型。

2，IN类型参数一般只用于传入，在调用存储过程中一般不作修改和返回。

3，如果调用存储过程中需要修改和返回值，可以使用OUT类型参数。

2，存储过程的传出参数OUT

需求：调用存储过程时，传入empno,返回该用户的ename。

Delimiter $$;create procedure test_param(IN my_empno int,OUT my_ename varcahr(32));--------BEGIN select ename into my_ename from emp where empno=my_empno; select my_ename;END;$$Delimiter ;-- 调用Set @uname=’’;Call test_param_out(7369,@uname);

提示：

1，传出参数：在调用存储过程中，可以改变其值，并可返回。

2，OUT是传出参数，不能用于传入参数值。

3，调用存储过程时，OUT参数也需要指定，但必须是变量，不能是常量。

4，如果既需要传入，同时又需要传出，则可以使用INOUT类型参数。

3,存储过程的可变参数INOUT

需求：调用存储过程时，参数my_empno和my_ename,既是传入，也是传出参数。

Delimiter $$;create procedure test_param_inout(INOUT my_empno int,INOUT my_ename varchar(32));BEGIN set my_empno=7369; set my_ename="smith";  select ename,empno into my_ename,my_empno from emp where empno=my_empno;END;$$Delimiter ;-- 调用set @uname:='';set @empno:=7399;call test_param_inout(@empno,@uname);select @empno,@uname;

特点：

1，可变变量INOUT,调用时可传入值，在调用过程中，可以修改其值，同时也可以返回值。

2，INOUT 参数集合了IN和OUT类型参数的功能

3，INOUT调用时传入的是变量，而不是常量

四、存储过程条件语句

1，存储过程的条件语句

需求：编写存储过程，如果用户empno是偶数则给出ename,其他情况只返回empno.

Delimiter $$;create procedure test_if(IN my_empno int);BEGINDECLARE my_ename VARCHAR(32) default '';  if(my_empno %2=0) then  select ename into my_ename from emp where empno=my_empno;select my_ename; else  select my_empno; end if;END;$$Delimiter ;-- 调用call test_if(7369);

特点：

1，条件语句最基本结构： if() then ... else ... end if;

2,if判断返回逻辑真或者假，表达式可以是任意返回真或假的表达式

2，存储过程的条件语句应用示例

需求：根据用户传入的empno参数判断：

(1)如果用户sal小于2000，则给用户加薪200

(2)如果用户sal小于1000，则给用户加薪500

(3)其他情况加薪100

Delimiter $$;create procedure test_if_else(IN my_empno int);BEGINDECLARE my_sal int default 0; select sal into my_sal from emp where empno=my_empno; if(my_sal<1000) then  update emp set sal=sal+500 where empno=my_empno;  ELSEIF(my_sal<2000) then update emp set sal=sal+200 where empno=my_empno;  else update emp set sal=sal+100 where empno=my_empno;  end if;END;$$Delimiter ;-- 调用call test_if_else(7369);

特点：

多条件判断结构：

If()

Then

...

Else if()

Then

...

Else

...

End if;

五、存储过程循环语句

1，while循环

需求：使用循环语句，向表emp中插入10条empno连续的记录。

Delimiter $$;create procedure test_while();BEGIN DECLARE i int default 0; while(i<10) DO BEGIN set i=i+1; insert into acc(id) values(i); END; END WHILE; END;$$Delimiter ;-- 调用call test_while();

特点：

1，while语句最基本结构： while() do begin ... end end while;;

2,while判断返回逻辑真或者假，表达式可以是任意返回真或假的表达式

2,repeat循环语句

需求：使用repeat循环向表acc插入10条id连续的记录

Delimiter $$;create procedure test_repeat();BEGIN DECLARE i int default 100; REPEAT BEGIN set i=i+1; insert into acc(id) values(i); END; UNTIL i>=110 END REPEAT;END;$$Delimiter ;-- 调用call test_repeat();

特点：

1，repeat语句最基本结构： repeat begin ... end until end repeat;;

2,while判断返回逻辑真或者假，表达式可以是任意返回真或假的表达式

六、存储过程游标的使用

1,什么是游标

需求：编写存储过程，使用游标，把uid为偶数的记录逐一更新用户名。

Delimiter $$;create procedure test_cursor();BEGIN DECLARE stopflag INT DEFAULT 0; -- 游标停止的标记 0：未停止 1：已停止 DECLARE my_uname VARCHAR(32) default ''; -- 存储查询出的用户名 DECLARE uname_cursor CURSOR for select uname from acc where uid%2=0; -- 定义游标uname_cursor，并指定结果集 DECLARE CONTINUE HANDLER for NOT found set stopflag=1; -- 游标结束后stopflag设置为1 open uname_cursor; -- 打开游标 FETCH uname_cursor into my_uname; -- -- 游标向前走一步，取出一条记录放到my_uname中my_uname WHILE(stopflag=0) DO BEGIN update acc set uname=CONCAT(my_uname,"_cur") where uname=my_uname; FETCH uname_cursor into my_uname;  END; end WHILE; close uname_cursor;END;$$Delimiter ;-- 调用call test_repeat();

特点：

declare uname_cur Cursor for select uname from acc where uid%2=0;

1，游标是保存查询结果的临时内存区域

2，游标变量uname_cur保存了查询的临时结果，实际上就是查询结果集

Declare continue handler for not found set stopflag=1;

3，当游标变量中保存的结果都查询一遍(遍历)，到达结尾，把变量stopflag设为1。

4， FETCH uname_cursor into my_uname; -- 游标向前走一步，取出一条记录放到my_uname中

Mysql函数

一、一个简单函数

需求：编写函数，传入一个uid,返回用户的uname

Delimiter $$; CREATE FUNCTION f01_simple(my_uid int) RETURNS varchar(32) CHARSET utf8BEGIN DECLARE my_uname varchar(32) default ''; select uname into my_uname from acc where uid=my_uid;RETURN my_uname;END$$Delimiter ;-- 调用Select f01_simple(2);

特点：

1，创建函数使用 create function 函数名(参数) return 返回值

2，函数体放在begin和end之间

3，return 指定函数的返回值

4，函数调用: select 函数名(实参);

二、自定义函数综合应用示例

1，自定义函数示例01

需求：输入用户uid,获得accountid,uid,uname组合的uuid值，作为用户的唯一标识。

Delimiter $$; CREATE FUNCTION test_uuid(my_uid int) RETURNS varchar(32) CHARSET utf8BEGIN DECLARE uuid varchar(32) default ''; select CONCAT(accountid,"_