Oracle快速入門手冊1

/第一章Oracle入門1.1安裝1.2系統(tǒng)服務(wù)圖1-SEQ圖\*ARABIC\s11Oracle數(shù)據(jù)庫系統(tǒng)服務(wù)1.3數(shù)據(jù)庫和實例1.3.1數(shù)據(jù)庫數(shù)據(jù)庫是磁盤上存儲的數(shù)據(jù)集合。每個數(shù)據(jù)庫都有自己的名字，數(shù)據(jù)庫名是用于區(qū)分數(shù)據(jù)庫的一個內(nèi)部標識，是以二進制方式存儲在數(shù)據(jù)庫控制文件中的參數(shù)。數(shù)據(jù)庫創(chuàng)建之后不能再修改這個參數(shù)。它被寫入數(shù)據(jù)庫參數(shù)文件p中。1.3.2數(shù)據(jù)庫實例 實例是一組后臺進程和共享內(nèi)存。數(shù)據(jù)庫實例是操作數(shù)據(jù)庫的實體，用戶通過實例與數(shù)據(jù)庫交互。每個數(shù)據(jù)庫實例都有自己的名字，實例名用來標識這個數(shù)據(jù)庫實例。數(shù)據(jù)庫創(chuàng)建后，實例名可以被修改。它也被寫入數(shù)據(jù)庫參數(shù)文件p中。1.3.3兩者關(guān)系 數(shù)據(jù)庫是磁盤上存儲的數(shù)據(jù)集合。? 數(shù)據(jù)庫可以由一個或多個實例（使用RAC）裝載和打開。 實例是一組后臺進程和共享內(nèi)存。 實例“一生”只能裝載并打開一個數(shù)據(jù)庫。數(shù)據(jù)庫名與實例名可以相同。一個數(shù)據(jù)庫對應(yīng)一個實例的情況下設(shè)置成相同的便于標識數(shù)據(jù)庫。但是在8i,9i的并行服務(wù)器中，數(shù)據(jù)庫與實例不存在一一對應(yīng)關(guān)系，而是一對多關(guān)系，一個數(shù)據(jù)庫對應(yīng)多個實例。不過一個用戶只能與一個實例相連。1.3.4數(shù)據(jù)庫物理結(jié)構(gòu)圖1-SEQ圖\*ARABIC\s12Oracle數(shù)據(jù)庫的物理結(jié)構(gòu)?參數(shù)文件數(shù)據(jù)庫參數(shù)文件并不是數(shù)據(jù)庫文件系統(tǒng)中的有效組成部分，因為在啟動數(shù)據(jù)庫的時候，參數(shù)文件并不直接參與工作。但是數(shù)據(jù)庫參數(shù)文件中，記錄著數(shù)據(jù)庫控制文件的物理地址，所以要靠它來尋找控制文件。圖1-3數(shù)據(jù)庫參數(shù)文件?文件系統(tǒng)圖1-4Oracle數(shù)據(jù)庫的文件系統(tǒng)*.CTL表示控制文件*.DBF表示數(shù)據(jù)文件*.LOG表示日志文件控制文件用來管理和控制數(shù)據(jù)文件和日志文件。在啟動數(shù)據(jù)庫的時候，啟動實例之后，就啟動對應(yīng)的控制文件，接著由控制文件打開數(shù)據(jù)文件。databaseamount就是打開控制文件，databaseopen是打開數(shù)據(jù)文件。在Oracle中，有兩種類型的日志文件：圖1-4中的REDO*.LOG稱為聯(lián)機日志文件，也成為重做日志文件。如果REDO01.LOG寫滿則寫REDO02.LOG，REDO02.LOG寫滿了寫REDO03.LOG，而REDO03.LOG寫滿了又會重新寫入REDO01.LOG，這是聯(lián)機日志文件中的非歸檔方式。還有一種稱為歸檔日志文件，是在備份尤其是熱備份的時候，一定要選擇的一種歸檔方式。1.4內(nèi)存結(jié)構(gòu)當一個Oracle實例啟動之后，它分配了一個稱為SGA（系統(tǒng)全局區(qū)）的內(nèi)存塊圖1-5Oracle數(shù)據(jù)庫內(nèi)存結(jié)構(gòu)1.4.1緩沖區(qū)?DBbuffer（數(shù)據(jù)庫高速緩沖區(qū)）如果每次執(zhí)行sql語句，都要對磁盤數(shù)據(jù)進行讀寫，則效率會非常慢。內(nèi)存中被用來頻繁讀取數(shù)據(jù)的部分就稱為數(shù)據(jù)庫高速緩沖區(qū)。DBbuffer是SGA中最大的部分。它又分為以下三個部分：保持緩存池再生緩存池默認緩存池?共享池1.4.2Oracle相關(guān)進程1.5邏輯結(jié)構(gòu)1.5.1表空間1.5.2段1.5.3盤區(qū)1.5.4數(shù)據(jù)塊第二章SQL/PLUS基礎(chǔ)1.1登錄賬戶用sys/system賬戶，以sysdba的方式登錄，可以解鎖其他賬戶，如：alteruserscottaccountunlock;alteruserscottidentifiedbyaaaaa;connscott/aaaaa;注意：密碼是純數(shù)字，如123456是不對的1.2幫助SQL>helpindexSQL>?SET1.3替代變量和執(zhí)行計劃A:SQL>select*fromdeptwheredeptno=10;SQL>select*fromdeptwheredeptno=20;B:SQL>select*fromdeptwheredeptno=&tt;SQL>10SQL>select*fromdeptwheredeptno=&tt;SQL>20A和B得到的結(jié)果一樣，但A中啟動兩個執(zhí)行計劃，B中只有一個給用戶授權(quán)：Grantconnettoaaa;第三章SQL語言基礎(chǔ)1.3.1語言的分類DDL：數(shù)據(jù)定語言Create實例：createtableabc(avarchar2(10),bchar(10));創(chuàng)建表 altertableChinaCitymodifyCitySimplenull;修改表字段為空grodtableChinaCity刪除表Alter實例：altertableabcaddcnumber;添加表字段Drop實例：droptableabc;刪除表，altertableabcdropcolumnc;刪除表中的某一字段DCL:數(shù)據(jù)控制語言Grant實例：grantselectondepttott;授權(quán)給tt用戶有查詢的權(quán)限Revoke實例：revokeselectondeptfromtt;收回tt用戶的查詢權(quán)限D(zhuǎn)ML:數(shù)據(jù)操縱語言Select實例：select*fromabc;查詢abc表的值Insert實例：insertintoabc(a,b)values(‘a(chǎn)bc’,’xy’);為abc表賦值Update實例：updateabcsetb=’ttt’;修改abc表中所有的b改為tttupdateabcsetb=’yyy’wherea=’abc’;把abc表中a為abc的b改為yyyDelete實例：deletefromabc;把表里面的所有數(shù)據(jù)都情空deletefromabcwherea=’abc’;把表里面a為abc的刪除1.3.2常用的系統(tǒng)函數(shù)字符：length查字符，lengthb差字節(jié)，trim，ltrim和rtrim截掉空格和左右兩邊空格，substr（’abcdefj’,2,3）截取字符串，從第二個開始取三個，substr(‘a(chǎn)bcdefg’,length(‘a(chǎn)bcdefg’)-3+1,3)答案efg可變長（varchar2(10)）的存多少字符長度就為多少，對于不可變長的(char（10）)，沒有存滿也是原本定的長度所以為了避免不必要的浪費，一般定義為可變長日期：sysdate當前時間，current_date查詢當前時間，altersessionsetnls_date_format=’dd-mon-yyyyhh:mi:ss’;設(shè)定時間的格式next_day指定的某個星期幾是幾號selectnext_day(sysdate,’星期三’)fromdual;轉(zhuǎn)換：To_char(sysdate,’yyyy-mm-ddhh24:mi:ss’)把日期型傳喚出字符型,24小時制To_date(’12-3月-04’)把字符型轉(zhuǎn)換成日期型To_number(‘333’)字符型轉(zhuǎn)換成整型聚集函數(shù)：Sum()總數(shù)，max()最大值，min()最小值，avg()平均值，count()總記錄數(shù)其他：selectuserfromdual;查詢當前的登錄帳號selectsum(decode(sex,’男’,1,0)),sum(decode(sex,’女’,1,0))from表;統(tǒng)計男為幾個，女為幾個selecta1,nvl(a2,’為輸入’)a2from表;如果為空值就表面為輸入select*from表面orderbya1asc;升序排列select*from表面orderbya1desc;降序排列selectdistincta1from表面;去除重復(fù)數(shù)據(jù)分組語句：selectpub,sum(price)frombooksgroupbypub;使用groupby分組查詢各出版社的圖書價格selectpub,sum(price)frombooksgroupbypubhavingsum(price)>50;groupby結(jié)合having查詢總金額大于50的出版社名稱selecta1,count(a1)fromaagroupbya1havingcount(a1)>1;查詢a1字段重復(fù)出現(xiàn)的次數(shù)模糊查詢：select*fromaawherea1like‘a(chǎn)_’;使用通配符（like）查詢a1字段中以a開頭，任意多個字符結(jié)尾的數(shù)據(jù)select*fromaawherea1like‘%a%’;查詢表中a1有a字符的數(shù)據(jù)表連接：selecte.eid編號,姓名,e.sex性別,所在部門fromeinnerjoindone.id=d.id;內(nèi)鏈接selecte.eid編號,姓名,e.sex性別,所在部門frome,dwheree.id=d.id(+);左鏈接selecte.eid編號,姓名,e.sex性別,所在部門frome,dwheree.id(+)=d.id;右鏈接子查詢：（無關(guān)子查詢，相關(guān)子查詢）select*fromewhereidin(selectidfromdwhereid=e.idandid=’03’);select*fromewhereidnotin(selectidfromdwhereid=e.idandid=’03’);select*fromewhereexists(selectidfromd);判斷是否存在結(jié)果集select*fromewherenotexists(selectidfromd);判斷是否不存在結(jié)果集selecteid,namefromeunionselectid,namefromd;把兩張表合并成一張，去除重復(fù)數(shù)據(jù)selectidfromeintersectselectidfromd；返回兩個sql語句中都出現(xiàn)的行insertintoe(eid,ename)selectid,namefromd; 一次從別的表中插入多條記錄createtabletttas(select*frome);創(chuàng)建表是復(fù)制別的表中的數(shù)據(jù)進入新表createtabletasselecteid,enamefromewhereeid=’001’;選擇001的數(shù)據(jù)復(fù)制到新的表里面第四章PL/SQL基礎(chǔ)PL/SQL的結(jié)構(gòu)：Declare..聲明變量，賦予初值，可選項Begin…具體的操作Exception…聲明，實現(xiàn)異常處理部分end;/實例：dclarexvarchar2(10);yinteger:=123;zstring(10):=’123’;beginx:=’thisis…’;dbms_output.put_line(‘x的值為：’||x);--||表示連接字符，dbms_output是一個包，實現(xiàn)與外部的交互，put_line打印輸出；end;/setserveroutputonsize10000;設(shè)置顯示輸出信息，默認的是不輸出，設(shè)置字節(jié)大小為10000；量聲明：變量聲明的內(nèi)容：賦予變量適當?shù)拿Q，適當?shù)臄?shù)據(jù)類型，定義變量（標準變量，復(fù)合變量），控制變量范圍命名規(guī)則：變量由字符開頭，可以包含數(shù)字，下劃線，$,#等，變量長度范圍1~30，大小寫不區(qū)分，變量名不能是系統(tǒng)關(guān)鍵字存儲：savec:\plsql_01.text;執(zhí)行：@c:\plsql_01.text;修改：editc:\plsql_01.text;dbms_output.new_line表示在新行里面打印輸出；和dbms_output.put一起使用，也就是dbms_output.put_line分支語句：if分支語法：if…then….elsif….then….else…endif實例：declareanumber;bvarchar2(10);begina:=2;Ifa=1thenb:=’a’;elsifa=2thenb:=’B’;elseb:=’c’;endif;dbms_output.put_line(‘b的值是：’||b);輸出end;/case分支語法：casewhen…thenelseendcase實例：declareanumber;bvarchar2(10);begina:=2;casewhena=1thenb:=’A’;whena=2thenb:=’B’;whena=3thenb:=’C’;elseb:=’abc’;endcase;dbms_output.put_line(‘b的值是：’||b);輸出end;/循環(huán)語句：基本循環(huán)（loop）語法：Loop…Endloop實例：DeclareXnumber;BeginX:=0;LoopX:=x+1;Ifx>=3thenExit;Endif;Dbms_output.put_line(‘內(nèi)：x=’||X);Endloop;Dbms_output.put_line(‘外：x=’||X);End;/while循環(huán)語法：whileexpressionloop…Endloop;實例：DeclareXnumber;BeginX:=0;Whilex<=3loopX:=X+1;Dbms_output.put_line(‘內(nèi)：x=’||X);Endloop;Dbms_output.put_line(‘外：x=’||X);End;/for循環(huán)語法：forcounterin[reverse]start_value起始…end_valueLoop結(jié)束…Endloop;實例：BeginForiIN1..5loop希望由大到小，在IN后面加上REVERSEDBMS_output.put_line(‘i=’||i);Endloop;DBMS_OUTPUT.PUT_LINE(‘endofforloop’);End;/goto語句實現(xiàn)循環(huán)：實例：DeclareXnumber;BeginX:=0;<<repeat_loop>>設(shè)置標記X:=x+1;Dbms_output.put_line(x);Ifx<3thenGotorepeat_loop;如果x小于3就goto到之前的標記位置Endif;End;/異常處理：異常分類系統(tǒng)異常DUP_VAL_ON_INDEX向有唯一約束的表中插入重復(fù)行NO_DATE_FOUND在一個selectinto語句中無返回值TOO_MANY_ROWSselectinto語句返回了多行VALUE_ERROR一個算法，轉(zhuǎn)換截斷或大小約束發(fā)生錯誤ZERO_DIVIDE發(fā)生唄零除自定義異常異常結(jié)構(gòu)ExceptionWhen…. Then….復(fù)合變量：記錄記錄的聲明：Typetype_nameisrecord(Variable_namedatatype[,Variable_namedatatype[,…..);Real_nametype_name;實例：DeclareTypemyrecordISrecord(Idvarchar2(10);idemp.eid%type;表示id變量的長度與emp表的id長度相同，也可以整張表的字段長度與某某表相同Namevarchar2(10);Real_recordmyrecord;BeginSelectemp_id,emp_nameintoreal_recordfromempwhereemo_id=’001’;Dbms_output.put_line(real_record.id||’,’||real_);End;/第五章PL/SQL高級應(yīng)用一、游標(執(zhí)行效率不高，消耗資源嚴重)1.1游標的概念：游標一種PL/SQL控制結(jié)構(gòu)，可以對sql語句的處理進行顯示控制，便于對表的行數(shù)據(jù)逐條進行處理。1.2游標的分類顯示，隱式1.3游標的屬性%FOUND,boolean型的判斷有數(shù)據(jù)可取%ISOPEN,查看游標是否打開，如：ifcur%isopenthen%NOTFOUND,boolean型的判斷沒有數(shù)據(jù)可取,和found相反%ROWCOUNT用來返回迄今為止已經(jīng)從游標中取出的數(shù)據(jù)數(shù)目顯示游標實例：DeclareCursormycuris創(chuàng)建游標Select*frombooks;Myrecordbooks%rowtype;聲明變量，與books表的類型相同BeginOpenmycur;Fetchmycurintomyrecord;把游標mycur里的數(shù)據(jù)放到變量myrecord里面，首先取的是第一條Whilemycur%foundloop循環(huán)去游標里面的數(shù)據(jù)Dbms_output.put_line(myrecord.books_id||’,’||myrecord.books_name);Fetchmycurintomyrecord;Endloop;Closemycur;End;/Oracle中游標可以帶參數(shù)實例：DeclareCursorcur_para(idvarchar2)is常見帶參數(shù)的游標Selectbooks_namefrombookswherebooks_id=id;t_namebooks.books_name%type;--聲明變量BeginOpencur_para(‘001’);傳入?yún)?shù)Loop--進入循環(huán)Fetchcur_paraintot_name;--把游標的內(nèi)容放入到變量里面Exitwhencur_para%notfound;--游標中沒有數(shù)據(jù)可取的時候退出Dbms_output.put_line(t_name);Endloop;Closecur_para;End;/以for循環(huán)的形式：不需要open和closeDeclareCursorcur_para(idvarchar2)isSelectbooks_namefrombookswherebooks_id=id;BeginDbms_output.put_line(‘結(jié)果集為’);Forcurincur_para(‘0001’)loop;Dbms_output.put_line(cur.books_name);Endloop；End;/ROWCOUNT的使用方法：Declaret_namevarchar2(10);cursormycurisselectnamefromdeptment;beginopenmycur;loopfetchmycurintot_name;exitwhenmycur%notfoundormycur%notfoundisnull;dbms_output.put_line(‘游標mycur的rowcount是：’||mycur%rowcount);endloop;closemycur;end;/利用游標修改數(shù)據(jù):DeclareCursorcuris--=-創(chuàng)建游標Selectnamefromdeptmentforupdate;加上forupdate選項才能利用游標修改數(shù)據(jù)textvarchar2(10);定義變量beginopencur;fetchcurintotext;--把游標中的數(shù)據(jù)放入變量里面whilecur%foundloop當有數(shù)據(jù)可取的時候進入while循環(huán)updatedeptmentsetname=name||’_t’wherecurrentofcur;currentofcur判斷游標的當前行fetchcurintotext;endloop;closecur;end;/隱式游標的實例：BeginForcurin(selectnamefromdeptment)loopDbms_output.put_line();Endloop;End;/4.1.1無參過程createorreplaceprocedureproc_test1isbegin dbms_output.put_line(systimestamp);end4.1.2帶輸入?yún)?shù)createorreplaceprocedureproc_test2(p_tempvarchar2)isbegin insertintoscott.test1values(p_temp);end4.1.3帶輸出參數(shù)createorreplaceprocedureproc_test3(p_tempvarchar2,p_countoutnumber)isbegin insertintoscott.test1values(p_temp); selectcount(a)intop_countfromscott.test1;endvarp_countnumberexecproc_test3('c',:p_count)printp_count4.1.4參數(shù)的幾種傳遞方式--位置傳遞--名稱傳遞--組合傳遞4.1.4查看過程的源代碼selecttextfromuser_sourcewherename='PROC_TEST3';【注意】過程的名字一定要大寫4.2過程實例4.2.1過程說明過程實現(xiàn)的功能是：接收一個關(guān)鍵字，根據(jù)關(guān)鍵字查詢各個品牌中，包含該關(guān)鍵字的包包數(shù)量在Oracle過程中查詢出來的東西都要放入變量(各種類型，如果是結(jié)果集則使用游標)游標類型要臨時定義，且必須在過程之前，這就需要將類型定義和過程放在同一個包中4.2.2建包--首先，創(chuàng)建包規(guī)范createorreplacepackagemypkgistypemycursorisrefcursor;procedureproc_countBrandbagByKeyword(p_tempvarchar2,cur_brandbagcountinfooutmycursor);end;4.2.3建過程--其次，創(chuàng)建包體（主要是過程）createorreplacepackagebodymypkgisprocedureproc_countBrandbagByKeyword(p_tempvarchar2,cur_brandbagcountinfooutmycursor)isbeginopencur_brandbagcountinfoforselecta.*,(selectcount(bagid)fromscott.T_Bagwherebagbrandid=a.brandidandbagnamelike'%'||p_temp||'%')bagcountfromscott.T_BagBrandaorderbybagcountdesc;endproc_countBrandbagByKeyword;endmypkg;4.2.4在Oracle中調(diào)用過程--在匿名塊中調(diào)用setserveroutputondeclaretypemycurtypeisrefcursor;cur_countinfomycurtype;v1number(18);v2varchar2(50);v3varchar2(20);v4number(5);beginc_countBrandbagByKeyword('11',cur_countinfo);loop fetchcur_countinfointov1,v2,v3,v4; exitwhencur_countinfo%notfound; dbms_output.put_line('品牌名稱:'||v2||'包包數(shù)量:'||v4);endloop;end;4.2.5用JAVA調(diào)用oracle過程一無輸出參數(shù)的過程表：TESTTB,里面兩個字段（I_ID，I_NAME）。過程：CREATEORREPLACEPROCEDURETESTA(PARA1INVARCHAR2,PARA2INVARCHAR2)ASBEGININSERTINTOHYQ.B_ID(I_ID,I_NAME)VALUES(PARA1,PARA2);ENDTESTA;java代碼：importjava.sql.*;publicclassTestProcedureOne{publicstaticvoidmain(String[]args){Stringdriver="oracle.jdbc.driver.OracleDriver";StringstrUrl="jdbc:oracle:thin:@:1521:hyq";hyq是指哪個數(shù)據(jù)庫Connectionconn=null;CallableStatementproc=null;try{Class.forName(driver);conn=DriverManager.getConnection(strUrl,"hyq","hyq");proc=conn.prepareCall("{callHYQ.TESTA(?,?)}");proc.setString(1,"100");proc.setString(2,"TestOne");proc.execute();}catch(SQLExceptionex2){ex2.printStackTrace();}catch(Exceptionex2){ex2.printStackTrace();}finally{//......}}}二有輸出參數(shù)的過程（非列表）存儲過程為：CREATEORREPLACEPROCEDURETESTB(PARA1INVARCHAR2,PARA2OUTVARCHAR2)ASBEGINSELECTINTOPARA2FROMTESTTBWHEREI_ID=PARA1;ENDTESTB;在java里調(diào)用時就用下面的代碼：packagecom.hyq.src;publicclassTestProcedureTWO{publicTestProcedureTWO(){}publicstaticvoidmain(String[]args){Stringdriver="oracle.jdbc.driver.OracleDriver";StringstrUrl="jdbc:oracle:thin:@:1521:hyq";Statementstmt=null;ResultSetrs=null;Connectionconn=null;try{Class.forName(driver);conn=DriverManager.getConnection(strUrl,"hyq","hyq");CallableStatementproc=null;proc=conn.prepareCall("{callHYQ.TESTB(?,?)}");proc.setString(1,"100");proc.registerOutParameter(2,Types.VARCHAR);proc.execute();StringtestPrint=proc.getString(2);System.out.println("=testPrint=is="+testPrint);}catch(SQLExceptionex2){ex2.printStackTrace();}catch(Exceptionex2){ex2.printStackTrace();}finally{try{if(rs!=null){rs.close();if(stmt!=null){stmt.close();}if(conn!=null){conn.close();}}}catch(SQLExceptionex1){}}}}}注意，這里的proc.getString(2)中的數(shù)值2并非任意的，而是和存儲過程中的out列對應(yīng)的，如果out是在第一個位置，那就是proc.getString(1)，如果是第三個位置，就是proc.getString(3)，當然也可以同時有多個返回值，那就是再多加幾個out參數(shù)了。三輸出參數(shù)為游標的過程（列表）由于oracle存儲過程沒有返回值，它的所有返回值都是通過out參數(shù)來替代的，列表同樣也不例外，但由于是集合，所以不能用一般的參數(shù)，必須要用pagkage了.所以要分兩部分，1，建一個程序包。如下：CREATEORREPLACEPACKAGETESTPACKAGEASTYPETest_CURSORISREFCURSOR;endTESTPACKAGE;2，建立存儲過程，存儲過程為：CREATEORREPLACEPROCEDURETESTC(p_CURSORoutTESTPACKAGE.Test_CURSOR)ISBEGINOPENp_CURSORFORSELECT*FROMHYQ.TESTTB;ENDTESTC;可以看到，它是把游標（可以理解為一個指針），作為一個out參數(shù)來返回值的。在java里調(diào)用時就用下面的代碼：packagecom.hyq.src;importjava.sql.*;importjava.io.OutputStream;importjava.io.Writer;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importoracle.jdbc.driver.*;publicclassTestProcedureTHREE{publicTestProcedureTHREE(){}publicstaticvoidmain(String[]args){Stringdriver="oracle.jdbc.driver.OracleDriver";StringstrUrl="jdbc:oracle:thin:@:1521:hyq";Statementstmt=null;ResultSetrs=null;Connectionconn=null;try{Class.forName(driver);conn=DriverManager.getConnection(strUrl,"hyq","hyq");CallableStatementproc=null;proc=conn.prepareCall("{callhyq.testc(?)}");proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);proc.execute();rs=(ResultSet)proc.getObject(1);while(rs.next()){System.out.println("<tr><td>"+rs.getString(1)+"</td><td>"+rs.getString(2)+"</td></tr>");}}catch(SQLExceptionex2){ex2.printStackTrace();}catch(Exceptionex2){ex2.printStackTrace();}finally{try{if(rs!=null){rs.close();if(stmt!=null){stmt.close();}if(conn!=null){conn.close();}}}catch(SQLExceptionex1){}}}}在這里要注意，在執(zhí)行前一定要先把oracle的驅(qū)動包放到class路徑里，否則會報錯的。四：函數(shù)基本語法：Createorreplacefunctionfunction_name(argument1mode1datatype1,參數(shù)argument2mode2datatype2,參數(shù)…….)參數(shù)Returndatatype返回值類型Is|asPl/sqlblock;語句塊4.1創(chuàng)建無參函數(shù)：CreateorreplacefunctioncountBagReturnnumberIsNnumber;BeginSelectcount(*)intonfromT_Bag;Returnn;End;4.2調(diào)用函數(shù)：方法一（sql語句中直接調(diào)用）：SelectcountBagfromdual;無參調(diào)用SelectcountBagByBrand(3)fromdual;有參調(diào)用方法二（使用變量接受函數(shù)返回值）：Varn1numberExec:n2:=countBagPrintn1Varn1numberExec:n2:=countBagByBrand(3)Printn1方法三（使用包DBMS_OUTPUT調(diào)用函數(shù)）：SetserveroutputonExecdbms_output.put_line(‘使用dbms_output函數(shù)調(diào)用’||countBag);SetserveroutputonExecdbms_output.put_line(‘使用dbms_output函數(shù)調(diào)用’||countBagByBrand(3));4.3創(chuàng)建帶參函數(shù)CreateorreplacecountBagByBrand(brandidnumber)ReturnnumberIsN2numberBeginSelectcount(*)inton2fromT_Bagwherebagbrandid=brandid;Returnn2;End;4.4java中調(diào)用：第六章視圖、同義詞、序列一：視圖1.視圖的概念：視圖實際上是一條查詢語句，是數(shù)據(jù)的顯示方式，降低語句的復(fù)雜性，使語句可讀性更強，不占用實際空間。2.視圖的作用：安全性（利用視圖限制用戶訪問表中行列的權(quán)利，限制用戶向表中插入數(shù)據(jù)的權(quán)利）方便性（利用視圖封裝更為復(fù)雜的查詢語句，生成報表更為簡單）一致性（提高效率的作用）3.創(chuàng)建視圖的語法：實例：CreateorreplaceviewmyviewAsSelect*frombooks;/創(chuàng)建帶條件的視圖：CreateorreplaceviewmyviewAsSelect*frombookswhereprice>30Withcheckoption;--表示檢查視圖的選項，也就是where條件，如果大于30的話就插入數(shù)據(jù)，小于就不行/往視圖中插入數(shù)據(jù)：實例：Insertintomyviewvalues(‘0007’,’sdfdf’,23,5,’df’);如果組成視圖的表是兩個或者兩個以上的話，不能更新視圖，需要更新的話要滿足一定的條件。（解決辦法要等到替代觸發(fā)器）,如果視圖中包含聚合函數(shù)也是不可以更新的創(chuàng)建只讀視圖：Createorreplaceview_read之前存在就刪除AsSelect*fromempWithreadonly;視圖為只讀二：同義詞（公共同義詞，私有的專用的同義詞）概念：語法：Createsynonymdeptforscott.dept;默認的是私有的專有的同義詞，只能當前用戶使用Createpublicdeptsynonymdeptforscott.dept;創(chuàng)建公共的同義詞，都可以使用Dropsynonymdept;刪除同義詞三：序列(相當于SQLSERVER中的自增長)語法：CreatesequencemyscqStartwith1希望從幾開始Incrementby1遞增幾個Order排序Nocycle;不循環(huán)，避免相同，但會影響性能Selectmyscq.nextvalfromdual;序列的下一個值Selectmyscq.curruvalfromdual;當前的值把序列應(yīng)用到某個表里面：Createtableauto(anumber,bvarchar2(10));Insertintoautovalues(myscq.nextval,’adf’);對auto表中的a列加入序列，實現(xiàn)自增修改當前序列的遞增量AlertsequencemyscqIncrementby3;序列只能修改遞增量，不能修改當前值查詢當前用戶的序列：Selectsequence_namefromuser_sequence;查詢某個用戶的序列：selectSEQUENCE_OWNER,SEQUENCE_NAMEfromdba_sequenceswheresequence_owner='SCOTT';創(chuàng)建用戶語法：CREATEUSERusernameIDENTIFIEDBY密碼;修改密碼：alteruserscottidentifiedbyscott11;修改用戶帳號為鎖定：Alteruserscottaccountlock;解鎖unlockSysdba給test授予對scott.dept的查詢的功能，同時test也可以把對scott.dept的查詢功能授權(quán)給別的用戶：grantselectonscott.depttotestwithgrantoption;第七章觸發(fā)器1、觸發(fā)器的概述、分類需要觸發(fā)事件，觸發(fā)對象，觸發(fā)條件，執(zhí)行觸發(fā)的語句體2、事務(wù)2.1什么是事務(wù)？事務(wù)是數(shù)據(jù)庫中重要的機制，用于確保數(shù)據(jù)完整性和并發(fā)處理的能力，它將一條/一組SQL語句當作成一個邏輯上的單元，用于保障這些語句都成功/失敗。2.1事務(wù)的特性？事務(wù)具有ACID四大特性：A(Atomicity)原子性：是一個邏輯的單元，不可分割的，只有成功和失敗兩種可能，如果一方失敗就都失敗。（手段）C（Consistency）一致性：操作的前后讓數(shù)據(jù)保持一致。（目的，也就是以原子性的手段達到一致性的目的）I（Isolation）隔離性：以加鎖的方式保證數(shù)據(jù)的完整。（隔離性越強并發(fā)性就越弱）D（Durability）永久性：對同一個事務(wù)而言，一旦提交就不能回滾，一旦回滾就不能提交。行級觸發(fā)器工作原理和設(shè)計應(yīng)用實例：Createorreplacetriggerdel_deptid創(chuàng)建一個觸發(fā)器Afterdeleteondeptment刪除動作，對deptment做觸發(fā)器Foreachrow循環(huán)，對每一行做觸發(fā)BeginDeletefromempwhereid=:old.id;Enddel_deptid;/觸發(fā)器創(chuàng)建完成后，再刪除Deletefromdeptmentwhereid=’01’;這樣兩張表都刪除了數(shù)據(jù)增加：Createorreplacetriggerinsert_deptid創(chuàng)建一個觸發(fā)器Afterinsertondeptment增加動作，對deptment做觸發(fā)器Foreachrow循環(huán)，對每一行做觸發(fā)BeginInsertintoemp(eid,ename,id)values(‘121’,’dffd’,:new.id);End;/創(chuàng)建完后，增加：Insertintodeptmentvalues(‘77’,’dfd’);這樣兩張表都插入了數(shù)據(jù)修改：Createorreplacetriggerupdate_deptid創(chuàng)建一個觸發(fā)器Afterupdateondeptment增加動作，對deptment做觸發(fā)器Foreachrow循環(huán)，對每一行做觸發(fā)BeginUpdateempsetid=:new.idwhereid=:old.id;End;/創(chuàng)建完后，修改：Updatedeptmentsetid=’yy’whereid=’01’;這樣兩張表都插入了數(shù)據(jù)判斷刪除：Createorreplacetriggerbooks_deleteAfterdeleteonbooksForeachrowBeginIf:old.books_id=’0001’then設(shè)置0001不能唄刪除Raise_applichtion_error(-20000,’不允許刪除！’);只能寫-20000~-20999別的不可以Endif;End;/創(chuàng)建完后，刪除：Deletefrombookswherebooks_id=’0001’;不能唄刪除，別的編號可以語句級觸發(fā)器的設(shè)計應(yīng)用實例：先創(chuàng)建表：(創(chuàng)建一個特殊表，記錄哪個用戶在哪個時間做了哪個動作)Createtablemylog(curr_uservarchar2(100),curr_datedate,actchar(1));再創(chuàng)建觸發(fā)器：Createorreplacetriggerdml_aaAfterinsertordeleteorupdateonaa在aa表上做的記錄，后觸發(fā)BeginIfinsertingthenInsertintomylogvalues(user,sysdate,’I’);ElsifdeleteingthenInsertintomylogvalues(user,sysdate,’D’);ElseInsertintomylogvalues(user,sysdate,’U’);Endif;End;/顯示轉(zhuǎn)換（時間轉(zhuǎn)換成字符串）：Selectcurr_user,to_char(curr_date,’yyyy-mm-ddhh24:mi:ss’)dfrommylog;結(jié)合觸發(fā)器利用序列實現(xiàn)自增：Createorreplacetriggerset_noBeforeinsertonauto對auto做觸發(fā)器ForeachrowDeclareSnnumber(5);定義變量BeginSelectmyseq.nextvalintosnfromdual;myseq.nextval是一個序列，把它放到變量里面:new.a:=sn;End;/創(chuàng)建完后，增加數(shù)據(jù)：Insertintoautovalues(32,’dfg’);32是無效的，會被序列中的數(shù)據(jù)替代替換觸發(fā)器應(yīng)用使用替換觸發(fā)器解決視圖中多張表的數(shù)據(jù)更新實例：Createorreplacetriggertr_v_e_dInsteadofinsertonv_emp_dept針對v_emp_dept視圖的增加觸發(fā)器ForeachrowBeginInsertintodeptmentvalues(:new.id,:);分別操作兩個基表，首先是deptment,使用new這個表Insertintoemp(eid,ename,sex,id)values(:new.eid,:new.ename,:new.sex,:new.id);再針對emp表，由于兩個表里面都要deptment編號，作為連接條件，為了公共匹配，向emp中插入數(shù)據(jù)時也要不過來End;/創(chuàng)建完后，插入數(shù)據(jù)：Insertintov_emp_deptvalues(‘456’,’test’,’r’,’33’,’gh’第八章安全管理8.1用戶管理8.1.1創(chuàng)建CREATEUSER"test"PROFILE"DEFALUT"IDENTIFIEDBY"test12345"DEFAULTTABLESPACEttACCOUNTUNLOCK;8.1.2授權(quán)前提：登錄到具備權(quán)限的賬戶下才能做以下授權(quán)GRANTconnectTOtest;?對象授權(quán)GRANTSELECTONscott.deptTOtest[WITHGRANTOPTION];[]為可選部分，如果省略，則test只能查詢scott.dept，如果不省略，則test還可以將對scott.dept的查詢授權(quán)給其他賬戶。一般來講，這個選項不建議使用。如果是表，SELECT部分還可以使用INSERTDELETEUPDATEALL如果是過程，SELECT部分要改成EXECUTE?系統(tǒng)授權(quán)GRANTCREATEUSERTOtest[WITHADMINOPTION];GRANTDROPUSERTOtest;?解除授權(quán)REVOKESELECTONscott.deptFROMtestREVOKECREATEUSERFROMtest8.1.3管理前提：登錄到具備權(quán)限的賬戶下才能做管理更改默認表空間：ALTERUSERtestDEFAULTTABLESPACEtt;更改密碼：ALTERUSERtestIDENTIFIEDBYtest54321;鎖定/解鎖：ALTERUSERtestACCOUNTLOCK/UNLOCK；8.2角色管理8.2.1創(chuàng)建CREATEROLEmyrole;8.2.2授權(quán)GRANTSELECTONscott.deptTOmyrole[WITHGRANTOPTION];8.2.3將角色分配給賬戶GRANTmyroleTOtest;8.3概要文件概要文件主要是賬戶登錄時的一些配置信息，比如：口令的有效期CPU會話并行會話數(shù)……主要是影響性能和安全的一些配置。下午作業(yè)：創(chuàng)建一個賬戶，只授權(quán)了connect，沒有授權(quán)dba看是否可以assysdba登錄？可以登錄某個用戶有insert和select權(quán)限，創(chuàng)建一個角色有select和insert功能，把這個角色分配給用戶，查看把角色移除了，這個用戶是否還有insert和select權(quán)限？還存在insert和select權(quán)限，因為即使刪除了角色，但用戶當前的insert和select功能還存在兩個用戶分別建同名的表放在同一個表空間，是否可以？可以，和表空間沒有關(guān)系，不會有阻礙一個用戶能不能在兩個不同的表空間建兩個表A？不可以用戶的默認表空間是tabsA的時候，建了10個表，這10個表在tabsA對應(yīng)的數(shù)據(jù)文件上；用戶的默認表空間是tabsB的時候，建了5個表，這5個表在tabsB對應(yīng)的數(shù)據(jù)文件上，假設(shè)我們將tabsA對應(yīng)的數(shù)據(jù)文件導(dǎo)到另一臺電腦的toracle，能看到幾張表？用戶的默認表空間是tabsA的時候，建了1個表并插入10條數(shù)據(jù)，；更改用戶的默認表空間是tabsB的時候，還在這個表里插入5條數(shù)據(jù)，假設(shè)我們將tabsB對應(yīng)的數(shù)據(jù)文件導(dǎo)到另一臺電腦的toracle，能看到這張表嗎？如果能看到，有幾條數(shù)據(jù)呢？自增長：自增長怎么做、創(chuàng)建一個序列：CreatesequencemyscqStartwith1希望從幾開始Incrementby1遞增幾個Order排序Nocycle;不循環(huán)，避免相同，但會影響性能Selectmyscq.nextvalfromdual;序列的下一個值Selectmyscq.curruvalfromdual;當前的值創(chuàng)建表：Createtableauto(anumber,bvarchar2(10));把表中的a列加入序列：Insertintoautovalues(myscq.nextval,’a’);對auto表中的a列加入序列，實現(xiàn)自增約束：只能是指定的一些值？Check約束：Altertableempaddconstraintck_emp_sexCHECK(sex=’男’orsex=’女’);只能是某個范圍的值？Check約束：Altertableempaddconstraintck_emp_ageCHECK(age>18orsex<150);只能是某個樣子的值（Email）？默認值？外鍵引用？Altertablemmaddconstraintfk_mnforeignkey(n1)referncesnn(n1);nn表中的n1和mm表中的n1是主外鍵索引：什么樣的表上需要索引？數(shù)據(jù)量很大的時候，需要索引來提高查詢速度什么樣的列上面需要建索引？列的唯一值很少的時候，需要建索引，如性別在什么情況下該建什么樣的索引？一般情況下：Createindexmy_mm_idxonmm(m1);唯一值很少的情況下，如性別）Createbitmapindexbit_emponemp(sex);需要唯一索引的情況下：Createuniqueindexmyidxonmm(m2);將一個表空間分配給某個用戶后，如果不授權(quán)，那么這個用戶能在該表空間里干什么呢？不能在此表空間里面建表第九章表空間的管理1、創(chuàng)建表空間（表空間就相當于SQLServer的數(shù)據(jù)庫）語法：Createtablespacetabsdatafile‘c:\oracle\product\10.1.1\oradata\test\tabs.dbf’size10m;某個用戶的默認表空間：Alteruserscottdefaulttablespacetabs;授予操作表空間權(quán)限:grantunlimitedtablespace,dbatoscott;把dba的權(quán)限給scott創(chuàng)建表放置到指定表空間：（不指定的話就放到默認表空間）Createtablety(tchar(10),yvarchar2(10))tablespacetabs;添加dbf文件到test用戶的表空間Altertablespacetestadddatafile‘d:\iracke\test1.dbf’size10m;收回對表空間的操作權(quán)限：Revokeunlimitedtablespacefromscott;如果要限制某個用戶對該表空間的使用，在此可以設(shè)置為0Alterusertestquota10M/0ontt;

