[大数据]MySQL存储过程实例

1、简单实例

create procedure demo.p_test1()
begin
  -- 使用 declare语句声明一个变量
  declare id int default 0;
  declare name varchar(50) default '';
  -- 使用set语句给变量赋值
  set id=7521;
  -- 将users表中id=1的名称赋值给username
  select ename into name from demo.emp where empno=id;
  -- 返回变量
  select name;
end;

2、通过游标遍历结果集

create table demo.test(
	id int,
	cnt varchar(50)
)

create procedure demo.p_test2()
-- 通过游标遍历结果集
begin
  -- 声明变量
  declare id int default 0;
  declare name varchar(50) default '';
  declare done boolean default 1;
  -- 声明游标
  declare rs cursor for SELECT deptno,dname from demo.dept ;
  -- 定义异常：
  declare continue handler for SQLSTATE '02000' SET done = 0; 
  -- 打开游标
  open rs;
  delete from demo.test;
  while done do 
  	begin
	  fetch rs into id,name;
      INSERT into demo.test SELECT e.deptno,count(1) from demo.emp e WHERE e.deptno =id group by e.deptno;
     end ;
  end while;
  -- 关闭游标
  close rs;
end;

call demo.p_test2();

SELECT * from  demo.test;