Oracle---day03

一、视图

　　对一个查询结果的封装（视图里面所有的数据都是来自于它查询的那张表，视图里面不存储任何数据）

　　　视图的好处：1.能够封装复杂的查询结果
　　　　　　　　　2.屏蔽表中的细节

　　　语法：create [or replace] view 视图名字 as 查询语句 [with read only]
　　　　　   or replace--如果存在就覆盖
　　　　　   with read only--只允许读，不允许修改，一般都会加

　　同义词--就是给表、视图等取另外一个名字

--创建一个视图
create or replace view view_test1 as select ename,job,deptno from emp;

--通过视图修改数据
update view_test1 set ename='SIMITH2' where ename = 'smith2';

--创建一个只读视图
create or replace view view_test2 as select ename,job,deptno from emp with read only;

--同义词概念
create synonym yuangong for view_test2;

select * from yuangong

select * from view_test1;
select * from emp;

二、序列

　　序列：生成类似于auto_increament（mysql中的id自增长）的ID

　　　语法：create sequence 序列名称
　　　　　　start with 起始值
　　　　　　increament by 增长量
　　　　　　maxvalue 最大值 | nomaxvalue
　　　　　　minvalue  最小值 | nominvalue
　　　　　　cycle  |  nocycle　　是否在max和min之间循环
　　　　　　cache 缓存数量 |  nocache
　　　用法：sequencename.currval　　获得当前值（至少在使用一次nextval之后才能使用）
　　　　　　sequencename.nextval　　获得下一个值

　　　最常用的写法：careate sequence seq_test;

--创建一个1,3,5....30  切循环的序列
create sequence seq_test1
start with 1
increment by 2
maxvalue 30
cycle
cache 3;
--最常用的序列写法
create sequence seq_test2;   --默认从1开始，每次增长1，无最大值，不循环，不缓存--1,2,3,4,5,6....


select seq_test1.nextval from dual;
select seq_test1.currval from dual;

三、索引

　　索引：相当于一本书的目录，能够提高我们的查询效率。如果某一列经常作为查询条件，就有必要创建索引（数据量大的情况下）

　　语法：create index 索引名称 on 表名(列名)

　　原理：btree balanceTree   平衡二叉树

　　注意：主键约束自带主键索引，唯一约束自带唯一索引。如果某列作为查询条件的时候创建索引，查询会变快，但修改，插入的时候会变慢索引创建好之后，每过一段时间，DBA都回去重构索引

--五百万条数据测试
create table wubaiwu(
       name varchar(20),
       address varchar(20)
);

declare
begin
     for i in 1..5000000 loop
         insert into wubaiwu values('姓名'||i,'地址'||i);
     end loop;
     commit;
end;

--在没有索引的情况下查询姓名为 "姓名3000000" 的数据
select * from wubaiwu where name='姓名3000000';

--创建索引，再查询
create index idx_wubaiwu on wubaiwu(name);
select * from wubaiwu where name='姓名3000000';

select * from wubaiwu where name='姓名3000000';

四、PLSQL编程

　　PLSQL编程：procedure Languege——过程语言，Oracle对SQL的一个扩展

　　　语法：declare  --声明变量
　　　　　　　　变量名 变量类型;
　　　　　　　　变量名 变量类型 := 初始值
　　　　　　　　vsal emp.sal%type;  --引用表字段类型%%%%
　　　　　　　　vrow emp%rowtype;    --引用表中一条记录的类型
　　　　　　begin
　　　　　　　　业务逻辑
　　　　　　end;

declare
   i varchar(10) := '张三';
begin
   dbms_output.put_line(i);   --类型java中的System.out.print
end;

--查询7369的工资，并打印
declare
   vsal emp.sal%type;    --引用emp表中sal字段的类型
begin
   select sal into vsal from emp where empno=7369;  --将查询结果赋值给vsal
   dbms_output.put_line(vsal);
end;

--查询7369员工信息并打印
declare
   vrow emp%rowtype;   --引用emp表一条记录的类型
begin
   select * into vrow from emp where empno=7369;
   dbms_output.put_line('姓名:'||vrow.ename||'  工资:'||vrow.sal);
end;

　　条件判断语句

/*
    条件判断语句：
    if ** then
    elsif ** then
    else
    end if
*/
declare 
   age number := &aa;
begin
   if age < 18 then
      dbms_output.put_line('未成年');
   elsif age >= 18 and age <= 24 then
      dbms_output.put_line('青年');
   elsif age > 24 and age <= 40 then
      dbms_output.put_line('中年');
   else
      dbms_output.put_line('老年');
   end if;