resource中包含有unlimitedtablespace的權(quán)限即對任何一個表空間的使用權(quán)限grantconnect,sourcetotest;導(dǎo)出表空間數(shù)據(jù)文件：Expscott/wayactionfile=’f:oracle\tabs.dmp’tablespaces=(tabs);查詢表所在的表空間：Selectt.tablespace_name,t.table_namefromall_tablestwheret.table_name=’TBLMATERIAL’;凡是在oracle中查找的東西name后面的名稱必須是大寫查詢某個用戶的默認表空間：select

default_tablespacefromdba_userswhereusername='SCOTT';（引號內(nèi)區(qū)分大小寫）查看當前oracle的默認實例名:SELECThost_name,instance_name,versionFROMv$instance;修改當前oracle的實例名要去修改注冊表運行下面的regedit創(chuàng)建臨時表空間：Createtemporarytablespacetemp2;查詢當前的字符集：Select*fromnls_database_parameterswhereparameter=’nls_characterset’;清屏：hocls塊的概念：匿名塊和命名塊（需要再深入了解），命名分為子程序（過程，函數(shù)，包，觸發(fā)器）第十章表的管理1、表的完整性與約束完整性：實體完整性（主鍵唯一值），域完整性（類型長度），參照完整性（外鍵約束）添加主鍵：語法：Altertablennaddconstraintpk_nnprimarykey(n1);主鍵nn施加到n1列上添加外鍵約束：語法：Altertablemmaddconstraintfk_mnforeignkey(n1)referencesnn(n1);nn表中的n1和mm表中的n1是主外鍵注意：主外鍵引用的話，主鍵列必須是唯一或主鍵列Check約束：語法：Altertableempaddconstraintck_emp_sexCHECK(sex=’男’orsex=’女’);emp表中的sex列只能為男或女查看一個表的約束：descdba_constrains(由管理員),descall_constrains,descuser_constrains(當前用戶模式下的)Selectconstraint_name,constraint_typefromuser_constraintwheretable_name=’EMP’;當前用戶下emp表的所有約束Boolean值：isCheckEmailchar(1)default0notnull;oracle中沒有boolean類型，所以可以用char(1)和number(1)來表示，但是相對來說char(1)比number(1)要好，但是char(1)不能為空插入日期（到毫秒）：注意(為了儲存毫秒，所以建表的時候，registertime要用timestamp)如registertiemtimestampdefaultcurrent_timestampnotnull;--因為sysdate沒有毫秒，所以取值要用current_timestampInsertintoscott.t_user(to_timestamp(’2011-12-1110:34:32.3434’,‘yyyy-mm-ddhh24:mi:ss.ff3’));分：Insertintoscott.t_user(to_timestamp(’2011-12-1110:34:32.3434’,‘yyyy-mm-ddhh24:mi:ss’));查詢的時候要轉(zhuǎn)換：selectto_char(registertime,’yyyy-mm-ddhh24:mi:ss:ff3’)fromscott.t_user;有主外鍵的情況下，并不知道約束名稱，刪除表：droptabletablenamecascadeconstraints查詢當前用戶下所有的表：selecttable_namefromuser_tables;修改列的類型：Altertablescott.bagmodify(bagNamevarchar2(20));把bag表中id列原本是10的改為202、管理索引（當表的數(shù)據(jù)量很大的時候，為了提高查詢速度，所以要建索引）索引的三個級別：根級索引，中間級索引，頁級索引創(chuàng)建索引：語法：Createindexmy_mm_idxonmm(m1);Oracle中特殊的索引：語法：（解決唯一值很少的索引，如性別）Createbitmapindexbit_emponemp(sex);單獨創(chuàng)建唯一索引：語法：Createuniqueindexmyidxonmm(m2);分頁中Rownum的使用(偽列)：select*from(selecta.*,rownumrfrom(select*frombag)a);在使用Rownum的時候，需要三層嵌套，最里面和最外面的查詢都不涉和rownum，Rownum針對<符號沒有問題，但>和=就不行，例如：select*,rownumrfrombagwhererownum>10;這句查詢語句首先查詢出rownum大于10的數(shù)據(jù)，但是rownum會對大于10的數(shù)據(jù)重新從一排序，所以就無法找到大于10的數(shù)據(jù)，=也是一樣，=1可以查詢出來，后面的就不行；rownum篩選后都會重新排序，所以需要三層嵌套Select*from(selecta.*,rownumrfrom(select*frombag)awhererownum<=5)bwherer>=1;3、過程創(chuàng)建無參過程語法：Create