Oracle(培訓(xùn)班帶出來)

1、.：.；PL/SQL簡介 Oracle公司在規(guī)范SQL言語的根底上開展出了PL/SQL(Procedural Language/SQL,即過程化SQL言語)言語，將變量、控制構(gòu)造、過程和函數(shù)等構(gòu)造化程序設(shè)計(jì)的要素引入SQL言語中，這樣就可以編寫出較復(fù)雜的SQL程序了。以bdyd的身份登錄，創(chuàng)建一個(gè)用戶表testtablecreate table testtable( recordNumber number(4) not null, currentDate date not null);向上述數(shù)據(jù)庫表中插入100條記錄 set serveroutput ondeclare maxrecords

2、constant int:=100; i number :=1;begin for i in 1.maxrecords loop insert into testtable(recordnumber,currentdate) values(i,sysdate); end loop;dbms_output.put_line(勝利錄入數(shù)據(jù)！);commit;end;其中，dbms_output為系統(tǒng)默許的程序包，put_line是包中定義的方法，功能是輸出信息。PL/SQL程序構(gòu)造結(jié)合上述實(shí)例進(jìn)展分析，一個(gè)完好的PL/SQL程序的構(gòu)造可以分成以下3部分：1定義部分：以declare開場，定義在程序

3、中所要運(yùn)用的常量、變量等等。它不像高級(jí)言語那樣可以在程序執(zhí)行過程中進(jìn)展定義，一切用到的內(nèi)容都必需在declare中進(jìn)展定義。2執(zhí)行部分：以begin開場，以end終了，中間部分是對(duì)數(shù)據(jù)庫的操作語句和各種流程控制語句。3異常處置部分：該部分包含在執(zhí)行部分里面，以exception標(biāo)識(shí)，該部分用來對(duì)運(yùn)用程序產(chǎn)生的例外進(jìn)展處置。一個(gè)完好的PL/SQL的構(gòu)造 declare 定義部分 begin 執(zhí)行部分 exception 異常處置部分 end在上述3部分中，只需begin.end部分是不可短少的1)begin.end部分set serveroutput onbegin dbms_output.pu

4、t_line(helloworld);end;2)declare 部分set serveroutput ondeclare v_stuName varchar2(20);begin v_stuName := zhangsan; dbms_output.put_line(v_stuName);end;declare v_stuName varchar2(20) not null :=lisi;begin commit;end;3)exception 異常處置部分set serveroutput ondeclare v_num number:=0;begin v_num := 9/v_num; d

5、bms_output.put_line(v_num);exception when others then dbms_output.put_line(error);end;常量常量的定義： 常量名 constant 數(shù)據(jù)類型 := 值例如： declare PI constant number(9) := 3.1415926; begin commit; end;變量1根本數(shù)據(jù)類型變量的定義： 變量名 數(shù)據(jù)類型 not null := 初始值例如: declare age number(3) :=25; begin commit; end;2復(fù)合數(shù)據(jù)類型變量：法一：運(yùn)用%type定義變量 為了

6、讓變量的類型和數(shù)據(jù)庫表中字段的數(shù)據(jù)類型一致，Oracle9i提供了%type的定義方法。這樣當(dāng)數(shù)據(jù)庫表中字段的類型修正后，PL/SQL程序中相應(yīng)變量的類型也自動(dòng)修正。 例如:declare mydate testtable.currentDate%type;begin commit;end;法二：記錄類型變量很多構(gòu)造化程序設(shè)計(jì)言語都提供了記錄類型的數(shù)據(jù)類型，在PL/SQL中，也支持將多個(gè)根本數(shù)據(jù)類型捆綁在一同的記錄數(shù)據(jù)類型。例如：declare type stuInfo is record( stuName varchar2(20), stuAge number, birthday date)

7、; myInfo stuInfo;begin myInfo.stuName := zhangsan;myInfo.stuAge := 25;myInfo.birthday := sysdate;dbms_output.put_line(myInfo.birthday);end;法三：運(yùn)用%rowtype定義變量運(yùn)用%type可以使得變量的類型和數(shù)據(jù)庫表中字段的類型一致，而運(yùn)用%rowtype可以使得變量的類型和整個(gè)記錄的類型一致。declare mytable testtable%rowtype;begin select * into mytable from testtable where

8、recordnumber=99; dbms_output.put_line(mytable.currentdate);end;declare mydept dept%rowtype;beginselect * into mydept from dept where deptno=10;dbms_output.put_line(mydept.loc);end;在上述例如中，定義了一個(gè)變量mytable，它的類型和數(shù)據(jù)庫表testtable的構(gòu)造一樣。表達(dá)式常量,變量經(jīng)常要組合成表達(dá)式來進(jìn)展各種運(yùn)算，下面引見在PL/SQL中常見表達(dá)式的運(yùn)算規(guī)那么。1算術(shù)表達(dá)式:算術(shù)運(yùn)算符：+ - * / *例如：

