Oracle复习笔记(八)
ull then
wText := ' where ' || whereText;
end if;
--判断是否有order by排序
if orderText is not null then
oText := ' order by ' || orderText;
end if;
--拼接分页sql语句
strSql := 'select * from (select rownum rn,' || showField || ' from (select * from ' || tableName || wText || oText ||' ) t where rownum <= :1) where rn > :2';
dbms_output.put_line(strSql);
--打开游标并取值(不用关闭)
open pageCursor for strSql using pageIndex * pageSize,(pageIndex - 1)* pageSize;
--拼接记录数sql语句
strSql := 'select count(1) from ' || tableName || wText || oText;
dbms_output.put_line(strSql);
--执行动态sql获取记录数
execute immediate strSql into counter;
--计算总页数
pageCount := floor((counter + pageSize - 1) / pageSize);
end;
end pagePack;
--创建视图(rowtype需要rownun列,因此需要创建视图,视图中其他列需要制定别名否则报错)
create or replace view viewEmp
as
select rownum rn,emp.* from emp;
--调用分页存储过程
declare
counter number;
pageCount number;
pageCursor pagePack.pageCursorType;
emp viewEmp%rowtype;
begin
pagePack.pageProc('emp','t.*','','',1,5,counter,pageCount,pageCursor);
dbms_output.put('COUNT:' || counter || ' PAGE_COUNT:' || pageCount);
dbms_output.new_line();
--这里使用for循环遍历游标会报异常,因为调用过程返回的游标已是打开的
loop
fetch pageCursor into emp; --类似java中ResultSet中rs.next();
exit when pageCursor%notfound;
dbms_output.put_line(emp.ename);
end loop;
close pageCursor;
end;
/*----------------------------------------触发器----------------------------------------*/
语法：
create [or replace] trigger 触发器名称
after | before | instead of
[insert] [[or] update [of 字段列表]]
[[or] delete]
on 表名 | 视图名 --触发器语句（事件）
[referencing {OLD [as] old / NEW [as] new}] --指定old表和new表的别名
[for each row] --行级触发器，每行都触发
[when (条件)] --触发器条件
begin
触发器操作;
end;
--限制只有scott才能对emp进行DML操作
create or replace trigger trig1
before
insert or update or delete
on emp
begin
if user <>
'SCOTT' then
raise_application_error(-20000,'只有SCOTT才能对此表进行操作！');
end if;
end;
--记录对scott用户下emp表操作(用户、操作时间、操作类型)
--扩展：如果是update操作记录该用户操作了哪些列？
create or replace trigger trig2
after
insert or update or delete
on emp
declare
action varchar2(10);
begin
if inserting then
action := 'insert';
elsif deleting then
action := 'update';
elsif updating then
action := 'update';
end if;
--system用户有一张scott_emp_log表并赋予public
insert into system.scott_emp_log values(user,sysdate,action,orcl_seq.nextval);
end;
--使用触发器对scott_emp_log表自动生成序号(插入数据id列写0或不指定id列)
create or replace trigger trig3
before insert
on system.scott_emp_log
for each row
begin
select orcl_seq.nextval into :new.id from dual;
end;
--创建触发器限制不允许修改员工奖金
create or replace trigger trig4
before update of comm
on emp
for each row
begin
if :new.comm <> :old.comm then
raise_application_error(-20000,'不允许修改comm字段！');
end if;
end;
示例6：使用触发器更新视图
--创建视图
create or replace view view_test
as
select empno,empno||ename newname,sal from emp;
--创建触发器
create or replace
trigger eg_trigger6
instead of update on view_test
begin
--将修改视图的操作替换成修改基表的操作
update emp set ename=substr(:new.newname,5) where empno=:new.empno;
end;
update view_test set newname='7369LISHU' where empno=7369;
示例7：记录删除的对象
--创建日志表
create table droped_objects
(
object_name varchar2(30),
object_type varchar2(30),
drop_date date
);
--创建触发器
create or replace
trigger eg_trigger7
after drop
on scott.schema --谁的操作
begin
insert into droped_objects values
(ora_dict_o