oracle中块（匿名块、命名块、子程序、触发器）的使用

1.匿名块：

declare

v_avgsal number(6,2);

begin

select avg(sal) into v_avgsal from emp where deptno=&no;

dbms_output.put_line('平均工资:'||v_avgsal);

end;

2.命名块：

<<outer>>

declare

v_deptno number(2);

v_dname varchar2(10);

begin

<<inner>>

begin

select deptno into v_deptno from emp where lower(ename)=lower('&name');

end;--<<inner>>

select dname into v_dname from dept where deptno=v_deptno;

dbms_output.put_line('部门名:'||v_dname);

end;--<<outer>>

<<outer>>外层块    <<inner>>内层块

3.子程序：包括过程、函数、包。

3.1过程：

create procedure update_sal(name1 varchar2,newsal number)

is

begin

update emp set sal=newsal where lower(ename)=lower(name1);

end;

 

exec update_sal('scott',3700);

3.2函数：

create function annual_income(name1 varchar2)

return number is

annual_salary number(7,2);

begin

select sal*12+nvl(comm,0) into annual_salary from emp where lower(ename)=lower(name1);

return annual_salary;

end;

 

var income number

call annual_income('scott') into :income;

 

3.3包

create package emp_pkg is

procedure update_sal(name1 varchar2,newsal number);

function annual_income(name1 varhcar2) return number;

end;

create

3.3.1包体：用于实现包规范中的过程和函数

create package body emp_pkg1 is

procedure update_sal(name1 varchar2,newsal number)

is

begin

update emp set sal=newsal where lower(ename)=lower(name1);

end;

function annual_income(name1 varchar2) return number

is

annual_salary number(7,2);

begin

select sal*12+nvl(comm,0) into annual_salary from emp where lower(ename)=lower(name1);

return annual_salary;

end;

end;

 

exec emp_pkg1.update_sal('scott',1500);

 

var income number

exec emp_pkg1.annual_income('scott') into :income;

 

4.触发器：

create trigger update_cascade

after update of deptno on dept for each row

begin

update emp set deptno=:new.deptno where deptno=:old.deptno;

end;