1、使用for循环来提取使用游标,for循环可以自动的打开游标,每一次循环都可以自动的提取一次游标,循环结束后会自动关闭游标
格式:
for 变量名 in 游标名 loop
使用游标语句
end loop;
例:1、declare
cursor cur is
select t.tno,t.tname,t.tage
from teacher t;
begin
for c in cur loop
dbms_output.put_line(c.tno ||','||c.tname ||','||c.tage );
end loop;
end;
2、/*利用游标,调整员工工资:
0——8500 5%(不含8500)
8500——9000 3%(不含9000)
9000—— 1%*/
declare
cursor cur is
select t.tno,t.tname,t.sal
from teacher t;
v_temp number(8,2);
begin
for c in cur loop
if c.sal<8500
then v_temp:=0.05;
elsif c.sal<9000
then v_temp:=0.03;
else v_temp:=0.01;
end if;
update teacher set sal=sal*(1+v_temp)
where tno=c.tno;
end loop;
end;
2、异常处理
Oracle为了在程序中解决异常的问题,提供了exception代码块,用来解决程序运行发生的异常
1、预定义的异常处理:oracle提供了一些常见的错误,并为它们起好了名字。
格式:exception
when 错误名 then 处理语句
例:declare
v_tno teacher.tno%type;
begin
select tno into v_tno from teacher;
exception
when too_many_rows then
dbms_output.put_line('对不起,您查询的结果过多');
end;
2、非预定义的异常处理:oracle没有指定名字的错误信息,需要我们自己来指定,在声明部分定义变量,并且为该变量关联错误编号。
格式:声明部分定义变量:变量名 exception
pragma exception_init(变量名,错误编号);
使用方法和预定的异常相同
例:declare
deletebyid exception;
pragma exception_init(deletebyid,-02292);
begin
delete from dept d where d.dno='5';
exception
when deletebyid then
dbms_output.put_line('对不起,您所要删除的系别正在被使用,无法删除');
end;
3、自定义异常处理:在程序的执行体中,可以使用 raise 异常变量名 来将定义的异常抛出。抛出后,就可以在exception代码块中像处理预定义异常一样的写法去处理。
例:declare
e_sal_toohigh exception;
v_sal tempteacher.sal%type;
begin
select sal into v_sal from tempteacher where tno=20;
if v_sal > 30000 then
raise e_sal_toohigh; /*将定义的异常抛出*/
else
dbms_output.put_line(v_sal);
end if;
exception
when e_sal_toohigh then
dbms_output.put_line('对不起,该员工工资异常');
end;
3、函数
函数可以将pl/sql代码保存在数据库中,供其他程序调用
格式:create or replace function 函数名(参数)
return 返回值类型
is
定义变量名
begin
程序体
end;
例:1、create or replace function goodbye
return varchar2
is
begin
return='再见';
end;
1、select goodbye from dual;
2、begin 函数的调用
dbms_output.put_line(goodbye);
end;
2、/*编写一个函数,返回当前系统时间,并调用该函数*/
create or replace function clock
return date
is
v_clock date;
begin
select sysdate into v_clock from dual;
return v_clock;
end;
1、select clock from dual;
2、begin 调用函数
dbms_output.put_line(clock);
end;
3、/*编写一个函数,传入字符串xxx,返回 hello,xxx*/
create or replace function hellostr(str varchar2) 定义可传入字符串函数
return varchar2
is
begin
return 'hello,'||str;
end;
begin
dbms_output.put_line(hellostr('Tom'));
end; 调用函数
select hellostr('Tom') from dual;
4、/*输入两个数,求和*/
create or replace function sumfun(a number,b number)
return number
is
s number;
begin
s:=a+b;
return s;
end;
select sumfun(6,3) from dual;
5、/*输入一个系别编号,得到该系别所有老师的工资总和*/
create or replace function sumsal(v_dno teacher.dno%type)
return teacher.sal%type
is
v_sum teacher.sal%type;
begin
select sum(t.sal) into v_sum from teacher t where t.dno = v_dno;
return v_sum;
end;
4、存储过程
存储过程也可以将PL/SQL保存到数据库中,供其他程序调用。
格式:
create or replace procedure 过程名(参数)
is
定义变量
begin
程序体
end;
注意:存储过程和函数的差别在于存储过程没有返回值,而函数则有返回值
例:1、create or replace procedure
insdept(v_dno dept.dno%type,v_dname dept.dname%type,v_stucount dept.stucount%type)
is
begin
insert into dept values (v_dno,v_dname,v_stucount);
dbms_output.put_line('数据插入成功');
end;
2、create or replace procedure deldept(dno dept.dno%type)
is
e_del exception;
v_count number;
begin
select count(*)into v_count from dept d where d.dno=v_dno;
if v_count >0
then delete from dept where dno=v_dno;
dbms_output.put_line('删除成功');
else
raise e_del;
end if;
exception
when e_del then
dbms_output.put_line('对不起,传入的编号不存在!') ;
end;
5、删除函数和存储过程
删除函数——drop function 函数名
删除存储过程——drop procedure 存储过程名
6、触发器
定义:触发器是在根据指定表中记录被新增,修改或者删除时所触发执行的PL/SQL代码。它可以设置为before或after。同时还可以设置为行级或语句级(for each row表示行级)
那么对于每一张表,可以创建12种触发器
格式:
create or replace trigger 触发器名
before 或 after
insert 或 update 或 delete
[for each row]
begin
执行代码
end;
例:create or replace trigger hellotrigger
after update on tempteacher
for each row
begin
dbms_output.put_line('helloworld!');
end;
注意:在触发器中可以使用:old来表示操作之前的数据;使用new来表示操作之后的数据
例:create table my_teacher as select t.tno,t.tname,t.tage from teacher t where 1=2;
create table my_teacher_bak as select t.tno,t.tname,t.tage from teacher t where 1=2;
create or replace trigger bak_my_teacher
after delete on my_teacher
for each row
begin
insert into my_teacher_bak values(:old.tno,:old.tname,:old.tage);
end;