概念:
光标又名游标,是一个PL/SQL结构,利用光标可以命名这些工作区,并通过光标访问工作区中的信息。
语法:
cursor 光标名(参数列表)
is
select。。。
属性:
%isopen 判断光标是否打开,所以第一步打开光标;
%rowcount 判断光标已经获取数据的行数;
%found 和 %notfound 判断是否还有下一行;
模板套路(需要记住,):
decare
cursor 光标名(cemp) is select .....
定义变量:
begin
open cemp;
loop
exit when cemp%notfound;
fecth cemp into....
end loop;
close cemp;
end;
举例说明
1 查询并打印所有员工的姓名及薪水;
declare
--定义光标cemp
cursor cemp is select ename,sal from emp;
--定义两个变量,用于存放读取的ename和sal数据;
pename emp.ename%type;
psal emp.sal%type;
begin
open cemp; --打开光标;
loop---循环,获取每行数据
fetch cemp into pename,psal;--获取当前行元素,移到下一行;
exit when cemp%notfound;
dbms_output.put_line(pename||psal);
end loop;
close cemp; --关闭光标
end;
--select empno,ename,sal from emp;
set SERVEROUTPUT ON;
declare
CURSOR cemp is select empno,ename,sal from emp;
cempno emp.empno%type;
cename emp.ename%type;
csal emp.sal%type;
begin
open cemp;
loop
fetch cemp into cempno,cename,csal;
exit when cemp%notfound;
DBMS_OUTPUT.PUT_LINE('部门编号为:'||cempno||'的姓名是'||cename||'的工资'||csal);
end loop;
DBMS_OUTPUT.PUT_LINE('完毕');
close cemp;
end;
--select empno,ename,12*sal+nvl(comm,0),deptno from emp;
declare
--定义光标cemp
CURSOR cemp is select empno,ename,12*sal+nvl(comm,0),deptno from emp;
--定义变量,用于存放变量值;
cempno emp.empno%type;
cename emp.ename%type;
csal emp.sal%type;
cdeptno emp.deptno%type;
begin
open cemp;
loop
exit when cemp%notfound;
FETCH cemp INTO cempno,cename,csal,cdeptno;
DBMS_OUTPUT.PUT_LINE('部门编号为:'||cempno||' 员工姓名:'||cename||' 年薪是:'||csal);
end loop;
close cemp;
end;
2 涨工资,如果是president 涨工资+1000,经理+500,其他的涨300。
--select ename,job from emp;
declare
cursor cemp is select empno,job from emp;
cempno emp.empno%type;
cjob emp.job%type;
begin
open cemp;
loop
exit when cemp%notfound;
FETCH cemp INTO cempno,cjob;
if cjob='PRESIDENT' then update emp set sal=sal+1000 where empno=cempno;
elsif cjob='PRESIDENT' then update emp set sal=sal+500 where empno=cempno;
else update emp set sal=sal+300 where empno=cempno;
end if;
end loop;
DBMS_OUTPUT.PUT_LINE('ok');
close cemp;
commit;
end;
3 查询某个部门的员工姓名,带参数的光标,通过参数决定部门;
set serveroutput on;
declare
cursor cemp(dno number) is select ename from emp where deptno = dno;--部门的所有名字存放在光标里面;
pename emp.ename%TYPE;
begin
open cemp(30);
loop
fetch cemp into pename;
exit when cemp%notfound;
dbms_output.put_line('...'||pename);
end loop;
close cemp;
end;