Oracle数据库

1、第1章 Oracle 11g基础入门31.1 Oracle 11g 简介31.2 Oracle 11g体系结构41.3 Oracle 11g 的安装81.4 Oracle 11g数据库实例的创建111.5 Oracle 11g实例的启动与关闭151.6 Oracle网络连接配置151.7 实践练习一281.8 实践练习二29第2章 SQL语言基础322.1 SQL语言概述322.2 使用DDL语句322.3 使用DML语句362.4 使用SELECT语句372.5 使用DCL数据控制语言432.6 使用TCL事务控制语言452.7 使用基本函数482.8 实践练习一542.9 实践练习二57第

2、3章 管理数据库对象623.1 同义词633.2 序列673.3 视图713.4 索引763.5 实践练习一843.6 实践练习二85第4章 PL/SQL语言基础894.1 PL/SQL语言简介904.2 程序结构904.3 流程控制984.4 异常处理1034.5 开发动态SQL1064.6 实践练习一1104.7 实践练习二112第5章 开发子程序和包1135.1 开发子程序1145.2 开发包1295.3 实践练习一1335.4 实践练习二135第6章 游标和触发器1366.1 游标1376.2 触发器1476.3 实践练习一1636.4 实践练习二167第7章 备份与恢复1697.1

3、启动和关闭数据库1707.2 Oracle数据库归档模式1767.3 备份与恢复概述1797.4 逻辑备份与恢复1797.5 数据库的脱机备份与恢复1867.6 数据库的联机备份与恢复191第1章 Oracle 11g基础入门1.1 Oracle 11g 简介 1.1.1 Oracle 数据库系统的特点Oracle数据库系统是美国Oracle公司（甲骨文）提供的以分布式数据库为核心的一组软件产品.Oracle数据库系统主要包含以下4个特点1) 支持大数据库、多用户、高性能的事务处理。2) 提供了基于角色（ROLE）分工的安全保密管理。3) 提供了新的分布式数据库能力。4) 具有可移植性、可兼容

4、性和可连接性。 1.1.2 Oracle发展历史1978年，Oracle 1诞生了，它是使用汇编语言开发的，但它的出现并没有引起太多的关注 1982年，Oracle公司推出了Oracle 3，这是第一个能够运行在大型和小型机上的关系型数据库 1997年，Oracle公司又推出了基于Java语言的Oracle 8 2001年6月，Oracle公司发布了Oracle 9i 2007年7月12日，甲骨文公司在美国纽约宣布推出Oracle 11g，Oracle 11g中的“g”代表“网格”。2013年7月8日，甲骨文中国宣布该公司新一代数据库Oracle 12c正式上市。Oracle 12c命名上的“

5、c明确了这是一款针对云计算(Cloud)而设计的数据库。网格计算可以将分布在世界各地的计算机连接在一起，并且将各地的计算机资源通过高速互联网组成充分共享的资源集成。通过合理的调度，不同的计算环境被综合利用并共享。 1.1.3 Oracle与SQL Server比较为什么在学习了SQL Server数据库之后还需要学习Oracle数据库？1) SQL Server只能在Windows上运行，没有丝毫的开放性；Oracle能在所有主流平台上运行（包括Windows）。2) SQL Server很难处理日益增多的用户数和数据卷；Oracle平行服务器通过使一组结点共享同一簇中的工作来扩展Window

6、 NT的能力，提供高可用性和高伸缩性的簇的解决方案。3) SQL Server没有获得任何安全证书，Oracle获得了最高认证级别的ISO标准认证 。4) SQL Server在多用户时性能不佳。Oracle中引入了共享SQL和多线索服务器体系结构，从而减少了Oracle的资源占用，并增强了Oracle的并发处理能力。1.2 Oracle 11g体系结构 1.2.1 进程结构1、 用户进程用户进程是在客户机内存上运行的程序，它向服务器进程发出数据处理请求。当客户端运行一个应用程序，准备向数据库服务器发送请求时，将会创建用户进程。2、 服务器进程为了给客户端的用户进程提供服务，Oracle会在客

7、户端成功连接Oracle数据库时，在服务器端创建相应的服务器进程。客户端的进程与服务器端的进程是一一对应的。用户进程必须通过服务器进程才能访问数据库。3、 后台进程在同一时刻，Oracle可以处理上百个并发的请求，进行复杂的数据操作，为了提高系统性能并协调多个用户，Oracle使用一些附加的线程，称为后台进程。后台进程存在于操作系统中，在Oracle实例启动时自动启动。服务器进程任务 1 解析并执行用户所提交的SQL语句。2 搜索SGA区的数据库缓存，决定是否读取数据文件，如果数据块不在SGA区的数据库缓存中，则将其从数据文件中读入。3 将查询或执行后形成的数据返回给用户。后台进程任务 1 在

