Oracle 进阶详细案例教程笔记_oracle的高级功能及使用案例

一、Oracle简介

​ Oracle 是甲骨文公司使用 Java 语言开发的一款关系型数据库产品。又名 Oracle RDBMS （关系型数据库管理系统）。Oracle 数据库是目前流行的关系数据库管理系统，它是一种高效率、高可靠性、高吞吐量的数据库解决方案，适用于大型企业、中型企业项目应用。

​ Oracle 整个知识体系图如下：

本文主要通过案例来讲解 Oracle 进阶部分的相关内容：

1. 用户：用户和模式区别、角色管理、权限分配、权限回收
2. PL 过程语言编程：语法、条件、循环、异常
3. 视图：基本视图、错误视图、只读视图、条件视图
4. PL 事务控制：提交、回滚、设置保存点
5. 自定义存储过程
6. 自定义函数
7. 函数和过程区别
8. 游标：隐式游标、显式游标、带参数游标、REF 游标
9. 触发器及应用
10. 数据备份和恢复

关注公众号：普惠学堂，回复：“oracle” 免费获取完整案例笔记内容！

二、用户管理

1.用户和模式区别

在使用数据库前，都需要进行身份验证。Oracle 具有安全性很高并很完整的数据保护机制。其中就涉及用户及权限的管理，下面我们先从用户管理来开始学习。

在 Oracle 学习中，很多人经常把用户和模式搞混淆，表示 “ 傻傻分不清 ”。我们先来区分下这两个概念。

Oracle用户：是用连接数据库和访问数据库对象的。（用户是用来连接数据库访问数据库）。

模式：是数据库对象的集合。模式对象是数据库数据的逻辑结构。

用户是用来连接数据库对象。而模式用是用创建管理对象的。模式和用户在 Oracle 中是一对一的关系。一个用户有一个缺省的schema，其 Schema 名就等于用户名，当然一个用户还可以使用其他的 Schema , 如果要访问其他 Schema 下的对象，则对象前加 Schema 名标明即可，例如 scott.emp 。

比如说：使用 scott 用户登录数据库后，用户就进入了 scott 的模式了，在此模式下创建的数据库对象，如表、视图、游标、过程等这些对象就都所属于 scott 模式了。这些对象的集合就是 scott 模式。而在此模式下要访问自己模式对象 emp 表，直接 select * from emp 就可以了。但是如果要访问 system 模式下的对象，则需要使用 system 模式名来访问，select * from system.dept ；

2. 内置用户

在 Oracle 中的内置用户有很多，其中有三个常用的系统用户（sys , system）和示例用户 ( scott )，这三个用户的密码都可以在安装过程中指定。

sys： 这个账户相当于 Mysql 的 root 或 SqlServer 中的 sa 。属于超级管理员，可以完成数据库的所有管理任务。具有 DBA （数据库管理员）权限并具有SYS模式。只能通过SYSDBA 登录数据库，是Oracle数据库中权限最高的帐号。同时拥有 “SYSDBA” 和 “SYSOPER” 权限。

system: system用户具有DBA权限，但是没有SYSDBA权限。平常一般用该帐号管理数据库，不建议使用 system 来创建一些和管理无关的数据库对象。

sys 只能以系统管理员（sysdba）或系统操作员（sysoper）的权限登录，而 system 可以直接登录（normal）。

scott: 此用户是为示例用户，为初学者提供一些简单的应用示例对象，比如 emp 表和dept 表等，此账户默认是锁定状态，密码默认 tiger。可以通过 system 来解锁和重置密码。

3. 用户管理命令

默认 scott 用户么有管理用户的权限，所以要学习用户的相关命令，可以暂时使用 system。

登录数据库和查看当前登录状态命令

-- 登录 myserver 服务器，myserver 为已经配置的网络名
conn system/pwd123@myserver ;
-- 查看当前登录用户
SHOW USER;

解释：上面 system 为账户名，pwd123 为密码，myserver 为服务器网络名，对应 IP

下面再来介绍几个常用的表

--查看当前用户能访问的表
select * from user_tables; 
--查询所有用户：
select * from all_users;
--查看当前用户权限：
select * from session_privs;
--查看所有用户权限
select * from dba_sys_privs;
--查看当前模式下用户角色
SELECT * FROM USER_ROLE_PRIVS

用户的创建

-- 创建用户并指定默认表空间 注意密码字母开头----
CREATE USER zhang
IDENTIFIED BY p123
DEFAULT TABLESPACE USERS -- 指定的表空间，可以不指定

用户的修改

