順序結構

按先后順序

?

分支判斷結構

?

IF語句

IF condition THEN

statements;

[ELSIF condition?THEN

statements;]

[ELSE

statements;]

END IF;

?

例：

DECLARE

v_myage number :=31;

BEGIN

IF v_myage<11

THEN

DBMS_OUTPUT.PUT_LINE(' I am a child ');

ELSIF v_myage<20 THEN

DBMS_OUTPUT.PUT_LINE(' I am young');

ELSIF v_myage<30 THEN

DBMS_OUTPUT.PUT_LINE('I am in twenties');

ELSIF v_myage<40 THEN

DBMS_OUTPUT.PUT_LINE(' I am in thirties');

ELSE

DBMS_OUT.PUT_LINE(' I am always yound');

END IF;

END;

/

?

例：

DECLARE

v_myage number;

BEGIN

IF v_myage <11 THEN

DBMS_OUTPUT.PUT_LINE(' I am a child ');

ELSE

DBMS_OUTPUT.PUT_LINE(' I am not a child');

END IF;

END;

/

注：初始化不賦值默認為NULL，不能確定其數值。

?

?

CASE表達式

CASE selector

WHEN expression1 THEN result1

WHEN expression2 THEN result2

...

WHEN expressionN THEN resultN

END;

?

例：

SET VERIFY OFF

DECLARE

v_grade CHAR(1) := UPPER('&grade');

v_appraisal VARCHAR2(20);

BEGIN

v_appraisal := CASE v_grade

WHEN 'A' THEN??'Excllent'

WHEN 'B' THEN ' Very Good'

WHEN 'C' THEN 'GOOD'

ELSE 'No such grade'

END;

DBMS_OUTPUT.PUT_LIND('Grade: ' || v_grade || 'Appraisal ' || v_appraisal);

END;

/

?

CASE語句

例:

DECARE

v_deptid NUMBER;

v_deptname VARCHAR2(20);

v_emps NUMBER;

v_mngid NUMBER := 108;

BEGIN

CASE v_mngid

WHEN??108 THEN

SELECT department_id,department_name

INTO v_deptid,v_deptname FROM departments

WHERE manager_id=108;

SELECT cout(*) INTO v_emps FROMemployees

WHERE department_id=v_deptid;

WHEN 200 THEN

END CASE;

DBMS_OUTPUT.PUT_LINE('You are working in the ' || v_deptname ||

'department. There are ' || v_emps || 'employees in this departmen');

END;

/

?

?

NULL邏輯運算

FALSE AND NULL = FALSE

FALSE OR NULL = NULL

?

短路

兩種條件中只要判斷一個條件成立則執行下一步

?

IF condtion1 AND condition2

THEN

statements;

ELSE

statements;

END IF;

?

IF condition1 OR condition2

THEN

statements;

ELSE

statements;

END IF;

?

GOTO語句

GOTO??label_name;

GOTO語句用作無條件跳轉，但只能在pl/sql塊內部跳轉，需要與標簽連用，但是標簽后不能直接跟END，如果沒有表達式則加多一行NULL;

?

例：

?DECLARE

…

BEGIN

IF condition THEN GOTO lastpoint END IF;

…

<<lastpoint>>

NULL;

END;

?

例：

BEGIN

GOTO second_output;

DBMS_OUTPUT.PUT_LINE('The line will never execute.');

<<second_output>>

DBMS_OUT.PUT_LINE('We are here!');

END;

/

?

循環結構

?

Basic loop(基本循環)至少被執行一次

LOOP

statement1;

EXIT [WHEN condition];(退出循環)

END;

?

例：

DECLARE

v_countryid loactions.country_id%TYPE := 'CA';

v_loc_id locations.location_id%TYPE;

v_counter NUMBER(2) :=1;

v_new_city locations.city%TYPE := 'Montreal';

BEGIN

SELECT MAX(location_id) INTO v_loc_id FROM locations

WHERE contry_id = v_countryid;

LOOP

INSERT INTO locatinos(location_id,city,country_id)

VALUES ((v_loc_id + v_counter),v_new_city,v_countryid);

v_counter := v_counter+1;

EXIT WHEN v_counter>3;

END LOOP;

END;

/

?

?

FOR loop (FOR循環)

FOR counter IN [ REVERSE]

lower_bound..upper_bound LOOP

statement1;

statement2;

..

END LOOP;

注釋：counter?不需要定義，在END LOOP后就不能再被使用

?

例：

DECLARE

v_coutryid locations.country_id%TYPE := 'CA';

v_loc_id locations.location_id%TYPE;

v_new_city locations.city%TYPE := 'Montreal';

BEGIN

SELECT MAX(location_id) INTO v_loc_id

FROM locations

WHERE country_id = v_countryid;

FOR i IN 1..3 LOOP

INSERT INTO locations(location_id,city,country_id)

VALUES((v_loc_id + i), v_new_city,v_countryid);

END LOOP;

END;

/

?

?

WHILE loop(WHILE循環)

WHILE condition LOOP

statement1;

statement2;

END LOOP;

?

例：

DECLARE

v_contryid locations.country_id%TYPE := 'CA';

v_loc_id locations.location_id%TYPE;

v_new_city locations.city%TPYE := 'Montreal';

v_counter NUMBER :=1;

BEGIN

SELECT MAX(location_id) INTO v_loc_id FROM locations

WHERE country_id = v_contryid;

WHILE v_counter <= 3 LOOP

INSERT INTO locations(location_id,city,country_id)

VALUES((v_loc_id + v_counter),v_new_city,v_countryid);

v_counter := v_counter +1;

END LOOP;

END;

/

?

例：多重循環

…

BEGIN

<<Outer_loop>>

LOOP

v_counter :=??v_counter+1;

EXIT WHEN v_counter>10;

<< Inner_loop>>

LOOP

…

EXIT Outer_loop WHEN total_done = 'YES';

--Leave both loops

EXIT WHEN inner_done = 'Yes';

--Leave inner loop only

…

END LOOP inner_loop;

…

END LOOP Outer_loop;

END;

/

?

CONTINUE語句

例：

DECLARE

v_total SIMPLE_TINTEGER :=0;

BEGIN

FOR i IN 1..10 LOOP

v_total := v_total +1;

DBMS_OUTPUT.PUT_LINE('Total is: ' || v_total);

CONTINUE WHEN i>5;

v_totail := v_total + I;

DBMS_OUTPUT.PUT_LINE('out of Loop Total is: ' || v_total);

END LOOP;

END;

/

?

例：使用標簽跳轉到外層循環

DECLARE

v_total NUMBER :=0;

BEGIN

<<BeforeTopLoop>>

FOR i IN 1..10 LOOP

v_total := v_total +1;

DBMS_OUTPUT.PUT_LINE('Total is: ' || v_total);

FOR j IN 1..10 LOOP

CONTINUE BeforeTopLoop WHEN i+j>5;

v_total :=v_total +1;

END LOOP;

END LOOP;

END two_loop;

?

程序員的基礎教程：菜鳥程序員

總結

以上是生活随笔為你收集整理的PL/SQL控制结构的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。