8、内存和外存之间进行I/O操作2 监视各个进程的状态3 协调各个进程的任务4 维护系统的性能5 保证系统的可靠性能 1.2.2 内存结构SGA（System Global Area，系统全局区）。SGA是例程内存结构的主要组成部分，每个Oracle实例都只有一个SGA。当多个用户同时连接到一个例程时，所有的用户进程、服务器进程都共享SGA 。它是不同用户进程与服务器进程进行通信的中心 。可以将SGA看作是Oracle的心脏。当启动Oracle实例时，即分配一个SGA；当终止Oracle实例时，即释放SGA占用的内存。1) 数据缓冲区（Data Buffer Cache）用于存储最近从数据库中读取

9、出来的数据块 用户进程查看的数据首先驻留在数据缓冲区中，如果用户进程需要的信息不在该区内，才访问物理磁盘驱动器读取数据块，然后放入该区供其他用户进程或服务器进程使用 2) 重做日志缓冲区（Redo Log Buffer）当执行INSERT、UPDAE、DELETE等操作时，Oracle都会为这些操作生成重做记录，以便在可能出现的数据库恢复过程中用于回滚事务 但是为了加快访问速度和工作效率，重做记录并不直接写入重做日志文件中，而是首先存入SGA的重做日志缓冲区内，当重做日志缓冲区中的重做记录达到一定数量或到达某个时间点时，再分批写入重做日志文件中 3) SQL共享池（Shared SQL Poo

10、l） SQL共享池是程序的高速缓冲区，存放所有通过SQL语法分析并准备执行的SQL语句程序全局区（Program Global Area，PGA）1) 当用户进程连接到Oracle实例时，就会创建一个对应的会话，该会话使用一个服务器进程来完成客户端与Oracle实例之间的通信。2) 每个服务器进程都有一个自己的PGA。PGA由Oracle为服务器进程分配，专门作为当前用户会话的内存区。3) 这个内存区是非共享的，只有服务器进程本身才能访问它自己的PGA区。而SGA区则是所有服务器进程都能共享、可写的内存区。 1.2.3 逻辑结构数据块（Data Block）数据块是最小的数据管理单位，即Ora

11、cle中所有的I/O操作都以块为单位。数据块的大小是操作系统块大小的整数倍，常见大小为2KB或4KB 数据区间（Data Extent）数据区间由物理上连续存放的块所构成，它是Oracle存储分配的最小单位，一个或多个块组成一个数据区间 数据段（Data Segment）若干个数据区间构成数据段 表空间（Table Space）为了提高数据库服务器管理和运行的效率，Oracle 11g使用“表空间”这个虚拟的概念来管理逻辑对象，用户可以将不同性质的逻辑对象存放在不同的表空间下 在Windows操作系统中，使用文件夹来分类管理各种文件，表空间相当于Oracle数据库的文件夹。每个表空间由一个或多

12、个数据文件组成，一个数据文件只能与一个表空间相联系。Oracle数据库服务器上的默认表空间名称主要作用EXAMPLE实例表空间，存放实例信息SYSAUXSYSTEM表空间的辅助空间，用以减少SYSTEM表空间的负荷SYSTEM存储数据字典，包括表、视图、存储过程的定义等TEMP存储SQL语句处理的表和索引的信息UNDOTBS1回滚表空间，存放数据库恢复信息USERS用户表空间，存放用户私有信息 1.2.4 物理结构物理块 物理块是操作系统分配的基本存储单位，逻辑结构中的数据块由若干个物理块构成物理文件每个物理文件由苦干个物理块所组成，包括数据文件、控制文件和日志文件数据文件数据文件是实际存储插

13、入到数据库表中的实际数据的操作系统文件数据文件的扩展名为“*.dbf”重做日志文件Oracle使用重做日志文件在故障发生之后重现当时的数据库操作，便于数据库的恢复重做日志文件的扩展名为“*.log”控制文件控制文件为二进制文件，它存储了数据库的名称，数据文件和重做日志文件的名称、位置、联机/脱机状态和大小等信息只有控制文件正常，才能“装载”、“打开”数据库，控制文件的扩展名为“*.ctl” 1.2.5 总体结构Oracle实例Oracle实例是后台进程和内存结构的集合。作为Oracle数据库与应用程序的接口Oracle实例为应用程序提供对数据库中数据的管理和维护功能 Oracle数据库Orac

