程序设计基础

Oracle常用命令type用%rowtypeTYPE游标的使for循loopwhileifelse的用case的用error的设exception过程及函procedure的建立和调function参数的调用（in模式为按址调用，outinout模式为按值调用。NOCOPY强行转换成按址调用包及封 包（PACKAGE）的建立和调 包的全局结封装函数的纯源代码的查建立用户及登授予权限和权限收依直接依查看依包之间调触发建立简单的触发触发器分稍复杂的触发条件谓触发器中不可使用系统触发器举例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结构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);外部执行的时候注意参数调用方式要加过程可以重载，符合C++的重载规则 IN表示传入的参数，不能修改，OUT表function的建立和调用createcreateorreplacefunctiontest(tinnumber)returnFunction的建立，需要返回值但不需要说明大小ift>10elsift<10thenendif;return/注意：调用的方法，不能 那样独立进行调用。函数是表达式的一部分（有返回值exectest(1);错exectt:=test(2);正Tips:建议使用return模式，而不是使用outprocedure中也能用return，这里的return只表示当前procedurecreateorreplacefunctiontest(tinnumber)createorreplacefunctiontest(tinnumber)returnift>10LINE/COLelsift<10PLS-00363:表达式'T'不能用作赋值endreturn/createorcreateorreplacefunctiontest(tinnumber)returnnumberift<10return1;endif;/function中调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这个过程的第二个参数是nocopy就是传址的，所以修改了lv_test_num，为5如果test_nocopy_sp这个过程的第二个参数不是nocopy，那么就是传值，lv_test_num不被修改，仍然为1。whenothers/errorhappenederrorhappened包（package）的建立（包含了函数的重载 过程、函数的原型createorreplace 过程、函数的原型procedure(testinnumber,outtestoutnumber);procedureprintsomthing(printinnumber);procedureprintsomthing(printinchar);functiontest

函数printsomething/