-- 修改用户密码
ALTER USER MARTIN IDENTIFIED BY newpass;
-- 给账户张解锁
ALTER USER ZHANG ACCOUNT UNLOCK;
-- 锁定用户
ALTER USER ZHANG ACCOUNT LOCK;

删除用户

-- 只删除用户账户信息
DROP USER ZHANG; 
-- 删除账户并级联删除其相关对象
DROP USER ZHANG CASCADE;

4. 权限管理

权限：指的是执行特定命令或访问数据库对象的权利 权限有两种类型：系统权限和对象权限 系统权限允许用户执行某些数据库操作，如创建表就是一个系统权限 对象权限允许用户对数据库对象（如表、视图、序列等）执行特定操作 角色是一组相关权限的组合，可以将权限授予角色，再把角色授予用户，以简化权限管理。

GRANT 命令可用于为用户分配权限或角色

-- 授予zhang用户 查询表 TEST 的权限
GRANT SELECT ON TEST TO zhang;
-- 授予zhang用户 修改表 TEST 的权限
GRANT UPDATE ON TEST TO zhang;

也可以先创建角色，然后给角色授权。再把角色分配给用户

-- 创建角色 R_TEST
CREATE ROLE R_TEST;
-- 给角色授权
GRANT SELECT ON TEST TO R_TEST;
GRANT UPDATE ON TEST TO R_TEST;
GRANT DELETE ON TEST TO R_TEST;
 
--再把角色赋予用户,这样zhang就有了查询，修改，删除表的权限了
GRANT R_TEST TO zhang;

回收角色和权限

--回收角色
revoke myrole from zhang;
--回收权限
revoke insert on usertbl from zhang;

5. 进阶案例

在 Oracle 不仅可以进行常规的用户权限管理，我们还可以对于账户进行特殊的限制管理，比如：尝试密码次数限制、锁定时间限制、口令历史限制等

DBA 使用 profile 管理用户口令：口令限制、资源限制命令集合
（1）帐户锁定：最多只能尝试3次，锁定时间2天
    创建profile文件：create profile 名称 limit failed_login_attempts 3(次) password_lock_time 2(天) ;
    alter user 用户名 profile  名称
（2）解锁：alter user 用户名 account unlock
（3）终止口令：要求用户每隔10天就修改一次密码，宽限期为2天
    create profile 名称 limit password_life_time 10 password_grace_time 2;
    alter user 用户名 profile  名称
（4）口令历史：不允许修改的新密码和原来的旧密码一样
     create profile 名称 limit password_life_time 10   password_grace_time 2 pasword_reuse_time 10 
     其中password_reuse_time 10指口令10天后可以重用
（5）删除profile：drop profile 名称[casecade]

三、PL/SQL 过程语言

数据库中 SQL 可以完成数据库对象的创建，数据的增删改查等操作。但是对于一些复制的业务逻辑操作仅仅使用 SQL 就无法满足我们的需求了。PL/SQL 正好弥补这方面的不足。

PL/SQL 是过程语言 ( Procedural Language ) 与结构化查询语言 ( SQL ) 结合而成的编程语言。PL/SQL 是对 SQL 的扩展，可用于创建存储过程、触发器和程序包，给 SQL 语句的执行添加程序逻辑。与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性。

PL/SQL 块是构成 PL/SQL 程序的基本单元。将逻辑上相关的声明和语句组合在一起。 PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分。

​ [DECLARE ​ declarations] ​ BEGIN ​ executable statements ​ [EXCEPTION ​ handlers] ​ END;

下面我们从简单到复杂语句一步步来学习 PL/SQL 的语法案例：

1. 打印 Hello,World

begin
  dbms_output.put_line('Hello,World!');
end;

2. 声明一个整型变量 并初始化 打印输出

-- 注意赋值 :=  连接符 ||
declare 
age integer:=12;
begin
  dbms_output.put_line('年龄为：'||age);
end;

3. 从控制台输入赋值

declare
age int;
begin
  age:=&请输入您的年龄;--接收用户输入
  dbms_output.put_line('年龄为：'||age);
end;

4. IF 语句

声明变量 接收用户输入 输入大于18 成年 否则 未成年

declare
age int;
begin
  age:=&情输入您的年龄;
  if age>=18
    then  dbms_output.put_line('成年人');
  else
     dbms_output.put_line('未成年');--没有then
  end if;
end;

5. IF- ELSIF

用户输入编号 根据编号查工资 并根据工资打印 ：大于3000 显示”高工资“ 大于等于2000小于3000 ”一般工资“ 否则显示“低工资”

