游标、例外、存储过程、存储函数、java调用存储过程、触发器(Oracle之二)

--游标(光标) 一般用于多行数据

语法:cursor 游标名称 (参数名 参数类型 ..) is select语句

--操作游标

open 游标名; --打开游标

loop

  fetch 游标名 into 记录型变量; --取出一条记录，放入记录型变量中

  exit when 游标名%notfound; --当游标取空，退出

  (具体操作)

end loop;

close 游标名; --关闭游标

--1、用游标方式输出emp表中的员工编号和姓名

declare

  cursor empList is select * from emp;

  pemp emp%rowtype;

begin

  open empList;

  loop

    fetch empList into pemp;

    exit when empList%notfound;

    dbms_output.put_line(pemp.empno||'-'||pemp.ename);

  end loop;

  close empList;

end;

--2、写一段PL/SQL程序，为部门号=10的员工涨工资

declare

  cursor addSal is select * from emp where deptno=10;

  pemp emp%rowtype;

begin

  open addSal;

  loop

    fetch addSal into pemp;

    exit when addSal%notfound;

    update emp set sal = sal +100 where empno = pemp.empno;

    commit;

  end loop;

  close addSal;

end;

--3、写一段PL/SQL程序，某部门号的员工涨工资

declare 

  cursor addSal (dpn number) is select * from emp where deptno=dpn;

  pemp emp%rowtype;

begin

  open addSal (20); --此时指定实参

  loop

    fetch addSal into pemp;

    exit when addSal%notfound;

    update emp set sal = sal +1 where empno = pemp.empno;

    commit;

  end loop;

  close addSal;

end;

--exception 例外 (异常)

declare

begin 

  

[exception]

end;

--系统定义的异常

no_data_found --没有找到数据

too_many_rows --select..into语句匹配多个行

zero_divide   --被零除

value_error   --算数或转换错误

timeout_on_resource --等待资源超时

declare

  pnum number;

begin

  --pnum:=1/0;

  pnum:='数字类型赋值字符串';

  exception

    when zero_divide then 

      dbms_output.put_line('被零除异常');

    when value_error then 

      dbms_output.put_line('算数或转换错误');

    when others then 

      dbms_output.put_line('其他异常');

end;

--自定义的异常

--1、查询部门编号是50 的员工，如果没有员工，则提示'没有找到员工'

declare

  cursor dept_emp is select * from emp where deptno = 50;

  pemp emp%rowtype;

  no_found_emp exception; --声明异常

begin

  open dept_emp;

    fetch dept_emp into pemp;

    if dept_emp%notfound then 

      raise no_found_emp; --抛出异常

    end if;

  close dept_emp;

  exception

    when no_found_emp then --捕获异常

      dbms_output.put_line('没有找到员工');

end;

--存储过程:把pl/sql提前封装(编译)好，放在服务端，以便于再次调用

语法:

create [or replace] procedure 名称[(参数名 in/out 参数类型)]

as|is

begin

  PLSQL程序

end[存储过程名称];

--1、给指定的员工涨100工资，并打印出涨前和涨后的工资

create procedure addSal(epn in  number) --创建存储过程

as

  be_sal number;

  af_sal number;

begin

  select sal into be_sal from emp where empno=epn;

  update emp set sal =sal+100 where empno=epn;

  commit;

  select sal into af_sal from emp where empno=epn;

  dbms_output.put_line(be_sal||'-'||af_sal);

end;

--调用存储过程

--第一种

call addSal(7369);

--第二种:在pl/sql调用

declare

begin

  addSal(7369);

end;

--2、使用存储过程来查询指定员工的年薪

create procedure yearSal(epn in number,total out number)

as 

  psal number;

  pcomm number;

begin

  select sal,comm into psal,pcomm from emp where empno=epn;

  total:=psal*12+nvl(pcomm,0);

end;

--调用存储过程

declare

  total number;

begin

  yearSal(7369,total);

  dbms_output.put_line(total);

end;

--3、查询指定[部门]的员工

create procedure empList (dpn in number,cur_emp out sys_refcursor) --游标型变量

as

begin

  open cur_emp for select * from emp where deptno=dpn;--给游标型变量放入多行结果集

end;

declare  --调用

  cur_emp sys_refcursor;

  pemp emp%rowtype;

begin

  empList(10,cur_emp); --调用存储过程

  loop

    fetch cur_emp into pemp;

    exit when cur_emp%notfound;

    dbms_output.put_line(pemp.empno||'-'||pemp.ename);

  end loop;

  close cur_emp; --关闭游标

end;

--存储函数

语法:

create [or replace] function 函数名(Name in type,Name out type ..) return 数据类型

as|is

  结果变量 数据类型;

begin

  PLSQL程序;

  return (结果变量);

end[函数名];

--1、使用存储函数来查询指定员工的年薪

create or replace function f_getYearSal(epn in number) return number

as

  psal number;

  pcomm number;

begin

  select sal,comm into psal,pcomm from emp where empno=epn;

  return psal*12+nvl(pcomm,0);

end;

declare --调用存储函数

  total number;

begin

  total:=f_getYearSal(7369);

  dbms_output.put_line(total);

end;

--函数和过程的区别

存储函数有返回值，但是过程和函数都可以使用out类型的参数返回一个或者多个值

--用java调用存储过程

  |-先用java查询emp

  |-调用存储过程(yearsal)

  |-调用(empList)

--触发器

语法:

  create [or replace] trigger 触发器名称

  before|after

  insert|delete|update [of 列名]

  on 表名

  [for each row]

  declare 

  begin

  end;

--例如：

create table person(

       id number(10),

       name varchar2(20)

)

--插入员工后打印一句话'一个新员工插入成功'

create trigger addPerson

after

insert

on person

for each row

declare

begin

  dbms_output.put_line('一个新员工插入成功');

end;  

insert into person values(1,'张三'); --测试

--不能在指定星期几增加新员工(此时是星期五)

--报错

raise_application_error(-20001,'不能再今天插入新员工') -- -20001~20999

create trigger noAdd

before

insert

on person

declare

  week varchar2(20);

begin

  select to_char(sysdate,'day') into week from dual;

  if trim(week)='friday' then

    raise_application_error(-20001,'不能在星期五增加员工');

  end if;

end;

insert into person values(1,'张三'); --测试

--emp表中，判断员工涨工资的工资值一定大于涨工资之前的工资

只有在for each row出现时可以使用 :old :new 

:old--伪记录型变量 某个操作之前的数据

:new--伪记录型变量 某个操作之后的数据

create or replace trigger addSal

before

update

on emp

for each row

declare

begin

  if :old.sal>:new.sal then

    raise_application_error(-20002,'涨工资的工资值一定大于涨工资之前的工资');

  end if;

end;

update emp set sal = sal-1 where empno=7369;--测试

update emp set sal = sal+1 where empno=7369;--测试

--实现主键自增列

--建表

create table test_user(

       id number(6) primary key,

       name varchar2(20) not null

)

--创建序列

create sequence seq_user;

--创建触发器

create trigger seq_pk

before

insert

on test_user

for each row

declare

begin

  select seq_user.nextval into :new.id from dual;

  --将自增主键 赋值给即将插入数据的主键

end;

insert into test_user(name) values('李四');

待续……