韓玩轉(zhuǎn)oracle講稿學(xué)習(xí)筆記plsql

oracle筆一 PLSQL概 過(guò)程化語(yǔ)言：在oracle中編寫過(guò)程、函數(shù)、觸發(fā)器等 特 學(xué)習(xí)的必要性 二 開(kāi)發(fā)工具介 PL/SQL 三 PL/SQL基 介紹 PL/SQL可以做什 編寫規(guī) 塊 過(guò) 函 變量類 四 Pl/sql進(jìn)階（控制語(yǔ)句、過(guò)程、例外 過(guò)程（編寫分頁(yè)過(guò)程 例外處 五 Oracle視 概念 視圖的必要 視圖和表的區(qū) 創(chuàng)建視 視圖可以當(dāng)作表一樣進(jìn)行joinon聯(lián)合查詢 一、PLSQL過(guò)程化語(yǔ)言：在oracle中編寫過(guò)程、函數(shù)、觸發(fā)器等。oracle二、開(kāi)發(fā)工具介創(chuàng)建一Createtablemytable(namevarchar2(20),passwd創(chuàng)建過(guò)程：往mytable表里添加數(shù)據(jù)Create(orrece)proceduremypro1isInsertintomytablevalues(‘韓順平’,’pw1234’);Exec1，Call1，PL/SQL 過(guò)程，該過(guò)程可以刪除某的記Create(orrece)proceduremypro2isDeletefrommytable;Exec三、PL/SQL需掌握SQL語(yǔ)句、PL/SQL語(yǔ)句語(yǔ)則PL/SQL可以做什 過(guò)包多行注釋 標(biāo)識(shí)符號(hào)名規(guī)（block：PLSQLPL/SQLPL/SQL，PL/SQLPL/SQL（Setserveroutputon/off/，再回車，即可輸出2（有定義個(gè)執(zhí)行部分的塊）V_enamevarchar2(5);--定義字符串變量SelectenameintoV_enamefromempwhereempno=&no;--執(zhí)行部分v_enamevarchar2(5);v_salnumber(7,2);selectename,salintov_ename,v_salfromempwhereempno=&no;/回車3（有定義、執(zhí)行和例外部分的塊）v_enamevarchar2(5);v_salnumber(7,2);selectename,salintov_ename,v_salfromempwhereempno=&no;whenno_data_foundthen4（要求輸入用戶名和新的工資，即可修改該用戶的工資）Createproceduresp_pro4(spnamevarchar2,newsalnumber)isUpdateempsetsal=newsalwhwereename=spname;createfunctionsp_fun1(spnamevarchar2)returnnumberyearsalnumber(7,2);selectsal*12+nvl(comm,0)*12intoyearsalfromempreturnyearsal;var年薪callsp_fun1(’SCOTT’)into:包--該包有一個(gè)過(guò)程 createpackagesp_pack1procedureupdate_sal(namevarchar2,newsalnumber); e(namevarchar2)returnnumber;createpackagebodysp_pack1procedureupdate_sal(namevarchar2,newsalnumber)updateempsetsal=newsalwhere e(namevarchar2)returnnumberisannual_salarynumber;selectsal*12+nvl(comm,0)intoannual_salaryfromempreturnexec標(biāo)量類型復(fù)合類型參照類型Lob（largeV_name9999.99~9999.99V_salnumber(6,2);5.4:=是pl/sqlV_sal2V_hiredatefalseV_validBooleannotnulldefaultfalse;輸入員工號(hào)，顯示雇員、工資、個(gè)人所得稅（稅率為0.03）c_tax_ratenumber(3,2):=0.03;v_enamevarchar2(5);v_salnumber(7,2);v_tax_salnumber(7,2);selectename,salintov_ename,v_salfromempwhereempno=&no;||v_enae||''||v_sal||'c_tax_ratenumber(3,2):=0.03;v_enameemp.ename%type;v_salnumber(7,2);v_tax_salnumber(7,2);selectename,salintov_ename,v_salfromempwhereempno=&no;||v_enae||''||v_sal||'Pl/sqlPl/sql（pl/sqlemp_record_type，類型包含三個(gè)數(shù)據(jù)typeemp_record_typeisrecord(nameemp.ename%type,salaryemp.sal%type,titleemp.job%type);sp_recordemp_record_type;selectename,sal,jobintosp_recordfromempwhereempno=&no;dbms_output.put_line('員工名：'||sp_||'工資：Pl/sqlpl/sqlpl/sql表的類型sp_table_type，該類型是用于--indexbybinary_integertypesp_table_typeistableofemp.ename%typeindexbysp_tablesp_table_typesp_tablesp_table_type;selectenameintosp_table(1)fromempwhereempno=&no;參照變量（常用游標(biāo)變量（refcursor）和對(duì)象類型變量（refobj_type）從而降低占用空間，pl/sql（refcursor）（refa)ref_cursorselect當(dāng)使用游標(biāo)時(shí)（open時(shí)）需要指定select語(yǔ)句，這樣一個(gè)游標(biāo)就與一個(gè)select員工和工資；2.在1的基礎(chǔ)上，如果某個(gè)員工工資低于200，就增加100typesp_emp_cursorisreftest_cursorsp_emp_cursor;v_enameemp.ename%type;v_salemp.sal%type;opentest_cursorforselectename,salfromempwherefetchtest_cursorintoexitwhen'||v_ename||'||'||_sal);endclosetest_cursor;四、Pl/sql進(jìn)階（控制語(yǔ)句、過(guò)程、例外10%。createproceduresp_pro6(spnamevarchar2)v_salselectsalintov_salfromempwhereifv_sal<2000updateempsetsal=sal*1.1whereendif;編寫一個(gè)過(guò)程，可以輸入一個(gè)雇員名，如果該雇員的不是0，那1000createproceduresp_pro7(sp_namevarchar2)is selectcomm mfromempwhere m<>0updateemp m+100whereupdateemp m+200whereend編寫一個(gè)過(guò)程，要求，輸入一個(gè)雇員編號(hào)，如果該雇員的職位是就給他的工資增加1000；如果該雇員的職位是manager就500200。createproceduresp_pro8(sp_nonumber)v_jobselectjobintov_jobfromempwhereif 'updateempset whereelsifv_job='MANAGER'updateempsetsal=sal+500whereupdateempsetsal=sal+200whereendif;循環(huán)語(yǔ)句-Loop循環(huán)：最簡(jiǎn)單的循環(huán)loopendloop結(jié)尾，至少循環(huán)表中戶編號(hào)從1開(kāi)始增加（test_user表有idname兩個(gè)字段）createproceduresp_pro9(spnamevarchar2)isv_numnumber:=1;insertintotest_uservalues(v_num,spname);exitwhenv_num=10;endloop;While循環(huán)：先判斷后執(zhí)行，若判斷條件為truewhile…loopendloop實(shí)例：編寫一個(gè)過(guò)程，輸入用戶名，即可循環(huán)添加10個(gè)用戶到foriinreverse1..10insertintotest_uservalues(i,'2aê???×?3');endloop;iintifi=10thengotoend_loop;endif;endNull：nullpl/sqlv_enamev_salselectename,salintofromempwhereifv_sal<3000updateempsetcomm=sal*0.1whereendif;2.過(guò)程（編寫分頁(yè)過(guò)程無(wú)返回值過(guò)程、bookJava、【bookcreatetable(bookidnumber,booknamevarchar2(20),pubhousevarchar2(20));】【createproceduresp_pro7(spbookidinnumber,spbooknameinvarchar2,sppubhouseinvarchar2)isinsertintobookvalues(spbookid,spbookname,sppubhouse);execsp_pro7 '新華】有返回值的過(guò)程（單個(gè)返回值或多個(gè)返回值createproceduresp_pro8(spnoinnumber,spnameoutvarchar2,spjoboutvarchar2,spsaloutnumber)selectename,job,salintospname,spjob,spsalempwhere有返回結(jié)果集的過(guò)因oracle過(guò)程無(wú)返回值，其返回值是用out參數(shù)來(lái)替代，但是集合packagetest_cursorcreatepackagetestpackageastypetest_cursorisrefcursor;end--創(chuàng)建一個(gè)過(guò)createprocedure(spnoinnumber,p_cursorouttestpackage.test_cursor)isopenp_cursorforselect*fromempwheredeptno=spno;select*(selecte1.*,rownumasrn(select*fromemp)e1where)wherecreatepackagetestpackageastest_cursorisrefcursor;endtestpackage;createorreceprocedurefenye(tablenameinvarchar2,pagesizesinnumber,nowinnumber,--第幾頁(yè)myrowsoutnumber,--錄數(shù)mypagecountoutnumber,--總頁(yè)數(shù)p_cursorouttestpackage.test_cursor--)v_sqlv_beginnumber:=(pagenow-1)*pagesizes-1;v_endnumber:=pagenow*pagesizes;v_sql:='select*from(selecte1.*,rownumrn(select*from'||tablename||')whererownum<='||v_end||')whereopenp_cursorforv_sql:='selectcount(*)from'||--sqlmyrows;executeimmediatev_sqlintomyrows;ifmod(myrows,pagesizes)=0endif;--closep_cursor;v_enameemp.ename%type;selectenameintov_enamefromempwhereempno=&no;v_enameemp.ename%type;selectenameintov_enamefromempwhereempno=&no;whenno_data_found用于處理oracle常見(jiàn)錯(cuò)誤，是由PLSQL提供的系統(tǒng)例外，當(dāng)PLSQL應(yīng)用程序oracle規(guī)定的限制時(shí)，則會(huì)隱含觸發(fā)一個(gè)內(nèi)部例外。PLSQL為createorreceproceduresp_pro10(spnoinnumber)isv_salselectsalintov_salfromempwherewhenv_sal<1000updateempsetsal=sal+1wherewhenv_sal<2000updateempsetsal=sal+2whereendcase;whencase_not_foundthendbms_output.put_line('case語(yǔ)句沒(méi)有與'||v_sal||'創(chuàng)建完成后execsp_pro10(’7001’);cursoremp_cursorisselectename,salfromopenforemp_record1inemp_cursorloopendloop;whencursor_already_opentheninsertintodeptvalues(10,'','');whendup_val_on_indexcursoremp_cursorisselectename,salfromemp;emp_recordemp_cursor%rowtype;fetchemp_cursorintoemp_record;closeemp_cursor;wheninvalid_cursorthenupdateempsetsal=sal+'1oo0owheninvalid_numberthenv_enameemp