set serveroutput on; //首先将此语句打开才能使得pl/sql程序能够输出结果
(一) 游标
1.显式游标
(1)不带参数的显式游标(强调游标的4个步骤)
声明一个游标,读取学生的学号
declare
cursor c_1 is select xh from xs; //定义游标
v_xh xs.xh%type;
begin
open c_1; //打开游标
fetch c_1 into v_xh; //检索游标
dbms_output.put_line(v_xh);
dbms_output.put_line(v_xh||' '||c_1%rowcount);
close c_1; //关闭游标
end;
(2)游标与表的%rowtype结合
declare
cursor c_2 is select * from xs;
v_1 xs%rowtype;
begin
open c_2;
fetch c_2 into v_1;
dbms_output.put_line(v_1.xh); //括号内必须具体
close c_2;
end;
(3)带参数的游标
declare
cursor c_3(v_xb xs.xb%type) is select * from xs where xb=v_xb;
v_2 xs%rowtype;
begin
open c_3('男'); //打开游标,参数为‘男’,表示读取信息为男同学信息
fetch c_3 into v_2;
dbms_output.put_line(v_2.xh||' '||c_3%rowcount);
close c_3;
end;
(4)游标的%rowtype类型
declare
cursor c_4 is select xs.xh,xm,kch,cj from xs,xs_kc where xs.xh=xs_kc.xh;
v_3 c_4%rowtype;
begin
open c_4;
fetch c_4 into v_3;
dbms_output.put_line(v_3.xh||' '||v_3.xm||' '||v_3.kch||' '||v_3.cj);
close c_4;
end;
2.显式游标的属性
1)%FOUND
布尔型,如果最近一次使用FETCH语句,有返回结果则为TRUE,否则为FALSE;
2)%NOTFOUND
布尔型,如果最近一次使用FETCH语句,没有返回结果则为TRUE,否则为FALSE;
3)%ROWCOUNT
数值型,返回到目前为止从游标缓冲区检索的元组数。
4)%ISOPEN
布尔型。如果游标已经打开,返回TRUE,否则为FALSE。
例如:游标的%isopen 属性练习
declare
cursor c_1 is select * from xs;
v_1 c_1%rowtype;
begin
if c_1%isopen=false then
open c_1;
end if;
fetch c_1 into v_1;
dbms_output.put_line(v_1.xh||v_1.xm||v_1.zxf);
close c_1;
end;
3.游标的遍历
Ⅰ.while循环
1)用游标遍历xs表中的xh
declare
cursor c_6 is select xh from xs;
v_xh xs.xh%type;
begin
open c_6;
fetch c_6 into v_xh; //打开游标后用fetch语句先取一行到变量
while c_6%found
loop
dbms_output.put_line(v_xh);
fetch c_6 into v_xh;
end loop;
close c_6;
end;
2)利用游标while循环统计并输出scott.emp表各个部门的平均工资;若平均工资大于2000,则输出“该部门平均工资较高。”
declare
cursor c1 is select deptno,avg(sal) avgsal from scott.emp group by deptno;
v_dept c1%rowtype;
begin
open c1;
fetch c1 into v_dept;
while c1%found
loop
dbms_output.put_line('部门号为'||v_dept.deptno||' '||'平均工资为'||trunc(v_dept.avgsal,1));
if (v_dept.avgsal>=2000) then
dbms_output.put_line(v_dept.deptno||'号部门工资较高');
end if;
fetch c1 into v_dept;
end loop;
close c1;
end;
Ⅱ.for循环
1)遍历xs表中的xh
declare
cursor c_5 is select xh from xs;
v_xh xs.xh%type;
begin
open c_5;
loop
fetch c_5 into v_xh;
exit when c_5%notfound;
dbms_output.put_line(v_xh);
end loop;
close c_5;
end;
2)利用for循环统计并输出各个部门的平均工资
⑴
declare
cursor c1 is select deptno,avg(sal) avgsal from scott.emp group by deptno;
v_dept c1%rowtype;
begin
for v_dept in c1
loop
dbms_output.put_line('部门号为'||v_dept.deptno||' '||'平均工资为'||trunc(v_dept.avgsal,1));
end loop;
end;
(2)//比第一种省略了v_dept,系统会自动将v_dept看作c1%rowtype类型
declare
cursor c1 is select deptno,avg(sal) avgsal from scott.emp group by deptno;
begin
for v_dept in c1
loop
dbms_output.put_line('部门号为'||v_dept.deptno||' '||'平均工资为'||trunc(v_dept.avgsal,1));
end loop;
end;
(3)//更简化,直接使用select 子查询代替游标名,但不推荐使用此种方式
declare
begin
for v_dept in(select deptno,avg(sal) avgsal from scott.emp group by deptno)
loop
dbms_output.put_line('部门号为'||v_dept.deptno||' '||'平均工资为'||trunc(v_dept.avgsal,1));
end loop;
end;
4.使用游标更新数据(带for update )
1)在Scott.emp表中,利用游标,给工资低于1200 的员工增加工资50。并输出“编码为’员工编码号’的工资已经更新”。
declare
v_1 scott.emp.empno%type;
v_sal scott.emp.sal%type;
cursor c1 is select empno,sal from scott.emp where sal<1200 for update; //此处要for update
begin
open c1;
loop
fetch c1 into v_1,v_sal;
exit when c1%notfound;
update scott.emp set sal=sal+50 where current of c1; //注意
dbms_output.put_line('雇员号为'||v_1||'工资已更新');
dbms_output.put_line('记录数:'||c1%rowcount);
end loop;
close c1;
end;
2)修改scott.emp表员工的工资,如果员工的部门号为10,工资提高100;部门号为20,工资提高150;部门号为30,工资提高200;否则工资提高250。 (与分支结构结合)
declare
cursor c1 is select * from scott.emp for update;
v_1 c1%rowtype;
v_zl scott.emp.sal%type;
begin
for v_1 in c1
loop
case v_1.deptno
when 10 then v_zl:=100;
when 20 then v_zl:=150;
when 30 then v_zl:=200;
else v_zl:=250;
end case;
update scott.emp set sal=sal+v_zl where current of c1;
end loop;
end;
3)修改emp表的工资,工资不足1000的,调整为1500,工资高于1000的,调整为原来工资的1.5倍,调整后,若工资〉10000,则设其为10000。
declare
cursor c1 is select empno,sal from scott.emp for update of sal nowait; //注意
v_sal scott.emp.sal%type;
begin
for v1 in c1
loop
if v1.sal<=1000 then v_sal:=1500;
else v_sal:=v1.sal*1.5;
if v_sal>10000 then v_sal:=10000;
end if;
end if;
update scott.emp set sal=v_sal where current of c1;
end loop;
end;
5.游标变量
创建游标变量的两个步骤
①定义REF CURSOR 类型 即引用游标类型
②声明这种类型的游标变量
declare
type t_dept is ref cursor; //注意定义方式
c_1 t_dept;
v_row scott.emp%rowtype;
begin
open c_1 for select * from scott.emp where deptno=10; //注意
fetch c_1 into v_row;
dbms_output.put_line(v_row.empno||' ' ||v_row.job);
close c_1;
open c_1 for select * from scott.emp where sal>=2000;
fetch c_1 into v_row;
dbms_output.put_line(v_row.deptno||' ' ||v_row.job);
close c_1;
end;
(二)异常处理
(1)预定义的oracle异常
1)
declare
x number;
begin
x:='aaaa';
exception
when value_error then dbms_output.put_line('数据类型错误');
end;
2)
declare
v_result xs.xm%type;
begin
select xh into v_result from xs where xm='李 明';
dbms_output.put_line('The student number is '||v_result);
exception
when too_many_rows then
dbms_output.put_line('there has too_many_rows error!');
when no_data_found then
dbms_output.put_line('there has no_data_found error!');
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
3)
declare
v_result xs.zxf%type;
begin
select xh into v_result from xs where xm='李明';
dbms_output.put_line('The student number is '||v_result);
exception
when too_many_rows then
dbms_output.put_line('there has too_many_rows error!');
when no_data_found then
dbms_output.put_line('there has no_data_found error!');
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
(2)自定义异常处理
declare
e_1 exception;
v_sal scott.emp.sal%type;
begin
update scott.emp set sal=sal+4000 where empno=7788;
select sal into v_sal from scott.emp where empno=7788;
if v_sal>6000 then raise e_1; //抛出异常
end if;
exception
when e_1 then dbms_output.put_line('The salary is too large!');
rollback; //回滚,还是回到原来的值
end;