09oracle函数、存储过程、触发器

1、存储函数

1.1、什么是存储函数

存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用 P/SQL 进行逻辑的处理。

1.2、存储函数语法结构

创建或修改存储过程的语法如下:

CREATE [ OR REPLACE ] FUNCTION 函数名称
（参数名称 参数类型, 参数名称 参数类型, ...）
RETURN 结果变量数据类型
IS
 变量声明部分;
BEGIN
 逻辑部分;
 RETURN 结果变量;
[EXCEPTION
 异常处理部分]
END;

注意，这里的参数类型没有长度！

1.3、案例

需求： 创建存储函数，根据地址 ID 查询地址名称。
语句:

create function fn_getaddress(v_id number)
return varchar2
is
 v_name varchar2(30);
begin
 select name into v_name from t_address where id=v_id;
 return v_name;
end;

测试此函数：

select fn_getaddress(1) from dual

需求：查询业主 ID，业主名称，业主地址，业主地址使用刚才我们创建的函数来实现。

select id 编号,name 业主名称,fn_getaddress(addressid) 地址
from t_owners 

函数返回游标

create or replace function fn_getowners(v_addressid in number)
return sys_refcursor
is
owners_cursor sys_refcursor;
 begin
	 open owners_cursor for select * from t_owners where addressid=v_addressid;
	 return owners_cursor;
 end;

DECLARE  

 owners_cursor SYS_REFCURSOR;  

 owner t_owners%ROWTYPE;  

BEGIN  

 owners_cursor := fn_getowners(1);  

 IF owners_cursor%ISOPEN THEN  

   LOOP  

       FETCH owners_cursor INTO owner;  

       EXIT WHEN owners_cursor%NOTFOUND;  

       DBMS_OUTPUT.PUT_LINE('id:'||owner.id||'姓名:'||owner.name||'门牌号:'||owner.housenumber||'创建时间:'||owner.adddate);  

   END LOOP;  

 END IF;  

 CLOSE owners_cursor;  

END;  

2、存储过程

2.1、什么是存储过程

存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：
（1）存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值。
（2）存储函数可以在 select 语句中直接使用，而存储过程不能。存储过程多数是被应用程序所调用。
（3）存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。

2.2、存储过程语法结构

创建或修改存储过程的语法如下:

CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
（参数名 类型, 参数名 类型, 参数名 类型）
IS|AS
 变量声明部分;
 BEGIN
 逻辑部分
[EXCEPTION
 异常处理部分]
END;

参数只指定类型，不指定长度
过程参数的三种模式：
IN 传入参数（默认）
OUT 传出参数 ，主要用于返回程序运行结果
IN OUT 传入传出参数
案例
（1）创建不带传出参数的存储过程：添加业主信息

--增加业主信息序列
create sequence seq_owners start with 11;
--增加业主信息存储过程
create or replace procedure pro_owners_add
(
 v_name varchar2,
 v_addressid number,
 v_housenumber varchar2,
 v_watermeter varchar2,
 v_type number
)
is
begin
 insert into T_OWNERS
values( seq_owners.nextval,v_name,v_addressid,v_housenumb
er,v_watermeter,sysdate,v_type );
 commit;
end;

赋值sql的时候一定要检查sql语句是否正常，有可能因为换行等原因导致出错，吃了大亏了
PL/SQL 中调用存储过程

call pro_owners_add('赵伟',1,'999-3','132-7',1);

或者

	begin
		pro_owners_add('赵伟',1,'999-3','22258',1);
	end; 

JDBC 调用存储过程

