ACCP V4.0第一章第一章Oracle入门ACCP V4.0课程地位.Net&C#SQLServerXMLSPMASP.NET&WebServiceOracleUMLSPR:ComputerBaseHTML&JavaScriptSQLServerBaseOOP&JavaBaseCSTBJSP/ServletEJB&WebServiceWinFormsStruts&JSFTesting&SQALinux2ACCP V4.0课程目标q理解Oracle数据库体系结构q熟练掌握SQL命令和SQL函数q使用Oracle的各种数据库对象q使用PL/SQL编写存储过程q掌握基本的数据库配置和管理3ACCP V4.0目标目标q了解Oracle体系结构的各种组件q掌握Oracle中的基本用户管理q了解Oracle的工具q了解Oracle的安装和卸载4ACCP V4.0Oracle数据库简介2-1q对象关系型的数据库管理系统(ORDBMS)q在管理信息系统、企业数据处理、因特网及电子商务等领域使用非常广泛q在数据安全性与数据完整性控制方面性能优越q跨操作系统、跨硬件平台的数据互操作能力qOracle数据库的主要特点q支持多用户、大事务量的事务处理q数据安全性和完整性控制q支持分布式数据处理q可移植性5ACCP V4.0Oracle数据库简介2-2qOracle数据库基于客户端/服务器技术客户端应用程序通过向服务器请求并接收信息的方式与数据库进行交互。它充当用户与数据库之间的接口请求 响应数据库服务器对数据库表进行最佳管理，处理多个客户端对同一数据的并发访问。全面地保持数据完整性，并控制数据库访问权限等安全性需求网络服务器服务器6ACCP V4.0Oracle9i体系结构Oracle服务器Oracle数据库Oracle实例Oracle数据库是一个数据的集合，该集合被视为一个逻辑单元管理数据库的后台进程和内存结构的集合称为Oracle实例q数据库的体系结构是指数据库的组成、工作过程与原理，以及数据在数据库中的组织与管理机制qOracle服务器提供开放、全面和综合的信息管理，它由Oracle数据库和Oracle实例组成7ACCP V4.0Oracle主要组件实例 内存结构内存结构 后台进程后台进程 PMON SMON DBWR LGWR CKPT 其他 数据库数据库数据文件数据文件数据文件控制文件控制文件日志文件日志文件参数文件归档日志文件归档日志文件口令文件SGA用户进程服务器进程PGA共享池数据缓冲区日志缓冲区8ACCP V4.0Oracle实例qOracle实例是后台进程和内存结构的集合Oracle实例系统全局区后台进程分配 启动 9ACCP V4.0Oracle数据库qOracle数据库由操作系统文件组成，这些文件为数据库信息提供实际物理存储区qOracle数据库包括逻辑结构和物理结构物理结构逻辑结构物理结构包含数据库中的一组操作系统文件。逻辑结构指数据库创建之后形成的逻辑概念之间的关系Oracle 数据库数据库 10ACCP V4.0会话q会话是用户与Oracle服务器的单个连接q当用户与服务器建立连接时创建会话q当用户与服务器断开连接时关闭会话启动Oracle实例使用SQL*Plus连接至数据库创建用户进程创建服务器进程提交SQL查询11ACCP V4.0Oracle实例内存结构系统全局区系统全局区 (SGA) 程序全局区程序全局区 (PGA) Oracle实例启动时分配系统全局区当服务器进程启动时分配程序全局区qOracle的内存结构包含以下两个内存区：内存区内存区 12ACCP V4.0系统全局区2-1q数据库信息存储于SGA，由多个数据库进程共享共享池共享池 数据缓冲区数据缓冲区 日志缓冲区日志缓冲区 SGA的内存结构的内存结构 13ACCP V4.0系统全局区2-2共享池 q共享池是对SQL、PL/SQL程序进行语法分析、编译、执行的内存区域q共享池由库缓存和数据字典缓存组成。q共享池的大小直接影响数据库的性能。数据缓冲区 q用于存储从磁盘数据文件中读入的数据，所有用户共享。q服务器进程将读入的数据保存在数据缓冲区中，当后续的请求需要这些数据时可以在内存中找到，不需要再从磁盘读取，提高了读取速度。q数据缓冲区的大小对数据库的读取速度有直接的影响。日志缓冲区 q日志记录数据库的所有修改信息，日志信息首先产生于日志缓冲区。q当日志缓冲区的日志数据达到一定数量时，由后台进程将日志数据写入日志文件中。q相对来说，日志缓冲区对数据库的性能影响较小。14ACCP V4.0程序全局区q程序全局区（PGA）包含单个服务器进程所需的数据和控制信息qPGA是在用户进程连接到数据库并创建一个会话时自动分配的，保存每个与Oracle数据库连接的用户进程所需的信息qPGA为非共享区，只能单个进程使用，当一个用户会话结束，PGA释放15ACCP V4.0Oracle实例进程结构qOracle实例有几种不同类型的进程，它们是：实例进程实例进程用户进程服务器进程后台进程用户进程是一个需要与Oracle服务器进行交互的程序。当用户运行一个应用程序准备向数据库服务器发送请求时，即创建了用户进程服务器进程用于处理连接到该实例的用户进程的请求。当用户连接至Oracle数据库实例创建会话时，即产生服务器进程后台进程是Oracle数据库为了保持最佳系统性能和协调多个用户请求而设置的。Oracle实例启动时即创建一系列后台进程16ACCP V4.0后台进程2-1后台进程PMONq实例的各种后台进程是：SMONDBWRLGWRCKPT其他17ACCP V4.0后台进程2-2PMON 进程监控进程进程监控进程q清理出现故障的进程。q释放所有当前挂起的锁定。q释放故障进程使用的资源。SMON 系统监控进程系统监控进程q在实例失败之后，重新打开数据库时自动恢复实例。q整理数据文件的自由空间，将相邻区域结合起来。q释放不再使用的临时段。DBWR 数据写入进程数据写入进程q管理数据缓冲区，将最近使用过的块保留在内存中。q将修改后的缓冲区数据写入数据文件中。LGWR 日志写入进程日志写入进程q负责将日志缓冲区中的日志数据写入日志文件。q系统有多个日志文件，该进程以循环的方式将数据写入文件。18ACCP V4.0Oracle物理组件q物理组件就是Oracle数据库所使用的操作系统物理文件。物理文件可分为三类：物理组件数据文件控制文件日志文件数据文件用于存储数据库数据，如表、索引数据等。控制文件是记录数据库物理结构的二进制文件。日志文件记录对数据库的所有修改信息，用于故障恢复19ACCP V4.0Oracle逻辑组件3-1q数据库的逻辑结构是从逻辑的角度分析数据库的组成。Oracle的逻辑组件包括：数据库表空间段区数据块模式20ACCP V4.0Oracle逻辑组件3-2q表空间是数据库中最大的逻辑单位，一个Oracle数据库至少包含一个表空间，就是名为SYSTEM的系统表空间。q每个表空间是由一个或多个数据文件组成的，一个数据文件只能与一个表空间相关联。q表空间的大小等于构成该表空间的所有数据文件大小之和。CREATE TABLESPACE tablespacenameDATAFILE filename SIZE integer K|M AUTOEXTEND OFF|ON;q创建表空间的语法是：21ACCP V4.0Oracle逻辑组件3-3段段q段是构成表空间的逻辑存储结构，段由一组区组成。q按照段所存储数据的特征，将段分为四种类型，即数据段、索引段、回退段和临时段。区区q区为段分配空间，它由连续的数据块组成。q当段中的所有空间已完全使用时，系统自动为该段分配一个新区。q区不能跨数据文件存在，只能存在于一个数据文件中。数据块数据块q数据块是Oracle服务器所能分配、读取或写入的最小存储单元。qOracle服务器以数据块为单位管理数据文件的存储空间。模式模式q模式是对用户所创建的数据库对象的总称。q模式对象包括表、视图、索引、同义词、序列、过程和程序包等。22ACCP V4.0Oracle网络配置2-1qOracle产品安装完成后，服务器和客户端都需要进行网络配置才能实现网络连接。q服务器端配置监听器，客户端配置网络服务名。tnsnames.oraOracle 客户端客户端listener.oraOracle 服务器服务器23ACCP V4.0Oracle网络配置2-2q服务器端监听器配置信息包括监听协议、地址及其他相关信息。配置信息保存在名为listener.ora的文件中。在安装服务器软件时自动配置一个监听器q客户端的网络服务名配置信息包括服务器地址、监听端口号和数据库SID等，与服务器的监听器建立连接。配置信息保存在名为tnsnames.ora的文件中qOracle中的NetConfigurationAssistant和NetManager工具都能用来配置监听器和网络服务名24ACCP V4.0Oracle查询工具Oracle 查询工具查询工具SQL*PlusiSQL*PlusPL/SQLqOracle提供的工具非常容易使用。Oracle的查询工具包括：SQL*Plus是Oracle最常用的工具之一，用于接受和执行SQL命令以及PL/SQL块。iSQL*Plus可以执行能用SQL*Plus完成的所有任务。该工具的优势在于能通过浏览器访问它。PL/SQL是SQL的扩展。PL/SQL结合了SQL语言的数据操纵能力和过程语言的流程控制能力25ACCP V4.0Oracle企业管理器qOracle企业管理器为管理员提供了图形化的用户界面，并集成了管理数据库的工具26ACCP V4.0Oracle默认用户q只有用合法的用户帐号才能访问Oracle数据库qOracle有几个默认的数据库用户Oracle默认用户SYSSYSTEMSCOTT数据库中所有数据字典表和视图都存储在SYS模式中。SYS用户主要用来维护系统信息和管理实例。SYSTEM是默认的系统管理员，该用户拥有Oracle管理工具使用的内部表和视图。通常通过SYSTEM用户管理数据库用户、权限和存储等SCOTT用户是Oracle数据库的一个示范帐户，在数据库安装时创建27ACCP V4.0创建新用户q要连接到Oracle数据库，就需要创建一个用户帐户q每个用户都有一个默认表空间和一个临时表空间qCREATEUSER命令用于创建新用户CREATE USER usernameIDENTIFIED BY passwordDEFAULT TABLESPACE tablespaceTEMPORARY TABLESPACE tablespace;qCREATEUSER命令的语法是：CREATEUSERMARTINIDENTIFIEDBYmartinpwdDEFAULTTABLESPACEUSERSTEMPORARYTABLESPACETEMP;创建一个名称为martin的用户，其密码为martinpwd28ACCP V4.0授予权限3-1q权限指的是执行特定命令或访问数据库对象的权利q权限有两种类型，系统权限和对象权限q系统权限允许用户执行某些数据库操作，如创建表就是一个系统权限q对象权限允许用户对数据库对象（如表、视图、序列等）执行特定操作q角色是一组相关权限的组合，可以将权限授予角色，再把角色授予用户，以简化权限管理。29ACCP V4.0授予权限3-2qGRANT命令可用于为用户分配权限或角色GRANTCONNECTTOMARTIN;CONNECT角色允许用户连接至数据库，并创建数据库对象GRANTRESOURCETOMARTIN;RESOURCE角色允许用户使用数据库中的存储空间GRANTCREATESEQUENCETOMARTIN;此系统权限允许用户在当前模式中创建序列，此权限包含在CONNECT角色中30ACCP V4.0授予权限3-3q授予用户MARTIN操作TEST表对象的权限GRANTSELECTONTESTTOMARTIN;允许用户查询TEST表的记录GRANTUPDATEONTESTTOMARTIN;允许用户更新TEST表中的记录GRANTALLONTESTTOMARTIN;允许用户插入、删除、更新和查询TEST表中的记录31ACCP V4.0更改和删除用户qALTERUSER命令可用于更改口令ALTERUSERMARTINIDENTIFIEDBYmartinpass;修改MARTIN用户的密码qDROPUSER命令用于删除用户DROPUSERMARTINCASCADE;删除MARTIN用户模式32ACCP V4.0Oracle9i的安装和卸载q通过运行OracleUniversalInstaller来安装和卸载Oracle数据库选择Oracle9iDatabase9.2.0.1.0来安装数据库服务器。选择Oracle9iClient9.2.0.1.0来安装Oracle客户端启动UniversalInstaller，选择“卸装产品”，选中要卸载的组件33ACCP V4.0使用Oracle数据库的开发流程服务器端安装Oracle服务器软件创建数据库(安装时自动创建)配置监听器(安装时自动配置)启动Oracle实例(自动启动服务)安装Oracle客户端软件配置网络服务名以新用户登录Oracle提交SQL查询创建新用户并授权创建用户表空间客户端34ACCP V4.0Windows中的Oracle服务3-1qOracle9i的每个实例在Windows中都作为一项服务启动q服务是在Windows注册表中注册的可执行进程，由Windows操作系统管理q“服务”对话框中的各项Oracle服务如图所示：35ACCP V4.0Windows中的Oracle服务3-2OracleHOME_NAMETNSListenerq该服务启动数据库服务器的监听器，监听器接受来自客户端应用程序的连接请求q若监听器未启动，则客户端将无法连接到数据库服务器OracleServiceSIDq该服务启动系统标识符为SID的数据库实例，其中SID是在安装Oracle9i时输入的数据库名称OracleHOME_NAMEAgent qOracle企业管理器组件智能代理服务，此智能代理用于执行作业和监控Oracle服务或对象的性能q在使用Oracle管理服务器网络的环境中必须启动此服务36ACCP V4.0Windows中的Oracle服务3-3OracleHOME_NAMEHTTPServerq该服务用于启动OracleHTTP服务器，它提供文档信息、基于Web的企业管理器和iSQL*Plus等服务OracleHOME_NAMEManagementServerq该服务启动Oracle管理服务器，它用于维护管理控制台对各个被管理服务器节点之间的集中的、智能的和分布式的控制q该服务必须经过企业管理器配置向导配置完成后才创建37ACCP V4.0总结qOracle服务器由Oracle数据库和Oracle实例组成qOracle实例由系统全局区内存结构和用于管理数据库的后台进程组成qOracle中用于访问数据库的主要查询工具有SQL*Plus、iSQL*Plus和PL/SQLqOracle企业管理器是用于管理、诊断和调整多个数据库的工具qOracle中的SYSTEM用户和SYS用户具有管理权限，而SCOTT用户只有基本的权限qOracle服务在Windows注册表中注册，并由Windows操作系统管理38ACCP V4.0第二章第二章SQL查询和SQL函数ACCP V4.0qOracle服务器由Oracle数据库和Oracle实例组成qOracle实例由系统全局区内存结构和用于管理数据库的后台进程组成qOracle中用于访问数据库的主要查询工具有SQL*Plus、iSQL*Plus和PL/SQLqOracle企业管理器是用于管理、诊断和调整多个数据库的工具qOracle中的SYSTEM用户和SYS用户具有管理权限，而SCOTT用户只有基本的权限回顾ACCP V4.0目标q了解Oracle数据类型q了解数据定义语言和数据操纵语言q了解事务控制语言和数据控制语言q掌握SQL操作符和SQL函数41ACCP V4.0SQL简介2-1qSQL是StructuredQueryLanguage（结构化查询语言）的首字母缩写词qSQL是数据库语言，Oracle使用该语言存储和检索信息q表是主要的数据库对象，用于存储数据q通过SQL可以实现与Oracle服务器的通信SELECTenameFROMEmp;发送SQL查询Oracle 服务器enameBLAKESMITHALLENDAVIDMARTIN发送命令输出到用户端用户42ACCP V4.0SQL简介2-2qSQL支持下列类别的命令：q数据定义语言（DDL）q数据操纵语言（DML）q事务控制语言（TCL）q数据控制语言（DCL）数据定义语言数据定义语言 CREATEALTERDROP数据操纵语言数据操纵语言 INSERTSELECTDELETEUPDATE事务控制语言事务控制语言COMMITSAVEPOINTROLLBACK数据控制语言数据控制语言 GRANTREVOKE43ACCP V4.0Oracle数据类型5-1q创建表时，必须为各个列指定数据类型q以下是Oracle数据类型的类别：数据类型数据类型字符数值日期时间RAW/LONGRAWLOB44ACCP V4.0Oracle数据类型5-2字符数据类型字符数据类型CHARVARCHAR2LONGq当需要固定长度的字符串时，使用CHAR数据类型。qCHAR数据类型存储字母数字值。qCHAR数据类型的列长度可以是1到2000个字节。qVARCHAR2数据类型支持可变长度字符串qVARCHAR2数据类型存储字母数字值qVARCHAR2数据类型的大小在1至4000个字节范围内qLONG数据类型存储可变长度字符数据qLONG数据类型最多能存储2GB45ACCP V4.0Oracle数据类型5-3q数值数据类型q可以存储整数、浮点数和实数q最高精度为38位q数值数据类型的声明语法：qNUMBER(p,s)qP表示精度，S表示小数点的位数q日期时间数据类型存储日期和时间值，包括年、月、日，小时、分钟、秒q主要的日期时间类型有：qDATE-存储日期和时间部分，精确到整个的秒qTIMESTAMP-存储日期、时间和时区信息，秒值精确到小数点后6位46ACCP V4.0Oracle数据类型5-4qRAW数据类型用于存储二进制数据qRAW数据类型最多能存储2000字节qLONGRAW数据类型用于存储可变长度的二进制数据qLONGRAW数据类型最多能存储2GBLOBqLOB称为“大对象”数据类型，可以存储多达4GB的非结构化信息，例如声音剪辑和视频文件等qLOB数据类型允许对数据进行高效、随机、分段的访问BLOBCLOBBFILECLOB即CharacterLOB（字符LOB），它能够存储大量字符数据BLOB即BinaryLOB（二进制LOB），可以存储较大的二进制对象，如图形、视频剪辑和声音文件BFILE即BinaryFile（二进制文件），它用于将二进制数据存储在数据库外部的操作系统文件中47ACCP V4.0Oracle数据类型5-5qOracle中伪列就像一个表列，但是它并没有存储在表中q伪列可以从表中查询，但不能插入、更新和删除它们的值q常用的伪列有ROWID和ROWNUMROWID是表中行的存储地址，该地址可以唯一地标识数据库中的一行，可以使用ROWID伪列快速地定位表中的一行ROWNUM是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数48ACCP V4.0数据定义语言q数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象q用于操纵表结构的数据定义语言命令有：qCREATETABLEqALTERTABLEqTRUNCATETABLEqDROPTABLE49ACCP V4.0数据操纵语言q数据操纵语言用于检索、插入和修改数据q数据操纵语言是最常见的SQL命令q数据操纵语言命令包括：qSELECTqINSERTqUPDATEqDELETE50ACCP V4.0DMLSELECT命令2-1q利用现有的表创建表q语法：CREATETABLEASSELECTcolumn_namesFROM;SQLCREATETABLEnewitemfileASSELECT*FROMitemfile;SQLCREATETABLEnewitemfile1ASSELECTitemcode,itemdesc,qty_handFROMitemfile;SQLCREATETABLEnewitemfile2ASSELECT*FROMitemfileWHERE1=2;51ACCP V4.0DMLSELECT命令2-2SQLSELECTDISTINCTvencodeFROMvendor_master;q选择无重复的行q在SELECT子句，使用DISTINCT关键字q使用列别名q为列表达式提供不同的名称q该别名指定了列标题SQLSELECTitemcode,itemdesc,max_level,max_level*2ASNEW_MAXLEVELFROMitemfile;SQLSELECTitemcode,itemdesc,max_level,max_level*2“NewMaximumLevel”FROMitemfile;52ACCP V4.0DMLINSERT命令2-1q插入日期类型的值q日期数据类型的默认格式为“DD-MON-RR”q使用日期的默认格式q使用TO_DATE函数转换INSERTINTOmy_table(date_col)VALUES(TO_DATE(2005-10-18,YYYY-MM-DD);INSERTINTOorder_masterVALUES(o001,12-5月-05,V002,c,25-5月-05);53ACCP V4.0DMLINSERT命令2-2q插入来自其它表中的记录q语法： INSERTINTO(cloumn_list)SELECTcolumn_namesFROM;SQLINSERTINTOnewvendor_masterSELECT*FROMvendor_master;SQLINSERTINTOnewvendor_master(vencode,venname)SELECTvencode,vennameFROMvendor_master;54ACCP V4.0事务控制语言q事务是最小的工作单元，作为一个整体进行工作q保证事务的整体成功或失败，称为事务控制q用于事务控制的语句有：qCOMMIT-提交并结束事务处理qROLLBACK-撤销事务中已完成的工作qSAVEPOINT标记事务中可以回滚的点SQLUPDATEorder_masterSETdel_date=30-8月-05WHEREordernoSAVEPOINTmark1;SQLDELETEFROMorder_masterWHEREorderno=o002;SQLSAVEPOINTmark2;SQLROLLBACKTOSAVEPOINTmark1;SQLCOMMIT;55ACCP V4.0数据控制语言q数据控制语言为用户提供权限控制命令q用于权限控制的命令有：qGRANT授予权限qREVOKE撤销已授予的权限SQLGRANTSELECT,UPDATEONorder_masterTOMARTIN;SQLGRANTUPDATE(qty_hand,re_level)ONitemfileTOMARTIN;SQLGRANTSELECTONvendor_masterTOaccountsWITHGRANTOPTION;SQLREVOKESELECT,UPDATEONorder_masterFROMMARTIN;56ACCP V4.0SQL操作符SQL操作符集合操作符逻辑操作符比较操作符算术操作符连接操作符qOracle支持的SQL操作符分类如下：57ACCP V4.0算术操作符q算术操作符用于执行数值计算q可以在SQL语句中使用算术表达式，算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成q算术操作符包括加(+)、减(-)、乘(*)、除(/)SQLSELECTitemdesc,max_level-qty_handavble_limitFROMitemfileWHEREp_category=spares;SQLSELECTitemdesc,itemrate*(max_level-qty_hand)FROMitemfileWHEREp_category=spares;58ACCP V4.0比较操作符q比较操作符用于比较两个表达式的值q比较操作符包括=、!=、=、BETWEENAND、IN、LIKE和ISNULL等SQLSELECTitemdesc,re_levelFROMitemfileWHEREqty_handSELECTordernoFROMorder_masterWHEREdel_dateIN(06-1月-05,05-2月-05);SQLSELECTvencode,venname,tel_noFROMvendor_masterWHEREvennameLIKEj_s;59ACCP V4.0逻辑操作符SQLSELECT*FROMorder_masterWHEREodate10-5月-05ANDdel_dateSELECTordernoFROMorder_masterINTERSECTSELECTordernoFROMorder_detail;SQLSELECTordernoFROMorder_masterMINUSSELECTordernoFROMorder_detail;MINUS操作符返回从第一个查询结果中排除第二个查询中出现的行。61ACCP V4.0连接操作符q连接操作符用于将多个字符串或数据值合并成一个字符串SQLSELECT(venname|的地址是|venadd1|venadd2|venadd3)addressFROMvendor_masterWHEREvencode=V001;通过使用连接操作符可以将表中的多个列合并成逻辑上的一行列62ACCP V4.0操作符的优先级SQL操作符的优先级从高到低的顺序是：q算术操作符-最高优先级q连接操作符q比较操作符qNOT逻辑操作符qAND逻辑操作符qOR逻辑操作符-最低优先级63ACCP V4.0SQL函数qOracle提供一系列用于执行特定操作的函数qSQL函数带有一个或多个参数并返回一个值q以下是SQL函数的分类：SQL函数单行函数分析函数分组函数64ACCP V4.0单行函数分类q单行函数对于从表中查询的每一行只返回一个值q可以出现在SELECT子句中和WHERE子句中q单行函数可以大致划分为：q日期函数q数字函数q字符函数q转换函数q其他函数65ACCP V4.0日期函数q日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果q日期函数包括：qADD_MONTHSqMONTHS_BETWEENqLAST_DAYqROUNDqNEXT_DAYqTRUNCqEXTRACT66ACCP V4.0字符函数2-1 函数函数 输入输入 输出输出Initcap(char)Selectinitcap(hello)fromdual;HelloLower(char)Selectlower(FUN)fromdual;funUpper(char)Selectupper(sun)fromdual;SUNLtrim(char,set)Selectltrim(xyzadams,xyz)fromdual;adamsRtrim(char,set)Selectrtrim(xyzadams,ams)fromdual;xyzadTranslate(char,from,to)Selecttranslate(jack,j,b)fromdual;backReplace(char,searchstring,repstring)Selectreplace(jackandjue,j,bl)fromdual;blackandblueInstr(char,m,n)Selectinstr(worldwide,d)fromdual;5Substr(char,m,n)Selectsubstr(abcdefg,3,2)fromdual;cdConcat(expr1,expr2)Selectconcat(Hello,world)fromdual;Helloworld字符函数字符函数q字符函数接受字符输入并返回字符或数值67ACCP V4.0字符函数2-2SQLSELECTCHR(67)FROMdual;q以下是一些其它的字符函数：qCHR和ASCIIqLPAD和RPADqTRIMqLENGTHqDECODESQLSELECTLPAD(function,15,=)FROMdual;SQLSELECTTRIM(9from9999876789999)FROMdual;SQLSELECTLENGTH(frances)FROMdual;SQLSELECTvencode,DECODE(venname,frances,Francis)nameFROMvendor_masterWHEREvencode=v001;68ACCP V4.0数字函数q数字函数接受数字输入并返回数值结果函数输入输出Abs(n)Selectabs(-15)fromdual;15Ceil(n)Selectceil(44.778)fromdual;45Cos(n)Selectcos(180)fromdual;-.5984601Cosh(n)Selectcosh(0)fromdual;1Floor(n)Selectfloor(100.2)fromdual;100Power(m,n)Selectpower(4,2)fromdual;16Mod(m,n)Selectmod(10,3)fromdual;1Round(m,n)Selectround(100.256,2)fromdual;100.26Trunc(m,n)Selecttrunc(100.256,2)fromdual;100.25Sqrt(n)Selectsqrt(4)fromdual;2Sign(n)Selectsign(-30)fromdual;-1数字函数数字函数69ACCP V4.0转换函数q转换函数将值从一种数据类型转换为另一种数据类型q常用的转换函数有：qTO_CHARqTO_DATEqTO_NUMBERSELECT TO_CHAR(sysdate,YYYY年年fmMM月月fmDD日日 HH24:MI:SS)FROM dual;SELECT TO_CHAR(itemrate,C99999) FROM itemfile;SELECT TO_DATE(2005-12-06 , yyyy-mm-dd)FROM dual;SELECT TO_NUMBER(100) FROM dual;70ACCP V4.0其它函数q以下是几个用来转换空值的函数：qNVLqNVL2qNULLIFSELECTitemdesc,NVL(re_level,0)FROMitemfile;SELECTitemdesc,NVL2(re_level,re_level,max_level)FROMitemfile;SELECTitemdesc,NULLIF(re_level,max_level)FROMitemfile;71ACCP V4.0分组函数q分组函数基于一组行来返回结果q为每一组行返回一个值AVGMINMAXSUMCOUNTSELECTAVG(re_level)FROMitemfileWHEREp_category=accessories;SELECTMAX(max_level)FROMitemfile;SELECTSUM(itemrate*max_level)FROMitemfile;SELECTCOUNT(*)FROMitemfile;SELECTCOUNT(itemrate)FROMitemfile;SELECTCOUNT(DISTINCTqty_hand)FROMitemfile;分组函数72ACCP V4.0GROUPBY和HAVING子句qGROUPBY子句q用于将信息划分为更小的组q每一组行返回针对该组的单个结果qHAVING子句q用于指定GROUPBY子句检索行的条件SELECTp_category,MAX(itemrate)FROMitemfileGROUPBYp_category;SELECTp_category,MAX(itemrate)FROMitemfileGROUPBYp_categoryHAVINGp_categoryNOTIN(accessories);73ACCP V4.0分析函数2-1q分析函数根据一组行来计算聚合值q用于计算完成聚集的累计排名、移动平均数等q分析函数为每组记录返回多个行分析函数DENSE_RANKRANKROW_NUMBER74ACCP V4.0分析函数2-2q以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始qROW_NUMBER返回连续的排位，不论值是否相等qRANK具有相等值的行排位相同，序数随后跳跃qDENSE_RANK具有相等值的行排位相同，序号是连续的SELECT ename, job, deptno, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS SAL_RANK FROM SCOTT.EMP;SELECT deptno, ename, sal, comm, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, comm) RANKFROM emp; SELECTd.dname,e.ename,e.sal,DENSE_RANK()OVER(PARTITIONBYe.deptnoORDERBYe.salDESC)ASDENRANKFROMempe,deptdWHEREe.deptno=d.deptno;75ACCP V4.0总结qSQL是通用的数据库语言qSQL命令可分为数据定义语言、数据操纵语言、事务控制语言和数据控制语言qOracle支持的数据类型包括字符、数值、日期时间、RAW和LOB等qSQL支持的操作符包括算术、比较、逻辑、集合和连接操作符qSQL函数可大致分为单行函数、聚合函数和分析函数76ACCP V4.0第三章第三章锁和表分区ACCP V4.0回顾qSQL是数据库语言，Oracle使用该语言在数据库中存储和检索信息qOracle支持各种数据类型，如VARCHAR2、NUMBER、LONG、RAW和DATE等q数据操纵语言用于查询和修改表中的数据q事务控制语言管理事务的一致性qSQL操作符包括算术、比较、逻辑、集合和连接操作符qSQL函数可以大致分为单行函数、聚合函数和分析函数78ACCP V4.0目标q理解锁定的概念q了解和使用表分区79ACCP V4.0锁的概念2-1q锁是数据库用来控制共享资源并发访问的机制。q锁用于保护正在被修改的数据q直到提交或回滚了事务之后，其他用户才可以更新数据80ACCP V4.0锁的概念2-2修改表修改表拒绝访问Toy_IDNamePriceT001Barbie20T002GIJoe4581ACCP V4.0锁定的优点q一致性一致性-一次只允许一个用户修改数据q完整性完整性-为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户q并行性并行性允许多个用户访问同一数据Toy_IDNamePriceT001Barbie 20T002GIJoe 45修改表中的数据查看表中的数据允许访问82ACCP V4.0表级锁行级锁锁的类型锁的类型83ACCP V4.0行级锁3-1Toy_IDNamePriceT001Barbie20T002GIJoe45更新T002行更新T001行行被锁定q对正在被修改的行进行锁定。其他用户可以访问除被锁定的行以外的行允许访问84ACCP V4.0行级锁3-2q行级锁是一种排他锁，防止其他事务修改此行q在使用以下语句时，Oracle会自动应用行级锁：qINSERTqUPDATEqDELETEqSELECTFORUPDATEqSELECTFORUPDATE语句允许用户一次锁定多条记录进行更新q使用COMMIT或ROLLBACK语句释放锁85ACCP V4.0行级锁3-3qSELECTFORUPDATE语法:SELECTFORUPDATEOFcolumnsWAITn|NOWAIT;SQLSELECT*FROMorder_masterWHEREvencode=V002FORUPDATEOFodate,del_date;SQLUPDATEorder_masterSETdel_date=28-8月-05WHEREvencode=V002;SQLCOMMIT;SQLSELECT*FROMorder_masterWHEREvencode=V002FORUPDATEWAIT5;SQLSELECT*FROMorder_masterWHEREvencode=V002FORUPDATENOWAIT;86ACCP V4.0表级锁3-1Toy_IDNamePriceT001Barbie20T002GIJoe45修改表中的行更新表拒绝访问锁定整个表，限制其他用户对表的访问。87ACCP V4.0表级锁3-2表级锁类型行共享行排他共享使用命令显示地锁定表，应用表级锁的语法是: LOCKTABLEtable_nameINmodeMODE;共享行排他排他88ACCP V4.0表级锁3-3q行共享(ROWSHARE)禁止排他锁定表q行排他(ROWEXCLUSIVE)禁止使用排他锁和共享锁q共享锁(SHARE)q锁定表，仅允许其他用户查询表中的行q禁止其他用户插入、更新和删除行q多个用户可以同时在同一个表上应用此锁q共享行排他(SHAREROWEXCLUSIVE)比共享锁更多的限制，禁止使用共享锁及更高的锁q排他(EXCLUSIVE)限制最强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表89ACCP V4.0死锁q当两个事务相互等待对方释放资源时，就会形成死锁qOracle会自动检测死锁，并通过结束其中的一个事务来解决死锁q右边是一个死锁的例子T1T2lock(D1)时时间间 lock(D2)lock(D2)lock(D1)等等待待等等待待90ACCP V4.0表分区q允许用户将一个表分成多个分区q用户可以执行查询，只访问表中的特定分区q将不同的分区存储在不同的磁盘，提高访问性能和安全性q可以独立地备份和恢复每个分区NameAddressStateCountryJohnSmith34thRd.California USMichealClarkeLKRd.NewYork USJackJones9thRoadCalifornia USBobSimmons12thStreetNewYork USJimTaylor53rdRoadNewYork US更新表只访问P1P1分区P2分区91ACCP V4.0表分区的类型9-1分区方法分区方法范围分区散列分区列表分区复合分区92ACCP V4.0表分区的类型9-2q范围分区q以表中的一个列或一组列的值的范围分区q范围分区的语法：PARTITIONBYRANGE(column_name)(PARTITIONpart1VALUELESSTHAN(range1),PARTITIONpart2VALUELESSTHAN(range2),.PARTITIONpartNVALUELESSTHAN(MAXVALUE);93ACCP V4.0表分区的类型9-3SQLCREATETABLESales(Product_IDvarchar2(5),Sales_Costnumber(10)PARTITIONBYRANGE(Sales_Cost)(PARTITIONP1VALUESLESSTHAN(1000),PARTITIONP2VALUESLESSTHAN(2000),PARTITIONP3VALUESLESSTHAN(3000);根据Sales_Cost创建分区分区的名称包含销售成本低于1000的所有产品的值SQLCREATETABLESALES2(PRODUCT_IDVARCHAR2(5),SALES_DATEDATENOTNULL,SALES_COSTNUMBER(10)PARTITIONBYRANGE(SALES_DATE)(PARTITIONP1VALUESLESSTHAN(DATE2003-01-01),PARTITIONP2VALUESLESSTHAN(DATE2004-01-01),PARTITIONP3VALUESLESSTHAN(MAXVALUE);q范围分区示例94ACCP V4.0表分区的类型9-4q散列分区q允许用户对不具有逻辑范围的数据进行分区q通过在分区键上执行HASH函数决定存储的分区q将数据平均地分布到不同的分区q散列分区语法PARTITIONBYHASH(column_name)PARTITIONSnumber_of_partitions;或PARTITIONBYHASH(column_name)(PARTITIONpart1TABLESPACEtbs1,PARTITIONpart2TABLESPACEtbs2,.PARTITIONpartNTABLESPACEtbsN);95ACCP V4.0表分区的类型9-5q散列分区示例SQLCREATETABLEEmployee(Employee_IDvarchar2(5),Employee_Namevarchar2(20),Departmentvarchar2(10)PARTITIONBYHASH(Department)(PartitionD1,PartitionD2,PartitionD3);在表Employee上创建分区键Department分区的名称创建3个分区SQLCREATETABLEEMPLOYEE(EMP_IDNUMBER(4),EMP_NAMEVARCHAR2(14),EMP_ADDRESSVARCHAR2(15),DEPARTMENTVARCHAR2(10)PARTITIONBYHASH(DEPARTMENT)PARTITIONS4;96ACCP V4.0表分区的类型9-6q列表分区q允许用户将不相关的数据组织在一起q列表分区的语法：PARTITIONBYLIST(column_name)(PARTITIONpart1VALUES(values_list1),PARTITIONpart2VALUES(values_list2),.PARTITIONpartNVALUES(DEFAULT);97ACCP V4.0表分区的类型9-7SQLCREATETABLEEmployee(Emp_IDnumber(4),Emp_Namevarchar2(14),Emp_Addressvarchar2(15)PARTITIONBYLIST(Emp_Address)(Partitionnorthvalues(芝加哥),Partitionwestvalues(旧金山,洛杉矶),Partitionsouthvalues(亚特兰大,达拉斯,休斯顿),Partitioneastvalues(纽约,波斯顿);包含住在芝加哥的职员的记录根据职员住址在表上创建的列表分区分区的名称q列表分区示例98ACCP V4.0表分区的类型9-8q复合分区q范围分区与散列分区或列表分区的组合q复合分区的语法：PARTITIONBYRANGE(column_name1)SUBPARTITIONBYHASH(column_name2)SUBPARTITIONSnumber_of_partitions(PARTITIONpart1VALUELESSTHAN(range1),PARTITIONpart2VALUELESSTHAN(range2),.PARTITIONpartNVALUELESSTHAN(MAXVALUE);99ACCP V4.0表分区的类型9-9SQLCREATETABLESALES(PRODUCT_IDVARCHAR2(5),SALES_DATEDATENOTNULL,SALES_COSTNUMBER(10)PARTITIONBYRANGE(SALES_DATE)SUBPARTITIONBYHASH(PRODUCT_ID)SUBPARTITIONS5(PARTITIONS1VALUESLESSTHAN(TO_DATE(01/4月/2001,DD/MON/YYYY),PARTITIONS2VALUESLESSTHAN(TO_DATE(01/7月/2001,DD/MON/YYYY),PARTITIONS3VALUESLESSTHAN(TO_DATE(01/9月/2001,DD/MON/YYYY),PARTITIONS4VALUESLESSTHAN(MAXVALUE);创建的四个范围分区的名称在表的Sales_Date列中创建范围分区在表的Product_ID列创建散列子分区在每个范围分区中创建5个散列子分区q复合分区示例100ACCP V4.0操纵已分区的表q在已分区的表中插入数据与操作普通表完全相同，Oracle会自动将数据保存到对应的分区q查询、修改和删除分区表时可以显式指定要操作的分区INSERTINTOSALES3VALUES(P001,02-3月-2001,2000);INSERTINTOSALES3VALUES(P002,10-5月-2001,2508);INSERTINTOSALES3VALUES(P003,05-7月-2001,780);INSERTINTOSALES3VALUES(P004,12-9月-2001,1080);SELECT*FROMSALES3PARTITION(P3);DELETEFROMSALES3PARTITION(P2);101ACCP V4.0分区维护操作q分区维护操作修改已分区表的分区。q分区维护的类型：q计划事件定期删除最旧的分区q非计划事件解决应用程序或系统问题q分区维护操作有：q添加分区q删除分区q截断分区q合并分区q拆分分区102ACCP V4.0维护分区2-1q添加分区在最后一个分区之后添加新分区SQLALTERTABLESALESADDPARTITIONP4VALUESLESSTHAN(4000);q删除分区删除一个指定的分区，分区的数据也随之删除SQLALTERTABLESALESDROPPARTITIONP4;q截断分区删除指定分区中的所有记录SQLALTERTABLESALESTRUNCATEPARTITIONP3;103ACCP V4.0维护分区2-2q合并分区-将范围分区或复合分区的两个相邻分区连接起来SQLALTERTABLESALESMERGEPARTITIONSS1,S2INTOPARTITIONS2;q拆分分区-将一个大分区中的记录拆分到两个分区中SQLALTERTABLESALESSPLITPARTITIONP2AT(1500)INTO(PARTITIONP21,PARTITIONP22);104ACCP V4.0总结q锁用于保护多用户环境下被修改的数据q锁分为两种级别，即行级锁和表级锁q表分区允许将一个表划分成几部分，以改善大型应用系统的性能q分区方法包括范围分区、散列分区、复合分区和列表分区q分区维护操作包括添加、删除、截断、合并和拆分分区105ACCP V4.0第四章第四章数据库对象ACCP V4.0回顾q锁用于保护多用户环境下被修改的数据q锁分为两种级别，即行级锁和表级锁q表分区允许将一个表划分成几部分，以改善大型应用系统的性能q分区方法包括范围分区、散列分区、复合分区和列表分区q分区维护操作包括添加、删除、截断、合并和拆分分区107ACCP V4.0目标q使用同义词q使用序列q创建视图q创建索引108ACCP V4.0数据库对象简介qOracle数据库对象又称模式对象q数据库对象是逻辑结构的集合，最基本的数据库对象是表q其他数据库对象包括：数据库对象数据库对象 同义词序列视图索引109ACCP V4.0同义词3-1q同义词是现有对象的一个别名。q简化SQL语句q隐藏对象的名称和所有者q提供对对象的公共访问q同义词共有两种类型：同义词私有同义词公有同义词私有同义词只能在其模式内访问，且不能与当前模式的对象同名。公有同义词可被所有的数据库用户访问。110ACCP V4.0同义词3-2CREATE SYNONYM emp FOR SCOTT.emp;SCOTT.emp的别名模式名表名私有同义词公有同义词CREATE PUBLIC SYNONYM emp_syn FOR SCOTT.emp;同义词名称111ACCP V4.0同义词3-3创建或替换现有的同义词CREATEORREPLACESYNONYMemp_synFORSCOTT.emp;替换现有的同义词SQLDROPSYNONYMemp;SQLDROPPUBLICSYNONYMemp_syn;删除同义词112ACCP V4.0序列q序列是用于生成唯一、连续序号的对象q序列可以是升序的，也可以是降序的q使用CREATESEQUENCE语句创建序列SQLCREATESEQUENCEtoys_seqSTARTWITH10INCREMENTBY10MAXVALUE2000MINVALUE10NOCYCLECACHE10;指定第一个序号从10开始指定序号之间的间隔为10表示序列的最大值为2000表示序列的最小值为10在达到最大值后停止生成下一个值指定内存中预先分配的序号数113ACCP V4.0访问序列q通过序列的伪列来访问序列的值qNEXTVAL返回序列的下一个值qCURRVAL返回序列的当前值SQLINSERTINTOtoys(toyid,toyname,toyprice)VALUES(toys_seq.NEXTVAL,TWENTY,25);SQLINSERTINTOtoys(toyid,toyname,toyprice)VALUES(toys_seq.NEXTVAL,MAGICPENCIL,75);指定序列的下一个值SQLSELECTtoys_seq.CURRVALFROMdual;检索序列的当前值114ACCP V4.0更改和删除序列SQLALTERSEQUENCEtoys_seqMAXVALUE5000CYCLE;使用ALTERSEQUENCE语句修改序列，不能更改序列的STARTWITH参数使用DROPSEQUENCE语句删除序列SQLDROPSEQUENCEtoys_seq;115ACCP V4.0视图q视图以经过定制的方式显示来自一个或多个表的数据q视图可以视为“虚拟表”或“存储的查询”q创建视图所依据的表称为“基表”q视图的优点有：q提供了另外一种级别的表安全性q隐藏的数据的复杂性q简化的用户的SQL命令q隔离基表结构的改变q通过重命名列，从另一个角度提供数据116ACCP V4.0创建视图3-1studnostudnamestudmarkssubnostudcaste1Rob452Open2James334SC3Jesica405OpenStud_detailsStud_viewstudnostudnamesubno1Rob22James43Jesica5创建视图CREATEVIEWstud_viewASSELECTstudno,studname,subnoFROMStud_details;117ACCP V4.0创建视图3-2q创建视图的语法： CREATEORREPLACEFORCEVIEWview_name(alias,alias.)ASselect_statementWITHCHECKOPTIONWITHREADONLY;118ACCP V4.0创建视图3-3使用WITHCHECKOPTION选项创建视图CREATEORREPLACEVIEWpause_viewASSELECT*FROMorder_masterWHEREostatus=pWITHCHECKOPTIONCONSTRAINTchk_pv;使用ORDERBY子句创建视图CREATEORREPLACEVIEWord_venASSELECT*FROMvendor_masterORDERBYvenname;创建带有错误的视图CREATEFORCEVIEWvenASSELECT*FROMvenmaster;119ACCP V4.0联接视图2-1StudnoStudnameSubmrksSubno1Rob4522James3343Jesica404SubnoSubname2English4Maths5ScienceStud_detailsSub_detailsStudnoStudnameSubmrksSubname1Rob45English2James33Maths3Jesica40MathsCREATEVIEWStud_sub_viewASSELECTStudno,Studname,Submrks,SubnameFROMStud_details,Sub_DetailsWHEREStud_details.Subno=Sub_details.Subno;Stud_sub_view联接视图120ACCP V4.0联接视图2-2创建外联接视图CREATEVIEWven_ord_outj_viewASSELECTvm.vencode,venname,orderno,odate,ostatusFROMvendor_mastervm,order_masteromWHEREvm.vencode=om.vencode(+);SELECTvm.vencode,venname,orderno,odate,ostatusFROMvendor_mastervmLEFTOUTERJOINorder_masteromONvm.vencode=om.vencode;121ACCP V4.0视图上的DML语句q在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETEq视图上的DML语句有如下限制：q只能修改一个底层的基表q如果修改违反了基表的约束条件，则无法更新视图q如果视图包含连接操作符、DISTINCT关键字、集合操作符、聚合函数或GROUPBY子句，则将无法更新视图q如果视图包含伪列或表达式，则将无法更新视图122ACCP V4.0键保留表StudnoStudnameSubmrksSubno1Rob4522James3343Jesica404SubnoSubname2English4Maths5ScienceStud_detailsSub_detailsStudnoStudnameSubmarksSubnoSubname1Rob452English2James334Maths3Jesica404Maths键保留表因为Studno既是Stud_details中的主键，也是联接结果中的主键Stud_details为什么是键保留表？联接视图123ACCP V4.0q视图中可以使用单行函数、分组函数和表达式q使用DROPVIEW语句删除视图视图中的函数CREATEVIEWitem_viewASSELECTitemcode,LOWER(itemdesc)item_descFROMitemfile;SQLDROPVIEWtoys_view;124ACCP V4.0索引3-1q索引是与表相关的一个可选结构q用以提高SQL语句执行的性能q减少磁盘I/Oq使用CREATEINDEX语句创建索引q在逻辑上和物理上都独立于表的数据qOracle自动维护索引125ACCP V4.0索引3-2q索引有各种类型，除了标准索引外，还有一些特殊类型的索引：索引的类型基于函数的索引反向键索引位图索引唯一索引组合索引126ACCP V4.0索引3-3SQLCREATEINDEXitem_indexONitemfile(itemcode)TABLESPACEindex_tbs;创建标准索引重建索引SQLALTERINDEXitem_indexREBUILD;删除索引SQLDROPINDEXitem_index;127ACCP V4.0唯一索引SQLCREATEUNIQUEINDEXitem_indexONitemfile(itemcode);q唯一索引确保在定义索引的列中没有重复值qOracle自动在表的主键列上创建唯一索引q使用CREATEUNIQUEINDEX语句创建唯一索引128ACCP V4.0组合索引SQLCREATEINDEXcomp_indexONitemfile(p_category,itemrate);q组合索引是在表的多个列上创建的索引q索引中列的顺序是任意的q如果SQL语句的WHERE子句中引用了组合索引的所有列或大多数列，则可以提高检索速度129ACCP V4.0q反向键索引反转索引列键值的每个字节q通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上q创建索引时使用REVERSE关键字反向键索引SQLCREATEINDEXrev_indexONitemfile(itemcode)REVERSE;SQLALTERINDEXrev_indexREBUIDNOREVERSE;130ACCP V4.0位图索引SQLCREATEBITMAPINDEXbit_indexONorder_master(orderno);q位图索引适合创建在低基数列上q位图索引不直接存储ROWID，而是存储字节位到ROWID的映射q减少响应时间q节省空间占用131ACCP V4.0索引组织表2-1SQLCREATETABLEind_org_tab(vencodeNUMBER(4)PRIMARYKEY,vennameVARCHAR2(20)ORGANIZATIONINDEX;q索引组织表的数据存储在与其关联的索引中q索引中存储的是行的实际数据，而不是ROWIDq基于主键访问数据qCREATETABLE命令与ORGANIZATIONINDEX子句一起用于创建索引组织表132ACCP V4.0索引组织表2-2q普通表与索引组织表的比较普通表索引组织表ROWID唯一地标识行主键唯一地标识行隐式的ROWID列没有隐式的ROWID列基于ROWID的访问基于主键的访问顺序扫描返回所有行完全索引扫描返回所有行，并按主键顺序排列支持分区不支持分区133ACCP V4.0基于函数的索引SQLCREATEINDEXlowercase_idxONtoys(LOWER(toyname);q基于一个或多个列上的函数或表达式创建的索引q表达式中不能出现聚合函数q不能在LOB类型的列上创建q创建时必须具有QUERYREWRITE权限SQLSELECTtoyidFROMtoysWHERELOWER(toyname)=doll;134ACCP V4.0索引中的分区q可以将索引存储在不同的分区中q与分区有关的索引有三种类型：q局部分区索引在分区表上创建的索引，在每个表分区上创建独立的索引，索引的分区范围与表一致q全局分区索引在分区表或非分区表上创建的索引，索引单独指定分区的范围，与表的分区范围或是否分区无关q全局非分区索引在分区表上创建的全局普通索引，索引没有被分区135ACCP V4.0获取索引的信息SQLSELECTINDEX_NAME,TABLE_NAME,COLUMN_NAMEFROMUSER_IND_COLUMNSORDERBYINDEX_NAME,COLUMN_POSITION;q与索引有关的数据字典视图有：qUSER_INDEXES用户创建的索引的信息qUSER_IND_PARTITIONS用户创建的分区索引的信息qUSER_IND_COLUMNS与索引相关的表列的信息136ACCP V4.0总结q同义词是现有数据库对象的别名q序列用于生成唯一、连续的序号q视图是基于一个或多个表的虚拟表q索引是与表相关的一个可选结构，用于提高SQL语句执行的性能q索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引q索引组织表基于主键访问数据137ACCP V4.0第五章第五章使用PL/SQLACCP V4.0回顾q同义词是现有数据库对象的别名q序列用于生成唯一、连续的序号q视图是基于一个或多个表的虚拟表q索引是与表相关的一个可选结构，用于提高SQL语句执行的性能q索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引q索引组织表基于主键访问数据139ACCP V4.0目标q理解PL/SQL功能和特点q了解数据类型及其用法q理解逻辑比较q理解控制结构q掌握错误处理140ACCP V4.0PL/SQL简介qPL/SQL是过程语言(ProceduralLanguage)与结构化查询语言(SQL)结合而成的编程语言qPL/SQL是对SQL的扩展q支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构q可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑q与Oracle服务器和Oracle工具紧密集成，具备可移植性、灵活性和安全性141ACCP V4.0PL/SQL的优点2-1q支持SQL，在PL/SQL中可以使用：q数据操纵命令q事务控制命令q游标控制qSQL函数和SQL运算符q支持面向对象编程(OOP)q可移植性，可运行在任何操作系统和平台上的Oralce数据库q更佳的性能，PL/SQL经过编译执行用户将整个语句块发送给OracleOracleProcedureBegin ProcedureCall SQL Command End142ACCP V4.0PL/SQL的优点2-2 PL/SQLSQLq与SQL紧密集成，简化数据处理。q支持所有SQL数据类型q支持NULL值q支持%TYPE和%ROWTYPE属性类型q安全性，可以通过存储过程限制用户对数据的访问143ACCP V4.0PL/SQL的体系结构qPL/SQL引擎驻留在Oracle服务器中q该引擎接受PL/SQL块并对其进行编译执行将PL/SQL块发送给Oracle服务器用户用户执行过程语句引擎将SQL语句发送给SQL语句执行器Oracle 服务器PL/SQL引擎引擎SQL语句执行器过程语句过程语句执行器执行器执行SQL语句将结果发送给用户144ACCP V4.0PL/SQL块简介qPL/SQL块是构成PL/SQL程序的基本单元q将逻辑上相关的声明和语句组合在一起qPL/SQL分为三个部分，声明部分、可执行部分和异常处理部分DECLAREdeclarationsBEGINexecutablestatementsEXCEPTIONhandlersEND;DECLAREqty_on_handNUMBER(5);BEGINSELECTquantityINTOqty_on_handFROMProductsWHEREproduct=芭比娃娃FORUPDATEOFquantity;IFqty_on_hand0THENUPDATEProductsSETquantity=quantity+1WHEREproduct=芭比娃娃;INSERTINTOpurchase_recordVALUES(已购买芭比娃娃,SYSDATE);ENDIF;COMMIT;EXCEPTION/*异常处理语句*/WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(出错:|SQLERRM);END;声明部分定义变量、游标和自定义异常包含SQL和PL/SQL语句的可执行部分指定出现错误时需要执行的操作145ACCP V4.0变量和常量1-2qPL/SQL块中可以使用变量和常量q在声明部分声明，使用前必须先声明q声明时必须指定数据类型，每行声明一个标识符q在可执行部分的SQL语句和过程语句中使用q声明变量和常量的语法：identifieridentifier CONSTANT CONSTANT datatypedatatype NOT NULL NOT NULL := | DEFAULT := | DEFAULT exprexpr; ;q给变量赋值有两种方法：q使用赋值语句:=q使用SELECTINTO语句146ACCP V4.0变量和常量2-2DECLAREicodeVARCHAR2(6);p_catgVARCHAR2(20);p_rateNUMBER;c_rateCONSTANTNUMBER:=0.10;BEGIN.icode:=i205;SELECTp_category,itemrate*c_rateINTOp_catg,p_rateFROMitemfileWHEREitemcode=icode;.END;147ACCP V4.0数据类型qPL/SQL支持的内置数据类型数据类型数据类型LOB类型标量类型属性类型%ROWTYPE数字字符布尔型日期时间BFILEBLOBCLOBNCLOB%TYPE提供某个变量或数据库表列的数据类型提供表示表中一行的记录类型存储非结构化数据块148ACCP V4.0数字数据类型q指定数值的存储格式数字数据类型数字数据类型NUMBERBINARY_INTEGERPLS_INTEGERNATURALNATURALLNPOSITIVEPOSITIVENDECIMALFLOATINTEGERREALSIGNTYPE存储有符号整数，所需存储空间少于NUMBER类型值存储整数、实数和浮点数存储有符号整数，可使算术计算快速而有效149ACCP V4.0字符数据类型q字符数据类型包括：qCHARqVARCHAR2qLONGqRAWqLONGRAWqPL/SQL的数据类型与SQL数据类型的比较数据类型SQL类型PL/SQL类型CHAR1.20001.32767LONG1.2GB1.32760LONG RAW1.2GB1.32760RAW1.20001.32767VARCHAR21.40001.32767150ACCP V4.0日期时间和布尔数据类型q日期时间类型q存储日期和时间数据q常用的两种日期时间类型qDATEqTIMESTAMPq布尔数据类型q此类别只有一种类型，即BOOLEAN类型q用于存储逻辑值(TRUE、FALSE和NULL)q不能向数据库中插入BOOLEAN数据q不能将列值保存到BOOLEAN变量中q只能对BOOLEAN变量执行逻辑操作151ACCP V4.0LOB数据类型2-1q用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。qLOB数据类型可存储最大4GB的数据。qLOB类型包括：qBLOB将大型二进制对象存储在数据库中qCLOB将大型字符数据存储在数据库中qNCLOB存储大型UNICODE字符数据qBFILE将大型二进制对象存储在操作系统文件中152ACCP V4.0LOB数据类型2-2qLOB类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置qDBMS_LOB程序包用于操纵LOB数据SETSERVEROUTPUTONDECLAREclob_varCLOB;amountINTEGER;offsetINTEGER;output_varVARCHAR2(100);BEGINSELECTchapter_textINTOclob_varFROMmy_book_textWHEREchapter_id=5;amount:=24;-要读取的字符数offset:=1;-起始位置DBMS_LOB.READ(clob_var,amount,offset,output_var);DBMS_OUTPUT.PUT_LINE(output_var);END;/从表中选择CLOB定位符到clob_var变量中从CLOB数据中读取24个字符存储到output_var变量中显示读到的信息153ACCP V4.0属性类型q用于引用数据库列的数据类型，以及表示表中一行的记录类型q属性类型有两种：q%TYPE-引用变量和数据库列的数据类型q%ROWTYPE-提供表示表中一行的记录类型q使用属性类型的优点：q不需要知道被引用的表列的具体类型q如果被引用对象的数据类型发生改变，PL/SQL变量的数据类型也随之改变icodeitemfile.itemcode%TYPE;emp_recscott.emp%ROWTYPE;154ACCP V4.0q布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符AND、OR和NOT连接q布尔表达式有三种类型：q数字布尔型q字符布尔型q日期布尔型逻辑比较q逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式q布尔表达式由关系运算符与变量或常量组成关系运算符关系运算符说明说明=比较两个变量是否相等，如果值相当，则返回比较两个变量是否相等，如果值相当，则返回 True, !=比较两个变量，如果不相等，则返回比较两个变量，如果不相等，则返回 True比较两个变量，检查值比较两个变量，检查值 1 是否大于是否大于 值值 2=比较两个变量，检查变量比较两个变量，检查变量 1 是否大于等于变量是否大于等于变量 2155ACCP V4.0控制结构PL/SQL支持的流程控制结构：q条件控制qIF语句qCASE语句q循环控制qLOOP循环qWHILE循环qFOR循环q顺序控制qGOTO语句qNULL语句156ACCP V4.0条件控制2-1qIF语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE和IF-THEN-ELSIFDECLARE icode VARCHAR2(4); irate NUMBER;BEGIN icode := i203; SELECT itemrate INTO irate FROM itemfile WHERE itemcode = icode; IF irate 200 THEN UPDATE itemfile SET itemrate = itemrate - 200 WHERE itemcode = icode; ELSE UPDATE itemfile SET itemrate = itemrate - 50 WHERE itemcode = icode; END IF; DBMS_OUTPUT.PUT_LINE(itemrate=| irate);END;157ACCP V4.0条件控制2-2BEGIN CASE &grade WHEN A THEN DBMS_OUTPUT.PUT_LINE(优异优异); WHEN B THEN DBMS_OUTPUT.PUT_LINE (优秀优秀); WHEN C THEN DBMS_OUTPUT.PUT_LINE (良好良好); WHEN D THEN DBMS_OUTPUT.PUT_LINE (一般一般); WHEN F THEN DBMS_OUTPUT.PUT_LINE (较差较差); ELSE DBMS_OUTPUT.PUT_LINE (没有此成绩没有此成绩); END CASE;END;qCASE语句用于根据单个变量或表达式与多个值进行比较q执行CASE语句前，先计算选择器的值158ACCP V4.0循环控制q循环控制用于重复执行一系列语句q循环控制语句包括：qLOOP、EXIT和EXITWHENq循环控制的三种类型：qLOOP-无条件循环qWHILE-根据条件循环qFOR-循环固定的次数LOOPsequence_of_statementsENDLOOP;WHILEconditionLOOPsequence_of_statementsENDLOOP;FORcounterINREVERSEvalue1.value2LOOPsequence_of_statementsENDLOOP;159ACCP V4.0顺序控制q顺序控制用于按顺序执行语句q顺序控制语句包括：qGOTO语句-无条件地转到标签指定的语句qNULL语句-什么也不做的空语句DECLARE qtyhand itemfile.qty_hand%type; relevel itemfile.re_level%type;BEGIN SELECT qty_hand,re_level INTO qtyhand,relevel FROM itemfile WHERE itemcode = i201; IF qtyhand relevel THEN GOTO updation; ELSE GOTO quit; END IF; UPDATE itemfile SET qty_hand = qty_hand + re_level WHERE itemcode = i201; NULL;END;160ACCP V4.0动态SQLq动态SQL是指在PL/SQL程序执行时生成的SQL语句q编译程序对动态SQL不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行qDDL语句命令和会话控制语句不能在PL/SQL中直接使用，但是可以通过动态SQL来执行q执行动态SQL的语法： EXECUTE IMMEDIATE dynamic_sql_string INTO define_variable_list USING bind_argument_list;DECLARE sql_stmt VARCHAR2(200); emp_id NUMBER(4) := 7566; emp_rec emp%ROWTYPE;BEGIN EXECUTE IMMEDIATE CREATE TABLE bonus1 (id NUMBER, amt NUMBER); sql_stmt := SELECT * FROM emp WHERE empno = :id; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;END;161ACCP V4.0错误处理2-1q在运行程序时出现的错误叫做异常q发生异常后，语句将停止执行，控制权转移到PL/SQL块的异常处理部分q异常有两种类型：q预定义异常-当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发q用户定义异常-用户可以在PL/SQL块的声明部分定义异常，自定义的异常通过RAISE语句显式引发162ACCP V4.0错误处理2-2q处理预定义异常q处理用户定义异常DECLAREordernumVARCHAR2(5);BEGINSELECTordernoINTOordernumFROMorder_master;EXCEPTIONWHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(返回多行);END;DECLAREinvalidCATEGORYEXCEPTION;categoryVARCHAR2(10);BEGINcategory:=&Category;IFcategoryNOTIN(附件,顶盖,备件)THENRAISEinvalidCATEGORY;ELSEDBMS_OUTPUT.PUT_LINE(您输入的类别是|category);ENDIF;EXCEPTIONWHENinvalidCATEGORYTHENDBMS_OUTPUT.PUT_LINE(无法识别该类别);END;163ACCP V4.0qRAISE_APPLICATION_ERROR过程q用于创建用户定义的错误信息q可以在可执行部分和异常处理部分使用q错误编号必须介于20000和20999之间q错误消息的长度可长达2048个字节q引发应用程序错误的语法： RAISE_APPLICATION_ERROR(error_number, error_message);引发应用程序错误DECLARErateitemfile.itemrate%TYPE;rate_exceptionEXCEPTION;BEGINSELECTNVL(itemrate,0)INTOrateFROMitemfileWHEREitemcode=i207;IFrate=0THENRAISErate_exception;ELSEDBMS_OUTPUT.PUT_LINE(项费率为：|rate);ENDIF;EXCEPTIONWHENrate_exceptionTHENRAISE_APPLICATION_ERROR(-20001,未指定项费率);END;164ACCP V4.0总结qPL/SQL是一种可移植的高性能事务处理语言qPL/SQL引擎驻留在Oracle服务器中qPL/SQL块由声明部分、可执行部分和异常处理部分组成qPL/SQL数据类型包括标量数据类型、LOB数据类型和属性类型q控制结构包括条件控制、循环控制和顺序控制qPL/SQL支持动态SQLq运行时出现的错误叫做异常q异常可以分为预定义异常和用户定义的异常165ACCP V4.0第六章第六章游标管理ACCP V4.0回顾qPL/SQL是一种可移植的高性能事务处理语言qPL/SQL引擎驻留在Oracle服务器中qPL/SQL块由声明部分、可执行部分和异常处理部分组成qPL/SQL支持的数据类型包括标量数据类型、LOB数据类型和属性类型q控制结构包括条件控制、循环控制和顺序控制qPL/SQL支持动态SQLq运行时出现的错误叫做异常q异常可以分为预定义异常和用户定义的异常167ACCP V4.0目标q掌握游标管理技巧168ACCP V4.0游标简介2-1Oracle 服务器服务器执行PL/SQL程序内存单元保存到游标中一次处理一行检索行提取行169ACCP V4.0游标简介2-2q逐行处理查询结果，以编程的方式访问数据q游标的类型：隐式游标REF游标显式游标在PL/SQL程序中执行DMLSQL语句时自动创建隐式游标。显式游标用于处理返回多行的查询。REF游标用于处理运行时才能确定的动态SQL查询的结果游标类型170ACCP V4.0隐式游标4-1q在PL/SQL中使用DML语句时自动创建隐式游标q隐式游标自动声明、打开和关闭，其名为SQLq通过检查隐式游标的属性可以获得最近执行的DML语句的信息q隐式游标的属性有：q%FOUNDSQL语句影响了一行或多行时为TRUEq%NOTFOUNDSQL语句没有影响任何行时为TRUEq%ROWCOUNTSQL语句影响的行数q%ISOPEN-游标是否打开，始终为FALSE171ACCP V4.0隐式游标4-2SQLSETSERVEROUTPUTONSQLBEGINUPDATEtoysSETtoyprice=270WHEREtoyid=P005;IFSQL%FOUNDTHENDBMS_OUTPUT.PUT_LINE(表已更新);ENDIF;END;/只有在DML语句影响一行或多行时，才返回True172ACCP V4.0隐式游标4-3SQLSETSERVEROUTPUTONSQLDECLAREv_TOYIDTOYS.ID%type:=&TOYID;v_TOYNAMETOYS.NAME%Type:=&TOYNAME;BEGINUPDATETOYSSETNAME=v_TOYNAMEWHEREtoyid=v_TOYID;IFSQL%NOTFOUNDTHENDBMS_OUTPUT.PUT_LINE(编号未找到。);ELSEDBMS_OUTPUT.PUT_LINE(表已更新);ENDIF;END;/如果DML语句不影响任何行，则返回True173ACCP V4.0隐式游标4-4SQLSETSERVEROUTPUTONSQLBEGINUPDATEvendor_masterSETvenname=RobMathewWHEREvencode=V004;DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);END;/返回DML语句影响的行数174ACCP V4.0SELECTINTO语句2-1SQLSETSERVEROUTPUTONSQLDECLAREempidVARCHAR2(10);desigVARCHAR2(10);BEGINempid:=&Employeeid;SELECTdesignationINTOdesigFROMemployeeWHEREempno=empid;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(职员未找到);END;/如果没有与SELECTINTO语句中的条件匹配的行，将引发NO_DATA_FOUND异常175ACCP V4.0SELECTINTO语句2-2SQLSETSERVEROUTPUTONSQLDECLAREempidVARCHAR2(10);BEGINSELECTempnoINTOempidFROMemployee;EXCEPTIONWHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(该查询提取多行);END;/如果SELECTINTO语句返回多个值，将引发TOO_MANY_ROWS异常176ACCP V4.0显式游标2-1q显式游标在PL/SQL块的声明部分定义查询，该查询可以返回多行q显式游标的操作过程：数据库打开游标30George344Roger245James1Stud_mrksStud_namestud_no提取行变量关闭游标177ACCP V4.0显式游标2-2SQLSETSERVEROUTPUTONSQLDECLAREmy_toy_pricetoys.toyprice%TYPE;CURSORtoy_curISSELECTtoypriceFROMtoysWHEREtoyprice250;BEGINOPENtoy_cur;LOOPFETCHtoy_curINTOmy_toy_price;EXITWHENtoy_cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE(TOYPRICE=:玩具单价=：|my_toy_price);ENDLOOP;CLOSEtoy_cur;END;声明游标打开游标提取行关闭游标178ACCP V4.0带参数的显式游标q声明显式游标时可以带参数以提高灵活性q声明带参数的显式游标的语法如下：CURSOR ( ) IS select_statement;SQLSETSERVEROUTPUTONSQLDECLAREdesigVARCHAR2(20);emp_codeVARCHAR2(5);empnmVARCHAR2(20);CURSORemp_cur(desigparamVARCHAR2)ISSELECTempno,enameFROMemployeeWHEREdesignation=desig;BEGINdesig:=&desig;OPENemp_cur(desig);LOOPFETCHemp_curINTOemp_code,empnm;EXITWHENemp_cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE(emp_code|empnm);ENDLOOP;CLOSEemp_cur;END;179ACCP V4.0使用显式游标更新行2-1q允许使用游标删除或更新活动集中的行q声明游标时必须使用SELECTFORUPDATE语句 CURSORISSELECTstatementFORUPDATE;UPDATESETWHERECURRENTOF更新的语法DELETE FROM WHERE CURRENT OF 删除的语法180ACCP V4.0使用显式游标更新行2-2SQLSETSERVEROUTPUTONSQLDECLAREnew_priceNUMBER;CURSORcur_toyISSELECTtoypriceFROMtoysWHEREtoyprice100FORUPDATEOFtoyprice;BEGINOPENcur_toy;LOOPFETCHcur_toyINTOnew_price;EXITWHENcur_toy%NOTFOUND;UPDATEtoysSETtoyprice=1.1*new_priceWHERECURRENTOFcur_toy;ENDLOOP;CLOSEcur_toy;COMMIT;END;181ACCP V4.0循环游标2-1q循环游标用于简化游标处理代码q当用户需要从游标中提取所有记录时使用q循环游标的语法如下：FORINLOOPENDLOOP;182ACCP V4.0循环游标2-2SQLSETSERVEROUTPUTONSQLDECLARECURSORmytoy_curISSELECTtoyid,toyname,toypriceFROMtoys;BEGINFORtoy_recINmytoy_curLOOPDBMS_OUTPUT.PUT_LINE(玩具编号：|toy_rec.toyid|玩具名称：|toy_rec.toyname|玩具单价：|toy_rec.toyprice);ENDLOOP;END;183ACCP V4.0REF游标和游标变量3-1qREF游标和游标变量用于处理运行时动态执行的SQL查询q创建游标变量需要两个步骤：q声明REF游标类型q声明REF游标类型的变量q用于声明REF游标类型的语法为：TYPEISREFCURSORRETURN;184ACCP V4.0REF游标和游标变量3-2TYPEmy_curtypeISREFCURSORRETURNstud_det%ROWTYPE;order_curmy_curtype;声明强类型的REF游标q打开游标变量的语法如下：OPENcursor_nameFORselect_statement;TYPEmy_ctypeISREFCURSOR;stud_curmy_ctype;声明弱类型的REF游标185ACCP V4.0REF游标和游标变量3-3SQLDECLARETYPEtoys_curtypeISREFCURSORRETURNtoys%ROWTYPE;toys_curvartoys_curtype;toys_rectoys%ROWTYPE;BEGINOPENtoys_curvarFORSELECT*FROMtoys;FETCHtoys_curvarINTOtoys_rec;.CLOSEtoys_curvar;END;声明REF游标类型声明游标变量186ACCP V4.0游标变量的优点和限制q游标变量的功能强大，可以简化数据处理。q游标变量的优点有：q可从不同的SELECT语句中提取结果集q可以作为过程的参数进行传递q可以引用游标的所有属性q可以进行赋值运算q使用游标变量的限制：q不能在程序包中声明游标变量qFORUPDATE子句不能与游标变量一起使用q不能使用比较运算符187ACCP V4.0使用游标变量执行动态SQLq可以使用游标变量执行动态构造的SQL语句q打开执行动态SQL的游标变量的语如下： OPEN cursor_name FOR dynamic_sqlstring USING bind_argument_list;DECLAREr_empemp%ROWTYPE;TYPEc_typeISREFCURSOR;curc_type;p_salaryNUMBER;BEGINp_salary:=2500;OPENcurFORselect*fromempwheresal:1orderbysaldescUSINGp_salary;DBMS_OUTPUT.PUT_LINE(薪水大于|p_salary|的员工有：);LOOPFETCHcurINTOr_emp;EXITWHENcur%NOTFOUND;DBMS_OUTPUT.PUT_LINE(编号：|r_emp.empno|姓名：|r_emp.ename|薪水：|r_emp.sal);ENDLOOP;CLOSEcur;END;188ACCP V4.0总结q游标用于处理查询结果集中的数据q游标类型有：隐式游标、显式游标和REF游标q隐式游标由PL/SQL自动定义、打开和关闭q显式游标用于处理返回多行的查询q显式游标可以删除和更新活动集中的行q要处理结果集中所有记录时，可使用循环游标q在声明REF游标时，不需要将SELECT语句与其关联189ACCP V4.0第七章第七章子程序和程序包ACCP V4.0回顾q游标用于处理查询结果集中的数据q游标类型有：隐式游标、显式游标和REF游标q隐式游标由PL/SQL自动定义、打开和关闭q显式游标用于处理返回多行的查询q显式游标可以删除和更新活动集中的行q要处理结果集中所有记录时，可使用循环游标q在声明REF游标时，不需要将SELECT语句与其关联191ACCP V4.0目标q创建和使用子程序q创建和使用程序包192ACCP V4.0子程序2-1q命名的PL/SQL块，编译并存储在数据库中。q子程序的各个部分：q声明部分q可执行部分q异常处理部分(可选)q子程序的分类：q过程执行某些操作q函数执行操作并返回值193ACCP V4.0子程序2-2子程序的优点：q模块化q将程序分解为逻辑模块q可重用性q可以被任意数目的程序调用q可维护性q简化维护操作q安全性q通过设置权限，使数据更安全194ACCP V4.0过程8-1q过程是用于完成特定任务的子程序q例如：前往售票厅询问关于车票的信息排队等候在柜台购买车票195ACCP V4.0过程8-2创建过程的语法：CREATEORREPLACEPROCEDURE()IS|ASBEGINEXCEPTIONEND;创建过程，可指定运行过程需传递的参数处理异常包括在过程中要执行的语句196ACCP V4.0过程8-3CREATEORREPLACEPROCEDUREfind_emp(emp_noNUMBER)ASempnameVARCHAR2(20);BEGINSELECTenameINTOempnameFROMEMPWHEREempno=emp_no;DBMS_OUTPUT.PUT_LINE(雇员姓名是|empname);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(雇员编号未找到);ENDfind_emp;/197ACCP V4.0过程8-4过程参数的三种模式：qINq用于接受调用程序的值q默认的参数模式qOUTq用于向调用程序返回值qINOUTq用于接受调用程序的值，并向调用程序返回更新的值198ACCP V4.0过程8-5SQLCREATEORREPLACEPROCEDUREitemdesc(item_codeINVARCHAR2)ISv_itemdescVARCHAR2(5);BEGINSELECTitemdescINTOv_itemdescFROMitemfileWHEREitemcode=item_code;DBMS_OUTPUT.PUT_LINE(item_code|项目的说明为|v_itemdesc);END;/SQLSETSERVEROUTPUTONSQLEXECUTEitemdesc(i201);执行过程的语法： EXECUTE procedure_name(parameters_list);199ACCP V4.0过程8-6SQLCREATEORREPLACEPROCEDUREtest(value1INVARCHAR2,value2OUTNUMBER)ISidentityNUMBER;BEGINSELECTITEMRATEINTOidentityFROMitemFileWHEREitemcode=value1;IFidentitySETSERVEROUTONSQLDECLAREnum1NUMBER:=100;num2NUMBER:=200;BEGINswap(num1,num2);DBMS_OUTPUT.PUT_LINE(num1=|num1);DBMS_OUTPUT.PUT_LINE(num2=|num2);END;/201ACCP V4.0过程8-8q将过程的执行权限授予其他用户：q删除过程：SQLGRANTEXECUTEONfind_empTOMARTIN;SQLGRANTEXECUTEONswapTOPUBLIC;SQLDROPPROCEDUREfind_emp;202ACCP V4.0函数4-1q函数是可以返回值的命名的PL/SQL子程序。q创建函数的语法： CREATEORREPLACEFUNCTION(param1,param2)RETURNIS|ASlocaldeclarationsBEGINExecutableStatements;RETURNresult;EXCEPTIONExceptionhandlers;END;203ACCP V4.0函数4-2q定义函数的限制：q函数只能接受IN参数，而不能接受INOUT或OUT参数q形参不能是PL/SQL类型q函数的返回类型也必须是数据库类型q访问函数的两种方式：q使用PL/SQL块q使用SQL语句204ACCP V4.0函数4-3q创建函数：q从SQL语句调用函数：CREATEORREPLACEFUNCTIONfun_helloRETURNVARCHAR2ISBEGINRETURN朋友，您好;END;/SQLSELECTfun_helloFROMDUAL;205ACCP V4.0函数4-4CREATEORREPLACEFUNCTIONitem_price_range(priceNUMBER)RETURNVARCHAR2ASmin_priceNUMBER;max_priceNUMBER;BEGINSELECTMAX(ITEMRATE),MIN(ITEMRATE)INTOmax_price,min_priceFROMitemfile;IFprice=min_priceANDprice CREATE OR REPLACE PACKAGE cur_pack IS CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE; PROCEDURE ord_pro(vcode VARCHAR2);END cur_pack;/SQL CREATE OR REPLACE PACKAGE BODY cur_pack AS CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE IS SELECT * FROM order_master WHERE VENCODE=vcode; PROCEDURE ord_pro(vcode VARCHAR2) IS or_rec order_master%ROWTYPE; BEGIN OPEN ord_cur(vcode); LOOP FETCH ord_cur INTO or_rec; EXIT WHEN ord_cur%NOTFOUND; DBMS_OUTPUT.PUT_LIne(返回的值为返回的值为 | or_rec.orderno); END LOOP; END ord_pro;END cur_pack;/215ACCP V4.0有关子程序和程序包的信息qUSER_OBJECTS视图包含用户创建的子程序和程序包的信息qUSER_SOURCE视图存储子程序和程序包的源代码SELECT object_name, object_typeFROM USER_OBJECTSWHERE object_type IN (PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY);SELECT line, text FROM USER_SOURCEWHERE NAME=TEST;216ACCP V4.0总结q子程序是命名的PL/SQL块，可带参数并可在需要时随时调用q有两种类型的PL/SQL子程序，即过程和函数q过程用户执行特定的任务，函数用于执行任务并返回值q程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装q程序包由两部分组成，即包规范和包主体q使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳217ACCP V4.0第八章第八章触发器和内置程序包ACCP V4.0回顾q子程序是命名的PL/SQL块，存储在数据库中，可带参数并可在需要时随时调用q有两种类型的PL/SQL子程序，即过程和函数q过程用户执行特定的任务，函数用于执行任务并返回值q程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装q程序包由两部分组成，即包规范和包主体q使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳219ACCP V4.0目标q理解和应用触发器q了解内置程序包220ACCP V4.0触发器q触发器是当特定事件出现时自动执行的存储过程q特定事件可以是执行更新的DML语句和DDL语句q触发器不能被显式调用q触发器的功能：q自动生成数据q自定义复杂的安全权限q提供审计和日志记录q启用复杂的业务逻辑221ACCP V4.0创建触发器的语法CREATEORREPLACETRIGGERtrigger_nameAFTER|BEFORE|INSTEADOFINSERTORUPDATEOFcolumn_listORDELETEONtable_or_view_nameREFERENCINGOLDASold/NEWASnewFOREACHROWWHEN(condition)pl/sql_block;222ACCP V4.0触发器的组成部分3-1触发器由三部分组成：q触发器语句（事件）q定义激活触发器的DML事件和DDL事件q触发器限制q执行触发器的条件，该条件必须为真才能激活触发器q触发器操作（主体）q包含一些SQL语句和代码，它们在发出了触发器语句且触发限制的值为真时运行223ACCP V4.0触发器的组成部分3-2SQLCREATEORREPLACETRIGGERtrig_salAFTERUPDATEOFempsalONsalary_records触发器语句为salary_records表创建trig-sal触发器在更新emp_sal列之后激活触发器触发器限制SQLFOREACHROWWHEN(NEW.empsalOLD.empsal)DECLARESal_diffNUMBER;只有在WHEN子句中的条件得到满足时，才激活trig_sal触发器触发器操作SQLBEGINsal_diff:=:NEW.empsal-:OLD.empsal;DBMS_OUTPUT.PUT_LINE(工资差额：sal_diff);END;如果WHEN子句中的条件得到满足，将执行BEGIN块中的代码224ACCP V4.0触发器的组成部分3-3Oracle数据库更新表表保存更新激活触发器触发器AFTER触发器的工作原理BEFORE触发器的工作原理更新表表激活触发器触发器保存更新Oracle数据库225ACCP V4.0创建触发器CREATEORREPLACETRIGGERaiu_itemfileAFTERINSERTONitemfileFOREACHROWBEGINIF(:NEW.qty_hand=0)THENDBMS_OUTPUT.PUT_LINE(警告：已插入记录，但数量为零);ELSEDBMS_OUTPUT.PUT_LINE(已插入记录);ENDIF;END;/226ACCP V4.0触发器类型6-1q触发器的类型有：触发器类型模式(DDL)触发器DML触发器数据库级触发器语句级触发器行级触发器INSTEADOF触发器227ACCP V4.0触发器类型6-2qDDL触发器q数据库级触发器qDML触发器q语句级触发器q行级触发器qINSTEADOF触发器在模式中执行DDL语句时执行在发生打开、关闭、登录和退出数据库等系统事件时执行在对表或视图执行DML语句时执行无论受影响的行数是多少，都只执行一次对DML语句修改的每个行执行一次用于用户不能直接使用DML语句修改的视图228ACCP V4.0触发器类型6-3q行级触发器SQLCREATETABLETEST_TRG(IDNUMBER,NAMEVARCHAR2(20);SQLCREATESEQUENCESEQ_TEST;SQLCREATEORREPLACETRIGGERBI_TEST_TRGBEFOREINSERTORUPDATEOFIDONTEST_TRGFOREACHROWBEGINIFINSERTINGTHENSELECTSEQ_TEST.NEXTVALINTO:NEW.IDFROMDUAL;ELSERAISE_APPLICATION_ERROR(-20020,不允许更新ID值！);ENDIF;END;/229ACCP V4.0触发器类型6-4SQLCREATEORREPLACETRIGGERtrgdemoAFTERINSERTORUPDATEORDELETEONorder_masterBEGINIFUPDATINGTHENDBMS_OUTPUT.PUT_LINE(已更新ORDER_MASTER中的数据);ELSIFDELETINGTHENDBMS_OUTPUT.PUT_LINE(已删除ORDER_MASTER中的数据);ELSIFINSERTINGTHENDBMS_OUTPUT.PUT_LINE(已在ORDER_MASTER中插入数据);ENDIF;END;/q语句级触发器230ACCP V4.0触发器类型6-5SQLCREATEORREPLACETRIGGERupd_ord_viewINSTEADOFUPDATEONord_viewFOREACHROWBEGINUPDATEorder_masterSETvencode=:NEW.vencodeWHEREorderno=:NEW.orderno;DBMS_OUTPUT.PUT_LINE(已激活触发器);END;/qINSTEADOF触发器231ACCP V4.0触发器类型6-6SQLCREATETABLEdropped_obj(obj_nameVARCHAR2(30),obj_typeVARCHAR2(20),drop_dateDATE);SQLCREATEORREPLACETRIGGERlog_drop_objAFTERDROPONSCHEMABEGININSERTINTOdropped_objVALUES(ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE,SYSDATE);END;/q模式触发器232ACCP V4.0q启用和禁用触发器q删除触发器启用、禁用和删除触发器SQLALTERTRIGGERaiu_itemfileDISABLE;SQLALTERTRIGGERaiu_itemfileENABLE;SQLDROPTRIGGERaiu_itemfile;233ACCP V4.0查看有关触发器的信息SQLSELECTTRIGGER_NAMEFROMUSER_TRIGGERSWHERETABLE_NAME=EMP;SQLSELECTTRIGGER_TYPE,TRIGGERING_EVENT,WHEN_CLAUSEFROMUSER_TRIGGERSWHERETRIGGER_NAME=BIU_EMP_DEPTNO;qUSER_TRIGGERS数据字典视图包含有关触发器的信息234ACCP V4.0内置程序包8-1q扩展数据库的功能q为PL/SQL提供对SQL功能的访问q用户SYS拥有所有程序包q是公有同义词q可以由任何用户访问235ACCP V4.0内置程序包8-2q一些内置程序包：程序包名称程序包名称说明明STANDARD和DBMS_STANDARD定义和扩展PL/SQL语言环境DBMS_LOB提供对LOB数据类型进行操作的功能DBMS_OUTPUT处理PL/SQL块和子程序输出调试信息DBMS_RANDOM提供随机数生成器DBMS_SQL允许用户使用动态SQLDBMS_XMLDOM用DOM模型读写XML类型的数据DBMS_XMLPARSERXML解析，处理XML文档内容和结构DBMS_XMLQUERY提供将数据转换为XML类型的功能DBMS_XSLPROCESSOR提供XSLT功能，转换XML文档UTL_FILE用PL/SQL程序来读写操作系统文本文件236ACCP V4.0内置程序包8-3SQLSETSERVEROUTPUTONSQLBEGINDBMS_OUTPUT.PUT_LINE(打印三角形);FORiIN1.9LOOPFORjIN1.iLOOPDBMS_OUTPUT.PUT(*);ENDLOOPfor_j;DBMS_OUTPUT.NEW_LINE;ENDLOOPfor_i;END;/qDBMS_OUTPUT包显示PL/SQL块和子程序的调试信息。237ACCP V4.0内置程序包8-4qDBMS_LOB包提供用于处理大型对象的过程和函数qDBMS_XMLQUERY包用于将查询结果转换为XML格式238ACCP V4.0内置程序包8-5SQLDECLAREresultCLOB;xmlstrVARCHAR2(32767);lineVARCHAR2(2000);line_noINTEGER:=1;BEGINresult:=DBMS_XMLQuery.getXml(SELECTempno,enameFROMemployee);xmlstr:=DBMS_LOB.SUBSTR(result,32767);LOOPEXITWHENxmlstrISNULL;line:=SUBSTR(xmlstr,1,INSTR(xmlstr,CHR(10)-1);DBMS_OUTPUT.PUT_LINE(line_no|:|line);xmlstr:=SUBSTR(xmlstr,INSTR(xmlstr,CHR(10)+1);line_no:=line_no+1;ENDLOOP;END;/239ACCP V4.0内置程序包8-6SQLSETSERVEROUTPUTONSQLDECLAREl_numNUMBER;counterNUMBER;BEGINcounter:=1;WHILEcounterCREATEDIRECTORYTEST_DIRASC:DEVELOP;SQLGRANTREAD,WRITEONDIRECTORYTEST_DIRTOSCOTT;qUTL_FILE包用于读写操作系统文本文件q操作文件的一般过程是打开、读或写、关闭qUTL_FILE包指定文件路径依赖于DIRECTORY对象241ACCP V4.0内置程序包8-8SQL SET SERVEROUTPUT ONSQL DECLARE input_file UTL_FILE.FILE_TYPE; input_buffer VARCHAR2(4000);BEGIN input_file := UTL_FILE.FOPEN( TEST_DIR, employees.xml, r); LOOP UTL_FILE.GET_LINE(input_file,input_buffer); DBMS_OUTPUT.PUT_LINE(input_buffer); END LOOP; UTL_FILE.FCLOSE(input_file);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(-);END;/242ACCP V4.0总结q触发器是当特定事件出现时自动执行的存储过程q触发器分为DML触发器、DDL触发器和数据库级触发器三种类型qDML触发器的三种类型包括行级触发器、语句级触发器和INSTEADOF触发器q一些常用的内置程序包：qDBMS_OUTPUT包输出PL/SQL程序的调试信息qDBMS_LOB包提供操作LOB数据的子程序qDBMS_XMLQUERY将查询结果转换为XML格式qDBMS_RANDOM提供随机数生成器qUTL_FILE用于读写操作系统文本文件243ACCP V4.0第九章第九章备份与恢复简介ACCP V4.0回顾q触发器是当特定事件出现时自动执行的存储过程q触发器分为DML触发器、DDL触发器和数据库级触发器三种类型qDML触发器的三种类型包括行级触发器、语句级触发器和INSTEADOF触发器q一些常用的内置程序包：qDBMS_OUTPUT包输出PL/SQL程序的调试信息qDBMS_LOB包提供操作LOB数据的子程序qDBMS_XMLQUERY将查询结果转换为XML格式qDBMS_RANDOM提供随机数生成器qUTL_FILE用于读写操作系统文本文件245ACCP V4.0目标q了解数据库备份与恢复的基础知识q理解在Oracle环境中可能发生的故障类型q掌握导出和导入实用程序q了解数据库归档模式246ACCP V4.0备份与恢复简介q备份是数据库中数据的副本，它可以保护数据在出现意外损失时最大限度的恢复qOracle数据库的备份包括以下两种类型：备份物理备份逻辑备份物理备份是对数据库的操作系统物理文件（如数据文件、控制文件和日志文件等）的备份逻辑备份是对数据库逻辑组件（如表、视图和存储过程等数据库对象）的备份247ACCP V4.0故障类型故障类型语句故障介质故障实例故障用户进程故障q导致数据库操作中止的故障包括四种类型：在执行SQL语句过程中发生的逻辑故障可导致语句故障。如果用户编写的SQL语句无效，就会发生逻辑故障当用户程序出错而无法访问数据库时发生用户进程故障。导致用户进程故障的原因是异常断开连接或异常终止进程当Oracle的数据库实例由于硬件或软件问题而无法继续运行时，就会发生实例故障在数据库无法正确读取或写入某个数据库文件时，会发生介质故障248ACCP V4.0导出和导入实用程序4-1q导出和导入实用程序用于实施数据库的逻辑备份和恢复q导出实用程序将数据库中的对象定义和数据备份到一个操作系统二进制文件中q导入实用程序读取二进制导出文件并将对象和数据载入数据库中249ACCP V4.0导出和导入实用程序4-2q导出和导入实用程序的特点有：q可以按时间保存表结构和数据q允许导出指定的表，并重新导入到新的数据库中q可以把数据库迁移到另外一台异构服务器上q在两个不同版本的Oracle数据库之间传输数据q在联机状态下进行备份和恢复q可以重新组织表的存储结构，减少链接及磁盘碎片250ACCP V4.0导出和导入实用程序4-3q使用以下三种方法调用导出和导入实用程序：调用导出和导入程序命令行参数参数文件交互提示符在命令行指定执行程序的参数和参数值。以交互的方式提示用户逐个输入参数的值。允许用户将运行参数和参数值存储在参数文件中，以便重复使用参数251ACCP V4.0导出和导入实用程序4-4q导出和导入数据库对象的四种模式是：导出导入模式完全数据库表空间表用户导出和导入整个数据库中的所有对象导出和导入一个或多个指定的表或表分区导出和导入一个用户模式中的所有对象导出和导入一个或多个指定的表空间中的所有对象252ACCP V4.0导出实用程序2-1q导出实用程序有以下常用命令参数：参数参数说明明USERID确定执行导出实用程序的用户名和口令BUFFER确定导出数据时所使用的缓冲区大小，其大小用字节表示FILE指定导出的二进制文件名称，默认的扩展名是.dmpFULL指定是否以全部数据库方式导出，只有授权用户才可使用此参数OWNER要导出的数据库用户列表HELP指定是否显示帮助消息和参数说明ROWS确定是否要导出表中的数据TABLES按表方式导出时，指定需导出的表和分区的名称PARFILE指定传递给导出实用程序的参数文件名TABLESPACES按表空间方式导出时，指定要导出的表空间名253ACCP V4.0导出实用程序2-2expscott/tigeraccpfile=scott_backowner=scott按用户方式导出数据expscott/tigeraccptables=(emp,dept)file=scott_back_tab按表方式导出数据expsystem/aptechparfile=C:parameters.txt使用参数文件导出数据expsystem/aptechaccptablespaces=(users)file=tbs_users按表空间方式导出数据254ACCP V4.0导出实用程序2-2expscott/tigeraccpfile=scott_backowner=scott按用户方式导出数据expscott/tigeraccptables=(emp,dept)file=scott_back_tab按表方式导出数据expsystem/aptechparfile=C:parameters.txt使用参数文件导出数据expsystem/aptechaccptablespaces=(users)file=tbs_users按表空间方式导出数据255ACCP V4.0导入实用程序2-1q导入实用程序有如下常用命令参数：参数参数说明明USERID指定执行导入的用户名和密码BUFFER指定用来读取数据的缓冲区大小，以字节为单位COMMIT指定是否在每个数组（其大小由BUFFER参数设置）插入后进行提交FILE指定要导入的二进制文件名FROMUSER指定要从导出转储文件中导入的用户模式TOUSER指定要将对象导入的用户名。FROMUSER与TOUSER可以不同FULL指定是否要导入整个导出转储文件TABLES指定要导入的表的列表ROWS指定是否要导入表中的行PARFILE指定传递给导入实用程序的参数文件名，此文件可以包含这里列出的所有参数IGNORE导入时是否忽略遇到的错误，默认为NTABLESPACES按表空间方式导入，列出要导入的表空间名256ACCP V4.0导入实用程序2-2impaccp/accpaccpfile=item_back.dmpignore=yfull=y将整个文件导入数据库impsystem/oracleparfile=C:parameters.txt使用参数文件导入数据impsystem/aptechaccpfile=scott_backfromuser=scotttouser=martintables=(emp,dept)将scott用户的表导入到martin用户257ACCP V4.0数据库归档方式3-1qOracle数据库可以运行在两种归档方式：q非归档日志方式q归档日志方式q非归档日志方式可以避免实例故障，但无法避免介质故障。在此方式下，数据库只能实施冷备份q归档日志方式产生归档日志，用户可以使用归档日志完全恢复数据库258ACCP V4.0数据库归档方式3-2q非归档日志方式下数据库的工作原理：表空间脱机备份表空间恢复表空间表空间联机259ACCP V4.0数据库归档方式3-3q归档日志方式下数据库的工作原理：日志文件1填满清空日志文件2向日志文件2写入信息准备向日志文件2写入信息备份日志文件2260ACCP V4.0配置归档日志方式4-1q配置数据库在归档日志方式下运行，包括以下三个步骤：确保数据库当前不处于归档方式设置相关数据库初始化参数在归档日志方式下启动数据库261ACCP V4.0配置归档日志方式4-2q查看当前数据库的归档方式262ACCP V4.0配置归档日志方式4-3q设置相关启动参数263ACCP V4.0配置归档日志方式4-4q以归档方式启动数据库264ACCP V4.0自动归档和手动归档归档日志方式下的数据库自动归档自动归档 手动归档手动归档 数据库的日志归档方式有两种：手动归档允许用户手动归档非活动日志文件文件的已填充组自动归档对非活动日志文件文件进行自动归档265ACCP V4.0获取归档日志信息SQLSELECTDEST_ID,DEST_NAME,STATUS,DESTINATIONFROMV$ARCHIVE_DESTWHERESTATUS=VALID;目标位置名称目标位置的状态归档日志文件的路径q可以通过数据字典视图查看归档日志信息qV$ARCHIVE_DEST显示当前所有归档日志存储位置及其状态qV$ARCHIVE_LOG显示历史归档日志信息SQLSELECTDEST_ID,NAME,ARCHIVEDFROMV$ARCHIVED_LOG;目标位置编号归档日志文件名及完整路径是否已归档266ACCP V4.0总结q数据库备份用于保护数据库中的数据，有两种类型的备份：物理备份和逻辑备份q导致数据库操作中止的故障类型有：语句故障、用户进程故障、实例故障和介质故障qOracle提供导出和导入实用程序实施数据库的逻辑备份q导出导入实用程序有四种工作模式：完全数据库、表、用户和表空间q数据库可在两种方式下运行：非归档日志方式和归档日志方式267