declare 
myno int;
mysal int;
begin
  myno:=&请输入编号;
  select sal into mysal from emp where empno=myno;
  if mysal>=3000
    then dbms_output.put_line('高工资'||mysal);
  elsif mysal>=2000 and mysal<3000
    then dbms_output.put_line('中工资'||mysal);
  else
    dbms_output.put_line('低工资'||mysal);   
  end if;
end;

6) CASE 语句

declare grade char;
begin
     --grade:='B';
     case &grade --允许用户输入值 但如果是字符要带引号''
       when 'A' then dbms_output.put_line('成绩优秀');
       when 'B' then dbms_output.put_line('成绩良好');
       when 'C' then dbms_output.put_line('成绩一般');
       when 'D' then dbms_output.put_line('成绩较差');
       else dbms_output.put_line('成绩不存在');
     end case;
end;

根据成绩评等级

declare
score int;
begin
    score:=&请输入您的成绩（数字）;
    case  --注意这里和上面写法区别 没有变量
        when score>=90  then dbms_output.put_line('优秀'); 
        when score>=80  then dbms_output.put_line('良好');
        when score>=70  then dbms_output.put_line('一般');
        when score>=60  then dbms_output.put_line('及格'); 
        else dbms_output.put_line('不及格');
    end case;
end;

7 . 循环

LOOP 循环

declare
i int:=0;
begin
     loop 
          i:=i+1;
          /**
          if (i>10)
             then exit;       
          end if;*/
          exit when(i>10);
          dbms_output.put_line(i);
     end loop;
end;

WHILE 循环 : 求 5 的阶乘

---while 求阶乘
declare
i int:=1;---声明并初始化条件变量
mysum int:=1;
mymax int:=5;---计算5的阶乘
begin
  while i<= mymax --条件判断
    loop
        mysum:=mysum*i;
        i:=i+1;
    end loop;
    dbms_output.put_line(mymax||'的阶乘为：'||mysum);
end;

FOR 循环：九九乘法表

--for  --九九乘法表
declare
myres int;
begin
  for x in 1..9    -- 注意 在for循环中不用变量声明
    loop
      for i in x..9
      loop
           myres:= x*i;
           dbms_output.put(x||'*'||i||'='||myres||' ');
      end loop;
      dbms_output.put_line('');
    end loop;
end;

8. 异常处理

运行时出现的错误叫做异常。

begin
       dbms_output.put_line(3/0);--语句块
       exception --开始异常代码段
       when 
       Zero_divide--异常类型 除数为零Zero_divide
       --others --异常类型：未知异常都others .select into时No_data_found 数字转换Invalid_number等二十多种预定义异常
       then dbms_output.put_line('出错了'||sqlerrm);
end;
 
 
-----others  Too_many_rows-----------
declare
mysal number;
begin
     select sal into mysal from emp where empno=7369;
     dbms_output.put_line('工资：'||mysal);
     dbms_output.put_line('结果:'||3/0);
     exception
     when Too_many_rows then dbms_output.put_line('记录数过多'); 
     when NO_DATA_FOUND then dbms_output.put_line('没有记录');
     when others then dbms_output.put_line('其他异常');
end;

9. 进阶综合应用案例

下面案例根据用户输入的员工编号，来查询当前员工工资，对于工资低于2000的员工进行加薪，否则直接退出，本案例用到了动态类型 %type、select..into、异常处理等

DECLARE
  mysal testemp.sal%type;--声明变量类型和表中列一致
  eno number;
BEGIN
  SELECT empno,sal into eno, mysal---注意多个值赋值的方式 cols,cols2 into var1,var2
  FROM testemp WHERE empno = &myin;
 IF mysal < 2000 THEN
   GOTO updation;
 ELSIF mysal < 1000 THEN
     UPDATE TESTEMP SET SAL = SAL+800;
    GOTO QUIT;
 ELSE
   GOTO quit;
 END IF;
 <<updation>>
   UPDATE testemp SET sal = sal + 500
   WHERE empno = eno;
   commit;
   dbms_output.put_line('数据更新了');
 <<quit>>
  NULL;
 
  exception 
  when No_data_found then dbms_output.put_line('没发现数据'||sqlerrm);
  when others then dbms_output.put_line('未知错误：'||sqlerrm);
END;

四、视图

在使用 SQL 的过程中，有时可能需要编写的 SQL 过于复杂，比如 多表连接查询且需要分组、筛选、嵌套、排序等综合查询，这样写出的 SQL 在复杂、不利于维护。这时我们使用视图就可以简化和解决这样的问题。除此之外，视图还有安全、隐藏结构等多方面的优点。

