1、ZealjiangOracle知識點總結根據閻赫老師講義整理1、Oracle數據庫的安裝和配置OracleInternet級數據庫SQLServer中小企業級數據庫Access桌面級數據庫Oracle的安裝注意：來源和目標的目錄不允許有中文或空格Oracle數據庫啟動時必需開啟的后臺服務 OracleOrahome90TNSListener使第三方的軟件或語言訪問 OracleServiceETCOracle的實例CRUD增刪改查注意：中的ETC是你起的實例的名字Oracle的開發工具 DO翳面的開發平臺->運行->sqlplus Oracle本身基于DOS勺平臺->運行-&

2、gt;sqlplusw OracleEnterpriseManagerConsolePL/SQLDeveloper7.1.5創建一個表的完整流程 創建一個數數庫例子：創建一個數據庫ETC,物理文件放在F:,初始化1m,白增長開啟createtablespaceetcdatafile'f:etc.dbfsize1mautoextendon;刪除表空間droptablespace數據庫名稱；為該數據庫創建一個帳號和密碼語法：createuser用戶名稱identifiedby密碼defaulttablespace數據庫名稱注意：1、在Oracle中賬號和數據庫one2one綁定、Oracl

3、e的密碼不允許為純數字或空、需要在system賬號完成動作修改Oracle的賬號密碼語法：alteruser用戶identifiedby新密碼用grant權限to用戶為該帳戶授權語法：grant權限名稱to用戶；撤銷權限語法：revoke權限名稱from用戶名；在該帳號下創建表Oracle中的事務處理Transacation事務特點：整體提交（commit）整體回滾（rollback）事務的四個特性原子性不可分割持久性->當數據整整寫入到數據庫物理文件中后，該數據被持久化隔離性->事務之間相互獨立互不干擾一致性->數據安全Oracle中的保留點（還原點）關鍵字：savepoi

4、nt使用方法：savepoint名稱；如進行保留點回滾rollbacktomark2Oracle備份數據表數據 createtable備份表名稱as查詢語句； Oracle備份表結構createtableemp_bakasselect*fromempwhere1=2;2、Oracle的函數function（系統預定義函數）函數和存儲過程相比，在于函數必須有返回值，而存儲過程只有輸出參數。語法:函數名稱createorreplacefunctionreturn返回類型asPL/SQL語句塊例子：輸入一個員工工號，返回該員工所在部門平均工資createorreplacefunctionfun_de

5、mo1(enoemp.empno%type)returnemp.sal%typeasavgsalemp.sal%type;beginselectavg(sal)intoavgsalfromempwhereempno=eno;returnavgsal;end;調用：selectfun_demo1(7788)fromdual;publicintadd(intnum1,intnum2)intres=0;res=num1+num2;returnres;單行函數（單值函數）：函數有且只有返回1個值substrinstr 字符函數|-字符串連接函數：concatconcat（字符串1,字符串2） Orac

6、le中字符連接操作符號|字符串對齊函數：lpad->leftpadding左填充/rpad語法：lpad（字符串,預留位置,填充字符）SQL:selectlpad（ename,10,''）fromemp;字符串截取函數：substr->substring語法：substr（字符串，截取的起始位置，截取個數）substr（字符串，截取的起始位置）SQL:>selectsubstr（ename,1,3）fromeemp;字符查找函數：instr->innerstring語法：instr（字符串，待找的字符，查找的起始位置，出現次數）SQL:>selec

