程序設(shè)計(jì)基礎(chǔ)

Oracle常用命令type用%rowtypeTYPE游標(biāo)的使for循loopwhileifelse的用case的用error的設(shè)exception過(guò)程及函procedure的建立和調(diào)function參數(shù)的調(diào)用（in模式為按址調(diào)用，outinout模式為按值調(diào)用。NOCOPY強(qiáng)行轉(zhuǎn)換成按址調(diào)用包及封 包（PACKAGE）的建立和調(diào) 包的全局結(jié)封裝函數(shù)的純?cè)创a的查建立用戶及登授予權(quán)限和權(quán)限收依直接依查看依包之間調(diào)觸發(fā)建立簡(jiǎn)單的觸發(fā)觸發(fā)器分稍復(fù)雜的觸發(fā)條件謂觸發(fā)器中不可使用系統(tǒng)觸發(fā)器舉例insteadoftype用法，提取typemyiddept.id%type;%type;selectid,nameintomyid,mynamefromdept;/%rowtype用法，提取%rowtypetypetype_deptistableofdept%rowtypeindexbybinary_integer;/TYPElv_order_dateDAte:=sysdate;lv_last_txtvarchar2(5)default'001';lv_lastvarchar2(10)notnull:='us';TYPEtype_ myiddept.id%type,%type);rectype_test;selectid,nameintorecfromdept;//g_idg_idchar(10):='002';find_notcursorcurselect*fromcur指向TYPEtype_deptrecord(myiddept.id%type,%type,myaddrdept.addr%type);recttype_dept;open提取cur指向的記錄rect結(jié)構(gòu)fetchcurintoifrect.myid=g_idthendbms_output.put_line('Findit!!');dbms_output.put_line('DEPTID:'||rect.myid);dbms_output.put_line('NAME:'||rect.myname);dbms_output.put_line('ADDR:'||rect.myaddr);endif;closeiffind_not='N'thendbms_output.put_line('norecord');endforforiin1..5endloop;/loopexitwhenv>5;end/whilewhilev<5end/errorifv1=10thendbms_output.put_line('v1is10');elsifv1=20thendbms_output.put_line('v2is20');elsegotoerr;dbms_output.put_line('normaldbms_output.put_line('errorfound');endif;/exceptionUpdatedeptsetname='Edison'whereid='100';ifSQL%NOTFOUNDendif;Whenexdbms_output.put_line('update/typerc_deptis(myiddept.id%type,%type,myaddrdept.addr%typeselectid,name,addrintotbfromdeptwhereid=:gb_id;dbms_output.put_line('id:'||tb.myid);dbms_output.put_line('name:'||tb.myname);dbms_output.put_line('addr:'||tb.myaddr);whenNO_DATA_FOUNDthendbms_output.put_line('norecordisfound');whenTOO_MANY_ROWSdbms_output.put_line('toomanyrowsareselected');whenOTHERSthendbms_output.put_line('undefineerror');dbms_output.put_line('errorcoede:'||SQLCODE);dbms_output.put_line('errormessage:'||SQLERRM);/typerc_deptis(myiddept.id%type,%type,myaddrdept.addr%typeselectid,name,addrintotbfromdeptwhereid=:gb_id;dbms_output.put_line('id:'||tb.myid);dbms_output.put_line('name:'||tb.myname);dbms_output.put_line('addr:'||tb.myaddr);whenNO_DATA_FOUNDdbms_output.put_line('norecordisfound,occurinwhenTOO_MANY_ROWSdbms_output.put_line('toomanyrowsareselected,occurinwhenOTHERSthendbms_output.put_line('undefineerror');dbms_output.put_line('errorcoede:'||dbms_output.put_line('errormessage:'||/ifelseifv1=10thendbms_output.put_line('v1is10');elsifv1=20thendbms_output.put_line('v2is20');elsedbms_output.put_line('v2isothers');/casevwhen10thendbms_output.put_line('viswhen20thendbms_output.put_line('vis20');elsedbms_output.put_line('visnot10and20');endcase;/procedurecreatecreateorreplaceprocedure(testinnumber,outtestoutiftest>10printsomthingoverprintsomthingend/createorreplaceprocedure(printin/createorreplaceprocedure(printin/exectest_sp(:test,:outtest);外部執(zhí)行的時(shí)候注意參數(shù)調(diào)用方式要加過(guò)程可以重載，符合C++的重載規(guī)則 IN表示傳入的參數(shù)，不能修改，OUT表function的建立和調(diào)用createcreateorreplacefunctiontest(tinnumber)returnFunction的建立，需要返回值但不需要說(shuō)明大小ift>10elsift<10thenendif;return/注意：調(diào)用的方法，不能 那樣獨(dú)立進(jìn)行調(diào)用。函數(shù)是表達(dá)式的一部分（有返回值exectest(1);錯(cuò)exectt:=test(2);正Tips:建議使用return模式，而不是使用outprocedure中也能用return，這里的return只表示當(dāng)前procedurecreateorreplacefunctiontest(tinnumber)createorreplacefunctiontest(tinnumber)returnift>10LINE/COLelsift<10PLS-00363:表達(dá)式'T'不能用作賦值endreturn/createorcreateorreplacefunctiontest(tinnumber)returnnumberift<10return1;endif;/function中調(diào)createcreateorreplacefunctiontest(tinnumber)returncreateorreplaceprocedure(printinreturn//createorreplaceproceduretest_nocopy_sp(p_ininnumber,p_outcreateorreplaceproceduretest_nocopy_sp(p_ininnumber,p_outinoutnocopyifp_in=1raiseend/createorreplaceprocedurelv_test_numnumber;test_nocopy_sp這個(gè)過(guò)程的第二個(gè)參數(shù)是nocopy就是傳址的，所以修改了lv_test_num，為5如果test_nocopy_sp這個(gè)過(guò)程的第二個(gè)參數(shù)不是nocopy，那么就是傳值，lv_test_num不被修改，仍然為1。whenothers/errorhappenederrorhappened包（package）的建立（包含了函數(shù)的重載 過(guò)程、函數(shù)的原型createorreplace 過(guò)程、函數(shù)的原型procedure(testinnumber,outtestoutnumber);procedureprintsomthing(printinnumber);procedureprintsomthing(printinchar);functiontest

