首页 > WEB开发 > 数据库 > 常见Oracle数据库对象及PL/SQL编程
2014
11-17

常见Oracle数据库对象及PL/SQL编程

四、Oracle数据库对象(二)

1、存储过程和存储函数

存储过程或存储函数是一段存储在数据库中的pl/sql程序,在需要的时候可以通过名字直接调用。他们的区别在于函数必须有一个return子句,用于返回函数值,而存储过程则不需要。

1)create procedure

语法:

create [or replace] procedure 过程名 [(参数列表)]

as

   pl/sql程序

Demo1:简单的存储过程(Helloworld)

create or replace procedure sayHelloworld
as
begin
  dbms_output.put_line('hello world');
end;
/

存储过程的调用:

--方式一:独立地调用
exec sayHelloworld();

--方式二:在pl/sql程序中调用
begin
  sayHelloworld();
end;
/

Demo2(带参数的存储过程):定义一个存储过程,为指定的员工增加指定额度的工资

create or replace procedure raiseSalary(eno number,money number)
as
  --保存涨前工资
  psal emp.sal%type;
begin
  select sal into psal from emp where empno=eno;
  update emp set sal=sal+money where empno=eno;
  --存储过程(函数)中不需要commit(在调用的地方处理事务)
  dbms_output.put_line('涨前:'||psal||' 涨后:'||(psal+money));
end;
/

存储过程的调用:exec raiseSalary(7839,1000);commit; –事务是在存储过程的调用处进行控制

2)create function

create [or replace] function 函数名(参数列表)

return 函数返回值类型

as

pl/sql子程序

Demo:定义一个函数,用于查询某个职工的年收入

create or replace function queryEmpIncome(eno in number)
  return number
as
  psal emp.sal%type; --保存员工的工资
  pcomm emp.comm%type; --保存员工的奖金
begin
  select sal,comm into psal,pcomm from emp where empno=eno;
  return psal*12 + nvl(pcomm,0);
end;
/

函数的调用:

set serveroutput on;
declare
  p_income number;
begin
  p_income := queryEmpIncome(7839);
  dbms_output.put_line('年收入是:'||p_income);
end;
/

3)in参数和out参数

存储过程和函数最大的不同在于函数必须通过return返回一个值,而过程不需要。

但是,过程和函数都可以通过out指定一个或多个输出参数【参数不指定in或者out时默认为in,如上面的Demo】,我们可以利用out参数,在过程和函数中实现返回多个值。

这时就要考虑什么时候用存储过程,什么时候用函数的问题了。

原则:如果只有一个返回值,用函数;否则,就用存储过程。

4)除了上述在pl/sql程序中调用存储过程和函数外,更常见的是在JDBC程序调用存储过程和函数,参考《MySQL(JDBC)》一文。【主要用到CallableStatement】

2、触发器

触发器也是一段存储在数据库中的pl/sql程序,但它不是通过调用来执行。每当一个特定的数据操作语句(insert,update,delete)在指定的表上发出时,Oracle自动地执行触发器中定义的语句序列。触发器的类型分为:

  • 语句级触发器:在指定的操作语句操作之前或之后执行一次,不管这条语句影响了多少行。
  • 行级触发器(for each row):触发语句作用的每一条记录都被触发。在行级触发器中使用:old和:new伪记录变量, 识别值的状态。

语法:

create [or replace] tigger 触发器名

{before | after} {insert | update | delete [of 列名]}

on 表名

[for each row]

pl/sql程序

Demo1:当成功插入新员工后,自动打印“成功插入新员工”

create trigger saynewemp
after insert
on emp
begin
   dbms_output.put_line('成功插入新员工');
end;
/

创建完触发器后,向emp表中插入数据后的效果如下:

04. 常见Oracle数据库对象及PLSQL编程8497

Demo2(语句级):禁止在非工作时间插入员工数据,非工作时间指的是:

  • 周末:to_char(sysdate,’day’) in (‘星期六’,'星期日’)
  • 上班前、下班后:to_number(to_char(sysdate,’hh24′)) not between 8 and 17
create or replace trigger securityemp
before insert
on emp
begin
  if to_char(sysdate,'day') in ('星期六','星期日') or
     to_number(to_char(sysdate,'hh24')) not between 8 and 17 then
     --禁止插入
    raise_application_error(-20001,'禁止在非工作时间插入员工数据');
  end if;
end;
/

由于笔者写到这儿的时间是18:10,因此是非工作时间,向emp表中插入员工数据后的效果如下:

04. 常见Oracle数据库对象及PLSQL编程8953

Demo3(行级):确保向emp表中插入的数据满足:涨后的工资(:new.sal)不能少于涨前的工资(:old.sal)

create or replace trigger checksal
before update
on emp
for each row
begin
  --if 涨后的薪水 < 涨前的薪水 then
  if :new.sal < :old.sal then
    raise_application_error(-20002,'涨后的工资不能少于涨前的工资.涨前:'||:old.sal||'  涨后:'||:new.sal);
  end if;
end;
/

向emp表中更新一条不符合语义的数据,效果如下:

04. 常见Oracle数据库对象及PLSQL编程9279

3、数据字典

问题:上面学习并创建了这么多数据库对象,如果突然有一天,你想看看自己创建了的数据库对象,去那儿找?

当遇到这种需求时,就可以借助数据库中的数据字典了。数据字典是一组系统表,描述了数据库的信息,由DBMS自动维护。Oracle中的数据字典可以从dictionary开始,这个表包含了数据字典的表名和描述,如下图:

04. 常见Oracle数据库对象及PLSQL编程9444

从上图也可以看出,在Oracle中,数据字典的命名是由特定的前缀开始的,这些前缀如下:

  • USER:当前用户自己创建的
  • ALL:当前用户可以访问到的
  • DBA:管理员视图
  • V$:性能相关的数据

下面列出了一些常见的数据库对象和数据字典(表)的对应关系:

database object 02


留下一个回复

你的email不会被公开。