Oracle PL/SQL basic selection (IF CASE), LOOP WHILE)

1. Control Structure of PL/SQL, including selection structure, loop structure and jump Structure

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)
 
1. Select Structure
 
(1) IF statement

IF condition1 THEN statement1;
[ELSIF condition2 THEN statement2;]
...
[ELSE else_statements;]
End if;

Attention: the logical operation results of PL/SQL include TRUE, FALSE, and NULL. Therefore, when determining the condition selection, consider the condition where the condition result is NULL.

Example:

Create or replace procedure detector_plsql_if (
V_control_type in varchar2)
As
V_pre_print varchar2 (100): = 'value of parameter [in] v_control_type ';
Begin

If v_control_type = 'if' then
Dbms_output.put_line (v_pre_print | '(if):' | v_control_type );
Elsif v_control_type = 'case' then
Dbms_output.put_line (v_pre_print | '(case):' | v_control_type );
Elsif v_control_type is null then
Dbms_output.put_line (v_pre_print | '(null):' | v_control_type );
Else
Dbms_output.put_line (v_pre_print | ':' | v_control_type );
End if;

End detector_plsql_if;

(2) CASE statements

There are two forms of CASE statements: one is equivalent comparison, and the other can compare multiple conditions.

A. Only perform equivalent comparison

CASE test_value
WHEN value1 THEN statement1;
[WHEN value2 THEN statement2;]
...
[ELSE else_statement;]
End case;

Example:

Create or replace procedure detector_plsql_case (
V_control_type in varchar2)
Is
V_pre_print varchar2 (100): = 'value of parameter [in] v_control_type ';
Begin

Case v_control_type
When 'if' then
Dbms_output.put_line (v_pre_print | '(if):' | v_control_type );
When 'case' then
Dbms_output.put_line (v_pre_print | '(case):' | v_control_type );
Else
Dbms_output.put_line (v_pre_print | ':' | v_control_type );
End case;

End detector_plsql_case;

B. Compare multiple conditions

CASE
WHEN condition1 THEN statement1;
[WHEN condition2 THEN statement2;]
...
[ELSE else_statements;]
End case;

The CASE statement is used to determine each WHEN condition. WHEN the condition is true, the following statement is executed. If none of the conditions are true, the statement after ELSE is executed.

Attention: In a CASE statement, WHEN the first WHEN condition is true, the subsequent operation is executed, and the CASE statement is ended after the operation is completed. Other WHEN conditions are no longer judged, the subsequent operation is not executed.

Example:

Create or replace procedure detector_plsql_case2 (
V_control_type in varchar2)
As
V_pre_print varchar2 (100): = 'value of parameter [in] v_control_type ';
Begin

Case
When v_control_type = 'if' then
Dbms_output.put_line (v_pre_print | '(if):' | v_control_type );
When v_control_type = 'case' then
Dbms_output.put_line (v_pre_print | '(case):' | v_control_type );
When v_control_type is null then
Dbms_output.put_line (v_pre_print | '(null):' | v_control_type );
Else
Dbms_output.put_line (v_pre_print | ':' | v_control_type );
End case;

End detector_plsql_case2;

For more details, please continue to read the highlights on the next page:

1 2 Next Page