/**
* 增加
* @param owners
*/
public static void add(Owners owners){
java.sql.Connection conn=null;
java.sql.CallableStatement stmt=null;
try {
conn=BaseDao.getConnection();
stmt=conn.prepareCall("{call
pro_owners_add(?,?,?,?,?)}");
stmt.setString(1, owners.getName());
stmt.setLong(2, owners.getAddressid());
stmt.setString(3, owners.getHousenumber());
stmt.setString(4, owners.getWatermeter());
stmt.setLong(5, owners.getOwnertypeid());
stmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {
BaseDao.closeAll(null, stmt, conn);
}
}

package com.francis.waterboos.test;

import com.francis.waterboos.dao.OwnersDao;
import com.francis.waterboos.entity.Owners;

import java.util.Date;

/**
 * @author Francis
 * @create 2021-08-25 15:39
 */
public class OwnersTest {
    public static void main(String[] args) {
        Owners owners = new Owners();
        owners.setId(29L);
        owners.setName("张君宝");
        owners.setAddressid(1L);
        owners.setHousenumber("1-1");
        owners.setWatermeter("22259");
        owners.setAdddate(new Date());
        owners.setOwnertypeid(1L);
         OwnersProDao.add(owners);.add(owners);
    }
}

（2）创建带传出参数的存储过程
需求：添加业主信息，传出参数为新增业主的 ID

--增加业主信息存储过程
create or replace procedure pro_owners_add
(
 v_name varchar2,
 v_addressid number,
 v_housenumber varchar2,
 v_watermeter varchar2,
 v_type number,
 v_id out number
)
is
begin
 select seq_owners.nextval into v_id from dual;
 insert into T_OWNERS
values( v_id,v_name,v_addressid,v_housenumber,v_watermete
r,sysdate,v_type );
 commit;
end;

PL/SQL 调用该存储过程

declare
 v_id number;--定义传出参数的变量
begin
 pro_owners_add('王旺旺',1,'922-3','133-7',1,v_id);
 DBMS_OUTPUT.put_line('增加成功,ID:'||v_id);
end;

JDBC 调用存储过程

public static long add1(Owners owners) {
        long id = 0;
        java.sql.Connection conn = null;
        java.sql.CallableStatement stmt = null;
        try {
            conn = BaseDao.getConnection();
            stmt = conn.prepareCall("{call pro_owners_add(?,?,?,?,?,?)}");
            stmt.setString(1, owners.getName());
            stmt.setLong(2, owners.getAddressid());
            stmt.setString(3, owners.getHousenumber());
            stmt.setString(4, owners.getWatermeter());
            stmt.setLong(5, owners.getOwnertypeid());
            stmt.registerOutParameter(6, OracleTypes.NUMBER);//注册传出参数类型
            stmt.execute();
            id = stmt.getLong(6);//提取传出参数
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(null, stmt, conn);
        }
        return id;
    }

3、触发器

3.1、什么是触发器

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。
触发器可用于
 数据确认
 实施复杂的安全性检查
 做审计，跟踪表上所做的数据操作等
 数据的备份和同步
触发器分类
 前置触发器（BEFORE）
 后置触发器（AFTER）

3.2、创建触发器的语法

语法：

CREATE [or REPLACE] TRIGGER 触发器名
 BEFORE | AFTER
 [DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]
 ON 表名
 [FOR EACH ROW ][WHEN(条件) ]
declare
 ……
begin
 PLSQL 块
End ；

FOR EACH ROW 作用是标注此触发器是行级触发器 语句级触发器
在触发器中触发语句与伪记录变量的值

触发语句	:old	:new
Insert	所有字段都是空(null)	将要插入的数据
Update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都是空(null)

3.3、 案例

1. 前置触发器
   需求：当用户输入本月累计表数后，自动计算出本月使用数 。
   代码：

create or replace trigger tri_account_updatenum1
before
update of num1
on t_account
for each row
declare
begin
 :new.usenum:=:new.num1-:new.num0;
end;

2. 后置触发器
   需求：当用户修改了业主信息表的数据时记录修改前与修改后的值

--创建业主名称修改日志表:用于记录业主更改前后的名称
create table t_owners_log
(
updatetime date,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
);
--创建后置触发器，自动记录业主更改前后日志
create trigger tri_owners_log
after
update of name
on t_owners 
for each row
declare

begin
 insert into t_owners_log
values(sysdate,:old.id,:old.name,:new.name);
end;

测试：
–更新数据

update t_owners set name='杨小花' where id=3;
commit;

–查询日志表

select * from t_owners_log;