oracle高級(jí)語法(事物、函數(shù)、存儲(chǔ)過程、觸發(fā)器、異常)

Xmanager工具:可以遠(yuǎn)程的調(diào)用，在本地顯示一個(gè)圖形化。。。--------------------------tnsnames.oravi$ORACLE_HOME/network/admin/tnsnames.ora在Linux中，如何圖形化配置網(wǎng)絡(luò)服務(wù)名：進(jìn)入oracle用戶-----------netmgr-----------------------------------------------------Oracle-->SID(實(shí)例)刪除重裝數(shù)據(jù)庫配置助手dbca--》一般來說一個(gè)Oracle服務(wù)器只有一個(gè)實(shí)例TransactionProcessing（事務(wù)處理）...step10:選擇字符集編碼----------------------------------------------------------------------------------------------------------------------------------------------------事務(wù)：舉例：轉(zhuǎn)賬A-->B￥100A:updatetb_balancesetbalance=balance-100whereid=1001;B:updatetb_balancesetbalance=balance+100whereid=1002;上述操作必須：要么全部成功，要么全部失敗事務(wù)是一組DML組成的Commit：提交到SID，一旦提交不能修改Rollback：回滾到修改前鎖：鎖一般加在數(shù)據(jù)庫中的表上行鎖表鎖字段鎖-------------------------------------提交：commit;結(jié)束事務(wù)、刪除保存點(diǎn)、釋放鎖rollback;刪除保存點(diǎn)、釋放鎖保存點(diǎn)：savepoint保存點(diǎn)的名字rollbackto保存點(diǎn)的名字---------------------------------------------數(shù)據(jù)庫的事務(wù)隔離級(jí)別：1、讀未提交--》最不安全的2、可重復(fù)讀--》MYSQL的事務(wù)隔離級(jí)別3、讀已提交--》Oracle的事務(wù)隔離級(jí)別4、序列化-->一個(gè)一個(gè)操作，效率最低-----------------------------------------事務(wù)什么是開啟？SQLPLUS客戶端登陸成功后，默認(rèn)開啟事務(wù)什么時(shí)候提交？手動(dòng)提交commit;自動(dòng)提交客戶端正常退出exitcommit后，一定會(huì)重新開啟一個(gè)新的事務(wù)----------------------------只讀事務(wù)：settransactionreadonly;特點(diǎn)：只對(duì)當(dāng)前用戶當(dāng)前窗口有效應(yīng)用：統(tǒng)計(jì)某個(gè)時(shí)間點(diǎn)一共賣票的張數(shù)------------------------------**小技巧：dual-->sys用戶創(chuàng)建的動(dòng)態(tài)的表。。。selectlower('ABC')fromdual;Oracle的函數(shù)maxmin,avg,count,sum-->統(tǒng)計(jì)函數(shù)，where條件后面不能跟統(tǒng)計(jì)函數(shù)。Oracle的函數(shù)：必須有輸入，肯定有返回值字符串處理：lower(字符串)-->轉(zhuǎn)換成小寫，返回類型字符串upper(字符串)-->轉(zhuǎn)換成大寫返回類型字符串length(字符串);-->字符串的長度，返回類型數(shù)字substr(char,m,n)--》截取字符串，返回類型字符串Oracle的字符串，下標(biāo)從1開始------------------------Oracle數(shù)學(xué)函數(shù)round(n,[m])-->四舍五入取m位小數(shù)，返回?cái)?shù)字類型

trunc(n,[m])-->截取指定位數(shù)小數(shù)，保留m位

mod(m,n)--->取模

floor(n)--->不大于n的最大整數(shù)

ceil(n)--》不小于n的最小整數(shù)power(m,n)-->m的n次方abs(n)-->n的絕對(duì)值----------------------------------------------------------------------格式化輸出to_char(待轉(zhuǎn)換的數(shù)據(jù),'格式')日期的處理：to_date函數(shù)(將要成的字符串，‘格式’)to_date('1990-1-114:00:20','yyyy-mm-ddhh24:mi:ss');------------------------------------------------------------------------------sysdate-->返回當(dāng)前時(shí)間add_months(d,n)-->在原有日期基礎(chǔ)上加上n個(gè)月add_months(sysdate,3);-->3個(gè)月后的今天last_day(日期)-->返回日期所在月份最后一天計(jì)算日期：selectsysdate-to_date('2014-3-114:00:00','yyyy-mm-ddhh24:mi:ss')fromdual;-----------------視圖：虛擬的表,只讀的，保存的是一個(gè)查詢的結(jié)果view_test;select*fromempwheredeptno=20;select*fromview_test;----------------------createview視圖名稱as查詢結(jié)果表一開始沒有創(chuàng)建視圖的權(quán)限：grantcreateviewtotest;--------------------------------數(shù)據(jù)庫的備份和恢復(fù)：備份的幾種方式：1）createtableemp_bakasselect*fromemp;2）導(dǎo)出sql文件3）使用工具exp前提：只要安裝Oracle的客戶端，就有這個(gè)工具exp命令如何使用？userid--》登錄到數(shù)據(jù)庫的用戶名密碼，網(wǎng)絡(luò)服務(wù)名test/123456@abctables-->需要備份哪些表file-->客戶端生成的文件放在哪個(gè)位置rows=n

