【JavaEE学习笔记】Oracle_04_存储过程,函数,触发器,索引,同义词

Oracle_04

A.存储过程

1.概述

是存入数据库的命名PL/SQL块

PL/SQL程序通常称为无名块

而存储过程是以命名的方式存储于数据库中的

2.优点

a.存储过程以命名的数据库对象形式存储于数据库当中

优点是很明显的：因为代码不保存在本地

用户可以在任何客户机登录到数据库，并调用或修改代码

b.存储过程可由数据库提供安全保证

要想使用存储过程和函数，需要有存储过程的所有者的授权

只有被授权的用户或创建者本身才能执行存储过程

c.存储过程的信息是写入数据字典的

所以存储过程可以看作是一个公用模块

用户编写的PL/SQL程序或其他存储过程都可以调用它

但存储过程不能调用PL/SQL程序

一个重复使用的功能，可以设计成为存储过程

比如：显示一张工资统计表，可以设计成为存储过程

d.像其他高级语言的过程一样，可以传递参数给存储过程

参数的传递也有多种方式，存储过程可以有返回值，也可以没有返回值

存储过程的返回值必须通过参数带回

3.语法

create [or replace] procedure

pro_name[(parameter1[,parameter2]...)] is|as

[inner variable declare]

begin

plsql_sentence;

[exception]

[dowith_sentence;]

end [pro_name];

备注：

parameter1是存储过程被调用时候的参数

输入参数则其后必须指定in关键字

输出参数则其后必须指定out关键字

in或out后面是数据类型，但是不能指定数据类型长度

is|as与begin之间可以声明变量， 变量带取值范围，后面接分号

4.练习

a.获取emp中，empno=7369的雇员名字：无参

1)创建存储过程

-- 创建无参存储过程create or replace procedure pro1 is    v_name varchar2(128);begin    select ename into v_name from emp    where empno = 7369;    -- 打印    dbms_output.put_line('雇员名字是：'||v_name);end;

查看存储过程

2)执行存储过程

-- 第一次使用前要打输出信息set serveroutput on;-- 执行存储过程call pro1();

b.获取emp中，empno=7369的雇员名字：有一个in输入参数

-- 创建存储过程create or replace procedure pro1(vno in number) is    v_name varchar2(128);begin    select ename into v_name from emp    where empno=vno;    dbms_output.put_line('雇员的名字是：'||v_name);end;-- 执行call pro1(7369);

c.out模式参数，输入empno，输出ename，job

out模式存储过程调用

a.先声明变量，然后传入exec命令执行，最后print打印

b.使用print 检索绑定的变量

variable vno varchar2(20);

variable vanem varchar2(20);

exec pro1(7369,:vname);

print vname;

-- 创建存储过程create or replace procedure pro1(    vno in number,    vname out varchar2,    vjob out varchar2) isbegin    select ename,job into vname,vjob from emp    where empno=vno;-- 异常exception    when no_data_found then    dbms_output.put_line(vno||'员工编号不存在');end;-- 执行declare    vname varchar2(20);    vjob varchar2(20);begin    -- 调用存储过程    pro1(7369,vname,vjob);    dbms_output.put_line(vname||'---'||vjob);end;

d.in out参数，输入员工姓名，返回职务(输入输出类型相同)

-- 创建存储过程-- 输入vn，输出vncreate or replace procedure pro1(vn in out varchar2) isbegin    select job into vn from emp where ename=vn;    exception        when no_data_found then        dbms_output.put_line(vn||':员工姓名不存在');end;-- 执行declare    vn varchar2(20):='SMITH';begin    pro1(vn);    dbms_output.put_line(vn);end;

5.删除存储过程

-- 删除存储过程drop procedure pro1;

6.缺点

a.不可移植性

每种数据库的内部编程语法都不太相同

当你的系统需要兼容多种数据库时，最好不要用存储过程

b.学习成本高

DBA一般都擅长写存储过程，但并不是每个程序员都能写好存储过程

除非你的团队有较多的开发人员熟悉写存储过程

否则后期系统维护会产生问题

c.维护调试成本

业务逻辑多处存在，采用存储过程后

也就意味着你的系统有一些业务逻辑不是在应用程序里处理

这种架构会增加一些系统维护和调试成本

d.和常用应用程序语言区别

存储过程和常用应用程序语言不一样

它支持的函数及语法有可能不满足需求

有些逻辑只能通过应用程序处理

e.可扩展属性问题