14、le数据库由操作系统文件组成，这些文件也称为数据库文件，为数据库信息提供实际的物理存储区 Oracle数据库最终由Oracle实例负责完成对数据库中全部数据的管理工作 Oracle服务器Oracle服务器一般指数据库各软件部件（如SQL*Plus）、Oracle实例及Oracle数据库3个主要部分，它由安装在服务器上的所有软件及启动成功后的实例组成 。数据库就像是一个仓库，存储了各类货物；而实例就像出入仓库的搬运工，负责将货物搬出、搬入仓库。1.3 Oracle 11g 的安装如果客户端需要远程访问某个Oracle数据库服务器，则客户端请求的远程Oracle数据库名称是全局数据库名还是SID?

15、按以上步骤成功安装Oracle 11g后，会在作为Oracle服务器的计算机中创建首个名为scce的Oracle数据库实例。在一台计算机中能否创建多个Oracle数据库实例？1.4 Oracle 11g数据库实例的创建新建一个数据库时，Oracle将默认创建几个用户 1) SYS用户SYS用户是Oracle中的超级用户，主要用于维护系统信息和管理实例，数据库中数据字典的所有表和视图都存储在SYS模式中。 2) SYSTEM用户SYSTEM用户是Oracle中默认的管理员，它拥有DBA权限。该用户拥有Oracle管理工具使用的内部表和视图，通常通过SYSTEM用户管理Oracle数据库的用户、权

16、限和存储等。 3) SCOTT用户SCOTT是Oracle数据库的一个示范账号。SCOTT用户模式包含4张示范表，SCOTT用户的默认口令为tiger。对于日常的管理任务，建议使用SYSTEM用户登录Oracle数据库服务器。如果需要执行备份、恢复、更改数据库的任务，就必须以SYS用户登录Oracle数据库服务器。1.5 Oracle 11g实例的启动与关闭Windows操作系统下的Oracle服务以后台服务进程的方式进行管理 OracleOraDb11g_homeTNSListener服务Oracle服务器的监听程序 OracleServiceSIDOracle的核心服务，要启动Oracle

17、实例，必须启动该服务 OracleJobSchedulerSID任务调度服务，负责对用户创建的作业按预先设置的时间周期性地进行调度执行，从而实现Oracle服务器的自动管理功能 OracleDBConsoleSID负责在Windows平台下启动Oracle企业管理器。Oracle 11g企业管理器是一个功能完善的Oracle数据库管理工具，可以管理本地数据库环境和网格环境1.6 Oracle网络连接配置 1.6.1 网络连接配置概述在Oracle产品安装完成后，客户端为了与数据库服务器连接实现数据访问，必须进行网络连接配置Oracle网络配置分为：服务器端配置和客户端配置。配置的结果由配置文件

18、来保存配置文件监听程序的配置文件（listener.ora）服务器端配置的目的就是配置该文件，该文件存储于服务器端。本地网格服务名的配置文件（tnsnames.ora）当采用本地命名方法时，就必须在客户端保存、配置tnsnames.ora文件，客户端配置的目的就是配置该文件。命名方法配置文件（sqlnet.ora）如果需要配置命名方法，则还要在客户端和服务器端配置一致的文件Oracle 11g默认采用本地命名方法。 1.6.2 连接描述符与网络服务名Oracle服务器使用连接描述符作为客户端访问数据库服务器的路径，即用于指定数据库的位置和数据库服务名等信息。网络服务名是数据库服务器在客户端的名

19、称，即逻辑名或者数据库的别名。它用于帮助客户端准确地连接到指定的Oracle数据库服务器。网络服务名被存储在客户端的监听程序配置文件“listener.ora”中。SCCE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hello)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = scce.wuhan) ) )注意：一个数据库可以有多个网络服务名。 1.6.3 监听程序监听程序是运行在服务器端的一个单独的服务进程，在Windows服务中对应于Oracle

20、OraDb11g_homeTNSListener服务。监听程序通过指定的协议监听端口号，监听网络上的客户端对服务器的连接请求，并管理客户端与服务器之间的网络通信量。每次客户端请求与服务器进行网络会话时，服务器的监听程序就会监听到该请求，并作为客户端请求的中介，再将请求传送给Oracle服务器。监听程序由存储在服务器端的监听程序配置文件“listener.ora”来集中控制。 1.6.4 Oracle网络连接服务器端配置OracleOraDb11g_homeTNSListener服务进程没有启动。输入不存在的的网络服务名 。 1.6.5 Oracle网络连接客户端配置 如果一台计算机上安装了Or