--》只導(dǎo)出表結(jié)構(gòu)，不導(dǎo)出表的數(shù)據(jù)1）舉例：備份emp表expuserid=test/123456@abctables=(emp,dept)file=d:\test\1.dumpimp導(dǎo)入工具，恢復(fù)empimpuserid=test/123456@abctables=empfile=d:\test\1.dumprows=n;-------------------------------------------了解：數(shù)據(jù)字典動(dòng)態(tài)性能視圖：需要了解用戶到底創(chuàng)建了哪些表？？alteruserscottaccountunlock;select*fromall_tableswhereowner='yang';----看yang用戶創(chuàng)建的所有的表select*fromuser_all_tables;----------查看所有的用戶的表select*fromv$session;--------------查看有哪些終端連入oracleselect*fromdba_sys_privswheregrantee='RESOURCE';select*fromdba_tab_privswheregrantee='TEST';select*fromdba_role_privswheregrantee='TEST';什么是表空間？在存儲(chǔ)里面****.dbf文件,存放表和視圖、索引-----------------------------------------------數(shù)據(jù)的完整性：Student:idnameclassNo(fk)1zhangsan10Class:cidcname10class10-------------------------使用約束，維護(hù)數(shù)據(jù)的完整性約束：限定不能做什么！??！Oracle的約束：非空notnull：字段輸入必須有值唯一unique：字段的值不能重復(fù)主鍵primarykey：notnull+unique外鍵foreignkey:引用另外一個(gè)地方的一個(gè)字段checked：限定只能輸入固定的值添加約束：1、屬性級(jí)約束（在建表的外面添加約束）（建議使用，因?yàn)檫@樣可以方便添加注解，增加代碼的可讀性）第一種分類，列的屬性notnull,uniquealtertabletb_balancemodify(字段類型notnull);createtabletb_balance(acctidnumber,balancenumber(9,2));第二種分類，必須是oracle中的一個(gè)對(duì)象primary,foreignkeyaltertable表名addconstraint約束名約束類型約束名定義規(guī)則：pk_XXXXfk_XXXXaltertable表名addconstraint約束名primarykey(字段);altertabletb_balanceaddconstraintfk_bankforeignkey(bank)referencestb_bank(bankid);刪除約束：altertabletb_balancedropconstraint約束名;----------------------------------------------2、表級(jí)約束(寫在表里面，缺點(diǎn)：不利于注解鍵的作用)：Createtabletb1(C#varchar2(20),Namevarchar2(20)Primarykey(C#),ForeignKey(C#)referencesT(T#))-----------------------------------------------------------索引（index）：為了提高查詢效率。創(chuàng)建索引后搜索機(jī)制將會(huì)自動(dòng)優(yōu)先調(diào)用索引。Studentidname1zhangsan2lisi....此處省略一萬行10001zhangsan10002zhangsan查詢名字是zhangsan的學(xué)生select*fromstudentwherename='zhangsan'沒有索引：共查詢10003次。。。-----------------------索引：name字段建立索引zhangsan1,10001lisi2wangwu3,100....共查詢兩次?。。?----------------------------------問題：select*fromstudentwherenamelike'zhang%'--》模糊查詢使用不到索引，全文的遍歷提高大數(shù)據(jù)量訪問的效率：1、建立索引2、全文檢索借助第三方工具sphinx,coreseek3、表分區(qū)按照用戶id分區(qū)1~10--》分區(qū)111~20--》分區(qū)24、分庫，分表------------------------------------------索引的問題：如果在數(shù)據(jù)量激增的情況，后面推薦使用重新建立索引1、創(chuàng)建索引：createindex索引名on表名（字段）2、刪除索引dropindex索引名;建立索引的原則：1、在大表上建立索引才有意義

2、在where子句或是連接條件上經(jīng)常引用的列上建立索引

3、索引的層次不要超過4層createindexemp_idx1onemp(ename,job);select*from時(shí)empwhereENAME='XXXX'ANDjob='CLERK'--》能使用到索引select*fromempwherejob='CLERK'andENAME='XXXX'-->使用不到索引也是保存在表空間上的，占用空間一般是表大小的1.2倍----------------------------------------------------PL/SQL-->SQL編程函數(shù)，存儲(chǔ)過程，觸發(fā)器函數(shù)：count,sum,lower...統(tǒng)計(jì)函數(shù)、數(shù)學(xué)函數(shù)、用戶自定義函數(shù)等，必須有且只有一個(gè)返回值存儲(chǔ)過程：Oracle服務(wù)端的一個(gè)代碼，節(jié)約帶寬，可以沒有、有一個(gè)或者多個(gè)返回值。觸發(fā)器：當(dāng)你操作一個(gè)表的時(shí)候，可以觸發(fā)一段代碼。---------------------存儲(chǔ)過程：1、兩種形式：臨時(shí)存儲(chǔ)過程永久存儲(chǔ)過程臨時(shí)存儲(chǔ)過程：1、最簡單的存儲(chǔ)過程：updatetb_balancesetbank=1;declare變量列表begin要編寫的代碼end;/：表示立即執(zhí)行edit--》編輯上一條命令-------------------createporocedure存儲(chǔ)過程名字is變量列表begin要編寫的代碼end;--------------------------如何調(diào)用存儲(chǔ)過程exec存儲(chǔ)過程名（或）call存儲(chǔ)過程名-------------------------showerrors;-->顯示錯(cuò)誤如何讓存儲(chǔ)過程在屏幕輸出一個(gè)值？setserveroutputon;----------------------------存儲(chǔ)過程函數(shù)觸發(fā)器塊：begin---end;包：集合，包含函數(shù)，觸發(fā)器，和存儲(chǔ)過程Paktest_prodexecPak.test_prod-----------------------------------如何輸出到屏幕dbms_ouput.put_line(字符串)---------------------------------------------標(biāo)識(shí)符號(hào)的命名規(guī)范定義變量，建議使用v_開頭：

定義常量，建議使用c_開頭；定義游標(biāo)，建議使用_cursor為后綴；定義異常時(shí)，建議使用e_作為前綴--------------------------------------練習(xí)：1、查詢員工的工資，并且打印出來declare--變量的定義v_salnumber(9,2);beginselectsalintov_salfromempwhereempno='&empno';--可以和鍵盤交互dbms_output.put_line(v_sal);end;/2、查詢員工的姓名和工資，并且打印出來(一個(gè)select后面只能有一個(gè)into)SMITH的工資是800declarev_namevarchar2(20);v_salnumber(9,2);beginselectename,salintov_name,v_salfromempwhereempno=&empno;dbms_output.put_line(v_name||'的工資是'||v_sal);end;/**注意：永久存儲(chǔ)過程不能使用鍵盤交互--------------------------------''-->自動(dòng)轉(zhuǎn)換，一般來說，寫SQL一定要注意類型，但是如果將number類型寫成字符類型，數(shù)據(jù)庫有自動(dòng)轉(zhuǎn)換的功能，但其他的類型錯(cuò)誤不行。t1(idnumber)insertintot1values(1);insertintot1values('1');----------------------------------異常和錯(cuò)誤？？錯(cuò)誤：程序代碼無法控制的一部分，一旦產(chǎn)生程序?qū)⒘⒓唇K止。異常：程序運(yùn)行過程中可能會(huì)遇到的問題，程序可以捕捉并且處理==異常處理。如：1/0-->除數(shù)為0select查詢，找不到記錄！??！是異常declare變量列表begin//執(zhí)行的代碼exception//異常處理的部分when異常名稱thenbegin//處理代碼。。endend;----------------------------2、查詢員工的姓名和工資，并且打印出來(一個(gè)select后面只能有一個(gè)into)SMITH的工資是800declarev_namevarchar2(20);v_salnumber(9,2);beginselectename,salintov_name,v_salfromempwhereempno=&empno;dbms_output.put_line(v_name||'的工資是'||v_sal);exceptionwhenno_data_foundthenbegindbms_output.put_line('記錄未找到');end;end;/----------------------異常的分類：1、Oracle預(yù)定義2、自定義-------------------if條件then.....elsif條件2then.....else.....endif;---------------------練習(xí)：查詢一個(gè)員工的薪資，如果太少(<1000)，則顯示“機(jī)密”（必須用異常）1)異常的定義ex_sal_lowexception;2）如何拋出異常declarev_salnumber(9,2);ex_sal_lowexception;beginselectsalintov_salfromempwhereempno=&empno;ifv_sal<1000thenraiseex_sal_low;endif;dbms_output.put_line(v_sal);exceptionwhenex_sal_lowthenbegindbms_output.put_line('薪資太少，機(jī)密');end;end;-----------------------------------------如何賦值？？1、Select???into變量from....//從表中查詢到的數(shù)據(jù)賦值2、:=賦值號(hào)//直接進(jìn)行變量間的賦值或者常量賦值練習(xí)：兩個(gè)數(shù)字num1:10,num2:20，計(jì)算num1+num2，打印結(jié)果declarenum1number;num2number;rsnumber;beginnum1:=10;num2:=20;rs:=num1+num2;dbms_output.put_line(rs);end;--------------------循環(huán)forwhile練習(xí)：使用for循環(huán)打印1~10declarebeginforiin[reverse]1..10loopdbms_output.put_line(i);endloop;end;使用while循環(huán)打印1~10declareinumber:=1;beginwhilei<=10loopdbms_output.p