9、set serveroutput ondeclare result number;begin result := 10+4*5+5*2; dbms_output.put_line(運(yùn)算結(jié)果是|to_char(result);end;declare area number; radius number := 3;begin area := 3.14*radius*radius; dbms_output.put_line(area);end;還有關(guān)系表達(dá)式、邏輯表達(dá)式等等。條件控制1) ifelse if 條件 then 語句段1 else 語句段2 end if;例如： set serverou

10、tput ondeclare number1 number :=90; number2 number :=30;begin if number1=number2 then dbms_output.put_line(number1=number2); else dbms_output.put_line(number1=85 then dbms_output.put_line(good); else if score=75 and score=90 then dbms_output.put_line(A); when score=80 and score=89 then dbms_output.p

11、ut_line(B); when score between 70 and 79 then dbms_output.put_line(C); else dbms_output.put_line(below C); end case;end;循環(huán)控制1)loopexitend loop loop 循環(huán)體 if 條件語句 then exit; else 退出循環(huán)的語句處置 end if; end loop;例如：set serveroutput ondeclare number1 number :=80; number2 number :=90; i number := 0;begin loop

12、number1:=number1+1; if number1=number2 then exit; else i := i+1; end if; end loop; dbms_output.put_line(共循環(huán)次數(shù):|to_char(i);end;2)loopexit when.end loop exit when 等價(jià)于 if 條件 then exit； end if；例如：set serveroutput ondeclare number1 number :=80; number2 number :=90; i number := 0;begin loop number1:=numbe

13、r1+1; i := i+1; exit when number1=number2; end loop; dbms_output.put_line(共循環(huán)次數(shù):|to_char(i);end;3) whileloopend loop while 條件 loop 循環(huán)體 end loop; 例如：set serveroutput ondeclare number1 number :=80; number2 number :=90; i number := 0;begin while number120;insert into view1 values(15,aaa,aaa);select * f

14、rom view1;select * from dept;create or replace view view2 as select * from dept where deptno20 with check optioncreate or replace view view3 as (復(fù)雜的sql)當(dāng)視圖基于多個(gè)表時(shí)不讓插入數(shù)據(jù)create or replace view view4 as select * from dept with read onlyselect text from user_views where where view_name=view4;同義詞:以king登陸C

15、reate synonym dept for scott.dept;Select * from dept;用另一用戶銜接時(shí)不可訪問-私有同義詞System/managerDrop synonym dept;Create public synonym dept from scott.dept;序列Create sequence myseqStart with 1Increment by 1OrderNocycleSelect myseq.nextval from dual;Select myseq.currval from dual;Insert into testable values(mys

16、eq.nextval,sysdate);Desc dba_sequences;Select sequence_name,sequence_owner from dba_sequence where sequence_name=myseq;Alter sequence myseq increment by 3;Select myseq.nextval from dual;游標(biāo)什么是游標(biāo)？游標(biāo)是構(gòu)建在PL/SQL中，用來查詢數(shù)據(jù)，獲取記錄集合的指針。它可以讓開發(fā)者一次訪問結(jié)果集中的一行。游標(biāo)可以以編程的方式訪問數(shù)據(jù)， 從而完成需求分別在結(jié)果集中每個(gè)記錄上執(zhí)行的過程代碼的義務(wù)。在Oracle中提供了

17、兩種游標(biāo)類型： 靜態(tài)游標(biāo)：在編譯時(shí)知道其select語句的游標(biāo)。靜態(tài)游標(biāo)又分成隱式游標(biāo)和顯示游標(biāo)。 REF游標(biāo)：游標(biāo)運(yùn)用的查詢直到運(yùn)轉(zhuǎn)的時(shí)候才可以確定，實(shí)踐上就是帶參數(shù)的游標(biāo)。隱式游標(biāo)：PL/SQL為一切SQL數(shù)據(jù)支配語句隱式聲明游標(biāo)。之所以稱為隱式游標(biāo)，是由于用戶不能直接命名和控制此類游標(biāo)。當(dāng)用戶在PL/SQL中 運(yùn)用數(shù)據(jù)支配語句時(shí)，Oracle預(yù)先定義一個(gè)名為SQL的隱式游標(biāo)，經(jīng)過檢查隱式游標(biāo)的屬性可以獲取最近執(zhí)行的SQL語句的相關(guān)信息。在執(zhí)行DML語句之后，隱式游標(biāo)屬性前往信息：%FOUND:只需在sql語句隱式一行或多行時(shí)，才前往true%NOTFOUND%ROWCOUNT:sql語句

