Oracle plsql程序设计基础

Oracle9iPL/SQLISBN7-302-08002-xBen2004MSN:[e-mail]%type%rowtypeTYPEforloopwhileif/elsecaseerrorexceptionprocedurefunctioninout/inoutNOCOPYPACKAGECommitLOGONinsteadofOracle9iPL/SQLISBN7-302-08002-xBen2004MSN:[e-mail]1.%type%typedeclaremyiddept.id%type;mynamedept.name%type;beginselectid,nameintomyid,mynamefromdept;dbms_output.put_line(myid);dbms_output.put_line(myname);end;/2.%rowtype%rowtypedeclaretypetype_deptistableofdept%rowtypeindexbybinary_integer;tbtype_dept;begintb(1).id:='001';tb(2).id:='001';dbms_output.put_line(tb.COUNT);end;/3.TYPEdeclarelv_order_dateDAte:=sysdate;lv_last_txtvarchar2(5)default'001';lv_lastvarchar2(10)notnull:='us';TYPEtype_testisrecord(myiddept.id%type,mynamedept.name%type);rectype_test;beginlv_order_date:=sysdate;dbms_output.put_line(lv_last);selectid,nameintorecfromdept;dbms_output.put_line(rec.myid);dbms_output.put_line(rec.myname);end;Oracle9iPL/SQLISBN7-302-08002-xBen2004MSN:[e-mail]/4.declareg_idchar(10):='002';find_notchar(1):='N';cursorcurisselect*fromdept;TYPEtype_deptisrecord(curmyiddept.id%type,mynamedept.name%type,myaddrdept.addr%type);recttype_dept;beginopencur;loopfetchcurintorect;exitwhencur%NOTFOUND;currectifrect.myid=g_idthenfind_not:='Y';dbms_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;endloop;closecur;iffind_not='N'thendbms_output.put_line('norecord');endif;end;/5.forbeginforiin1..5loopdbms_output.put_line(i);endloop;end;/6.loopOracle9iPL/SQLISBN7-302-08002-xBen2004MSN:[e-mail]:=1;beginloopdbms_output.put_line(v);exitwhenv5;v:=v+1;endloop;end;/7.whiledeclarevnumber:=1;beginwhilev5loopdbms_output.put_line(v);v:=v+1;endloop;end;/8.errordeclarev1number:=90;beginifv1=10thendbms_output.put_line('v1is10');elsifv1=20thendbms_output.put_line('v2is20');elsegotoerr;dbms_output.put_line('normalend');errdbms_output.put_line('errorfound');endif;end;/Oracle9iPL/SQLISBN7-302-08002-xBen2004MSN:[e-mail]ion;beginUpdatedeptsetname='Edison'whereid='100';ifSQL%NOTFOUNDThenRaiseex;endif;ExceptionWhenexthendbms_output.put_line('updatefailed.');end;/declaretyperc_deptisrecord(myiddept.id%type,mynamedept.name%type,myaddrdept.addr%type);tbrc_dept;beginselectid,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);exceptionwhenNO_DATA_FOUNDthendbms_output.put_line('norecordisfound');whenTOO_MANY_ROWSthendbms_output.put_line('toomanyrowsareselected');whenOTHERSthendbms_output.put_line('undefineerror');dbms_output.put_line('errorcoede:'||SQLCODE);dbms_output.put_line('errormessage:'||SQLERRM);end;/Oracle9iPL/SQLISBN7-302-08002-xBen2004MSN:[e-mail]_deptisrecord(myiddept.id%type,mynamedept.name%type,myaddrdept.addr%type);tbrc_dept;beginbeginselectid,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);exceptionwhenNO_DATA_FOUNDthendbms_output.put_line('norecordisfound,occurininner.');end;exceptionwhenTOO_MANY_ROWSthendbms_output.put_line('toomanyrowsareselected,occurinouter.');whenOTHERSthendbms_output.put_line('undefineerror');dbms_output.put_line('errorcoede:'||SQLCODE);dbms_output.put_line('errormessage:'||SQLERRM);end;/10.if/elsedeclarev1number:=90;beginifv1=10thendbms_output.put_line('v1is10');elsifv1=20thendbms_output.put_line('v2is20');elsedbms_output.put_line('v2isothers');endif;end;/11.casedeclarevnumber:=10;Oracle9iPL/SQLISBN7-302-08002-xBen2004MSN:[e-mail]:vwhen10thendbms_output.put_line('vis10');when20thendbms_output.put_line('vis20');elsedbms_output.put_line('visnot10and20');endcase;end;/12.procedurecreateorreplaceproceduretest_sp(testinnumber,outtestoutnumber)isbeginINOUTiftest10thenprintsomthing('testisover10!!');elsebeginouttest:=test;printsomthing(outtest);end;endif;end;/createorreplaceprocedureprintsomthing(printinnumber)isbegindbms_output.put_line(print);end;/createorreplaceprocedureprintsomthing(printinchar)isbegindbms_output.put_line(print);end;/exectest_sp(:test,:outtest);C++Oracle9iPL/SQLISBN7-302-08002-xBen2004MSN:[e-mail]unctiontest(tinnumber)returnnumberisFunctionbeginift10thendbms_output.put_line(t);elsift10thendbms_output.put_line(t);endif;returnt;end;/procedureexectest(1);exec:tt:=test(2);Tips:returnoutprocedurereturnreturnprocedureprocedureincreateorreplacefunctiontest(tinnumber)returnnumberisbeginift10thent:=t+10;dbms_output.put_line(t);elsift10thendbms_output.put_line(t);LINE/COLERROR-------------------------------------------------------------------------5/3PLS-00363:'T'5/3PL/SQL:Statementignoredendif;returnt;end;/returncreateorreplacefunctiontest(tinnumber)returnnumberisbeginift10thenreturn1;elsift=10t