Oracle术语及其基本操作

1、Oracle基础培训2009-12-24北京橙红创新科技有限公司培训内容容Oracle概述Oracle术语Oracle基本操作作Oracle中Join用法Oracle异常处理理OracleUpdate操作OracleSQL优化Oracle概述1.1什么是ORACLEORACLE，即甲骨骨文股份份有限公公司，成成立于1977年，总部部位于美美国加州州Redwood shore，是全球球最大的的信息管管理软件件及服务务供应商商，也是是最早进进入中国国的跨国国软件巨巨头。甲骨文公公司主要要的产品品目前分分为以下下几大类类：* 服务务器（服服务器）及工具具（主要要竞争对对手：国国际商用用机器、微软）*

2、 数据据库服务务器：最最新版本本11G*应用服务务器：OracleApplicationServer*开发工具具：OracleJDeveloper，OracleDesigner，OracleDeveloper，等* 企业业资源计计划(ERP)软件。已已有10年以上的的历史。2005年，并购购了开发发企业软软件的仁仁科软件件公司（PeopleSoft）以增强强在这方方面的竞竞争力。* 客户户关系管管理(CRM)软件。自自1998年开始研研发这种种软件。2005年，并购购了开发发客户关关系管理理软件的的希柏软软件公司司（Siebel）。Oracle概述1.2Oracle数据库Oracle数据库的的

3、逻辑体体系包括括服务器器和客户户端：OracleServer是一个对对象一关关系数据据库管理理系统。它提供供开放的的、全面面的、和和集成的的信息管管理方法法。每个个Server由一个OracleDB和一个OracleServer实例组成成。每个个Oracle数据库对对应唯一一的一个个实例名名SID，Oracle数据库服服务器启启动后，一般至至少有以以下几个个用户：Internal，它不是是一个真真实的用用户名，而是具具有SYSDBA优先级的的Sys用户的别别名，它它由DBA用户使用用来完成成数据库库的管理理任务，包括启启动和关关闭数据据库；Sys，它是一一个DBA用户名，具有最最大的数数据库操

4、操作权限限；System，它也是是一个DBA用户名，权限仅仅次于Sys用户。Oracle客户端即即为数据据库用户户操作端端，由应应用、工工具、SQL* NET组成，用用户操作作数据库库时，必必须连接接到一服服务器，该数据据库称为为本地数数据库（LocalDB）。在网网络环境境下其它它服务器器上的DB称为远程程数据库库（RemoteDB）。用户户要存取取远程DB上的数据据时，必必须建立立数据库库链。Oracle数据库的的存储结结构：它由至少少一个表表空间和和数据库库模式对对象组成成。这里里，模式式是对象象的集合合，而模模式对象象是直接接引用数数据库数数据的逻逻辑结构构。模式式对象包包括这样样一些

5、结结构：表表、视图图、序列列、存储储过程、同一词词、索引引、簇和和数据库库链等。逻辑存存储结构构包括表表空间、段和范范围，用用于描述述怎样使使用数据据库的物物理空间间。而其其中的模模式对象象和关系系形成了了数据库库的关系系设计。数据块（Block）：是数数据库进进行UO操作的最最小单位位，它与与操作系系统的块块不是一一个概念念。oracle数据库不不是以操操作系统统的块为为单位来来请求数数据，而而是以多多个Oracle数据库块块为单位位。段（Segment）：是表表空间中中一个指指定类型型的逻辑辑存储结结构，它它由一个个或多个个范围组组成，段段将占用用并增长长存储空空间。其中包括括：数据段：用

6、来存存放表数数据；索引段：用来存存放表索索引；临时段：用来存存放中间间结果；回滚段：用于出出现异常常时，恢恢复事务务。范围（Extent）：是数数据库存存储空间间分配的的逻辑单单位，一一个范围围由许多多连续的的数据块块组成，范围是是由段依依此分配配的，分分配的第第一个范范围称为为初始范范围，以以后分配配的范围围称为增增量范围围。Oracle术语2.1数据库名名数据库名名就是一一个数据据库的标标识，就就像人的的身份证证号一样样。他用用参数DB_NAME表示，如如果一台台机器上上装了多多全数据据库，那那么每一一个数据据库都有有一个数数据库名名。在数数据库安安装或创创建完成成之后，参数DB_NAME

