Oracle术语及其基本操作课件

Oracle基础培训2009-12-24北京橙红创新科技有限公司Oracle基础培训2009-12-24北京橙红创新科技有限1培训内容Oracle概述Oracle术语Oracle基本操作Oracle中Join用法Oracle异常处理OracleUpdate操作OracleSQL优化培训内容Oracle概述2Oracle概述1.1什么是ORACLEORACLE，即甲骨文股份有限公司，成立于1977年，总部位于美国加州Redwoodshore，是全球最大的信息管理软件及服务供应商，也是最早进入中国的跨国软件巨头。甲骨文公司主要的产品目前分为以下几大类：*服务器（服务器）及工具（主要竞争对手：国际商用机器、微软）*数据库服务器：最新版本11G*应用服务器：OracleApplicationServer*开发工具：OracleJDeveloper，OracleDesigner，OracleDeveloper，等*企业资源计划(ERP)软件。已有10年以上的历史。2005年，并购了开发企业软件的仁科软件公司（PeopleSoft）以增强在这方面的竞争力。*客户关系管理(CRM)软件。自1998年开始研发这种软件。2005年，并购了开发客户关系管理软件的希柏软件公司（Siebel）。Oracle概述1.1什么是ORACLE3Oracle概述1.2Oracle数据库Oracle数据库的逻辑体系包括服务器和客户端：OracleServer是一个对象一关系数据库管理系统。它提供开放的、全面的、和集成的信息管理方法。每个Server由一个OracleDB和一个OracleServer实例组成。每个Oracle数据库对应唯一的一个实例名SID，Oracle数据库服务器启动后，一般至少有以下几个用户：Internal，它不是一个真实的用户名，而是具有SYSDBA优先级的Sys用户的别名，它由DBA用户使用来完成数据库的管理任务，包括启动和关闭数据库；Sys，它是一个DBA用户名，具有最大的数据库操作权限；System，它也是一个DBA用户名，权限仅次于Sys用户。Oracle客户端即为数据库用户操作端，由应用、工具、SQL*NET组成，用户操作数据库时，必须连接到一服务器，该数据库称为本地数据库（LocalDB）。在网络环境下其它服务器上的DB称为远程数据库（RemoteDB）。用户要存取远程DB上的数据时，必须建立数据库链。Oracle数据库的存储结构：它由至少一个表空间和数据库模式对象组成。这里，模式是对象的集合，而模式对象是直接引用数据库数据的逻辑结构。模式对象包括这样一些结构：表、视图、序列、存储过程、同一词、索引、簇和数据库链等。逻辑存储结构包括表空间、段和范围，用于描述怎样使用数据库的物理空间。而其中的模式对象和关系形成了数据库的关系设计。数据块（Block）：是数据库进行UO操作的最小单位，它与操作系统的块不是一个概念。oracle数据库不是以操作系统的块为单位来请求数据，而是以多个Oracle数据库块为单位。段（Segment）：是表空间中一个指定类型的逻辑存储结构，它由一个或多个范围组成，段将占用并增长存储空间。其中包括：数据段：用来存放表数据；．索引段：用来存放表索引；临时段：用来存放中间结果；回滚段：用于出现异常时，恢复事务。范围（Extent）：是数据库存储空间分配的逻辑单位，一个范围由许多连续的数据块组成，范围是由段依此分配的，分配的第一个范围称为初始范围，以后分配的范围称为增量范围。Oracle概述1.2Oracle数据库4Oracle术语2.1数据库名数据库名就是一个数据库的标识，就像人的身份证号一样。他用参数DB_NAME表示，如果一台机器上装了多全数据库，那么每一个数据库都有一个数据库名。在数据库安装或创建完成之后，参数DB_NAME被写入参数文件之中。格式如下：DB_NAME=myorcl...在创建数据库时就应考虑好数据库名，并且在创建完数据库之后，数据库名不宜修改，即使要修改也会很麻烦。因为，数据库名还被写入控制文件中，控制文件是以二进制型式存储的，用户无法修改控制文件的内容。假设用户修改了参数文件中的数据库名，即修改DB_NAME的值。但是在Oracle启动时，由于参数文件中的DB_NAME与控制文件中的数据库名不一致，导致数据库启动失败，将返回ORA-01103错误。Oracle术语2.1数据库名5Oracle术语数据库实例名与Oracle_SID一个数据库实例（Instance）是由SGA，后台进程以及数据文件组成，每个数据库有自己的SGA和独立的Oracle进程集。ORACLE_SID是操作系统的环境变量。两者都是oracle实例，但两者是有区别的。instance_name是oracle数据库参数，而ORACLD_SID用于与操作系统交互，也就是说，从操作系统的角度访问实例名，必须通过ORACLE_SID。数据库实例名除了与操作系统交互外，还用于网络连接的oracle服务器标识。当你配置oracle主机连接串的时候，就需要指定0实例名。在winnt平台，ORACLE_SID还需存在于注册表中。在数据库安装之后，ORACLE_SID被用于定义数据库参数文件的名称。如：$ORACLE_BASE/admin/DB_NAME/pfile/init$ORACLE_SID.ora。Oracle术语数据库实例名与Oracle_SID6Oracle术语2.3表空间一个Oracle数据库由一个或多个逻辑存储单元——表空间构成，表空间存储着数据库中所有的数据；Oracle数据库中的每个表空间由一个或多个文件组成，这些文件就是数据文件，它们是Oracle所在操作系统上的物理结构。Oracle数据库中的所有数据物理的存储在数据文件中。除了普通表空间外，Oracle还有几种特殊的表空间。系统表空间（SYSTEM表空间）、UNDO表空间（回滚表空间）、临时表空间(TEMP表空间)。系统表空间中除了保存数据字典外还保存所有的存储过程、函数、包、触发器和对象。UNDO表空间用于保存被DML语句影响的记录的原始状态，以便在事务失败的时候可以进行回滚。USER表空间TOOLS表空间当大的排序操作发生时，排序无法在内存中完成，这时会使用临时表空间。表空间可以设置为ONLINE和OFFLINE，OFFLINE的表空间无法进行访问。通过对单独表空间的ONLINE和OFFLINE，可以减少对系统的影响。表空间可以设置为READONLY和READWRITE两种状态，只读的表空间不可以进行锁操作，因此甚至可以放到只读设备上如CDROM。Oracle术语2.3表空间7Oracle术语同义词同义词用于屏蔽基表的身份，降低sql语句的复杂性，用户不必知道同义词所代表的表是哪一个表，从而用户不需要指明表的所有者的名称。同义词有私有和公有之分。私有同义词是普通用户所创建的同义词，只有创建该同义词的用户才能使用此同义词，而公用同义词是指数据库管理员创建的，公用同义词允许所有用户使用。Oracle术语同义词8Oracle术语序列建序列号主要用来自动产生数据，如学生号自动加1，如果有很多的学生，比如10000个，那么使用序列较手工实现会提高效率。创建语法：createsequencenameincrementbyx//x为增长间隔startwithx//x为初始值maxvaluex//x为最大值minvaluex//x为最小值cycle//循环使用，到达最大值或者最小值时，重新建立对象cachex//制定缓存序列值的个数创建>createsequencesincrementby10startwith1nomaxvalue;删除>dropsequences;使用>insertintoavalues(s.nextval,xxx);Oracle术语序列9Oracle基本操作创建表空间如何创建表空间创建表空间可以使用SQL语句，也可以通过DBAStudio。使用SYSTEM帐户

