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的名称赋值给usernameselect 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 beginfetch 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;