—创建游标
cursor 游标名(参数名 参数类型) is 查询结果集
—使用步骤
1·声明游标
2·打开游标
open 游标名
3·从游标取数据
fetch 游标名 into 变量
游标名%found :找到数据
游标名%notfound :没有找到数据
4·关闭游标
close 游标名
—例:(无参数游标) 输出所有员工表中所有员工的姓名和工资
declare
—创建游标
cursor vrows is select * from emp
—声明变量,记录一行数据
vrow emp%rowtype
begin
—打开游标
open vrows;
—从游标提取数据,循环取数据
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbm_output.put_line(‘姓名:’ || vrow.ename || ‘工资’ || vrow.sal)
end loop;
—关闭游标
close vrows;
end;
—例:(有参数游标) 输出指定员工表中所有员工的姓名和工资
declare
—创建游标
cursor vrows(dno number) is select * from emp where deptno = duo;
—声明变量
vrow emp%rowtype;
begin
—打开游标
open vrows(10); —指定10号员工
—循环遍历,取数据
loop
fatch vrows into vrow;
exit when vrows%notfound;
dbm_output.put_line(‘姓名:’ || vrow.ename || ‘工资’ || vrow.sal)
end loop;
—关闭游标
close vrows;
end;
—例:(系统游标) 输出所有员工表中所有员工的姓名和工资
declare
—创建游标
cursor vrows sys_refcursor;
—声明变量,记录一行数据
vrow emp%rowtype
begin
—打开游标
open vrows for select * from emp;
—从游标提取数据,循环取数据
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbm_output.put_line(‘姓名:’ || vrow.ename || ‘工资’ || vrow.sal)
end loop;
—关闭游标
close vrows;
end;
—例:(使用for循环) 输出所有员工表中所有员工的姓名和工资
declare
—创建游标
cursor vrows sys_refcursor;
begin
—从游标提取数据,循环取数据
for vrow in vrows loop
dbm_output.put_line(‘姓名:’ || vrow.ename || ‘工资’ || vrow.sal)
end loop;
end;
采用for循环:
不需要声明变量
不需要打开游标
不需要关闭游标
—例:根据不同的职位涨工资,总裁涨1000,经理涨800,其他人涨400
declare
corsor vrows is select * from emp;
vrow emp%rowtype;
begin
open vrows;
loop
fetch vrows into vrow;
exit when crows%notfound;
if vrow.job = ‘zongcai’ then
update emp set sal = sal + 1000 where empno = vrow.empno;
else vrow.job = ‘jingli’ then
update emp set sal = sal + 800 where empno = vrow.empno;
else
update emp set sal = sal + 400 where empno = vrow.empno;
end if;
end loop;
close brows;
commit;
end;