21、acle 11g DataBase软件，则该计算机既可以作为服务器，又可以作为客户端来连接另外一个Oracle数据库服务器。Oracle 11g安装程序会自动地创建一个默认的tnsnames.ora文件。如果一台计算机上只安装了Oracle 11g Client客户端软件，则该计算机只能作为客户端，不会产生默认的tnsnames.ora文件。如果客户端需要同时连接访问多个Oracle数据库服务器 ，该如何配置？ 1.6.6 远程连接Oracle常见问题在客户端远程连接Oracle数据库服务器过程中，经常会提示“连接超时”的错误信息，无法连接上服务器。此时服务器与客户端的配置却都没有任何错误。此

22、类问题产生的原因往往是Windows防火墙屏蔽了1521端口。 1.6.7 Oracle 11g管理工具SQL DeveloperOracle Enterprise Manager 1.6.8 卸载Oracle 11g停止所有的Oracle服务使用Oracle Universal Installer从产品清单中卸载所有的Oracle组件 手动删除Oracle遗留成分 从文件夹中删除Oracle遗留成分。直接删除安装Oracle产品主目录下的遗留的文件夹及文件夹 1.7 实践练习一 训练内容使用Net Manager工具配置Oracle网络连接。使用SCOTT示范账户连接远程Oracle数据库服

23、务器。 需求说明在局域网中，有一台Oracle数据库服务器，其主机名为“Lab1-21”，IP地址为“192.168.0.21”，Oracle数据库的SID为“scce”，全局数据库名为“scce.wuhan”。现要求在主机名“Labl1-18”的客户端配置网络连接，以远程连接到主机名为“Lab1-21”上的Oracle数据库服务器。 实现思路在主机名为“Lab1-21”的服务器端配置监听程序在主机名为“Lab1-21”的服务器端解除1521端口屏蔽在主机名为“Labl1-18”的客户端配置网络服务名在主机名为“Lab1-21”的服务器端，使用lsnrctl命令测试监听程序和Oracle实例是

24、否启动使用“net start”命令启动Oracle实例在主机名为“Labl1-18”的客户端使用tnsping命令，测试网络服务名配置是否正确在主机名为“Labl1-18”的客户端，使用SCOTT账户连接远程Oracle数据库服务器键入SQL语句“select empno,ename,job from emp;” 来查询示范表emp的内容 需求说明使用Oracle Enterprise Manager创建一个账户，用户名为“mstanford”，密码为“8888”，且该账户拥用查询SCOTT用户表dept的权限。 实现思路启动主机名为“Labl1-21”的服务器端上的OracleDBCons

25、oleSID服务 在主机名为“Labl1-21”的服务器端启动Oracle Enterprise Manager工具，并以SYSTEM账户登录，进入Oracle Enterprise Manager主页的“主目录”子页选择“服务器”“安全性”“用户”单击“用户”超链接进入 “用户”子页单击“创建”按钮进入 “创建用户”子页切换至 “对象权限”选项卡在主机名为“Labl1-18”的客户端的命令提示符中键入“sqlplus”后单击回车，键入“MSTANFORD/8888scce”连接到远程Oracle数据库服务器，键入SQL语句“select * from scott.dept;” 来查询SCOT

26、T用户的dept表的内容。 需求说明通过练习部分（一）中Oracle Enterprise Manager创建的mstanford用户来访问时，可以连接数据库，可以访问SCOTT用户的dept表，但其没有创建表、存储过程等权限。要求使用SQL*Plus工具以命令行的方式为mstanford用户赋予创建表、存储过程等权限。 实现思路以SYSTEM管理员账户登录，并通过GRANT命令为mstanford用户赋予RESOURCE角色以“mstanford”用户登录并创建表，测试mstanford用户所授予的权限。1.8 实践练习二 训练内容创建表空间使用SQL Developer工具 需求说明某公司

27、需要开发一个大型的ERP应用程序，此应用程序预计未来将会产生大量的数据。该ERP涉及200多张表，为了与其他建立在此数据库上的应用系统实现存储空间的独立，需要创建一个名为MSTANFORDTABLESPACE的表空间，该应用系统中的表都将存储在此表空间中。 实现思路启动“Oracle Enterprise Manager”应用程序，进入登录窗口，在此窗口中以“SYS”超级用户登录，在“连接身份”下拉框中选择“SYSDBA”，以管理员的角色身份创建表空间。选择“服务器”“存储”“表空间” 。单击“表空间”超链接后将进入表空间管理页面 。单击“创建”按钮，进入表空间创建页面。 在“数据文件”项中单

