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;