Oracle的进阶

Oracle进阶：

程序包：

包是一组相关过程、函数、变量、常量、类型和游标等PL/SQL程序设计元素的组合。

包具有面向对象设计的特点，是对这些PL/SQL程序设计元素的封装。

包：

包头（定义一个包）、包体（定义一个实现包）。

包的相关注意：

1、包和包体必须有相同的名字；

2、包的开始没有begin语句，与存储过程和函数不同

3、在包头部分定义函数和过程的名称和参数，具体实现在包体中定义

4、在包体内声明常量、变量、类型定义、异常、游标时不使用declare

5、包体内的过程和函数的定义不要create or replace语句；

6、包定义和包体是分离开来的

 

--程序包
--作用：就是用于将常量，变量，过程，函数组合成一个整体，方便归类调用
/*
--定义包
create [or replace] package <包名>  as|is
   
    --定义变量
    --定义常量
    --定义类型
    --定义过程（是没有实现的）
    --定义函数（是没有实现的）
 
end [<包名>];
 
--实现包
create or replace package body <包名> as
 
    --实现定义包的函数
    --实现定义的的过程
 
end [<包名>];
 
注意事项：
1.有定义包可以不用实现包，但如果有实现包一定对应一个定义包。
2.实现的包名与定义的包名要一样的
3.包里面定义的变量，常量，过程，函数调用格式为
   包名.过程
   包名.函数
   包名.变量
   包名.常量
 
*/
 
--查看程序包
select * from user_objects where OBJECT_TYPE='PACKAGE';
 
--创建一个定义包
create or replace package pack1
as
   v_say_hello varchar2(20):='Hello World!';
   --定义的存储过程是不需要create or replace
   procedure pro_say_hello;
end;
/
--创建一个实现包
create or replace package body pack1 as
 
    procedure pro_say_hello as
    begin
       SYS.dbms_output.put_line(pack1.v_say_hello);
    end pro_say_hello; --end后面的过程可以忽略不写
 
end pack1;
/
 
--调用包里面的存储过程
 
set serveroutput on;
exec pack1.pro_say_hello;
 
--删除包
--删除实现包
drop package body pack1;
--删除定义包
drop package pack1;
 
 
 
--应用
/*
创建一个包含有变量、存储过程和函数的包；其中
存储过程可根据员工号查询并输出员工的姓名和工资
函数中利用定义的变量，然后则根据员工号查询出该员工奖金并返回
*/
--创建一个定义包
create or replace package pack2
as
  --定义一个员工的编号
  v_empno emp.empno%type:=7788;
  --定义了一个过程
  procedure pro_find_emp_by_empno(p_empno in emp.empno%type, p_emp out emp%rowtype );
  --定义一个函数
  function  fn_find_com_by_empno(p_empno in emp.empno%type) return emp.comm%type;
 
end;
/
create or replace package body pack2 as
   --实现过程
   procedure pro_find_emp_by_empno(p_empno in emp.empno%type, p_emp out emp%rowtype )
   as
   begin
      select * into p_emp from emp where empno=p_empno ;
   end pro_find_emp_by_empno;
   
   --实现函数
   function  fn_find_com_by_empno(p_empno in emp.empno%type) return emp.comm%type
   as
    v_comm emp.comm%type;
   begin
      select comm into v_comm   from emp where empno=p_empno;
      return v_comm;
   end fn_find_com_by_empno;
 
end;
 
--调用过程
declare
  v_emp emp%rowtype;
begin
  pack2.pro_find_emp_by_empno(7788,v_emp);
  SYS.dbms_output.put_line(v_emp.ename||'--'||v_emp.sal);
end;
--调用方法
declare
  v_comm emp.comm%type;
begin
  v_comm:= pack2.fn_find_com_by_empno(pack2.v_empno);
  SYS.dbms_output.put_line(v_comm);
end;

触发器：

触发器常用于在sql生效前或者后触发出特定的功能。常用于和序列实现数据插入时自增长的效果。

--触发器
--就是在操作（增,删，改）记录的时候，触发其它的数据行为（增删查改...）;
/*
语法
create  or replace trigger <名字>
  after|before  --两选一，表示在操作之前触发，还是在操作之后触发。
  inser|update|delete [of <字段名>] on <表名>  --触发的情况，三选1，update,insert,delete
  [for each row]  
  --plsql语句
  [delcare]  --定义
  begin
  end;
 
after|before:  --两选一，表示在操作之前触发，还是在操作之后触发。
inser|update|delete   --触发的情况，三选1，update,insert,delete
[of <字段名>]:指定触发的字段，update触发器使用
on <表名> 触发器对应名
[for each row]：如果设置了就是行级触发器，不设置就是表级触发器
                行级触发器：每影响一行记录就触一次
                表级触发器：不管影响多少行记录，每次只触一次。
plsql:用于编写触发的行为                
 
*/
--需求：员工表插入数据的时候，打印HelloWorld
create or replace trigger tri_insert_emp
   after
   insert  on emp
   for each row
   begin
     sys.dbms_output.put_line('HelloWorld!');
     
   end;
   
--查看触发器
select * from sys.user_triggers;
   
--插入一个员工
insert into emp(empno,ename,sal) values(44,'tri1',1000);
 
--停用指定触发器
alter trigger tri_insert_emp disable;
--启用指定触发器
alter trigger tri_insert_emp enable;
 
--如果一个表有多个触发器，如何一次停止该表所有触发器
alter table emp disable all triggers;
--启动同一个表所以的触发器
alter table emp enable all triggers;
 
--删除触发器
drop trigger tri_insert_emp;
 
