Oracle存储过程功能强大,他主要的作用有以下几点:
1.可以批量执行sql语句,提供内置的事务支持,同时能够提高数据库操作的效率。
2.客户端程序依据存储过程名来调用,提供程序的可移植性。
3.提供复杂的SQL语句逻辑支持。
语法结构包括过程声明,执行过程部分,存储过程异常
1)不带参数:
Create or replace procedure NoparProcedureName
is
begin
delete from tablename a where a.id='';
delete from tablename b where b.id='';
exception
end;
2)带参数:
Create or replace procedure ParProcedureName(key_id in char)
is
begin
delete from tablename a where a.id=key_id;
update tablename b set b.name='测试' where b.id=key_id;
exception
end;
3)带参数含赋值
Create or replace procedure MyProcedureName(key_id in char,isal out varchar)
is icount number ;
begin
select count(*) into icount from emp where sal>isal ;
if icount==1 then ...
esle
...
end if ;
end