函數(shù)printsomething/

(tinnumber)returncreateorreplacepackagebodytest_package無(wú)procedure(testinnumber,outtestoutiftest>10printsomthingoverprintsomthingendendendprocedure(printinendprintsomthing;procedure(print(printinendprintsomthing;functiontest(tinnumber)returnnumberift>10elsift<10thenendif;endtest;/具體寫體的時(shí)候和以前一樣寫，只不過(guò)不用寫createorreplace，在endSQL>vartest1SQL>exec調(diào)用方法就 面加個(gè)包名，其余over意點(diǎn)和過(guò)程或函數(shù)相同PL/SQL過(guò)程已成功完成SQL>exec1PL/SQL過(guò)程已成功完成SQL>execPL/SQL過(guò)程已成功完成SQL>print createcreateorreplacepackageglobal_vproceduresetValue(p1in包的全局變/createorreplacepackagebodyproceduresetValue(p1innumber)修改全局變量并輸end/2exectest_global.setValue(20);exec 個(gè)會(huì)話分 自己的全局變量?；ゲ挥绊慶reatecreateorreplacepackageglobal_vfunctionsetValue(p1innumber)returnnumber;pragmarestrict_references(setValue,WNPS);/createorreplacepackagebodyfunctionsetValue(p1innumber)returnnumberWNPS的約束PACKAGEBODYTEST_GLOBAL出現(xiàn)錯(cuò)誤LINE/COLPLS-00452:子程序 了它相關(guān)注指定純度WNDSWritesNoDatabaseRNDSReadsNoDatabase函數(shù) 任何WNPSWritesNoPackageRNPSReadsNoPackage函數(shù) 任何封裝變r(jià)eturnglobal_v;returnglobal_v;end/SQL>SQL>desc名是否為空?類 存放源代碼的字SQL>selecttextfromuser_sourcewhere注意：name一定要大寫functiontest(tinnumber)returnnumberift>10elsift<10thenendif;已選擇10行 顯示行SQL>selectrownum,textfromuser_sourcewhereSQL>createSQL>createuserMascotZhuangIDENTIFIEDBY用戶已創(chuàng)用戶SQLSQLgrantcreatesessiontoMascotZhuang賦予能夠連上數(shù)據(jù)庫(kù)的權(quán)力ORA-01045:userMASCOTZHUANGlacksCREATESESSIONprivilege;logonC:\>sqlplus連接到注意如果以MascotZhuang/MascotZhuangasOracle9iEnterpriseEditionRelease.1-登陸，相當(dāng)于assysdba登陸WiththePartitioning通過(guò)showuser可以看到usersys，而不是授予授予全部權(quán)SQL>grantallontest_packageto成功授予特定權(quán)SQL>grantexecuteontest_packagetoSQL>revokeallontest_packagefrom創(chuàng)建的用戶使用SQL>vartest1number;SQL>exec:test1:=sys.test_package.test(20);PL/SQL過(guò)程已成功完createorreplaceproceduretest_dependency(p_printchar)createorreplaceproceduretest_dependency(p_printchar)/當(dāng)創(chuàng)建這個(gè)procedure PLS-00201:必須說(shuō) PL/SQL:Statement因?yàn)檫@個(gè)時(shí)候，printsomething這個(gè)procedure還沒(méi)有創(chuàng)建。換句話說(shuō)，test_dependency依賴于printsomething。所以在printsomething創(chuàng)建之前，test_dependency是無(wú)效的。因此，必須創(chuàng)建printsomethingcreateorreplaceprocedureprintsomething(p_printchar)/這時(shí)候，test_denpendency的status還是invalid。需要進(jìn)行重現(xiàn)編譯，才能使得status為valid。SQL>alterproceduretest_dependencycompile;SQL>selectstatusfromuser_objectswhereobject_name='TEST_DEPENDENCY';SQL>SQL>selectreferenced_name,referenced_typefromuser_dependencieswhere TEST_DEPENDENCY所依賴的依賴系統(tǒng)的SQL>selectreferenced_name,referenced_typefromuser_dependencieswherename='PRINTSOMETHING'; 依賴系統(tǒng)的createcreateorreplacepackageglobal_vproceduresetValue(p1in都有一個(gè)包的全局變procedureprint(p1in/createorreplacepackagebodyproceduresetValue(p1innumber)dbms_output.put_line('thisistest_global,global_vis'||end調(diào)用test1_global這個(gè)包中的setValue這procedureprint(p1indbms_output.put_line('test_global,global_vis'||global_v);endprint;/createorreplacepackageglobal_vproceduresetValue(p1in都有一個(gè)包的全局變procedureprint(p1in/createcreateorreplacepackagebodyproceduresetValue(p1innumber)dbms_output.put_line('thisistest1_global,global_vis'||global_v);endsetValue;procedureprint(p1innumber)dbms_output.put_line('test1_global,global_vis'||global_v);endprint;/ SQL>execthisistest_global,global_visthisistest1_global,global_visPL/SQLSQL>exectest_global,global_visPL/SQLSQL>exectest1_global,global_visPL/SQL建立一建立一個(gè)簡(jiǎn)單的表createtabletable_test_trigger(id建立簡(jiǎn)單的觸發(fā)器觸發(fā)器觸發(fā)的時(shí)機(jī)，有記錄table_test_trigger后就觸發(fā)createorreplacetriggerafterinsertondbms_output.put_line('somethingisinserted!!');/SQL>insertintotable_test_triggersomethingis已創(chuàng)建1行分為：行級(jí)觸發(fā)器和語(yǔ)句級(jí)觸發(fā)分為：行級(jí)觸發(fā)器和語(yǔ)句級(jí)觸發(fā)行級(jí)觸發(fā)器：對(duì)于DML語(yǔ)句影響的每一行都觸發(fā)觸發(fā)器代碼。只適合于UPDATE和DELETE事件。語(yǔ)句級(jí)觸發(fā)器：對(duì)該事件觸發(fā)一次觸發(fā)器。INSERT事件第一第一個(gè)觸發(fā)器createtabletable_test_trigger(idchar(10),namechar(10));insertintotable_test_triggervalues('01','a');insertintotable_test_triggervalues('02','b');insertintotable_test_triggervalues('03','c');insertintotable_test_triggerinsertintotable_test_triggercreateorreplacetriggerafterupdateofnameontable_test_trigger這里比24建立觸發(fā)器中多了ofname，dbms_output.put_line('somethingis/SQL>updatetable_test_triggersetname='zz'wheresomethingis 觸發(fā)了觸發(fā)器已更新1SQL>updatetable_test_triggersetid='99'where已更新1行沒(méi)有觸發(fā)觸發(fā)器。因?yàn)闆](méi)對(duì)name進(jìn)createorreplacetriggerafterupdateofnameontable_test_triggerforeachrowForeachrow表示未更新前的name指，相對(duì)的有個(gè)NEW.namedbms_output.put_line('somthingisinserted!');/SQL>updatetable_test_triggersetsomthingiscreateorreplacetriggerafterupdateofnameontable_test_triggerforeachrow外部updatedbms_output.put_line('somthingis/SQL>updatetable_test_triggersetsomthingis這里是無(wú)條件的UPDATEsomthingissomthingisinserted!somthingisinserted!somthingiscreatecreateorreplacetriggerafterupdateofnameontable_test_triggerforeachrowifUPDATINGendif;ifDELETINGendif;ifINSERTINGendif;/觸發(fā)器觸SQL>updatetable_test_triggerset觸發(fā)器觸已更5createorreplacetriggerafterupdateofnameontable_test_triggerforeachrow當(dāng)name當(dāng)name字段update的時(shí)候才觸發(fā)觸發(fā)器ifUPDATING('name')endif;/觸發(fā)觸發(fā)SQL>updatetable_test_triggerset觸發(fā)觸發(fā)已更5未觸發(fā)觸發(fā)SQL>updatetable_test_triggerset未觸發(fā)觸發(fā)已更5Triggercreatetabletest(idcreatetabletest1(idchar(10),logdatecreateorreplacetriggermyTriggerafterinsertontest沒(méi)有commit沒(méi)有commitinsertintotest1values('001',sysdate);/SQL>insertintotest已創(chuàng)建1行SQL>select*fromtest1; 14-10月-SQL>回退已完成SQL>select*fromSQL>select*fromcreateorreplace