ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 笔记(过程、函数、触发器)

oracle 笔记(过程、函数、触发器)

原创 Linux操作系统 作者:bontonpan 时间:2009-04-14 09:45:03 0 删除 编辑


第二十章 管理过程、函数、触发器

  1. 管理过程

过程是命名的PL/SQL程序,被保存到数据库中。

匿名的PL/SQL块无法保存到数据库中,它是一次性使用的。如果对一个PL/SQL块进行了命名,那么该PL/SQL快就可以被保存到数据库中成为存储过程,即过程。

  1. iSQL*PLUS中创建、使用过程

Sql>Create or replace procedure query_emp

(

V_no in emp.empno%type,

V_name out emp.ename%type,

V_sal out emp.sal%type

)

Is

E_sal_error exception;

Begin

Select ename,sal into v_name,v_sal from emp

Where empno=v_no;

If v_sal >= 2500 then

Dbms_output.put_line(‘该雇员工资为:’||v_sal);

Raise e_sal_error;

End if ;

Exception

When no_data_found then

Dbms_output.put_line(‘没有该雇员:’||v_no);

When e_sal_error then

Dbms_output.put_line(‘该雇员工资高于2500了’);

End query_emp;


这是一个带参数的、使用了SQL语句的、有异常处理部分的过程。该过程的功能是,按照输入参数V_no得到的雇员号empno,在emp表中查找相应雇员的姓名ename和薪水sal,并放入输出参数v_namev_sal中。如果没有该雇员,则进行异常处理。如果工资高于2500元,则用异常处理进行提示。

  1. iSQL*PLUS中使用过程

在“工作区”中输入调用该过程的PL/SQL程序

Set serveroutput on

Declare

V_a1 emp.ename%type;

V_a2 emp.sal%type;

Begin

Query_emp(v_name=>v_a1,v_sal=>v_a2,v_no=>5678);

End;

  1. 管理函数

函数是有返回值的PL/SQL程序,用于计算和返回特定的数据。

  1. iSQL*PLUS中创建、使用函数

在“工作区”输入

Create or replace function get_salary_by_deptno

(v_dept_no in emp.deptno%type, ////输入部门号

V_emp_cnt out number ///输出部门人数

)

Return number

Is

V_sum number (10,2); ///返回指定部门的工资总和

Begin

Select sum(sal),count(*) into v_sum,v_emp_cnt from emp

Where deptno=v_dept_no;

Return v_sum;

End get_salary_by_deptno;


这是一个带参数的、使用了SQL语句的函数。该函数的功能是,按照输入参数v_dept_no得到的部门号deptno,在emp表中查询该部门所有雇员的工资总和及部门人数,并放入返回参数v_sum和输出参数v_emp_cnt中。

  1. iSQL*PLUS中使用函数

在“工作区”中输入调用该函数的PL/SQL语句:

Set serveroutput on

Declare

V_a1 emp.deptno%type;

V_a2 number;

V_sum number(10,2);

Begin

V_sum:=get_salary_by_deptno(v_emp_cnt=>v_a2,v_dept_no=>10);

If v_a2=0 then

Dbms_output.put_line(‘该部门无人’);

Else

Dbms_output.put_line(‘该部门工资总和:’||v_sum||’人数:’||v_a2);

End if ;

End;

  1. 管理触发器

触发器是与表(表中的行、列)、视图、事件(DML语句、执行前或执行后)相关联的过程,用于监视它们的动作或行为,一旦符合条件,触发器将立即激活并自动执行触发器中定义的措施。

触发器与过程的区别: 过程需要用户显式的调用才执行,而触发器则是当触发事件发生时由Oracle自动执行。

  1. 触发器概述

◆ 触发事件: 引起触发器被触发的事件。如DML语句(insertupdatedelete语句对表或视图)执行数据处理操作、DDL语句(createalterdrop语句在数据库中创建、修改、删除模式对象)、数据库系统事件(如系统启动或退出、异常错误)、用户事件(如登录或退出数据库)等。

◆ 触发时机: 指定触发器的触发时间。如果指定为Before,则表示在执行DML操作之前触发;如果指定为After,则表示在执行DML操作之后触发。

◆ 触发类型:

◆ 触发对象: 包括表、视图、模式、数据库。

◆ 引用:

◆ 触发条件:

  1. iSQL*PLUS中创建触发器

在“工作区”输入如下语句:

Create or replace trigger tr_emp_sal_comm

Before update of sal , comm.

Or delete

On emp

For each row

When (oldJob=’salesman’)

Begin

Case

When updating(‘sal’) then

If :newSal<:oldSal then

Raise_application_error(-20001,’销售人员工资只能涨不能降’);

End if ;

When updating(‘comm’) then

If :newComm<:oldComm then

Raise_application_error(-20002,’销售人员补助只能涨不能降’)

End if ;

When deleting then

Raise_application_error(-20003,’不能删除emp表的销售人员记录’);

End case;

End;

由于在该触发器定义中使用了For Each Row选项,所以该触发器是一个行级触发器在该触发器中,使用了Before表示了该触发器的触发时机是在DML操作执行之前;使用when oldJob=’salesman’)限定了只对岗位是salesman的记录进行触发器操作;使用条件谓词updating(‘sal’),updating(‘comm’),deleting分别处理不同的触发事件;使用限定词NewOld引用列值。

  1. iSQL*PLUS中使用触发器

在“工作区”输入如下语句:

Update emp

Set comm.=800

Where job=’SALESMAN’

则会出现错误提示:销售人员补助只能涨不能降

在“工作区”输入如下语句:

Delete from emp

Where job=’SALESMAN’

则出现不能删除emp表的销售人员记录的错误提示。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11754813/viewspace-588930/,如需转载,请注明出处,否则将追究法律责任。

上一篇: create sequence
请登录后发表评论 登录
全部评论

注册时间:2009-01-30

  • 博文量
    52
  • 访问量
    36564