end;

　　循环语句

/*
    循环语句
    for循环：
        for x in [reverse] 起始值..终止值 loop
           循环体
        end loop
    
    while循环：
        while 条件 loop
           循环体
        end loop
        
    loop循环：
       loop
          循环体
          exit when 条件     --满足条件时退出    
       end loop
*/

--for循环输出1-10
declare

begin
   for i in reverse 1..10 loop
       dbms_output.put_line('未成年'||i);
   end loop;
end;

--while循环输出1-10
declare
   val number := 1;
begin
   while val<=10 loop
      dbms_output.put_line(val);
      val := val+1;
   end loop;
end;

--loop循环输出1-10
declare
   val number := 1;
begin
   loop
      dbms_output.put_line(val);
      val := val+1;
      exit when val>10;
   end loop;
end;

--输出菱形
declare

begin
   for i in 1..5 loop
      for j in 1..5 loop
         if(i+j>=4 and j<=i+2 and i+j<=8 and j>=i-2) then
            dbms_output.put('*');
         else
            dbms_output.put(' ');
         end if;
      end loop;
      dbms_output.put_line(' ');
   end loop;
end;


select sal from emp where empno=7369

五、游标

　　游标：数据的缓冲区，从表中查询出结构集，指定访问一条记录，类似于数组。游标还可以用于抛出自定义异常

　　　语法：cursor 游标名 [(参数名, 参数类型)] is 查询结果集      --声明游标

　　　使用方式：1.声明游标
　　　　　　　　2.打开游标　　--open 游标名
　　　　　　　　3.从游标中获取数据：fetch 游标名 into 变量
　　　　　　　　　　　　　　　　　　游标名%found　--找到数据
　　　　　　　　　　　　　　　　　　游标名%nofound　--没有找到数据
　　　　　　　　4.关闭游标　　--close 游标名

--输出所有员工姓名(不带参数游标)
declare
    cursor vrows is select * from emp;
    vrow emp%rowtype;
begin
    open vrows    --打开游标
    loop
        fetch vrows into vrow;
        exit when vrows%ontfound    --没有找到的时候退出
        dbms_output.put_line(vrow.ename || ' ---' || vrow.sal);
    end loop;
    close vrows;
end;

--输出指定部门下的员工姓名和工资(带参数的游标)
declare
    --声明游标
    cursor vrows(dno number) is select * from emp where deptno=dno;
    --声明变量记录每一行数据
    vrow emp%rowtype
begin
    open vrows(10)    --查询10号部门
    loop
        fetch vrows into row
        exit when vrows%notfound
        dbms_output.put_line('姓名:'||vrow.ename||'   工资:'||vrow.sal);
    end loop;
    close vrows;
end;

--for循环遍历游标，不需要打开关闭游标
declare
   cursor vrows is select * from emp;
begin
   for vrow in vrows loop
       dbms_output.put_line('姓名:'||vrow.ename||'   工资:'||vrow.sal);
   end loop;
end;
--涨工资，总裁涨1000，经理800，其他400
declare
   cursor vrows is select * from emp;
   vrow emp%rowtype;
begin
   open vrows;
   loop
       fetch vrows into vrow;
       exit when vrows%notfound;
       if vrow.job='PRESIDENT' then
           update emp set sal=sal+1000 where empno=vrow.empno;
       elsif vrow.job='MANAGER' then
           update emp set sal=sal+800 where empno=vrow.empno;
       else
           update emp set sal=sal+400 where empno=vrow.empno;
       end if;
   end loop;
   close vrows;
   commit;
end;


/*
    异常
*/
--捕获异常
declare
   val emp%rowtype;
begin
   select * into val from emp where empno=123;
     
exception
   when others then
       dbms_output.put_line('捕获到异常');
end;

--自定义异常
declare
   cursor vrows(dno number) is select * from emp where deptno=dno;
   val emp%rowtype;
   no_emp exception;
begin
   open vrows(123);
   fetch vrows into val;
   if vrows%notfound then   --判断是否有值
       raise no_emp;
   end if;
   close vrows;
exception
   when no_emp then
       dbms_output.put_line('捕获到自定义异常');
end;

 

六、存储过程和存储函数

　　存储过程：实际上是封装在服务器上的一段PLSQL代码，已经编译好了的代码。客户端调用存储过程，执行效率就会非常高兴。