--
/*
触发器使用：删除表的同时备份表数据到另一张备份表
*/
create table emp_bak as select * from emp where 1=2;
--表级触发器：不管任何的行数是多少，每次只触发一次触发器
create or replace trigger tri_emp_delete
  before
  delete on emp
  begin
     insert into emp_bak  select * from emp;
  end;
  
  
  delete from emp;

--触发器应用
--需求，表的编号的自增长的实现
select * from SYS.user_sequences;
create sequence seq_emp
  increment by 1
  start with 1
  nomaxvalue
  nocycle;
  
insert into emp(empno,ename,sal) values(seq_emp.nextval,'张三',2000);  
--自增不需要显示声明
 
 
create or replace trigger tri_insert_emp_increment
   before
   insert on emp
   for each row
   begin
     --如何获得这值
     --dbms_output.put_line( :new.empno||'-新插入的值--');
     --每次插入数据，让这条新创建的记录的这个字段（empno）赋予一个seq_emp.nextval
     :new.empno:=seq_emp.nextval;
   end;
   
insert into emp(ename,sal) values('张三',2000);    

角色：

Oracle提供了三种标准的角色（role）：CONNECT、RESOURCE和DBA。

    1. CONNECT Role(连接角色)

    临时用户，特别是那些不需要建表的用户，通常只赋予他们CONNECT role。CONNECT是使用Oracle的简单权限，这种权限只有在对其他用户的表有访问权时，包括select、insert、update和delete等，才会变得有意义。

    2. RESOURCE Role(资源角色)

    更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程、触发器、索引和簇。

    3. DBA Role(数据库管理员角色)

DBA role拥有所有的系统权限--包括无限制的空间限额和给其他用户授予各种权限的能力。

除此以上角色外；还可以自行创建角色。用户创建的role可以由表或系统权限或两者的组合构成。为了创建role，用户必须具有CREATE ROLE系统权限。

--角色，就是一组权限的集合
--授权
grant create role to itcast_us;
--查看角色
select * from SYS.dba_roles;
--删除角色
drop role itcast_role;
drop role itcast_role1;
--创建一个角色
create role itcast_role;
 
--给角色授权
grant create session to itcast_role;
 
select * from SYS.dba_sys_privs where grantee='ITCAST_ROLE';
 
grant itcast_role to itcast_us;
 
select * from user_role_privs;

闪回：

在Oracle中，会不可避免的出现操作失误或者用户失误，如果数据或者数据库表不小心删除掉了。则可以使用闪回来返回原来的数据，实现数据的快速恢复，而且不需要数据备份。

闪回的特点：

传统的恢复技术缓慢：它是整个数据库或者一个文件恢复，不只恢复损坏的数据在数据库日志中每个修改都必须被检查

闪回的速度快：通过行和事务把该表编入索引，改变的数据会被恢复

闪回命令容易，没有复杂步骤

闪回的类型：

1、闪回表(flashback table)

2、闪回删除(flashback drop)

3、闪回数据库（flashback database）；

一般情况下对数据库的闪回需要配置闪回数据库，然后自动产生闪回日志；再根据闪回日志恢复数据库。

--回闪技术
--作用就是要不小心删除了数据，回复数据的技术
--回闪查询，回闪表，回闪删除
 
select * from emp;
delete from emp where empno=1000;
commit;
 
--回闪查询
--需求100秒前的数据
--语法
--select 查询语句 as of  timestamp sysdate - interval '秒数' second;
select * from emp as of timestamp sysdate - interval '时' hour;
select * from emp as of timestamp sysdate - interval '分' minute;
 
--应用场景
 
create table emp_bak1 as (select * from emp as of timestamp sysdate - interval '10' minute);
 
 
--select * from emp as of scn timestamp_to_scn(sysdate - interval '10' second);
--scn:System Change Nubmer，计数器。
--计数器的作用，就是可以将一个时间使用一个整数来表示
select timestamp_to_scn(sysdate) from dual;
select scn_to_timestamp(3178240) from dual;
 
select timestamp_to_scn(sysdate - interval '10' minute) from dual;
 
--一个时间可以找到对应的计数器
select * from emp as of scn timestamp_to_scn(sysdate - interval '10' minute);
 
---回闪表
---看完以后发现，数据就是被删了。
--恢复数据
 
--查询数据是否允许移动
select row_movement from tabs where table_name='EMP';
--字段row_movement不是enabled，需要设置为允许移动
alter table emp enable row movement;
 
--回显表的语法
--flashback table <表名> to 日期
 
--查询回闪
select * from emp as of timestamp sysdate-interval '20' minute;
--使用时间格式
flashback table emp to timestamp sysdate-interval '20' minute;
 
--使用scn格式
select timestamp_to_scn(sysdate - interval '100' second) from dual;
flashback table emp to scn timestamp_to_scn(sysdate - interval '30' minute);
 
--回闪删除
drop table emp;
 
--回收站
select * from recyclebin;
purge recyclebin;
 

数据备份与恢复

数据备份

--全表备份
exp itcast/itcast@orcl file=d:\database\oracle_data\itcast.dmp full=y;
 
--指定表备份
exp itcast/itcast@orcl file=d:\database\oracle_data\itcast_emp_dept.dmp tables=(emp,dept);
 
【说明】full：完整导出数据库，一般使用system具有管理员权限的用户在命令行下进行操作。

数据恢复 

--全表恢复
imp itcast/itcast@orcl ignore=y file=d:\database\oracle_data\itcast.dmp  full=y;
 
--指定表恢复
imp itcast/itcast@orcl ignore=y file=d:\database\oracle_data\itcast_emp_dept.dmp tables=(emp,dept);
 
【说明】ignore：忽略创建错误