二十五、oracle pl/sql進階--控制結構(分支，迴圈，控制)

標籤：

 

一、pl/sql的進階--控制結構
在任何電腦語言(c，java，pascal)都有各種控制語句(條件陳述式，迴圈結構，順序控制結構...)，在pl/sql中也存在這樣的控制結構。
在本部分學習完成後，希望大家達到：
1.使用各種if語句
2.使用迴圈語句
3.使用控制語句——goto和null(goto語句不推薦使用)；

  

二、條件分支語句
pl/sql中提供了三種條件分支語句if—then，if–then–else，if–then–else if–then。
這裡我們可以和java語句進行一個比較。

1)、簡單的條件判斷if–then
問題：編寫一個過程，可以輸入一個僱員名，如果該僱員的工資低於2000，就給該員工工資增加10%。

SET serveroutput ON;
CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
  --定義
  V_SAL EMP.SAL%TYPE;
BEGIN
  --執行
  SELECT SAL INTO V_SAL FROM EMP WHERE ENAME = SPNAME;
  --判斷
  IF V_SAL < 2000 THEN
    UPDATE EMP SET SAL = SAL + SAL * 0.1 WHERE ENAME = SPNAME;
    COMMIT;
  END IF;
END;
/

--調用預存程序
exec SP_PRO6(‘ALLEN‘);

2)、二重條件分支 if–then–else
問題：編寫一個過程，可以輸入一個僱員名，如果該僱員的補助不是0就在原來的基礎上增加100；如果補助為0就把補助設為200；

CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
  --定義
  V_COMM EMP.COMM%TYPE;
BEGIN
  --執行
  SELECT COMM INTO V_COMM FROM EMP WHERE ENAME = SPNAME;
  --判斷
  IF V_COMM <> 0 THEN
    UPDATE EMP SET COMM = COMM + 100 WHERE ENAME = SPNAME;
  ELSE
    UPDATE EMP SET COMM = COMM + 200 WHERE ENAME = SPNAME;
  END IF;
  COMMIT;
END;
/

--調用預存程序
exec SP_PRO6(‘ALLEN‘);

3)、多重條件分支 if–then–ELSIF–then
問題：編寫一個過程，可以輸入一個僱員編號，如果該僱員的職位是PRESIDENT就給他的工資增加1000，如果該僱員的職位是MANAGER 就給他的工資增加500，其它職位的僱員工資增加200。

CREATE OR REPLACE PROCEDURE SP_PRO6(SPNO NUMBER) IS
  --定義
  V_JOB EMP.JOB%TYPE;
BEGIN
  --執行
  SELECT JOB INTO V_JOB FROM EMP WHERE EMPNO = SPNO;
  IF V_JOB = ‘PRESIDENT‘ THEN
    UPDATE EMP SET SAL = SAL + 1000 WHERE EMPNO = SPNO;
  ELSIF V_JOB = ‘MANAGER‘ THEN
    UPDATE EMP SET SAL = SAL + 500 WHERE EMPNO = SPNO;
  ELSE
    UPDATE EMP SET SAL = SAL + 200 WHERE EMPNO = SPNO;
  END IF;
  COMMIT;
END;
/
--調用預存程序
exec SP_PRO6(7499);

  

三、迴圈語句–loop
是pl/sql中最簡單的迴圈語句，這種迴圈語句以loop開頭，以end loop結尾，這種迴圈至少會被執行一次。
案例：現有一張表users，表結構如下：
使用者vid | 使用者名稱 uname

CREATE TABLE USERS(
vid NUMBER(5),
uname VARCHAR2(30)
);

請編寫一個過程，可以輸入使用者名稱，並迴圈添加10個使用者到users表中，使用者編號從1開始增加。

CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
  --定義 :=表示賦值
  V_NUM NUMBER := 1;
BEGIN
  LOOP
    INSERT INTO USERS VALUES (V_NUM, SPNAME);
    --判斷是否要退出迴圈
    EXIT WHEN V_NUM = 10;
    --自增
    V_NUM := V_NUM + 1;
  END LOOP;
  COMMIT;
END;
/

--調用預存程序
EXEC SP_PRO6(‘ALLEN‘);

  

四、迴圈語句–while迴圈
基本迴圈至少要執行迴圈體一次，而對於while迴圈來說，只有條件為true時，才會執行迴圈體語句，while迴圈以while...loop開始，以end loop 結束。
案例：現有一張表users，表結構如下：
使用者vid | 使用者名稱 uname
問題：請編寫一個過程，可以輸入使用者名稱，並迴圈添加10個使用者到users表中，使用者編號從11開始增加。

CREATE OR REPLACE PROCEDURE SP_PRO6(SPNAME VARCHAR2) IS
  --定義 :=表示賦值
  V_NUM NUMBER := 11;
BEGIN
  WHILE V_NUM <= 20 LOOP
    --執行
    INSERT INTO USERS VALUES (V_NUM, SPNAME);
    V_NUM := V_NUM + 1;
  END LOOP;
  COMMIT;
END;
/

--調用預存程序
EXEC SP_PRO6(‘ALLEN‘);

            

五、迴圈語句–for迴圈
基本for迴圈的基本結構如下

CREATE OR REPLACE PROCEDURE SP_PRO6 IS--注意如果無參記得不要加()
BEGIN
  FOR I IN REVERSE 1 .. 10 LOOP --REVERSE反轉函數，表示I從10到1遞減，去掉REVERSE表示I從1到10遞增
    INSERT INTO USERS VALUES (I, ‘shunping‘);
  END LOOP;
END;
/

--調用預存程序
EXEC SP_PRO6;

我們可以看到控制變數i，在隱含中就在不停地增加。

  

六、順序控制語句–goto、null
1)、goto語句
goto語句用於跳轉到特定符號去執行語句。注意由於使用goto語句會增加程式的複雜性，並使得應用程式可讀性變差，所以在做一般應用開發時，建議大家不要使用goto語句。
基本文法如下goto lable，其中lable是已經定義好的標號名

set serveroutput on;
DECLARE
  I INT := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE(‘輸出i=‘ || I);
    IF I = 1 THEN
      GOTO END_LOOP;
    END IF;
    I := I + 1;
  END LOOP;
  <<END_LOOP>>
  DBMS_OUTPUT.PUT_LINE(‘迴圈結束‘);
END;
/

  

2)、null語句
null語句不會執行任何操作，並且會直接將控制傳遞到下一條語句。使用null語句的主要好處是可以提高pl/sql的可讀性。

SET serveroutput ON;
DECLARE
  V_SAL   EMP.SAL%TYPE;
  V_ENAME EMP.ENAME%TYPE;
BEGIN
  SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO = &NO;
  IF V_SAL < 3000 THEN
    UPDATE EMP SET COMM = SAL * 0.1 WHERE ENAME = V_ENAME;
    dbms_output.put_line(‘1111‘);
  ELSE
    NULL;
    dbms_output.put_line(‘2222‘);--不會被執行
  END IF;
END;
/

 

二十五、oracle pl/sql進階--控制結構(分支，迴圈，控制)