7、被写入参参数文件件之中。格式如如下：DB_NAME=myorcl.在创建数数据库时时就应考考虑好数数据库名名，并且且在创建建完数据据库之后后，数据据库名不不宜修改改，即使使要修改改也会很很麻烦。因为，数据库库名还被被写入控控制文件件中，控控制文件件是以二二进制型型式存储储的，用用户无法法修改控控制文件件的内容容。假设设用户修修改了参参数文件件中的数数据库名名，即修修改DB_NAME的值。但但是在Oracle启动时，由于参参数文件件中的DB_NAME与控制文文件中的的数据库库名不一一致，导导致数据据库启动动失败，将返回回ORA-01103错误。Oracle术语数据库实实例名与与Oracle_SI

8、D一个数据据库实例例（Instance） 是由由SGA，后台进进程以及及数据文文件组成成，每个个数据库库有自己己的SGA和独立的的Oracle进程集。ORACLE_SID是操作系系统的环环境变量量。两者都是是oracle实例，但但两者是是有区别别的。instance_name是oracle数据库参参数，而而ORACLD_SID用于与操操作系统统交互，也就是是说，从从操作系系统的角角度访问问实例名名，必须须通过ORACLE_SID。数据库库实例名名除了与与操作系系统交互互外，还还用于网网络连接接的oracle服务器标标识。当当你配置置oracle主机连接接串的时时候，就就需要指指定0实例名。在w

9、innt平台，ORACLE_SID还需存在在于注册册表中。在数据库库安装之之后，ORACLE_SID被用于定定义数据据库参数数文件的的名称。如：$ORACLE_BASE/admin/DB_NAME/pfile/init$ORACLE_SID.ora。Oracle术语2.3表空间一个Oracle数据库由由一个或或多个逻逻辑存储储单元表空间构构成，表表空间存存储着数数据库中中所有的的数据；Oracle数据库中中的每个个表空间间由一个个或多个个文件组组成，这这些文件件就是数数据文件件，它们们是Oracle所在操作作系统上上的物理理结构。Oracle数据库中中的所有有数据物物理的存存储在数数据文件件中

10、。除了普通通表空间间外，Oracle还有几种种特殊的的表空间间。系统统表空间间（SYSTEM表空间）、UNDO表空间（回滚表表空间）、临时时表空间间(TEMP表空间)。系统表空空间中除除了保存存数据字字典外还还保存所所有的存存储过程程、函数数、包、触发器器和对象象。UNDO表空间用用于保存存被DML语句影响响的记录录的原始始状态，以便在在事务失失败的时时候可以以进行回回滚。USER表空间TOOLS表空间当大的排排序操作作发生时时，排序序无法在在内存中中完成，这时会会使用临临时表空空间。表空间可可以设置置为ONLINE和OFFLINE，OFFLINE的表空间间无法进进行访问问。通过过对单独独表空

11、间间的ONLINE和OFFLINE，可以减减少对系系统的影影响。表空间可可以设置置为READ ONLY和READ WRITE两种状态态，只读读的表空空间不可可以进行行锁操作作，因此此甚至可可以放到到只读设设备上如如CDROM。Oracle术语同义词同义词用用于屏蔽蔽基表的的身份，降低sql语句的复复杂性，用户不不必知道道同义词词所代表表的表是是哪一个个表，从从而用户户不需要要指明表表的所有有者的名名称。同义词有有私有和和公有之之分。私私有同义义词是普普通用户户所创建建的同义义词，只只有创建建该同义义词的用用户才能能使用此此同义词词，而公公用同义义词是指指数据库库管理员员创建的的，公用用同义词词

12、允许所所有用户户使用。Oracle术语序列建序列号号主要用用来自动动产生数数据，如如学生号号自动加加1，如果有有很多的的学生，比如10000个，那么么使用序序列较手手工实现现会提高高效率。创建语法法：createsequencenameincrementbyx/x为增长间间隔startwithx/x为初始值值maxvaluex/x为最大值值minvaluex/x为最小值值cycle/循环使用用，到达达最大值值或者最最小值时时，重新新建立对对象cachex/制定缓存存序列值值的个数数创建create sequence sincrement by 10 start with1nomaxvalue;

