Oracle数据库入门笔记（下）

Oracle数据库入门笔记（下）

1.视图

1.视图的概念：

视图就是提供一个查询的窗口，所有数据来自原表。创建视图必须有dba权限

1.2视图的作用：

1. 视图可以屏蔽一些敏感的字段
2. 保证总部和分部数据及时统一。
3. 保护数据起到安全性作用。

1.3创建视图的语法

CREATE VIEW 视图名称 AS 子查询

CREATE OR REPLACE VIEW 视图名称 AS 子查询

--创建员工视图
create view e_emp
as
select ename,job from emp

1.4修改视图（不推荐）

我们一般不会去修改视图

update e_emp set job = 'CLERK' where ename = 'ALLEN'
commit;

1.5设置视图为只读 语法

CREATE OR REPLACE VIEW 视图名称 
AS 子查询 
WITH READ ONLY

--创建员工视图，并将其设置为只读
create view e_emp_read 
as
select ename,job from emp
with read only

2.索引

2.1 索引的概念

• 索引就是在表的列上构建一个二叉树，到达大幅度提供查询效率的目的。但是索引会影响增、删、改的效率。

2.2索引的分类

单列索引：单列索引是基于单个列所建立的索引

CREATE index 索引名 on 表名(列名)
--创建单列索引
create index idx_emp_ename on emp(ename)

• 单列索引触发规则，条件必须是索引列中的原始值。单行函数，模糊查询，都会影响索引的触发。（面试知识点）

select * from emp where ename='SCOTT'

复合索引

1. 复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是 要求列的组合必须不同
2. 如果要触发复合索引，必须包含有优先检索列中的原始值
3. 复合索引中第一列为优先检索列

select * from emp where ename = 'SCOTT' and job = 'xx'--触发符合索引
select * from emp where ename = 'SCOTT';--触发单列索引
select * from emp where ename = 'SCOTT' or job = 'xx'--不触发索引。

2.3索引的使用原则

1. 在大表上建立索引才有意义
2. 在 where 子句后面或者是连接条件上的字段建立索引
3. 表中数据修改频率高时不建议建立索引

3.Pl/SQL基本语法

3.1 概念

• pl/sql 编程语言是对sql 语言的扩展，使得sql语言具有过程化编程特性。（面向过程的语言）。
• pl/sql 编程语言比这一般的过程化编程语言，更加灵活高效。
• pl/sql 编程语言主要用来编写存储过程和存储函数等。

3.2 常量和变量定义

赋值操作可以用 := 也可以使用into 查询语句赋值

declare
    i number(2) := 10;
    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;
    select * into emprow from emp where empno = 7788;
    dbms_output.put_line(ena);
    dbms_output.put_line(emprow.ename || '的工资为：' ||emprow.sal);
end;

3.3 if 分支

--输入小于18的数字，输出未成年
--输入大于18小于40的数字，输出中年人
--输入大于40的数字，输出老年人

declare
  i number(3) := &scan;
begin
  if i<18 then
    dbms_output.put_line('未成年');
  elsif i<40 then 
    dbms_output.put_line('中年人');
  else
    dbms_output.put_line('老年人');
  end if;

end;

3.4 LOOP 循环语句

--for循环 输出1到10 10个数字
declare 

begin
     for i in 1..10 loop
       dbms_output.put_line(i);
     end loop;  
end;

3.5 exit循环

--exit循环 输出1到10 10个数字
declare
     i number(2) := 1;
begin
  loop 
    exit when i>10;
    dbms_output.put_line(i);
    i := i + 1;
  end loop;
end;

3.6 while循环

--while循环 输出1到10 10个数字
declare 
     i number(2) := 1;
begin
     while i<11 loop
        dbms_output.put_line(i);
        i := i + 1;
     end loop;  
end;

3.7 游标 Cursor

概念：可以放多个对象，多行记录。

--输出emp表中所有员工的姓名
declare
     cursor c1 is select * from emp;
     emprow emp%rowtype;
begin
     open c1;
          loop 
            fetch c1 into emprow;
            exit when c1%notfound; 
            dbms_output.put_line(emprow.ename);
          end loop;
     
     close c1;
end;

--给指定部门员工涨工资
declare
     cursor c2(eno emp.deptno%type) 
     is select empno from emp where deptno = eno;
     en emp.empno%type; 
begin
     open c2(10);
          loop
               fetch c2 into en;
               exit when c2%notfound;
               update emp set sal = sal+100 where empno = en;  
               commit;
          end loop;
     close c2;
