Oracle基礎(chǔ)知識專項(xiàng)培訓(xùn)

第1頁神州數(shù)碼思特奇信息技術(shù)股份有限公司Oracle基礎(chǔ)培訓(xùn)胡繼鋼內(nèi)容介紹Oracle基礎(chǔ)(*)使用DDL(*)使用DML(*)PL/SQL基礎(chǔ)知識(*)PROC*C基礎(chǔ)知識(*)Oracle基礎(chǔ)配置.ora文件客戶端tnsnames.ora文件的位置和作用文件位置

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

保存著連接到服務(wù)器監(jiān)聽端口需要的地址和端口號信息 可配置多個(gè)服務(wù)器監(jiān)聽端口

配置.ora文件客戶端tnsnames.ora的基本內(nèi)容在UNIX下配置和使用Oracle屬性文件(.profileetc.)我們關(guān)注的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用戶、安全和模式內(nèi)容介紹Oracle基礎(chǔ)管理(*)使用DML(*)使用DDL(*)PL/SQL基礎(chǔ)知識(*)PROC*C基礎(chǔ)知識(*)Oracle基礎(chǔ)使用DML內(nèi)容介紹常用SQL語句常用內(nèi)部函數(shù)的使用構(gòu)造高效率的SQL使用DML－查詢數(shù)據(jù)基本的Select查詢語法