13、删除dropsequences;使用insert intoavalues(s.nextval,xxx);Oracle基本操作作创建表空空间如何创建建表空间间创建表空空间可以以使用SQL语句，也也可以通通过DBAStudio。使用SYSTEM帐户drop tablespacetestincludingcontents;/删除表空空间createtablespacetest logging/datafiled:test.dbfsize 100Mreuseautoextend on next10M maxsizeunlimited ;createtemporary tablespacetemptes

14、ttempfiled:temptest.dbfsize 10Mreuse;Oracle基本操作作连接ORACLE如何配置置ORACLE客户端如果Oracle服务器在在另外一一台主机机，你要要连接到到数据库库服务器器。那么么需要做做的事情情就是：在自己的的机器安安装ORACLE客户端。配置ORACLE连接：演演示选择”Network Administration”-”Net8Configuration Assistant”进行配置置Oracle基本操作作常用的一一些语句句1.select* fromtab ;查看当前前表和视视图2.selectname fromv$database;查看数据据库

15、3.select* fromnls_database_parameters;查看数据据库参数数设置4.select* fromuser_role_privs;查看当前前用户的的角色5.selectusername,default_tablespace,temporary_tablespacefromuser_users;查看用户户名，默默认/临时表空空间6.selectview_name fromuser_views;查看视图图7.selecttable_name fromuser_tables;查看表（和第1的区别）8.select* fromuser_constraints;查看表的的约束

16、情情况9.select*from user_catalog查看表，视图，同义词词和当前前所有序序列的简简明目录录Oracle中Join用法内连接左连接右连接完全连接接迪卡尔1.内连接：选出两两个表中中所有符符合连接接条件的的row构成的集集合。2.外连接，分为LEFT、RIGHT、FULL三种如有R与S做连接，那么LEFT是所有满满足条件件的row以及所有有在R属性对应应值在S中为NULL的集合。RIGHT是所有满满足条件件的row以及所有有在S属性对应应值在R中为NULL的集合FULL是所有满满足条件件的row以及任何何在R或S中出现NULL的集合Oracle中Join用法innerjoin

