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

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

（DML語(yǔ)句〕…2.常量和變量定變量的基本類型就是oracle中的建表時(shí)字段的變量如char,varchar2,date,number,boolean,long constantboolean:=true 型變量，即my_name的類型與empename列的類型一樣在sql中使用into來(lái)賦值emprecemp.ename%type; pemp%rowtype;select*intopfromemptwheret.empno=7369;dbms_output.put_line(p.ename||''||p.sal);3.if分支 條件THEN語(yǔ)句2;IF條件THEN 語(yǔ)句序列2； 條件THEN語(yǔ)句;ELSIF語(yǔ)句THEN pnumnumber:=#ifpnum=1thenendif;mynumnumber:=#ifmynum=1thenendif;mynumnumber:=#ifmynum<18thenelsifmynum18andmynum40thenelsifmynum>=40thenendif;4.LOOP循環(huán) <= ..total:=total+salary; 條件 1.. 語(yǔ)句序列 范例: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;過(guò)程（StoredProcedure）SQL語(yǔ)句集，經(jīng)create[orrece]PROCEDURE過(guò)程名[(參數(shù)名in/out數(shù)據(jù)類型)]create[orrece]PROCEDURE過(guò)程名[(參數(shù)名in/out數(shù)據(jù)類型)] 過(guò)程名 createorre ceprocedureoworldisendoworld;在plsql中調(diào)用過(guò)--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ù)名;但過(guò)程和函數(shù)都可以通過(guò)outout參數(shù)，在過(guò)程和函數(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ù)庫(kù)觸發(fā)器是一個(gè)與表相關(guān)聯(lián)的、的PL/SQL程序。每當(dāng)一個(gè)特定的數(shù)據(jù)操作語(yǔ)了多少行。發(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)用過(guò)程[應(yīng)用1.javaoraclejar可以在虛擬機(jī)中xp的oracle安 下找到j(luò)ar包2.數(shù)據(jù)庫(kù)連接字符StringStringStringurl="jdbc:oracle:thin:@192.168.56.10:1521:orcl";Stringusername="scott";String3.實(shí)現(xiàn)過(guò)程的調(diào)1.調(diào)用過(guò)1.1.8.1.11.----統(tǒng)計(jì)年薪的過(guò)createorre ceprocedureproc_countyearsal(enoinnumber,esaloutnumber)selectsal*12+nvl(comm,0)in