Oracle数据库高级特性详解-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_43369851/article/details/102714839

文章目录

一、视图（应用）
二、索引（应用）
三、pl/sql语言的基本语法（了解）
- 3.1 什么是pl/sql?
四、存储过程（理解）
五、存储函数（理解）
六、触发器（理解）
七、java与Oracle数据库的连接

一、视图（应用）

视图的概念：视图就是提供一个查询的窗口，所有数据来自于原表

1.1 复制原来Scott表中的子表emp表

create table emp as select * from scott.emp;

1.2 视图的作用

第一：视图可以屏蔽调一些敏感字段。
第二：保证总部和分布数据及时统一

1.3 创建视图（必须有dba权限）

create view v_emp as select ename,job from emp;

1.4 修改视图

不推荐使用：一般直接这样修改也会把原表的数据给修改掉了

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

1.5 创建只读视图（常用）

此视图只能进行查询，并不能对原有表的数据进行更改

create view v_emp1 as select ename,job from emp with read only;

二、索引（应用）

2.1 索引的概念

索引就是在表的列上构建一个二叉树（相当于书中的目录）

① 索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低
i/o 次数,从而提高数据访问性能。
② 索引可以达到大幅度提高查询效率的目的，但是索引会影响增删改的效率。

常用的索引有：单列索引和复合索引

2.2 单列索引

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

语法:CREATE index 索引名 on 表名(列名)

create index idx_ename on emp(ename);

2.2.1 单列所用的触发规则（条件必须是索引列中的原始值）

单行函数，模糊查询，都会影响索引的触发（就是有单行函数或者模糊查询可能不会触发索引）

select * from emp where ename = 'SCOTT';	--触发了（因为SCOTT是原始值）

2.3 复合索引

复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是
要求列的组合必须不同。

创建复合索引

create index idx_enamejob on emp(ename,job);

对于单列索引和复合索引的总结

--- 范例：给person表name建立
create index pname index on person(name);
--- 范例：给person表创建一个name和gender的索引
create index pname index on person(name,gender);

2.4 复合索引的触发规则（必须包含有优先检索列中的原始值）

复合索引中的第一列为优先检索列

select * from emp where ename = 'SCOTT' and job = 'xxx';  --触发了复合索引（优先检索列有原始值）

select * from emp where ename = 'SCOTT' or job = 'xxx';   -- -不触发索引

-----(因为or代表两个分开的执行语句，而前面的select...where ename = 'SCOTT'触发了
-----后面的select...where job = 'xxx'没有触发，一个触发，一个没有触发，总的还是没有触发)

select * from emp where ename = 'SCOTT';    --触发了复合索引（优先检索列有原始值）

2.5 索引的使用原则

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

三、pl/sql语言的基本语法（了解）

3.1 什么是pl/sql?

PL/SQL（Procedure Language/SQL）
PLSQL 是Oracle 对sql 语言的过程化扩展，指在SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL
语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PLSQL 面向过程但比过程语言简单、高效、灵活和实用。

pl/sql编程语言主要用来编写存储过程和存储函数等。

范例 1：为职工涨工资，每人涨 10％的工资
update emp set sal=sal*1.1
范例 2：按职工的职称长工资,总裁涨 1000 元,经理涨800 元，其他人员涨 400 元
这样的需求我们就无法使用一条 SQL
来实现，需要借助其他程序来帮助完成，也可以使用 pl/sql

3.1.1 pl/sql程序语法

声明方法：declare begin end;

---声明方法
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;
       dbms_output.put_line(ena);
       select *  into emprow from emp where empno = 7788;
       dbms_output.put_line(emprow.ename||'的工作为'||emprow.job);
end;

3.1.2 常量和变量定义

在程序的声明阶段可以来定义常量和变量

变量的基本类型

变量的基本类型就是 oracle 中的建表时字段的变量如 char,
varchar2, date, number, boolean, long

定义语法：varl char(15);
Psal number(9,2);

引用变量

Myname emp.ename%type;
引用型变量，即my_name 的类型与emp 表中 ename 列的类型一样在 sql 中使用into 来赋值

declare
  emprec emp.ename%type; 
begin
	select t.ename into emprec
	from emp t where t.empno = 7369;
	dbms_output.put_line(emprec); 
end;

记录型变量

Emprec emp%rowtype
记录变量分量的引用

3.1.3 if 分支

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

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

3.1.4 pl/sq中的三个循环：while循环，exit循环，for循环

案例：用三种方式输出1到10的数字

3.1.4.1 while循环

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

3.1.4.2 exit循环（比较常用）

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

3.1.4.3 for循环

---for循环
declare 
      
begin
      for i in 1..10 loop
          dbms_output.put_line(i);
      end loop;
end;

3.1.5 游标 Cursor

在写 java 程序中有集合的概念，那么在 pl/sql 中也会用到多条记录，这时候我们就要用到游标，游标可以存储查询返回的多条数据。

语法：
案例1：输出emp表中所有员工的姓名

---输出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;

案例2：给指定部门员工涨工资

---给指定部门员工涨工资
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;

---查询10号部门的人的工资
select * from emp where deptno = 10;

四、存储过程（理解）

4.1 存储过程的概念

存储过程（StoredProcedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

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

基本语法：
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
AS
begin
PLSQL子程序体
end;

4.2 in和out类型的参数的区别是什么

存储过程使用了out类型的参数，其本质也不是真的有了返回值。
而是在存储过程内部给out类型参数城值，在执行完转后，我们直接拿到输出类型的值

注意：凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰

4.3 入门案例

入门案例：给指定员工涨工资

create or replace procedure p1(eno emp.empno%type)
is   ----(这里或者使用as也可以，一样的作用)

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

