oracle(cursor、exception、procedure、function、trigger)例子

555
declare
  cursor vrows is select * from emp;

  vrow emp%rowtype;
begin
  open vrows;
  loop
    fetch vrows into vrow;
    exit when vrows%notfound;
    dbms_output.put_line('name: ' '' vrow.ename '' ' sal: ' '' vrow.sal);
  end loop;
  close vrows;
end;  

--------for循环遍历游标
declare
  cursor vrows is select * from emp;
begin
  for vrow in vrows loop
    dbms_output.put_line('name: ' '' vrow.ename '' ' sal: ' '' vrow.sal);
  end loop;
end;

-------自定义异常
declare
  vrow emp%rowtype;

  --自定异常
  no_emp exception;
begin
  select * into vrow from emp;
  if vrow.sal is null then
    raise no_emp;
  end if;
exception
  when no_emp then
    dbms_output.put_line('customize exception happened');
  when others then
    dbms_output.put_line('other exception happened' '' sqlerrm);
end;

------存储过程
create or replace procedure pro_updatesal(vempno in number, vnum in number)
is
current_sal number;
begin
  select sal into current_sal from emp where empno = vempno;
  --print salary before update sal
  dbms_output.put_line('before: ' '' current_sal);
  update emp set sal = sal + vnum where empno = vempno;
  --print salary after update sal
  dbms_output.put_line('after: ' '' (current_sal + vnum));
  commit;
end;

call pro_updatesal(7839, -10);

---
create or replace procedure proc_gettotalsal(vempno in number, tsal out number)
is
begin
  select sal*12 + nvl(comm, 0) into tsal from emp where empno = vempno;
end;

------函数
create or replace function fun_getannualsalary(vempno number) return number
is
        annual_salary number;
begin
  select (sal*12 + nvl(comm, 0)) into annual_salary from emp where empno = vempno;
  return annual_salary;
end;

--execute function
declare 

  vsal number;
begin
  vsal := fun_getannualsalary(7788);
  dbms_output.put_line('annual salary is ' '' vsal);
end;

----trigger
create sequence seq_test;

create table person(
        id number(10),
        name varchar2(20)
);

create or replace trigger tri_auto_increment
before
insert
on person
for each row

declare

begin
  if :new.id is null then
    select seq_test.nextval into :new.id from dual;
  end if;
end;

insert into person values (null, 'test');

select * from person;