如果存储过程中有复杂运算的话

会增加一些数据库服务端的处理成本

对于集中式数据库可能会导致系统可扩展性问题

f.提高性能

为了提高性能，数据库会把存储过程代码编译成中间运行代码

类似于Java的class文件，所以更像静态语言

当存储过程引用的对像(表、视图等等)结构改变后

存储过程需要重新编译才能生效

在24*7高并发应用场景，一般都是在线变更结构的

所以在变更的瞬间要同时编译存储过程

这可能会导致数据库瞬间压力上升引起故障

Oracle数据库就存在这样的问题

B.函数

1.概述

是存入数据库的命名PL/SQL块

函数是以命名的方式存储于数据库中的

函数用于计算和返回一个值，可以将常用的计算或功能写成函数

2.与存储过程的区别

函数和存储过程比较类似，可以接受零或多个输入参数

但是函数必须有且只有一个返回值(存储过程没有)

应用场景

a.函数

一般情况下是用来计算并返回一个计算结果

b.存储过程

一般是用来完成特定的数据操作

比如修改、插入数据库表或执行某些DDL语句等等

如果只有一个返回值，用存储函数，否则，一般用存储过程

3.分类

a.内置函数

upper(),concat(),substr()

b.用户自定义函数

4.语法

create ro replace function function_name

[(parameter1[,parameter2]...)]

return return_datatype is/as

[inner variable decalre]

begin

  pl/sql sentence;

  [exception]

  [dowith_sentence;]

end function_name;

5.练习

a.查找emp中empno=7369的员工姓名，无参

1)定义函数

-- 定义函数create or replace function getName return varchar2 is    vname varchar2(20);begin    select ename into vname from emp    where empno=7369;    return vname;end;

2)查看结果

3)执行函数

-- 调用函数declare    vn varchar2(20);begin    vn:=getName();    dbms_output.put_line(vn);end;

b.一个输入参数

-- 创建函数create or replace function getName(vno number)return varchar2 is    vname varchar2(20);begin    select ename into vname from emp    where empno=vno;    return vname;end;-- 调用函数declare    vn varchar2(20);begin    vn:=getName(7369);    dbms_output.put_line(vn);end;

c.带有输出参数

-- 创建函数create or replace function getName(    vno in number,    vname out varchar2    ) return varchar2 isbegin    select ename into vname from emp    where empno=vno;    return vname;end;-- 调用declare    vn varchar(20);begin    vn:=getName(7369,vn);    dbms_output.put_line(vn);end;

6.删除函数

-- 删除函数drop function getName;

C.触发器

1.概述

可以看做特殊的存储过程

定义了数据库相关事件(insert update delete等)发生时应执行的功能代码块

通常用于管理完整性约束或监控表的修改

或通知其他程序或实现数据的审计功能等

触发器有声明：执行和异常处理过程的PL/SQL块

2.语法

create [or replace] trigger 触发器名称

{before|after}

{insert|delete|update[of column [,column...]]}

or {insert|delete|update[of column [,column...]]}

on [schema.] 表名|[schema.]视图

[for each row]

[when condition]

begin

执行语句;

end;

详细说明：

or replace带上则为覆盖

before在触发事件之前执行

after在触发事件之后执行

insert/delete/update在插入、删除、更新操作时触发

or可以多个操作同时定义触发器

on对哪一个表或视图进行监控

for each row带上是对每一条数据都记录

when condition 条件表达式

示例：自增长主键(参考)

【JavaEE学习笔记】Oracle_02_序列,分页,常用函数,jdbc,PL/SQL——A

D.package包

1.概述

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

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

包类似于C++或Java程序中的类，而变量相当于类中的成员变量

过程和函数相当于方法，把相关的模块归类成为包

可使开发人员利用面向对象的方法进行存储过程的开发，从而提高系统性能

与类相同，包中的程序元素也分为公用元素和私有元素两种

这两种元素的区别是他们允许访问的程序范围不同，即他们的作用域不同

公用元素不仅可以被包中的函数、过程调用

也可以被包外的PL/SQl块调用

而私有元素只能被该包内部的函数或过程调用

2.优点

在PL/SQL设计中，使用包不仅可以使程序模块化，对外隐藏包内所使用的信息

而且程序包可以提高程序的运行效率

因为，当程序首次调用程序包内部的函数或过程时

Oracle将整个程序包调入内存，当再次调用程序包中的元素时

Oracle直接从内存中读取，而不需要进行磁盘的IO操作，从而使程序的执行效率提高