28、击“添加”按钮，添加一个数据文件，如单击“确定”按钮，完成表空间的创建。 启动“SQL Developer”应用程序，以“SCOTT”用户登录，进入主页面，在左侧的树型菜单中选择“Tables”，单击右键，选择“新建”命令，将弹出“创建表”窗口。“一般”选项卡中，在“名称”输入项中输入表的名称“Product”，在“表空间”下拉框选择上述创建的“MSTANFORDTABLESPACE”表空间。 需求说明数据字典是Oracle数据库的核心组件，它由一系列拥有数据库元数据信息的数据字典表和用户可以读取的数据字典视图组成。数据字典表中记录了数据库的系统信息（如方案对象的信息）、例程运行的性能信息（如

29、SGA信息）。对于数据库用户而言，数据字典中的数据字典视图类似于当前Oracle数据库的参考手册。通过使用select语句来查询数据字典视图，可以获得该数据库的相关信息在Oracle实例运行过程中，Oracle会在数据字典中维护一系列虚拟的表，在其中记录与数据库当前活动相关的实时性能统计信息，这些表被称为动态性能表。这些表在实例启动时创建，在实例关闭时销毁。“动态”是指在实例启动后，表中的内容会不断地被Oracle根据当前的性能状态实时更新要求通过user_objects视图查看自己所创建的所有方案对象的信息通过user_tables视图查看登录用户所创建的表信息通过“v$”开头的动态性能视图

30、查询当前系统Oracle运行的所有进程使用v$controlfile视图查看Oracle数据库服务器控制文件使用v$controlfile视图查看Oracle数据库服务器日志文件使用v$controlfile视图查看Oracle数据库服务器数据文件使用v$sga视图查看SGA区的大小信息 实现思路以SCOTT用户登录SQL*Plus，查询user_objects视图，获取其创建的所有方案对象信息以SCOTT用户登录SQL*Plus，查询user_tables视图，获取其创建的所有方案对象信息以SYSTEM用户登录SQL*Plus，并查看当前系统Oracle运行的所有进程 以SYSTEM用户登录

31、SQL*Plus，并查看Oracle数据库服务器控制文件以SYSTEM用户登录SQL*Plus，并查看Oracle数据库服务器日志文件以SYSTEM用户登录SQL*Plus，并查看Oracle数据库服务器数据文件以SYSTEM用户登录SQL*Plus，查看SGA的大小信息第2章 SQL语言基础2.1 SQL语言概述 2.1.1 SQL语言分类数据定义（Data Definition Language，DDL）用于创建、修改和删除数据库对象，如CREATE TABLE、ALTER TABLE、DROP TABLE等。DDL语句会自动提交事务 数据操纵语言（Data Manipulation La

32、nguage，DML）用于操纵数据库，包括INSERT、UPDATE、DELETE、SELECT等数据控制语言（Data Control Language，DCL）用于执行授予权限和撤销权限的操作，包括GRANT（授予权限）、REVOKE（撤销权限）两条命令。DCL语句会自动提交事务 事务控制语言（Transactional Control Language，TCL）用于维护数据的一致性，包括COMMIT（提交事务）、ROLLBACK（回滚事务）和SAVEPOINT（设置保存点）3条语句 2.1.2 SQL语句编写规则SQL关键字不区分大小写，既可以使用大写格式，又可使用小写格式，或者大小写混

33、用。对象名与列名不区分大小写。字符值与日期值区分大小写。 在SQL*Plus中，每条SQL语句要以分号结束。如果语句比较长，可以将语句文本分布到多行上，最后以分号结束。2.2 使用DDL语句 2.2.1 Oracle 11g常用的数据类型字符类型CHAR类型CHAR表示固定长度字符串，长度不够的用空格补充，最多可以存储2000字节CHAR类型区分中英文，中文在CHAR中占两个字节，而英文只占一个字节VARCHAR2类型VARCHAR2表示可变长度字符串，最多可以存储4000字节在定义该数据类型时，应该指定其大小。与CHAR类型相比，使用VARCHAR2可以节省磁盘空间数值类型NUMBER类型可

