第9章 序列、同义词、程序包

第 1 页 共 18 页 第第 9 章章 序列 同义词 程序包序列 同义词 程序包 学习目标 了解序列 同义词 同义词的概念 掌握序列的定义及应用方法 掌握同义词的定义及应用方法 掌握程序包在程序设计中的应用方法 9 1 序列 序列 Sequence 是可以是被多个用户使用的用于产生一系列唯一数字的数据库对象 序 列定义存储在数据字典中 序列通过提供唯一数值的顺序表用于简化程序设计工作 当一 个序列第一次被查询调用时 它将返回一个预定值 在随后的每一次查询中 序列将产生 一个按其指定的增量增长的值 9 1 1 创建序列 1 创建序列 创建序列是使用 CREATE SEQUENCE 命令完成的 如果要在当前方案中建立序列 用户必须具有 CREATE SEQUENCE 系统权限 如果要在其他方案中建立序列 要求用户 必须具有 CREATE ANY SEQUENCE 系统权限 建立序列的基本语法如下 CREATE SEQUENCE schema sequence name INCREMENT BY integer START WITH integer MAXVALUE integer NOMAXVALUE MINVALUE integer NOMINVALUE CYCLE NOCYCLE CACHE integer NOCACHE ORDER NOORDER 其中 INCREMENT BY integer 指定序列以 integer 递增 如果不指定 integer 使用默 认值 1 START WITH integer 序列的起始值为 integer 若不指定该值 对升序序列将 使用该序列默认的最小值 对降序序列 将使用该序列默认的最大值 MAXVALUE integer NOMAXVALUE 设置最大值 integer 或不设置最大值 MINVALUE integer NOMINVALUE 设置最小值 integer 或不设置最小值 CYCLE NOCYCLE 当达到最大值时循环或不循环 CACHE integer NOCACHE 预分配缓存大小 默认为 20 ORDER NOORDER 默认情况下是 NOORDER 这两个参数控制序列是否按照 顺序生成 只有在 RAC 环境中 这两个参数才有实际意义 第 2 页 共 18 页 例 9 1 建立序列 seq 1 代码如下 CREATE SEQUENCE seq 1 START WITH 50 INCREMENT BY 10 MAXVALUE 99 CACHE 10 执行了以上语句后 会建立序列 seq 1 序列 seq 1 的第一个序列号 50 序列增量为 10 因为指定其最大值为 99 所以将来要生成的序列号顺序为 50 60 70 80 90 2 虚表和伪列 虚表 DUAL 主要是为保证在使用 SELECT 语句中的语句的完整性而提供的 DUAL 表是 Oracle 中的一个实际存在的表 常用在没有目标表的 SELECT 操作中 DUAL 表由 Oracle 连同数据字典一同创建 所有的用户都可以访问该表 这个表只有一个列 DUMMY 该列定义为 VARCHAR2 1 类型 有一行值 X Oracle 系统为了实现完整的关系数据库功能 还专门提供了一组伪列 这些列不是在 建立对象时由我们完成的 而是由 Oracle 完成的 Oracle 主要有以下伪列 CURRVAL AND NEXTVAL 使用序列号的保留字 LEVEL 查询数据所对应的级 ROWID 记录的唯一标识 ROWNUM 限制查询结果集的数量 3 序列的使用 1 利用 NEXTVAL 伪列添加数据 格式如下 INSERT INTO 方案名 表名 VALUES SYSTEM 序列名 NEXTVAL COMMIT 2 利用虚表 DUAL 访问序列的值 格式如下 SELECT 方案名 序列名 NEXTVAL FROM DUAL SELECT 方案名 序列名 CURRVAL FROM DUAL 9 1 2 修改序列 修改序列是使用 ALTER SEQUENCE 命令完成的 数据库用户可以直接修改其自身方 案的序列 如果要修改其它方案的序列 则要求该用户必须具有 ALTER ANY SEQUENCE 系统的权限 需要注意 START WITH 选项不能被修改 修改序列基本语法如下 ALTER SEQUENCE schema sequence name INCREMENT BY integer 递增或递减值 MAXVALUE integer NOMAXVALUE 最大值 MINVALUE integer NOMINVALUE 最小值 CYCLE NOCYCLE 是否循环 CACHE integer NOCACHE 高速缓冲区设置 ORDER NOORDER 例 9 2 将序列 seq 1 的最大值修改为 200 缓冲值修改为 20 代码如下 ALTER SEQUENCE seq 1 第 3 页 共 18 页 MAXVALUE 200 CACHE 20 9 1 3 删除序列 当序列不再需要时 数据库用户可以执行 DROP SEQUENCE 命令删除序列 例 9 3 删除序列 seq 1 代码如下 DROP SEQUENCE seq 1 9 1 4 使用 OEM 管理序列 4 创建序列 1 登录 OEM 选择 管理 属性页 单击 数据库对象 下的 序列 超链接 打 开 序列 页面 如图 9 1 所示 图 9 1 序列 页 2 单击 创建 按钮 进入 创建序列 页面 如图 9 2 所示 在该页面设置新序列 的信息 创建序列 页面包含以下信息 第 4 页 共 18 页 图 9 2 创建序列 页 一般信息 项包含以下信息 名称 创建序列的名称 注意 序列名称必须是一个有效的 Oracle 标识符 方案 在方案文本框输入该序列的方案 也可以通过 手电筒 形状的按钮打开 方 案选择 页面选择方案 默认方案是当前用户对应的方案 值 项包含以下信息 最大值 序列可允许的最大值 也可以设置最大值 无限制 最小值 序列可允许的最小值 也可以设置最小值 无限制 选项 项包含以下信息 循环值 选中此选项 即指定在达到序列最小值或最大值之后 序列应继续生成值 对升序序列来说 在达到最大之后将生成最小值 对降序序列来说 在大到最小值后将生 成最大值 如果未选中 循环值 序列将在达到最小值或最大值后停止生成任何值 创建 序列时 该复选框在默认情况下是未选中状态 排序值 选中该选项 即指定序列号要按请求次序生成 创建序列时 该复选框在默 认情况下是未选中状态 高速缓存选项 若勾选该选项 需要设置高速缓存大小 默认值为 20 也可以 指定值 可接受的最小值为 2 对循环序列来说 该值必须小于循环中值的个数 如果序 列能够生成的值数的上限小于高速缓存的大小 则高速缓存大小将自动改换为该上限值 默认情况下选中此选项 如果没有勾选该选项 则指定不预分配序列值 3 各参数设置完成后 单击 确定 按钮即可 5 修改序列 序列创建之后 可以利用 OEM 对其进行修改 在如图 9 1 所示的页面中 选择要修改 的序列 单击 编辑 按钮 进入 编辑序列 页面进行修改即可 如图 9 3 所示 第 5 页 共 18 页 图 9 3 编辑序列 页 6 删除序列 进入 序列 管理页面后 如果要删除序列 应首先在图 9 1 中 选中序列 然后单 击 删除 按钮 进入 确认 页面 如果确认删除序列 单击按钮 是 如果要保留序 列 单击按钮 否 9 2 同义词 同义词是方案对象如 表 索引 视图等对象的别名 在使用同义词时 Oracle 会将 其翻译成对应的对象名称 在数据库应用程序开发过程中 应尽量避免直接引用表 视图 等数据库对象名称 应该用这些对象的同义词 这样当 DBA 对数据库对象做修改或变动 时 可以避免重新编译应用程序 使用同义词 当引用对象发生变化时 只需要对同义词 进行修改即可 而不必改动应用程序 在 Oracle 数据库中 同义词包括公有同义词和私有同义词 其中 公有同义词在数据 库中所有用户都可以使用 这种同义词由 PUBLIC 用户组拥有 私有同义词由创建它的用 户所拥有 用户可以控制其他用户是否有权使用自己的同义词 9 2 1 创建同义词 创建公有同义词是使用 CREATE PUBLIC SYNONYM 命令完成的 创建私有同义词是 使用 CREATE SYNONYM 命令完成的 基本语法如下 CREATE PUBLIC SYNONYM schema synonym name FOR schema object dblink 其中 PUBLIC 表示创建公有同义词 第一个 schema 指向将要创建同义词所属的方案 默认 情况下为当前用户 第二个 schema 表示同义词指向的对象所属方案 同义词指向的对象可 以是表 视图 过程 函数 包和序列 dblink 表明同义词是远程数据库同义词 第 6 页 共 18 页 例 9 4 建立基于 课程表 的公用同义词 ty kc 代码如下 CREATE PUBLIC SYNONYM ty kc FOR 课程表 该同义词属于 PUBLIC 用户组 所有用户都可以直接引用 注意 用户要有能够访问 课程表 的权限 9 2 2 引用同义词 一旦创建同义词后 数据库用户可以直接通过同义词名称访问该同义词所指的数据库 对象 而不需要给出该对象的所属关系 例 9 5 以 SYS 用户查询 Student 数据库 课程表 中课程基本信息 代码如下 SELECT FROM ty kc 如果没有为 Student 数据库 课程表 创建同义词 ty kc 那么 SYS 用户查询 课程表 时 则需指定 课程表 的所有者 SYSTEM 课程表 9 2 3 删除同义词 删除公有同义词是使用 DROP PUBLIC SYNONYM 命令完成的 删除私有同义词是使 用 DROP SYNONYM 命令完成的 语法格式如下 DROP PUBLIC SYNONYM schema synonym name 其中 PUBLIC 表示删除一个公有同义词 SCHEMA 指定将要删除同义词的所属方案 synonym name 为将要删除的同义词名称 例 9 6 删除公有同义词 ty kc 代码如下 DROP PUBLIC SYNONYM ty kc 9 2 4 使用 OEM 管理同义词 7 创建同义词 例 9 7 为 Student 数据库中 课程表 创建私有同义词 kc 步骤如下 1 登录 OEM 选择 管理 属性页 单击 数据库对象 下的 同义词 超链接 打开 同义词 页如图 9 4 所示 单击 创建 按钮 进入 创建同义词 页面 如图 9 5 所示 该页面包括以下信息 一般信息 项包含以下信息 名称 将要创建的同义词名称 设置为 kc 类型 指定要创建公有同义词还是私有同义词 创建私有同义词 选择 方案 类型 创建公有同义词 选择 公用 类型 在此 选择 方案 类型 值为 SYSTEM 数据库 可以定义本地同义词 也可以为远程数据库定义同义词 若选择了 远程 那么需要指定 服务名 选择数据库链接 在此 选择本地数据库 别名 代表 同义词指向的对象 单击 手电筒 形状按钮 选择对象 SYSTEM 课程表 如图 9 6 所示 单击 选择 按钮 返回 创建同义词 页如图 9 7 单 击 确定 按钮 完成同义词的创建 第 7 页 共 18 页 图 9 4 同义词 页 图 9 5 创建同义词 页 第 8 页 共 18 页 图 9 6 引用方案和对象 页 图 9 7 返回创建同义词 页 8 删除同义词 在图 9 4 中 在搜索栏项 在 方案 文本框输入 SYSTEM 在 对象名 文本框 输入 kc 单击 开始 按钮 进行同义词查找 如图 9 8 所示 选中要删除的同义词 kc 单击 删除 按钮 出现 确认 页面 如图 9 9 所示 单击 是 按钮 删除该 同义词即可 第 9 页 共 18 页 图 9 8 搜索同义词 页 图 9 9 确认删除同义词 页 9 3 程序包 9 3 1 概述 程序包 Package 简称为包 是将相关的 PL SQL 程序块或元素 变量 常量 过程 函 数 游标等 组织在一起 作为一个完整的单元被存储在数据库中 以名称来标识 包由两个分离的部分组成 包头 规范 说明 和包体 主体 这两个部分独立地存储在 第 10 页 共 18 页 数据字典中 包头是包与应用程序之间的接口 在这里可以定义包的公有组件 如变量 常量 过程 函数 游标等 这些公有组件不仅可以在包内使用 还可以由其他过程和函 数调用 包体是包的具体实现 在其中可以定义私有组件 可以定义公有游标 实现在包 头中说明的过程 函数等 9 3 2 创建程序包 包的创建分为包头的创建和包体的创建两部分 9 创建包头 创建包头的语法格式为 CREATE OR REPLACE PACKAGE schema package name IS AS pl sql package spec END package name 其中 schema 指定将要创建的包所属用户方案 package name 将要创建的包的名称 pl sql package spec 变量 常量及数据类型定义 游标定义 函数 过程定义和 参数列表返回类型 在定义包头时 要遵循以下规则 包元素的位置可以任意安排 然而 在声明部分 对象必须在引用前进行声明 包头可以不对任何类型的元素进行说明 例如 包头可以只带过程和函数说明语 句 而不声明任何异常和类型 对过程和函数的任何声明都必须只对子程序和其参数进行描述 但不带任何代码 的说明 实现代码则只能在包体中 例 9 9 创建包头 my package1 其中包含一个函数 test func 和一个过程 test proc 代码如下 CREATE OR REPLACE PACKAGE my package1 AS FUNCTION test func RETURN VARCHAR2 PROCEDURE test proc END my package1 10 创建包体 创建包体的语法格式为 CREATE OR REPLACE PACKAGE BODY schema package name IS AS pl sql package body END package name 其中 pl sql package body 是游标 函数 过程的具体定义 创建包体时 注意以下问题 包体只能在包头被编译后才进行编译 第 11 页 共 18 页 包体中的过程 函数名称必须严格地与包头中定义的名称相匹配 包体中声明的变量 常量等都是私有的 只能在包体中使用 但包体中可以使用 包头中声明的变量 常量等 例 9 10 对应包头 my package1 创建包体 函数 test func 显示系统时间 过程 test proc 利用系统时间修改 成绩表 中学生的成绩 包体部分定义如下 CREATE OR REPLACE PACKAGE BODY my package1 IS FUNCTION test func RETURN VARCHAR2 AS i NUMBER BEGIN RETURN TO CHAR SYSDATE yyyy mm dd hh24 TO CHAR SYSDATE mi TO CHAR SYSDATE ss END PROCEDURE test proc IS i NUMBER BEGIN i TO NUMBER TO CHAR SYSDATE yyyymmddhh24miss i i mod 100 UPDATE 成绩表 SET 成绩 i END END my package1 9 3 3 程序包应用 11 调用包 在创建了包头和相应的包体之后 就可以调用该包的各个组件了 对包内公有组件的 调用格式要用到包名并加 作为限定词 即包名 组件名称 下面用 EXECUTE 命令来 调用包中的各个组件 例 9 11 调用包 my package1 中的函数 test func 显示系统当前时间 代码如 下 SELECT my package1 test func FROM DUAL 例 9 12 调用包 my package1 中的过程 test proc 修改 成绩表 中 成绩 列 代码如下 EXECUTE my package1 test proc 12 包重载 在包的内部 过程和函数可以重载 也就是说包中可以包含一个以上名称相同的对象 例 9 13 创建包 xs package 包内定义两个过程 分别实现按不同的输入参数添 加学生信息 第 12 页 共 18 页 包头部分定义如下 CREATE OR REPLACE PACKAGE xs package AS PROCEDURE addstudent xh IN 学生表 学号 TYPE PROCEDURE addstudent xh IN 学生表 学号 TYPE xm IN 学生表 姓名 TYPE END xs package 包体部分定义如下 CREATE OR REPLACE PACKAGE BODY xs package AS PROCEDURE addstudent xh IN 学生表 学号 TYPE IS BEGIN INSERT INTO 学生表 学号 VALUES xh COMMIT END ADDstudent PROCEDURE ADDstudent xh IN 学生表 学号 TYPE xm IN 学生表 姓名 TYPE IS BEGIN INSERT INTO 学生表 学号 姓名 VALUES xh xm COMMIT END addstudent END xs package 例 9 14 调用 xs package 包 通过输入不同参数添加学生信息 代码如下 DECLARE BEGIN xs package addstudent 033 END 或者 DECLARE BEGIN xs package addstudent 034 lie END 13 删除包 当不在需要某个程序包时 可以将其删除 也可以只删除包体 删除整个程序包的语法如下 DROP PACKAGE package name 第 13 页 共 18 页 只删除程序包体的语法如下 DROP PACKAGE BODY package name 例 9 15 删除 xs package 程序包 代码如下 DROP PACKAGE xs package 9 3 4 使用 OEM 管理程序包 14 创建程序包 使用 OEM 创建包也分为创建包头和包体两部分 创建步骤如下 1 登录 OEM 选择 管理 属性页 在 程序 下有 程序包 和 程序包体 两 个超链接 分别进行包头和包体的操作 2 创建包头 单击 程序包 超链接 进入 程序包 页面 如图 9 10 所示 单击 创建 按钮 打开 创建程序包 页面 如图 9 11 所示 在 名称 文本框中输入包头名 在 方案 文本框中输入或选择包所属用户方案 在 源 编辑区中输入代码实现变量 常量及数据 类型定义 游标定义 函数 过程定义和参数列表返回类型 单击 创建 按钮 完成创 建操作 3 创建程序包体 单击 程序包体 超链接 进入 程序包体 页面 如图 9 12 所示 单击 创建 按 钮 打开 创建程序包体 页面 如图 9 13 所示 在 名称 文本框输入程序包体的名称 在 方案 文本框中输入或选择包体所属用户方案 然后在 源 编辑区域输入定义函数 过程等的 PL SQL 源代码 单击 创建 按钮 完成创建操作 图 9 10 程序包 页 第 14 页 共 18 页 图 9 11 创建程序包头 页 图 9 12 程序包体 页 第 15 页 共 18 页 图 9 13 创建程序包体 页 15 删除程序包 在图 9 10 中 选择要删除的程序包 单击 删除 按钮 进入 确认 页面 如果确 认删除 单击 是 按钮 完成包的删除 9 3 5 系统的内置程序包 Oracle 系统提供了若干具有特殊功能的内置程序包 DAMS ALERT 包 用于数据库报警 允许会话间通信 DAMS JOB 包 用于任务调度服务 DAMS LOB 包 用于大型对象操作 DAMS PIPE 包 用于数据库管道 允许会话间通信 DAMS SQL 包 用于执行动态 SQL UTL FILE 包 用于文本文件的输入与输出 DBMS OUTPUT 包 允许用户从程序包 触发器和存储过程中输出信息 除了包 UTL FILE 既存储在服务器端 又存储在客户端 其他的 DBMS 包都存储在服 务器端 此外 在某些客户环境下 Oracle 还提供一些额外的包 9 4 本章小结 序列用于生成顺序编号 能够以串行方式生成一系列顺序整数 序列可以被设置为递 增 递减 有界或无界 循环或不循环等形式 序列经常被用来为主键列或者本来没有顺 序的记录行提供顺序的流水号 同义词是方案对象的别名 使用同义词 一方面可以简化对象的访问 另一方面可以 提高对象访问的安全性 程序包使程序设计实现模块化 提高程序的编写效率 在程序中首次调用包内的过程 第 16 页 共 18 页 或函数时 Oracle 会把整个包调入内存 当再次访问包内对象时 Oracle 将直接从内存中 读取 不需要从磁盘载入 从而提高了程序的执行效率 9 5 练习与思考 1 建立序列后 首次调用序列时应该使用哪个伪列 A ROWID B ROWNUM C NEXTVAL D CURRVAL 2 建立了公共同义词后 所有用户都可以使用该公共同义词吗 A 是 B 不是 3 以 SYSTEM 用户登录到 Student 数据库 创建序列并执行下面操作 1 建立序列 seq 2 序列初值为 10 序列增量为 1 序列最大值为 100 2 为学生表插入一条数据 学号使用序列 seq 2 生成数据 3 查看 学生表 显示所有学生信息 4 显示序列 seq 2 的当前值 5 显示当前用户所有序列的名称 序列增量和最大值 4 创建同义词并执行下面操作 1 以 SYSTEM 用户登录 Student 数据库 基于 学生表 建立公共同义词 public xs 2 以 SYSTEM 用户登录 Student 数据库 查询同义词 public xs 显示学生的姓名 和出生日期 5 使用程序包有哪些优点 9 6 上机实训 16 实训目标 1 掌握创建程序包头方法 2 掌握创建程序包体的方法 3 掌握程序包的调用方法 17 实训内容 1 创建