Oracle学习日历（十二）- pl/sql分类 -- 过程，函数，包，触发器

过程

 
过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in)，也可以指定输出参数(out)， 通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。
实例如下：
1.请考虑编写一个过程，可以输入雇员名，新工资，可修改雇员的工资
2.如何调用过程有两种方法； exec  call
3.如何在java程序中调用一个存储过程
问题：如何使用过程返回值？
特别说明：
对于过程我们会在以后给大家详细具体的介绍，现在请大家先有一个概念。

create procedure sp_pro3(spName varchar2, newSal number) is
--不要写成number(3,2),表明类型就可以了，不需要大小。就好像Java写方法时的参数一样

Sql代码

begin  
  --执行部分，根据用户名去修改工资   
update emp set sal=newSal where ename=spName;   
end;   
/  

java程序中调用一个存储过程
//演示java程序去调用oracle的存储过程案例

Java代码

import java.sql.*;   
public class TestOraclePro{   
    public static void main(String[] args){   
           
        try{   
            //1.加载驱动   
            Class.forName("oracle.jdbc.driver.OracleDriver");   
            //2.得到连接   
            Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");   
  
            //3.创建CallableStatement   
            CallableStatement cs = ct.prepareCall("{call sp_pro3(?,?)}");   
            //4.给?赋值   
            cs.setString(1,"SMITH");   
            cs.setInt(2,10);   
            //5.执行   
            cs.execute();   
            //关闭   
            cs.close();   
            ct.close();   
        } catch(Exception e){   
            e.printStackTrace();   
        }   
    }   
}  

函数

  函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句。而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数，实际案例：

Sql代码

--输入雇员的姓名，返回该雇员的年薪

create function annual_incomec(name varchar2)

return number is

annual_salazy number(7,2);

begin

    --执行部分

    select sal*12+nvl(comm, 0) into annual_salazy from emp where ename=name;

    return annual_salazy;

end;

如果函数创建过程有编译错误，可以使用show error;命令显示错误
在sqlplus中调用函数

Sql代码

SQL> var income number   
SQL> call annual_incomec('scott') into: income;   
SQL> print income  

同样我们可以在java程序中调用该函数

select annual_income('SCOTT') from dual;

这样可以通过rs.getInt(l)得到返回的结果。
 

包

 
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
1.我们可以使用create package命令来创建包。
实例：
--创建一个包sp_package
--声明该包有一个过程update_sal
--声明该包有一个函数annual_income

Sql代码

create package sp_package is

  procedure update_sal(name varchar2, newsal number);

  function annual_income(name varchar2) return number;

end;

  包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。
2.建立包体可以使用create package body命令
--给包sp_package实现包体

create or replace package body sp_package is

  procedure update_sal(name varchar2, newsal number)

  is

  begin

    update emp set sal = newsal where ename = name;

  end;

  function annual_income(name varchar2) return number is

    annual_salary number;

  begin

    select sal * 12 + nvl(comm, 0) into annual_salary from emp

     where ename = name;

    return annual_salary;

  end;

end;

3.如何调用包的过程或是函数
当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案名。
如：

call sp_package.update_sal('SCOTT', 1500);

特别说明：
包是pl/sql中非常重要的部分，我们在使用过程分页时，将会再次体验它的威力呵呵。
 

触发器

  触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert,update,delete语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。
特别说明：
我们会在后面详细为大家介绍触发器的使用，因为触发器是非常有用的，可维护数据库的安全和一致性。

触发器的语法：

create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
 pl/sql语句
end

其中：

触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：指明触发器何时执行，该值可取：
before：表示在数据库动作之前触发器执行;
after：表示在数据库动作之后触发器执行。
触发事件：指明哪些数据库动作会触发此触发器：
insert：数据库插入会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删除会触发此触发器。
表 名：数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

例子：

create or replace trigger auth_secure before insert or update or DELETE
on tb_emp
begin
  IF(to_char(sysdate,'DY')='星期日') THEN
    RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表tb_emp');
  END IF;
END;
/