34、以存储正数、负数、零、定点数和精度为38位的浮点数 NUMBER(M,N)。其中，M表示精度，代表数字的总位数；N表示小数点右边数字的位数 日期类型DATE类型DATE数据类型用于存储表中的日期和时间数据，取值范围是公元前4712年1月1日至公元9999年12月31日DATE类型的长度是7，7个字节分别表示世纪、年、月、日、时、分和秒TIMESTAMP类型TIMESTAMP数据类型用于存储日期的年、月、日以及时间的小时、分和秒值其中，秒值精确到小数点后6位，该数据类型同时包含时区信息。大对象类型 CLOB大字符串对象类型 CLOB（Character Large Object）数据类型用于存储

35、可变长度的字符数据，最多可存储4GB数据。该数据类型用于存储VARCHAR2类型不能存储的长文本信息。BLOB大二进制类型。BLOB（Binary Large Object）数据类型用于存储较大的二进制对象，如图形、视频剪辑和声音剪辑等，最多可以存储4GB数据。 2.2.2 CREATE TABLE命令CREATE TABLE用于创建表。在创建表时，经常会创建该表的主键、外键、唯一约束、Check约束等。 2.2.3 ALTER TABLE 命令向已经创建的表中增加一个新列 alter table tableName add columnName dataType修改表中指定列的数据类型和类型

36、长度 alter table tableName modify columnName dataType删除表中指定的列 alter table tableName drop column columnName 2.2.4 TRUNCATE TABLE命令TRUNCATE TABLE命令用于删除表的所有内容，并释放表使用的存储空间，但不删除表结构语法truncate table tableNameTRUNCATE TABLE命令可以快速删除表的记录并释放空间，不使用事务处理，速度快且效率高，但无法回滚事务DELETE命令可以在执行删除之后通过ROLLBACK撤销删除 2.2.5 DROP TAB

37、LE 命令DROP TABLE命令用于从数据库中删除表及全部数据。2.3 使用DML语句 2.3.1 INSERT命令插入普通数据插入日期值 2.3.2 UPDATE命令 当日期列的数据为条件时，日期数据类型必须遵守默认日期格式。如果希望使用习惯的日期格式，则可以使用TO_DATE函数进行格式化。 2.3.3 DELETE 命令DELETE命令用于删除表的内容2.4 使用SELECT语句 2.4.1 示例数据库概述安装Oracle 11g创建数据库的环节中，如果在“指定数据库配置详细资料”窗口的“示例方案”选项卡中选择了“创建带样本方案的数据库”复选框，就会安装新的用户示例数据库 Oracle

38、的示例方案都基于一个虚拟的通过各种渠道销售物质的公司 其中，HR方案是基本的关系数据库方案，用于介绍最简单和最基本的话题 该方案中包含7张表：雇员（employees）、部门（departments）、地点（locations）、国家（countries）、地区（regions）、岗位（jobs）和工作履历（job_history） 2.4.2 HR示例方案简介默认情况下，所有示例方案的账户都已被锁定，它们的密码是过期的，不能连接到数据库解除HR用户的锁定状态。 2.4.3 HR示例方案中的表结构 2.4.4 子查询查询公司中工资最高的雇员信息 查询工资高于部门20中所有员工的雇员信息 查询负

39、责管理其他雇员的管理员信息 查询各部门中工资最低的雇员信息 2.4.5 在DDL、DML语句中使用子查询在CREATE TABLE语句中使用子查询 在INSERT语句中使用子查询 在DELETE语句中使用子查询在UPDATE语句中使用子查询 2.5 使用DCL数据控制语言 2.5.1 GRANT如果用户在自己的模式中创建了一张表，则该用户即为此表的所有者，拥有对这张表的所有操作权限 除非该用户希望与其他用户共享这张表，否则无须为其他用户赋予该表的任何操作权限 可以通过GRANT命令为其他用户授予该表的某些操作权限，这样的权限被称为对象权限 语法GRANT privileges ON objec

40、t-name TO userName如何为某个用户授予某张表中部分列的SELECT对象权限？为了授予列级的SELECT权限，应该创建一个包含所需列的视图，然后授予视图的SELECT权限？1. 授予SCOTT用户关于表emp的更新权限2. 授予视图SELECT权限3. 以HR用户登录，授予SCOTT用户关于表emp的SELECT权限4. 再次以SCOTT用户登录，并查询HR用户的emp表2.6 使用TCL事务控制语言 2.6.1 事务的开始与结束事务开始连接到数据库，并开始执行一条DML（UPDATE、INSERT、DELETE）语句前一个事务结束或者执行一条会自动提交事务的语句事务结束执行一条