SELECTcolumn_lists FROMtable_lists WHEREconditions GROUPBYcolumn_lists HAVINGconditions ORDERBYcolumn_lists;使用DML－查詢數(shù)據(jù)選擇數(shù)據(jù)SELECTlast_name,first_name,specialty FROMartist;排列數(shù)據(jù)使用ORDERBY降序：DESC升序：ASC（默認(rèn)）SELECTlast_name,first_name,specialty FROMartist ORDERBYfirst_nameDESC;使用用DML－－查查詢詢數(shù)數(shù)據(jù)據(jù)約束束結(jié)結(jié)果果使用用WHERESELECTlast_name,first_name,specialtyFROMartistWHEREspecialty=‘‘DIGITAL’’;比較較運(yùn)運(yùn)算算符符＝，，<>>，，<，，>=，，<=LIKE‘‘%’’匹匹配配字字符符串串，，‘‘_’’匹匹配配字字符符BETWEEN，，INISNULL，，ISNOTNULL使用用DML－－查查詢詢數(shù)數(shù)據(jù)據(jù)使用用WHERE的的注注意意事事項(xiàng)項(xiàng)引用用的的字字符符必必須須在在單單引引號號內(nèi)內(nèi)，，而而不不是是雙雙引引號號如果果需需要要在在字字符符串串內(nèi)內(nèi)放放單單引引號號，，應(yīng)應(yīng)該該寫寫兩兩次次，，例例如如想想比比較較won’’t，，在在WHERE中中應(yīng)應(yīng)寫寫成成‘‘won’’’’t’’注意AND和OR的使使用要考慮空空值的影影響使用DML－－查詢數(shù)據(jù)據(jù)關(guān)聯(lián)表Cartesian產(chǎn)品使用DML－－查查詢數(shù)據(jù)據(jù)使用相等等關(guān)聯(lián)來來比較列列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_idandbl.bl_student_id=s.student.id;使用DML－－查查詢數(shù)據(jù)據(jù)內(nèi)部關(guān)聯(lián)聯(lián)兩個(gè)表之之間的內(nèi)內(nèi)部關(guān)聯(lián)聯(lián)返回兩兩個(gè)表中中都符合合的記錄錄的行。。使用DML－－查查詢數(shù)據(jù)據(jù)外部關(guān)聯(lián)聯(lián)外部關(guān)聯(lián)聯(lián)關(guān)聯(lián)了了兩個(gè)表表并使得得即使第第二個(gè)表表沒有與與第一個(gè)個(gè)表符合合的記錄錄時(shí)也能能返回結(jié)結(jié)果。使用DML－－查查詢數(shù)據(jù)據(jù)使用GROUPBY子句句原理Oracle對對查詢檢檢索數(shù)據(jù)據(jù)并在臨臨時(shí)存儲(chǔ)儲(chǔ)區(qū)中保保存；Oracle對對數(shù)據(jù)排排序，并并和GROUPBY值一起起對所有有數(shù)據(jù)分分組；Oracle把把指定的的聚集函函數(shù)應(yīng)用用到每個(gè)個(gè)記錄組組并對每每組返回回一行。。注意由于求和和查詢對對每組返返回一行行，所以以在選擇擇列中不不可能有有GROUPBY子子句中沒沒有列出出的列，，除非它它們應(yīng)用用了一個(gè)個(gè)聚集函函數(shù)。使用DML－－查查詢數(shù)據(jù)據(jù)例子正確SELECTspecialty,COUNT(*)FROMartistGROUPBYspecialty;錯(cuò)誤SELECTspecialty,last_name,first_name,COUNT(*)FROMartistGROUPBYspecialty;使用DML－－查查詢數(shù)據(jù)據(jù)使用HAVING子子句HAVING和和WHERE的的區(qū)別別在于于HAVING子句句中的的條件件是被被求和和后應(yīng)應(yīng)用到到數(shù)據(jù)據(jù)，它它只對對指定定組有有興趣趣。SELECTspecialtyFROMartistGROUPBYspecialtyHAVINGCOUNT(*)=1ANDspecialty<>’OTHER’;SELECTspecialtyFROMartistWHEREspecialty<>’’OTHER’’GROUPBYspecialtyHAVINGCOUNT(*)=1;使用DML－－查查詢數(shù)數(shù)據(jù)使用DISTINCT和和ALL關(guān)關(guān)鍵字字默認(rèn)為為ALLSELECTCOUNT(DISTINCTspcialty)FROMartist;SELECTCOUNT(spcialty)FROMartist;使用DML－修修改數(shù)據(jù)插入數(shù)據(jù)基本形式INSERTINTOtablename(columnname1,columnname2,…)VALUES(value1,value2,…);子查詢形式INSERTINTOtablename(columnname1,columnname2,…)subquery;使用DML－修修改數(shù)據(jù)更新數(shù)據(jù)基本形式UPDATEtablenameSETcolumnname1=expression1,columnname2=expression2,…WHEREconditions;子查詢形式UPDATEtablenameSET(columnname1,columnname2,……)=(SELECTcolumnname3,columnname4,…FROMtablename2WHEREconditions)WHEREconditions;使用DML－修修改數(shù)據(jù)刪除數(shù)據(jù)基本形式DELETEFROMtablenameWHEREconditions;子查詢形式DELETEFROMtablenameWHEREsubquery;使用DML－常用內(nèi)部函數(shù)數(shù)常用內(nèi)部函數(shù)數(shù)(1)－通用函數(shù)LOWER()函數(shù)UPPER()函數(shù)DECODE()函數(shù)INSTR()函數(shù)SUBSTR()函數(shù)NVL()函數(shù)LENGTH()函數(shù)常用內(nèi)部函數(shù)數(shù)(2)－數(shù)字函數(shù)ROUND()函數(shù)TRUNC()函數(shù)MOD()函數(shù)連接運(yùn)算符：||使用DML－常常用內(nèi)部函函數(shù)常用內(nèi)部函數(shù)數(shù)(3)－－日期函數(shù)SYSDATEMONTHS_BETWEEN()函數(shù)NEXT_DAY()函函數(shù)LAST_DAY()函函數(shù)ADD_MONTHS()函數(shù)ROUND()函數(shù)TRUNC()函數(shù)常用內(nèi)部函數(shù)數(shù)(4)－－格式轉(zhuǎn)換函函數(shù)TO_CHAR()函數(shù)數(shù)TO_DATE()函數(shù)數(shù)TO_NUMBER()函數(shù)常用的格式轉(zhuǎn)轉(zhuǎn)化使用DML－常常用內(nèi)部函函數(shù)常用內(nèi)部函數(shù)數(shù)(5)－聚聚合函數(shù)AVG()函函數(shù)COUNT()函數(shù)MAX()函函數(shù)MIN()函函數(shù)SUM()函函數(shù)使用DML－構(gòu)造造高效率的SQL內(nèi)容提要使用索引應(yīng)該避免的問問題使用DML－構(gòu)造造高效率的SQL使用索引索引可以成百百上千倍的提提高SQL語句效效率查詢系統(tǒng)中的的索引按照索引的順順序訪問表使用效率最高高的索引使用DML－構(gòu)造造高效率的SQL應(yīng)該避免的問問題盡量不要在檢檢索條件中對對列使用函數(shù)數(shù)，因?yàn)闀?huì)阻阻止索引的使使用如果可以使用用where條條件，盡量不不要在having中限限制數(shù)據(jù)盡量不要使數(shù)數(shù)據(jù)排序引起排序的條條件OrderbyGroupbyUnion,intersect,minusDistinct謹(jǐn)慎使用in,exists內(nèi)容介紹Oracle基礎(chǔ)管理(*)使用DML(*)使用DDL(*)PL/SQL基礎(chǔ)知識(*)PROC*C基礎(chǔ)知識(*)Oracle基基礎(chǔ)使用DDL－－管管理表了解表表是關(guān)系系數(shù)據(jù)庫庫的基礎(chǔ)礎(chǔ)，它們們典型的的由下面面幾個(gè)主主要元素素組成：：列 －定定義存存儲(chǔ)在表表中的數(shù)數(shù)據(jù)約束－－限制制存儲(chǔ)在在表中的的數(shù)據(jù)索引－－允許許根據(jù)一一列或多多列中的的值快速速訪問一一個(gè)表中中的數(shù)據(jù)據(jù)使用DDL－管理理表列名列名必須須以字母母開頭列名最多多可包括括30個(gè)個(gè)字符列名的第第一個(gè)字字母后，，可由字字母、數(shù)數(shù)字、（（#）、、（$））、（_）任意意組合而而成除非在括括號中引引用，否否則Oracle自自動(dòng)會(huì)將將所有列列名轉(zhuǎn)化化為大寫寫字母約束主鍵－－唯一一且非空空唯一鍵－－唯唯一外部鍵－－連連接包括括相關(guān)信信息的兩兩個(gè)表檢查－－允許許定義一一個(gè)強(qiáng)制制性條件件，在一一行被存存入表之之前它它必須為為真，比比如NOTNULL使用DDL－管理理表建立表CREATETABLEaquatic_animal(id_no number(10),tank_nonumber(10),animal_namevarchar2(30),birth_datedate,death_datedate,markings_desc varchar2(30),CONSTRAINTaquatic_animal_pkPRIMARYKEY(id_no)USINGINDEXTABLESPACEindx,CONSTRAINTaquatic_name_tankUNIQUE(animal_name,tank_no)USINGINDEXTABLESPACEindxSTORAGE(INITIAL50KNEXT10K))TABLESPACEusers;使用DDL－管理表修改表增加列和約束束ALTERTABLEtablenameADD(column_or_constraint[,column_or_constraint…]);例如：ALTERTABLEaquatic_animalADD(animal_sexchar,CONSTRAINTanimal_sex_mfCHECK(animal_sexIN(‘M’,‘‘F’)));使用DDL－管理表刪除約束ALTERTABLEtablenameDROPCONSTRAINTconstraintname;如果打算刪除除一個(gè)主鍵約約束或者一個(gè)個(gè)唯一鍵約束束而它有外部部鍵約束，必必須指定cascade選項(xiàng)，比如如：ALTERTABLEtankDROPCONSTRAINTtank_pkcascade;使用DDL－管理表刪除列ALTERTABLEtablenameDROPCOLUMNcolumnname;在Oracle8i以前前的版本，刪刪除一個(gè)列的的唯一方法是是先刪除整個(gè)個(gè)表然后從臨臨時(shí)本中重建建一個(gè)。臨時(shí)時(shí)本是一個(gè)為為了滿足保存存和還原數(shù)據(jù)據(jù)而建立的進(jìn)進(jìn)程。在大表中刪除除列時(shí)要小心心。Oracle實(shí)際上上讀取表中的的每一行，并并從每一行中中刪除列。快速刪除一列列的方法ALTERTABLEtablenameSETUNUSED(columnname);ALTERTABLEtablenameDROPUNUSEDCOLUMNS;使用DDL－管理表修改列ALTERTABLEtablenameMODIFY(columnname1typedefine,…);比如：ALTERTABLEaquatic_animalMODIFY(animal_namevarchar2(60));使用DDL－管理表刪除表DROPTABLEtablename;當(dāng)其它表有外外部鍵指向被被刪除的表時(shí)時(shí)，需要加選選項(xiàng)CASCADECONSTRAINS。。DROPTABLEaquatic_animalCASCADECONSTRAINS;使用DDL－管理索引引建立索引CREATE[UNIQUE]INDEXindexnameONtablename(columnname1,columnname2,…)TABLESPACEtablespacename;使用DDL－管理索引引修改索引修改索引名稱稱ALTERINDEXoldindexnameRENAMETOnewindexname;修改索引屬性性ALTERINDEXindexnameREBUILD…;如果想向索引引中增加列，，必須先刪除除索引然后再再重建。刪除索引DROPINDEXindexname;Oracle編程－PL/SQLSQL和PL/SQLSQL是一種種ANSI標(biāo)標(biāo)準(zhǔn)類型的描描述語言；PL/SQL是Oracle公司開開發(fā)的一種對對SQL語言言進(jìn)行了擴(kuò)充充的過程化語語言。二者都可以在在數(shù)據(jù)庫的服服務(wù)器上運(yùn)行行。二者之間可以以相互調(diào)用。。PL/SQL支持?jǐn)?shù)據(jù)庫庫使用的多種種標(biāo)準(zhǔn)數(shù)據(jù)類類型。Oracle編程－PL/SQLPL/SQL塊的結(jié)構(gòu)變量聲明（可可選部分）程序代碼異常處理（可可選部分）DECLARE…BEGIN…EXCEPTION…END;Oracle編程－PL/SQL變量聲明包含內(nèi)容賦予變量適當(dāng)當(dāng)?shù)拿Q賦予變量正確確的數(shù)據(jù)類型型定義標(biāo)量變量量和記錄類型型變量（記錄錄由標(biāo)量組成成）控制變量的范范圍命名規(guī)則變量名必須由由字符開頭在首字符之后后，變量名應(yīng)應(yīng)包括：字母母、數(shù)字、_、$、#變量的長度范范圍：1～30變量名大小寫寫不敏感變量名不能和和語言中的關(guān)關(guān)鍵子重復(fù)數(shù)據(jù)類型Oracle編程－PL/SQLDBMS_OUTPUT包使用前定義：：SETSERVEROUTPUTONDBMS_OUTPUT包允許使用用SQL*Plus從PL/SQL中顯示輸出出信息輸出過程DBMS_OUTPUT.PUT_LINE允許顯示一行行文本DBMS_OUTPUT.PUT允許顯示一段段文本，但不不能生成換行行符DBMS_NEW_LINE生成換行符Oracle編程－PL/SQL語句類型分支控制語句句IFexpressionTHENstatements[ELSIFexpressionstatements][ELSEstatements]ENDIF;Oracle編程－PL/SQL循環(huán)語句PL/SQL支持三種不不同類型的循循環(huán)結(jié)構(gòu)：WHILE循循環(huán)FOR循環(huán)基本循環(huán)WHILE循循環(huán)WHILEexpressionLOOPstatementsENDLOOP;FOR循環(huán)FORcounterIN[REVERSE]startvalue……endvalueLOOPstatementsENDLOOP;Oracle編程－PL/SQL基本循環(huán)沒有內(nèi)部控制制的循環(huán)結(jié)構(gòu)構(gòu)，在程序代代碼中編寫代代碼來結(jié)束該該循環(huán)。LOOPstatementsENDLOOP;EXIT語句句EXIT是只只有一個(gè)單詞詞的語句，它它能夠結(jié)束一一個(gè)循環(huán)。EXITWHEN語句句EXITWHEN語句句是EXIT語語句的的一個(gè)個(gè)更新新的版版本。。EXITWHENexpression;Oracle編編程－－PL/SQLNULL語語句NULL語語句什什么也也不做做。使使用NULL語語句的的唯一一原因因就是是把它它當(dāng)作作一個(gè)個(gè)占位位符。。IFx=2THENDBMS_OUTPUT.PUT_LINE(‘x=2’);ELSENULL;ENDIF;Oracle編編程－－PL/SQLGOTO語語句GOTOlabel;DECLARExNUMBER:=0;BEGINx:=0;<<repeat_loop>>x:=x+1;DBMS_OUTPUT.PUT_LINE(x);IFx<3THENGOTOrepeat_loop;ENDIF;END;Oracle編編程－－PL/SQL異常處處理標(biāo)準(zhǔn)異異常的的檢測測使用SQLERRM使用WHEN語語句WHENexception[ORexception……]THENstatementsWHENOTHERSTHENstatementsOracle編編程－－PL/SQL異常處處理的的例子子CREATEORREPLACEFUNCTIONgetAnimalName(animal_idINNUMBER)RETURNvarchar2ISanimal_namevarchar2(20);BEGINSELECTanimal_nameINTOgetAnimalName.animal_nameFROMaquatic_animalWHEREid_no=animal_id;RETURNanimal_name;EXCEPTIONWHENNO_DATA_FOUNDORTOO_MANY_ROWSTHENRETURN‘‘Non-existentornon-uniqueID’’;WHENVALUE_ERRORTHENRETURN‘‘Animalnametoolong’’;WHENOTHERSTHENRETURN‘‘Namecouldnotbedetermined’’;END;Oracle編編程－－PL/SQL產(chǎn)生異常常的原因因SELECTINTO語句句如果該語語句返回回值少于于或多于于一行，，異常就就會(huì)發(fā)生生任何類型型的SQL語句句如INSERT、UPDATE、DELETE語語句任何類型型的游標(biāo)標(biāo)過程語語句如OPEN、CLOSE、FETCH語句句嵌入表和數(shù)數(shù)組的使用用當(dāng)使用這些些對象類型型時(shí)，如果果使用了無無效的索引引值、對象象初始化操操作失敗以以及對空元元素的訪問問均會(huì)引起起異常Oracle編程－－PL/SQL從PL/SQL中執(zhí)行行SQL使用游標(biāo)FOR循環(huán)環(huán)FORrecord_nameIN(select_statement)LOOPstatementsENDLOOP;例如：BEGINFORanimalIN(SELECTanimal_nameFROMaquatic_animalORDERBYanimal_name)LOOPDBMS_OUTPUT.PUT_LINE(animal.animal_name);ENDLOOP;END;Oracle編程－－PL/SQL使用標(biāo)準(zhǔn)的的游標(biāo)進(jìn)程程在PL/SQL程序體體中的聲明明部分聲明明一個(gè)游標(biāo)標(biāo)使用OPEN語句打打開這個(gè)游游標(biāo)編輯一條或或多條FETCH語語句取回這這個(gè)游標(biāo)的的SELECT語句句所返回的的結(jié)果使用CLOSE語句句關(guān)閉這個(gè)個(gè)游標(biāo)Oracle編程－PL/SQL游標(biāo)的聲明明CURSORcursorname[(parametername[IN]datatype[{:=|DEFAULT}value][,(parametername[IN]datatype[{:=|DEFAULT}value…])]ISselect_statement;例如：CURSORanimal_list(tank_numberNUMBER)ISSELECTanimal_nameFROMaquatic_animalWHEREtank_no=tank_numberORDERBYanimal_name;Oracle編程－－PL/SQL打開一個(gè)游游標(biāo)OPENcursorname[(parametervalue[,(parametervalue…])];例如：OPENanimal_list(1);關(guān)閉一個(gè)游游標(biāo)CLOSEcursorname;例如：CLOSEanimal_list;Oracle編程－－PL/SQL從游標(biāo)中取取數(shù)據(jù)FETCHcursornameINTO{recordname|variablename[,variablename……]};例如：FETCHanimal_listINTOanimal_name;LOOPFETCHanimal_listINTOanimal_name;…EXITWHENanimal_list%NOTFOUND;EXITLOOP;FETCHanimal_listINTOanimal_name;WHILEanimal_list%FOUNDLOOPFETCHanimal_listINTOanimal_name;…ENDLOOP;Oracle編編程程－－過過程程存儲(chǔ)儲(chǔ)過過程程的的典典型型結(jié)結(jié)構(gòu)構(gòu)CREATEPROCEDUREprocedurename(argumentlist)DeclarationSectionBEGINPL/SQLCodeEXCEPTIONPL/SQLExceptionHandlingCodeEND;Oracle編編程程－－過過程程存儲(chǔ)儲(chǔ)過過程程的的例例子子CREATEORREPLACEPROCEDUREdemoproc(vIDININTEGER)ASvNamevarchar2(60);BEGINSELECTnameINTOvNameFROMdemotableWHEREid=vID;EXCEPTIONWHENNO_DATA_FOUNDTHENvName:=‘nodatafound’;END;Oracle編程－－過過程程取消過過程DROPPROCEDUREprocedurename;修改過過程刪除過過程，，然后后重新新建立立這個(gè)個(gè)過程程使用CREATEORREPLACEPROCEDURE命令查看程程序錯(cuò)錯(cuò)誤信信息SHOWERRORSPROCEDUREprocedurename;檢索源源代碼碼USER_S