Oracle(五)

我见过太多善变的风景，差不多绝美或冷清。

刘昊霖-<淤青>

---

一、存储函数

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

--语法:
CREATE [ OR REPLACE ] FUNCTION 函数名称
（参数名称 参数类型, 参数名称 参数类型, ...）
RETURN 结果变量数据类型
IS
变量声明部分;
BEGIN
逻辑部分;
RETURN 结果变量;
[EXCEPTION
异常处理部分]
END;
--案例: 创建存储函数，根据地址 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;  
--测试1
select fn_getaddress(3) from dual;
--测试2: 查询业主 ID，业主名称，业主地址，业主地址使用刚才我们创建的函数来实现。
select id 编号,name 业主名称,fn_getaddress(addressid) 地址 from t_owners;

二、存储过程

存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。
应用程序可以调用存储过程，执行相应的逻辑。

存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：
​ 1、 存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过
​ 传出参数返回多个值。
​ 2、 存储函数可以在 select 语句中直接使用，而存储过程不能。过程多数是
​ 被应用程序所调用。
​ 3、 存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。

--语法:
CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
（参数名 类型, 参数名 类型, 参数名 类型）
IS|AS
    变量声明部分;
BEGIN
    逻辑部分
[EXCEPTION
    异常处理部分]
END;
--参数只指定类型，不指定长度
--过程参数的三种模式：
--IN 传入参数（默认）
--OUT 传出参数 ，主要用于返回程序运行结果
--IN OUT 传入传出参数
--创建不带传出参数的存储过程
create or replace procedure mypro1 as
		  --这里可以声明变量
begin
 dbms_output.put_line('存储过程调用了');
end;
--调用
 execute mypro1;
 
--创建带in和out参数，参数个数不限制
create or replace procedure pro_getaddress(v_id number, v_address out varchar2) as
begin
	select name into v_address from t_address where id=v_id;
end;
-- plsql调用存储过程：
		execute mypro1;---对于无参数存储过程可以这样调用
		或者
		call mypro1    ---对于只有in 参数也可以这样调用
		或者
		declare
            ---此处声明一个变量用于接收out参数输出
            v_address varchar2(30);
        begin
            pro_getaddress(1,v_address);
            DBMS_OUTPUT.put_line('地址：'||v_address);
        end;
        
        
--jdbc调用含参的存储函数和存储过程：
	    public String callProGetAddress(Long id) {

		String addressName = null;
		Connection connection = DbUtils.getConnection();
		CallableStatement callableStatement = null;
		try {
			callableStatement = connection.prepareCall("{call pro_getaddress(?,?)}");
			callableStatement.setLong(1, id);
			//注册传出参数类型
			callableStatement.registerOutParameter(2, OracleTypes.VARCHAR);
			//执行存储过程
			callableStatement.execute();
			//获取传出参数赋值后的结果
			addressName = callableStatement.getString(2);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbUtils.closeAll(connection, callableStatement, null);
		}
		return addressName;
	}      
    
    
--案例:添加业主信息(不带传出参数的存储过程)
--增加业主信息序列
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_housenumber,v_watermeter,sysdate,v_type );
commit;
end;
--调用
call pro_owners_add('赵伟',1,'999-3','132-7',1);

--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);
}
}

三、触发器

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的
数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发
器中定义的语句序列。

触发器可用于:

 数据确认

 实施复杂的安全性检查

 做审计，跟踪表上所做的数据操作等

 数据的备份和同步

​ 触发器分类:

 前置触发器（BEFORE）

 后置触发器（AFTER）

--语法:
CREATE [or REPLACE] TRIGGER 触发器名
    BEFORE | AFTER
    [DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]
    ON 表名
    [FOR EACH ROW ][WHEN(条件) ]
declare
    ……
begin
    PLSQL 块
End ；
--FOR EACH ROW 作用是标注此触发器是行级触发器,未标注是语句级触发器.

--前置触发器案例:
--当用户输入本月累计表数后，自动计算出本月使用数.
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;

--后置触发器案例:
--当用户修改了业主信息表的数据时记录修改前与修改后的值
--创建业主名称修改日志表:用于记录业主更改前后的名称
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;

--触发器应用一
/*
实施复杂的安全性检查：
禁止在非工作时间插入新员工

非工作时间
周末：to_char(sysdate,'day') in ('星期六','星期日')
上班前 下班后：to_number(to_char(sysdate,'hh24')) not between 9 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 9 and 17 then
     --禁止insert
     raise_application_error(-20001,'禁止在非工作时间插入新员工');
  end if;

end;

--触发器应用二
/*
数据的确认：涨后的工资不能少于涨前的工资
*/
create or replace trigger checksalary
before update
on emp
for each row 
begin
  --if 涨后的薪水  < 涨前的薪水  then 
  if :new.sal < :old.sal then
    raise_application_error(-20002,'涨后的工资不能少于涨前的工资.涨后:'||:new.sal||'  涨前:'||:old.sal);
  end if;
end;