41、COMMIT（事务提交）或ROLBACK（事务回滚）语句执行一条会自动提交事务的语句执行一条DML语句却失败了自动提交事务执行一条DDL语句（如CREATE TABLE、ALTER TABLE等）执行一条DCL语句（如GRANT、REVOKE等）断开与数据库的连接。例如，在SQL*PLUS中执行一条DISCONNECT命令，或在同一个SQL*PLUS中执行一条CONNECT命令启动一个会话，或者执行EXIT命令退出SQL*PLUS等 2.6.2 使用COMMIT命令提交事务会话A向emp表中插入一条记录，在提交之前，另一个会话B无法查看到新的记录。只有在会话A提交事务之后，会话B才可以查看新增

42、加的记录，这实际上就是事务的隔离性。会话A向emp表中插入一条记录，并未提交。会话B查看emp表中的记录 会话A提交事务 会话B再次查看emp表中的数据 2.6.3 使用ROLLBACK回滚事务 2.6.4 回滚部分事务2.7 使用基本函数dual表dual表是Oracle数据库中的一个虚表，它有一行一列，所有者是SYS用户，但可以被数据库中的所有用户使用不能向该表插入数据，但可以使用该表来选择系统变量，或者求一个表达式的值单行函数日期函数ADD_MONTHS函数：用于返回指定的日期加上指定的月数后的日期值MONTHS_BETWEEN函数：用于返回两个日期之间的月份数LAST_DAY函数：用于

43、返回指定日期对应月份的最后一天NEXT_DAY函数：用于返回指定下一个星期几的日期EXTRACT函数：用于提取日期中的特定部分字符函数函数名功能示例输出结果LOWER（c）小写转换selectlower(SccE)fromdual;scceUPPER(c)大写转换selectupper(scce)fromdual;SCCELTRIM（c1,c2）去掉c1左边所包含的c2中的任何字符。当遇到不是c2中的字符时结束，并返回剩余字符串。若c2省略，则默认为空格selectltrim(adminscce,ad)fromdual;minscceRTRIM（c1,c2）去掉c1右边所包含的c2中的任何字符

44、。当遇到不是c2中的字符时结束，并返回剩余字符串。若c2省略，则默认为空格selectrtrim(adminscce,scce)fromdual;adminREPLACE(c1,c2,c3)将c1中出现的c2都替换成c3，并返回剩余的字符串。c3默认为null。如果c3为null，则所有出现c2的地方的字符都被删除；如果c2为null，则返回c1；如果c1为null，则返回nullselectreplace(a*b*,*)fromdual;abINSTR(c1,c2)在c1中搜索c2首次出现的位置，如果找不到则返回零selectinstr(abcdbc,bc)fromdual;2SUBSTR(

45、c,m,n)返回c的子串，其中m是子串的开始位置，n是子串的长度。如果省略n，则表示截取从m位置开始的所有子串selectsubstr(scce,2,2)fromdual;ccCONCAT(c1,c2)连接字符串selectconcat(scc,e)fromdual;scceLENGTH(c)返回字符串长度selectlength(scce)fromdual;4数学函数函数名功能示例结果ABS取绝对值selectabs(-1)fromdual;1CEIL向上取整selectceil(1.33)fromdual;2FLOOR向下取整selectfloor(1.33)fromdual;1POWER

46、求幂selectpower(2,3)fromdual;8MOD取余selectmod(10,3)fromdual;1ROUND四舍五入selectround(12.126,2)fromdual;12.13TRUNC截断selecttrunc(12.126,2)fromdual;12,12转换函数TO_CHAR：用于将日期或数字以指定的格式转换为VARCHAR2数据类型的值语法TO_CHAR(d|n ,fmt)d表示日期，n表示数字，fmt是指定日期或数字的格式TO_DATE：将字符类型转换为日期数据类型语法TO_DATE(char ,fmt)fmt指定需要转换的日期格式 TO_NUMBER：将

47、包含数字的字符串转换为NUMBER数据类型，从而可以对该数据类型执行算术运算。语法TO_NUMBER(char)通常不需要进行转换，因为Oracle可以对数字字符串进行隐式转换其他函数NVL：用于将空值替换为指定值语法NVL(expr1,expr2)如果expr1不是NULL，则返回expr1；否则返回expr2向HR方案中的employees表中添加一个关于奖金的字段bonus，该字段充许为空。现要求计算雇员的实发工资，实发工资=基本工资+奖金NVL2：与NVL类似，只是参数有所不同语法NVL2(expr1,expr2,expr3)如果expr1不是NULL，则返回expr2的值；否则返回e