3.程序包的组成

a.包定义

包定义部分声明包内数据类型、变量、常量、游标、子程序和函数等元素

这些元素为包的共有元素，相当于接口，定义规范

b.包主体

包主题则定义了包定义部分的具体实现

在包主体中还可以声明和实现私有元素，相当于实现功能的类

前面使用的DBMS_OUTPUT.PUT_LINE就是程序包的一个

DBMS_OUTPUT是包名，PUT_LINE是存储过程名

4.包声明

a.语法：

CREATE [OR REPLACE] PACKAGE package_name

[AUTHID {CURRENT_USER | DEFINER}] {IS | AS}

[公有数据类型定义[公有数据类型定义]…]

[公有游标声明[公有游标声明]…]

[公有变量、常量声明[公有变量、常量声明]…]

[公有函数声明[公有函数声明]…]

[公有过程声明[公有过程声明]…]

END [package_name];

b.练习

创建一个程序包“规范”

首先在该程序中声明一个可以获取指定部门的平均工资的函数

然后再声明一个可以实现按照指定比例上调指定职务的工资的存储过程

-- 包声明create or replace package pack_emp is    -- 定义一个函数，获取制定部门的平均工资    function fun_avg_sal(num_deptno number)    return number;    -- 定义一个过程，按照比例上调工资    procedure pro_regulate_sal(        var_job varchar2,        num_proportion number    );end pack_emp;

5.包体

a.语法

CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}

[私有数据类型定义[私有数据类型定义]…]

[私有变量、常量声明[私有变量、常量声明]…]

[私有异常错误声明[私有异常错误声明]…]

[私有函数声明和定义[私有函数声明和定义]…]

[私有函过程声明和定义[私有函过程声明和定义]…]

[公有游标定义[公有游标定义]…]

[公有函数定义[公有函数定义]…]

[公有过程定义[公有过程定义]…]

BEGIN

执行部分(初始化部分)

END package_name;

b.练习

实现上面定义的包

-- 实现定义的包create or replace package body pack_emp is   -- 函数    function fun_avg_sal(        num_deptno number    ) return number is    num_avg_sal number;    begin        -- 计算平均工资并返回        select avg(sal) into num_avg_sal from emp        where deptno=num_deptno;        return(num_avg_sal);        exception            when no_data_found then                dbms_output.put_line('该部门编号无雇员');                return 0;        end fun_avg_sal;    -- 过程    procedure pro_regulate_sal(        var_job varchar2,        num_proportion number    ) is    begin        update emp set sal=sal*(1+num_proportion)        where job=var_job;    end pro_regulate_sal;end pack_emp;

-- 调用包中的函数和过程declare    -- 部门编号    num_deptno emp.deptno%type;    -- 职务    var_job emp.job%type;    -- 平均工资    num_avg_sal emp.sal%type;    -- 上调比例    num_proportion number;begin    num_deptno:=10;    num_avg_sal:=pack_emp.fun_avg_sal(num_deptno);    dbms_output.put_line(num_deptno||'号部门的平均工资是：'||num_avg_sal);    var_job:='SALESMAN';    num_proportion:=0.1;    pack_emp.pro_regulate_sal(var_job,num_proportion);end;

6.删除包

drop package pack_emp;

E.Java调用存储过程和函数

1.步骤（存储过程为例）

a.call＋包名＋存储过程名（传入、传出值用？）

String str="{call  pro1(?,?)}";

b.建立连接

 Connection conn=null; conn=DriverManager.getConnection();

c.使用java.sql.*类

 CallableStatement cs=conn.prepareCall(str);

d.传入in值

cs.setInt(1,7369);

e.设置out值

cs.registerOutParameter(2,Types.VARCHAR);

f.执行

cs.excute();

g.根据参数位置取出out值

String name=cs.getString(2);

h.关连接

conn.close(); 

2.练习

a.无返回值存储过程

1)建立存储过程

create or replace procedure addDept(    vno in number,    vname in varchar2,    vloc in varchar2) isbegin    insert into dept values(vno,vname,vloc);end;

2)Java实现

