主流框架34會(huì)員版20就業(yè)課20oracle大綱

第1章Oracle第二1.：CREATEVIEWAScreateviewempvd20asselect*fromemptwheret.deptno= CEVIEW視圖名稱AS子查createorreceviewempvd20asselect*fromemptwheret.deptno= CEVIEW視圖名稱AS子查詢WITHREADcreateorreceviewempvd20asselect*fromemptwheret.deptno=20withreadi/o專門來索引，索引是要有時(shí)間精力的開銷的，也就是說索引是不能亂建的，所以建索引有CREATEindexonCreateindexemp_idx1onemp(ename,job);Createindexemp_idx1oncreateindexpname_indexoncreateindexpname_gender_indexon(name,什么是PL/SQL（ProcedurePLSQLOraclesqlSQL（如分支、循PLSQLupdateempsetsal=sal*1.11.pl/sql程序語

（DML語句〕…2.常量和變量定變量的基本類型就是oracle中的建表時(shí)字段的變量如char,varchar2,date,number,boolean,long constantboolean:=true 型變量，即my_name的類型與empename列的類型一樣在sql中使用into來賦值emprecemp.ename%type; pemp%rowtype;select*intopfromemptwheret.empno=7369;dbms_output.put_line(p.ename||''||p.sal);3.if分支 條件THEN語句2;IF條件THEN 語句序列2； 條件THEN語句;ELSIF語句THEN pnumnumber:=#ifpnum=1thenendif;mynumnumber:=#ifmynum=1thenendif;mynumnumber:=#ifmynum<18thenelsifmynum18andmynum40thenelsifmynum>=40thenendif;4.LOOP循環(huán) <= ..total:=total+salary; 條件 1.. 語句序列 范例:1110stepnumber:=1;whilestep<=10loopstep:=step+1;endloop;范例:2110stepnumber:=1;exitwhenstep>10;step:=step+1;endloop;范例:3110stepnumber:=1;forstepin1..10loopendloop; 游標(biāo)名[(參數(shù)名數(shù)據(jù)類型,參數(shù)名數(shù)據(jù)類型,...)] 例如：cursorc1isselectenamefromemp; open exitwhen注意：上面的pjob必須與emp表中的job列類型一致：定義：pjobemp.empjob%type;cursorpcisselect*fromemp;pempemp%rowtype;openpc;fetchpcexitwhendbms_output.put_line(pemp.empno||''||pemp.ename);endloop;closepc;備份出一張新表為myemp;createtablemyempasselect*fromemp;cursorpcisselect*frommyemp;addsalmyemp.sal%type;pempmyemp%rowtype;openpc;fetchpcexitwhenifpemp.job=''thenaddsal:=1000;elsifpemp.job='MANAGER'thenaddsal:=800;addsal:=endupdatemyemptsett.sal=t.sal+addsalwheret.empno=endloop;closecursorpc(dnomyemp.deptno%type)selectempnofrommyempwheredeptno=dno;pnomyemp.empno%type;openpc(20);fetchpcexitwhenupdatemyemptsett.sal=t.sal+1000wheret.empno=pno;endloop;closepc;過程（StoredProcedure）SQL語句集，經(jīng)create[orrece]PROCEDURE過程名[(參數(shù)名in/out數(shù)據(jù)類型)]create[orrece]PROCEDURE過程名[(參數(shù)名in/out數(shù)據(jù)類型)] 過程名 createorre ceprocedureoworldisendoworld;在plsql中調(diào)用過--CallthecreateorreceprocedureaddSal1(enoinnumber)ispempmyemp%rowtype;select*intopempfrommyempwhereempno=eno;updatemyempsetsal=sal+100whereempno=eno;dbms_output.put_line漲工資前||pemp.sal||漲工資后||(pemp.sal+100));end--Calltheprocedureaddsal1(eno=>7902);createorrecefunction函數(shù)名(Nameintype,Nameintype,...)return數(shù)據(jù)類型;end函數(shù)名;但過程和函數(shù)都可以通過outout參數(shù)，在過程和函數(shù)中實(shí)createorrecefunction numberispsalemp.sal%type; selectt.salintopsalfromemptwheret.empno=eno;returnpsal*12+nvl(pcomm,0);createorreceprocedure ep(enoinemp.empno%type,eoutnumber)ispsalemp.sal%type; select mintopsal,pcommfromemptwhere=e:=psal*12+nvl(pcomm,0); e 數(shù)據(jù)庫觸發(fā)器是一個(gè)與表相關(guān)聯(lián)的、的PL/SQL程序。每當(dāng)一個(gè)特定的數(shù)據(jù)操作語了多少行。發(fā)器中使用old和new偽記錄變量,識(shí)別值的狀態(tài)。 [orRECE] {DELETE|INSERT|UPDATEOF列名 [FOREACHROWWHEN(條件PLSQLEndcreateorrecetriggertestTriggerafterinserton--localvariablesendtestTrigger;createorrecetriggervalidInsertbeforeinsertonweekendvarchar2(10);selectto_char(sysdate,'dayintoweekendfromdual;ifweekendin('星期一')thenraise_application_error(-20001,'不能在時(shí)間插入員工');endif;end createorre cetriggeraddsal4pbeforeupdateofsalonmyempforeachrowif:old.sal>=:new.salraise_application_error(-20002漲前的工資不能大于漲后的工資');endif;updatemyemptsett.sal=t.sal-七．Java程序調(diào)用過程[應(yīng)用1.javaoraclejar可以在虛擬機(jī)中xp的oracle安 下找到j(luò)ar包2.數(shù)據(jù)庫連接字符StringStringStringurl="jdbc:oracle:thin:@192.168.56.10:1521:orcl";Stringusername="scott";String3.實(shí)現(xiàn)過程的調(diào)1.調(diào)用過1.1.8.1.11.----統(tǒng)計(jì)年薪的過createorre ceprocedureproc_countyearsal(enoinnumber,esaloutnumber)selectsal*12+nvl(comm,0)in