48、xpr3的值DECODE：DECODE函数相当于一条件语句（IF），它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值语法DECODE(input_value,value,result,value,result,default_result);input_value表示试图处理的数值。DECODE函数将该数值与一系列的序偶相比较，决定最后的返回结果value表示一组成序偶的数值。如果输入数值与之匹配成功，相应的结果将被返回。result表示一组成序偶的结果值。default_result表示未能与任何一序偶匹配成功时函数返回的默认值。为employees表中的雇员按表需求增加工资，

49、并确认更新结果。2.8 实践练习一训练内容Oracle分析函数之over窗口函数需求说明over窗口函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。存在用于记录某商品的日营业额表，要求根据此表按天累加统计营业额，即后一天在前一天的基础上进行累加 实现思路创建一张用于记录商店日营业额表，命名为tb_sale，并插入测试数据使用over窗口函数实现功能。需求说明根据雇员销售记录表（编号、雇员编号、商品编号、销售量和年份），要求使用over窗口函数，统计各个雇员每个商品的销售总量及该商品总的销售量。实现思路创建完成功能所需要的表。使用ove

50、r函数完成上述功能。2.9 实践练习二训练内容Oracle分析函数之rank函数需求说明现有一张记录学生成绩的表，要求统计出学生的总分及排名实现思路创建一张用于记录学生成绩的表 使用Rank完成上述功能需求说明使用SCOTT中的dept表和emp表，查询各个雇员的雇员编号、姓名、工资、所属部门名称，以及各部门的工资排名情况。实现思路考虑到需求中需要进行排名，所以可以选用rank函数来处理。但此时排名不是以所有雇员的工资为标准，而是以部门内雇员工资为标准，所以可以设置rank函数的可选项“partition by column”为部门编号列，即以该雇员工所在部门的雇员信息为参考依据，计算雇员的名

51、次。需求说明要求对上述雇员销售记录表进行分组显示，计算各雇员各商品的销售总额，要求具有汇总小计功能。实现思路由于需要统计出各雇员各商品的销售总额，且要求具有汇总小计，所以可考虑对empno和shopid进行分组，结合rollup函数来实现汇总小计功能。需求说明要求对上述雇员销售记录表进行分组显示，计算各雇员各商品的销售总额，要求具有汇总小计功能，并使用lag函数去掉重复的empno。实现思路由于需要统计出各雇员各商品的销售总额，并且要求有汇总小计，所以可以考虑对empno和shopid进行分组，并结合rollup函数实现汇总小计功能。又由于需要去掉重复的empno，所以可考虑使用lag函数实现

52、。需求说明要求根据SCOTT用户中的emp表，查询在部门编号为20或30的部门中工作的雇员，并同时显示各个雇员所在部门拥有最高工资的雇员姓名。实现思路由于first_value函数能够自动获取指定集合中排在第一位的值，所以可以以部门编号为计算区间、以雇员名为显示值来进行查询。第3章 管理数据库对象3.1 同义词 3.1.1 方案方案是数据库对象的集合，为了区分各个集合，需要给集合命名，其名称即为方案名。 方案中包含各种对象，如表、视图、索引、同义词、序列、数据库链接等对象。 一般情况下，一个用户对应一个方案，在创建用户的同时为该用户创建一个与用户名同名的方案，并作为该用户的缺省方案。 如果访问

53、一个表时没有指明该表属于哪一个用户方案，则系统将会自动地在访问表前加上缺省的方案名。 数据库中一个对象的完整名称为“方案名.对象名”，而不是“用户名.对象名”。用户所持有的是系统的权限及资源，而方案所涵盖的是各种对象，包含了表、视图、序列等对象的“所在地”，并不包括对他们的权限控制。类似于一个房子（方案），里面放满了家具（方案对象），对这些家具有支配权的是房子的主人（用户），而不是房子（方案）。 3.1.2 同义词概述同义词是方案对象的一个别名，常用于简化对象和提高对象访问的安全性。同义词并不占用实际存储空间，只在数据库字典中保存同义词的定义。可以创建同义词的对象主要包括表、视图、同义词、序列、存储过程等对象。Oracle中可以创建两种类型的同义词：公用同义词和方案同义词。在开发数据库应用程序时，应当普遍遵守的规则是：尽量避免直接引用表、视图