17、(等值连接接只返回两两个表中中联结字字段相等等的行1.select*fromTable1 a,Table2 bwherea.id=b.id2.Select*from table 1AINNERJOIN table2B ON A.ID=B.IDOracle中Join用法left join(左联接)返回包括括左表中中的所有有记录和和右表中中联结字字段相等等的记录录select*fromTable1a,table2bwherea.id=b.id(+);SELECT*FROMtable1ALEFTJOINtable2BONA.ID=B.IDOracle中Join用法rightjoin(右联接)返回包括

18、括右表中中的所有有记录和和左表中中联结字字段相等等的记录录select*fromtable1a,table2bwherea.id(+)=b.id;SELECT*FROMtable1 ARIGHTJOIN table2B ON A.ID=B.IDOracle中Join用法完全连接接返回包括括右表中中的所有有记录和和左表中中的所有有记录select*fromtable1a,table2bwherea.id=b.id(+)unionselect*fromTable1a,table2bwherea.id(+)=b.id;SELECT*FROMtable1 AFULL OUTER JOINtable2

19、BONA.ID=B.IDOracle中Join用法迪卡尔返回两个个表相等等字段的的乘积select*fromtable1,table2;Oracle异常处理理1、 异常常的优点点如果没有有异常，在程序序中，应应当检查查每个命命令的成成功还是是失败。2、异常的的分类有两种类类型的异异常，一一种为内内部异常常，一种种为用户户自定义义异常。3、异常的的抛出由三种方方式抛出出异常1 通过过PL/SQL运行时引引擎2.使用RAISE语句(RAISEexception_name; )3.调用RAISE_APPLICATION_ERROR存储过程程.定义DECLAREinventory_too_lowEXC

20、EPTION;Oracle异常处理理4、异常的的处理PL/SQL程序块的的异常部部分包含含了程序序处理错错误的代代码，当当异常被被抛出时时，一个个异常陷陷阱就自自动发生生，程序序控制离离开执行行部分转转入异常常部分,一旦程序序进入异异常部分分就不能能再回到到同一块块的执行行部分。下面是是异常部部分的一一般语法法：EXCEPTIONWHEN exception_name THENCode forhanding exception_nameWHENanother_exceptionTHENCode forhanding another_exceptionWHENothersTHENcode for

21、handing anyotherexception.Oracle异常处理理例子：declareexec_sql_errexception;var_msg varchar(255);Beginupdatetable1 setname=testwhereid=1;ifsql%notfoundthenraiseexec_sql_err;endif;Exceptionwhen othersthenwhen exec_sql_errthenvar_msg :=sqlcode|sqlerrm;dbms_output.put_line(var_msg );End;OracleUpdate用法关于ORACLE

22、的UPDATE更新多表表的问题题一种是：updatetable1set(field1,field2.) =(Select Field1,field2.from table2wheretable1.field1=table2.field1)wheretable1.field1 in (selectfield1 fromtable2)OracleUpdate用法二种是：将table1,table2相关联字字段建立立主键Primary key或UnionkeyUpdate(SelectTable1.field1,table1.field2,table2.field1,table2.field2fro

23、m table1,table2whereTable1.Field1=table2.field1)Settable1.table2=table2.table2,table1.fieldn=table2.tablen,.table1的Field1和table2的field1将需要建建立主键键或唯一一索引才才行OracleUpdate用法三种是：Oracle9i引入了MERGE命令,你能够在在一个SQL语句中对对一个表表同时执执行inserts和updates操作. MERGE命令从一一个或多多个数据据源中选选择行来来updating或inserting到一个或或多个表表.Oracle10g中MER

24、GE有如下一一些改进进：1、UPDATE或INSERT子句是可可选的2、UPDATE和INSERT子句可以以加WHERE子句3、ON条件中使使用常量量过滤谓谓词来insert所有的行行到目标标表中,不需要连连接源表表和目标标表mergeinto table1usingtable2on(table1.field1=table2.field1 and.)when matchedthenUpdateset table1.field2=table2.field2,table1.fieldn=table2.fieldn,.when notmatched thendoing other thing其余方法

25、法可使用用游标;OracleSQL优化1、选择最最有效率率的表名名顺序例如:表TAB1 16,384条记录表TAB21条记录选择TAB2作为基础础表(最好的方方法)selectcount(*)fromtab1,tab2执行时间间0.96秒选择TAB2作为基础础表(不佳的方方法)selectcount(*)fromtab2,tab1执行时间间26.09秒如果有3个以上的的表连接接查询,那就需要要选择交交叉表(intersection table)作为基础础表,交叉表是是指那个个被其他他表所引引用的表表OracleSQL优化2、WHERE子句中的的连接顺顺序ORACLE采用自下下而上的的顺序解解析

26、WHERE子句,根据这个个原理,表之间的的连接必必须写在在其他WHERE条件之前前,那些可以以过滤掉掉最大数数量记录录的条件件必须写写在WHERE子句的末末尾.例如:(低效,执行时间间156.3秒)SELECTFROMEMPEWHERESAL 50000ANDJOB= MANAGERAND25(SELECT COUNT(*) FROMEMPWHEREMGR=E.EMPNO); (高效,执行时间间10.6秒)SELECTFROM EMPEWHERE2550000ANDJOB =MANAGER; OracleSQL优化3、SELECT子句中避避免使用用 *当你想在在SELECT子句中列列出所有有的COLUMN时,使用动态态SQL列引用* 是一一个方便便的方法法.不幸的是是,这是一个个非常低低效的方方法.实际上,ORACLE在解析的的过程中中,会将*依依次转换换成所有有的列名名,这个工作作是通过过查询数数据字典典完成的的,这意味着着将耗费费更多的的时间.4、减少访访问数据据库的次次数当执行每每条SQL语句时, ORACLE在内部执执行了许许多工作作:解析SQL语句,估算索引引的利用用率,绑定变量量,读数据块块等等.由此可见见,减少访问问数据库库的次数数,就能实际际上减少少ORACLE的工作量量. 例如,以下有三三种方法法可以检检索出雇雇员号等等于034