droptablespacetestincludingcontents;//删除表空间createtablespacetestlogging//datafile'd:\test.dbf'size100Mreuseautoextendonnext10Mmaxsizeunlimited;createtemporarytablespacetemptesttempfile'd:temptest.dbf'size10Mreuse;Oracle基本操作创建表空间10Oracle基本操作连接ORACLE

如何配置ORACLE客户端如果Oracle服务器在另外一台主机，你要连接到数据库服务器。那么需要做的事情就是：在自己的机器安装ORACLE客户端。配置ORACLE连接：演示选择”NetworkAdministration”--”Net8ConfigurationAssistant”进行配置Oracle基本操作连接ORACLE11Oracle基本操作常用的一些语句1.select*fromtab;查看当前表和视图2.selectnamefromv$database;查看数据库3.select*fromnls_database_parameters;查看数据库参数设置4.select*fromuser_role_privs;查看当前用户的角色5.selectusername,default_tablespace,temporary_tablespacefromuser_users;查看用户名，默认/临时表空间6.selectview_namefromuser_views;查看视图7.selecttable_namefromuser_tables;查看表（和第1的区别）8.select*fromuser_constraints;查看表的约束情况9.select*fromuser_catalog查看表，视图，同义词和当前所有序列的简明目录Oracle基本操作常用的一些语句12Oracle中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用法内连接13Oracle中Join用法innerjoin(等值连接

只返回两个表中联结字段相等的行1.select

*

from

Table1a,Table2bwherea.id=b.id2.Select*fromtable1

A

INNERJOINtable2BONA.ID=B.IDOracle中Join用法innerjoin(等值连接14Oracle中Join用法leftjoin(左联接)返回包括左表中的所有记录和右表中联结字段相等的记录select

*

from

Table1

a,

table2

b

where

a.id=b.id(+);

SELECT*FROM

table1A

LEFTJOINtable2BONA.ID=B.IDOracle中Join用法leftjoin(左联接)15Oracle中Join用法rightjoin(右联接)

返回包括右表中的所有记录和左表中联结字段相等的记录select

*

from

table1

a,

table2

b

where

a.id(+)=b.id;SELECT*FROM

table1A

RIGHTJOINtable2BONA.ID=B.IDOracle中Join用法rightjoin(右联接)16Oracle中Join用法完全连接返回包括右表中的所有记录和左表中的所有记录select

*

from

table1

a,

table2

b

where

a.id=b.id(+)

union

select

*

from

Table1

a,

table2

b

where

a.id(+)=b.id;SELECT*FROM

table1A

FULLOUTERJOINtable2BONA.ID=B.IDOracle中Join用法完全连接17Oracle中Join用法迪卡尔

返回两个表相等字段的乘积select

*

from

table1,

table2;Oracle中Join用法迪卡尔18Oracle异常处理1、异常的优点如果没有异常，在程序中，应当检查每个命令的成功还是失败。2、异常的分类有两种类型的异常，一种为内部异常，一种为用户自定义异常。3、异常的抛出由三种方式抛出异常1．通过PL/SQL运行时引擎2.使用RAISE语句(RAISEexception_name;)3.调用RAISE_APPLICATION_ERROR存储过程.定义DECLAREinventory_too_lowEXCEPTION;Oracle异常处理1、异常的优点19Oracle异常处理4、异常的处理PL/SQL程序块的异常部分包含了程序处理错误的代码，当异常被抛出时，一个异常陷阱就自动发生，程序控制离开执行部分转入异常部分,一旦程序进入异常部分就不能再回到同一块的执行部分。下面是异常部分的一般语法：EXCEPTION

WHENexception_nameTHEN

Codeforhandingexception_name

[WHENanother_exceptionTHEN

Codeforhandinganother_exception]

[WHENothersTHEN

codeforhandinganyotherexception.]Oracle异常处理4、异常的处理20Oracle异常处理例子：declareexec_sql_errexception;var_msgvarchar(255);Beginupdatetable1setname=‘test’whereid=1;ifsql%notfoundthenraiseexec_sql_err;endif;Exceptionwhenothersthenwhenexec_sql_errthenvar_msg:=sqlcode||sqlerrm;dbms_output.put_line(var_msg);End;Oracle异常处理例子：21OracleUpdate用法关于ORACLE的UPDATE更新多表的问题一种是：

updatetable1set(field1,field2...)=(SelectField1,field2....fromtable2wheretable1.field1=table2.field1)wheretable1.field1in(selectfield1fromtable2)OracleUpdate用法关于ORACLE的UPDATE22OracleUpdate用法二种是：将table1,table2相关联字段建立主键Primarykey或UnionkeyUpdate(SelectTable1.field1,table1.field2,table2.field1,table2.field2fromtable1,table2whereTable1.Field1=table2.field1)Settable1.table2=table2.table2,table1.fieldn=table2.tablen,......table1的Field1和table2的field1将需要建立主键或唯一索引才行OracleUpdate用法二种是：23OracleUpdate用法三种是：Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作.MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.Oracle10g中MERGE有如下一些改进：1、UPDATE或INSERT子句是可选的2、UPDATE和INSERT子句可以加WHERE子句3、ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表mergeintotable1usingtable2on(table1.field1=table2.field1and....)whenmatchedthenUpdatesettable1.field2=table2.field2,table1.fieldn=table2.fieldn,......whennotmatchedthen[doingotherthing]其余方法可使用游标;OracleUpdate用法三种是：24OracleSQL优化1、选择最有效率的表名顺序例如:

表

TAB116,384条记录

表

TAB21条记录

选择TAB2作为基础表

(最好的方法)selectcount(*)fromtab1,tab2执行时间0.96秒

选择TAB2作为基础表

(不佳的方法)selectcount(*)fromtab2,tab1执行时间26.09秒如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表OracleSQL优化1、选择最有效率的表名顺序25OracleSQL优化2、WHERE子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.例如:(低效,执行时间156.3秒)SELECT…FROMEMPEWHERESAL>50000ANDJOB=‘MANAGER’AND25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);

(高效,执行时间10.6秒)SELECT…FROMEMPEWHERE25<(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL>50000ANDJOB=‘MANAGER’;

OracleSQL优化2、WHERE子句中的连接顺序26OracleSQL优化3、SELECT子句中避免使用‘*‘当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*’是一个方便的方法.不幸的是,这是一个非常低效的方法.实际上,ORACLE在解析的过程中,会将’*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间.4、减少访问数据库的次数当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量.

例如,

以下有三种方法可以检索出雇员号等于0342或0291的职员.

OracleSQL优化3、SELECT子句中避免使用‘27OracleSQL优化方法1(最低效)SELECTENAME,SALFROMEMPWHEREEMPNO=342;SELECTENAME,SALFROMEMPWHEREEMPNO=291;方法2(高效)SELECTA.ENAME,A.SAL,B.ENAME,B.SALFROMEMPA,EMPBWHEREA.EMPNO=342ANDB.EMPNO=291;5.尽量多使用COMMIT只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:COMMIT所释放的资源:a.回滚段上用于恢复数据的信息.b.被程序语句获得的锁OracleSQL优化方法1(最低效)28OracleSQL优化6、用Where子句替换HAVING子句避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.例如:

低效:SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONGROUPBYREGIONHAVINGREGION!=‘SYDNEY’ANDREGION!=‘PERTH’

高效

SELECTREGION，AVG(LOG_SIZE)FROMLOCATIONWHEREREGIONREGION!=‘SYDNEY’ANDREGION!=‘PERTH’GROUPBYREGIONOracleSQL优化6、用Where子句替换HAVING29OracleSQL优化7.用EXISTS替代IN8.用NOTEXISTS替代NOTIN9.用表连接替换EXISTS

通常来说,采用表连接的方式比EXISTS更有效率

SELECTENAMEFROMEMPEWHEREEXISTS(SELECT‘X’FROMDEPTWHEREDEPT_NO=E.DEPT_NOANDDEPT_CAT=‘A’);(更高效)SELECTENAMEFROMDEPTD,EMPEWHEREE.DEPT_NO=D.DEPT_NOANDDEPT_CAT=‘A’;OracleSQL优化7.用EXISTS替代IN30谢谢！Oracle术语及其基本操作课件31Oracle基础培训2009-12-24北京橙红创新科技有限公司Oracle基础培训2009-12-24北京橙红创新科技有限32培训内容Oracle概述Oracle术语Oracle基本操作Oracle中Join用法Oracle异常处理OracleUpdate操作OracleSQL优化培训内容Oracle概述33Oracle概述1.1什么是ORACLEORACLE，即甲骨文股份有限公司，成立于1977年，总部位于美国加州Redwoodshore，是全球最大的信息管理软件及服务供应商，也是最早进入中国的跨国软件巨头。甲骨文公司主要的产品目前分为以下几大类：*服务器（服务器）及工具（主要竞争对手：国际商用机器、微软）*数据库服务器：最新版本11G*应用服务器：OracleApplicationServer*开发工具：OracleJDeveloper，OracleDesigner，OracleDeveloper，等*企业资源计划(ERP)软件。已有10年以上的历史。2005年，并购了开发企业软件的仁科软件公司（PeopleSoft）以增强在这方面的竞争力。*客户关系管理(CRM)软件。自1998年开始研发这种软件。2005年，并购了开发客户关系管理软件的希柏软件公司（Siebel）。Oracle概述1.1什么是ORACLE34Oracle概述1.2Oracle数据库Oracle数据库的逻辑体系包括服务器和客户端：OracleServer是一个对象一关系数据库管理系统。它提供开放的、全面的、和集成的信息管理方法。每个Server由一个OracleDB和一个OracleServer实例组成。每个Oracle数据库对应唯一的一个实例名SID，Oracle数据库服务器启动后，一般至少有以下几个用户：Internal，它不是一个真实的用户名，而是具有SYSDBA优先级的Sys用户的别名，它由DBA用户使用来完成数据库的管理任务，包括启动和关闭数据库；Sys，它是一个DBA用户名，具有最大的数据库操作权限；System，它也是一个DBA用户名，权限仅次于Sys用户。Oracle客户端即为数据库用户操作端，由应用、工具、SQL*NET组成，用户操作数据库时，必须连接到一服务器，该数据库称为本地数据库（LocalDB）。在网络环境下其它服务器上的DB称为远程数据库（RemoteDB）。用户要存取远程DB上的数据时，必须建立数据库链。Oracle数据库的存储结构：它由至少一个表空间和数据库模式对象组成。这里，模式是对象的集合，而模式对象是直接引用数据库数据的逻辑结构。模式对象包括这样一些结构：表、视图、序列、存储过程、同一词、索引、簇和数据库链等。逻辑存储结构包括表空间、段和范围，用于描述怎样使用数据库的物理空间。而其中的模式对象和关系形成了数据库的关系设计。数据块（Block）：是数据库进行UO操作的最小单位，它与操作系统的块不是一个概念。oracle数据库不是以操作系统的块为单位来请求数据，而是以多个Oracle数据库块为单位。段（Segment）：是表空间中一个指定类型的逻辑存储结构，它由一个或多个范围组成，段将占用并增长存储空间。其中包括：数据段：用来存放表数据；．索引段：用来存放表索引；临时段：用来存放中间结果；回滚段：用于出现异常时，恢复事务。范围（Extent）：是数据库存储空间分配的逻辑单位，一个范围由许多连续的数据块组成，范围是由段依此分配的，分配的第一个范围称为初始范围，以后分配的范围称为增量范围。Oracle概述1.2Oracle数据库35Oracle术语2.1数据库名数据库名就是一个数据库的标识，就像人的身份证号一样。他用参数DB_NAME表示，如果一台机器上装了多全数据库，那么每一个数据库都有一个数据库名。在数据库安装或创建完成之后，参数DB_NAME被写入参数文件之中。格式如下：DB_NAME=myorcl...在创建数据库时就应考虑好数据库名，并且在创建完数据库之后，数据库名不宜修改，即使要修改也会很麻烦。因为，数据库名还被写入控制文件中，控制文件是以二进制型式存储的，用户无法修改控制文件的内容。假设用户修改了参数文件中的数据库名，即修改DB_NAME的值。但是在Oracle启动时，由于参数文件中的DB_NAME与控制文件中的数据库名不一致，导致数据库启动失败，将返回ORA-01103错误。Oracle术语2.1数据库名36Oracle术语数据库实例名与Oracle_SID一个数据库实例（Instance）是由SGA，后台进程以及数据文件组成，每个数据库有自己的SGA和独立的Oracle进程集。ORACLE_SID是操作系统的环境变量。两者都是oracle实例，但两者是有区别的。instance_name是oracle数据库参数，而ORACLD_SID用于与操作系统交互，也就是说，从操作系统的角度访问实例名，必须通过ORACLE_SID。数据库实例名除了与操作系统交互外，还用于网络连接的oracle服务器标识。当你配置oracle主机连接串的时候，就需要指定0实例名。在winnt平台，ORACLE_SID还需存在于注册表中。在数据库安装之后，ORACLE_SID被用于定义数据库参数文件的名称。如：$ORACLE_BASE/admin/DB_NAME/pfile/init$ORACLE_SID.ora。Oracle术语数据库实例名与Oracle_SID37Oracle术语2.3表空间一个Oracle数据库由一个或多个逻辑存储单元——表空间构成，表空间存储着数据库中所有的数据；Oracle数据库中的每个表空间由一个或多个文件组成，这些文件就是数据文件，它们是Oracle所在操作系统上的物理结构。Oracle数据库中的所有数据物理的存储在数据文件中。除了普通表空间外，Oracle还有几种特殊的表空间。系统表空间（SYSTEM表空间）、UNDO表空间（回滚表空间）、临时表空间(TEMP表空间)。系统表空间中除了保存数据字典外还保存所有的存储过程、函数、包、触发器和对象。UNDO表空间用于保存被DML语句影响的记录的原始状态，以便在事务失败的时候可以进行回滚。USER表空间TOOLS表空间当大的排序操作发生时，排序无法在内存中完成，这时会使用临时表空间。表空间可以设置为ONLINE和OFFLINE，OFFLINE的表空间无法进行访问。通过对单独表空间的ONLINE和OFFLINE，可以减少对系统的影响。表空间可以设置为READONLY和READWRITE两种状态，只读的表空间不可以进行锁操作，因此甚至可以放到只读设备上如CDROM。Oracle术语2.3表空间38Oracle术语同义词同义词用于屏蔽基表的身份，降低sql语句的复杂性，用户不必知道同义词所代表的表是哪一个表，从而用户不需要指明表的所有者的名称。同义词有私有和公有之分。私有同义词是普通用户所创建的同义词，只有创建该同义词的用户才能使用此同义词，而公用同义词是指数据库管理员创建的，公用同义词允许所有用户使用。Oracle术语同义词39Oracle术语序列建序列号主要用来自动产生数据，如学生号自动加1，如果有很多的学生，比如10000个，那么使用序列较手工实现会提高效率。创建语法：createsequencenameincrementbyx//x为增长间隔startwithx//x为初始值maxvaluex//x为最大值minvaluex//x为最小值cycle//循环使用，到达最大值或者最小值时，重新建立对象cachex//制定缓存序列值的个数创建>createsequencesincrementby10startwith1nomaxvalue;删除>dropsequences;使用>insertintoavalues(s.nextval,xxx);Oracle术语序列40Oracle基本操作创建表空间如何创建表空间创建表空间可以使用SQL语句，也可以通过DBAStudio。使用SYSTEM帐户

droptablespacetestincludingcontents;//删除表空间createtablespacetestlogging//datafile'd:\test.dbf'size100Mreuseautoextendonnext10Mmaxsizeunlimited;createtemporarytablespacetemptesttempfile'd:temptest.dbf'size10Mreuse;Oracle基本操作创建表空间41Oracle基本操作连接ORACLE

如何配置ORACLE客户端如果Oracle服务器在另外一台主机，你要连接到数据库服务器。那么需要做的事情就是：在自己的机器安装ORACLE客户端。配置ORACLE连接：演示选择”NetworkAdministration”--”Net8ConfigurationAssistant”进行配置Oracle基本操作连接ORACLE42Oracle基本操作常用的一些语句1.select*fromtab;查看当前表和视图2.selectnamefromv$database;查看数据库3.select*fromnls_database_parameters;查看数据库参数设置4.select*fromuser_role_privs;查看当前用户的角色5.selectusername,default_tablespace,temporary_tablespacefromuser_users;查看用户名，默认/临时表空间6.selectview_namefromuser_views;查看视图7.selecttable_namefromuser_tables;查看表（和第1的区别）8.select*fromuser_constraints;查看表的约束情况9.select*fromuser_catalog查看表，视图，同义词和当前所有序列的简明目录Oracle基本操作常用的一些语句43Oracle中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用法内连接44Oracle中Join用法innerjoin(等值连接

只返回两个表中联结字段相等的行1.select

*

from

Table1a,Table2bwherea.id=b.id2.Select*fromtable1

A

INNERJOINtable2BONA.ID=B.IDOracle中Join用法innerjoin(等值连接45Oracle中Join用法leftjoin(左联接)返回包括左表中的所有记录和右表中联结字段相等的记录select

*

from

Table1

a,

table2

b

where

a.id=b.id(+);

SELECT*FROM

table1A

LEFTJOINtable2BONA.ID=B.IDOracle中Join用法leftjoin(左联接)46Oracle中Join用法rightjoin(右联接)

返回包括右表中的所有记录和左表中联结字段相等的记录select

*

from

table1

a,

table2

b

where

a.id(+)=b.id;SELECT*FROM

table1A

RIGHTJOINtable2BONA.ID=B.IDOracle中Join用法rightjoin(右联接)47Oracle中Join用法完全连接返回包括右表中的所有记录和左表中的所有记录select

*

from

table1

a,

table2

b

where

a.id=b.id(+)

union

select

*

from

Table1

a,

table2

b

where

a.id(+)=b.id;SELECT*FROM

table1A

FULLOUTERJOINtable2BONA.ID=B.IDOracle中Join用法完全连接48Oracle中Join用法迪卡尔

返回两个表相等字段的乘积select

*

from

table1,

table2;Oracle中Join用法迪卡尔49Oracle异常处理1、异常的优点如果没有异常，在程序中，应当检查每个命令的成功还是失败。2、异常的分类有两种类型的异常，一种为内部异常，一种为用户自定义异常。3、异常的抛出由三种方式抛出异常1．通过PL/SQL运行时引擎2.使用RAISE语句(RAISEexception_name;)3.调用RAISE_APPLICATION_ERROR存储过程.定义DECLAREinventory_too_lowEXCEPTION;Oracle异常处理1、异常的优点50Oracle异常处理4、异常的处理PL/SQL程序块的异常部分包含了程序处理错误的代码，当异常被抛出时，一个异常陷阱就自动发生，程序控制离开执行部分转入异常部分,一旦程序进入异常部分就不能再回到同一块的执行部分。下面是异常部分的一般语法：EXCEPTION

WHENexception_nameTHEN

Codeforhandingexception_name

[WHENanother_exceptionTHEN

Codeforhandinganother_exception]

[WHENothersTHEN

codeforhandinganyotherexception.]Oracle异常处理4、异常的处理51Oracle异常处理例子：declareexec_sql_errexception;var_msgvarchar(255);Beginupdatetable1setname=‘test’whereid=1;ifsql%notfoundthenraiseexec_sql_err;endif;Exceptionwhenothersthenwhenexec_sql_errthenvar_msg:=sqlcode||sqlerrm;dbms_output.put_line(var_msg);End;Oracle异常处理例子：52OracleUpdate用法关于ORACLE的UPDATE更新多表的问题一种是：

updatetable1set(field1,field2...)=(SelectField1,field2....fromtable2wheretable1.field1=table2.field1)wheretable1.field1in(selectfield1fromtable2)OracleUpdate用法关于ORACLE的UPDATE53OracleUpdate用法二种是：将table1,table2相关联字段建立主键Primarykey或UnionkeyUpdate(SelectTable1.field1,table1.field2,table2.field1,table2.field2fromtable1,table2whereTable1.Field1=table2.field1)Settable1.table2=table2.table2,table1.fieldn=table2.tablen,......table1的Field1和table2的field1将需要建立主键或唯一索引才行OracleUpdate用法二种是：54OracleUpdate用法三种是：Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作.MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.Oracle10g中MERGE有如下一些改进：1、UPDATE或INSERT子句是可选的2、UPDATE和INSERT子句可以加WHERE子句3、ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表mergeintotable1usingtable2on(table1.field1=table2.field1and....)whenmatchedthenUpdatesettable1.field2=table2.field2,table1.fieldn=table2.fieldn,......whennotmatchedthen[doingotherthing]其余方法可使用游标;OracleUpdate用法三种是：55OracleSQL优化1、选择最有效率的表名顺序例如:

表

TAB116,384条记录

表

TAB21条记录

选择TAB2作为基础表

(最好的方法)selectcount(*)fromtab1,tab2执行时间0.96秒

选择TAB2作为基础表

(不佳的方法)selectcount(*)fromtab2,tab1执行时间26.09秒如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表OracleSQL优化1、选择最有效率的表名顺序56OracleSQL优化2、WHERE子句中的连接顺序

ORACLE采用