7、tename,instr（ename,'T',1,1）fromemp;注意：當返回值為0時，字符不存在函數：initcap->initialcaptor語法：initcap（字符串）SQL:>selectinitcap(ename)fromemp;函數：length語法：length(字符串)SQL:>select*fromempwherelength(ename)=5;函數：lower/upper)字符串lower(語法：SQL:>selectlower(ename)fromemp; |-日期函數sysdateadd_months函數:sysdateS

8、QL:>selectsysdatefromdual;dual-無實際意義,函數測試或其他測試使用月份差:months_between語法：months_between(日期1,日期2)SQL:>select*fromempwheremonths_between(sysdate,hiredate)>=144函數：last_day語法：last_day(日期數據)SQL:>select*fromempwherelast_day(hiredate)-2=hiredate;函數:add_months語法：add_months(時間，添加月份)SQL:>selectadd_

9、months(sysdate,3)fromdual;|-轉換函數to_charto_date轉換函數to_char語法：to_char(時間數據，制定格式)SQL:>selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ssddddday')fromdual;yyyy-mm-dd年月曰hh24:mi:ss時分秒(24)d一周第幾天ddd一年的第幾天day星期幾 to_date:將文本類型數據按照制定格式轉換成時間類型數據語法：to_date('具體的時間字符數據','時間字符串格式')to_date('2

10、010-01-19','yyyy-mm-dd')注意：Oracle中的date字段類型及其特殊|-數學函數absceilfloorabs(n)絕對值ceil(n)向上取值ceilling天花板ceil(1.1)->2floor(n)向下取值floor地板floor(2.9)->2mod(m,n)求模取余mod(5,2)->1power(m,n)m的n次方power(2,8)->256round(m,n)四舍五入m待處理數據n精度round(3.1415,2)->3.14trunc(m)整數截取trunc(3.1415)=3sign(m)符號

11、sqrt(m)平方根|-混合函數usernvl返回當前登錄賬號user:SQL>selectuserfromdual;nvl:替空函數語法：nvl(字符串，替換后的數據)注意：替換后的數據必須與該字段類型保持一致SQL>selectename,nvl(comm,0)fromemp;分析函數：數據分析和挖掘功能DataMiningrankdense_rank作用：主要用于排名使用函數：rank語法：rank()over(orderby字段名稱)SQL:>selectrank()over(orderbysaldesc),ename,salfromemp;若排名相同下一個將跳轉SQ

12、L:>selectdense_rank()over(orderbysaldesc),ename,salfromemp;連續語法：rank(參數1,參數2)withingroup(orderby字段1,字段2)SQL:>selectename,rank(2850,'BLAKE')withingroup(orderbysal,ename)fromemp;分組函數：類似于SQLServer中的聚合函數sumavgcountgroupby分組關鍵字having條件3、Oracle數據庫的查詢制定區間查詢都)包括用戶白定義創建的數據表(為每張數據表Oracle中偽列：Orac

13、le白動創建兩個位列分別是：rowid、rownum通過命令：SQL>desc表名；-查看表結構rowid:由18個字符組成唯一標識每一行rownum:行號介紹問題：使用Oracle分層查詢以數據結構顯示emp表中的人員關系語法:selectlpad(ename,level*5,'')fromempconnectbypriorempno=mgrstartwithmgrisnull;并集：查詢部門10的辦事員和部門20的經理關鍵字：unionallselect*fromempwheredeptno=10andjob='CLERK'unionallselect

14、*fromempwheredeptno=20andjob='MANAGER'注意：unionall不會消除查詢結果中的重復數據，union會消除查詢結果中的重復記錄交集：查詢部門10和部門20都有的工作類型關鍵字：intersectselectjobfromempwheredeptno=10intersectselectjobfromempwheredeptno=20;差集：查詢部門30中有，而部門10中沒有的工作類型minus關鍵字：selectjobfromempwheredeptno=30minusselectjobfromempwheredeptno=10;4、Orac

15、le數據庫的數據對象Oracle的數據庫對象：用戶、表、約束、序列、視圖、同義詞和索引定義：但凡使用create開頭創建的對象稱之為數據庫對象。鎖定/解除用戶語法：alteruser用戶名稱accountlock;注意：該指令一般在system賬號下輸入解除用戶鎖定語法：alteruser用戶名稱accountunlock;SQL>alteruserscottaccountunlock;注意：Oracle11g中scott賬號是默認鎖定的，需要進行解鎖處理。用戶授權/撤銷grant權限名稱to用戶名稱；撤銷用戶權限revoke權限名稱from用戶名；表數據字段的類型數字類型：number

16、)精度，長度number(語法：number(5,2)數字長度為3,精度為2范圍：-999.99999.99number(5)數字長度為5,精度為0范圍：-9999999999number精度默認為0范圍：-3276732768 字符類型：varchar2可變長度char字符類型語法：varchar2（長度） 日期類型：date long數據類型：該字段最大存儲空間為2GB該字段不允許添加索引LOB數據類型：該字段最大存儲空間為4GB該字段不允許添加索引CLOB:大子符類型->文章（小說，cnki）BLOB:大一進制類型->圖片、首碩、視碩.多媒體文件FLOB:文件定位器->

17、內存指針創建數據表的語法結構：createtable表名（字段名稱1類型約束,字段名稱1類型約束,字段名稱n類型約束）使用desc表名查看表結構telephone和address給表添加字段語法：altertable表名add（字段名稱類型）;刪除表字段address語法：altertable表名dropcolumn字段名稱； 修改表中已有字段的類型語法：altertable表名modify（字段名稱新類型）； 修改表名稱語法：rename原表名to新表名；約束完整性約束=準確性+一致性約束的四大分類： 實體完整性約束（行約束）->盡量減少數據表中數據的冗余（重復的數據）技術實施：主鍵約

18、束、唯一約束語法：altertable表名addprimarykey（字段名稱）;altettable表名addunique（字段名稱）；添加非空約束語法：altertable表名modify（字段名稱notnull）; 域完整性約束（列約束）->達到數據的準確性，控制數據的大小或范圍或格式技術實施：check檢查約束altertable表名addconstraint約束名稱check（條件）;達到數據的一致性）->表間約束（引用完整性約束技術實施：外鍵約束添加外鍵約束altertable夕卜鍵表addconstraint約束名稱foreignkey（外鍵字段）references

19、主鍵表（主鍵字段）;刪除外鍵約束語法：altertable表名dropconstraint約束名稱；刪除匿名約束語法：altertable表名modify（字段名稱null）;白定義完整性約束->以上三種數據庫內置約束不能滿足開發人員的需求是，需要開發人員白定一些約束條件技術實施：觸發器使用sql腳本批量插入數據使用命令SQL>路徑+文件名稱SQL>f:/a.sql;序列定義：Oracle中使用sequence來實現字段的白增長功能，和SQLServer中的identity屬性類型語法：createsequence序列名稱startwith起始數字incrementby增長量

20、;用法：序列對象通過兩個重要的屬性進行訪問取值下一個值.nextval->nextvalue->例子:selectseq_1.nextvalfromdual;.currval->currentvalue->當前值例子:selectseq_1.currvalfromdual;如何實現其白增長功能通過語法結構將序列對象與對應的數據表進行綁定，實現其白增長功能。例子：createtableusers(useridnumber(2),usernamevarchar2(10);createsequenceseq_usersstartwith1incrementby1;insert

21、intousersvalues(seq_users.nextval,'alvin');注意：Oracle建議一個序列對象盡與一張數據表進行綁定技巧：createsequence序列名稱；默認從1每次增長1視圖作用：1、簡化復雜的SQ薛句2、提高數據的訪問安全性語法：createorreplaceview視圖名稱as復雜的SQL查詢語句視圖是一張虛擬的數據表，在Table對象中不存在，只存在于內存中注意：若視圖由1張基表組成，修改視圖將會修改基表數據，若視圖由多張表組成，則不會修改基表數據同義詞作用：Oracle中的同義詞提供各種數據庫對象(表)的別名，目的在于提高數據表訪問的安

22、全性，尤其多用戶并發訪問時。語法：createsynonym表的另U名for表名;SQL>createsynonymbaklforemp;擴展：公有同義詞public語法：createpublicsynonym另U名for表名;(1。)索引作用：提高SQL查詢語句按照制定字段查詢的效率語法：createindex索引的名稱on數據表(字段)例子：為hiredate字段添加索引，提高按日期查詢的SQL語句的效率createindexidx_hireonemp(hiredate)原理：空間換效率SQL語句索引優化規則 不帶where條件的SQL語句一定不能使用索引 在where條件中有索引的

23、字段不能使用函數例子：假設我們查詢在1987年參加工作的所有員工SQL>select*fromempwhereto_char(hiredate,'yyyy')='1987'SQL>select*fromempwherehiredate=to_date('1987','yyyy'); 在where條件中有索引的字段不能參與運算天以前參加工作的員工信息10000例子：查詢在SQL>select*fromempwheresysdate-hiredate>10000;SQL>select*fromempwhe

24、rehiredate<sysdate-10000; 盡量少用notin,notexists,like'%'等關鍵字 書寫查詢語句的時候，應該首先考慮關聯查詢、其次考慮集合查詢，最后考慮子查詢 where條件中限制性強的條件應該寫在where最后的一個條件where條件從右向左經行條件解析 多表查詢中，驅動表應該是返回數據少的表例子：查詢部門account的所有員工信息SQL>selecte.*,d.dnamefromempe,deptdwheree.deptno=d.deptnoandd.dname='ACCOUNTING'(11)Oracle中常用

25、的數據字典Oracle是以中以表管表的模式，同時數據字典是有表或視圖組成。數據字典的分類：USER_xxx:表示當前用戶所擁有的數據庫對象ALL_xxxx:表示當前用戶與權力查看的數據庫對象DBA_xxxx:表示數據中所擁有的全部對象，只有在超級管理員級別下才可查看。xxxx:代表數據庫對象的復數形式，例如：tablesindexes5、Oracle數據庫的多表查詢等連接:selectemp.ename,dept.dnamefromemp,deptwhereemp.deptno=dept.deptno;使用內連接模式編寫：內連接:innerjoinonselectemp.ename,dept.

26、dnamefromempinnerjoindeptonemp.deptno=dept.deptno;分析：部門為主顯示字段，dept為主表姓名為輔助顯示字段，emp為輔助表夕卜連接:selectd.dname,e.enamefromempe,deptdwheree.deptno(+)=d.deptno;6、Oracle數據庫的游標游標cursor游標的分類：隱式游標：Oracle白動應以一個隱式游標名稱為SQL,該游標不被程序員控制，白動開啟、操作及結束。顯式游標：可供程序員白己創建及操作|-靜態游標靜態游標loop循環游標/for循環游標=loop循環游標=步驟1:創建一個游標cursor游

27、標名稱isSQL查詢語句；步驟2:開啟游標open游標名稱；->執行定義的SQL查詢語句并將結果集合存放到游標中步驟3:使用loop循環遍歷游標中的數據并進行相應處理loopfetch游標名稱into變量；exitwhen游標名稱notfound;endloop；步驟4:關閉游標close游標名稱；例子：顯示部門編號為10的員工姓名declare-步驟1:創建一個游標cursormycurisselectenamefromempwheredeptno=10;enemp.ename%type;begin-步驟2:開啟游標openmycur;-步驟3:使用loop循環遍歷游標中的數據并進行相

28、應處理loopfetchmycurintoen;exitwhenmycur%notfound;dbms_output.put_line(en);endloop;-步驟4:關閉游標closemycur;end;=for循環游標=declare-步驟1:創建一個游標cursormycurisselect*fromempwheredeptno=10;erowemp%rowtype;begin-步驟3:使用for循環遍歷游標中的數據并進行相應處理forerowinmycurloopdbms_output.put_line（erow.ename）;endloop;end;動態游標（擴展）作用：使用游標變

29、量高度重用（只能使用loop循環）語法結構：步驟1:聲明一個動態游標類型type類型名稱isrefcursor;步驟2:使用聲明號的類型創建一個游標變量游標名稱類型名稱；步驟3:打開游標并且綁定SQL語句open游標名稱forSQL查詢語句步驟4:使用循環遍歷游標中的數據并進行處理loopfetch游標名稱into變量；exitwhen游標名稱%notfound.數據處理.endloop;34:重復5步驟步驟6:關閉游標close游標名稱；例子：顯示部門30的員工姓名及dept表中全部部門名稱declare-聲明一個游標類型typecurisrefcursor;-聲明游標變量mycurcur;

30、-聲明變量erowemp%rowtype;drowdept%rowtype;begin-打開游標openmycurforselect*fromempwheredeptno=30;loopfetchmycurintoerow;exitwhenmycur%notfound;dbms_output.put_line(erow.ename);endloop;dbms_output.put_line('=');-打開游標openmycurforselect*fromdept;loopfetchmycurintodrow;exitwhenmycur%notfound;dbms_output

31、.put_line(drow.dname);endloop;-關閉游標closemycur;end;游標所有屬性游標名稱!性名稱屬性名稱含義notfound當游標中沒有遍歷的數據時返回1found當游標中存在有遍歷的數據時返回isopen當游標開啟狀態時返回1rowcount返回游標影響行數7、Oracle數據的PL/SQL(1)PL/SQL塊語法格式：declare聲明.變量聲明區begin程序開始執行語句區.exception異常處理部分end;程序結束；變量的聲明標量標識符，不區分大小寫，先聲明再使用變量聲明的語法結構：標量名稱類型；例子：declareenonumber(4);edat

32、edate;beginend; 屬性引用類型|-字段引用類型例子：declareenoemp.ename%type;|-行引用類型例子：declareerowemp%rowtype; 變量的賦值四種方式：聲明時賦值賦值運算符“:=”例子：聲明一個變量賦值為2declarenumlnumber(2):=2;beginend;執行區賦值例子：聲明一個變量賦值為2declarenum1number(2);beginnum1:=2;end;對話框賦值->程序與用戶之間進行數據交互例子：聲明一個變量賦值為2declarenumlnumber(2);beginnum1:=&請輸入數字;end

33、;注意點：當輸入字符串類型數據的使用書寫格式為：name:='&姓名'；當輸入數字類型數據的使用書寫格式為：age:=&年齡；o.賦值模式例子：將emp表中simth的工資復制到變量esal中declareesalemp.sal%type;beginselectsalintoesalfromempwhereename='SMITH' end;Oracle的PL/SQL中屏幕輸出語句將信息輸出并換行語法:dbms_output.put_line('xxxxxxx');dbms_output.put('xx

34、xxxxx');輸出不換行注意：Oracle默認關閉屏幕輸出功能，需要開發人員輸入指令將其功能開啟SQL>setserveroutputon;中的異常處理PL/SQLD.語法:exceptionwhen異常名稱then.處理方式.異常：others->任何異常都可捕獲執行語句部分 循環的使用：forwhileloopfor語法結構：for循環變量in起始數字.結束數字loop.循環體.endloop;例子1：顯示數字101(reverse)declareinumber;beginforiinreverse1.10loopdbms_output.put_line(i);end

35、loop;end;例子1:顯示乘法口訣declarei number;jnumber;begin-外層循環控制行數foriin1.9loop-內層循環控制個數forjin1.iloopdbms_output.put(j|'*'|i|'='|i*j|'');endloop;-換行dbms_output.put_line('');endloop;end; while循環語法：while進入條件loop.循環體.endloop;loop循環loop語法：exitwhen推出條件；.循環體.endloop;條件判斷語法：if條件thene

36、lsif條件thenelseendif;例子：模擬一個登錄結構，用戶名為admin密碼為chinasoftdeclareusernamevarchar2(10);passwordvarchar2（10）;begin-用戶輸入賬號及密碼username:='&賬號'password:='&密碼'-邏輯判斷ifusername='admin'andpassword='chinasoft'thendbms_output.put_line（'歡迎登錄:'|username）;elsedbms_output.

37、put_line（'賬號或密碼錯誤！'）;endif;end;處理白定義異常例子：若用戶輸入的數字不再110之內則拋出異常步驟1:創建一個異常對象在變量聲明區輸入一下代碼expexception;-聲明一個異常對象，名稱為exp步驟2：編寫條件判斷結構拋出異常在語句執行區編寫一下代碼ifthenraise異常對象名稱；-拋出異常endif;步驟3:異常捕獲在語句執行區編寫exceptionwhen異常對象名稱then處理方式declarennumber;-用來接收用戶輸入的數字expexception;-創建一個異常對象beginn:=&數字;-判斷ifn<1or

38、n>10thenraiseexp;-拋出異常endif;dbms_output.put_line(n);exceptionwhenexpthendbms_output.put_line('數字范圍不再110之內');end;8、Oracle數據庫中的存儲過程、函數、數據包Oracle中的存儲過程Oracle中的PL/SQL語句塊，沒有名字，成為匿名的PL/SQL塊，代碼重用性低。需要為PL/SQL塊起名字，起名后的PL/SQL塊統稱為子程序。 子程序的分類：存儲過程、白定義函數存儲過程存儲過程名稱createorreplaceprocedure語法:asPL/SQL塊如何

39、調用存儲過程兩種方式：1、指令方式->exec存儲過程名稱；、使用PL/SQL調用begin存儲過程名稱；end；帶參數的存儲過程兩大類：a)帶輸入參數b)帶輸出參數a)帶輸入參數的存儲過程語法:createorreplaceprocedure存儲過程名稱(參數名稱1in類型,.,參數名稱n類型）asPL/SQL塊b）帶輸出參數的存儲過程語法：createorreplaceprocedure存儲過程名稱（參數名稱1out類型,.,參數名稱nout類型）as塊PL/SQL.例子：顯示部門10的平均工資（使用輸出參數）createorreplaceprocedurepro_demo3（avg

40、saloutemp.sal%type）asbeginselectavg（sal）intoavgsalfromempwheredeptno=10;end;調用：只能使用PL/SQL塊declaregetsalemp.sal%type;beginpro_demo3(getsal);dbms_output.put_line(getsal);end;/1、輸入指定員工的員工編號，輸出其所在部門的其他姓名(存儲過程、帶參數的游標、輸入參數)思路：*1、使用SQL語句查詢7788員工所在部門的其他員工信息select*fromempwheredeptno=(selectdeptnofromempwhere

41、empno=7788)andempno<>7788;*2、修改成PL/SQL塊declarecursormycur(enoemp.empno%type)isselect*fromempwheredeptno=(selectdeptnofromempwhereempno=eno)andempno<>eno;erowemp%rowtype;tmpemp.empno%type;begintmp:=&工號;forerowinmycur(tmp)loopdbms_output.put_line(erow.ename);endloop;end;3 、修改成為存儲過程crea

42、teorreplaceprocedurepro_demo4(tmpemp.empno%type)ascursormycur(enoemp.empno%type)isselect*fromempwheredeptno=(selectdeptnofromempwhereempno=eno)andempno<>eno;erowemp%rowtype;beginforerowinmycur(tmp)loopdbms_output.put_line(erow.ename);endloop;end;*4、調用execpro_demo4(7788);/2、輸出制定員工所在部門的平均工資(存儲過程

43、、輸入參數、輸出參數)思路：*1、編寫SQL語句selectavg(sal)fromempwheredeptno=(selectdeptnofromempwhereempno=7788)* 2、PL/SQL語句declareenoemp.empno%type;esalemp.sal%type;begineno:=&編號;selectavg(sal)intoesalfromempwheredeptno=(selectdeptnofromempwhereempno=eno);dbms_output.put_line(esal);end;* 3、修改為存儲過程createorreplacep

44、rocedurepro_demo5(enoinemp.empno%type,esaloutemp.sal%type)asbeginselectavg(sal)intoesalfromempwheredeptno=(selectdeptnofromempwhereempno=eno);end;*4、調用declaretmpemp.sal%type;beginpro_demo5(7788,tmp);dbms_output.put_line(tmp);end;程序包程序包是一個函數或存儲過程的集合。程序包的結構：第一部分：程序包的聲明createorreplacepackage包名稱as存儲過程的聲

45、明；白定義函數的聲明；end;第二部分：程序包主體createorreplacepackagebody包名稱as存儲過程的實現；白定義函數的實現；例子：完成對EM軟進行增刪改的操作parti:createorreplacepackageemp_packprocedureadd_emp(enoemp.empno%type,enmemp.ename%type,dnoemp.deptno%type);procedureupdate_emp(enoemp.empno%type,esalemp.sal%type);proceduredelete_emp(enoemp.empno%type);end;part2:createorreplacepackagebodyemp_packas-添加procedureadd_emp(enoemp.empno%type,enmemp.ename%type,dnoemp.deptno%t