　　　语法：create [or replace] procedure 存储过程名称 (参数名 in|out 参数类型，参数名 in|out 参数类型)　　--in 代表输入参数，out 代表输出参数。
　　　　　　as | is
　　　　　　--声明部分
　　　　　　begin
　　　　　　--业务逻辑
　　　　　　end;

　　存储函数：和存储过程差不多，只是多了一个返回值。但有了out参数，好像存储函数就没啥用了。

　　　语法：create [or replace] procedure 存储过程名称 (参数名 in|out 参数类型) return 返回值类型
　　　　　　.....其他和储存过程一样

--创建一个存储过程，给指定员工涨工资，并输出涨工资前后的工资，，，输入参数in--vempno,vsal
create or replace procedure proc_updateSal(vempno in number, vsal in number)
is
    currentSal number;
begin
    select sal into currentSal from emp where empno=vempno;
    dbms_output.put_line('涨薪前的工资：'||currentSal);
    update emp set sal = sal + vsal where empno=vempno;   --更新工资
    dbms_output.put_line('涨薪前的工资：'||(currentSal+vsal));
    commit;
end;

--调用存储过程
--方式一
call proc_updateSal(7788, 10);

--方式二，也是最长用的方式
declare
begin
   proc_updateSal(7788,-100);
end;

--查询员工年薪
select sal*12+nvl(comm, 0) from emp where empno=7788;
create or replace procedure get_YearSal(vempno in number, yearSal out number)
is

begin
    select sal*12+nvl(comm, 0) into yearSal from emp where empno=vempno;
end;
--测试存储过程
declare 
   yearSal number;
begin
   get_yearSal(7788, yearSal);
   dbms_output.put_line(yearSal);
end;


--查询员工年薪---使用存储函数
create or replace function fun_YearSal(vempno in number) return number
is
   yearSal number;
begin
    select sal*12+nvl(comm, 0) into yearSal from emp where empno=vempno;
    return yearSal;
end;
--测试存储函数
declare 
   yearSal number;
begin
   yearSal:=fun_yearSal(7788);
   dbms_output.put_line(yearSal);
end;

七、触发器

　　触发器：当用户执行了insert，update，delete等操作后，可以触发一系列其他动作

　　　语法：create [or replace] trigger 触发器名字
　　　　　　before | after
　　　　　　insert | update | delete
　　　　　　on 表名
　　　　　　[for each row]　　--是否应用到每一行
　　　　　　declare
　　　　　　...
　　　　　　begin
　　　　　　...
　　　　　　end;

　　　触发器分类：
　　　　　　　　语句级触发器：　　不管影响多少行，都只触发一次，也就是不加 for each row
　　　　　　　　行级触发器：　　    影响了多少行就触发多少行。
　　　其他：　:old　　更新前的记录
　　　　　　   :new 　 更新或的记录

--新员工入职后，输出一句话：欢迎来到德莱联盟
create or replace trigger tri_test1
before
insert
on emp
declare
begin
     dbms_output.put_line('欢迎来到德莱联盟');
end;

--数据校验，周二老板不在，不能办理入职手续
create or replace trigger tri_test2
before
insert
on emp
declare
   vday varchar2(10);
begin
   select trim(to_char(sysdate,'day')) into vday from dual;
   if vday='星期二' then
      dbms_output.put_line('周二老板不在，不能办理入职手续');
      --抛出系统异常
      raise_application_error(-20001,'周二老板不在，不能办理入职手续');
   end if;
end;


insert into emp(empno,ename) values(1188,'德莱厄斯')

--更新所有员工的工资，并输出一句话
create or replace trigger tri_test3
after
update
on emp
for each row
declare

begin
   dbms_output.put_line('涨钱啦！');
end;


update emp set sal = sal+10;

--判断员工涨工资后的工资一定要大于更新后的工资
create or replace trigger tri_updateSal
before
update
on emp
for each row
declare

begin
   if :old.sal>:new.sal then
      raise_application_error(-20002,'这不是涨工资是扣工资');
   end if;
end;

update emp set sal = sal-1;

　模拟mysql中的id只增长属性  auto_increment

--创建测试表
create table person(
       pid number primary key,
       pname varchar2(20)
);

--创建自增长序列
create sequence seq_person_pid;

--创建触发器
create or replace trigger tri_add_pid
before
insert
on person
for each row
declare

begin
   dbms_output.put_line('嘻嘻'||:new.pname);
   select seq_person_pid.nextval into :new.pid from dual;
end;
;


insert into person values(null,'张三');

select seq_person_pid.nextval from dual;

select * from person;

select * from emp;