package org.xxxx.oracle;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class Demo01 {public static void main(String[] args) throws ClassNotFoundException, SQLException {// 加载驱动Class.forName("oracle.jdbc.OracleDriver");String url = "jdbc:oracle:thin:@localhost:1521:orcl";String user = "scott";String password = "123456";// 获取连接Connection conn = DriverManager.getConnection(url, user, password);// 定义sql语句String sql = "{call addDept(?,?,?)}";// 获取预编译对象CallableStatement cs = conn.prepareCall(sql);// 传入in值cs.setInt(1, 29);cs.setString(2, "teach");cs.setString(3, "China");// 执行cs.execute();// 关闭conn.close();}}

查询数据库

b. 使用java代码调用有一个输入参数 一个输出参数的存储过程pro1

1)建立存储过程

-- 创建存储过程create or replace procedure getName(    vno in number,    vname out varchar2) isbegin    select ename into vname from emp    where empno=vno;-- 异常exception    when no_data_found then    dbms_output.put_line(vno||'员工编号不存在');end;

2)Java实现

package org.xxxx.oracle;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Types;public class Demo01 {public static void main(String[] args) throws ClassNotFoundException, SQLException {// 加载驱动Class.forName("oracle.jdbc.OracleDriver");String url = "jdbc:oracle:thin:@localhost:1521:orcl";String user = "scott";String password = "123456";// 获取连接Connection conn = DriverManager.getConnection(url, user, password);// 定义sql语句String sql = "{call getName(?,?)}";// 获取预编译对象CallableStatement cs = conn.prepareCall(sql);// 传入in值cs.setInt(1, 7369);// 设置out值cs.registerOutParameter(2, Types.VARCHAR);// 执行cs.execute();// 取出out值String ename = cs.getString(2);System.out.println(ename);// 关闭conn.close();}}

3.Java调用函数

和存储过程很相似，但是有返回值参数

1)创建函数

-- 创建函数create or replace function getName(    vno in number,    vname out varchar2    ) return varchar2 isbegin    select ename into vname from emp    where empno=vno;    return vname;end;

2)Java代码