18、影響的行數(shù)%ISOPEN:游標(biāo)能否翻開，執(zhí)行完sql語句后，Oracle自動(dòng)封鎖SQL游標(biāo)，所以隱式游標(biāo)的%ISOPEN屬性一直為falsedeclare empid emp.empno%type; job emp.job%type;begin empid := &職員編號(hào); select job into job from emp where empno=empid; if sql%rowcount0 then dbms_output.put_line(職員的頭銜是|job); end if; exception when others then dbms_output.put_line(n

19、ot found);end;declare cursor c is select * from emp; firstemp c%rowtype;begin open c; fetch c into firstemp; dbms_output.put_line(firstemp.ename); close c;end;declare cursor c is select * from emp; firstemp c%rowtype; begin open c; loop fetch c into firstemp; exit when (c%notfound); dbms_output.put_

20、line(firstemp.ename); end loop; close c; end;declare cursor c is select * from emp; firstemp c%rowtype; begin open c; loop fetch c into firstemp; dbms_output.put_line(firstemp.ename); exit when (c%notfound); end loop; close c; end;declare cursor c is select * from emp; firstemp c%rowtype;begin open

21、c; fetch c into firstemp; while(c%found) loop dbms_output.put_line(firstemp.ename); fetch c into firstemp; end loop; close c;end;declare cursor c is select * from emp;begin for firstemp in c loop dbms_output.put_line(firstemp.ename); end loop;end;declare cursor c(v_deptno emp.deptno%type,v_job emp.j

22、ob%type) is select ename,sal from emp where deptno=v_deptno and job=v_job;begin for firstemp in c(30,CLERK) loop dbms_output.put_line(firstemp.ename); end loop;end;declare cursor c is select * from emp for update; begin for firstemp in c loop if (firstemp.salb) then ret := a; else ret := b; end if;

23、temp := temp+1;end;帶參數(shù)存儲(chǔ)過程的調(diào)用declare a number := 1; b number := 2; ret number; temp number := 3;begin p(a,b,ret,temp); dbms_output.put_line(ret); dbms_output.put_line(temp);end;關(guān)于show error;函數(shù):與過程類似，也是數(shù)據(jù)庫中存儲(chǔ)的已命名的PL/SQL程序塊，它的主要特征是必需前往一個(gè)值。函數(shù)的一些限制：1)函數(shù)只能帶有in參數(shù)，不能帶有out和in out參數(shù)2)形參只能運(yùn)用數(shù)據(jù)庫類型，不得運(yùn)用PL/SQL類型

24、3)函數(shù)的前往類型也必需是數(shù)據(jù)庫類型創(chuàng)建函數(shù)：create or replace function calculateTax (sal number) return numberisbegin if sal2000 then return 0.10; elsif sal=1); create table transInfo( cardId varchar2(15) not null, transType varchar2(10) not null, transMoney number not null); alter table transInfo add constraint ck2 che

25、ck(transType in(存入,取出); insert into bank values(zhangsan,1001,1000); insert into bank values(lisi,1002,1); insert into transInfo(cardId,transType,transMoney) values(1001,取出,200); select * from bank; select * from transInfo;當(dāng)zhangsan取了200元時(shí)，雖然買賣信息表中保管了取款200元的信息，但帳戶表中的余額依然是1000，并沒有自動(dòng)跟隨修正。顯然，我們應(yīng)該 根據(jù)買賣類

26、型是“存入還是“取出，自動(dòng)添加或減少帳戶表中的余額。而且，它應(yīng)該具有事務(wù)的特征：一旦買賣失敗，對(duì)余額的修正也應(yīng) 該自動(dòng)取消。這個(gè)時(shí)候，就需求依托觸發(fā)器的作用。觸發(fā)器的分類：1)Insert觸發(fā)器：當(dāng)向表中插入數(shù)據(jù)時(shí)觸發(fā)，自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語句。2) Update觸發(fā)器：當(dāng)更新表中某列、多列時(shí)觸發(fā)，自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語句3)Delete觸發(fā)器：當(dāng)刪除表中記錄時(shí)觸發(fā)，自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語句觸發(fā)器的作用：強(qiáng)化約束，級(jí)聯(lián)運(yùn)轉(zhuǎn)create table emp2_log(userName varchar2(20),action varchar2(20),actionTime

27、date)create or replace trigger mytrig after insert or delete or update on emp2 for each rowbegin if inserting then insert into emp2_log values(user,insert,sysdate); elsif updating then insert into emp2_log values(user,update,sysdate); elsif deleting then insert into emp2_log values(user,delete,sysda

28、te); end if;end;-create or replace trigger del_deptid after delete on dept for each rowbegin delete from emp where id=:old.id;end;delete from dept where deptno=10;rollback;-create or replace trigger insert_dept after insert on dept for each rowbegin insert into emp(empno,ename,deptno) values(111,111,:new.id);end;insert into dept val