Oracle 11~ 游标与触发器

1.Oracle 游标（cursor）：通过关键字CURSOR来定义一组查询出来的数据集，类似数组一样，把查询到的数据集存储在内存中，然后通过游标指向其中的一条记录，通过循环游标达到循环数据局的目的。

（1）显式游标：使用之前先声明定义。一般是对查询结果定义游标，通过打开游标循环获取结果集内的记录，跳出循环结束游标的获取，关闭游标不再获取结果集。

   声明游标：给游标命名并关联一个查询结果集。

declare cursor 游标名
is select查询语句;

    打开游标：初始化游标指针，游标一旦打开，就算表的基础数据发生变化游标对应的结果集也不会再变了。

open 游标名;

    读取游标中的数据：把当前游标指针指向的数据行读取到对应的变量中，一般和循环一起使用。

fetch 游标名 into 变量名;

    关闭游标：游标使用完，一定要关闭游标释放资源。

close 游标名;

   显式游标的常用属性：%notfound（没有数据返回true，有数据返回false，用来判断游标是否全部循环完毕）

                                      %found（和notfound相反）

                                      %isopen（判断游标是否打开）

                                      %rowcount（用作计数器，表示当前游标fetch into获取了多少行数据）

     例1，创建一个游标，循环打印学生表中的学生信息。

declare
cursor stu_cur is select * from stuinfo order by stuid;
ls_curinfo stu_cur%rowtype;
begin
open stu_cur;
loop
fetch stu_cur into ls_curinfo;
exit when stu_cur%notfound;
dbms_output.put_line('学号：'||ls_curinfo.stuid ||'姓名：'||ls_curinfo.stuname);
end loop;
close stu_cur;
end;

 例2，用%rowcount修改案例1，输出语句改为：

dbms_output.put_line('rowcount计数器：第'||stu_cur%rowcount||'位'||'学号：'||ls_curinfo.stuid ||'姓名：'||ls_curinfo.stuname);

（2）隐式游标：PL/SQL自己管理的游标，开发者只能获得它的属性信息，而不能控制操作。在DML语句中，PL/SQL会自动打开隐式游标，直接采用SQL作为隐式游标的名称，一样具有上述的属性。如下面这个例子：

declare
ls_curinfo1 stuinfo%rowtype;
ls_curinfo2 stuinfo%rowtype;
begin
select t.* into ls_curinfo1 from stuinfo t where t.stuid = 'sc201801001';
if sql%found then
 dbms_output.put_line('姓名：'||ls_curinfo1.stuname||'性别：'||ls_curinfo1.sex);
end if;
select t.* into ls_curinfo2 from stuinfo t where t.stuid = 'sc201801011';
if sql%found then
 dbms_output.put_line('姓名：'||ls_curinfo2.stuname||'性别：'||ls_curinfo2.sex);
end if;
exception
when no_data_found then  dbms_output.put_line('查无此人');
end;

2.Oracle触发器：当使用者对数据库的对象做特定操作时，触发的一段pl/sql代码。一般用来记录操作日志、校验数据、同步数据等。

（1）数据操作（DML）触发器：定义在表上，执行select、insert、update、delete时会触发。又可分为行级触发器和语句级触发器，或按照修改数据的前后又可以分为after触发器和before触发器。

create [or replace] trigger 触发器名
before|after
delete|insert|update [of 列1,列2,...]
on 表名
[for each row]
[follows 其它触发器名]
[when 条件]
declare
声明部分
begin
触发器内容
end;

例1，创建一个触发器来校验学生信息的正确性。

create or replace trigger tr_stuinfo_insert
before insert on stuinfo
for each row
begin
if :new.sex not in ('1','2') then
  RAISE_APPLICATION_ERROR(-20001, '性别只能是男或女');
end if;
end;


insert into stuinfo(stuid,stuname,sex,age,classno,stuaddress,grade,enroldate,idnumber)
values('sc201802004','周杰伦','3',25,'c201802','福建省厦门市xxx号','2018',to_date('01-09-2018','dd-mm-yyyy'),'3503021993xxxxxxxx');

 

例2，利用行级触发器来记录更新学生信息表stuinfo的操作记录。

先建立一个日志表用来存放更新信息。

create table oplog
(logid number,tablename varchar2(20),colname varchar2(20),
newdata varchar2(20),olddata varchar2(20),opdate date,oprator varchar2(10));

再创建一个序列用来生成主键 logid

create sequence pk_oplog_id; //默认从1开始，步长为1

 创建触发器。行级触发器通过 :new 和 :old来访问更新的数据和原来的数据。

create or replace trigger tr_update
before update on stuinfo
for each row
begin
if :new.classno <> :old.classno then
insert into oplog
(logid,tablename,colname,newdata,olddata,opdate,oprator)
values(pk_oplog_id.nextval,'stuinfo','classno',:new.classno,:old.classno,sysdate,'jsq');
end if;
end;

 更新一行数据验证。

