oracle:触发、分页

oracle:触发、分页

分页解释:

int index=1;
int size=5;
int start=(index-1)*size+1;
int end=index*size;
--1 1 5
--2 6 10
where mr between 6 and 10

select * from(select a.*,rownum mr from emp a) where mr>=6 and mr<=10

触发:

create or replace function f(x number)
return number
is
begin
  if x=1 then return 1;
  else return f(x-1)+x;
  end if;
end;

select f(100) s from dual
--1 2 3 4  10 100
--1 3 6 10 55 5050

begin
  for i in 97..122
  loop
   dbms_output.put_line(chr(i));
  end loop;
end;
--ASC

create or replace trigger dept_line_delete
before delete on dept for each row
declare
v1 number;
begin
 select count(*)  into v1 from emp where deptno=:old.deptno;
 if v1>=1 then RAISE_APPLICATION_ERROR(-20000,’错误’);
 end if;
end;
--当EMP表中部门号存在时,该部门不允许删除