存储过程最直接的理解:就是保存了批量的 sql(select,insert,if for),以后可以通过一个名字 把这些批量的 sql 执行,使用存储过程在大批量数据查询或计算时会带来高性能,存储过程 编写和调试比较复杂,不同数据库产品存储过程差异非常大,很难实现平滑移植
1
建立存储过程
create or replace procedure proc_test(in_var number,out_var out sys_refcursor)
as
begin
open out_var for select * from emp where deptno=in_var;
end;
执行存储过程
var ret refcursor
exec proc_test(20,:ret)
print :ret
2
触发器
触发器是特殊的存储过程,它与数据库的 insert、update 和 delete 相关联,如定义完成触发 器之后,会在 insert、update 或 delete 语句执行前或执行后自动执行触发器中的内容
触发器示例,向 emp 表中加入数据,采用触发器自动再向 t_log 表里加入一条数据
首先建立 t_log 表
create table t_log (
log_id number(10) primary key,
log_time date
);
为建立 t_log 的主键建立 sequence
create sequence seq_log_id start with 1 increment by 1;
建立触发器
create or replace trigger tri_test
after insert on emp
begin
insert into t_log(log_id, log_time) values(seq_log_id.nextval, sysdate);
end;
向 emp 表中加入数据
insert into emp(empno, deptno) values(7777, 10);
在 emp 中多了一条数据 empno 为 7777,在 t_log 中自动加入了一条数据,这就是触发器的 作用。
3
游标
有时采用 select 会返回一个结果集,使用简单的 select 无法得到上一行,下一行,后 5 行,后 10 行,如果想做到这一点必须使用游标,游标是存储在数据库服务器上的一个数据 库查询,它不是一条 select 语句,他是一个结果集,有了游标就可以根据需要滚动浏览数据
下面通过一个示例,根据岗位加工资,如果是 MANAGER 增加 20%的工资,如果是 SALESMAN 增加 10%的工资,其他的增加 5%的工资
for update 是将数据库表的数据进行锁定的操作,不让其他的事务可以修改。在 Oracle 中 的这种锁定是对查询的结果数据进行加锁,其他的数据不会被加锁。我们把这样的锁定方式 叫行级锁
create or replace procedure proc_sal
is
cursor c is
select * from emp for update;
begin
for v_emp in c loop
if (v_emp.job = 'MANAGER') then
update emp set sal = sal + sal*0.2 where current of c;
elsif (v_emp.job = 'SALESMAN') then
update emp set sal = sal + sal*0.1 where current of c;
else
update emp set sal = sal + sal*0.05 where current of c;
end if;
end loop;
commit;
end;
执行存储过程 exec proc_sal;