plsql--迴圈討論

PL/SQL提供3種不同的迴圈結構

1. LOOP迴圈

2. FOR迴圈

3. WHILE迴圈

每種迴圈被設計出來都有其特別的用意

最簡單的LOOP迴圈例子

CREATE OR REPLACE PROCEDURE dbtest_loop (

start_number IN integer,

end_number IN integer

)

IS

current_number integer := start_number;--注意這裡一定要先申明變數並且指定型別

BEGIN

LOOP

exit when current_number > end_number;

DBMS_OUTPUT.put_line ('current_number is '||current_number);

current_number := current_number + 1;――這裡要指定步長

END LOOP;

END dbtest_loop;

FOR迴圈例子:

/* Formatted on 2008/12/11 09:40 (Formatter Plus v4.8.6) */

CREATE OR REPLACE PROCEDURE dbtest_forloop (

start_number IN integer,

end_number IN integer

)

IS

BEGIN

FOR current_number IN start_number .. end_number—―――――注意這裡的current_number在使用前不需要申明

LOOP

DBMS_OUTPUT.put_line ('current_number is '||current_number);

END LOOP;

END dbtest_forloop;

注意FOR迴圈還有另外一種結構，如下例

PROCEDURE display_multiple_years (

start_year_in IN PLS_INTEGER

,end_year_in IN PLS_INTEGER

)

IS

BEGIN

FOR l_current_year IN (

SELECT * FROM sales_data

WHERE year BETWEEN start_year_in AND end_year_in)

LOOP

-- This procedure is now accepted a record implicitly declared

-- to be of type sales_data%ROWTYPE...

display_total_sales (l_current_year);

END LOOP;

END display_multiple_years;

第3種迴圈結構就是WHILE迴圈，這種結構與簡單的LOOP迴圈很相似，關鍵的區別在於它在迴圈前檢查迴圈終止條件，它有可能一次也不執行迴圈休中的內容

WHILE迴圈例子如下：

CREATE OR REPLACE PROCEDURE display_multiple_years (

start_year_in IN PLS_INTEGER

,end_year_in IN PLS_INTEGER

)

IS

l_current_year PLS_INTEGER := start_year_in;

BEGIN

WHILE (l_current_year <= end_year_in)

LOOP

display_total_sales (l_current_year);

l_current_year := l_current_year + 1;

END LOOP;

END display_multiple_years;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15720542/viewspace-617114/，如需轉載，請註明出處，否則將追究法律責任。