PLSQL

PL/SQL塊

 PL/SQL塊是在SQL語言之上發展起來的一種應用，可以集中的處理各種複雜的SQL操作。

組成：

 DECLARE：

  聲明部分

 BEGIN

  編寫趁許的主題

 EXCEPTION

  捕獲異常

 END ;

 /

看一個簡單的PL/SQL塊

DECLARE

 i NUMBER ;

BEGIN

 i := 30 ;

 DBMS_OUTPUT.put_line('I的内容為：'||i) ;

END ;

/

此時，直接執行程式即可。

執行之後發現沒有任何的輸出。因為Oracle在系統設定中預設設定了輸出不顯示，如果要顯示的話，輸入以下指令：

 set serveroutput on

PL/SQL塊還可以接收使用者的輸入資訊，例如：現在要求使用者輸入一個雇員編号，之後根據輸入的内容進行查詢，查詢雇員的姓名。

 • 使用者的輸入資訊使用“&”完成。

 eno NUMBER ;

 en VARCHAR(30) ;

 -- 輸入的資訊儲存在eno裡

 eno := &no ;

 -- 之後根據eno的值，對資料庫進行查詢操作

 SELECT ename INTO en FROM emp WHERE empno=eno ;

 DBMS_OUTPUT.put_line('編号為：'||eno||'雇員的姓名為：'||en) ;

EXCEPTION

 WHEN no_data_found THEN

  DBMS_OUTPUT.put_line('沒有此雇員') ;

在以上的查詢中再進一步：可以根據雇員的編号查出姓名及其上司的姓名和所在的部門，進行顯示。

 eno emp.empno%TYPE ;

 en emp.ename%TYPE ;

 mn emp.ename%TYPE ;

 dn dept.dname%TYPE ;

 SELECT e.ename,m.ename,d.dname INTO en,mn,dn FROM emp e,dept d,emp m WHERE e.empno=7369 AND e.mgr=m.empno AND e.deptno=d.deptno ;

 DBMS_OUTPUT.put_line('編号為：'||eno||'雇員的上級姓名為：'||mn) ;

 DBMS_OUTPUT.put_line('編号為：'||eno||'雇員所在的部門：'||dn) ;

說明：

 • emp.empno%TYPE ;：表示以emp表中的empno字段的類型定義變量

 • e.ename,m.ename,d.dname INTO en,mn,dn：一次可以同時放進去多個值

PL/SQL之中也包含了：循環、分支等條件控制語句

1、 Loop循環

格式：

 LOOP

  循環的語句 ;

  EXIT WHEN 終止條件 ;

  循環條件必須更改 ;

 END LOOP ;

循環輸出1~10。

 cou NUMBER ;

 -- 必須給一個初始值

 cou := 1 ;

 LOOP 

  DBMS_OUTPUT.put_line('cou = '||cou) ;

  EXIT WHEN cou>10 ;

  cou := cou + 1 ;

 此循環是先執行一次之後再進行判斷

2、 while循環

 while(判斷循環的條件) loop

  循環條件的改變 ;

 End loop ;

使用此語句修改上面的程式：

 WHILE(cou<10) LOOP

 此語句，是先判斷，之後如果條件滿足則執行，與while循環類似。

3、 for循環

 FOR 變量名稱 in 變量的初始值..結束值 LOOP

  循環語句 ;

 FOR cou IN 1..10 LOOP

4、 IF語句

 條件判斷

 IF 條件 THEN

  滿足條件時，執行此語句

 END IF ;

 cou := 11 ;

 IF cou>10 THEN

5、 IF…ELSE語句

 如果IF滿足了，則執行，否則執行ELSE

 ELSE

  DBMS_OUTPUT.put_line('條件不滿足') ;

6、 IF…ELSIF…ELSE語句

 ELSIF cou<5 THEN

  DBMS_OUTPUT.put_line('值小于5') ;

問題1：

 輸入一個雇員的編号，如果其工資高于3500，則顯示高工資，工資大于2000，則顯示中等工資，工資小于2000的則認為是低等工資。

 sal emp.sal%TYPE ;

 eno := &en ;

 SELECT sal INTO sal FROM emp WHERE empno=eno ;

 IF sal>3500 THEN

  DBMS_OUTPUT.put_line('高工資。。。') ;

 ELSIF sal>2000 THEN

  DBMS_OUTPUT.put_line('中等工資。。') ;

  DBMS_OUTPUT.put_line('底工資。。。') ;

問題2：

 輸入一個雇員編号，根據它所在的部門漲工資，規則：

 • 10部門上漲10%

 • 20部門上漲20%

 • 30部門上漲30%

 所有部門的上漲工資，最不能超過5000，如果超過5000，則工資就為5000。

 dno emp.deptno%TYPE ;

 SELECT deptno,sal INTO dno,sal FROM emp WHERE empno=eno ;

 IF dno=10 THEN

  IF sal*1.1>5000 THEN

   UPDATE emp SET sal=5000 WHERE empno=eno ;

  ELSE

   UPDATE emp SET sal=sal*1.1 WHERE empno=eno ;

  END IF ;

 ELSIF dno=20 THEN

  IF sal*1.2>5000 THEN

   UPDATE emp SET sal=sal*1.2 WHERE empno=eno ;

 ELSIF dno=30 THEN

  IF sal*1.3>5000 THEN

   UPDATE emp SET sal=sal*1.3 WHERE empno=eno ;

  null ;

7、 GOTO語句

 無條件跳轉語句

  goto po1 ;

  goto po2 ;

  goto po3 ;

 <<po1>>

 <<po2>>

 <<po3>>