end;

4.存储过程

1.概念

• 就是提前已经编译好的一段pl/sql语言，放在数据库端，可以直接被调用。这一段pl/sql一般都是固定步骤的业务。

2.语法

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)] 
AS 
begin 
    PLSQL 子程序体； 
End;

3.定义

--定义给指定员工涨100块钱
create or replace procedure p1(eno emp.empno%type)
as

begin
       update emp set sal = sal+100 where empno = eno;  
       commit;
end; 

4.使用

--为工号为7788的员工涨工资
declare 

begin
     p1(7788);  
end;

5.存储函数

• 注意：存储过程和存储函数的参数都不能带长度

定义存储函数语法

create or replace function 函数名(Name in type, Name in type, ...) return 数据类型 is 
    结果变量 数据类型; 
begin 

    return(结果变量); 
end 函数名;

--范例：通过存储函数实现计算指定员工的年薪
create or replace function f_yearsal(eno emp.empno%type) 
return number
is
  s number(10); 
begin
  select sal*12+nvl(comm,0) into s from emp where empno = eno;
  return s;
end;

--调用存储函数
declare
  s number(10);
begin  
  s := f_yearsal(7788);
  dbms_output.put_line(s);
end;

• out类型参数如何使用

--使用存储过程来算年薪
create or replace procedure p_yearsal(eno emp.empno%type,yearsal out  number)
is
       s number(10);
       c emp.comm%type;
begin
       select sal*12,nvl(comm,0) into s,c from emp where empno = eno;
       yearsal := s+c;
end;

--测试p_yearsal
declare
  yearsal number(10);     
begin
  p_yearsal(7788, yearsal);
  dbms_output.put_line(yearsal);
end;

6.存储过程和存储函数的区别

• 语法区别

1. 关键字不一样
2. 存储函数比存储过程多了两个return。

• 本质区别

1. 存储函数有返回值，而存储过程没有返回值。
2. 如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数。
3. 即便是存储过程使用了out类型的参数，其本质不是真的有了返回值。
4. 而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值
5. 我们可以使用存储函数有返回值的特性，来自定义函数。
6. 而存储过程不能用来自定义函数。

案例需求：查询出员工姓名，员工所在部门名称

• 使用传统方式实现案例需求

select emp.ename,dept.dname
from emp,dept
where emp.deptno = dept.deptno

• 使用存储函数来实现提供一个部门编号，输出一个部门名称。

create or replace function fdna(dno dept.deptno%type)
return dept.dname%type
is
       dna dept.dname%type;
begin
       
       select dname into dna from dept where deptno = dno;
       return dna;
end;

--使用fdna存储函数来实现案例需求：查询出员工姓名，员工所在部门名称
select emp.ename,fdna(deptno)
from emp 
--只有存储函数能做

7.触发器

1.概念

• 触发器，就是制定一个规则，在我们做增删改操作的时候只要满足规则，自动触发，无需调用。
• 比如：着火了，烟雾报警器自动洒水。

2.分类

1. 语句级触发器
   在指定的操作语句操作之前或之后执行一次，不管这条语句影响 了多少行 。

--范例：插入一条记录输出一个新员工入职
create or replace trigger t1
after insert
on person
declare
   
begin
   dbms_output.put_line('一个新员工入职');
end;

--测试触发器
insert into person values(1,'It闲言');
commit;

2. 行句级触发器

   1. 触发语句作用的每一条记录都被触发。在行级触 发器中使用 old 和 new 伪记录变量, 识别值的状态。
   2. 包含有for each row 的就是行级触发器。
   3. 加for each row 是为了使用 :old 或者 :new对象或者一行记录。

--范例：不能给员工降薪【行级触发器】
create or replace trigger t2
before update
on emp
for each row
declare
    
begin
    if :old.sal > :new.sal then
      --raise_application_eeror(-20001~-20999之间,'错误提示信息');
      raise_application_error(-20001,'不能给员工降薪');
    end if;  
end;

--测试t2 触发器
update emp set sal = sal -100 where empno = 7788;
commit;

触发器实现主键自增

• 分析：在用户做插入操作之前，拿到即将插入的数据。给该数据中的主键列赋值

create or replace trigger auid
before insert
on person
for each row
declare

begin
  select s_person.nextval into :new.pid from dual;
end;

--使用auid实现主键自增
insert into person values(1,'闲言');
commit;
--我这里在pid的位置虽然把主键为1传进去，但这是没有效果的。