Oracle基础知识专项培训课件

第1页神州数码思特奇信息技术股份有限公司Oracle基础培训胡继钢第1页神州数码思特奇信息技术股份有限公司Oracle基础培1内容介绍Oracle基础(*)使用DDL(*)使用DML(*)PL/SQL基础知识(*)PROC*C基础知识(*)Oracle基础内容介绍Oracle基础2配置.ora文件客户端tnsnames.ora文件的位置和作用文件位置

UNIX:$ORACLE_HOME/network/admin WIN:ORACLE_HOME\net80\admin作用

保存着连接到服务器监听端口需要的地址和端口号信息 可配置多个服务器监听端口

配置.ora文件客户端tnsnames.ora文件的位置和作3配置.ora文件客户端tnsnames.ora的基本内容配置.ora文件客户端tnsnames.ora的基本内容4在UNIX下配置和使用Oracle属性文件(.profileetc.)我们关注的ORACLE_SID=las;exportORACLE_SIDORACLE_OWNER=oracle;exportORACLE_OWNERORACLE_BASE=/oracle/oracle/app/oracle;exportORACLE_BASEORACLE_HOME=$ORACLE_BASE/product/8.0.6;exportORACLE_HOMEORACLE_DOC=$ORACLE_HOME/doc;exportORACLE_DOCORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data;exportORA_NLS33TMPDIR=/tmp;exportTMPDIRLD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib;exportLD_LIBRARY_PATHPATH=$PATH:$ORACLE_HOME/bin:/usr/lbin:.;exportPATHNLS_LANG=American_America.zhs16gbk;exportNLS_LANGSHLIB_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib:/usr/lib;exportSHLIB_PATH在UNIX下配置和使用Oracle属性文件(.profile5用户、安全和模式用户、安全和模式6内容介绍Oracle基础管理(*)使用DML(*)使用DDL(*)PL/SQL基础知识(*)PROC*C基础知识(*)Oracle基础内容介绍Oracle基础7使用DML内容介绍常用SQL语句常用内部函数的使用构造高效率的SQL使用DML内容介绍8使用DML－查询数据基本的Select查询语法

SELECTcolumn_lists FROMtable_lists WHEREconditions GROUPBYcolumn_lists HAVINGconditions ORDERBYcolumn_lists;使用DML－查询数据基本的Select查询语法9使用DML－查询数据选择数据SELECTlast_name,first_name,specialty FROMartist;排列数据使用ORDERBY降序：DESC升序：ASC（默认）SELECTlast_name,first_name,specialty FROMartist ORDERBYfirst_nameDESC;使用DML－查询数据选择数据10使用DML－查询数据约束结果使用WHERESELECTlast_name,first_name,specialty FROMartist WHEREspecialty=‘DIGITAL’;比较运算符＝，<>>，<，>=，<=LIKE ‘%’匹配字符串，‘_’匹配字符BETWEEN，INISNULL，ISNOTNULL使用DML－查询数据约束结果11使用DML－查询数据使用WHERE的注意事项引用的字符必须在单引号内，而不是双引号如果需要在字符串内放单引号，应该写两次，例如想比较won’t，在WHERE中应写成‘won’’t’注意AND和OR的使用要考虑空值的影响使用DML－查询数据使用WHERE的注意事项12使用DML－查询数据关联表Cartesian产品使用DML－查询数据关联表13使用DML－查询数据使用相等关联来比较列SELECTbook_id,book_title,bl_book_id,bl_student_idFROMbooks,books_loanedWHEREbook_id=bl_book_id;使用表的别名SELECTb.book_id,b.book_title, bl.bl_book_id,bl.bl_student_id, s.student_id,s.first_name,s_last_nameFROMbooksb,books_loanedbl,studentssWHEREb.book_id=bl.bl_book_id andbl.bl_student_id=s.student.id;使用DML－查询数据使用相等关联来比较列14使用DML－查询数据内部关联 两个表之间的内部关联返回两个表中都符合的记录的行。 使用DML－查询数据内部关联15使用DML－查询数据外部关联 外部关联关联了两个表并使得即使第二个表没有与第一个表符合的记录时也能返回结果。使用DML－查询数据外部关联16使用DML－查询数据使用GROUPBY子句原理Oracle对查询检索数据并在临时存储区中保存；Oracle对数据排序，并和GROUPBY值一起对所有数据分组；Oracle把指定的聚集函数应用到每个记录组并对每组返回一行。注意由于求和查询对每组返回一行，所以在选择列中不可能有GROUPBY子句中没有列出的列，除非它们应用了一个聚集函数。使用DML－查询数据使用GROUPBY子句17使用DML－查询数据例子正确

SELECTspecialty,COUNT(*) FROMartist GROUPBYspecialty;错误 SELECTspecialty,last_name,first_name,COUNT(*) FROMartist GROUPBYspecialty;使用DML－查询数据例子18使用DML－查询数据使用HAVING子句 HAVING和WHERE的区别在于HAVING子句中的条件是被求和后应用到数据，它只对指定组有兴趣。 SELECTspecialty FROMartist GROUPBYspecialty HAVINGCOUNT(*)=1ANDspecialty<>’OTHER’; SELECTspecialty FROMartist WHEREspecialty<>’OTHER’ GROUPBYspecialty HAVINGCOUNT(*)=1;使用DML－查询数据使用HAVING子句19使用DML－查询数据使用DISTINCT和ALL关键字 默认为ALL SELECTCOUNT(DISTINCTspcialty) FROMartist; SELECTCOUNT(spcialty) FROMartist;使用DML－查询数据使用DISTINCT和ALL关键字20使用DML－修改数据插入数据基本形式 INSERTINTOtablename (columnname1,columnname2,…) VALUES(value1,value2,…);子查询形式 INSERTINTOtablename (columnname1,columnname2,…) subquery;使用DML－修改数据插入数据21使用DML－修改数据更新数据基本形式 UPDATEtablename SETcolumnname1=expression1, columnname2=expression2, … WHEREconditions;子查询形式 UPDATEtablename SET(columnname1,columnname2,…)= (SELECTcolumnname3,columnname4,… FROMtablename2 WHEREconditions) WHEREconditions;使用DML－修改数据更新数据22使用DML－修改数据删除数据基本形式 DELETEFROMtablename WHEREconditions;子查询形式 DELETEFROMtablename WHEREsubquery;使用DML－修改数据删除数据23使用DML－常用内部函数常用内部函数(1)－通用函数LOWER()函数UPPER()函数DECODE()函数INSTR()函数SUBSTR()函数NVL()函数LENGTH()函数常用内部函数(2)－数字函数ROUND()函数TRUNC()函数MOD()函数连接运算符：||使用DML－常用内部函数常用内部函数(1)－通用函数24使用DML－常用内部函数常用内部函数(3)－日期函数SYSDATEMONTHS_BETWEEN()函数NEXT_DAY()函数LAST_DAY()函数ADD_MONTHS()函数ROUND()函数TRUNC()函数常用内部函数(4)－格式转换函数TO_CHAR()函数TO_DATE()函数TO_NUMBER()函数常用的格式转化使用DML－常用内部函数常用内部函数(3)－日期函数25使用DML－常用内部函数常用内部函数(5)－聚合函数AVG()函数COUNT()函数MAX()函数MIN()函数SUM()函数使用DML－常用内部函数常用内部函数(5)－聚合函数26使用DML－构造高效率的SQL内容提要使用索引应该避免的问题使用DML－构造高效率的SQL内容提要27使用DML－构造高效率的SQL使用索引索引可以成百上千倍的提高SQL语句效率查询系统中的索引按照索引的顺序访问表使用效率最高的索引使用DML－构造高效率的SQL使用索引28使用DML－构造高效率的SQL应该避免的问题尽量不要在检索条件中对列使用函数，因为会阻止索引的使用如果可以使用where条件，尽量不要在having中限制数据尽量不要使数据排序引起排序的条件OrderbyGroupbyUnion,intersect,minusDistinct谨慎使用in,exists使用DML－构造高效率的SQL应该避免的问题29内容介绍Oracle基础管理(*)使用DML(*)使用DDL(*)PL/SQL基础知识(*)PROC*C基础知识(*)Oracle基础内容介绍Oracle基础30使用DDL－管理表了解表表是关系数据库的基础，它们典型的由下面几个主要元素组成：列 －定义存储在表中的数据约束 －限制存储在表中的数据索引 －允许根据一列或多列中的值快速访问一个表中的数据使用DDL－管理表了解表31使用DDL－管理表列名列名必须以字母开头列名最多可包括30个字符列名的第一个字母后，可由字母、数字、（#）、（$）、（_）任意组合而成除非在括号中引用，否则Oracle自动会将所有列名转化为大写字母约束主键 －唯一且非空唯一键 －唯一外部键 －连接包括相关信息的两个表检查 －允许定义一个强制性条件，在一行被存入表 之前它必须为真，比如NOTNULL使用DDL－管理表列名32使用DDL

－管理表建立表CREATETABLEaquatic_animal( id_no number(10), tank_no number(10), animal_name varchar2(30), birth_date date, death_date date, markings_desc varchar2(30), CONSTRAINTaquatic_animal_pk PRIMARYKEY(id_no) USINGINDEXTABLESPACEindx, CONSTRAINTaquatic_name_tank UNIQUE(animal_name,tank_no) USINGINDEXTABLESPACEindx STORAGE(INITIAL50KNEXT10K))TABLESPACEusers;使用DDL－管理表建立表33使用DDL

－管理表修改表增加列和约束ALTERTABLEtablenameADD(column_or_constraint[,column_or_constraint…]);例如：ALTERTABLEaquatic_animalADD(animal_sex char, CONSTRAINTanimal_sex_mf CHECK(animal_sexIN(‘M’,‘F’)) );使用DDL－管理表修改表34使用DDL－管理表删除约束ALTERTABLEtablename DROPCONSTRAINTconstraintname;如果打算删除一个主键约束或者一个唯一键约束而它有外部键约束，必须指定cascade选项，比如： ALTERTABLEtank DROPCONSTRAINTtank_pkcascade;使用DDL－管理表删除约束35使用DDL－管理表删除列ALTERTABLEtablename DROPCOLUMNcolumnname;在Oracle8i以前的版本，删除一个列的唯一方法是先删除整个表然后从临时本中重建一个。临时本是一个为了满足保存和还原数据而建立的进程。在大表中删除列时要小心。Oracle实际上读取表中的每一行，并从每一行中删除列。快速删除一列的方法 ALTERTABLEtablenameSETUNUSED(columnname); ALTERTABLEtablenameDROPUNUSEDCOLUMNS;使用DDL－管理表删除列36使用DDL－管理表修改列ALTERTABLEtablenameMODIFY(columnname1 typedefine, … );比如：ALTERTABLEaquatic_animalMODIFY(animal_name varchar2(60));使用DDL－管理表修改列37使用DDL－管理表删除表DROPTABLEtablename;当其它表有外部键指向被删除的表时，需要加选项CASCADECONSTRAINS。 DROPTABLEaquatic_animalCASCADECONSTRAINS;使用DDL－管理表删除表38使用DDL

－管理索引建立索引CREATE[UNIQUE]INDEXindexname ONtablename ( columnname1, columnname2, … )TABLESPACEtablespacename;使用DDL－管理索引建立索引39使用DDL－管理索引修改索引修改索引名称 ALTERINDEXoldindexname RENAMETOnewindexname;修改索引属性 ALTERINDEXindexname REBUILD…;如果想向索引中增加列，必须先删除索引然后再重建。删除索引

DROPINDEXindexname;使用DDL－管理索引修改索引40Oracle编程－PL/SQLSQL和PL/SQLSQL是一种ANSI标准类型的描述语言；PL/SQL是Oracle公司开发的一种对SQL语言进行了扩充的过程化语言。二者都可以在数据库的服务器上运行。二者之间可以相互调用。PL/SQL支持数据库使用的多种标准数据类型。Oracle编程－PL/SQLSQL和PL/SQL41Oracle编程－PL/SQLPL/SQL块的结构变量声明（可选部分）程序代码异常处理（可选部分）

DECLARE … BEGIN … EXCEPTION … END;Oracle编程－PL/SQLPL/SQL块的结构42Oracle编程－PL/SQL变量声明包含内容赋予变量适当的名称赋予变量正确的数据类型定义标量变量和记录类型变量（记录由标量组成）控制变量的范围命名规则变量名必须由字符开头在首字符之后，变量名应包括：字母、数字、_、$、#变量的长度范围：1～30变量名大小写不敏感变量名不能和语言中的关键子重复数据类型Oracle编程－PL/SQL变量声明43Oracle编程－PL/SQLDBMS_OUTPUT包使用前定义：SETSERVEROUTPUTONDBMS_OUTPUT包允许使用SQL*Plus从PL/SQL中显示输出信息输出过程DBMS_OUTPUT.PUT_LINE 允许显示一行文本DBMS_OUTPUT.PUT 允许显示一段文本，但不能生成换行符DBMS_NEW_LINE 生成换行符Oracle编程－PL/SQLDBMS_OUTPUT包44Oracle编程－PL/SQL语句类型分支控制语句IFexpressionTHEN statements[ELSIFexpression statements][ELSE statements]ENDIF;Oracle编程－PL/SQL语句类型45Oracle编程－PL/SQL循环语句PL/SQL支持三种不同类型的循环结构：WHILE循环FOR循环基本循环WHILE循环WHILEexpressionLOOP statementsENDLOOP;FOR循环FORcounterIN[REVERSE]startvalue…endvalueLOOP statementsENDLOOP;Oracle编程－PL/SQL循环语句46Oracle编程－PL/SQL基本循环 没有内部控制的循环结构，在程序代码中编写代码来结束该循环。LOOP statementsENDLOOP;EXIT语句EXIT是只有一个单词的语句，它能够结束一个循环。EXITWHEN语句EXITWHEN语句是EXIT语句的一个更新的版本。EXITWHENexpression;Oracle编程－PL/SQL基本循环47Oracle编程－PL/SQLNULL语句 NULL语句什么也不做。使用NULL语句的唯一原因就是把它当作一个占位符。 IFx=2THEN DBMS_OUTPUT.PUT_LINE(‘x=2’); ELSE NULL; ENDIF;Oracle编程－PL/SQLNULL语句48Oracle编程－PL/SQLGOTO语句 GOTOlabel;

DECLARE x NUMBER:=0; BEGIN x:=0; <<repeat_loop>> x:=x+1; DBMS_OUTPUT.PUT_LINE(x); IFx<3THEN GOTOrepeat_loop; ENDIF; END;Oracle编程－PL/SQLGOTO语句49Oracle编程－PL/SQL异常处理标准异常的检测使用SQLERRM使用WHEN语句WHENexception[ORexception…]THEN statementsWHENOTHERSTHEN statementsOracle编程－PL/SQL异常处理50Oracle编程－PL/SQL异常处理的例子

CREATEORREPLACEFUNCTIONgetAnimalName( animal_id IN NUMBER) RETURNvarchar2IS animal_name varchar2(20); BEGIN SELECTanimal_nameINTOgetAnimalName.animal_name FROMaquatic_animal WHEREid_no=animal_id; RETURNanimal_name; EXCEPTION WHENNO_DATA_FOUNDORTOO_MANY_ROWSTHEN RETURN‘Non-existentornon-uniqueID’; WHENVALUE_ERRORTHEN RETURN‘Animalnametoolong’; WHENOTHERSTHEN RETURN‘Namecouldnotbedetermined’; END;Oracle编程－PL/SQL异常处理的例子51Oracle编程－PL/SQL产生异常的原因SELECTINTO语句 如果该语句返回值少于或多于一行，异常就会发生任何类型的SQL语句 如INSERT、UPDATE、DELETE语句任何类型的游标过程语句 如OPEN、CLOSE、FETCH语句嵌入表和数组的使用 当使用这些对象类型时，如果使用了无效的索引值、对象初始化操作失败以及对空元素的访问均会引起异常Oracle编程－PL/SQL产生异常的原因52Oracle编程－PL/SQL从PL/SQL中执行SQL使用游标FOR循环FORrecord_nameIN(select_statement)LOOP statementsENDLOOP;例如：BEGIN FORanimalIN( SELECTanimal_name FROMaquatic_animal ORDERBYanimal_name) LOOP DBMS_OUTPUT.PUT_LINE(animal.animal_name); ENDLOOP;END;Oracle编程－PL/SQL从PL/SQL中执行SQL53Oracle编程－PL/SQL使用标准的游标进程在PL/SQL程序体中的声明部分声明一个游标使用OPEN语句打开这个游标编辑一条或多条FETCH语句取回这个游标的SELECT语句所返回的结果使用CLOSE语句关闭这个游标Oracle编程－PL/SQL使用标准的游标进程54Oracle编程－PL/SQL游标的声明CURSORcursorname [(parametername[IN]datatype[{:=|DEFAULT}value] [,(parametername[IN]datatype[{:=|DEFAULT}value…])] ISselect_statement;例如：CURSORanimal_list(tank_numberNUMBER)IS SELECTanimal_name FROMaquatic_animal WHEREtank_no=tank_number ORDERBYanimal_name;Oracle编程－PL/SQL游标的声明55Oracle编程－PL/SQL打开一个游标OPENcursorname[(parametervalue[,(parametervalue…])];例如：OPENanimal_list(1);关闭一个游标CLOSEcursorname;例如：CLOSEanimal_list;Oracle编程－PL/SQL打开一个游标56Oracle编程－PL/SQL从游标中取数据FETCHcursornameINTO{recordname|variablename[,variablename…]};例如：FETCHanimal_listINTOanimal_name;LOOPFETCHanimal_listINTOanimal_name; … EXITWHENanimal_list%NOTFOUND;EXITLOOP;FETCHanimal_listINTOanimal_name;WHILEanimal_list%FOUNDLOOP FETCHanimal_listINTOanimal_name; …ENDLOOP;Oracle编程－PL/SQL从游标中取数据57Oracle编程－过程存储过程的典型结构CREATEPROCEDUREprocedurename(argumentlist)DeclarationSectionBEGIN PL/SQLCodeEXCEPTION PL/SQLExceptionHandlingCodeEND;Oracle编程－过程存储过程的典型结构58Oracle编程－过程存储过程的例子CREATEORREPLACEPROCEDUREdemoproc( vIDININTEGER)AS vNamevarchar2(60);BEGIN SELECTnameINTOvName FROMdemotable WHEREid=vID;EXCEPTION WHENNO_DATA_FOUNDTHEN vName:=‘nodatafound’;END;Oracle编程－过程存储过程的例子59Oracle编程－过程取消过程

DROPPROCEDUREprocedurename;修改过程删除过程，然后重新建立这个过程使用CREATEORREPLACEPROCEDURE命令查看程序错误信息SHOWERRORSPROCEDUREprocedurename;检索源代码USER_SOURCE 用户所有的程序源代码资源ALL_SOURCE 用户所有程序或是已访问的程序的源代码资源DBA_SOURCE 数据库中所有的程序源代码Oracle编程－过程取消过程60Oracle编程－函数函数和过程的区别 函数需要返回一个值。函数的例子CREATEORREPLACEFUNCTIONdemofunc( vIDININTEGER)RETURNVARCHAR2AS vNamevarchar2(60);BEGIN SELECTnameINTOvName FROMdemotable WHEREid=vID; RETURNvName;EXCEPTION WHENNO_DATA_FOUNDTHEN vName:=‘nodatafound’; RETURNvName;END;Oracle编程－函数函数和过程的区别61Oracle编程－触发器触发器类型（14种组合）INSERTUPDATEDELETE先触发器（Before）后触发器（After）替代触发器（Insteadof）语句级触发器行级触发器Oracle编程－触发器触发器类型（14种组合）62Oracle编程－触发器例子

CREATEORREPLACETRIGGERdemotrigger AFTERINSERTORUPDATEONtable1 FOREACHROW BEGIN IF:new.column1>10THEN UPDATEtable2 SETcolumn2=‘F’ WHEREtable1.column2=table2.column1; ELSE UPDATEdemotable2 SETcolumn2=‘T’ WHEREtable1.column2=table2.column1; ENDIF; ENDOracle编程－触发器例子63Oracle编程－PROC*COracle编程－PROC*C64Oracle编程－PROC*C

PROC*C简介一个可以在高级语言中嵌入SQL语句的编程工具需要经过预编译过程才能转换为C语言代码使用嵌入式SQL完成数据库操作Oracle编程－PROC*CPROC*C简介65几点补充说明常用的几种链接oracle数据库的工具这几种工具是如何连接到oracle数据库的学习PL/SQL的资料推荐《SQL21自学通.pdf》工作中遇到问题，如何快速解决？如何寻求帮助？找出问题的关键，baidu、google、导师、同事如何快速的融入到公司的项目中？展现自己几点补充说明常用的几种链接oracle数据库的工具66谢谢！谢谢！67第68页神州数码思特奇信息技术股份有限公司Oracle基础培训胡继钢第1页神州数码思特奇信息技术股份有限公司Oracle基础培68内容介绍Oracle基础(*)使用DDL(*)使用DML(*)PL/SQL基础知识(*)PROC*C基础知识(*)Oracle基础内容介绍Oracle基础69配置.ora文件客户端tnsnames.ora文件的位置和作用文件位置

UNIX:$ORACLE_HOME/network/admin WIN:ORACLE_HOME\net80\admin作用

保存着连接到服务器监听端口需要的地址和端口号信息 可配置多个服务器监听端口

配置.ora文件客户端tnsnames.ora文件的位置和作70配置.ora文件客户端tnsnames.ora的基本内容配置.ora文件客户端tnsnames.ora的基本内容71在UNIX下配置和使用Oracle属性文件(.profileetc.)我们关注的ORACLE_SID=las;exportORACLE_SIDORACLE_OWNER=oracle;exportORACLE_OWNERORACLE_BASE=/oracle/oracle/app/oracle;exportORACLE_BASEORACLE_HOME=$ORACLE_BASE/product/8.0.6;exportORACLE_HOMEORACLE_DOC=$ORACLE_HOME/doc;exportORACLE_DOCORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data;exportORA_NLS33TMPDIR=/tmp;exportTMPDIRLD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib;exportLD_LIBRARY_PATHPATH=$PATH:$ORACLE_HOME/bin:/usr/lbin:.;exportPATHNLS_LANG=American_America.zhs16gbk;exportNLS_LANGSHLIB_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib:/usr/lib;exportSHLIB_PATH在UNIX下配置和使用Oracle属性文件(.profile72用户、安全和模式用户、安全和模式73内容介绍Oracle基础管理(*)使用DML(*)使用DDL(*)PL/SQL基础知识(*)PROC*C基础知识(*)Oracle基础内容介绍Oracle基础74使用DML内容介绍常用SQL语句常用内部函数的使用构造高效率的SQL使用DML内容介绍75使用DML－查询数据基本的Select查询语法

SELECTcolumn_lists FROMtable_lists WHEREconditions GROUPBYcolumn_lists HAVINGconditions ORDERBYcolumn_lists;使用DML－查询数据基本的Select查询语法76使用DML－查询数据选择数据SELECTlast_name,first_name,specialty FROMartist;排列数据使用ORDERBY降序：DESC升序：ASC（默认）SELECTlast_name,first_name,specialty FROMartist ORDERBYfirst_nameDESC;使用DML－查询数据选择数据77使用DML－查询数据约束结果使用WHERESELECTlast_name,first_name,specialty FROMartist WHEREspecialty=‘DIGITAL’;比较运算符＝，<>>，<，>=，<=LIKE ‘%’匹配字符串，‘_’匹配字符BETWEEN，INISNULL，ISNOTNULL使用DML－查询数据约束结果78使用DML－查询数据使用WHERE的注意事项引用的字符必须在单引号内，而不是双引号如果需要在字符串内放单引号，应该写两次，例如想比较won’t，在WHERE中应写成‘won’’t’注意AND和OR的使用要考虑空值的影响使用DML－查询数据使用WHERE的注意事项79使用DML－查询数据关联表Cartesian产品使用DML－查询数据关联表80使用DML－查询数据使用相等关联来比较列SELECTbook_id,book_title,bl_book_id,bl_student_idFROMbooks,books_loanedWHEREbook_id=bl_book_id;使用表的别名SELECTb.book_id,b.book_title, bl.bl_book_id,bl.bl_student_id, s.student_id,s.first_name,s_last_nameFROMbooksb,books_loanedbl,studentssWHEREb.book_id=bl.bl_book_id andbl.bl_student_id=s.student.id;使用DML－查询数据使用相等关联来比较列81使用DML－查询数据内部关联 两个表之间的内部关联返回两个表中都符合的记录的行。 使用DML－查询数据内部关联82使用DML－查询数据外部关联 外部关联关联了两个表并使得即使第二个表没有与第一个表符合的记录时也能返回结果。使用DML－查询数据外部关联83使用DML－查询数据使用GROUPBY子句原理Oracle对查询检索数据并在临时存储区中保存；Oracle对数据排序，并和GROUPBY值一起对所有数据分组；Oracle把指定的聚集函数应用到每个记录组并对每组返回一行。注意由于求和查询对每组返回一行，所以在选择列中不可能有GROUPBY子句中没有列出的列，除非它们应用了一个聚集函数。使用DML－查询数据使用GROUPBY子句84使用DML－查询数据例子正确

SELECTspecialty,COUNT(*) FROMartist GROUPBYspecialty;错误 SELECTspecialty,last_name,first_name,COUNT(*) FROMartist GROUPBYspecialty;使用DML－查询数据例子85使用DML－查询数据使用HAVING子句 HAVING和WHERE的区别在于HAVING子句中的条件是被求和后应用到数据，它只对指定组有兴趣。 SELECTspecialty FROMartist GROUPBYspecialty HAVINGCOUNT(*)=1ANDspecialty<>’OTHER’; SELECTspecialty FROMartist WHEREspecialty<>’OTHER’ GROUPBYspecialty HAVINGCOUNT(*)=1;使用DML－查询数据使用HAVING子句86使用DML－查询数据使用DISTINCT和ALL关键字 默认为ALL SELECTCOUNT(DISTINCTspcialty) FROMartist; SELECTCOUNT(spcialty) FROMartist;使用DML－查询数据使用DISTINCT和ALL关键字87使用DML－修改数据插入数据基本形式 INSERTINTOtablename (columnname1,columnname2,…) VALUES(value1,value2,…);子查询形式 INSERTINTOtablename (columnname1,columnname2,…) subquery;使用DML－修改数据插入数据88使用DML－修改数据更新数据基本形式 UPDATEtablename SETcolumnname1=expression1, columnname2=expression2, … WHEREconditions;子查询形式 UPDATEtablename SET(columnname1,columnname2,…)= (SELECTcolumnname3,columnname4,… FROMtablename2 WHEREconditions) WHEREconditions;使用DML－修改数据更新数据89使用DML－修改数据删除数据基本形式 DELETEFROMtablename WHEREconditions;子查询形式 DELETEFROMtablename WHEREsubquery;使用DML－修改数据删除数据90使用DML－常用内部函数常用内部函数(1)－通用函数LOWER()函数UPPER()函数DECODE()函数INSTR()函数SUBSTR()函数NVL()函数LENGTH()函数常用内部函数(2)－数字函数ROUND()函数TRUNC()函数MOD()函数连接运算符：||使用DML－常用内部函数常用内部函数(1)－通用函数91使用DML－常用内部函数常用内部函数(3)－日期函数SYSDATEMONTHS_BETWEEN()函数NEXT_DAY()函数LAST_DAY()函数ADD_MONTHS()函数ROUND()函数TRUNC()函数常用内部函数(4)－格式转换函数TO_CHAR()函数TO_DATE()函数TO_NUMBER()函数常用的格式转化使用DML－常用内部函数常用内部函数(3)－日期函数92使用DML－常用内部函数常用内部函数(5)－聚合函数AVG()函数COUNT()函数MAX()函数MIN()函数SUM()函数使用DML－常用内部函数常用内部函数(5)－聚合函数93使用DML－构造高效率的SQL内容提要使用索引应该避免的问题使用DML－构造高效率的SQL内容提要94使用DML－构造高效率的SQL使用索引索引可以成百上千倍的提高SQL语句效率查询系统中的索引按照索引的顺序访问表使用效率最高的索引使用DML－构造高效率的SQL使用索引95使用DML－构造高效率的SQL应该避免的问题尽量不要在检索条件中对列使用函数，因为会阻止索引的使用如果可以使用where条件，尽量不要在having中限制数据尽量不要使数据排序引起排序的条件OrderbyGroupbyUnion,intersect,minusDistinct谨慎使用in,exists使用DML－构造高效率的SQL应该避免的问题96内容介绍Oracle基础管理(*)使用DML(*)使用DDL(*)PL/SQL基础知识(*)PROC*C基础知识(*)Oracle基础内容介绍Oracle基础97使用DDL－管理表了解表表是关系数据库的基础，它们典型的由下面几个主要元素组成：列 －定义存储在表中的数据约束 －限制存储在表中的数据索引 －允许根据一列或多列中的值快速访问一个表中的数据使用DDL－管理表了解表98使用DDL－管理表列名列名必须以字母开头列名最多可包括30个字符列名的第一个字母后，可由字母、数字、（#）、（$）、（_）任意组合而成除非在括号中引用，否则Oracle自动会将所有列名转化为大写字母约束主键 －唯一且非空唯一键 －唯一外部键 －连接包括相关信息的两个表检查 －允许定义一个强制性条件，在一行被存入表 之前它必须为真，比如NOTNULL使用DDL－管理表列名99使用DDL

－管理表建立表CREATETABLEaquatic_animal( id_no number(10), tank_no number(10), animal_name varchar2(30), birth_date date, death_date date, markings_desc varchar2(30), CONSTRAINTaquatic_animal_pk PRIMARYKEY(id_no) USINGINDEXTABLESPACEindx, CONSTRAINTaquatic_name_tank UNIQUE(animal_name,tank_no) USINGINDEXTABLESPACEindx STORAGE(INITIAL50KNEXT10K))TABLESPACEusers;使用DDL－管理表建立表100使用DDL

－管理表修改表增加列和约束ALTERTABLEtablenameADD(column_or_constraint[,column_or_constraint…]);例如：ALTERTABLEaquatic_animalADD(animal_sex char, CONSTRAINTanimal_sex_mf CHECK(animal_sexIN(‘M’,‘F’)) );使用DDL－管理表修改表101使用DDL－管理表删除约束ALTERTABLEtablename DROPCONSTRAINTconstraintname;如果打算删除一个主键约束或者一个唯一键约束而它有外部键约束，必须指定cascade选项，比如： ALTERTABLEtank DROPCONSTRAINTtank_pkcascade;使用DDL－管理表删除约束102使用DDL－管理表删除列ALTERTABLEtablename DROPCOLUMNcolumnname;在Oracle8i以前的版本，删除一个列的唯一方法是先删除整个表然后从临时本中重建一个。临时本是一个为了满足保存和还原数据而建立的进程。在大表中删除列时要小心。Oracle实际上读取表中的每一行，并从每一行中删除列。快速删除一列的方法 ALTERTABLEtablenameSETUNUSED(columnname); ALTERTABLEtablenameDROPUNUSEDCOLUMNS;使用DDL－管理表删除列103使用DDL－管理表修改列ALTERTABLEtablenameMODIFY(columnname1 typedefine, … );比如：ALTERTABLEaquatic_animalMODIFY(animal_name varchar2(60));使用DDL－管理表修改列104使用DDL－管理表删除表DROPTABLEtablename;当其它表有外部键指向被删除的表时，需要加选项CASCADECONSTRAINS。 DROPTABLEaquatic_animalCASCADECONSTRAINS;使用DDL－管理表删除表105使用DDL

－管理索引建立索引CREATE[UNIQUE]INDEXindexname ONtablename ( columnname1, columnname2, … )TABLESPACEtablespacename;使用DDL－管理索引建立索引106使用DDL－管理索引修改索引修改索引名称 ALTERINDEXoldindexname RENAMETOnewindexname;修改索引属性 ALTERINDEXindexname REBUILD…;如果想向索引中增加列，必须先删除索引然后再重建。删除索引

DROPINDEXindexname;使用DDL－管理索引修改索引107Oracle编程－PL/SQLSQL和PL/SQLSQL是一种ANSI标准类型的描述语言；PL/SQL是Oracle公司开发的一种对SQL语言进行了扩充的过程化语言。二者都可以在数据库的服务器上运行。二者之间可以相互调用。PL/SQL支持数据库使用的多种标准数据类型。Oracle编程－PL/SQLSQL和PL/SQL108Oracle编程－PL/SQLPL/SQL块的结构变量声明（可选部分）程序代码异常处理（可选部分）

DECLARE … BEGIN … EXCEPTION … END;Oracle编程－PL/SQLPL/SQL块的结构109Oracle编程－PL/SQL变量声明包含内容赋予变量适当的名称赋予变量正确的数据类型定义标量变量和记录类型变量（记录由标量组成）控制变量的范围命名规则变量名必须由字符开头在首字符之后，变量名应包括：字母、数字、_、$、#变量的长度范围：1～30变量名大小写不敏感变量名不能和语言中的关键子重复数据类型Oracle编程－PL/SQL变量声明110Oracle编程－PL/SQLDBMS_OUTPUT包使用前定义：SETSERVEROUTPUTONDBMS_OUTPUT包允许使用SQL*Plus从PL/SQL中显示输出信息输出过程DBMS_OUTPUT.PUT_LINE 允许显示一行文本DBMS_OUTPUT.PUT 允许显示一段文本，但不能生成换行符DBMS_NEW_LINE 生成换行符Oracle编程－PL/SQLDBMS_OUTPUT包111Oracle编程－PL/SQL语句类型分支控制语句IFexpressionTHEN statements[ELSIFexpression statements][ELSE statements]ENDIF;Oracle编程－PL/SQL语句类型112Oracle编程－PL/SQL循环语句PL/SQL支持三种不同类型的循环结构：WHILE循环FOR循环基本循环WHILE循环WHILEexpressionLOOP statementsENDLOOP;FOR循环FORcounterIN[REVERSE]startvalue…endvalueLOOP statementsENDLOOP;Oracle编程－PL/SQL循环语句113Oracle编程－PL/SQL基本循环 没有内部控制的循环结构，在程序代码中编写代码来结束该循环。LOOP statementsENDLOOP;EXIT语句EXIT是只有一个单词的语句，它能够结束一个循环。EXITWHEN语句EXITWHEN语句是EXIT语句的一个更新的版本。EXITWHENexpression;Oracle编程－PL/SQL基本循环114Oracle编程－PL/SQLNULL语句 NULL语句什么也不做。使用NULL语句的唯一原因就是把它当作一个占位符。 IFx=2THEN DBMS_OUTPUT.PUT_LINE(‘x=2’); ELSE NULL; ENDIF;Oracle编程－PL/SQLNULL语句115Oracle编程－PL/SQLGOTO语句 GOTOlabel;

DECLARE x NUMBER:=0; BEGIN x:=0; <<repeat_loop>> x:=x+1; DBMS_OUTPUT.PUT_LINE(x); IFx<3THEN GOTOrepeat_loop; ENDIF; END;Oracle编程－PL/SQLGOTO语句116Oracle编程－PL/SQL异常处理标准异常的检测使用SQLERRM使用WHEN语句WHENexception[ORexception…]THEN statementsWHENOTHERSTHEN statementsOracle编程－PL/SQL异常处理117Oracle编程－PL/SQL异常处理的例子

CREATEORREPLACEFUNCTIONgetAnimalName( animal_id IN NUMBER) RETURNvarchar2IS animal_name varchar2(20); BEGIN SELECTanimal_nameINTOgetAnimalName.animal_name FROMaquatic_animal WHEREid_no=animal_id; RETURNanimal_name; EXCEPTION WHENNO_DATA_FOUNDORTOO_MANY_ROWSTHEN RETURN‘Non-existentornon-uniqueID’; WHENVALUE_ERRORTHEN RETURN‘Animalnametoolong’; WHENOTHERSTHEN RETURN‘Namecouldnotbedetermined’; END;Oracle编程－PL/SQL异常处理的例子118Oracle编程－PL/SQL产生异常的原因SELECTINTO语句 如果该语句返回值少于或多于一行，异常就会发生任何类型的SQL语句 如INSERT、UPDATE、DELETE语句任何类型的游标过程语句 如OPEN、CLOSE、FETCH语句嵌入表和数组的使用 当使用这些对象类型时，如果使用了无效的索引值、对象初始化操作失败以及对空元素的访问均会引起异常Oracle编程－PL/SQL产生异常的原因119Oracle编程－PL/SQL从PL/SQL中执行SQL使用游标FOR循环FORrecord_nameIN(select_statement)LOOP statementsENDLOOP;例如：BEGIN FORanimalIN( SELECTanimal_name FROMaquatic_animal ORDERBYanimal_name) LOOP DBMS_OUTPUT.PUT_LINE(animal.animal_name); ENDLOOP;END;Oracle编程－PL/SQL从PL/SQL中执行SQL120Oracle编程－PL/SQL使用标准的游标进程在PL/SQL程序体中的声明部分声明一个游标使用OPEN语句打开这个游标编辑一条或多条FETCH语句取回这个游标的SELECT语句所返回的结果使用CLOSE语句关闭这个游标Oracle编程－PL/SQL使用标准的游标进程121Oracle编程－PL/SQL游标的声明CURSORcursorname [(parametername[IN]datatype[{:=|DEFAULT}value] [,(parametername[IN]datatype[{:=|DEFAULT}value…])] ISselect_statement;例如：CURSORanimal_list(tank_numberNUMBER