1. 视图简介及优点

视图是基于一个或多个表的虚拟表，视图以经过定制的方式显示来自一个或多个表的数据。 视图可以视为 “虚拟表” 或 “存储的查询 ”，创建视图所依据的表称为 “基表”。 视图的优点有： 提供了另外一种级别的表安全性 隐藏的数据的复杂性 简化的用户的SQL命令 隔离基表结构的改变 通过重命名列，从另一个角度提供数据

创建视图的语法：

CREATE [OR REPLACE] [FORCE] VIEW view_name [(alias[, alias]...)] AS select_statement [WITH CHECK OPTION] [WITH READ ONLY];

下面来创建视图及使用视图，创建好视图后，可以把视图和表等同来使用。

--创建视图
  create view emp_view as
  select empno,ename,sal,comm from emp;
--使用视图
  select * from emp_view;
--创建一个有序的视图
  create or replace view order_emp as 
  select * from emp order by sal;

视图可以基于多张基表

  --创建一个联表的视图
  create view two as
  select empno,ename,sal,dname
  from dept d,emp e
  where d.deptno=e.deptno

2. 视图下的 DML 操作

视图可以进行 DML 操作，可以 update， insert , delete ，select 。

insert into emp_view values(8888,'曹操',900,700)--再原表查询
 
delete from emp_view where empno=8888;

但是视图上做 DML 更新操作，有如下限制： a. 只能修改一个底层的基表 b. 如果修改违反了基表的约束条件，则无法更新视图 c. 如果视图包含连接操作符、DISTINCT 关键字、集合操作符、聚合函数或 GROUP BY 子句，则将无法更新视图 d. 如果视图包含伪列或表达式，则将无法更新视图

可以创建带条件的视图或对视图进行条件约束，如果使用 with check option 检查条件后，则只有大于2000 的才能插入视图。

如果使用 with read only 则不能通过视图来做更新操作，只能查询 select 。

--创建一个带条件检查的视图
create or replace view testview 
as
select * from test where sal>2000
--with check option --检查条件 只有大于2000的才能插入视图
with read only      --只读视图

3. 错误视图

错误视图：视图默认情况下不能基于不存在的表创建，但是可以通过 force 关键字来强制创建错误视图,

一般用在开发阶段，现在按着规定名字使用，将来可以追加表。

---创建一个不存在错误的视图 ---FORCE view--
  create FORCE view err as
  select tname,temail from test;

4. 视图的进阶应用

CREATE TABLE userInfo  --用户信息表
(
  customerID number NOT NULL,
  customerName varCHAR2(8) NOT NULL,
  PID varCHAR(18) NOT NULL,
  telephone varCHAR(20) NOT NULL,
  address VARCHAR(50)
);
 
 
CREATE TABLE cardInfo  --银行卡信息表
(
  cardID  CHAR(19) NOT NULL,
  curID  VARCHAR(10) NOT NULL,
  savingID number NOT NULL,
  openDate DATE NOT NULL,
  openMoney number NOT NULL,
  balance  number NOT NULL,
  pass CHAR(6) NOT NULL,
  IsReportLoss NUMBER(1)  NOT NULL,
  customerID NUMBER NOT NULL
);
 
 
CREATE TABLE tradeInfo  --交易信息表
(
  tradeDate  DATE NOT NULL,
  tradeType  CHAR(4) NOT NULL,
  cardID  CHAR(19) NOT NULL,
  tradeMoney  NUMBER NOT NULL,
  remark  LONG   
);
 
CREATE TABLE Deposit  --存款类型表
(
  savingID  NUMBER NOT NULL,
  savingName  VARCHAR(20) NOT NULL,
  descrip VARCHAR(50)
);
--- 创建视图：查询银行卡信息
create or replace VIEW vw_cardInfo  --银行卡信息表视图
  AS 
    select c.cardID as 卡号,u.customerName as 客户,c.curID as 货币种类, d.savingName as 存款类型,c.openDate as 开户日期,
       c.balance as 余额,c.pass 密码,
       case c.IsReportLoss when 0 then '正常'
                           when 1 then '挂失'
       end as 是否挂失
    from cardInfo c, deposit d,userinfo u
    where c.savingID=d.savingID and c.customerID=u.customerID;

全部笔记内容：

Oracle数据库案例教程笔记免费领（视图、游标、PL/SQL、过程、事务、触发器、函数、备份、权限等）_哔哩哔哩_bilibili 或关注公众号：普惠学堂，回复 oracle 即可免费获取全部案例笔记内容！