ORACLE技术上手教程
|
|
主要内容 |
|
|
Oracle安装 Oracle创建用户和角色 客户端链接Oracle服务器 |
Oracle简介
在第一学期我们已经接触过关系型数据库SQL Server，对数据库、表、记录、表的增删改查操作等这些基本的概念已经了解。Oracle是基于对象的关系型数据库，Oracle也是用表的形式对数据存储和管理，并且在Oracle的操作中添加了一些面向对象的思想。
Oracle数据库是Oracle（中文名称叫甲骨文）公司的核心产品，Oracle数据库是一个适合于大中型企业的数据库管理系统。在所有的数据库管理系统中（比如：微软的SQL Server，IBM的DB2等），Oracle的主要用户涉及面非常广，包括：银行、电信、移动通信、航空、保险、金融、电子商务和跨国公司等。Oracle产品是免费的，可以在Oracle官方网站上下载到安装包，另一方面Oracle服务是收费的。
Oracle公司成立以来，从最初的数据库版本到Oracle7、Oracle8i、Oracle9i，Oracle10g到Oracle11g，虽然每一个版本之间的操作都存在一定的差别，但是Oracle对数据的操作基本上都遵循SQL标准。因此对Oracle开发来说版本之间的差别不大。
很多人没有学习Oracle就开始发怵，因为人们在误解Oracle，认为Oracle太难学了，认为Oracle不是一般人用的数据库，其实任何数据库对应用程序研发人员来说，都是大同小异，因为目前多数数据库都支持标准的SQL。在Oracle这本书中，我们能学习到：
Oracle的安装
Oracle数据管理
常用子查询及常用函数
PL/SQL编程
Oracle基本管理
由于在第一学期已经接触了SQL Server，Oracle数据库的概念不是很难，主要是实践，因此在本书的学习中，认真的完成上机练习是学习好本书的关键。
接下来我们先从Oracle安装开始，接触一些Oracle中基本的概念。
Oracle安装
Oracle数据库产品是免费的，我们可以从Oracle的官方网站(http://www.oracle.com)下载到程序安装包，Oracle在Windows下的安装非常方便，安装开始后，一直点击安装程序的“下一步”即可。
1. 下载Oracle10g后，解压到一个文件夹下，单击“setup.exe”文件即可启动安装界面。如下图：
图1 Oracle安装启动界面
Oracle主目录位置就是Oracle准备安装的位置，称为“Oracle_Home”，一般Oracle根据当前计算机的硬盘大小默认给出一个合适的位置。Oracle安装时可以只安装Oracle软件，然后单独创建数据库，也可以在上图中选中“创建启动数据库”复选框，在安装Oracle产品时，同时创建一个数据库，对初学者来说，推荐这样安装。填写全局数据库名，以及管理员的密码。全局数据库名是数据库在服务器网络中的唯一标识。
2. 点击“下一步”，就会出现如下图内容，开始对Oracle服务器进行环境检查，主要查看服务器是否符合Oracle安装的条件，比如操作系统是否支持、系统内存是否符合Oracle安装的最低要求等。
图2 Oracle安装前环境检查
3. Oracle检查通过后，单击“下一步”，就会列出所有安装Oracle过程中的默认选项。
图3 Oracle默认安装设置
4. 单击“安装”按钮，进入安装界面，这一过程经历时间比较长，根据计算机的性能不同有很大差别。
图4 Oracle安装
5. 上图完成后，进入了各种Oracle工具的安装阶段，包括网络配置向导，iSQL*plus等（后面课程中讲解）。如下图所示：
图5 Oracle各种工具的安装
6. 接下来自动启动DBCA（Database Configuration Assistant）进入创建默认数据库阶段。
图6 DBCA下安装数据库
Oracle中的数据库主要是指存放数据的文件，这些文件在Oracle安装完成后，在计算机硬盘上都能找到，包括数据文件、控制文件和数据库日志文件。
数据库创建后会有一系列为该数据库提供服务的内存空间和后台进程，称为该数据库的实例。每一个数据库至少会有一个实例为其服务。实例中的内存结构称为系统全局区（SGA），系统会根据当前计算机系统的性能给SGA分配非常可观的内存空间。
Oracle创建数据库不能像SQL Server那样用一个简单的CREATE DATABASE命令就能完成，在创建数据库的过程中还需要配置各种参数。虽然有DBCA工具向导，但是仍然需要进行比较麻烦的配置。
|
|
虽然一个Oracle数据库服务器中可以安装多个数据库，但是一个数据库需要占用非常大的内存空间，因此一般一个服务器只安装一个数据库。每一个数据库可以有很多用户，不同的用户拥有自己的数据库对象（比如：数据库表），一个用户如果访问其他用户的数据库对象，必须由对方用户授予一定的权限。不同的用户创建的表，只能被当前用户访问。因此在Oracle开发中，不同的应用程序只需使用不同的用户访问即可。 |
7. 数据库创建完毕后，需要设置数据库的默认用户。Oracle中为管理员预置了两个用户分别是SYS和SYSTEM。同时Oracle为程序测试提供了一个普通用户scott，口令管理中，可以对数据库用户设置密码，设置是否锁定。 Oracle客户端使用用户名和密码登录Oracle系统后才能对数据库操作。
图7 DBCA下的口令管理
图8 为system,sys,scott用户设置密码
默认的用户中，SYS和SYSTEM用户是没有锁定的，安装成功后可以直接使用，SCOTT用户默认为锁定状态，因此不能直接使用，需要把SCOTT用户设定为非锁定状态才能正常使用。
这一步完成后，Oracle系统安装成功。
|
|
Oracle数据库中，默认情况下，所有系统的数据，SQL关键字等都是大写的，在操作过程中，Oracle会自动把这些内容转换为大写，因此用户操作时不需考虑大小写问题，一般情况下，为了良好的程序风格，程序中建议关键字用大写，非关键字可以使用小写。 |
Oracle客户端工具
Oracle服务器安装成功后，就可以通过客户端工具连接Oracle服务器了，可以到Oracle官方下载Oracle专用的客户端软件，大多客户端工具都是基于Oracle客户端软件的。接下来介绍几种常用的Oracle客户端工具。
SQL*Plus工具
该工具是Oracle系统默认安装下，自带的一个客户端工具。在Windows命令行中输入“sqlplusw”命令，就能够启动该工具了。
图9 SQL*Plus工具
输入用户名和密码后，如果SQL*Plus与数据库服务器在同一台计算机上，并且当前服务器下只有一个数据库实例，那么“主机字符串”可以不用填写。
|
|
SQL*Plus工具虽然是Oracle自带的工具，但是在现实开发中，基于该环境对开发不方便，因此很少使用。 |
SQL*Plus连接成功后就如图所示：
图10 SQL*Plus工具登录后
SQL*Plus命令行工具
该命令行工具，提供了与数据库交互的能力和维护数据库的能力，包括了Oracle自带的SQL*Plus工具的全部功能，在Oracle管理中经常使用。在命令行中输入：“sqlplus /nolog”即可启动该工具。如下图：
图11 启动SQL*Plus命令行工具
输入“sqlplus /nolog”命令后，只是启动了一个客户端进程，并没有与服务器连接，连接到Oracle服务器的命令是：
conn 用户名/密码 as 连接身份@服务器连接字符串
说明：
1. 连接身份：表示该用户连接后拥有的权限。
sysdba： 即数据库管理员，权限包括：打开数据库服务器、关闭数据库服务器、备份数据库、恢复数据库、日志归档、会话限制、管理功能、创建数据库。sys用户必须用sysdba身份才能登录，system用户可以用普通身份登录。
sysyoper：即数据库操作员，权限包括：打开数据库服务器、关闭数据库服务器、备份数据库、恢复数据库、日志归档、会话限制。
normal：即普通用户，权限只有查询某些数据表的数据。默认的身份是normal用户。
2. 客户端工具可以根据“服务器连接字符串”对服务器进行连接，有了连接字符串后客户端就可以像操作本机一样操作远程数据库，因此“服务器连接字符串”的配置也叫本地网络服务配置，如果SQL*Plus工具启动在服务器上，并且服务器上只有一个数据库实例的情况下，连接字符串可以缺省，在连接字符串中包括连接服务器的协议，服务器的地址，服务器的端口等设置，Oracle服务名等，该配置文件在Oracle安装目录下的： network/ADMIN/ tnsnames.ora。该文件是一个文本文件，用记事本打开后如下所示：
图12 服务器连接字符串配置
配置本地网络服务名
本地网络服务名，即客户端与服务器的连接字符串，本地网络服务名是客户端的配置，Oracle客户端安装后，可以使用客户端自带的网络配置向导（Net Configuration Assistant）进行配置：
1. 启动Net Configuration Assistant。选择“本地Net服务名配置”选项。如下图所示：
图13 启动Net Configuration Assistant
2. 选择“下一步”，本步骤可以对本地网络服务名进行添加，删除，测试是否正常连接等操作，选择“添加”选项。
图14 Net Configuration Assistant
3. 点击“下一步”，填写服务名，该服务名就是Oracle安装时（图1），为数据库取的全局数据库名。
图15 服务名配置
4. 点击“下一步”，选择服务需要的协议，默认是TCP协议。推荐使用默认的TCP协议。
图16 选择协议
5. 点击“下一步”，输入主机名，主机名可以是计算机名称，也可以是一个IP地址，主机如果是本机，可以使用本机计算机名称、“localhost”、“127.0.0.1”、或者本机的IP地址。
图17 输入主机名和端口
6. 单击“下一步”，选择“是，进行测试”选项。进入下图界面。
图18 测试成功
在测试时，默认采用的用户名和密码是system/manager进行测试，如果用户system的密码不是“manager”，有可能测试通不过，更改登录后，输入正确的用户名和密码后再进行测试即可。
7. 测试成功后，单击“下一步”，出现如下界面，这一步是为本地网络服务命名，即图12中的服务器连接字符串名。
图19 为网络服务名命名
点击“下一步”，配置就完成了，进入tnsnames.ora文件中查看，就出现了如图12中的内容。
PL/SQL Developer工具
在实际Oracle开发中，经常使用一个功能强大的第三方工具：“PL/SQL Developer”工具。PL/SQL Developer基本上可以实现Oracle开发中的任何操作。它运行在客户端时必须先安装Oracle客户端，并且通过网络配置向导配置网络服务名后才能正常与服务器连接。
图20 PL/SQL Developer
Oracle服务
Oracle在windows中安装完成后，会安装很多服务，下面介绍几个主要的服务。
图21 Oracle服务
OracleService+服务名，该服务是数据库启动的基础，只有该服务启动了，Oracle数据库才能正常启动。这是必须启动的服务。
OracleOraDb10g_home1TNSListener，该服务是服务器端为客户端提供的监听服务，只有该服务在服务器上正常启动，客户端才能连接到服务器。该监听服务接收客户端发出的请求，然后将请求传递给数据库服务器。一旦建立了连接，客户端和数据库服务器就能直接通信了。
OracleOraDb10g_home1iSQL*Plus，该服务提供了用浏览器对数据库中数据操作的方式。该服务启动后，就可以使用浏览器进行远程登录并进行数据库操作了。如下图所示：
图22 iSQL*Plus
OracleDBConsole+服务名，Oracle10g中的一个新服务。在Oracle9i之前，Oracle官方提供了一个基于图形界面的企业管理器（EM），从Oracle10g开始，Oracle提供了一个基于B/S的企业管理器，在操作系统的命令行中输入命令：emctl start dbconsole，就可以启动OracleDbConsole服务，如下图所示：
图23 EM服务的启动
服务启动之后，就可以在浏览器中输入上图中进入EM的地址，使用B/S方式管理Oracle服务器。
Oracle启动和关闭
OracleService启动动后，就可以对数据库进行管理了，Oracle的启动和关闭是最基本的命令，在SQL*Plus中，启动Oracle必须是sys用户，命令格式是：
startup open
图24 Oracle服务启动
Oracle服务关闭用命令：shutdown immediate
图25 Oracle服务关闭
Oracle用户和权限
Oracle中，一般不会轻易在一个服务器上创建多个数据库，在一个数据库中，不同的项目由不同的用户访问，每一个用户拥有自身创建的数据库对象，因此用户的概念在Oracle中非常重要。Oracle的用户可以用CREATE USER命令来创建。其语法是：
语法结构：创建用户
|
CREATE USER 用户名 IDENTIFIED BY 口令 [ACCOUNT LOCK|UNLOCK] |
语法解析：
LOCK|UNLOCK创建用户时是否锁定，默认为锁定状态。锁定的用户无法正常的登录进行数据库操作。
代码演示：创建用户
|
SQL> CREATE USER jerry 2 IDENTIFIED BY tom 3 ACCOUNT UNLOCK; |
|
|
Oracle在SQL*Plus中的命令以分号（；）结尾，代表命令完毕并执行，系统同时会把该命令保存在缓存中，缓存中只保存最近执行过的命令，如果重新执行缓存中的命令，直接使用左斜杠符号（/）。如果命令不以分号结尾，该命令只是写入缓存保存起来，但并不执行。 |
尽管用户成功创建，但是还不能正常的登录Oracle数据库系统，因为该用户还没有任何权限。如果用户能够正常登录，至少需要CREATE SESSION系统权限。
Oracle用户对数据库管理或对象操作的权利，分为系统权限和数据库对象权限。系统权限比如：CREATE SESSION，CREATE TABLE等，拥有系统权限的用户，允许拥有相应的系统操作。数据库对象权限，比如对表中的数据进行增删改操作等，拥有数据库对象权限的用户可以对所拥有的对象进行对应的操作。
还有一个概念就是数据库角色（role），数据库角色就是若干个系统权限的集合。下面介绍几个常用角色：
CONNECT角色，主要应用在临时用户，特别是那些不需要建表的用户，通常只赋予他们CONNECT role。CONNECT是使用Oracle的简单权限，拥有CONNECT角色的用户，可以与服务器建立连接会话（session，客户端对服务器连接，称为会话）。
RESOURCE角色，更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程（procedure）、触发器（trigger）、索引（index）等。
DBA角色，DBA role拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。用户SYSTEM拥有DBA角色。
一般情况下，一个普通的用户（如SCOTT），拥有CONNECT和RESOURCE两个角色即可进行常规的数据库开发工作。
可以把某个权限授予某个角色，可以把权限、角色授予某个用户。系统权限只能由DBA用户授权，对象权限由拥有该对象的用户授权，授权语法是：
语法结构：授权
|
GRANT 角色|权限 TO 用户（角色） |
代码演示：授权
|
SQL> GRANT CONNECT TO jerry; 授权成功。 SQL> GRANT RESOURCE TO jerry; 授权成功。 SQL> |
语法结构：其他操作
|
//回收权限 REVOKE 角色|权限 FROM 用户（角色） //修改用户的密码 ALTER USER 用户名 IDENTIFIED BY 新密码 //修改用户处于锁定（非锁定）状态 ALTER USER 用户名 ACCOUNT LOCK|UNLOCK |
本章总结
Oracle是基于对象的关系型数据库，Oracle产品免费，服务收费。
Oracle安装后默认会有两个管理员用户（system，sys）和一个普通用户Scott。
Sql*plus是Oracle管理和数据操作的客户端工具。
客户端链接服务器前，服务器要启动监听服务，并且客户端工具要安装Oracle客户端，并且在客户端要建立本地网络服务名。
Oracle服务和监听启动后才能对数据库进行操作。
用startup命令启动数据库，用shutdown命令关闭数据库。
Oracle的角色包括了一系列系统权限和普通对象权限，可以把权限授权给角色，把权限或者角色授权给用户。
本章练习
1. 描述Oracle安装过程中的关键点。
2. 描述创建本地网络服务名的步骤。
3. 描述Oracle主要服务的作用。
4. Oracle使用什么命令才能启动和关闭。
5. 什么是Oracle权限和角色？他们的关系是什么？
6. 创建一个用户，并授权CONNECT和RESOURCE。
章节知识结构图
第2章
SQL数据操作和查询
|
|
主要内容 |
|
|
Oracle数据类型 SQL建表和约束 SQL对数据增删改 SQL查询 Oracle伪列 |
1. SQL简介
在第一学期的SQL Server学习中，已经知道，SQL是结构化查询语言（Structured Query Language），专门用于数据存取、数据更新及数据库管理等操作。并且已经学习了用SQL语句对数据库的表进行增删改查的操作。
在Oracle开发中，客户端把SQL语句发送给服务器，服务器对SQL语句进行编译、执行，把执行的结果返回给客户端。Oracle SQL语句由如下命令组成：sql92/sql99
数据定义语言（DDL），包括CREATE（创建）命令、ALTER（修改）命令、DROP（删除）命令等。
数据操纵语言（DML），包括INSERT（插入）命令、UPDATE（更新）命令、DELETE（删除）命令、SELECT等。
数据查询语言（DQL），包括基本查询语句、Order By子句、Group By子句等。
事务控制语言（TCL），包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。
数据控制语言（DCL），GRANT（授权）命令、REVOKE（撤销）命令。
目前主流的数据库产品（比如：SQL Server、Oracle）都支持标准的SQL语句。数据定义语言，表的增删改操作，数据的简单查询，事务的提交和回滚，权限的授权和撤销等，Oracle与SQL Server在操作上基本一致。
Oracle数据类型
Oracle数据库的核心是表，表中的列使用到的常见数据类型如下：
|
类型 |
含义 |
|
CHAR(length) |
存储固定长度的字符串。参数length指定了长度，如果存储的字符串长度小于length，用空格填充。默认长度是1，最长不超过2000字节。 |
|
VARCHAR2(length) |
存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1，最长不超过4000字符。 |
|
NUMBER(p，s) |
既可以存储浮点数，也可以存储整数，p表示数字的最大位数（如果是小数包括整数部分和小数部分和小数点，p默认是38为），s是指小数位数。 |
|
DATE |
存储日期和时间，存储纪元、4位年、月、日、时、分、秒，存储时间从公元前4712年1月1日到公元后4712年12月31日。 |
|
TIMESTAMP |
不但存储日期的年月日，时分秒，以及秒后6位，同时包含时区。 |
|
CLOB |
存储大的文本，比如存储非结构化的XML文档 |
|
BLOB |
存储二进制对象，如图形、视频、声音等。 |
表1 Oracle的部分数据类型
对应NUMBER类型的示例：
|
格式 |
输入的数字 |
实际的存储 |
|
NUMBER |
1234.567 |
1234.567 |
|
NUMBER（6，2） |
123.4567 |
123.46 |
|
NUMBER（4，2） |
12345.67 |
输入的数字超过了所指定的精度，数据库不能存储 |
表2 Number示例
对于日期类型，可以使用sysdate内置函数可以获取当前的系统日期和时间，返回DATE类型，用systimestamp函数可以返回当前日期、时间和时区。
图1 sysdate和sysTimestamp
Oracle的查询中，必须使用“select 列… from 表”的完整语法，当查询单行函数的时候，from后面使用DUAL表，dual表在系统中只有一行一列，该表在输出单行函数时为了select…from的语法完整性而使用。
创建表和约束
Oracle创建表同SQL Server一样，使用CREATE TABLE命令来完成。创建约束则使用如下命令：
语法格式：ALTER TABLE命令
|
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容。 |
不论创建表还是约束，与SQL Server基本相同，注意：在Oracle中default是一个值，而SQL Server中default是一个约束，因此Oracle的default设置可以在建表的时候创建。
案例1：创建一个学生信息（INFOS）表和约束
代码演示：Oracle创建表和约束
|
CREATE TABLE INFOS ( STUID VARCHAR2(7) NOT NULL, --学号 学号=‘S’+班号+2位序号 STUNAME VARCHAR2(10) NOT NULL, --姓名 GENDER VARCHAR2(2) NOT NULL, --性别 AGE NUMBER(2) NOT NULL, --年龄 SEAT NUMBER(2) NOT NULL, --座号 ENROLLDATE DATE, --入学时间 STUADDRESS VARCHAR2(50) DEFAULT '地址不详', --住址 CLASSNO VARCHAR2(4) NOT NULL --班号 班号=学期序号+班级序号 ) / ① ALTER TABLE INFOS ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID) ② / ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDER CHECK(GENDER = '男' OR GENDER = '女') ③ / ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_SEAT CHECK(SEAT >=0 AND SEAT <=50) ④ / ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_AGE CHECK(AGE >=0 AND AGE<=100) ⑤ / ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_CLASSNO CHECK((CLASSNO >='1001' AND CLASSNO<='1999') OR (CLASSNO >='2001' AND CLASSNO<='2999')) ⑥ / ALTER TABLE INFOS ADD CONSTRAINTS UN_STUNAME UNIQUE(STUNAME) ⑦ / |
代码解析：
1 在Oracle代码中，“/”执行缓存区中的语句，由于缓冲区中只存储一条刚刚保存过语句，由于每条语句没有用分号结尾，只是保存在缓冲区，因此每条语句后面都有单独一行“/”。
2 创建一个主键约束。
3 与 ④ ⑤ ⑥ ⑦一起创建各种check约束。其中⑦是唯一约束，表示该列值是唯一的，列中的值不能重复。
Oracle中创建外键约束与SQL Server相同。比如：现有成绩表定义如下：
案例2：创建一个成绩表（SCORES）表和约束
代码演示：Oracle创建表和约束
|
CREATE TABLE SCORES ( ID NUMBER , --ID ① TERM VARCHAR2(2), --学期 S1或S2 STUID VARCHAR2(7) NOT NULL, --学号 EXAMNO VARCHAR2(7) NOT NULL, --考号 E+班号+序号 WRITTENSCORE NUMBER(4,1) NOT NULL, --笔试成绩 LABSCORE NUMBER(4,1) NOT NULL --机试成绩 ) ALTER TABLE SCORES ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2') / ALTER TABLE SCORES ADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID) ② / |
代码解析：
1 SQL Server中可以使用identify创建自动增长列，但是Oracle中的自动增长需要借助序列（Sequence）完成，在后面章节中讲解。
2 Oracle中的外键约束定义。
数据操纵语言（DML）
数据操纵语言（DML）用于对数据库的表中数据进行添加、修改、删除和SELECT…For UPDATE(后面专门学习该查询)操作。对比一期学习过的SQL Server操作，接下来一一介绍在Oracle中的操作。
简单查询
数据查询是用SELECT命令从数据库的表中提取信息。SELECT语句的语法是：
语法结构：简单查询
|
SELECT *|列名|表达式 FROM 表名 WHERE 条件 ORDER BY 列名 |
语法解析：
1. *表示表中的所有列。
2. 列名可以选择若干个表中的列名，各个列表中间用逗号分隔。
3. 表达式可以是列名、函数、常数等组成的表达式。
4. WHERE子句是查询的条件。
5. ORDER BY 要求在查询的结果中排序，默认是升序。
图2 数据查询
Oracle中可以把查询的结果根据结果集中的表结构和数据形成一张新表。
语法结构：根据结果集创建表
|
CREATE TABLE 表名 AS SELECT语句 |
代码演示：根据结果集创建表
|
SQL> CREATE TABLE INFOS1 AS SELECT * FROM INFOS; TABLE CREATED |
使用上面命令创建的新表中，不存在任何约束，并且把查询的数据一起插入到新表中。如果只复制表结构，只需使查询的条件不成立（比如where 1=2），就不会查询从出任何数据，从而复制一个表结构。
代码演示：复制表结构
|
SQL> CREATE TABLE INFOS2 AS SELECT * FROM INFOS WHERE 1=2; TABLE CREATED |
数据插入
用INSERT命令完成对数据的插入。
语法结构：根据结果集创建表
|
INSERT INTO 表名(列名1，列名2……) VALUES (值1，值2……) |
语法解析：
1. 列名可以省略。当省略列名时，默认是表中的所有列名，列名顺序为表定义中列的先后顺序。
2. 值的数量和顺序要与列名的数量和顺序一致。值的类型与列名的类型一致。
代码演示：向INFOS表和SCORES表中插入数据
|
SQL> INSERT INTO INFOS VALUES ( ① 2 's100102', '林冲', '男', 22, 2, 3 TO_DATE('2009-8-9 06:30:10',' YYYY-MM-DD HH24:MI:SS '), ② 4 '西安', '1001' 5 ) 6 / 1 row inserted SQL> INSERT INTO INFOS VALUES ( 's100104','阮小二','男',26,3,SYSDATE,default,'1001'); ③ 1 row inserted SQL>COMMIT; ④ |
代码解析：
1 表名后面缺省了列名，默认是表Infos中的所有列名，values中的值要与表中列一一对应，包括顺序和数据类型的对应。在SQL*Plus中一条语句可以写在多行，那么从第二行开始，sqlplus会为每一行前面给出行号。
2 在Oracle中，日期是国际化的，不同的区域安装的数据库，默认的日期格式不同，因此为了程序便于移植，日期的输入要使用TO_DATE函数对日期格式化后输入，采用格式化字符串对日期进行格式化时，格式化字符串中字符不区分大小写，常见的格式化字符如下：
1. yyyy表示四位年份
2. mm表示两位月份，比如3月表示为03 month 3月
3. dd表示两位日期
4. hh24表示小时从0-23，hh12也表示小时从0-11。
5. mi 表示分钟
6. ss表示秒
3 在遇到存在默认值的列时，可以使用default值代替。
4 commit是把用户操作（添加、删除、修改操作）提交，只有提交操作后，数据才能真正更新到表中，否则其他用户无法查询到当前用户操作的结果。
在Oracle中，一个INSERT命令可以把一个结果集一次性插入到一张表中。使用的语句是：INSERT INTO 表 SELECT子句，如下示例：
代码演示：INSERT向表中插入一个结果集
|
SQL> INSERT INTO INFOS2 SELECT * FROM INFOS; 5 rows inserted |
在这种语法下，要求结果集中每一列的数据类型必须与表中的每一列的数据类型一致，结果集中的列的数量与表中的列的数量一致。比如表INFOS2，该表的结构与INFO表一样，那么可以把INFO表中的所有记录一次性插入到INFOS2表中。
Oracle的简单查询和SQL Server一样都可以在查询列中使用常量，如图：
图3 Select中的常量
可以使用刚才的做法，把该结果集中的数据插入到表INFOS中。
代码演示：INSERT向表中插入一个常量结果集
|
SQL> INSERT INTO INFOS SELECT 's100106','卢俊义','男',23,5, TO_DATE('2009-8-9 08:00:10','YYYY-MM-DD HH24:MI:SS'), '青龙寺','1001' FROM DUAL; 1 rows inserted SQL>COMMIT; |
更新数据
Oracle在表中更新数据的语法是：
语法结构：UPDATE操作
|
UPDATE 表名 SET 列名1=值，列名2=值…… WHERE 条件 |
代码演示：UPDATE操作
|
SQL> UPDATE INFOS SET CLASSNO='1002',STUADDRESS='山东莱芜' WHERE STUNAME='阮小二'; 1 rows updated SQL> commit; |
删除数据
Oracle在表中删除数据的语法是：
语法结构：DELETE操作
|
DELETE FROM表名 WHERE 条件 |
代码演示：DELETE操作
|
SQL> DELETE FROM INFOS WHERE STUID='s100103'; 1 ROW DELETED SQL> COMMIT; |
TRUNCATE
在数据库操作中， TRUNCATE命令（是一个DDL命令）可以把表中的所有数据一次性全部删除，语法是：
语法结构：TRUNCATE
|
TRUNCATE TABLE 表名 |
TRUNCATE和DELETE都能把表中的数据全部删除，他们的区别是：
1. TRUNCATE是DDL命令，删除的数据不能恢复；DELETE命令是DML命令，删除后的数据可以通过日志文件恢复。
2. 如果一个表中数据记录很多，TRUNCATE相对DELETE速度快。
由于TRUNCATE命令比较危险，因此在实际开发中，TRUNCATE命令慎用。
|
|
Oracle默认安装中，已经创建了一个SCOTT用户，默认密码是：tiger，该用户下有四张表分别是：雇员表（EMP），部门表（DEPT），工资登记表和奖金表，请参考本章后面的附表。接下来很多操作都是在该用户下完成的。 |
操作符
Oracle开发中，依然存在算术运算，关系运算，和逻辑运算。
算术运算
Oracle中的算术运算符，没有C#中的算术运算符丰富，只有+、-、*、/四个，其中除号(/)的结果是浮点数。求余运算只能借助函数：MOD(x,y)：返回x除以y的余数。
案例3：每名员工年终奖是2000元，请显示基本工资在2000元以上的员工的月工资，年总工资。
该案例的表请参见本章练习的附表1、附表2、附表3，这三张表是ORACLE 10g自带的。
代码演示：查询中的算术运算
|
SQL> SELECT ENAME,SAL,(SAL*12+2000) FROM EMP WHERE SAL>2000;
6 rows selected |
关系运算和逻辑运算
Oracle中Where子句经中经常见到关系运算和逻辑运算，常见的关系运算有：
|
运算符 |
说明 |
运算符 |
说明 |
|
= |
等于 |
> |
大于 |
|
<>或者!= |
不等于 |
<= |
小于或者等于 |
|
< |
小于 |
>= |
大于或者等于 |
表3 Oracle的关系运算符
逻辑运算符有三个：AND、OR、NOT
关系运算和逻辑运算与前面SQL Server学习过的一致。
字符串连接操作符（||）
在Oracle中，字符串的连接用双竖线（||）表示。比如，在EMP表中，查询工资在2000元以上的姓名以及工作。
代码演示：字符串连接
|
SQL> SELECT (ENAME || 'is a ' || JOB) AS "Employee Details" ① 2 FROM EMP 3 WHERE SAL>2000; Employee Details ------------------------ JONESis a MANAGER BLAKEis a MANAGER CLARKis a MANAGER SCOTTis a ANALYST KINGis a PRESIDENT FORDis a ANALYST 6 rows selected |
代码解析：
1 Oracle中字符串可以用单引号，也可以用双引号，在别名中存在空格时，必须用双引号。在表名、列名时用双引号。
高级查询
在第一期学习过SQL的简单查询和连接查询。现在学习一些新的SQL操作符。
消除重复行
在Oracle查询中结果中，可能出现若干行相同的情况，那么可以使用DISTINCT消除重复行。具体的用法如示例：
代码演示：DISTINCT消除重复行
|
SQL> SELECT DISTINCT DEPTNO FROM EMP; DEPTNO ------ 30 20 10 |
NULL操作
如果某条记录中有缺少的数据值，就是空值（NULL值）。空值不等于0或者空格，空值是指未赋值、未知或不可用的值。任何数据类型的列都可以包括NULL值，除非该列被定义为非空或者主键。
代码演示：EMP中的NULL值
|
SQL> SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE SAL<2000;
7 rows selected |
在查询条件中NULL值用IS NULL作条件，非NULL值用NOT IS NULL做条件。
案例4：查询EMP表中没有发奖金的员工。
代码演示：NULL值查询
|
SQL> SELECT ENAME,JOB,SAL,COMM FROM EMP 2 WHERE SAL<2000 AND COMM IS NULL;
|
IN 操作
在Where子句中可以使用IN操作符来查询其列值在指定的列表中的行。比如：查询出工作职责是SALESMAN、PRESIDENT或者ANALYST的员工。条件有两种表示方法：
1. WHERE job = 'SALESMAN ' OR job = 'PRESIDENT ' OR job = 'ANALYST '
2. WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST')
代码演示：IN操作
|
SQL> SELECT ENAME,JOB,SAL FROM EMP 2 WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST');
7 rows selected |
对应IN操作的还有NOT IN，用法一样，结果相反。
BETWEEN…AND…
在WHERE子句中，可以使用BETWEEN操作符来查询列值包含在指定区间内的行。比如，查询工资从1000到2000之间的员工。可以使用传统方法：
WHERE SAL>=1000 AND SAL<=2000
也可以使用：
WHERE SAL BETWEEN 1000 AND 2000
BWTWEEN操作所指定的范围也包括边界。
代码演示：BETWEEN操作
|
SQL> SELECT ename,job,sal FROM EMP WHERE sal BETWEEN 1000 AND 2000;
6 rows selected |
LIKE模糊查询
在一些查询时，可能把握不准需要查询的确切值，比如百度搜索时输入关键字即可查询出相关的结果，这种查询称为模糊查询。模糊查询使用LIKE关键字通过字符匹配检索出所需要的数据行。字符匹配操作可以使用通配符“%”和“_”:
%：表示零个或者多个任意字符。
_：代表一个任意字符。
语法是：LIKE '字符串'[ESCAPE '字符']。匹配的字符串中，ESCAPE后面的“字符”作为转义字符。与一期SQLServer中ESCAPE用法相同。
|
通配符表达式 |
说明 |
|
'S%' |
以S开头的字符串。 |
|
'_S%' |
第二个字符时S的字符串。 |
|
'%30\%%' escape '\' |
包含“30%”的字符串，“\”指转义字符，“\%”在字符串中表示一个字符“%”。 |
表4 通配符示例
案例5：显示员工名称以J开头以S结尾的员工的姓名、工资和工资。
代码演示：LIKE操作
|
SQL> SELECT ENAME,JOB,SAL FROM EMP WHERE ENAME LIKE 'J%S'; ENAME JOB SAL ---------- --------- --------- JONES MANAGER 2975.00 JAMES CLERK 950.00 |
集合运算
集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：
INTERSECT(交集)，返回两个查询共有的记录。
UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
UNION(并集)，返回各个查询的所有记录，不包括重复记录。
MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。
当使用集合操作的时候，要注意：查询所返回的列数以及列的类型必须匹配，列名可以不同。
案例6：查询出dept表中哪个部门下没有员工。只需求出dept表中的部门号和emp表中的部门号的补集即可。
代码演示：求补运算
|
SQL> SELECT DEPTNO FROM DEPT 2 MINUS 3 SELECT DEPTNO FROM EMP; DEPTNO ------ 40 |
前面学习过可以通过insert into …select把一个结果集插入到另一张结构相同的表中，因此可以使用union把若干条记录一次性插入到一张表中。
代码演示：用union插入多条数据
|
SQL> INSERT INTO DEPT 2 SELECT 50,'公关部','台湾' FROM DUAL 3 UNION 4 SELECT 60,'研发部','西安' FROM DUAL 5 UNION 6 SELECT 70,'培训部','西安' FROM DUAL 7 / 3 rows inserted |
连接查询
在SQL Server中已经学习过内联接(inner join)、外联接(outer join)，外联接又分为左外联接(left outer join)和右外联接(right outer join)。Oracle中对两个表或者若干表之间的外联接用（+）表示。
案例7：请查询出工资大于2000元的，员工姓名，部门，工作，工资。
由于部门名称在dept中，其他的信息在emp表中，需要内联接才能完成。
代码演示：内联接
|
SQL> SELECT e.ENAME,e.JOB,e.SAL,d.DNAME 2 FROM emp e,dept d 3 WHERE e.deptno=d.deptno 4 AND e.SAL>2000;
6 rows selected |
也可以使用SQL/92标准中的内联接：
代码演示：内联接
|
SELECT e.ENAME,e.JOB,e.SAL,d.DNAME FROM EMP e INNER JOIN DEPT d ON e.DEPTNO=d.DEPTNO WHERE e.SAL>2000 |
这里INNER JOIN中，关键字INNER可以省略。
案例8：请查询出每个部门下的员工姓名，工资。
案例分析：
Emp表用外键deptno引用Dept表中的deptno，在Dept表中如果有某些部门没有员工，那么用内联接，没有员工的部门将无法显示，因此必须以Dept表为基准的外联接。
代码演示：外联接
|
SQL> SELECT e.ENAME,e.JOB,e.SAL,d.DNAME 2 FROM EMP e ,DEPT d 3 WHERE e.DEPTNO(+)=d.DEPTNO ① 4 /
18 rows selected |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
代码解析：
1 (+)：Oracle专用的联接符，在条件中出现在左边指右外联接，出现在右边指左外联接。
也可以使用SQL/92标准的写法：
代码演示：外联接
|
SELECT e.ENAME,e.JOB,e.SAL,d.DNAME FROM EMP e RIGHT OUTER JOIN DEPT d ON e.DEPTNO=d.DEPTNO |
这里RIGHT OUTER JOIN中，关键字OUTER可以省略。
|
|
虽然Oracle自身的联接查询语法比较好写，同时容易理解，但是为了程序便于移植，推荐使用SQL/92表中的联接查询。同时也可以与SQL Server获得一致。 |
本章总结
Oracle SQL语句中有数据操纵语言（DML）、数据定义语言（DDL）、数据控制语言（DCL）、事务控制语言（TCL）等等。
DML语句包括增删改查语句，DDL语句包括数据库对象创建、修改和删除语句，数据控制命令包括GRANT、REVOKE等，事务控制命令有COMMIT、ROLLBACK等。
数据库中建表常用的类型有：数字类型number（p，s），可变字符串varchar2（length），日期date。
Oracle中default是一个值，在Oracle中不存在default约束。
Oracle的增删改语句与SQL Server基本一致，都是使用INSERT、UPDATE、DELETE完成。
Oracle高级查询中要注意：DISTINCT、NULL、IN、BETWEEN…AND…。
集合操作有：UNION、UNION ALL、INTESECT、MINUS。
联接查询有内联接和外联接。
本章练习
1. 创建一查询，显示与Blake在同一部门工作的雇员的项目和受雇日期，但是Blake不包含在内。
2. 显示位置在Dallas的部门内的雇员姓名、变化以及工作。
3. 显示被King直接管理的雇员的姓名以及工资。
4. 创建一查询，显示能获得与Scott一样工资和奖金的其他雇员的姓名、受雇日期以及工资。
附表1：Scott表中的EMP表：员工表
|
序号 |
列名 |
类型 |
说明 |
|
1 |
EMPNO |
NUMBER(4) |
员工编号，EMP表主键 |
|
2 |
ENAME |
VARCHAR2(10) |
员工姓名 |
|
3 |
JOB |
VARCHAR2(9) |
员工工作 |
|
4 |
MGR |
NUMBER(4) |
员工的领导编号，引用EMPNO |
|
5 |
HIREDATE |
DATE |
入职日期 |
|
6 |
SAL |
NUMBER(7,2) |
员工工资 |
|
7 |
COMM |
NUMBER(7,2) |
员工奖金 |
|
8 |
DEPTNO |
NUMBER(2) |
员工部门编号，是表DEPT的外键。 |
附表2：Scott表中的DEPT表：部门表
|
序号 |
列名 |
类型 |
说明 |
|
1 |
DEPTNO |
NUMBER(2) |
部门编号，主键 |
|
2 |
DNAME |
VARCHAR2(14) |
部门名称 |
|
3 |
LOC |
VARCHAR2(13) |
部门位置 |
附表3：Scott表中的SALGRADE表：工资等级表
|
序号 |
列名 |
类型 |
说明 |
|
1 |
GRADE |
NUMBER |
等级 |
|
2 |
LOSAL |
NUMBER |
此等级下最低工资 |
|
3 |
HISAL |
NUMBER |
此等级下最高工资 |
章节知识结构图
第3章
子查询和常用函数
|
|
主要内容 |
|
|
子查询 伪列 锁的概念 |
1. 子查询
子查询在SELECT、UPDATE、DELETE语句内部可以出现SELECT语句。内部的SELECT语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表。子查询的类型有：
1. 单行子查询：不向外部返回结果，或者只返回一行结果。
2. 多行子查询：向外部返回零行、一行或者多行结果。
案例1：查询出销售部（SALES）下面的员工姓名，工作，工资。
案例分析
该问题可以用联接查询实现，由于所需的结果信息都在Emp表中，可以先从Dept表中查询出销售部对应的部门号，然后根据当前部门号再到Emp表中查询出符合该部门的员工记录即可。从销售表中查询出的结果可以作为Emp表中查询的条件，SQL语句实现如下：
代码演示：单行子查询
|
SQL> SELECT ENAME,JOB,SAL FROM EMP 2 WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES') ① 3 /
6 rows selected |
代码解析：
1 内部查询的结果作为外部查询的条件。
需要注意：
如果内部查询不返回任何记录，则外部条件中字段DEPTNO与NULL比较永远为假，也就是说外部查询不返还任何结果。
在单行子查询中外部查询可以使用=、>、<、>=、<=、<>等比较运算符。
内部查询返回的结果必须与外部查询条件中的字段（DEPTNO）匹配。
如果内部查询返回多行结果则出现错误。
案例2：查询出Emp表中比任意一个销售员(“SALESMAN”)工资低的员工姓名、工作、工资。
案例分析
销售员在Emp表中有很多条记录，每个人工资不相等，如果返回“比任意员工的工资还低”的条件，返回比“最高工资还低”即可。如果用子查询做，子查询中就会返回多条记录。用普通的关系符（>、<等）运行就会出错。这时候需要用关键字ANY。ANY放在比较运算符后面，表示“任意”的意思。
代码演示：ANY子查询
|
SQL> SELECT ENAME,JOB,SAL FROM EMP 2 WHERE SAL<ANY (SELECT SAL FROM EMP WHERE JOB='SALESMAN') ① 3 /
7 rows selected |
代码解析：
1 <any:比子查询结果中任意的值都小，也就是说，比子查询结果中最大值还小，那么同理>any表示比子查询结果中最小的还大。
案例3：查询出比所有销售员的工资都高的员工姓名，工作，工资。
案例分析
ANY可以表示任意的，但本案例中要求比所有销售员工资都高，那么就要使用另外一个关键字ALL。ALL与关系操作符一起使用，表示与子查询中所有元素比较。
代码演示：ALL子查询
|
SQL> SELECT ENAME,JOB,SAL FROM EMP 2 WHERE SAL>ALL (SELECT SAL FROM EMP WHERE JOB='SALESMAN') ①
6 rows selected |
||||||||||||||||||||||||||||
代码解析：
1 >ALL：比子查询结果中所有值还要大，也就是说，比子查询结果中最大值还要大。<ALL表示比最小值还要小。
对于子查询还可以使用IN和NOT IN操作符进行操作。
Oracle中的伪列
在Oracle的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。接下来学习两个伪列：ROWID和ROWNUM。
ROWID
表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回的就是该行的物理地址。使用ROWID可以快速的定位表中的某一行。ROWID值可以唯一的标识表中的一行。由于ROWID返回的是该行的物理地址，因此使用ROWID可以显示行是如何存储的。
代码演示：ROWID
|
SQL> SELECT ROWID,ENAME FROM EMP WHERE SAL>2000;
6 rows selected |
ROWNUM
在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。通过ROWNUM伪列可以限制查询结果集中返回的行数。
|
|
ROWNUM与ROWID不同，ROWID是插入记录时生成，ROWNUM是查询数据时生成。ROWID标识的是行的物理地址。ROWNUM标识的是查询结果中的行的次序。 |
案例4：查询出员工表中前5名员工的姓名，工作，工资。
代码演示：ROWNUM
|
SQL> SELECT ROWNUM,ENAME,JOB,SAL FROM EMP WHERE ROWNUM<=5;
|
案例5：查询出工资最高的前5名员工的姓名、工资和工资。
案例分析
“工资最高的前5名”需要先降序排序，再取前5名，但是生成ROWNUM操作比排序要早，排序时已经连同ROWNUM一起排序了，因此不能直接在案例1的语句中直接加上Order by就行，而是需要对排序的结果重新做二次查询，产生新的ROWNUM才能作为查询的条件依据。
代码演示：ROWNUM应用
|
SQL> SELECT ROWNUM,T.* FROM ① 2 (SELECT ENAME,JOB,SAL 3 FROM EMP ORDER BY SAL DESC) T ② 4 WHERE ROWNUM<=5 5 /
|
代码解析：
1 T是子查询②的别名，这里的ROWNUM是第二次查询后的ROWNUM。
案例6：查询出表EMP中第5条到第10条之间的记录。
案例分析
这是分页的应用。在查询条件中，如果查询条件中ROWNUM大于某一正整数，则不返还任何结果。
代码演示：ROWNUM分页
|
SQL> SELECT * FROM 2 (SELECT ROWNUM R,ENAME,JOB,SAL ① 3 FROM EMP WHERE ROWNUM<=10) ② 4 WHERE R>5 ③ 5 /
|
代码解析：
1 内部查询中得到ROWNUM 并且用别名R记录，供外层条件③使用。
2 内部查询的ROWNUM，与外出的ROWNUM列是平等的两列。
3 使用的R是内层产生的ROWNUM，在外层看来，内层查询的ROWNUM是正常的一列。
Oracle函数
Oracle SQL提供了用于执行特定操作的专用函数。这些函数大大增强了SQL语言的功能。函数可以接受零个或者多个输入参数，并返回一个输出结果。Oracle数据库中主要使用两种类型的函数：
1. 单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，比如：MOD(x,y)返回x除以y的余数（x和y可以是两个整数，也可以是表中的整数列）。常用的单行函数有：
字符函数：对字符串操作。
数字函数：对数字进行计算，返回一个数字。
转换函数：可以将一种数据类型转换为另外一种数据类型。
日期函数：对日期和时间进行处理。
2. 聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如SUM(x)返回结果集中x列的总合。
字符函数
字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。下表列出了常用的字符函数。
|
函数 |
说明 |
|
ASCII(x) |
返回字符x的ASCII码。 |
|
CONCAT(x,y) |
连接字符串x和y。 |
|
INSTR(x, str [,start] [,n) |
在x中查找str，可以指定从start开始，也可以指定从第n次开始。 |
|
LENGTH(x) |
返回x的长度。 |
|
LOWER(x) |
x转换为小写。 |
|
UPPER(x) |
x转换为大写。 |
|
LTRIM(x[,trim_str]) |
把x的左边截去trim_str字符串，缺省截去空格。 |
|
RTRIM(x[,trim_str]) |
把x的右边截去trim_str字符串，缺省截去空格。 |
|
TRIM([trim_str FROM] x) |
把x的两边截去trim_str字符串，缺省截去空格。 |
|
REPLACE(x,old,new) |
在x中查找old，并替换为new。 |
|
SUBSTR(x,start[,length]) |
返回x的字串，从staart处开始，截取length个字符，缺省length，默认到结尾。 |
表1 字符函数
|
示例 |
示例结果 |
|
SELECT ASCII('a') FROM DUAL |
97 |
|
SELECT CONCAT('Hello', ' world') FROM DUAL |
Hello world |
|
SELECT INSTR('Hello world'，'or') FROM DUAL |
8 |
|
SELECT LENGTH('Hello') FROM DUAL |
5 |
|
SELECT LOWER('hElLO') FROM DUAL; |
hello |
|
SELECT UPPER('hello') FROM DUAL |
HELLO |
|
SELECT LTRIM('===HELLO===', '=') FROM DUAL |
HELLO=== |
|
SELECT '=='||LTRIM(' HELLO===') FROM DUAL |
==HELLO=== |
|
SELECT RTRIM('===HELLO===', '=') FROM DUAL |
===HELLO |
|
SELECT '='||TRIM(' HELLO ')||'=' FROM DUAL |
=HELLO= |
|
SELECT TRIM('=' FROM '===HELLO===') FROM DUAL |
HELLO |
|
SELECT REPLACE('ABCDE','CD','AAA') FROM DUAL |
ABAAAE |
|
SELECT SUBSTR('ABCDE',2) FROM DUAL |
BCDE |
|
SELECT SUBSTR('ABCDE',2,3) FROM DUAL |
BCD |
表2 字符函数示例
数字函数
数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。
|
函数 |
说明 |
示例 |
|
ABS(x) |
x绝对值 |
ABS(-3)=3 |
|
ACOS(x) |
x的反余弦 |
ACOS(1)=0 |
|
COS(x) |
余弦 |
COS(1)=1.57079633 |
|
CEIL(x) |
大于或等于x的最小值 |
CEIL(5.4)=6 |
|
FLOOR(x) |
小于或等于x的最大值 |
FLOOR(5.8)=5 |
|
LOG(x,y) |
x为底y的对数 |
LOG(2,4)=2 |
|
MOD(x,y) |
x除以y的余数 |
MOD(8,3)=2 |
|
POWER(x,y) |
x的y次幂 |
POWER(2,3)=8 |
|
ROUND(x[,y]) |
x在第y位四舍五入 |
ROUND(3.456,2)=3.46 |
|
SQRT(x) |
x的平方根 |
SQRT(4)=2 |
|
TRUNC(x[,y]) |
x在第y位截断 |
TRUNC(3.456,2)=3.45 |
表3 数字函数
说明：
1. ROUND(X[,Y])，四舍五入。
在缺省y时，默认y=0；比如：ROUND(3.56)=4。
y是正整数，就是四舍五入到小数点后y位。ROUND(5.654,2)=5.65。
y是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。
2. TRUNC(x[,y])，直接截取，不四舍五入。
在缺省y时，默认y=0；比如：TRUNC (3.56)=3。
y是正整数，就是四舍五入到小数点后y位。TRUNC (5.654,2)=5.65。
y是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。
日期函数
日期函数对日期进行运算。常用的日期函数有：
1. ADD_MONTHS(d,n)，在某一个日期d上，加上指定的月数n，返回计算后的新日期。d表示日期，n表示要加的月数。
图1 ADD_MONTHS函数示例
2. LAST_DAY(d)，返回指定日期当月的最后一天。
图2 LAST_DAY函数示例
3. ROUND(d[,fmt])，返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式模型。默认fmt为DDD，即月中的某一天。
如果fmt为“YEAR”则舍入到某年的1月1日，即前半年舍去，后半年作为下一年。
如果fmt为“MONTH”则舍入到某月的1日，即前月舍去，后半月作为下一月。
默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
如果fmt为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。
图3 ROUND函数示例
与ROUND对应的函数时TRUNC(d[,fmt])对日期的操作，TRUNC与ROUND非常相似，只是不对日期进行舍入，直接截取到对应格式的第一天。
4. EXTRACT(fmt FROM d)，提取日期中的特定部分。
fmt为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中YEAR、MONTH、DAY可以为DATE类型匹配，也可以与TIMESTAMP类型匹配；但是HOUR、MINUTE、SECOND必须与TIMESTAMP类型匹配。
HOUR匹配的结果中没有加上时区，因此在中国运行的结果小8小时。
图4 EXTRACT函数示例
转换函数
转换函数将值从一种数据类型转换为另外一种数据类型。常用的转换函数有：
1. TO_CHAR(d|n[,fmt])
把日期和数字转换为制定格式的字符串。fmt是格式化字符串，日期的格式化字符串前面已经学习过。
代码演示：TO_CHAR对日期的处理
|
SQL> SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS') "date" ① 2 FROM DUAL; date ----------------------- 2009年08月11日 12:06:00 |
代码解析：
1 在格式化字符串中，使用双引号对非格式化字符进行引用。
针对数字的格式化，格式化字符有：
|
参数 |
示例 |
说明 |
|
9 |
999 |
指定位置处显示数字。 |
|
. |
9.9 |
指定位置返回小数点 |
|
, |
99,99 |
指定位置返回一个逗号 |
|
$ |
$999 |
数字开头返回一个美元符号 |
|
EEEE |
9.99EEEE |
科学计数法表示 |
|
L |
L999 |
数字前加一个本地货币符号 |
|
PR |
999PR |
如果数字式负数则用尖括号进行表示 |
表4 数字格式化字符
代码演示：TO_CHAR对数字的处理
|
SQL> SELECT TO_CHAR(-123123.45,'L9.9EEEEPR') "date" 2 FROM DUAL 3 / date -------------------- <￥1.2E+05> |
2. TO_DATE(x [,fmt])
把一个字符串以fmt格式转换为一个日期类型，前面已经学习过。
3. TO_NUMBER(x[,fmt])
把一个字符串以fmt格式转换为一个数字。fmt格式字符参考表3。
代码演示：TO_NUM函数
|
SQL> SELECT TO_NUMBER('-$12,345.67','$99,999.99') "NUM" 2 FROM DUAL 3 / NUM --------------- -12345.67 |
其他单行函数
1. NVL(x,value)
如果x为空，返回value，否则返回x。
案例7：对工资是2000元以下的员工，如果没有发奖金，每人奖金100元。
代码演示：NVL函数
|
SQL> SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000;
7 rows selected |
2. NVL2(x,value1,value2)
如果x非空，返回value1，否则返回value2。
案例8：对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元。
代码演示：NVL2函数
|
SQL> SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) "comm" 2 FROM EMP WHERE SAL<2000;
8 rows selected |
聚合函数
聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。
|
名称 |
作用 |
语法 |
|
AVG |
平均值 |
AVG(表达式) |
|
SUM |
求和 |
SUM(表达式) |
|
MIN、MAX |
最小值、最大值 |
MIN(表达式)、MAX(表达式) |
|
COUNT |
数据统计 |
COUNT(表达式) |
表 5 聚合函数
案例9：求本月所有员工的基本工资总和。
代码演示：SUM函数
|
SQL> select sum(sal) from emp; SUM(SAL) ---------------- 29025 |
案例10：求不同部门的平均工资。
代码演示：AVG函数下的分组查询
|
SQL> SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO; DEPTNO AVG(SAL) --------- ---------- 30 1566.66666 20 2175 10 2916.66666 |
本章总结
Oracle常用函数有字符相关的函数、数字相关的函数、日期相关的函数、转换函数等。
EXTRACT函数能够获取日期中的某个字段的值。
TO_CHAR函数能够把数字和日期转换成固定的字符串格式。TO_DATE 函数能够把固定格式的字符串转换为日期类型。
子查询中有返回单行的子查询和返回多行的子查询。
Oracle中存在ROWID、ROWNUM等伪列。
本章练习
1. 描述TO_CHAR和TO_DATE函数的用法。
2. 描述EXTRACT函数的用法。
3. 你知道有哪些关于日期函数的用法？
章节知识结构图
第4章
表空间、数据库对象
|
|
主要内容 |
|
|
同义词概念 序列的应用 视图的概念 索引的概念 表空间的概念 |
1. Oracle数据库对象
数据库对象是数据库的组成部分，常常用CREATE命令进行创建，可以使用ALTER命令修改，用DROP执行删除操作。前面已经接触过的数据库对象有表、用户等。
今天将学习更多的Oracle数据库对象：
同义词：就是给数据库对象一个别名。
序列：Oracle中实现增长的对象。
视图：预定义的查询，作为表一样的查询使用，是一张虚拟表。
索引：对数据库表中的某些列进行排序，便于提高查询效率。
同义词
同义词（Synonym）是数据库对象的一个别名，Oracle可以为表、视图、序列、过程、函数、程序包等指定一个别名。同义词有两种类型：
私有同义词：拥有CREATE SYNONYM权限的用户（包括非管理员用户）即可创建私有同义词，创建的私有同义词只能由当前用户使用。
公有同义词：系统管理员可以创建公有同义词，公有同义词可以被所有用户访问。
创建同义词的语法是：
语法结构：同义词
|
CREATE [OR REPLACE] [PUBLIC] SYSNONYM [schema.]synonym_name FOR [schema.]object_name |
语法解析：
1 CREATE [OR REPLACE：]表示在创建同义词时，如果该同义词已经存在，那么就用新创建的同义词代替旧同义词。
2 PULBIC：创建公有同义词时使用的关键字，一般情况下不需要创建公有同义词。
3 Oracle中一个用户可以创建表、视图等多种数据库对象，一个用户和该用户下的所有数据库对象的集合称为Schema（中文称为模式或者方案），用户名就是Schema名。一个数据库对象的全称是：用户名.对象名，即schema.object_name。
如果一个用户有权限访问其他用户对象时，就可以使用全称来访问。比如：
代码演示：System用户访问Scott用户的Emp表
|
SQL> conn system/manager@orcl; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as system SQL> SELECT ENAME,JOB,SAL FROM SCOTT.EMP WHERE SAL>2000; ①
6 rows selected |
代码解析：
1 管理员用户可以访问任何用户的数据库对象，SYSTEM用户访问SCOTT用户的EMP表时，必须使用SCOTT.EMP。
案例1：创建一个用户XiaoMei，该用户拥有CONNECT角色和RESOURCE角色。为SCOTT用户的EMP表创建同义词，并通过同义词访问该EMP表。
代码演示：创建同义词并访问
|
SQL> CONN system/manager@orcl; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as system SQL> CREATE USER XiaoMei IDENTIFIED BY XiaoMei; ① User created SQL> GRANT CONNECT TO XiaoMei; Grant succeeded SQL> GRANT RESOURCE TO XiaoMei; Grant succeeded SQL> GRANT CREATE SYNONYM TO XiaoMei; Grant succeeded SQL> CONN XiaoMei/XiaoMei@ORCL; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as XiaoMei SQL> CREATE SYNONYM MyEmp FOR SCOTT.EMP; ② Synonym created SQL> SELECT * FROM MYEMP; ③ SELECT * FROM MYEMP ORA-00942: 表或视图不存在 SQL> CONNECT SCOTT/tiger@ORCL Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as SCOTT SQL> GRANT ALL ON EMP TO XiaoMei; ④ Grant succeeded SQL> CONNECT XiaoMei/XiaoMei@ORCL; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as XiaoMei SQL> SELECT ENAME,JOB,SAL FROM MyEmp WHERE SAL>2000; ⑤
6 rows selected |
代码解析：
1 在管理员用户下创建用户XiaoMei，对用户XiaoMei授予CONNECT和RESOURCE角色。为了XiaoMei能够创建序列，必须授予系统权限：CREATE SYNONYM。
2 在XiaoMei用户下，为SCOTT.EMP创建私有同义词MyEmp，同义词MyEmp只能在XiaoMei用户下使用。访问MyEmp就是访问SCOTT.EMP对象。
3 访问MyEmp对象出错：对象不存在。因为XiaoMei如果访问MyEmp，就相当于访问SCOTT.EMP对象，那么SCOTT用户必须对XiaoMei授予相应的权限。
4 SCOTT用户下，把EMP表的所有权限（增删改查）授予XiaoMei。
5 对MyEmp执行查询操作。MyEmp就可以像在本地的表一样使用。
删除同义词使用的语法是：
语法结构：删除同义词
|
DROP [PUBLIC] SYNONYM [schema.]sysnonym_name |
语法解析：
1 PUBLIC：删除公共同义词。
2 同义词的删除只能被拥有同义词对象的用户或者管理员删除。
3 此命令只能删除同义词，不能删除同义词下的源对象。
序列
序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列，序列中的可以升序生成，也可以降序生成。创建序列的语法是：
语法结构：创建序列
|
CREATE SEQUENCE sequence_name [START WITH num] [INCREMENT BY increment] [MAXVALUE num|NOMAXVALUE] [MINVALUE num|NOMINVALUE] [CYCLE|NOCYCLE] [CACHE num|NOCACHE] |
语法解析：
1 START WITH：从某一个整数开始，升序默认值是1，降序默认值是-1。
2 INCREMENT BY：增长数。如果是正数则升序生成，如果是负数则降序生成。升序默认值是1，降序默认值是-1。
3 MAXVALUE：指最大值。
4 NOMAXVALUE：这是最大值的默认选项，升序的最大值是：1027，降序默认值是-1。
5 MINVALUE：指最小值。
6 NOMINVALUE：这是默认值选项，升序默认值是1，降序默认值是-1026。
7 CYCLE：表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最小值后，从最大值重新开始。
8 NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默认NOCYCLE。
9 CACHE：使用CACHE选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。Oracle默认会生产20个序列号。
10 NOCACHE：不预先在内存中生成序列号。
案例2：创建一个从1开始，默认最大值，每次增长1的序列，要求NOCYCLE，缓存中有30个预先分配好的序列号。
代码演示：生成序列号
|
SQL> CREATE SEQUENCE MYSEQ 2 MINVALUE 1 3 START WITH 1 4 NOMAXVALUE 5 INCREMENT BY 1 6 NOCYCLE 7 CACHE 30 8 / Sequence created |
序列创建之后，可以通过序列对象的CURRVAL和NEXTVAL两个“伪列”分别访问该序列的当前值和下一个值。
代码演示：序列使用
|
SQL> SELECT MYSEQ.NEXTVAL FROM DUAL; NEXTVAL ---------- 1 SQL> SELECT MYSEQ.NEXTVAL FROM DUAL; NEXTVAL ---------- 2 SQL> SELECT MYSEQ.CURRVAL FROM DUAL; CURRVAL ---------- 2 |
使用ALTER SEQUENCE可以修改序列，在修改序列时有如下限制：
1. 不能修改序列的初始值。
2. 最小值不能大于当前值。
3. 最大值不能小于当前值。
使用DROP SEQUENCE命令可以删除一个序列对象。
代码演示：序列修改和删除
|
SQL> ALTER SEQUENCE MYSEQ 2 MAXVALUE 10000 3 MINVALUE -300 4 / SEQUENCE ALTERED SQL> DROP SEQUENCE MYSEQ; SEQUENCE DROPPED |
视图
视图（View）实际上是一张或者多张表上的预定义查询，这些表称为基表。从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的SELECT…FROM即可。
视图具有以下优点：
1. 可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表。
2. 可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。
3. 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。
4. 从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。
语法结构：创建视图
|
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name AS SELECT查询 [WITH READ ONLY CONSTRAINT] |
语法解析：
1. OR REPLACE：如果视图已经存在，则替换旧视图。
2. FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。
3. NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。
4. WITH READ ONLY：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制（比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作），WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。
案例3：基于EMP表和DEPT表创建视图
代码演示：视图
|
SQL> CREATE OR REPLACE VIEW EMPDETAIL 2 AS 3 SELECT EMPNO,ENAME,JOB,HIREDATE,EMP.DEPTNO,DNAME 4 FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO 5 WITH READ ONLY 6 / VIEW CREATED SQL> SELECT * FROM EMPDETAIL; ①
14 ROWS SELECTED |
代码解析：
1 对视图可以像表一样进行查询。该视图中隐藏了员工的工资。
删除视图可以使用“DROP VIEW 视图名称”，删除视图不会影响基表的数据。
索引
当我们在某本书中查找特定的章节内容时，可以先从书的目录着手，找到该章节所在的页码，然后快速的定位到该页。这种做法的前提是页面编号是有序的。如果页码无序，就只能从第一页开始，一页页的查找了。
数据库中索引（Index）的概念与目录的概念非常类似。如果某列出现在查询的条件中，而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据排序，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，Oracle会自动的引用该索引，先从索引表中查询出符合条件记录的ROWID，由于ROWID是记录的物理地址，因此可以根据ROWID快速的定位到具体的记录，表中的数据非常多时，引用索引带来的查询效率非常可观。
|
|
如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该列创建索引。 当从很多行的表中查询少数行时，也要考虑创建索引。有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的10%时，索引就非常有用。 |
Oracle数据库会为表的主键和包含唯一约束的列自动创建索引。索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。
语法结构：创建索引
|
CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…]) |
语法解析：
1. UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。
2. index_name：指定索引名。
3. tabl_name：指定要为哪个表创建索引。
4. column_name：指定要对哪个列创建索引。我们也可以对多列创建索引；这种索引称为组合索引。
案例4：为EMP表的ENAME列创建创建唯一索引，为EMP表的工资列创建普通索引，把JOB列先变为小写再创建索引。
代码演示：创建索引
|
SQL> CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME); ① Index created SQL> CREATE INDEX IDX_SAL ON EMP(SAL); ② Index created SQL> CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB)); ③ Index created |
代码解析：
1 为SCOTT.EMP表的ENAME列创建唯一索引。
2 为SCOTT.EMP表的SAL列创建索引。
3 在查询中可能经常使用job的小写作为条件的表达式，因此创建索引时，可以先对JOB列中的所有值转换为小写后创建索引，而这时需要使用lower函数，这种索引称为基于函数的索引。
在select语句查询时，Oracle系统会自动为查询条件上的列应用索引。索引就是对某一列进行排序，因此在索引列上，重复值越少，索引的效果越明显。
Oracle可以为一些列值重复非常多且值有限的列（比如性别列）上创建位图索引。关于Oracle更多的索引类型（比如反向键索引等），请参考Oracle官方文档。
表空间
在数据库系统中，存储空间是较为重要的资源，合理利用空间，不但能节省空间，还可以提高系统的效率和工作性能。Oracle可以存放海量数据，所有数据都在数据文件中存储。而数据文件大小受操作系统限制，并且过大的数据文件对数据的存取性能影响非常大。同时Oracle是跨平台的数据库，Oracle数据可以轻松的在不同平台上移植，那么如何才能提供统一存取格式的大容量呢？Oracle采用表空间来解决。
表空间只是一个逻辑概念，若干操作系统文件（文件可以不是很大）可以组成一个表空间。表空间统一管理空间中的数据文件，一个数据文件只能属于一个表空间。一个数据库空间由若干个表空间组成。如图所示：
图1 数据空间、表空间和数据文件
Oracle中所有的数据（包括系统数据），全部保存在表空间中，常见的表空间有：
系统表空间：存放系统数据，系统表空间在数据库创建时创建。表空间名称为SYSTEM。存放数据字典和视图以及数据库结构等重要系统数据信息，在运行时如果SYSTEM空间不足，对数据库影响会比较大，虽然在系统运行过程中可以通过命令扩充空间，但还是会影响数据库的性能，因此有必要在创建数据库时适当的把数据文件设置大一些。
TMEP表空间：临时表空间，安装数据库时创建，可以在运行时通过命令增大临时表空间。临时表空间的重要作用是数据排序。比如当用户执行了诸如Order by等命令后，服务器需要对所选取数据进行排序，如果数据很大，内存的排序区可能装不下太大数据，就需要把一些中间的排序结果写在硬盘的临时表空间中。
用户表自定义空间：用户可以通过CREATE TABLESPACE命令创建表空间。
创建表空间需要考虑数据库对分区（Extent，一个Oracle分区是数据库文件中一段连续的空间，Oracle分区是Oracle管理中最小的单位）的管理，比如当一个表创建后先申请一个分区，在Insert执行过程中，如果分区数据已满，需要重新申请另外的分区。如果一个数据库中的分区大小不一，创建表空间时需要考虑一系列问题。因此在Oracle8i之后，创建表空间都推荐使用“本地管理表空间”，这种表空间中的分区是一个固定大小的值，创建表空间的语法是：
语法结构：创建表空间
|
CREATE TABLESPACE 空间名称 DATAFILE '文件名1' SIZE 数字M [,'文件名2' SIZE 数字….] EXTENT MANAGEMENT LOCAL UNIFORM SIZE 数字M |
语法解析：
1. 文件名包括完整路径和文件名，每个数据文件定义了文件的初始大小，初始大小一般以“M”为单位。一个表空间中可以有多个数据文件。
2. EXTENT MANAGEMENT LOCAL指明表空间类型是：本地管理表空间。本地管理表空间要求Oracle中的数据分区（Extent）大小统一。
3. UNIFORM SIZE：指定每个分区的统一大小。
案例5：创建一个表空间，包含两个数据文件大小分别是10MB，5MB，要求extent的大小统一为1M。
代码演示：创建表空间
|
SQL> CREATE TABLESPACE MYSPACE 2 DATAFILE 'D:/A.ORA' SIZE 10M, 3 'D:/B.ORA' SIZE 5M 4 EXTENT MANAGEMENT LOCAL 5 UNIFORM SIZE 1M 6 / Tablespace created |
必须是管理员用户才能创建表空间，当表空间的空间不足时可以使用ALTER TABLESPACE命令向表空间中追加数据文件扩充表空间。
代码演示：扩充表空间
|
SQL> ALTER TABLESPACE MYSPACE 2 ADD DATAFILE 'D:/C.ORA' SIZE 10M 3 / Tablespace altered |
表空间可以在不使用时删除，使用DROP TABLESPACE命令。
数据库的所有数据全部在某一表空间中存放，在创建用户时，可以为用户指定某一表空间，那么该用户下的所有数据库对象（比如表）默认都存储在该空间中。
代码演示：为某一用户指定默认表空间
|
SQL> CREATE USER ACONG IDENTIFIED BY ACONG 2 DEFAULT TABLESPACE MYSPACE 3 / User created |
在创建表时，表中数据存放在用户的默认表空间中，也可以通过tablespace子句为表指定表中数据存放在其他表空间中。
代码演示：为表指定表空间
|
SQL> CREATE TABLE SCORES 2 ( 3 ID NUMBER , 4 TERM VARCHAR2(2), 5 STUID VARCHAR2(7) NOT NULL, 6 EXAMNO VARCHAR2(7) NOT NULL, 7 WRITTENSCORE NUMBER(4,1) NOT NULL, 8 LABSCORE NUMBER(4,1) NOT NULL 9 ) 10 TABLESPACE MYSPACE 11 / Table created |
创建索引时也可以为索引指定表空间。
代码演示：为索引指定表空间
|
SQL> CREATE INDEX UQ_ID ON SCORES(ID) 2 TABLESPACE MYSPACE; Index created |
表和索引一旦创建，表空间无法修改。
本章总结
Oracle数据库对象都是使用CREATE命令创建的。
同义词就是数据库对象的一个别名。同义词的类型有公有同义词和私有同义词。只有管理员可以创建共有同义词。创建同义词的命令是：CREATE SYNONYM。
序列能够产生一个连续不重复的整数。经常作为数据库的主键生成器。创建序列的命令是CREATE SEQUENCE。
序列的访问使用两个“伪列”，CURRVAL表示序列的当前值，NEXTVAL表示序列的下一个值。
视图就是一个预处理的查询语句，可以从若干表中过滤数据。
索引就是在查询中经常使用的列进行排序。常见的索引有：普通索引、唯一序列、组合索引以及基于函数的索引。此外还有位图索引、反向键索引等。
表空间是数据库的一个逻辑概念，表空间由若干个数据文件组成。为数据库对象和数据提供统一的空间管理。
本章练习
1. 产生一个用于DEPT表的主键值的序列，序列起始值是100，最大值是500，增长步长是10。
2. 用序列产生DEPT表的主键，向DEPT表中插入3条记录。
3. 为DEPT表创建一个同义词。
4. 创建一个视图包括EMP表的EMPNO,ENAME,JOB，部门表的DNAME列，只能包含销售部的记录。
5. 为EMP表的ENAME列创建唯一索引。
6. 为EMP表的SAL列创建一个普通索引。
7. 以学期和学生姓名为名称比如（S2XiaoMei）创建一个表空间，该表空间是以学生姓名为用户的默认表空间。
章节知识结构图
第5章
PL/SQL程序设计
|
|
主要内容 |
|
|
PL/SQL数据类型 PL/SQL条件和循环控制 动态执行SQL PL/SQL中的异常处理 |
1. PL/SQL简介
从第一学期到现在，在数据库上一直使用单一的SQL语句进行数据操作，没有流程控制，无法开发复杂的应用。Oracle PL/SQL语言（Procedural Language/SQL）是结合了结构化查询与Oracle自身过程控制为一体的强大语言，PL/SQL不但支持更多的数据类型，拥有自身的变量声明、赋值语句，而且还有条件、循环等流程控制语句。过程控制结构与SQL数据处理能力无缝的结合形成了强大的编程语言，可以创建过程和函数以及程序包。
PL/SQL是一种块结构的语言，它将一组语句放在一个块中，一次性发送给服务器，PL/SQL引擎分析收到PL/SQL语句块中的内容，把其中的过程控制语句由PL/SQL引擎自身去执行，把PL/SQL块中的SQL语句交给服务器的SQL语句执行器执行。如图所示：
图1 PL/SQL体系结构
PL/SQL块发送给服务器后，先被编译然后执行，对于有名称的PL/SQL块（如子程序）可以单独编译，永久的存储在数据库中，随时准备执行。PL/SQL的优点还有：
支持SQL
SQL是访问数据库的标准语言，通过SQL命令，用户可以操纵数据库中的数据。PL/SQL支持所有的SQL数据操纵命令、游标控制命令、事务控制命令、SQL函数、运算符和伪列。同时PL/SQL和SQL语言紧密集成，PL/SQL支持所有的SQL数据类型和NULL值。
支持面向对象编程
PL/SQL支持面向对象的编程，在PL/SQL中可以创建类型，可以对类型进行继承，可以在子程序中重载方法等。
更好的性能
SQL是非过程语言，只能一条一条执行，而PL/SQL把一个PL/SQL块统一进行编译后执行，同时还可以把编译好的PL/SQL块存储起来，以备重用，减少了应用程序和服务器之间的通信时间，PL/SQL是快速而高效的。
可移植性
使用PL/SQL编写的应用程序，可以移植到任何操作系统平台上的Oracle服务器，同时还可以编写可移植程序库，在不同环境中重用。
安全性
可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分隔，这样可以限制对Oracle数据库的访问，数据库还可以授权和撤销其他用户访问的能力。
PL/SQL块
PL/SQL是一种块结构的语言，一个PL/SQL程序包含了一个或者多个逻辑块，逻辑块中可以声明变量，变量在使用之前必须先声明。除了正常的执行程序外，PL/SQL还提供了专门的异常处理部分进行异常处理。每个逻辑块分为三个部分，语法是：
语法结构：PL/SQL块的语法
|
[DECLARE --declaration statements] ① BEGIN --executable statements ② [EXCEPTION --exception statements] ③ END; |
语法解析：
1 声明部分：声明部分包含了变量和常量的定义。这个部分由关键字DECLARE开始，如果不声明变量或者常量，可以省略这部分。
2 执行部分：执行部分是 PL/SQL块的指令部分，由关键字BEGIN开始，关键字END结尾。所有的可执行PL/SQL语句都放在这一部分，该部分执行命令并操作变量。其他的PL/SQL块可以作为子块嵌套在该部分。PL/SQL块的执行部分是必选的。注意END关键字后面用分号结尾。
3 异常处理部分：该部分是可选的，该部分用EXCEPTION关键字把可执行部分分成两个小部分，之前的程序是正常运行的程序，一旦出现异常就跳转到异常部分执行。
PL/SQL是一种编程语言，与Java和C#一样，除了有自身独有的数据类型、变量声明和赋值以及流程控制语句外，PL/SQL还有自身的语言特性：
PL/SQL对大小写不敏感，为了良好的程序风格，开发团队都会选择一个合适的编码标准。比如有的团队规定：关键字全部大些，其余的部分小写。
PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以是多行的，但分号表示该语句结束。一行中可以有多条SQL语句，他们之间以分号分隔，但是不推荐一行中写多条语句。 Int x=12 x+=1 x :=12 x number :=3; x varchar2(10) default ‘kkk’
--
/**/
PL/SQL中的特殊符号说明：
|
类型 |
符号 |
说明 |
|
赋值运算符 |
:= |
Java和C#中都是等号，PL/SQL的赋值是：= |
|
特殊字符 |
|| |
字符串连接操作符。 |
|
-- |
PL/SQL中的单行注释。 |
|
|
/*,*/ |
PL/SQL中的多行注释，多行注释不能嵌套。 |
|
|
<<,>> |
标签分隔符。只为了标识程序特殊位置。 |
|
|
.. |
范围操作符，比如：1..5 标识从１到５ |
|
|
算术运算符 |
+，-，*，/ |
基本算术运算符。 |
|
** |
求幂操作，比如：3**2=9 |
|
|
关系运算符 |
>，<,>=,<=,= |
基本关系运算符，=表示相等关系，不是赋值。 |
|
<>,!= |
不等关系。 |
|
|
逻辑运算符 |
AND,OR,NOT |
逻辑运算符。 |
表1 PL/SQL中的特殊符号和运算符
变量声明
PL/SQL支持SQL中的数据类型，PL/SQL中正常支持NUMBER,VARCHAR2,DATE等Oracle SQL数据类型。声明变量必须指明变量的数据类型，也可以声明变量时对变量初始化，变量声明必须在声明部分。声明变量的语法是：
语法格式：声明变量
|
变量名 数据类型[ :=初始值] |
语法解析：
数据类型如果需要长度，可以用括号指明长度，比如：varchar2(20)。
代码演示：声明变量
|
SQL> DECLARE 2 sname VARCHAR2(20) :='jerry'; ① 3 BEGIN 4 sname:=sname||' and tom'; ② 5 dbms_output.put_line(sname); ③ 6 END; 7 /jerry PL/SQL procedure successfully completed |
代码解析：
1 声明一个变量sname，初始化值是“jerry”。字符串用单引号，如果字符串中出现单引号可以使用两个单引号（’’）来表示，即单引号同时也具有转义的作用。
2 对变量sname重新赋值，赋值运算符是“:=”。
3 dbms_output.put_line是输出语句，可以把一个变量的值输出，在SQL*Plus中输出数据时，可能没有结果显示，可以使用命令：set serveroutput on设置输出到SQL*Plus控制台上。
对变量赋值还可以使用SELECT…INTO 语句从数据库中查询数据对变量进行赋值。但是查询的结果只能是一行记录，不能是零行或者多行记录。
代码演示：变量赋值
|
SQL> DECLARE 2 sname VARCHAR2(20) DEFAULT 'jerry'; ① 3 BEGIN 4 SELECT ename INTO sname FROM emp WHERE empno=7934; ② 5 dbms_output.put_line(sname); 6 END; 7 / MILLER PL/SQL procedure successfully completed |
代码解析：
1 变量初始化时，可以使用DEFAULT关键字对变量进行初始化。
2 使用select…into语句对变量sname赋值，要求查询的结果必须是一行，不能是多行或者没有记录。
声明常量
常量在声明时赋予初值，并且在运行时不允许重新赋值。使用CONSTANT关键字声明常量。
代码演示：声明常量
|
SQL> DECLARE 2 pi CONSTANT number :=3.14; --圆周率长值 ① 3 r number DEFAULT 3; --圆的半径默认值3 ② 4 area number; --面积。 5 BEGIN 6 area:=pi*r*r; --计算面积 7 dbms_output.put_line(area); --输出圆的面积 8 END; 9 / 28.26 PL/SQL procedure successfully completed |
代码解析：
1 声明常量时使用关键字CONSTANT，常量初值可以使用赋值运算符（:=）赋值，也可以使用DEFAULT关键字赋值。
在SQL*Plus中还可以声明Session（会话，也就是一个客户端从连接到退出的过程称为当前用户的会话。）全局级变量，该变量在整个会话过程中均起作用，类似的这种变量称为宿主变量。宿主变量在PL/SQL引用时要用“:变量名”引用。
代码演示：宿主常量
|
SQL> var emp_name varchar(30); ① SQL> BEGIN 2 SELECT ename INTO :emp_name FROM emp WHERE empno=7499; ② 3 END; 4 / PL/SQL procedure successfully completed emp_name --------- ALLEN SQL> print emp_name; ③ emp_name --------- ALLEN |
代码解析：
1 可以使用var声明宿主变量。
2 PL/SQL中访问宿主变量时要在变量前加“:”。
3 在SQL*Plus中，使用print可以输出变量中的结果。
PL/SQL数据类型
前面在建表时，学习过Oracle SQL的数据类型，PL/SQL不但支持这些数据类型，还具备自身的数据类型。PL/SQL的数据类型包括标量数据类型，引用数据类型和存储文本、图像、视频、声音等非结构化的大数据类型（LOB数据类型）等。下面列举一些常用的类型。
标量数据类型
标量数据类型的变量只有一个值，且内部没有分量。标量数据类型包括数字型，字符型，日期型和布尔型。这些类型有的是Oracle SQL中定义的数据类型，有的是PL/SQL自身附加的数据类型。字符型和数字型又有子类型，子类型只与限定的范围有关，比如NUMBER类型可以表示整数，也可以表示小数，而其子类型POSITIVE只表示正整数。
|
类型 |
说明 |
|
VARCHAR2(长度) |
可变长度字符串，Oracle SQL定义的数据类型，在PL/SQL中使用时最常32767字节。在PL/SQL中使用没有默认长度，因此必须指定。 |
|
NUMBER(精度，小数) |
Oracle SQL定义的数据类型，见第二章。 |
|
DATE |
Oracle SQL定义的日期类型，见第二章。 |
|
TIMESTAMP |
Oracle SQL定义的日期类型，见第二章。 |
|
CHAR(长度) |
Oracle SQL定义的日期类型，固定长度字符，最长32767字节，默认长度是1，如果内容不够用空格代替。 |
|
LONG |
Oracle SQL定义的数据类型，变长字符串基本类型，最长32760字节。在Oracle SQL中最长2147483647字节。 |
|
BOOLEAN |
PL/SQL附加的数据类型，逻辑值为TRUE、FALSE、NULL |
|
BINARY_INTEGER |
PL/SQL附加的数据类型，介于-231和231之间的整数。 |
|
PLS_INTEGER |
PL/SQL附加的数据类型，介于-231和231之间的整数。类似于BINARY_INTEGER，只是PLS_INTEGER值上的运行速度更快。 |
|
NATURAL |
PL/SQL附加的数据类型，BINARY_INTEGER子类型，表示从0开始的自然数。 |
|
NATURALN |
与NATURAL一样，只是要求NATURALN类型变量值不能为NULL。 |
|
POSITIVE |
PL/SQL附加的数据类型，BINARY_INTEGER子类型，正整数。 |
|
POSITIVEN |
与POSITIVE一样，只是要求POSITIVE的变量值不能为NULL。 |
|
REAL |
Oracle SQL定义的数据类型，18位精度的浮点数 |
|
INT,INTEGER,SMALLINT |
Oracle SQL定义的数据类型，NUMBERDE的子类型，38位精度整数。 |
|
SIGNTYPE |
PL/SQL附加的数据类型，BINARY_INTEGER子类型。值有：1、-1、0。 |
|
STRING |
与VARCHAR2相同。 |
表2 PL/SQL中标量数据类型。
属性数据类型
当声明一个变量的值是数据库中的一行或者是数据库中某列时，可以直接使用属性类型来声明。Oracle中存在两种属性类型：%TYPE和%ROWTYPE。
% ROWTYPE
引用数据库表中的一行作为数据类型，即RECORD类型（记录类型），是PL/SQL附加的数据类型。表示一条记录，就相当于C#中的一个对象。可以使用“.”来访问记录中的属性。
代码演示：
|
SQL> DECLARE 2 myemp EMP%ROWTYPE; ① 3 BEGIN 4 SELECT * INTO myemp FROM emp WHERE empno=7934; ② 5 dbms_output.put_line(myemp.ename); ③ 6 END; 7 / MILLER PL/SQL procedure successfully completed |
代码解析：
1 声明一个myemp对象，该对象表示EMP表中的一行。
2 从EMP表中查询一条记录放入myemp对象中。
3 访问该对象的属性可以使用“.”。
%TYPE
引用某个变量或者数据库的列的类型作为某变量的数据类型。
代码演示：%TYPE应用
|
SQL> DECLARE 2 sal emp.sal%TYPE; ① 3 mysal number(4):=3000; 4 totalsal mysal%TYPE; ② 5 BEGIN 6 SELECT SAL INTO sal FROM emp WHERE empno=7934; 7 totalsal:=sal+mysal; 8 dbms_output.put_line(totalsal); 9 END; 10 / 4300 PL/SQL procedure successfully completed |
代码解析：
1 定义变量sal为emp表中sal列的类型。
2 定义totalsal是变量mysal的类型。
%TYPE可以引用表中的某列作的类型为变量的数据类型，也可以引用某变量的类型作为新变量的数据类型。
PL/SQL条件控制和循环控制
PL/SQL程序可通过条件或循环结构来控制命令执行的流程。PL/SQL提供了丰富的流程控制语句，与C#一样也有三种控制结构：
顺序结构
条件结构
循环结构
条件控制
C#中的条件控制使用关键字if和switch。PL/SQL中关于条件控制的关键字有IF-THEN、IF-THEN-ELSE、IF-THEN-ELSIF和多分枝条件CASE。
IF-THEN
该结构先判断一个条件是否为TRUE，条件成立则执行对应的语句块，与C#中的if语句很相似，具体语法是：
|
C#中if语法 |
PL/SQL中IF语法 |
|
if (条件){ //条件结构体 } |
IF 条件 THEN --条件结构体 END IF; |
表3 PL/SQL中条件语法
说明：
1 用IF关键字开始，END IF关键字结束，注意END IF后面有一个分号。
2 条件部分可以不使用括号，但是必须以关键字THEN来标识条件结束，如果条件成立，则执行THEN后到对应END IF之间的语句块内容。如果条件不成立，则不执行条件语句块的内容。
3 C#结构用一对大括号来包含条件结构体的内容。PL/SQL中关键字THEN到END IF之间的内容是条件结构体内容。
4 条件可以使用关系运算符合逻辑运算符。
案例1：查询JAMES的工资，如果大于900元，则发奖金800元。
代码演示：IF-THEN应用
|
DECLARE newSal emp.sal % TYPE; BEGIN SELECT sal INTO newSal FROM emp WHERE ename='JAMES'; IF newSal>900 THEN ① UPDATE emp SET comm=800 WHERE ename='JAMES'; END IF; COMMIT ; ② END; |
代码解析：
1 先判断条件，如果条件为TRUE，则执行条件结构体内部的内容。
2 在PL/SQL块中可以使用事务控制语句，该COMMIT同时也能把PL/SQL块外没有提交的数据一并提交，使用时需要注意。
IF-THEN-ELSE
语法格式：IF-THEN-ELSE
|
C#中if语法 |
PL/SQL中IF语法 |
|
if (条件){ //条件成立结构体 } else{ //条件不成立结构体 } |
IF 条件 THEN --条件成立结构体 ELSE --条件不成立结构体 END IF; |
表4 PL/SQL中条件语法
语法解析：
把ELSE与IF-THEN连在一起使用，如果IF条件不成立则执行就会执行ELSE部分的语句。
案例2：查询JAMES的工资，如果大于900元，则发奖金800元，否则发奖金400元。
代码演示：IF-THEN-ELSE应用
|
DECLARE newSal emp.sal % TYPE; BEGIN SELECT sal INTO newSal FROM emp WHERE ename='JAMES'; IF newSal>900 THEN UPDATE emp SET comm=800 WHERE ename='JAMES'; ELSE UPDATE emp SET comm=400 WHERE ename='JAMES'; END IF; END; |
IF-THEN-ELSIF
语法格式：IF-THEN-ELSIF
|
C#中if语法 |
PL/SQL中IF语法 |
|
if (条件2){ //条件成立结构体 } else if(条件2){ //条件不成立结构体 } else{ //以上条件都不成立结构体 } |
IF 条件1 THEN --条件1成立结构体 ELSIF 条件2 THEN --条件2成立结构体 ELSE --以上条件都不成立结构体 END IF; |
表5 PL/SQL中多分枝条件语法
语法解析：
PL/SQL中的再次条件判断中使用关键字ELSIF，而C#使用else if。
案例3：查询JAMES的工资，如果大于1500元，则发放奖金100元，如果工作大于900元，则发奖金800元，否则发奖金400元。
代码演示：IF-THEN-ELSIF应用
|
DECLARE newSal emp.sal % TYPE; BEGIN SELECT sal INTO newSal FROM emp WHERE ename='JAMES'; IF newSal>1500 THEN UPDATE emp SET comm=1000 WHERE ename='JAMES'; ELSIF newSal>1500 THEN UPDATE emp SET comm=800 WHERE ename='JAMES'; ELSE UPDATE emp SET comm=400 WHERE ename='JAMES'; END IF; END; |
CASE
CASE是一种选择结构的控制语句，可以根据条件从多个执行分支中选择相应的执行动作。也可以作为表达式使用，返回一个值。类似于C#中的switch语句。语法是：
语法格式：CASE
|
CASE [selector] WHEN 表达式1 THEN 语句序列1； WHEN 表达式2 THEN 语句序列2； WHEN 表达式3 THEN 语句序列3； …… [ELSE 语句序列N]； END CASE; |
语法解析：
如果存在选择器selector，选择器selector与WHEN后面的表达式匹配，匹配成功就执行THEN后面的语句。如果所有表达式都与selector不匹配，则执行ELSE后面的语句。
案例4：输入一个字母A、B、C分别输出对应的级别信息。
代码演示：CASE中存在selector，不返回值
|
DECLARE v_grade CHAR(1):=UPPER('&p_grade'); ① BEGIN CASE v_grade ② WHEN 'A' THEN dbms_output.put_line('Excellent'); WHEN 'B' THEN dbms_output.put_line('Very Good'); WHEN 'C' THEN dbms_output.put_line('Good'); ELSE dbms_output.put_line('No such grade'); END CASE; END; |
代码解析：
1 & grade表示在运行时由键盘输入字符串到grade变量中。
2 v_grade分别于WHEN后面的值匹配，如果成功就执行WHEN后的程序序列。
CASE语句还可以作为表达式使用，返回一个值。
代码演示：CASE中存在selector，作为表达式使用
|
DECLARE v_grade CHAR(1):=UPPER('&grade'); p_grade VARCHAR(20) ; BEGIN p_grade := ① CASE v_grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' ELSE 'No such grade' END; dbms_output.put_line('Grade:' ||v_grade||',the result is '||p_grade); END; |
代码解析：
1 CASE语句可以返回一个结果给变量p_grade
PL/SQL还提供了搜索CASE语句。也就是说，不使用CASE中的选择器，直接在WHEN后面判断条件，第一个条件为真时，执行对应THEN后面的语句序列。
代码演示：搜索CASE
|
DECLARE v_grade CHAR(1):=UPPER('&grade'); p_grade VARCHAR(20) ; BEGIN p_grade := CASE WHEN v_grade='A' THEN 'Excellent' WHEN v_grade='B' THEN 'Very Good' WHEN v_grade='C' THEN 'Good' ELSE 'No such grade' END; dbms_output.put_line('Grade:' ||v_grade||',the result is '||p_grade); END; |
循环结构
PL/SQL提供了丰富的循环结构来重复执行一些列语句。Oracle提供的循环类型有：
1. 无条件循环LOOP-END LOOP语句
2. WHILE循环语句
3. FOR循环语句
在上面的三类循环中EXIT用来强制结束循环，相当于C#循环中的break。
LOOP循环
LOOP循环是最简单的循环，也称为无限循环，LOOP和END LOOP是关键字。
语法格式：LOOP循环
|
LOOP --循环体 END LOOP； |
语法格式：
1. 循环体在LOOP和END LOOP之间，在每个LOOP循环体中，首先执行循环体中的语句序列，执行完后再重新开始执行。
2. 在LOOP循环中可以使用EXIT或者[EXIT WHEN 条件]的形式终止循环。否则该循环就是死循环。
案例5：执行1+2+3+…+100的值
代码演示：LOOP循环
|
DECLARE counter number(3):=0; sumResult number:=0; BEGIN LOOP counter := counter+1; sumResult := sumResult+counter; IF counter>=100 THEN ① EXIT; END IF; -- EXIT WHEN counter>=100; ② END LOOP; dbms_output.put_line('result is :'||to_char(sumResult)); END; |
代码解析：
1 LOOP循环中可以使用IF结构嵌套EXIT关键字退出循环
2 注释行，该行可以代替①中的循环结构，WHEN后面的条件成立时跳出循环。
WHILE循环
与C#中的while循环很类似。先判断条件，条件成立再执行循环体。
语法格式：WHILE
|
C#中while语法 |
PL/SQL中WHILE语法 |
|
while (条件){ //循环体体 } |
WHILE 条件 LOOP --循环体 END LOOP; |
表5 PL/SQL中LOOP语法
案例6：WHILE循环
代码演示：WHILE循环
|
DECLARE counter number(3):=0; sumResult number:=0; BEGIN WHILE counter<100 LOOP counter := counter+1; sumResult := sumResult+counter; END LOOP; dbms_output.put_line('result is :'||sumResult); END; |
FOR循环
FOR循环需要预先确定的循环次数，可通过给循环变量指定下限和上限来确定循环运行的次数，然后循环变量在每次循环中递增（或者递减）。FOR循环的语法是：
语法格式：FOR循环
|
FOR 循环变量 IN [REVERSE] 循环下限..循环上限 LOOP LOOP --循环体 END LOOP； |
语法解析：
循环变量：该变量的值每次循环根据上下限的REVERSE关键字进行加1或者减1。
REVERSE：指明循环从上限向下限依次循环。
案例7：FOR循环
代码演示：FOR循环
|
DECLARE counter number(3):=0; sumResult number:=0; BEGIN FOR counter IN 1..100 LOOP sumResult := sumResult+counter; END LOOP; dbms_output.put_line('result is :'||sumResult); END; |
顺序结构
在程序顺序结构中有两个特殊的语句。GOTO和NULL
GOTO语句
GOTO语句将无条件的跳转到标签指定的语句去执行。标签是用双尖括号括起来的标示符，在PL/SQL块中必须具有唯一的名称，标签后必须紧跟可执行语句或者PL/SQL块。GOTO不能跳转到IF语句、CASE语句、LOOP语句、或者子块中。
NULL语句
NULL语句什么都不做，只是将控制权转到下一行语句。NULL语句是可执行语句。NULL语句在IF或者其他语句语法要求至少需要一条可执行语句，但又不需要具体操作的地方。比如GOTO的目标地方不需要执行任何语句时。
案例8：GOGO 和 NULL
代码演示：GOTO和NULL
|
DECLARE sumsal emp.sal%TYPE; BEGIN SELECT SUM(sal) INTO sumsal FROM EMP; IF sumsal>20000 THEN GOTO first_label; ① ELSE GOTO second_label; ② END IF; <<first_label>> ③ dbms_output.put_line('ABOVE 20000:' || sumsal); <<second_label>> ④ NULL; END; |
代码解析：
1 跳转到程序first_label位置，就是②的位置，first_label是一个标签，用两个尖括号包含。
2 无条件跳转到sedond_label位置，就是④的位置。④处不执行任何内容，因此是一个NULL语句。
与C#一样，在PL/SQL中，各种循环之间可以相互嵌套。
PL/SQL中动态执行SQL语句
在PL/SQL程序开发中，可以使用DML语句和事务控制语句，但是还有很多语句（比如DDL语句）不能直接在PL/SQL中执行。这些语句可以使用动态SQL来实现。
PL/SQL块先编译然后再执行，动态SQL语句在编译时不能确定，只有在程序执行时把SQL语句作为字符串的形式由动态SQL命令来执行。在编译阶段SQL语句作为字符串存在，程序不会对字符串中的内容进行编译，在运行阶段再对字符串中的SQL语句进行编译和执行，动态SQL的语法是：
语法格式：动态SQL
|
EXECUTE IMMEDIATE 动态语句字符串 [INTO 变量列表] [USING 参数列表] |
语法解析：
如果动态语句是SELECT语句，可以把查询的结果保存到INTO后面的变量中。如果动态语句中存在参数，USING为语句中的参数传值。
动态SQL中的参数格式是：[:参数名]，参数在运行时需要使用USING传值。
案例9：动态SQL
代码演示：动态SQL
|
DECLARE sql_stmt VARCHAR2(200); --动态SQL语句 emp_id NUMBER(4) := 7566; salary NUMBER(7,2); dept_id NUMBER(2) := 90; dept_name VARCHAR2(14) := 'PERSONNEL'; location VARCHAR2(13) := 'DALLAS'; emp_rec emp%ROWTYPE; BEGIN --无子句的execute immediate EXECUTE IMMEDIATE 'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)'; ① ----using子句的execute immediate sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; ② ----into子句的execute immediate sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; ③ ----returning into子句的execute immediate sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2'; EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; ④ EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num' USING dept_id; ⑤ END; |
代码解析：
1 动态执行一个完整的SQL语句。
2 SQL语句中存在3个参数分别标识为：[:1、:2、:3]，因此需要用USING关键字对三个参数分别赋值。
3 对动态查询语句可以使用INTO子句把查询的结果保存到一个变量中，要求该结果只能是单行。
4 在Oracle的insert，update，delete语句都可以使用RETURNING子句把操作影响的行中的数据返回，对SQL语句中存在RETURNING子句时，在动态执行时可以使用RETURNING INTO来接收。
5 动态执行参数中可以是：[:数字]也可以是[:字符串]。
PL/SQL的异常处理
在程序运行时出现的错误，称为异常。发生异常后，语句将停止执行，PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。异常处理机制简化了代码中的错误检测。PL/SQL中任何异常出现时，每一个异常都对应一个异常码和异常信息。比如：
图1 PL/SQL中的异常
预定义异常
为了Oracle开发和维护的方便，在Oracle异常中，为常见的异常码定义了对应的异常名称，称为预定义异常，常见的预定义异常有：
|
异常名称 |
异常码 |
描述 |
|
DUP_VAL_ON_INDEX |
ORA-00001 |
试图向唯一索引列插入重复值 |
|
INVALID_CURSOR |
ORA-01001 |
试图进行非法游标操作。 |
|
INVALID_NUMBER |
ORA-01722 |
试图将字符串转换为数字 |
|
NO_DATA_FOUND |
ORA-01403 |
SELECT INTO语句中没有返回任何记录。 |
|
TOO_MANY_ROWS |
ORA-01422 |
SELECT INTO语句中返回多于1条记录。 |
|
ZERO_DIVIDE |
ORA-01476 |
试图用0作为除数。 |
|
CURSOR_ALREADY_OPEN |
ORA-06511 |
试图打开一个已经打开的游标 |
表6 PL/SQL中预定义异常
PL/SQL中用EXCEPTION关键字开始异常处理。具体语法是：
语法格式：异常处理
|
BEGIN --可执行部分 EXCEPTION -- 异常处理开始 WHEN 异常名1 THEN --对应异常处理 WHEN 异常名2 THEN --对应异常处理 …… WHEN OTHERS THEN --其他异常处理 END; |
语法解析：
异常发生时，进入异常处理部分，具体的异常与若干个WHEN子句中指明的异常名匹配，匹配成功就进入对应的异常处理部分，如果对应不成功，则进入OTHERS进行处理。
案例10 ：异常处理
代码演示：异常处理
|
SQL> DECLARE 2 newSal emp.sal % TYPE; 3 BEGIN 4 SELECT sal INTO newSal FROM emp; 5 EXCEPTION 6 WHEN TOO_MANY_ROWS THEN 7 dbms_output.put_line('返回的记录太多了'); 8 WHEN OTHERS THEN 9 dbms_output.put_line('未知异常'); 10 END; 11 / 返回的记录太多了 PL/SQL procedure successfully completed |
自定义异常。
除了预定义异常外，用户还可以在开发中自定义异常，自定义异常可以让用户采用与PL/SQL引擎处理错误相同的方式进行处理，用户自定义异常的两个关键点：
异常定义：在PL/SQL块的声明部分采用EXCEPTION关键字声明异常，定义方法与定义变量相同。比如声明一个myexception异常方法是：
myexception EXCEPTION；
异常引发：在程序可执行区域，使用RAISE关键字进行引发。比如引发myexception方法是：
RAISE myexception;
案例11：自定义异常
代码演示：自定义异常
|
SQL> DECLARE 2 sal emp.sal%TYPE; 3 myexp EXCEPTION; ① 4 BEGIN 5 SELECT sal INTO sal FROM emp WHERE ename='JAMES'; 6 IF sal<5000 THEN 7 RAISE myexp; ② 8 END IF; 9 EXCEPTION 10 WHEN NO_DATA_FOUND THEN 11 dbms_output.put_line('NO RECORDSET FIND!'); 12 WHEN MYEXP THEN ③ 13 dbms_output.put_line('SAL IS TO LESS!'); 14 END; 15 / SAL IS TO LESS! PL/SQL procedure successfully completed |
代码解析：
1 用EXCEPTION定义一个异常变量myexp
2 在一定条件下用RAISE引发异常myexp
3 在异常处理部分，捕获异常，如果不处理异常，该异常就抛给程序执行者。
引发应用程序异常
在Oracle开发中，遇到的系统异常都有对应的异常码，在应用系统开发中，用户自定义的异常也可以指定一个异常码和异常信息，Oracle系统为用户预留了自定义异常码，其范围介于-20000到-20999之间的负整数。引发应用程序异常的语法是：
RAISE_APPLICATION_ERROR(异常码，异常信息)
案例12：引发系统异常
代码演示：引发应用系统异常
|
SQL> DECLARE 2 sal emp.sal%TYPE; 3 myexp EXCEPTION; 4 BEGIN 5 SELECT sal INTO sal FROM emp WHERE ename='JAMES'; 6 IF sal<5000 THEN 7 RAISE myexp; 8 END IF; 9 EXCEPTION 10 WHEN NO_DATA_FOUND THEN 11 dbms_output.put_line('NO RECORDSET FIND!'); 12 WHEN MYEXP THEN 13 RAISE_APPLICATION_ERROR(-20001,'SAL IS TO LESS!'); ① 14 END; 15 / ORA-20001: SAL IS TO LESS! ② ORA-06512: 在 line 14 |
代码解析：
1 引发应用系统异常，指明异常码和异常信息。
2 在控制台上显示异常码和异常信息。
如果要处理未命名的内部异常，必须使用OTHERS异常处理器。也可以利用PRAGMA EXCEPTION_INIT把一个异常码与异常名绑定。
PRAGMA由编译器控制，PRAGMA在编译时处理，而不是在运行时处理。EXCEPTION_INIT告诉编译器将异常名与ORACLE错误码绑定起来，这样可以通过异常名引用任意的内部异常，并且可以通过异常名为异常编写适当的异常处理器。PRAGMA EXCEPTION_INIT的语法是：
PRAGMA EXCEPTION_INIT(异常名,异常码)
这里的异常码可以是用户自定义的异常码，也可以是Oracle系统的异常码。
案例13：PRAGMA EXCEPTION_INIT异常
代码演示：PRAGMA EXCEPTION_INIT异常
|
<<outterseg>> DECLARE null_salary EXCEPTION; PRAGMA EXCEPTION_INIT(null_salary, -20101); ① BEGIN <<innerStart>> ② DECLARE curr_comm NUMBER; BEGIN SELECT comm INTO curr_comm FROM emp WHERE empno = &empno; IF curr_comm IS NULL THEN RAISE_APPLICATION_ERROR(-20101, 'Salary is missing'); ③ ELSE dbms_output.put_line('有津贴'); END IF; END; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('没有发现行'); WHEN null_salary THEN dbms_output.put_line('津贴未知'); ④ WHEN OTHERS THEN dbms_output.put_line('未知异常'); END; |
代码解析：
1 把异常名称null_salary与异常码-20101关联，该语句由于是预编译语句，必须放在声明部分。也就是说-20101的异常名称就是null_salary。
2 嵌套PL/SQL语句块
3 在内部PL/SQL语句块中引发应用系统异常-20101。
4 在外部的PL/SQL语句块中就可以用异常名null_salary进行捕获。
本章总结
PL/SQL是一种块结构的语言，它将一组语句放在一个块中，一次性发送给服务器，PL/SQL引擎把接收到PL/SQL语句块中的内容进行分析，把其中的过程控制语句由PL/SQL引擎自身去执行，把PL/SQL语句块中的SQL语句交给服务器的SQL语句执行器执行。
PL/SQL的数据类型包括标量数据类型，引用数据类型和存储文本、图像、视频、声音等非结构化得大数据类型（LOB数据类型）等。
Oracle中存在两种属性类型：%TYPE和%ROWTYPE。
PL/SQL程序可通过控制结构来控制命令执行的流程。PL/SQL中提供三种程序结构：顺序结构、条件结构和循环结构。
在PL/SQL程序开发中，可以使用DML语句和事务控制语句，还可以动态执行SQL语句，动态执行SQL语句的命令是：EXECUTE IMMEDIATE。
在程序运行时出现的错误，称为异常。发生异常后，语句将停止执行，PL/SQL引擎立即将控制权转到PL/SQL块的异常处理部分。PL/SQL中任何异常出现时，每一个异常都对应一个异常码和异常信息。
本章练习
1. PL/SQL有哪些优点？
2. 请描述PL/SQL块的结构。
3. 请描述多分枝判断CASE的用法。
4. PL/SQL中有哪些循环控制语句？如何使用它们？
5. 如何执行动态SQL语句？
6. 如何自定义异常，如何把自定义异常与异常码绑定？
7. 编写一个程序，输入一个整数，使用循环结构将该数字左右翻转，输出翻转后的结果。
8. 编写一个程序，在EMP表查找姓名为ALLEN员工，并获取TOO_MANY_ROWS和NO_DATA_FOUND异常。
9. 编写一个过程为班级每位同学创建一个用户，用户名和密码都是：“班级号+学号”，并为每位用户授权：CONNECT和RESOURCE。
章节知识结构图
第6章
Oracle应用于.Net平台
|
|
主要内容 |
|
|
回顾ADO.NET 使用ADO.NET连接Oracle 抽象工厂加入Oracle |
1. 回顾ADO.NET
ADO.NET是一组用于和数据源进行交互的面向对象类库集，它存在于.Net Framework中。通常情况下，数据源可以是各种类型的数据库，利用ADO.NET可以访问目前几乎所有的主流数据库，如Oracle、SQL Server、DB2、Access等，但数据源同样也能够是文本文件、Excel文件或者XML文件，因此，ADO.NET可以访问的数据源是很多的。
ADO.NET由两部分组成：.Net数据提供程序和数据集。
.Net数据提供程序
ADO.NET提供了与常用的各种数据源进行交互的一些公共方法，但是对于不同的数据源由于它们各采用的协议是不一样的，所以会采用不同的类库，这些类库称为数据提供程序。主要的数据提供程序如表1所示：
|
数据提供程序 |
说明 |
对应的命名空间 |
|
SQL Server.NET |
访问SQL Server数据库的提供程序，由于是针对SQLServer数据源而设计，所以采用本提供程序访问SQL Server数据源时要比采用其它的提供程序，如OLE DB.NET访问SQL Server数据源，要快速得多 |
System.Data.SqlClient |
|
OLE DB.NET |
访问基于OLE DB协议构建的数据源 |
System.Data.OleDb |
|
Oracle.NET |
访问Oracle数据库 |
System.Data.OracleClient |
|
ODBC.NET |
访问基于ODBC协议构建的数据源 |
System.Data.Odbc |
表1 主要的.NET数据提供程序
ADO.NET主要负责与数据库服务器建立连接通道，并基于此连接通道实现从数据库中检索数据,并将内存中的数据回送到数据库以提交更新或将在内存中拼写好的SQL语句提交到数据库服务器并执行以实现某种数据操作等等。
数据集
数据集可以被看作是一个存储在内存中的离线式的数据库，它很像我们最终存储数据的物理数据库，它将很多物理数据库的机制搬到了内存中。
ADO.NET的主要部分已经在之前的章节介绍过了，例如：利用SQL Server.NET链接SQL Server数据库。本章将主要介绍利用ADO.NET连接Oracle数据库，实现数据的存取。ADO.NET的结构如图1所示。
图1 ADO.NET结构图
2. 使用ADO.NET连接Oracle
使用ADO.NET数据访问技术连接Oracle数据库和连接Sql Server数据库的步骤基本相同：
1. 使用Connection对象建立数据库连接。
2. 使用Command对象执行数据库操作。
3. 采用连线或者断线的方式进行数据的存取。
4. 使用Connection对象的Close方法关闭数据库连接。
连接Sql Server数据库和Oracle数据库所使用的数据提供程序是不一样的，连接Sql Server数据库使用的是SqlServer.NET，包括SqlConnection、SqlCommand等数据访问类。而连接Oracle数据库则使用的是Oracle.NET，包括OracleConnection、OracleCommand等数据访问类，包含在System.Data.OracleClient命名空间下，由于该命名空间默认并没有被添加到项目中来，所以在使用前需要如图2所示添加响应的引用才能使用。
图2 添加System.Data.OracleClient引用
案例1：在Oracle数据库中在System用户下，创建UserInfo表，并插入一定数据，使用ADO.NET数据访问技术将UserInfo表中的数据检索出来，并显示在ASP.NET页面中。UserInfo表中的数据如图3所示。
图2 UserInfo表中数据
实现步骤：
1. 使用OracleConnection对象建立与Oracle之间的连接。
代码演示：建立连接
|
//连接字符串 string connectionString = "Data Source=MYORCL;User ID=System;Password=accp;Unicode=True"; //创建Oracle连接对象 OracleConnection con = new OracleConnection(connectionString); //打开连接 con.Open(); |
2. 创建命令行对象，准备执行检索数据库操作。
代码演示：建立命令行对象
|
//建立Sql查询语句 string sql = "select * from userinfo"; //创建Oracle命令行对象 OracleCommand cmd = new OracleCommand(sql, con); //执行命令行对象 OracleDataReader odr = cmd.ExecuteReader(CommandBehavior.CloseConnection); |
3. 创建实体类，并遍历结果集，将数据存放到集合中存储。
代码演示：创建实体类
|
//实体类 public class UserInfo { int userID; public int UserID { get { return userID; } set { userID = value; } } string userName; public string UserName { get { return userName; } set { userName = value; } } string userAge; public string UserAge { get { return userAge; } set { userAge = value; } } } |
代码演示：遍历结果集
|
//遍历结果集 IList<UserInfo> users = new List<UserInfo>(); while (odr.Read()) { UserInfo user = new UserInfo(); user.UserID = Convert.ToInt32(odr["UserID"]); user.UserName = Convert.ToString(odr["UserName"]); user.UserAge = Convert.ToString(odr["UserAge"]); users.Add(user); } |
4. 将集合中的数据绑定到GridView控件上显示出来。
代码演示：绑定数据
|
//绑定DataGridView控件 this.GridView1.DataSource = users; this.GridView1.DataBind(); |
根据上面的操作，不难看出连接Oracle数据库与连接SqlServer数据库除了数据提供程序方面有所区别之外，从操作步骤和实现原理等方面看区别不大。按照上面步骤操作，在页面中运行的效果如图3所示。增删改操作和检索操作基本相似，在这里就不再叙述了。
图3 案例效果
3. 抽象工厂中加入Oracle
在设计模式中介绍过抽象工厂设计模式（Abstract Factory），抽象工厂有四种关键角色：抽象工厂、实体工厂、抽象产品、实体产品。抽象工厂模式实现原理强调的是对象组合机制，由在“父工厂”内定义不同的“子工厂”对象来负责不同的目标对象的创建，也就是说利用实体产品由抽象产品来约束，而由实体工厂来创建，实体工厂则由抽象工厂约束，可以有效的发挥工厂模式管理清晰的优点。
案例2：采用抽象工厂模式实现在学员信息管理系统中支持Access、SQLServer以及Oracle三套数据库的切换（以学员基本信息模块为例），以学员基本信息模块为例给出概要的实现，并实现展示所有学生信息功能。
案例分析：本例是抽象工厂课堂案例的延续，需要在项目中多添加一个Oracle数据库，实体产品是数据访问对象，三套数据库相当于有三套数据库访问对象，通过三个实体工厂管理三套数据库访问对象，最后使用抽象工厂管理三个实体工厂。
实现步骤：
1. 在Oracle数据库中使用Sql语句新建Infos表，并添加约束和表数据，如图3所示：
图3 Student表数据
2. 在VS2008中创建空白解决方案，命名为Test.sln。
3. 在解决方案中添加表示层，并添加StudentList.aspx页面。
4. 在解决方案中添加模型层，根据上面的表结构新建Student.cs实体类。
代码演示：Student类
|
public class Student { string stuID; public string StuID { get { return stuID; } set { stuID = value; } } /** * 其他成员… … */ } |
5. 在解决方案中添加数据访问层IDAL（抽象产品）。
代码演示：抽象产品
|
public interface IStudentService { //获取所有学生信息 IList<Student> GetAllStudents(); } |
6. 在解决方案中添加数据访问层DAL（实体产品），并利用文件夹将不同的实体产品分类，如图4所示。
图4 实体产品
代码演示：实体产品
|
/// <summary> /// 获得所有学生信息 /// </summary> /// <returns>所有学生信息集合</returns> public IList<Student> GetAllStudents() { //创建SQL语句 string sql = "select * from sys.infos"; //创建泛型集合 IList<Student> students = new List<Student>(); //执行SQL语句得到结果集 OracleDataReader odr = dbh.ExecuteReader(sql); //遍历结果集 while(odr.Read()) { Student student = new Student(); student.StuID = Convert.ToString(odr["StuID"]); student.StuName = Convert.ToString(odr["StuName"]); student.StuAddress = Convert.ToString(odr["StuAddress"]); student.Seat = Convert.ToInt32(odr["Seat"]); student.Gender = Convert.ToString(odr["Gender"]); student.EnRollDate = Convert.ToDateTime(odr["EnRollDate"]); student.ClassNo = Convert. ToString (odr["ClassNo"]); //添加到泛型集合 students.Add(student); } //返回 return students; } |
7. 在解决方案中添加业务逻辑层，命名为StudentManager.cs。
代码演示：BLL层
|
/// <summary> /// 获取所有学生信息 /// </summary> /// <returns>所有学生信息集合</returns> public IList<Student> GetAllStudents() { //利用抽象工厂创建实体工厂 Factory factory = Factory.CreateFactory(); //利用工厂创建产品 IStudentService iss = factory.GetStudentService(); return iss.GetAllStudents(); } |
8. 在解决方案中添加抽象工厂，并添加相应实体工厂，如图5所示。
图5 抽象工厂
代码演示：抽象工厂
|
//抽象工厂 public abstract class Factory { public static Factory CreateFactory() { //采用反射技术得到配置文件中的配置信息 string factoryType = Config.FactoryType; Factory factory = (Factory)System.Reflection.Assembly.Load("DBFactory").CreateInstance(factoryType); return factory; } //定义子类（实体工厂）的操作规则 public abstract IStudentService GetStudentService(); } |
9. 在表示层添加数据展示控件，通过设定属性绑定数据提取方法，实现案例目标。
4. 本章总结
ADO.NET是一组用于和数据源进行交互的面向对象类库集，它存在于.Net Framework中。通常情况下，数据源可以是各种类型的数据库，利用ADO.NET可以访问目前几乎所有的主流数据库，如Oracle、SQL Server、DB2、Access等。
使用ADO.NET技术访问数据库的大致步骤如下：
1. 使用Connection对象建立数据库连接。
2. 使用Command对象执行数据库操作。
3. 采用连线或者断线的方式进行数据的存取。
4. 使用Connection对象的Close方法关闭数据库连接。
抽象工厂有四种关键角色：抽象工厂、实体工厂、抽象产品、实体产品。
5. 本章练习
1. 利用本章所学知识，完成如下功能：
1) 在本章完成项目基础上，继续实现对Oracle数据库的增删改操作。
2) 在StudentList.aspx页面添加查看详细链接，编写GetStudentById等方法。
3) 在StudentList.aspx页面中添加删除链接，编写DeleteStudent等方法。
4) 在StudentList.aspx页面中添加修改链接，编写UpdateStudent等方法。
章节知识结构图
附录
数据库导入导出
|
|
主要内容 |
|
|
导出 导入 |
1. Oracle导入导出
Oracle的备份是Oracle操作中常见的工作，常见的备份方案有：逻辑备份（IMP&EXP命令进行备份）、物理文件备份（脱机及联机备份）、利用RMAN(Recovery Manager)的增量物理文件系统备份。ORACLE数据库的逻辑备份分为四种模式：表空间备份(tablespace)、表备份(table)、用户备份(user)和完全备份(full)。Oracle的逻辑备份是使用IMP&EXP命令进行数据导入导出的操作。使用EXP命令导出或者使用IMP命令导入时，需要Create Session系统权限，但是如果要导出其他的表，必须拥有权限：EXP_FULL_DATABASE。
调用导入导出命令时，首先要估计所需的空间。EXP命令导出的文件是二进制文件（*.dmp）只能由对应的IMP命令进行读取恢复。导入导出的用途是：
备份与恢复
Oracle平台更换：可以在相同版本之间进行备份与恢复，Oracle较低版本的export数据文件可以import到高版本的Oracle数据库中，但是Oracle的版本只能是相邻的，不能垮版本。
EXP导出数据
EXP命令可以在交互环境下导出数据库中的数据，也可以在非交互环境下执行命令。交互环境下的命令执行，是一步一步执行的过程。
代码演示：exp的交互环境
|
D:\>exp scott/tiger@my_orcl ① Export: Release 10.2.0.3.0 - Production on 星期一 10月 19 17:04:14 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options 输入数组提取缓冲区大小: 4096 > ② 导出文件: EXPDAT.DMP > scott.dmp ③ (2)U(用户), 或 (3)T(表): (2)U > 2 ④ 导出权限 (yes/no): yes > yes ⑤ 导出表数据 (yes/no): yes > yes ⑥ 压缩区 (yes/no): yes > no ⑦ 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 . 正在导出 pre-schema 过程对象和操作 . 正在导出用户 SCOTT 的外部函数库名 . 导出 PUBLIC 类型同义词 . 正在导出专用类型同义词 . 正在导出用户 SCOTT 的对象类型定义 即将导出 SCOTT 的对象... . 正在导出数据库链接 . 正在导出序号 . 正在导出簇定义 . 即将导出 SCOTT 的表通过常规路径... . . 正在导出表 BONUS导出了 0 行 . . 正在导出表 DEPT导出了 10 行 . . 正在导出表 EMP导出了 14 行 . . 正在导出表 SALGRADE导出了 5 行 . . 正在导出表 TBLSTUDENT导出了 3 行 . 正在导出同义词 . 正在导出视图 . 正在导出存储过程 . 正在导出运算符 . 正在导出引用完整性约束条件 . 正在导出触发器 . 正在导出索引类型 . 正在导出位图, 功能性索引和可扩展索引 . 正在导出后期表活动 . 正在导出实体化视图 . 正在导出快照日志 . 正在导出作业队列 . 正在导出刷新组和子组 . 正在导出维 . 正在导出 post-schema 过程对象和操作 . 正在导出统计信息 成功终止导出, 没有出现警告。 D:\> |
代码解析：
1 Exp是导出命令，该命令后面紧跟“用户名/密码@服务器网络连接”。
2 Exp程序导出时使用的缓冲区大小，缓冲区越大，导出速度越快。直接回车代表使用默认值4096B。
3 Exp命令会把所有要到处的数据导出到一个Dmp文件中，该步骤是Exp询问导出的数据文件名称。
4 Exp程序询问导出整个用户还是导出某个表。默认导出整个用户。
5 Exp程序询问是否导出每张表的访问权限。默认导出访问权限。
6 Exp程序询问是否导出表中的数据。默认导出数据库表中的数据。
7 Oracle表中的数据可能来自不同的分区中的数据块，默认导出时会把所有的数据压缩在一个数据块上，IMP导入时，如果不存在连续一个大数据块，则会导入失败。
也可以使用Exp命令时，设置各种参数，使准备就绪的Exp命令不需要与用户交互，按照参数的要求，Exp命令会一次性执行导出工作。要指定参数，您可以使用关键字:
EXP KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)
例如: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
|
参数名 |
说明 |
|
USERID |
表示“用户名/密码”。 |
|
BUFFER |
数据缓冲区大小。以字节为单位，一般在64000以上。 |
|
FILE |
指定输出文件的路径和文件名。一般以.dmp为后缀名，注意该文件包括完整路径，但是路径必须存在，导出命令不能自动创建路径。 |
|
COMPRESS |
是否压缩导出，默认yes。 |
|
GRANTS |
是否导出权限，默认yes |
|
INDEXES |
是否导出索引，默认yes |
|
DIRECT |
是否直接导出，默认情况，数据先经过Oracle的数据缓冲区，然后再导出数据。 |
|
LOG |
指定导出命令的日志所在的日志文件的位置。 |
|
ROWS |
是否导出数据行，默认导出所有数据。 |
|
CONSTRAINTS |
是否导出表的约束条件，默认yes |
|
PARFILE |
可以把各种参数配置为一个文本键值形式的文件，该参数可以指定参数文件的位置。 |
|
TRIGGERS |
是否导出触发器，默认值是yes。 |
|
TABLES |
表的名称列表，导出多个表可以使用逗号隔开。 |
|
TABLESPACES |
导出某一个表空间的数据。 |
|
Owner |
导出某一用户的数据。 |
|
Full |
导出数据库的所有数据。默认值是no。 |
|
QUERY |
把查询的结果导出。 |
表1 EXP参数说明
代码演示：exp的非交互环境
|
D:\>exp scott/tiger file=employee.dmp tables=(emp,dept) Export: Release 10.2.0.3.0 - Production on 星期一 10月 19 17:38:25 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 即将导出指定的表通过常规路径... . . 正在导出表 EMP导出了 14 行 . . 正在导出表 DEPT导出了 10 行 成功终止导出, 没有出现警告。 D:\> |
IMP导入
IMP程序导入就是把Exp导出的文件重新导入到数据库的过程。导入时也有一些重要的参数：
Fromuser:指出导出时dmp文件中记载的用户信息。
Touser:dmp文件要导入到什么目标用户中。
Commit:默认是N,在缓冲区满时是否需要commit,如果设为N,需要较大的回滚段。
Igore: Oracle在恢复数据的过程中，当恢复某个表时，该表已经存在，就要根据ignore参数的设置来决定如何操作。若ignore=y，Oracle不执行CREATE TABLE语句，直接将数据插入到表中，如果插入的记录违背了约束条件，比如主键约束，则出错的记录不会插入，但合法的记录会添加到表中。若ignore=n，Oracle不执行CREATE TABLE语句，同时也不会将数据插入到表中，而是忽略该表的错误，继续恢复下一个表。
代码演示：Imp导入
|
D:\>imp system/manager file=employee.dmp fromuser=scott touser=employee commit=y Import: Release 10.2.0.3.0 - Production on 星期一 10月 19 17:54:51 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options 经由常规路径由 EXPORT:V10.02.01 创建的导出文件 警告: 这些对象由 SCOTT 导出, 而不是当前用户 已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入 . 正在将 SCOTT 的对象导入到 EMPLOYEE . . 正在导入表 "EMP"导入了 14 行 . . 正在导入表 "DEPT"导入了 10 行 即将启用约束条件... 成功终止导入, 没有出现警告。 D:\> |
常见问题
数据库对象已经存在
一般情况, 导入数据前应该彻底删除目标数据下的表，序列，函数/过程,触发器等。
数据库对象已经存在, 按缺省的imp参数，则会导入失败。
如果用了参数ignore=y，会把exp文件内的数据内容导入。
如果表有唯一关键字的约束条件，不合条件将不被导入。
如果表没有唯一关键字的约束条件，将引起记录重复。
数据库对象有主外键约束
不符合主外键约束时，数据会导入失败。
解决办法: 先导入主表，再导入依存表。
disable目标导入对象的主外键约束，导入数据后，再enable它们。
权限不够
如果要把A用户的数据导入B用户下， A用户需要有imp_full_database权限。
导入大表( 大于80M ) 时，存储分配失败
默认的EXP时，compress = Y，也就是把所有的数据压缩在一个数据块上。
导入时，如果不存在连续一个大数据块，则会导入失败。
导出80M以上的大表时，记得compress= N，则不会引起这种错误。
Imp和Exp使用的字符集不同
如果字符集不同，导入会失败，可以改变unix环境变量或者NT注册表里NLS_LANG相关信息。
Imp和Exp版本不能往上兼容
Imp可以成功导入低版本Exp生成的文件, 不能导入高版本Exp生成的文件根据情况我们可以用。
- 点赞
- 收藏
- 关注作者
评论（0）