select * from emp where empno = 7788;
---- 测试
declare
begin
 p1(7788);
end;

五、存储函数（理解）

5.1 基本语法

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

5.2 案例：计算指定员工的年薪

5.2.1 通过存储函数来实现案例

注意：存储函数的返回值类型不能带长度

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;
----测试f_yearsal
declare
  s number(10);
begin
  s := f_yearsal(7788);
  dbms_output.put_line(s);
end;

5.2.2 通过存储过程来实现案例（此案例使用了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;

5.3 存储过程和存储函数的区别

5.3.1 语法区别：关键字不一样

1.存储过程使用的是procedure，而存储函数使用的是function
2.存储函数比存储过程多了两个return

5.3.2 本质区别：存储函数有返回值，而存储函数没有返回值

如某存储过程想实现有返回值的业务，我们就必须使用out类型的参数。
即便是存储过程使用了out类型的参数，其本质也不是真的有了返回值，
而是在存储过程内部给out类型参数城值，在执行完转后，我们直接拿到输出类型的值。

案例2：查询出员工姓名，员工所在部门名称（自定义函数）

我们可以使用存储函数有返回值的特性，来自自定义函数，而存储过程不能用来自定义函数。

案例准备工作：把scott用户下的dept表复制到当前用户下
create table dept as select * from scott.dept;
① 使用传统方式来实现需求

---使用传统方式来实现案例需求
select
  e.ename,d.dname
from
  emp e,dept d
where
  e.deptno = d.deptno;

② 使用存储函数来实现案例需求

---使用存储函数来实现提供一个部门编号，输出一个部门名称
create or replace function fdna(dno dept.deptno%type) return varchar2
is
  s varchar2(14);
begin
  select dname into s from dept where deptno = dno;
  return s;
end;

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

六、触发器（理解）

6.1 触发器的概念及作用

概念：数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

触发器，就是制定一个规则，在我们做增删改操作的时候，只要满足该规则，自动触发，无需调用。(通俗理解)

触发器可用于：
1.数据确认
2.实施复杂的安全性检查
3. 做审计，跟踪表上所作的数据操作等
4.数据的备份和同步

6.2 触发器的类型

加for each row是为了使用：old或者:new对象或者一行记录

在触发器中触发语句与伪记录的值

触发语句	:old	:new
Insert	所有字段都是空(null)	将要插入的数据
Update	更新以前该行的值	更新后的值
Delete	删除以前该行的值	所有字段都是空（null）

6.2.1 语句级触发器：不包含有for each row的触发器

案例实现：

---语句级触发器
----插入一条记录，输出一个新员工入职
create or replace trigger t1
after
insert
on person
declare

begin
   dbms_output.put_line('一个新员工入职');	
end;

---触发t1
insert into person values (3,'小华');
commit;

6.2.2 行级触发器：包含有for each row的触发器

案例实现：

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

---触发t2
update emp set sal = sal - 1 where empno = 7788;
commit;

select * from emp where empno = 7788;

6.3 案例：实现主键自增（行级触发器）——实战常用

---分析：在用户做插入操作的之前，拿到即将插入的数据
---------给该数据中的主键列赋值
create or replace trigger auid
before
insert
on person
for each row
declare

begin
   select  s_person.nextval into :new.pid from dual;
end;
---查询person表数据
select * from person;
select s_person.nextval from dual;
----使用auid实现主键自增
insert into person  values (1,'b');
commit;

七、java与Oracle数据库的连接

String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@192.168.79.10:1521:orcl";
String username = "scott";
String password = "tiger";

代码实现：

public class OracleDemo {

    /**
     * java连接oracle数据库进行简单的增删改查
     * @throws Exception
     */
    @Test
    public void javaAndOracle() throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接对象
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "hugong", "hugong");
        //得到预编译的Statement对象
        PreparedStatement statement = connection.prepareStatement("select * from emp where empno = ?");
        //给参数赋值
        statement.setObject(1,7788 );
        //执行数据库查询操作
        ResultSet rs = statement.executeQuery();
        //输出结果
        while(rs.next()) {
            System.out.println(rs.getString("ename"));
        }
        rs.close();
        statement.close();
        connection.close();
    }

    /**
     * java调用过程
     * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储函数使用
     * {call <procedure-name>[(<arg1>,<arg2>, ...)]}    调用存储过程使用
     * @throws Exception
     */
    @Test
    public void javaCallProcedure() throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接对象
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "hugong", "hugong");
        //得到预编译的Statement对象
        CallableStatement statement = connection.prepareCall("{call p_yearsal(?,?)}");  //查询员工年薪
        //给参数赋值
        statement.setObject(1,7788 );
        statement.registerOutParameter(2, OracleTypes.NUMBER );
        //执行数据库查询操作
        statement.execute();
        //输出结果[第二个参数]
        System.out.println(statement.getObject(2));
        statement.close();
        connection.close();
    }

    /**
     * java调用存储函数
     * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储函数使用
     * {call <procedure-name>[(<arg1>,<arg2>, ...)]}    调用存储过程使用
     * @throws Exception
     */
    @Test
    public void javaCallFunction() throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //得到Connection连接对象
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "hugong", "hugong");
        //得到预编译的Statement对象
        CallableStatement statement = connection.prepareCall("{?= call f_yearsal(?)}"); //查询员工年薪
        //给参数赋值
        statement.setObject(2,7788 );
        statement.registerOutParameter(1, OracleTypes.NUMBER );
        //执行数据库查询操作
        statement.execute();
        //输出结果[第一个参数]
        System.out.println(statement.getObject(1));
        statement.close();
        connection.close();
    }
}