package org.xxxx.oracle;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Types;public class Demo01 {public static void main(String[] args) throws ClassNotFoundException, SQLException {// 加载驱动Class.forName("oracle.jdbc.OracleDriver");String url = "jdbc:oracle:thin:@localhost:1521:orcl";String user = "scott";String password = "123456";// 获取连接Connection conn = DriverManager.getConnection(url, user, password);// 定义sql语句String sql = "{?=call getName(?,?)}";// 获取预编译对象CallableStatement cs = conn.prepareCall(sql);// 设置返回值类型cs.registerOutParameter(1, Types.VARCHAR);// 传入in值cs.setInt(2, 7369);// 设置out值cs.registerOutParameter(3, Types.VARCHAR);// 执行boolean flag = cs.execute();// 判断if (!flag) {String ename = cs.getString(1);System.out.println(ename);}// 关闭conn.close();}}

F.数据库索引

1.概述

表中有海量的数据，执行指定条件的查询时候，会读取所有的记录

然后依次比对条件，最后返回结果，时间开销和I/O开销都十分巨大

因此要引入索引

索引是帮助数据库高效获取结果数据的数据结构

是在海量数据中应用的一种高效的查询算法

最终是为了提高数据的查询速度

Oracle对索引管理和其他对象有很多相同之处

不仅在数据字典中保存索引的定义

还需要在表空间中分配实际的存储位置

默认放在用户默认表空间中

2.分类

按照存储方式分为

B树索引

位图索引 

反向键索引 

基于函数的索引

创建索引时，oracle首先对将要建立索引的字段进行排序

然后将排序后的字段和ROWID存储在索引段中

3.基本语法

create index 索引名 on 表名(列名)

4.B树索引

基于B树结构组织并存放索引数据，是oracle最常用的索引，默认升序排列索引数据

如果表中数据较多，并且经常在where子句中引用某列或某几列

则应该基于该列或几列创建B树索引

包含根块，分支块，叶块组成，无论索引条目位于何处

均花费相同的I/O获取，即索引层次相同

每个索引条目（或块）都具有两个字段

第一个字段表示当前该分支节点块下面所链接的索引块中，所包含的最小键值

第二个字段为四个字节，表示所链接的索引块的地址，该地址指向下面一个索引块

叶子节点中第一个字段是键值，第二个是ROWID

在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定

练习

在scott模式下，为emp的deptno列创建索引

-- 创建emp_deptnp_index索引给emp的deptno字段create index emp_deptno_index on emp(deptno)pctfree 25 tablespace users;

其中pctfree是为将来的insert操作预留空间

5.位图索引

主要针对有大量相同值的列而创建

如果使用Btree索引，导致返回接近一半的记录，失去了索引的意义

例如在表的性别列添加索引

它的组织形式与B树索引相同，也是一棵平衡树

与B树索引的区别在于叶子节点里存放索引条目的方式不同

位图索引条目上还包含表里第一条记录，所对应的ROWID

以及最后一条记录，所对应的ROWID

最后部分则是由多个bit位所组成的bitmap，每个bit位对应一条记录

查询条件为‘01’时，oracle会去搜索01所在的叶子节点

然后扫描该索引条目中的bitmap里所有的bit位

第一个bit位为 1，则说明第一条记录上的C1值为01

于是返回第一条记录所在的ROWID

根据该索引条目里记录的start ROWID加上行号得到该记录所在的ROWID

位图索引适合唯一值很少的列，也就是重复值很多的列

如果基数低于1%，该列不适合Btree索引，而适合位图索引

基数是索引列中不同值数量与总行数的比例

create bitmap index emp_deptno_bmp on emp(deptno)tablespace users;

6.反向键索引

Oracle会自动为表的主键列建立索引，这个默认的索引是普通的B-Tree索引

对于主键值是按顺序(递增或递减)加入的情况，默认的B-Tree索引并不理想

这是因为如果索引列的值具有严格顺序时，随着数据行的插入

索引树的层级增长很快，导致数据读取层级增加很快，

减少索引的层级数是索引性能调整的一个重要方法

而如果索引列的数据以随机值的方式插入

我们将得到一棵趋向对称的索引树

反向键索引是特殊类型的BTREE索引，在顺序递增列上创建索引时候非常有用

即预先对列值进行比特位的反向，如1000,11001经过反向后的值将是0001,1101

经过位反向处理的有序数据变得比较随机了

这样所得到的索引树就比较对称，从而提高表的查询性能

create index emp_deptno_reverse on emp(deptno)reverse tablespace users;

7.基于函数的索引

Oracle对大小写敏感

如emp表的job列记录为MANAGER时，小写输入是不匹配的

可以将输入转换为大写进行匹配

但这样用户不是基于表中数据进行查询的

在job列上创建的索引不会生效，而会进行全表搜索

然后给对应列进行upper函数

sql语句中经常使用小写字符串，函数索引能加快速度

create index emp_job_fun on emp(upper(job));

如果查询条件包含相同的函数，则该索引会生效提高查询速度

select * from emp where upper(job)='MANAGER';

8.索引删除

drop index emp_job_fun;

9.索引注意事项

a.建立在where子句频繁引用的列上

b.经常基于某列或某几列上排序，添加索引加快排序速度

c.在连接列上创建索引，提高多表连接的性能

d.限制索引的个数，索引降低dml操作速度

e.指定索引块空间的使用参数，使用PCTFREE参数预留Insert操作空间

f.表和索引存放在不同的表空间提高访问性能

g.大表上创建索引时候使用nologging参数最小化重做日志记录，以节省重做日志空间

h.小表上不要创建索引

G.同义词

1.概述

是表 、索引、视图等模式对象的别名

可以隐藏实际名称和所有者，提供一定的安全保证

同义词仅在数据字典中保存定义，不占用实际的存储空间

使用同义词，即使引用的对象发生变化，不必改变应用程序

仅在数据库中修改同义词即可

普通用户创建的同义词一般都是私有同义词

公有同义词一般由DBA创建，普通用户如果希望创建同义词

则需要CREATE PUBLIC SYNONYM这个系统权限

2.公用同义词

由一个特殊的用户组Public所拥有

数据库中所有的用户都可以使用公用同义词

往往用来表示一些比较普通的数据库对象

这些对象往往大家都需要引用

3.私有同义词

是由创建他的用户所有

创建者可以通过授权，控制其他用户是否有权使用属于自己的私有同义词

4.权限

与同义词相关的权限有

a.CREATE SYNONYM

用户在自己的模式下创建私有同义词

这个用户必须拥有CREATE SYNONYM权限，否则不能创建私有同义词

b.CREATE ANY SYNONYM

如果需要在其它模式下创建同义词

则必须具有CREATE ANY SYNONYM的权限

c.CREATE PUBLIC SYNONYM

创建公有同义词则需要CREATE PUBLIC SYNONYM系统权限

5.语法

CREATE [OR REPLACE] [PUBLIC] SYNONYM [ schema.]

同义词名称  FOR [ schema.] object [ @dblink ];

-- 专有同义词create synonym sysn_test for emp;-- 公共同义词create public synonym public_test for test;