遊标

 操作步驟：

 • 聲明遊标

 • 打開遊标

 • 取出結果，此時的結果取出的是一行資料

 • 關閉遊标

到底那種類型可以把一行的資料都裝進來

 • 此時使用ROWTYPE類型，此類型表示可以把一行的資料都裝進來。

例如：查詢雇員編号為7369的資訊（肯定是一行資訊）。

 empInfo emp%ROWTYPE ;

 SELECT * INTO empInfo FROM emp WHERE empno=eno ;

 DBMS_OUTPUT.put_line('雇員編号：'||empInfo.empno) ;

 DBMS_OUTPUT.put_line('雇員姓名：'||empInfo.ename) ;

編寫第一個遊标，輸出全部的資訊。

 -- 聲明遊标

 CURSOR mycur IS SELECT * FROM emp ;

 -- 遊标操作使用循環，但是在操作之前必須先将遊标打開

 OPEN mycur ;

 -- 使遊标向下一行

 FETCH mycur INTO empInfo ;

 -- 判斷此行是否有資料被發現

 WHILE (mycur%FOUND) LOOP

  DBMS_OUTPUT.put_line('雇員編号：'||empInfo.empno) ;

  DBMS_OUTPUT.put_line('雇員姓名：'||empInfo.ename) ;

  -- 修改遊标，繼續向下

  FETCH mycur INTO empInfo ;

也可以使用另外一種方式循環遊标：LOOP…END LOOP;

  -- 使遊标向下一行

  EXIT WHEN mycur%NOTFOUND ;

注意1：

 在打開遊标之前最好先判斷遊标是否已經是打開的。

 通過ISOPEN判斷，格式：遊标%ISOPEN

 IF mycur%ISOPEN THEN

  OPEN mycur ;

注意2：

 可以使用ROWCOUNT對遊标所操作的行數進行記錄。

  cou := mycur%ROWCOUNT ;

  DBMS_OUTPUT.put_line(cou||'雇員編号：'||empInfo.empno) ;

  DBMS_OUTPUT.put_line(cou||'雇員姓名：'||empInfo.ename) ;

使用for循環操作遊标（比較常用）

 FOR empInfo IN mycur LOOP

練習：

 一次性上漲全部雇員的工資。根據它所在的部門漲工資，規則：

  IF empInfo.deptno=10 THEN

   IF empInfo.sal*1.1>5000 THEN

    UPDATE emp SET sal=5000 WHERE empno=empInfo.empno ;

   ELSE

    UPDATE emp SET sal=sal*1.1 WHERE empno=empInfo.empno ;

   END IF ;

  ELSIF empInfo.deptno=20 THEN

   IF empInfo.sal*1.2>5000 THEN

    UPDATE emp SET sal=sal*1.2 WHERE empno=empInfo.empno ;

  ELSIF empInfo.deptno=30 THEN

   IF empInfo.sal*1.3>5000 THEN

    UPDATE emp SET sal=sal*1.3 WHERE empno=empInfo.empno ;

   null ;

過程（存儲過程）：

 過程 = 過程的聲明 + PL/SQL塊

現在定義一個簡單的過程，就是列印一個數字

CREATE OR REPLACE PROCEDURE myproc

AS

 i := 100 ;

 DBMS_OUTPUT.put_line('i = '||i) ;

執行過程：

 exec 過程名字

下面編寫一個過程，要求，可以傳入部門的編号，部門的名稱，部門的位置，之後調用此過程就可以完成部門的增加操作。

CREATE OR REPLACE PROCEDURE myproc(dno dept.deptno%TYPE,name dept.dname%TYPE,dl dept.loc%TYPE)

 -- 判斷插入的部門編号是否存在，如果存在則不能插入

 SELECT COUNT(deptno) INTO cou FROM dept WHERE deptno=dno ;

 IF cou=0 THEN

  -- 可以增加新的部門

  INSERT INTO dept(deptno,dname,loc) VALUES(dno,name,dl) ;

  DBMS_OUTPUT.put_line('部門插入成功！') ;

  DBMS_OUTPUT.put_line('部門已存在，無法插入！') ;

 END IF ; 

過程的參數類型：

 • IN：值傳遞，預設的

 • IN OUT：帶值進，帶值出

 • OUT：不帶值進，帶值出

IN OUT類型：

CREATE OR REPLACE PROCEDURE myproc(dno IN OUT dept.deptno%TYPE,name dept.dname%TYPE,dl dept.loc%TYPE)

  -- 修改dno的值

  dno := 1 ;

  dno := -1 ;

編寫PL/SQL塊驗證過程：

 deptno dept.deptno%TYPE ;

BEGIN 

 deptno := 12 ;

 myproc(deptno,'開發','南京') ;

 DBMS_OUTPUT.put_line(deptno) ;

OUT類型

 不帶任何值進，隻把值帶出來。

CREATE OR REPLACE PROCEDURE myproc(dno OUT dept.deptno%TYPE)

 dno := 10 ;

 myproc(deptno) ;

函數：

 函數就是一個有傳回值的過程。

定義一個函數：此函數可以根據雇員的編号查詢出雇員的年薪

CREATE OR REPLACE FUNCTION myfun(eno emp.empno%TYPE) RETURN NUMBER

 rsal NUMBER ;

 SELECT (sal+nvl(comm,0))*12 INTO rsal FROM emp WHERE empno=eno ;

 RETURN rsal ;

直接寫SQL語句，調用此函數：

 SELECT myfun(7369) FROM dual ;

本文轉自 李興華 51CTO部落格，原文連結：http://blog.51cto.com/lixinghua/91221，如需轉載請自行聯系原作者