update stuinfo set classno = 'c201803' where stuid = 'sc201801001';
select * from oplog;

 

我这里的logid = 2是因为我之前删除了一行数据。

例3，语句级触发器一般用来限制某种操作。比如今天是2020年2月5号，禁止每月的5号操作学生信息表。

create or replace trigger tr_stuinfo_sql
before update or insert or delete on stuinfo
begin
 if to_char(sysdate,'dd') = '05' then
 raise_application_error(-20001,'每月5号不能修改学生信息表');
 end if;
 end;


update stuinfo set stuname = '张小三' where stuid = 'sc201801005';

同一个对象可以有多个DML触发器，但是触发器有先后顺序：before型触发器>after型触发器，行级触发器>语句级触发器，同类型的触发器就按照follows指定的顺序触发。

（2）数据定义（DDL）触发器：对数据库对象进行create、alter、drop时会触发，一般用来记录DDL操作的相关信息，创建DDL触发器的语法结构为：

create or replace trigger 触发器名
before | after
DDL操作(create/alter/drop) | 数据库操作
on schema| database
[follows 其它触发器名]
[when 条件]
declare
声明部分
begin
触发器内容
end;

例1，利用DDL触发器创建一个禁止修改删除stuinfo表结构的触发器。

create or replace trigger tr_dll_stuinfo
before alter or drop on schema
begin
if dictionary_obj_name = 'STUINFO_201812' then
  if sysevent = 'alter' then
  raise_application_error(-20001,'禁止对学生表进行alter操作');
  end if;
  if sysevent = 'drop' then
  raise_application_error(-20001,'禁止对学生表进行drop操作');
  end if;
end if;
end;


alter table stuinfo_201812
modify(stuid varchar2(12));

 

 这里不知道为什么表名一定要大写才行。。。

例2，建立一个数据库级别的DDL触发器，记录用户登录数据库的记录信息。

先创建一个登录表来存放登录信息，创建一个序列用来生成主键。

create table login_log
(logid varchar2(20),loginuser VARCHAR(20),logindate date)
tablespace USERS
 PCTFREE 10
 INITRANS 1
 MAXTRANS 255
 STORAGE (INITIAL 64k MINEXTENTS 1 MAXSIZE unlimited);

create sequence pk_login_id;

顺便熟悉一下创建表有哪些详细的属性~

创建触发器。

create or replace trigger tr_ddl_login
after logon
on database
begin
insert into login_log values(pk_oplog_id.nextval,sys.login_user,sysdate);
end;

 

（3）用户和系统事件触发器：定义在数据库系统上，当进行数据库事件时会触发，一般用来记录登录的相关信息。

         用户事件包括：create、alter、drop、analyze、grant、revoke等。

         例，创建一个触发器，在删除用户前将用户名称保存到表all_user_info中。

         先创建一个表all_user_info用来存放用户信息，创建一个新用户test。

create table all_user_info
(name varchar(20), op_time date);

create user test identified by 123456;

create or replace trigger tr_drop_user
before drop on schema
begin
  insert into all_user_info values(SYS.dictionary_obj_name,sysdate);
end;

         系统事件包括：数据库启动（STARTUP）、数据库关闭（SHUTDOWN）等。

         例，创建一个触发器记录数据库启动时间。

create or replace trigger tr_record_starttime
after startup on database
begin
 insert into stime_record values(sysdate);
end;

select * from stime_record;

啊啊啊啊，我不会在sql developer里关闭和启动数据库，敲‘shutdown immediate’或者直接关闭sql developer都不行，百度了半天也没找到答案，最后还是在sqlplus里关闭重启了数据库。

（4）INSTEAD OF 触发器：作用在视图上，当用户对视图进行操作时会触发，一般触发器会把相关操作转换为对表的操作。

                                             instead of 触发器不能指定before/after，且必须指定for each row。

例，创建一个instead of 触发器以便于在视图中更新数据并同步到表。

    先创建一个简单的视图 t_view

create view t_view as
select a.stuid, a.stuname, a.classno, b.classaddress
from stuinfo a, class b
where a.classno = b.classno;

   创建触发器tr_update

create or replace trigger tr_update
instead of update on t_view
for each row
begin
update stuinfo  set stuname = :new.stuname,classno = :new.classno where stuid = :new.stuid;
update class set classaddress = :new.classaddress where classno = :new.classno;
end;

update t_view set stuname = '张晓峰', classno = 'c201802', classaddress = '静远301' where stuid = 'sc201801006';


select * from t_view;

（5）复合触发器：复合多种DML触发器来完成一些更复杂的操作，如一个触发器中包含着行级触发器和语句级触发器。

Oracle在dbms_standard包中提供了一些功能性的函数，可以为触发器提供一些信息。

ora_database_name：返回当前数据库的名称。

ora_dict_obj_name：返回DDL操作所对应的数据库对象名。

ora_dict_obj_owner：返回DDL操作对象的所有者名称。

ora_login_user：返回当前的用户名。

ora_sysevent：返回触发触发器的系统事件名。