plsql學習筆記（條件語句和循環語句篇）

條件語句

from http://en.wikipedia.org/wiki/PL/SQL

IF x = 1 THEN

   sequence_of_statements_1;

ELSIF x = 2 THEN

   sequence_of_statements_2;

ELSIF x = 3 THEN

   sequence_of_statements_3;

ELSIF x = 4 THEN

   sequence_of_statements_4;

ELSIF x = 5 THEN

   sequence_of_statements_5;

ELSE

   sequence_of_statements_N;

END IF;

CASE

   WHEN x = 1 THEN sequence_of_statements_1;

   WHEN x = 2 THEN sequence_of_statements_2;

   WHEN x = 3 THEN sequence_of_statements_3;

   WHEN x = 4 THEN sequence_of_statements_4;

   WHEN x = 5 THEN sequence_of_statements_5;

   ELSE sequence_of_statements_N;

END CASE;

CASE x

   WHEN 1 THEN sequence_of_statements_1;

   WHEN 2 THEN sequence_of_statements_2;

   WHEN 3 THEN sequence_of_statements_3;

   WHEN 4 THEN sequence_of_statements_4;

   WHEN 5 THEN sequence_of_statements_5;

   ELSE sequence_of_statements_N;

END CASE;

循環語句 from http://www.adp-gmbh.ch/ora/plsql/loops.html Types of loops Basic loop loop /* statements */ end loop; While loop while a > b loop /* statements */ end loop See also  Iterating over collection variables. For loop for i in 1..1000 loop insert into a values(i,i*2); end loop; for i in reverse 1..1000 loop insert into a values(i,i*2); end loop; See also  Iterating over collection variables. Cursor for loop for rec in (select col_1, col_2 from table_a) loop /*Statements, use rec.col_1 and rec.col_2 */ end loop; for rec in cursor_name loop /*Statements, use rec.col_1 and rec.col_2 */ end loop; for rec in cursor_name(cursor_param_1, cursor_param_2...) loop /*Statements, use rec.col_1 and rec.col_2 */ end loop; Labels Each of the loops can be labeled: <<label_name>> loop .... end loop label_name; When a loop is labeled, the exit statement can then refer to that label: begin <<i_loop>> for i in 1 .. 10 loop <<j_loop>> for j in 1 .. 10 loop dbms_output.put(to_char(j, '999')); exit j_loop when j=i; end loop; dbms_output.new_line; end loop; end; / 1 1 2 1 2 3 1 2 3 4 1 2 3 4 5 1 2 3 4 5 6 1 2 3 4 5 6 7 1 2 3 4 5 6 7 8 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7 8 9 10 exit exit; exit when foo > bar; exit label_name; exit label_name when foo > bar; Any of these loops can be exited with the  exit  statement.