oracle數(shù)據(jù)庫(kù)課件chapter2PLSQLfor計(jì).ppt

PL/SQL語(yǔ)言基礎(chǔ),一、例1 DECLARE vxh varchar2(6):= 101302 vxm varchar2(8):= 張瓊; vzxf number(2):=45; /*定義變量類(lèi)型*/ BEGIN UPDATE XSB SET XM=vxm, ZXF=vzxf WHERE XH=vxh; /*更新學(xué)生表*/ IF SQL%NOTFOUND THEN /*檢查記錄是否存在，如果不存在就插入記錄*/ INSERT INTO XSB(XH, XM, XB, CSSJ, ZY, ZXF) VALUES(vxh,vxm, 女, 16-5月-90, 軟件工程, vzxf); END IF; END; / (鄭P137),二、PL/SQL簡(jiǎn)述 1. PL/SQL塊及程序 PL/SQL是一種過(guò)程化編程語(yǔ)言。用PL/SQL定 義塊可將多個(gè)SQL編制成程序（程序過(guò)程、函數(shù) 觸發(fā)器等）。程序是由若干PL/SQL塊組成。 2. PL/SQL塊結(jié)構(gòu)與用途 PL/SQL塊由三部分組成： 定義部分：定義所處理的變量、常量、游標(biāo)等。 可執(zhí)行部分：SQL語(yǔ)句及控制結(jié)構(gòu)的PL/SQL語(yǔ)句 異常處理部分：對(duì)執(zhí)行過(guò)程中的錯(cuò)誤進(jìn)行處理。 塊可以嵌套。,PL/SQL塊塊結(jié)構(gòu)： DECLARE 說(shuō)明部分 BEGIN 語(yǔ)句(SQL或PL/SQL語(yǔ)句） EXCEPTION 錯(cuò)誤處理程序 END,3. PL/SQL程序分類(lèi) 由基本PL/SQL塊組成PL/SQL程序。程序有： 無(wú)名塊：它嵌入在某個(gè)應(yīng)用中的PL/SQL塊。 存儲(chǔ)過(guò)程或函數(shù)：命名了的PL/SQL塊，可以帶參數(shù)，并重復(fù)調(diào)用，是數(shù)據(jù)庫(kù)對(duì)象。 包：是命名PL/SQL塊，由一組相關(guān)的過(guò)程、函 數(shù)和標(biāo)識(shí)符組成。是數(shù)據(jù)庫(kù)對(duì)象。 觸發(fā)器:與表相關(guān)聯(lián)的存儲(chǔ)過(guò)程。一表最多12個(gè)。,4. PL/SQL程序運(yùn)行環(huán)境 可以有多個(gè)：SQL Plus Worksheet,SQL PLUS 、isqlplus等。 在SQL PLUS環(huán)境中，第一句是DECLARE或BEGIN就識(shí)別為是PL/SQL程序。 它只能以“/”表示程序結(jié)束。,三、PL/SQL基礎(chǔ) 標(biāo)識(shí)符 標(biāo)識(shí)符是用戶定義的符號(hào)串，用來(lái)命名變量、常 量、過(guò)程等。 標(biāo)識(shí)符以字母開(kāi)頭，后跟數(shù)字(0-9)或特殊字符$、#和_且長(zhǎng)度不超過(guò)30，不能是PL/SQL的保留字，不能有空格。 例1：合法標(biāo)識(shí)符：A34、BB#，D_123DF 不合法標(biāo)識(shí)符：5A$、#ABC、END。,2. 變量及變量說(shuō)明 變量是表示要處理數(shù)據(jù)項(xiàng)的名稱(chēng)。變量名用標(biāo)識(shí) 符來(lái)表示。 變量在使用之前必須用DECLARE進(jìn)行說(shuō)明。 變量說(shuō)明： 變量名 CONSTANT 類(lèi)型 NOT NULL:=值DEFAULT SQL表達(dá)式；,變量或常量數(shù)據(jù)類(lèi)型 數(shù)值型：NUMBER(p,s),INTEGER,FLOAT,DEC 字符型：CHAR(n),VARCHAR2(n),RAW(n) 日期型：DATE 布爾型：BOOLEAN(TRUE或FALSE) 大數(shù)據(jù)類(lèi)型：BFILE,BLOB,CLOB,NCLOB 4. 數(shù)據(jù)類(lèi)型轉(zhuǎn)換 顯示轉(zhuǎn)換: TO_CHAR(),TO_NUMBER(), RAWTOHEX(), ,HEXTORAW(),ROWIDTOCHAR(),TO_DATE(),5. 函數(shù),6. 表達(dá)式 表達(dá)式是由變量、常量、列名、函數(shù)和運(yùn)算符 結(jié)合的有意義式子。 （1）數(shù)值表達(dá)式：數(shù)值運(yùn)算符、變量、常量、函數(shù)等 數(shù)值運(yùn)算符：+、-、/、*、() 例2: DECLARE A INT :=4; B FLOAT:=4.324; X FLOAT; BEGIN X:= SIN(3)*10+2*8-A*10*(10-B*2); DBMS_OUTPUT.PUT_LINE(TO_CHAR(X); END;,（2）字符表達(dá)式：字符運(yùn)算符: |(合并) （3）關(guān)系表達(dá)式:關(guān)系表達(dá)式的結(jié)果是TRUE和FALSE 關(guān)系運(yùn)算符: != = = LIKE IN BETWEENAND 例3: ABC 123 123345 THIS LIKE T% X BETWEEN 10 AND 20 A IN (SS,DD,AD) FALSE SIN(X)+10 A*2+B,（4）邏輯表達(dá)式 邏輯運(yùn)算符：NOT ； AND ； OR 例4: NOT (A 10 AND B100 AND CCC LIKE C% 例5: DECLARE A INT :=4; B FLOAT:=4.324; X FLOAT; C BOOLEAN; BEGIN X:= SIN(3)*10+2*8-A*10*(10-B*2); C:=A IN (SS,DD,AD); IF C OR AB THEN DBMS_OUTPUT.PUT_LINE(TO_CHAR(X); END IF; END;,7. 變量賦值 變量名：=與變量同類(lèi)型的表達(dá)式； 例6: DECLARE C1 CHAR(34) ; -賦給C1的字符串不能大于34 N1 NUMBER(3); N2 NUMBER(2); B1 BOOLEAN; BEGIN N1:=10; N2:=SIN(N1)*2+20.30; C1:=TO_CHAR(N2)| TEST; B1:= C1 LIKE %T; END;,8. 注釋行 單行注釋?zhuān)涸谝恍械娜魏挝恢靡浴?字符串” 多行注釋?zhuān)?* 注釋內(nèi)容 */ 例7:DECLARE C1 CHAR(34); -字符變量，長(zhǎng)度不超過(guò)34 N1 NUMBER(3); -數(shù)字型變量 BEGIN /* 賦值語(yǔ)句范例： 變量類(lèi)型要與表達(dá)式類(lèi)型一致 */ N1:=10; C1:=TO_CHAR(N1)| TEST; END;,9. PL/SQL中的語(yǔ)句 PL/SQL塊中每一行一條語(yǔ)句，并且必須以分號(hào) “；”結(jié)束。 變量或常量說(shuō)明語(yǔ)句、 變量賦值語(yǔ)句、CASE語(yǔ)句和IF語(yǔ)句、 各種循環(huán)語(yǔ)句、數(shù)據(jù)處理語(yǔ)句SIDU、事務(wù)處理語(yǔ)句、游標(biāo)語(yǔ)句DBMS_OUTPUT.PUT_LINE(字符串) 等。 COMMIT、ROLLBACK EXECUTE IMMEDIATE 動(dòng)態(tài)串 SELECTINTO 變量名表|記錄名 FROM,每個(gè)塊由若干語(yǔ)句組成。若干塊組成程序。,四、PL/SQL控制結(jié)構(gòu) 三種程序結(jié)構(gòu)：順序、選擇和循環(huán) 順序結(jié)構(gòu) 順序結(jié)構(gòu)是指執(zhí)行過(guò)程按所寫(xiě)程序的順序執(zhí)行。 例8： DECLARE VV CHAR(20); SAL NUMBER(4); BEGIN SELECT LAST_NAME INTO VV FROM EMPLOYEES WHERE EMPLOYEE_ID=201; DBMS_OUTPUT.PUT_LINE(VV); SAL:=10; END;,例9：DECLARE JID VARCHAR2(10):=AD_VP; JTIT CHAR(30); BEGIN SELECT JOB_TITLE INTO JTIT FROM JOBS WHERE JOB_ID=JID; DBMS_OUTPUT.PUT_LINE(JTIT); END;,說(shuō)明：JID的類(lèi)型與JOB_ID的類(lèi)型要完全一致，且長(zhǎng)度要一樣。改為JID CHAR(10)將出錯(cuò)。 可改為：JID JOBS.JOB_ID%TYPE:=AD_VP;,2. IF選擇結(jié)構(gòu),條件,語(yǔ)句1,語(yǔ)句2,IF 條件 THEN 語(yǔ)句序列； END IF; “條件”為邏輯表達(dá)式或關(guān)系表達(dá)式，“條件”為T(mén)RUE時(shí)執(zhí)行“語(yǔ)句序列”，為FALSE時(shí)執(zhí)行END IF后面的語(yǔ)句。,IF 條件 THEN 語(yǔ)句序列1 ELSE 語(yǔ)句序列2 END IF;,ENDIF,IF 條件1 THEN 語(yǔ)句序列1 ELSIF 條件2 THEN 語(yǔ)句序列2 ELSE 語(yǔ)句序列3 END IF; 例10: DECLARE N1 NUMBER:=11; N2 NUMBER; BEGIN IF N110 THEN N2:=N1+10; DBMS_OUTPUT.PUT_LINE(TO_CHAR(N2); END IF; END;,例11：嵌套選擇結(jié)構(gòu) DECLARE N1 NUMBER:=11; N2 NUMBER DEFAULT 10; BEGIN IF N110 THEN IF N2N1 THEN N2:=N1+10; DBMS_OUTPUT.PUT_LINE(TO_CHAR(N2); END IF; END IF; END; 說(shuō)明：嵌套必須是完全嵌套,可以是任何選擇結(jié)構(gòu) IF_END IF、IF_ELSE_END IF 等。,例12：IFTHENELSE結(jié)構(gòu) DECLARE N1 NUMBER:=9; N2 NUMBER:=19; BEGIN IF N110 THEN N2:=N2+10; ELSE N2:=N1*N1; END IF; DBMS_OUTPUT.PUT_LINE(TO_CHAR(N2); END;,例13: IFTHENELSIFEND IF結(jié)構(gòu) DECLARE N1 NUMBER:=10; N2 NUMBER:=20; CC CHAR(20); C2 DATE:=TO_DATE(1999-1-1,YYYY-MM-DD); BEGIN DBMS_OUTPUT.PUT_LINE(TO_CHAR(C2); IF N110 THEN N2:=N1+10; ELSIF N1 BETWEEN 7 AND 12 THEN N2:=SQRT(N1)*2; ELSIF C2=DATE1998-1-1 THEN CC:=TO_CHAR(SYSDATE); ELSE CC:=NO RESULT; END IF; DBMS_OUTPUT.PUT_LINE(CC| |TO_CHAR(N2); END;,3. CASE選擇結(jié)構(gòu) CASE 變量 WHEN 表達(dá)式1 THEN 語(yǔ)句序列1; WHEN 表達(dá)式2 THEN 語(yǔ)句序列2; WHEN 表達(dá)式N THEN 語(yǔ)句序列N; ELSE 語(yǔ)句序列N+1; END CASE,說(shuō)明: 1. 當(dāng)“變量”的值與某個(gè)表達(dá)式值相同時(shí)，將執(zhí)行相應(yīng)的語(yǔ)句序列 2. 當(dāng)變量與所有表達(dá)式不等時(shí)執(zhí)行序列N+1 3. 語(yǔ)句序列1到語(yǔ)句序列N+1中只能執(zhí)行一個(gè)語(yǔ)句序列。,例14：DECLARE C1 CHAR(1):=F; N1 NUMBER:=2; N2 NUMBER:=35; NC NUMBER; BEGIN NC:=ASCII(C1); CASE NC WHEN N2*N2 THEN N1:=N2*N2; DBMS_OUTPUT.PUT_LINE(N1=|TO_CHAR(N1); WHEN N2+N2 THEN N1:=N2+N2; DBMS_OUTPUT.PUT_LINE(N1=|TO_CHAR(N1); ELSE DBMS_OUTPUT.PUT_LINE(N1=|TO_CHAR(N1); DBMS_OUTPUT.PUT_LINE(NC=|TO_CHAR(NC); END CASE; END;,4. 循環(huán)結(jié)構(gòu) 循環(huán)結(jié)構(gòu)是指按照指定的邏輯條件循環(huán)執(zhí)行一組 命令。有三種循環(huán)：LOOP-EXIT-END;LOOP- EXIT-WHEN-END;WHILE-LOOP-END和FOR- IN-LOOP-END。,當(dāng)條件為真時(shí)，執(zhí)行語(yǔ)句序列，直到條件為假。 條件是任何合法的邏輯表達(dá)式或關(guān)系表達(dá)式。,4.1 LOOP-EXIT-END循環(huán) LOOP 語(yǔ)句序列 EXIT END LOOP 說(shuō)明：執(zhí)行EXIT時(shí)從循環(huán) 中退出，在條件語(yǔ)句中執(zhí) 行EXIT。該循環(huán)中，必須 有EXIT,否則就會(huì)成為“死 循環(huán)”。EXIT只能在循環(huán)體內(nèi)。,例15： 計(jì)算1+2+3+100 DECLARE I INT :=1; S INT:=0; BEGIN LOOP S:=S+I; IF I=100 THEN EXIT; END IF; I:=I+1; END LOOP; DBMS_OUTPUT. PUT_LINE(TO_CHAR(S); END;,4.2 LOOP-EXIT WHEN-END循環(huán) LOOP 語(yǔ)句序列 EXIT WHEN 條件 -等價(jià)IF 條件 THEN EXIT，可用在其它循環(huán) END LOOP,例15：計(jì)算10！ DECLARE I INT :=1; S INT:=1; BEGIN LOOP S:=S*I; EXIT WHEN I=10; I:=I+1; END LOOP; DBMS_OUTPUT.PUT_LINE(TO_CHAR(S); END;,4. 3 WHILE-LOOP-END循環(huán) WHILE 條件 LOOP 每次循環(huán)前計(jì)算條 語(yǔ)句序列； 件，為T(mén)RUE，執(zhí) END LOOP； 行語(yǔ)句，否則不。,例16：計(jì)算s=1*2+2*3+N*(N+1),當(dāng)N=50的值。 DECLARE I INT :=1; S INT:=0; BEGIN WHILE I=50 LOOP S:=S+I*(I+1); I:=I+1; END LOOP; DBMS_OUTPUT.PUT_LINE(TO_CHAR(S); END;,例17：在WHILE循環(huán)中使用EXIT或EXIT WHEN 計(jì)算S=1*2*3+2*3*4+N*(N+1)*(N+2);當(dāng)N=40 DECLARE I INT :=1; S INT:=0; BEGIN WHILE TRUE LOOP S:=S+I*(I+1)*(I+2); EXIT WHEN I=40; I:=I+1; END LOOP; DBMS_OUTPUT.PUT_LINE(TO_CHAR(S); END; 說(shuō)明：EXIT和EXIT WHEN可用在任何循環(huán)內(nèi)， 且只能在循環(huán)內(nèi)。,4.4 FOR-IN-LOOP-END循環(huán) FOR 循環(huán)變量 IN REVERSE下界上界 LOOP 語(yǔ)句序列； END LOOP；,說(shuō)明： 循環(huán)變量被隱式說(shuō)明為BINARY-INTEGER，也可顯式說(shuō)明。 步長(zhǎng)1或-1(REVERSE)，循環(huán)次數(shù):上界-下界+1; EXIT或EXIT WHEN可用在FOR循環(huán)中； 已知循環(huán)次數(shù)時(shí)，可用FOR循環(huán)，也可用其它 末知循環(huán)次數(shù)不能用FOR循環(huán) 上界或下界可以為表達(dá)式。,例18：顯示20到50的平方根的值及它們的和。 DECLARE S FLOAT :=0; BEGIN FOR I IN 2050 LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQRT(I),9.9999); S:=S+SQRT(I); END LOOP; DBMS_OUTPUT.PUT_LINE(S= |TO_CHAR(S); END; 說(shuō)明：循環(huán)變量I不用說(shuō)明。,例19: 步長(zhǎng)-1。加REVERSE。 /*說(shuō)明：加REVERSE，上界仍要大于下界* / DECLARE S FLOAT :=0; BEGIN FOR I IN REVERSE 2050 LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQRT(I),9.9999); S:=S+SQRT(I); END LOOP; DBMS_OUTPUT.PUT_LINE(S= |TO_CHAR(S); END;,例20:上下界為表達(dá)式的FOR循環(huán) DECLARE S FLOAT :=0; N INT :=10; BEGIN FOR I IN REVERSE 2*N-1N*N LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQRT(I),9.9999); S:=S+SQRT(I); END LOOP; DBMS_OUTPUT.PUT_LINE(S= |TO_CHAR(S); END;,例21: 生成字符串SWJ001-SWJ120 declare n number(1); sn varchar2(8); sn1 char(3); begin for n in 1120 loop if n10 then sn1:=ltrim(to_char(n,9); sn:=rtrim(swj00|sn1); else if n100 then sn1:=ltrim(to_char(n,99); sn:=rtrim(swj0|sn1); else sn1:=ltrim(to_char(n,999); sn:=rtrim(swj|sn1); end if ; end if; DBMS_OUTPUT.PUT_LINE(SN); end loop; End;,4. 5 循環(huán)嵌套 上面的任何循環(huán)都可以互相嵌套，且可以多重嵌 套，但必須是完全嵌套。如： LOOP FOR LOOP -可有其它循環(huán)(多重循環(huán)) END LOOP; WHILE LOOP -可有其它循環(huán)(多重循環(huán)) END LOOP; END LOOP;,例22: 兩重循環(huán)，計(jì)算S=1!+2!+10! DECLARE S FLOAT :=0; N INT :=10; K INT ; BEGIN FOR I IN 1N LOOP - 計(jì)算階乘和 K:=1; FOR J IN 1I LOOP -計(jì)算K! K:=K*J; END LOOP; S:=S+K; DBMS_OUTPUT.PUT_LINE(TO_CHAR(K); END LOOP; DBMS_OUTPUT.PUT_LINE(S= |TO_CHAR(S); END;,4.6 循環(huán)標(biāo)簽 循環(huán)標(biāo)簽是以用來(lái)標(biāo)示循環(huán),放在循環(huán)前。其格式： 例23 DECLARE S FLOAT :=0; N INT :=10; K INT ; BEGIN FOR I IN 1N LOOP - 計(jì)算階乘和 K:=1; FOR J IN 1I LOOP -計(jì)算K! K:=K*J; END LOOP FOR_LOOP2; S:=S+K; DBMS_OUTPUT.PUT_LINE(TO_CHAR(K); END LOOP FOR_LOOP1; DBMS_OUTPUT.PUT_LINE(S= |TO_CHAR(S); END;,循環(huán)標(biāo)簽一是提高程序的可讀性，二是可以 從內(nèi)循環(huán)跳到外層。 LOOP . LOOP . EXIT outer WHEN . 退出兩重循環(huán)。 END LOOP; . END LOOP outer; 注意：只能從內(nèi)循環(huán)跳到外循環(huán)，反之不行。,五、%TYPE類(lèi)型 PL/SQL變量用來(lái)存儲(chǔ)表中的數(shù)據(jù)，但二者要有相 同類(lèi)型。要保證變量與列名類(lèi)型總是一致，就要 用%TYPE類(lèi)型。 變量名 表名.列名%TYPE; 例24：DECLARE J_T JOBS.JOB_TITLE%TYPE; BEGIN SELECT JOB_TITLE INTO J_T FROM JOBS WHERE JOB_ID=AD_VP; DBMS_OUTPUT.PUT_LINE(J_T); END;,六、%ROWTYPE類(lèi)型 用%ROWTYPE說(shuō)明變量為一個(gè)記錄類(lèi)型。 變量名 表名%ROWTYPE; 引用變量時(shí)：變量名.列名 例25：DECLARE EMP EMPLOYEES %ROWTYPE; BEGIN SELECT * INTO EMP FROM EMPLOYEES WHERE EMPLOYEE_ID=201; DBMS_OUTPUT.PUT_LINE (EMP.LAST_NAME| |EMP.FIRST_NAME); END;,七、游標(biāo)(CURSOR) 游標(biāo)是將從表中選擇的一組記錄，放置在內(nèi)存的 臨時(shí)表中。游標(biāo)是數(shù)據(jù)類(lèi)型，要先說(shuō)明后使用。 說(shuō)明游標(biāo) CURSOR 游標(biāo)名 IS SELECT語(yǔ)句； 例26: DECLARE MIN_S JOBS.MIN_SALARY%TYPE; CURSOR MS IS SELECT * FROM JOBS WHERE MIN_SALARY=MIN_S; BEGIN NULL; -空語(yǔ)句 END;,2. 打開(kāi)游標(biāo) OPEN 游標(biāo)名； 注意：打開(kāi)之前，游標(biāo)中的變量必須有值；打開(kāi) 游標(biāo)后,查詢記錄放在內(nèi)存,指針指向第一個(gè)記錄。 例27：DECLARE MIN_S JOBS.MIN_SALARY%TYPE; CURSOR MS IS SELECT * FROM JOBS WHERE MIN_SALARY=MIN_S; BEGIN MIN_S:=1000; -先給變量賦值 OPEN MS; - 才能打開(kāi)游標(biāo) END;,3. 提取游標(biāo)數(shù)據(jù) FETCH 游標(biāo)名 INTO 變量1,變量2,; 注意: 變量要與說(shuō)明游標(biāo)時(shí)SELECT后的列名在類(lèi) 型和個(gè)數(shù)上完成一樣。不能只提取部分。 FETCH MS INTO J1，J2，M1，M2； 4. 關(guān)閉游標(biāo) CLOSE 游標(biāo)； CLOSE MS； 關(guān)閉后的游標(biāo)不能再使用。 使用游標(biāo)的過(guò)程：說(shuō)明游標(biāo)、變量賦值、打開(kāi)游 標(biāo)、處理、關(guān)閉游標(biāo)。,例28：提出數(shù)據(jù) DECLARE J1 JOBS.JOB_ID%TYPE; -變量名與列名類(lèi)型保持一致 J2 JOBS.JOB_TITLE%TYPE; M1 JOBS.MIN_SALARY%TYPE; M2 JOBS.MAX_SALARY%TYPE; MIN_S JOBS.MIN_SALARY%TYPE; CURSOR MS IS SELECT * FROM JOBS -說(shuō)明游標(biāo) WHERE MIN_SALARY=MIN_S; BEGIN MIN_S:=1000; -先給變量賦值 OPEN MS; - 才能打開(kāi)游標(biāo) FETCH MS INTO J1,J2,M1,M2; -第一條記錄 DBMS_OUTPUT.PUT_LINE(J1| |J2| |TO_CHAR(m1+m2); FETCH MS INTO J1,J2,M1,M2; -第二條記錄 DBMS_OUTPUT.PUT_LINE(J1| |J2| |TO_CHAR(m1+m2); FETCH MS INTO J1,J2,M1,M2; -第三條記錄 DBMS_OUTPUT.PUT_LINE(J1| |J2| |TO_CHAR(m1+m2); END;,例29: 復(fù)雜游標(biāo)（分組） DECLARE DEP EMPLOYEES.DEPARTMENT_ID%TYPE; COU INT; CURSOR DEPT IS SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID; BEGIN OPEN DEPT; - 打開(kāi)游標(biāo) FETCH DEPT INTO DEP,COU; -第一條記錄 DBMS_OUTPUT.PUT_LINE(DEP|的人數(shù)是： |TO_CHAR(COU); FETCH DEPT INTO DEP,COU; -第二條記錄 DBMS_OUTPUT.PUT_LINE(DEP|的人數(shù)是： |TO_CHAR(COU); CLOSE DEPT; -關(guān)閉游標(biāo) END;,5. 游標(biāo)屬性 通過(guò)游標(biāo)屬性來(lái)知道游標(biāo)的狀態(tài)。 A: %FOUND 如果前一個(gè)FETCH返回一行，%FOUND返回 TRUE；否則返回FALSE。在未打開(kāi)游標(biāo)之前設(shè) 置%FOUND，返回錯(cuò)誤ORA_1001。用法： 游標(biāo)名%FOUND %NOTFOUND 與%FOUND相反，常用于退出循環(huán)提出。 C. %ISOPEN 判定游標(biāo)是否打開(kāi)。打開(kāi)為T(mén)RUE,否則為FALSE.,%ROWCOUNT 返回從游標(biāo)中已提取的行數(shù)。,例30：declare emp employees %rowtype; cursor sal is select * into emp from employees where salary=10000; -說(shuō)明游標(biāo) begin open sal; - 打開(kāi)游標(biāo) loop fetch sal into emp; if sal%found then - 提出成功 dbms_output.put_line (emp.last_name| 工資:|to_char(emp.salary); else - 不成功 exit; end if; end loop; CLOSE sal end;,例31：測(cè)試游標(biāo)屬性 declare emp employees %rowtype; cursor sal is select * into emp from employees where department_id=100; begin if not sal%isopen then -游標(biāo)未打開(kāi) open sal; end if; dbms_output.put_line(提取行數(shù)：|to_char(sal%rowcount); loop fetch sal into emp; dbms_output.put_line(emp.last_name|行:|to_char(sal%rowcount); exit when sal%notfound or sal%rowcount=5; end loop; end;,例32: 有表DATA_FIEL(n1,n2,n3,exnum)和表TEMP DECLARE num1 data_table.n1%TYPE; num2 data_table.n2%TYPE; num3 data_table.n3%TYPE; result temp.col1%TYPE; CURSOR c1 IS SELECT n1, n2, n3 FROM data_table WHERE exnum = 1; BEGIN OPEN c1; LOOP FETCH c1 INTO num1, num2, num3; EXIT WHEN c1%NOTFOUND; result := num2/(num1 + num3); INSERT INTO temp VALUES (result, NULL, NULL); END LOOP; CLOSE c1; COMMIT; END;,八、參數(shù)化游標(biāo)和隱含游標(biāo) 參數(shù)化游標(biāo) CRUSOR 游標(biāo)名(變量1 類(lèi)型1,變量2 類(lèi)型2,) IS SELECT語(yǔ)句 說(shuō)明：SELECT語(yǔ)句的條件中使用變量,變量類(lèi)型 可以為%TYPE。 打開(kāi)參數(shù)化游標(biāo)： OPEN 游標(biāo)名(實(shí)參1,實(shí)參2,); 說(shuō)明：在打開(kāi)時(shí)所有實(shí)參要有具體值，且在數(shù)量 和類(lèi)型上與游標(biāo)說(shuō)明時(shí)一致。 實(shí)參可以是表達(dá)式。,例33：帶參數(shù)游標(biāo) DECLARE EMP EMPLOYEES%ROWTYPE; CURSOR c1(S1 EMPLOYEES.SALARY%TYPE, DID INT) IS SELECT * FROM EMPLOYEES WHERE SALARY=S1 and DEPARTMENT_ID=did; BEGIN OPEN c1(10000,90); LOOP FETCH c1 INTO EMP; DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT); EXIT WHEN c1%NOTFOUND; END LOOP; CLOSE c1; END;,例34: 帶參數(shù)游標(biāo) declare cursor c1(s1 employees.salary%type) is select max(salary),min(salary) from employees where salary=s1; m1 employees.salary%type:=16000; m2 employees.salary%type:=6000; begin open c1(m1+m2)/2); -實(shí)參為表達(dá)式 loop fetch c1 into m1,m2; if c1%found then insert into jobs values (new_j,new job,m1,m2); end if; dbms_output.put_line(to_char(c1%rowcount); exit when c1%notfound; end loop; close c1; commit; -提交游標(biāo) end;,2. 隱含游標(biāo)（SQL游標(biāo)) 每次處理一個(gè)SQL語(yǔ)句，ORACLE自動(dòng)打開(kāi)一個(gè) 游標(biāo)，該游標(biāo)不能使用OPEN,FETCH或CLOSE進(jìn) 行操作。但可用游標(biāo)特性來(lái)獲得執(zhí)行SQL的信息。 隱含游標(biāo)的名字是：SQL SQL游標(biāo)總是反映最后一個(gè)SQL語(yǔ)句執(zhí)行情況。 %ISOPEN 永遠(yuǎn)是FALSE(執(zhí)行完SQL后自動(dòng)關(guān)閉） %FOUND 至少處理一行,為T(mén)RUE;否則為FALSE %NOTFOUND 與%FOUND相反 %ROWCOUNT 處理影響的行數(shù)。,例35: 隱含游標(biāo)的屬性使用 begin delete from jobs where job_id like ddad%; if sql%found then dbms_output.put_line(刪除行數(shù): |to_char(sql%rowcount); else dbms_output.put_line(沒(méi)有刪除任何行); end if; update jobs set max_salary=(max_salary+min_salary)/2, min_salary=(max_salary-min_salary)/2 where job_id like ad%; if sql%found then dbms_output.put_line(修改行數(shù):|to_char(sql%rowcount); else dbms_output.put_line(沒(méi)有修改任何行); end if; end;,BEGIN UPDATE emp SET salary=salary+100; if SQL%FOUND THEN DBMS_OUTPUT.put_line(被修改的行數(shù)：|sql%rowcount); end if; delete from dept; if sql%found then dbms_output.put_line(被刪除的行數(shù):|SQL%ROWCOUNT); end if; INSERT INTO DEPT VALUES (01,NETWORK); INSERT INTO DEPT VALUES (02,SOFTWARE); if sql%found then dbms_output.put_line(最近插入的行數(shù):|SQL%ROWCOUNT); end if; rollback;,SQL / 被修改的行數(shù)：107 被刪除的行數(shù):28 最近插入的行數(shù):1 PL/SQL procedure successfully completed,上機(jī)題： 計(jì)算1！+2！+3！+10!的值。 計(jì)算 x/1!+x2/2!+.+xn/n! (其中X2,n=10) 編程序求滿足不等式 1+32+52+N22000的最小N值。 計(jì)算下面級(jí)數(shù)當(dāng)末項(xiàng)小于0.001時(shí)的部分和。 1/(1*2)+1/(2*3)+1/(3*4)+1/(n*(n+1)+ 計(jì)算并輸出1900-2000之間的素?cái)?shù)的個(gè)數(shù)N 定義游標(biāo)：從雇員表中顯示工資大于10000的記錄，只要姓名、部門(mén)編號(hào)和工資。編程顯示其中的奇數(shù)記錄。 定義游標(biāo)：列出每個(gè)員工的姓名、部門(mén)名稱(chēng)。編程顯示第10個(gè)到第20個(gè)記錄。,8. 在JOBS表中刪除工作編號(hào)為AD_NEW的記錄，如果無(wú)，插入(AD_NEW,POLICE,1999,20000) 9. 將雇員表中的所有工資小于10000增加1000，統(tǒng)計(jì)出增加工資的人數(shù)及增加的工資數(shù)量。 10. 將雇員表中的部門(mén)編號(hào)為100的所有員工刪除，統(tǒng)計(jì)刪除的人數(shù)及刪除人的平均工資。 11. 編程輸出9*9乘法表。 12. 從雇員表中顯示工資最高的前五個(gè)人的姓名，部門(mén)和工資。,14.5過(guò)程、函數(shù)、觸發(fā)器,無(wú)名塊只能以文件形式執(zhí)行、不存放在數(shù)據(jù)庫(kù)中， 每次執(zhí)行要編譯，不能在其它PL/SQL塊中調(diào)用。 過(guò)程、函數(shù)和觸發(fā)器都是有名塊，是數(shù)據(jù)庫(kù)對(duì)象,一、過(guò)程設(shè)計(jì) 創(chuàng)建過(guò)程(系統(tǒng)權(quán)限CREATE PROCEDURE) 過(guò)程結(jié)構(gòu)如下： CREATE OR REPLACE PROCEDURE 過(guò)程名(參數(shù)列表) AS 說(shuō)明部分 -內(nèi)部變量 BEGIN 執(zhí)行部分 EXCEPTION 異常處理部分 END 過(guò)程名,說(shuō)明： 過(guò)程必須有名字; 過(guò)程可以有參數(shù)或無(wú)參數(shù) 創(chuàng)建過(guò)程經(jīng)編譯無(wú)錯(cuò)后存放在數(shù)據(jù)庫(kù)中(不馬上執(zhí)行)； 調(diào)用過(guò)程時(shí)執(zhí)行塊內(nèi)語(yǔ)句。 過(guò)程的使用：先創(chuàng)建后調(diào)用。,例1. Create or replace procedure test1 as sal employees.salary%type; begin select salary into sal from employees where last_name=Abel; dbms_output.put_line(to_char(sal); end; 注意:即使程序編譯有錯(cuò)，過(guò)程也將寫(xiě)入數(shù)據(jù)庫(kù)。,查詢過(guò)程USER_SOURCE USER_SOURCE: NAME、TEXT、TYPE、LINE SELECT TEXT FROM USER_SOURCE WHERE NAME=TTT ORDER BY LINE; 3. 修改過(guò)程 在OEM中，在指定用戶模式下，選中過(guò)程名，通過(guò)查看/編輯詳細(xì)資料，可修改。 EDIT 4. 調(diào)用過(guò)程(EXECUTE ANY PROCEDURE) 在塊中或過(guò)程中調(diào)用存儲(chǔ)過(guò)程，不能在表達(dá)式中調(diào)用。,調(diào)用方式：過(guò)程名(實(shí)參1,實(shí)參N) 例2: 用戶SWJ001： BEGIN TEST1; END; 例3:SWJ001:GRANT EXECUTE ON TEST1 TO HR; HR用戶：BEGIN SWJ001.TEST1; END; 5. 刪除過(guò)程 DROP PROCEDURE 過(guò)程名 例4: DROP PROCEDURE TEST1;,6. 帶參數(shù)的過(guò)程 CREATE OR REPLACE PROCEDURE 過(guò)程名 (參數(shù)1 IN|OUT|IN OUT 類(lèi)型1,) AS 內(nèi)部變量說(shuō)明 BEGIN 執(zhí)行部分 EXCEPTION 異常處理 END； 說(shuō)明：IN 向過(guò)程送參數(shù)，讀入?yún)?shù) OUT: 從過(guò)程獲得參數(shù)，輸出參數(shù) IN OUT 即可以讀入?yún)?shù)，可也獲得參數(shù),例5: create or replace procedure test2( v1 in employees.job_id%type, -參數(shù)說(shuō)明 v2 out employees.salary%type) as vv employees.salary%type;-內(nèi)部變量說(shuō)明 begin select max(salary) into vv from employees where job_id like v1; v2:=vv+10000; -OUT類(lèi)型參數(shù)必須賦值 end; 調(diào)用：declare v employees.salary%type; begin test2(AD%,v); dbms_output.put_line(to_char(v); end;,例6：求階乘 create or replace procedure jc(v1 in int, v2 out int) as s int; begin s:=1; for i in 1v1 loop s:=s*i; end loop; v2:=s; dbms_output.put_line(過(guò)程內(nèi)V2值:|to_char(s); end; 調(diào)用： declare k int:=10; begin jc(k,k); dbms_output.Put_line(過(guò)程外K:|to_char(k); end;,注意： 過(guò)程調(diào)用參數(shù)類(lèi)型與個(gè)數(shù)必須完全一致；,OUT或IN OUT參數(shù)在調(diào)用時(shí)不能對(duì)應(yīng)表達(dá)式；例：JC(K+1,K);-正確 但JC(K+1,K*2);非法,參數(shù)表示對(duì)應(yīng)表列時(shí)，最好%TYPE類(lèi)型,過(guò)程調(diào)用只能出現(xiàn)在塊中，不能與表達(dá)式計(jì)算,可以多個(gè)OUT參數(shù)來(lái)返回多個(gè)值。,IN參數(shù)在過(guò)程中不能賦值,如果要給參數(shù)賦值，必須指定為OUT或IN OUT,不指明IN、OUT或IN OUT時(shí)，缺省為IN 出錯(cuò)信息表USER_ERRORS(列：LINE,TEXT,),例7:在過(guò)程中用游標(biāo)(返回第N個(gè)不等于的記錄) create or replace procedure jobk( j in jobs.job_id%type, jk out jobs%rowtype, n in int) as cursor j_c is select * from jobs where job_idj; k int; begin k:=1; if not j_c%isopen then open j_c; end if; loop fetch j_c into jk; exit when n=k; k:=k+1; end loop; close j_c; end;,調(diào)用： declare jj jobs%rowtype; begin jobk(ST_MAN,jj,3); dbms_output.put_line(jj.job_id| |jj.job_title); jobk(FI_MGR,jj,5); dbms_output.put_line(jj.job_id| |jj.job_title); end;,例8: 顯示表EMPLOYEES的第N條記錄的內(nèi)容 create or replace procedure n_record(n int, emp out employees%rowtype) as k int; cursor e_c is select * from employees; begin k:=1; if not e_c %isopen then open e_c; end if; loop fetch e_c into emp; exit when e_c%notfound or k=n; k:=k+1; end loop; if kn then dbms_output.put_line(NO FOUND); end if; end;,例9: 顯示MN條記錄的姓名和工資 create or replace procedure mn_record(m int , n int) as jj employees %rowtype; k int; begin k:=m; loop n_record(k,jj); -在mn_record過(guò)程中調(diào)用過(guò)程 dbms_output.put_line(jj.last_name| |to_char(jj.salary); k:=k+1; exit when kn; end loop; end; 調(diào)用： BEGIN mn_record(10,20); END;,二、函數(shù)設(shè)計(jì) 函數(shù)是命名塊，可以有參數(shù)或無(wú)參數(shù)，但必須有 返回值，函數(shù)是用于表達(dá)式中而不能單獨(dú)作為一 個(gè)命令行。其它與過(guò)程相似。 建立函數(shù) CREATE OR REPLACE FUNCTION 函數(shù)名 (參數(shù)1IN|OUT|IN OUT,) RETURN 類(lèi)型 AS 內(nèi)部參數(shù)說(shuō)明 BEGIN 執(zhí)行部分或有異常處理 END;,例1:判定整數(shù)N是否為素?cái)?shù) create or replace function pri(n int) return boolean as k int; flag boolean; - ii int; begin k:=round(sqrt(n); flag:=false; for I in 2k loop ii:=i; exit when mod(n,i)=0; end loop; if iik then flag:=false; -return false; elsif mod(n,ii)=0 then flag:=false; -return false; else flag:=true; -return true; end if; return flag; - end;,例2：顯示2到200之間的所有素?cái)?shù) begin for i in 2200 loop if pri(i) then dbms_output.put_line(to_char(i); end if; end loop; end;,例3：函數(shù)中的游標(biāo) create or replace function dep return int as cursor de is select department_id,count(*) from employees group by department_id; S int:=0; d1 employees.department_id%type; i1 int; Begin open de; loop fetch de into d1,i1; s:=s+i1; exit when de%notfound; end loop; return s; End; - 無(wú)參數(shù)函數(shù)，調(diào)用時(shí)用DEP()或DEP 調(diào)用：select dep()+10 from dual;,2. 刪除函數(shù)和修改函數(shù) 修改函數(shù)是以文件存在,在SQL PLUS:get filename 刪除函數(shù)：DROP FUNCTION 函數(shù)名； 3. 查詢函數(shù)(USER_SOURCE) NAME,TYPE,TEXT,LINE 三、子程序 內(nèi)置子程序(Stored Subprogram：過(guò)程與函數(shù)) 用Create or replace 建立的子程序是內(nèi)置子程序， 它們存放在數(shù)據(jù)庫(kù)中，它是數(shù)據(jù)庫(kù)對(duì)象。任何位 置都可調(diào)用。 USER_OBJECTS： 用戶所有對(duì)象信息 USER_SOURCE,USER_ERROR(編譯錯(cuò)誤信息),2. 本地子程序 如果子程序是在PL/SQL塊中說(shuō)明部分定義，叫本 地子程序。它只能在本塊中調(diào)用。本地子程序的