D48-Oracle02(查询，索引，触发器)

一、分页

• 分页格式：

select * from 
       (select rownum rn,tt.* from (
       -- 主查询

       )tt where rownum<#{end})
where rn>#{begin};

1.1 行号：rownum

• 每出来一行记录，就会出现一个行号
• 必须从1开始，依次递增
• 不属于任何一张表，不能用表名.rownum，但每张表都能用

1.2 行物理地址：rowid

• 行物理地址和表中的行固定绑定，永不改变
• 删除该行，否则物理地址一致不变
• 作用： 可以在plsql中对数据进行修改

select p.rowid ,p.* from person p;

1.3 查询

• 跨用户查询

select * from scott.emp;

• 复制scott用户下的emp表到当前用户

create table emp as select * from scott.emp;

• 根据工资倒序排列，每页5条记录，查询第二页

select rownum，e.* from emp where rownum<11 
order by e.sal desc;

• 先排序，后加rownum

select rownum,e.* from(select * from emp order by sal desc) e where rownum<11;

二、视图

• 提供了一个查询的窗口

• 本身不存放数据，所有数据都来自于原表

• 视图的作用：

• • 可以实现数据的实时同步
• • 可以过滤掉一些敏感字段
    
    

• 创建视图

create view v_emp as  select e.ename,e.job from emp e;

• 查询视图

select * from v_emp;

• 修改视图

update v_emp set job='CLARK' where ename='ALLEN'

三、索引

单列索引和复合索引

• 在一定条件下可以大幅度提高查询效率，但会影响增删改的效率。

• 分为单列索引 和复合索引

• • 单列索引触发的条件：查询条件中必须包含索引列中的原始值才能触发。
    
    

• 创建单列索引

create index idx_emp on emp(ename);
select * from emp where ename='SCOTT';-- 触发
select * from emp where ename like '%SCO%';-- 不触发

• 复合索引
• • 作用在多列上
• • 复合索引第一列为优先检索列
• • 复合索引的触发的条件：询条件中必须包含优先检索列中的原始值。

select * from emp where ename='SCOTT';-- 优先触发单列索引，没有单列索引也会触发复合索引

3.1 注意

1. 只有在findById的时候一般才用。
2. 但是主键约束自带索引。

四、pl/sql编程语言

• pl/sql编程语言是在sql的基础之上进行强化。使得sql语言具有过程化编程的特性。
• 声明函数： 类似于java中的main方法，可以直接执行。
• pl/sql中 := 相当于java中的 = 号。
• pl/sql中 = 相当于java中的 == 号。

declare
    i number(2) := 1;--int i = 1;   
    s varchar2(10) := '小明';
    ena emp.ename%type;---引用型变量
    emprow emp%rowtype;---记录型变量
begin
    dbms_output.put_line(i);
    dbms_output.put_line(s);
    select ename into ena from emp where empno=7788;
    dbms_output.put_line(ena);
    select * into emprow from emp where empno=7788;
    dbms_output.put_line(emprow.ename || '的工作为：' || emprow.job);
end;

五、存储过程和存储函数

• 存储过程或存储函数是在数据库端编写了一个业务方法。
• 里面所有的内容是已经编译好的。
• 存储过程和存储函数作用是一样的，语法有区别而已。
  
  
• 用存储过程给指定员工涨100块钱

create or replace procedure p1(eno emp.empno%type)
is
  s emp.sal%type;
begin
  select sal into s from emp where empno = eno;
  dbms_output.put_line('涨前：' || s);
  update emp set sal=sal+100 where empno = eno;
  commit;
  select sal into s from emp where empno = eno;
  dbms_output.put_line('涨前：' || s);
end;

• 存储函数的返回值类型和参数或存储过程的参数都不能带长度。
• 使用存储函数算出指定员工的年薪

create or replace function f_yearsal(eno emp.empno%type) return number
is
   yearsal number(10);
begin
   select sal*12+nvl(comm, 0) into yearsal from emp where empno = eno;
   return yearsal;
end;

• 调用f_yearsal
• 存储函数的返回值必须接收

declare
  yearsal number(10); 
begin
  yearsal := f_yearsal(7788);
  dbms_output.put_line(yearsal);
end;

5.2 总结

5.2.1 pl/sql【存储函数和存储过程】与java【重点】的比较

1. pl/sql编程是面向过程的 ，速度比java高。
2. pl/sql编程语言在数据库端的编程语句，如果有一个业务逻辑，不经常升级，而且和数据库交互特别多， 就推荐使用pl/sql。
3. pl/sql可以减少java和数据库交互的次数，而且pl/sql是已经编译好的，无需对sql进行重复编译。
4. java作为一个面向对象的编程语言，维护起来更加方便，代码更加简洁，开发速度更快，升级更方便。

5.2.2 存储过程和存储函数的区别:

1. 语法区别： 存储函数比存储过程多了两个return
2. 本质区别： 存储函数有返回值，存储过程没有返回值。
3. 因为存储函数有返回值，所以我们可以使用存储函数来自定义函数。

5.2.3 触发器

• 触发器就是制定一个规则， 在增删改操作的时候，只要满足条件，自动触发，无需调用。
• 触发器：语句级触发器 和 行级触发器。
• • 凡是用到 ：old 或者 ：new 的触发都叫行级触发器。
• 序列： 就是给主键赋值使用。
• • 默认从1开始，依次递增1，永不回头。

1. 语句级触发器

• 插入一条记录，输出一个新员工入职

create or replace trigger t1
after
insert
on person
declare

begin
 dbms_output.put_line('一个新员工入职');
end;
---触发t1
select * from person;
insert into person values (4, '小刚');
commit;