(tinnumber)returncreateorreplacepackagebodytest_package无procedure(testinnumber,outtestoutiftest>10printsomthingoverprintsomthingendendendprocedure(printinendprintsomthing;procedure(print(printinendprintsomthing;functiontest(tinnumber)returnnumberift>10elsift<10thenendif;endtest;/具体写体的时候和以前一样写，只不过不用写createorreplace，在endSQL>vartest1SQL>exec调用方法就 面加个包名，其余over意点和过程或函数相同PL/SQL过程已成功完成SQL>exec1PL/SQL过程已成功完成SQL>execPL/SQL过程已成功完成SQL>print createcreateorreplacepackageglobal_vproceduresetValue(p1in包的全局变/createorreplacepackagebodyproceduresetValue(p1innumber)修改全局变量并输end/2exectest_global.setValue(20);exec 个会话分 自己的全局变量。互不影响createcreateorreplacepackageglobal_vfunctionsetValue(p1innumber)returnnumber;pragmarestrict_references(setValue,WNPS);/createorreplacepackagebodyfunctionsetValue(p1innumber)returnnumberWNPS的约束PACKAGEBODYTEST_GLOBAL出现错误LINE/COLPLS-00452:子程序 了它相关注指定纯度WNDSWritesNoDatabaseRNDSReadsNoDatabase函数 任何WNPSWritesNoPackageRNPSReadsNoPackage函数 任何封装变returnglobal_v;returnglobal_v;end/SQL>SQL>desc名是否为空?类 存放源代码的字SQL>selecttextfromuser_sourcewhere注意：name一定要大写functiontest(tinnumber)returnnumberift>10elsift<10thenendif;已选择10行 显示行SQL>selectrownum,textfromuser_sourcewhereSQL>createSQL>createuserMascotZhuangIDENTIFIEDBY用户已创用户SQLSQLgrantcreatesessiontoMascotZhuang赋予能够连上数据库的权力ORA-01045:userMASCOTZHUANGlacksCREATESESSIONprivilege;logonC:\>sqlplus连接到注意如果以MascotZhuang/MascotZhuangasOracle9iEnterpriseEditionRelease.1-登陆，相当于assysdba登陆WiththePartitioning通过showuser可以看到usersys，而不是授予授予全部权SQL>grantallontest_packageto成功授予特定权SQL>grantexecuteontest_packagetoSQL>revokeallontest_packagefrom创建的用户使用SQL>vartest1number;SQL>exec:test1:=sys.test_package.test(20);PL/SQL过程已成功完createorreplaceproceduretest_dependency(p_printchar)createorreplaceproceduretest_dependency(p_printchar)/当创建这个procedure PLS-00201:必须说 PL/SQL:Statement因为这个时候，printsomething这个procedure还没有创建。换句话说，test_dependency依赖于printsomething。所以在printsomething创建之前，test_dependency是无效的。因此，必须创建printsomethingcreateorreplaceprocedureprintsomething(p_printchar)/这时候，test_denpendency的status还是invalid。需要进行重现编译，才能使得status为valid。SQL>alterproceduretest_dependencycompile;SQL>selectstatusfromuser_objectswhereobject_name='TEST_DEPENDENCY';SQL>SQL>selectreferenced_name,referenced_typefromuser_dependencieswhere TEST_DEPENDENCY所依赖的依赖系统的SQL>selectreferenced_name,referenced_typefromuser_dependencieswherename='PRINTSOMETHING'; 依赖系统的createcreateorreplacepackageglobal_vproceduresetValue(p1in都有一个包的全局变procedureprint(p1in/createorreplacepackagebodyproceduresetValue(p1innumber)dbms_output.put_line('thisistest_global,global_vis'||end调用test1_global这个包中的setValue这procedureprint(p1indbms_output.put_line('test_global,global_vis'||global_v);endprint;/createorreplacepackageglobal_vproceduresetValue(p1in都有一个包的全局变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建立一建立一个简单的表createtabletable_test_trigger(id建立简单的触发器触发器触发的时机，有记录table_test_trigger后就触发createorreplacetriggerafterinsertondbms_output.put_line('somethingisinserted!!');/SQL>insertintotable_test_triggersomethingis已创建1行分为：行级触发器和语句级触发分为：行级触发器和语句级触发行级触发器：对于DML语句影响的每一行都触发触发器代码。只适合于UPDATE和DELETE事件。语句级触发器：对该事件触发一次触发器。INSERT事件第一第一个触发器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建立触发器中多了ofname，dbms_output.put_line('somethingis/SQL>updatetable_test_triggersetname='zz'wheresomethingis 触发了触发器已更新1SQL>updatetable_test_triggersetid='99'where已更新1行没有触发触发器。因为没对name进createorreplacetriggerafterupdateofnameontable_test_triggerforeachrowForeachrow表示未更新前的name指，相对的有个NEW.namedbms_output.put_line('somthingisinserted!');/SQL>updatetable_test_triggersetsomthingiscreateorreplacetriggerafterupdateofnameontable_test_triggerforeachrow外部updatedbms_output.put_line('somthingis/SQL>updatetable_test_triggersetsomthingis这里是无条件的UPDATEsomthingissomthingisinserted!somthingisinserted!somthingiscreatecreateorreplacetriggerafterupdateofnameontable_test_triggerforeachrowifUPDATINGendif;ifDELETINGendif;ifINSERTINGendif;/触发器触SQL>updatetable_test_triggerset触发器触已更5createorreplacetriggerafterupdateofnameontable_test_triggerforeachrow当name当name字段update的时候才触发触发器ifUPDATING('name')endif;/触发触发SQL>updatetable_test_triggerset触发触发已更5未触发触发SQL>updatetable_test_triggerset未触发触发已更5Triggercreatetabletest(idcreatetabletest1(idchar(10),logdatecreateorreplacetriggermyTriggerafterinsertontest没有commit没有commitinsertintotest1values('001',sysdate);/SQL>insertintotest已创建1行SQL>select*fromtest1; 14-10月-SQL>回退已完成SQL>select*fromSQL>select*fromcreateorreplace