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;

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值