Oracle's PL/SQL Programming _ Process Control statements

SELECT statement

1. If...then statements

Grammar:

If < condition_expression > Thenplsql_sentenceend if;

Condition_expression: Represents a conditional expression whose value is true when the program executes the PL/SQL statement below if;

If the value is False, the program skips the statement following the IF statement and executes the statements immediately after the end If.

The statement to execute when Plsql_sentence:condition_expression is true.

2. If...then...else statements

Grammar:

If < condition_expression > Thenplsql_sentence_1;elseplsql_sentence_2;end if;

3.if...then...elsif statements

Grammar:

If < Condition_expression1 > Thenplsql_sentence_1;elsif < condition_expression2 > thenplsql_sentence_2; .... Elseplsql_sentence_n;end if;

4. Case statements

Grammar:

Case < selector >when <expression_1> then plsql_sentence_1;when <expression_2> then plsql_sentence_2;.. . when <expression_n> then Plsql_sentence_n; [Else plsql_sentence;] End case;

Selector: A variable used to store the value to be detected, often referred to as a selector.

The value of the selector needs to match the value of the expression in the When clause.

Expression_1: The expression in the first when clause, which is usually a constant, when the value of the selector equals the value of the expression,

The program executes the Plsql_setence_1 statement.

Expression_2: The expression in the second when clause, which is usually a constant, when the value of the selector equals the value of the expression,

The program executes the plsql_setence_2 statement.

Expression_n: The expression in the nth when clause, which is usually a constant, when the value of the selector equals the value of the expression,

The program executes the Plsql_setence_n statement.

Plsql_sentence: A PL/SQL statement that executes the PL/SQL statement when there is no when constant that matches the selector.

The Else statement in which it resides is an optional option.

Cases:

Specify a quarterly value, and then use the case statement to determine the month information it contains and output it.

Code:

Declareseason int:=3;aboutlnfo varchar2 () begincase seasonwhen 1 Thenaboutlnfo: = season| | ' Quarter includes 1, 2, March '; when 2 thenaboutinfo: = season| | ' Quarter includes 4, 5, June '; when 3 thenaboutinfo: = season| | ' Quarter includes 7, 8, September '; when 4 thenaboutinfo: = season| | ' Quarterly includes 10, 11, December '; Elseaboutinfo: = season| | ' The season is illegal '; end Case;dbms_output.put_line (aboutinfo); end;

Results: 3 quarter including 7,8,9 month

Looping statements

1. Loop statement

Grammar:

Loopplsql_sentence;exit when End_condition_expend loop;

plsql_sentence: PL/SQL statements in the loop body. Be executed at least once.

End_condition_exp: loop-end conditional expression, when the expression is true, the program exits the loop body, or the program executes again.

Cases:

Use the Loop statement to calculate the first 100 natural numbers and output to the screen.

Sql> set serveroutput on; sql> declaresun_i int:=0;i int:=0;beginloopi:=i+1;sum_i:=sum_i +1;exit when I =100;--a loop 100 times, the program exits the loop body. End Loop;dbms_output.put_line (' first 100 natural numbers and: ' | | sum_i); end;/

2. While statement

Grammar:

While condition_expression Loopplsql_sentence;end Loop;

Condition_expression: Represents a conditional expression, but when its value is true, the program executes the loop body.

Otherwise, the program exits the loop body, and the program determines whether the expression is true before each execution of the loop body.

Plsql_sentence: The Plsql statement within the loop.

Cases:

Use the While statement to calculate the first 100 natural numbers and output to the screen.

DECLARE sum_i int:=0;i int:=0;beginwhile i<=99 loop i:=i+1; Sum_i:=sum_i+1;end Loop;dbms_output.put_line (' The first 100 natural numbers ' and is: ' | | sum_i); end;/

3. For statement

Grammar:

For variable_counter_name in [reverse] lower_limit. Upper_limit Loopplsql_sentence;end Loop;

Variable_counter_name: Represents a variable, usually an integer type, that is used as a counter.

By default, the value of the counter is incremented, and when you use the reverse keyword in a loop, the value of the counter decrements with the loop.

Lower_limit: The counter lower value, which exits the loop when the value of the counter is less than the lower limit value.

Upper_limit: The counter upper value that exits the loop when the value of the counter is greater than the upper limit value.

Plsql_sentence: The Plsql statement within the loop.

Cases:

Use the For statement to calculate the sum of the first 100 natural numbers and output to the screen.

Declaresum_i int:= 0;beginfor i in reverse 1..100 loopif mod (i,2) =0 then--determine if an even sum_i:=sum_i+i;end if;end LOOP;DBMS_OUTP Ut.put_line (' The first 100 natural numbers are even and: ' | | sum_i); end;/

This article is from the "Learn like riding" blog, please be sure to keep this source http://2226894115.blog.51cto.com/9973734/1956460

Oracle's PL/SQL Programming _ Process Control statements