oracle包、触发器

-- 创建包头,有点类似于Java的接口
create or replace package first_package
is
     type dept_table_type is table of dept.dname%type
     index by binary_integer;
     
     procedure put_boolean(p_val boolean);
     
     function cal_cube(p_num number) return number;
     
     -- 此处定义的变量，它是全局有效的。
     -- 在不同的Session中，系统为该变量生成不同的副本。
     v_age number;
     
     v_name varchar2(50);
     -- 包头里定义的成分，是全局有效。      

end;

-- 包体和包头要同名，系统知道该包体是负责为上面的包头提供实现
create or replace package body first_package
is
     -- 声明部分
     -- 说它是局部变量有道理，因为它的作用域只在该包体内有效。
     -- 说它是包体内的全局变量也有道理，
     -- 因为它可以在该包所包含所有程序单元内有效。
     v_test varchar2(50);
     v_tmp number;
     
     procedure put_boolean(p_val boolean)
     is     
     begin
          if p_val then
              dbms_output.put_line('真');
          else
              dbms_output.put_line('假');
          end if;
     end;
     
     -- 包头里定义的函数、过程，包体必须全部提供实现。
     function cal_cube(p_num number) return number
     is
           
     begin
          v_tmp := p_num * p_num * p_num;
          return v_tmp;     
     end;
     
     -- 这个函数只在包体内定义了，包头里没有声明。
     -- 这个函数就该包体内有效。
     function factor_fun(p_num number) return number
     is
           v_factor number := 1;
           v_index number := 1;
     begin
           while v_index <= p_num loop
                v_factor := v_factor * v_index;
                v_index := v_index + 1;           
           end loop;
           return v_factor;
     end;
     
     -- 子程序重载
     function factor_fun(p_num varchar2) return number
     is
     begin
          return 20;
     end;     
begin
     -- 包体内的可执行性语句，相当于该包体的初始化语句。
     -- 在一次Session里，包体内的初始化语句最多执行一次。
     
     dbms_output.put_line('--包体内的可执行性语句--');
end;

begin
     dbms_output.put_line(first_package.factor_fun(4));
     
end;

begin
     dbms_output.put_line(first_package.cal_cube(4));
     
end;

-- 建用户
create user fkjava
identified by "12345678"
default tablespace users
temporary tablespace temp;

-- 授权
grant connect to fkjava;

grant select on scott.dept to fkjava;

grant insert on scott.dept to fkjava;

grant delete on scott.dept to fkjava;

select * from user_source;

create or replace trigger dept_insert_trigger
before
insert on dept
declare
     -- 局部变量声明
     v_name varchar2(50);
begin
     dbms_output.put_line('向dept表插入记录！');
end;

select * from dept;
delete from dept where to_char(deptno) not like '%0';

commit;

insert into dept
values(12 , 'xxxx' , 'yyy');

create table dept_bk
(
 deptno number primary key,
 dname varchar2(50),
 loc varchar2(50)
);

select * from dept_bk;

-- 行级触发器，它可以完成数据的同步复制
create or replace trigger dept_copy_trigger
after
insert or update on dept
-- 增加for each row之后，它就是行级触发器。
for each row
begin
     -- :new 引用正在插入、修改之后的新记录。
     -- :old 引用修改、删除之前的旧记录。
     
     -- 如果正在执行insert语句
     if inserting then
         insert into dept_bk
         values(:new.deptno , :new.dname , :new.loc);
     end if;
     
     -- 如果正在执行insert语句
     if updating then
          dbms_output.put_line('修改dept表的的当前记录');
          update dept_bk
          set dname = :new.dname , loc = :new.loc
          where deptno = :new.deptno;
     end if;
end;

insert into dept 
values(18 , 'aaa' , 'bbb');

select *from dept_bk;

update dept 
set dname='我的部门'
where deptno=16;

select * from dept;

create or replace trigger before_dept_trigger
before 
update on dept
begin
     dbms_output.put_line('对dept表执行修改之前！');
end;

create or replace trigger after_dept_trigger
after 
update on dept
begin
     dbms_output.put_line('对dept表执行修改之后！');
end;

update dept
set dname='新名字'
where to_char(deptno) not like '%0';

create or replace trigger create_trigger
after
-- 只要在scott schema里创建数据库对象，都会触发该触发器
create on schema
begin
      dbms_output.put_line('正在创建数据库对象！');
end;

/*
  当我们对一本书建目录时，往往是对各个知识点建目录。
  ——因为我们经常需要根据知识点进行查询。
  
  如果我们经常需要正对某列进行查询，
  那我们就可以针对该列建一个索引！
  以后根据根据该列查询时，就可以快速定位到指定数据。
  
  索引的坏处：当数据表的数据发生改变时，系统会自动去维护索引，
  索引的维护是有系统开销的。
  
  建索引有两种形式：
  1. 隐式创建：主键列，唯一键列，系统会为之自动建索引。
  2。显式创建：由程序员通过create index语句来创建索引。
*/

-- 指定索引名，对哪个表的哪个列。
create index dept_dname_index
on dept(dname);

--如果程序以后针对dept表的dname列进行查询时，查询效率很高。

create table log_inf
(
   log_id number primary key,
   log_user varchar2(50),
   log_date date,
   logout_date date
);

create sequence log_seq
start with 1
increment by 1;

create or replace trigger logon_trigger
after
logon on database
begin
     if user != 'SYSMAN' AND user != 'DBSNMP' then
          insert into log_inf(log_id , log_user , log_date )
          values(log_seq.nextval , user , sysdate);
     end if;
end;

create or replace trigger logoff_trigger
before
logoff on database
begin
     if user != 'SYSMAN' AND user != 'DBSNMP' then
        update log_inf
        set logout_date = sysdate
        where log_user = user and logout_date is null;
     end if;
end;

select * from log_inf;
delete from log_inf;

create or replace trigger error_trigger
after
servererror
on database
begin
      dbms_output.put_line('数据库发生了错误！');
end;

-- 导致主键重复错误，也会触发相应的触发器。
insert into dept
values(20 , 'ss' , 'sss');

commit;