2018-1-9

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;