Shulou(Shulou.com)06/01 Report--

Like most programming languages, PL/SQL has structures that control the execution of statements, mainly including:

1. Sequential structure: the program executes statements one by one from top to bottom, which is the sequential structure.

2. Branch condition judgment: branch condition judgment mainly refers to IF statement and CASE sentence.

3. Loop statement: loop structure mainly refers to REPEAT, LOOP and DO WHILE statements.

This chapter mainly explains the usage of conditional judgment sentence.

I. the usage of IF sentences

The basic format of the IF statement is:

IF conditionTHEN

Statements

[ELSIF conditionTHEN

-- Note that there is no ejime ELSIF in the ELSIF here that can appear any number of times.

Statements;]

[ELSE

Statements

]

END IF

The judgment condition after IF can be Boolean value, constant or expression, and the result is TRUE, FALSE or NULL. NULL is unique to PL/SQL and is not common in other programming languages.

If the conditional judgment after IF is TRUE, the statement after THEN is executed; if the result is FALSE and NULL, the result after THEN is not executed, but the statement after ELSE is executed.

Here's a simple example:

SQL > edit

DECLARE v_myage number: = 31 ~ begin IF v_myage

< 11 THEN DBMS_OUTPUT.PUT_LINE(' I am a child'); ELSE DBMS_OUTPUT.PUT_LINE( ' I am not achild! '); -- 由于IF后的条件判断结果为FALSE，所以会执行这条语句 END IF;END;/ SQL>

/

I am not a child!PL/SQL procedure successfullycompleted.

Now add a few more branches for the above example to determine the condition:

SQL > edit

BEGIN IF v_myage

< 11 THEN DBMS_OUTPUT.PUT_LINE(' I am achild '); ELSIF v_myage < 20 THEN DBMS_OUTPUT.PUT_LINE( 'Iam young '); ELSIF v_myage < 30 THEN DBMS_OUTPUT.PUT_LINE( 'Iam in my twenties'); ELSIF v_myage < 40 THEN DBMS_OUTPUT.PUT_LINE( 'I am in mythirties'); -- 由于31大于11，20,30，而小于40，所以执行这条语句 ELSE DBMS_OUTPUT.PUT_LINE( 'Iam always young '); END IF;END;/ SQL>

/

I am in my thirtiesPL/SQL procedure successfullycompleted.

If the result of the IF conditional judgment is NULL, the statement that follows the THEN will not be executed.

SQL > edit

DECLARE v_myage NUMBER;BEGIN-- IF v_myage IS NULL-- the result here is TRUE, because it determines whether the variable v_myage is null, TRUE if it is, or FALSE IF v_myage if not.

< 11 THEN -- 如果判断的条件为NULL值，则结果为NULL THEN DBMS_OUTPUT.PUT_LINE(' I am a child'); ELSE DBMS_OUTPUT.PUT_LINE( ' I am not achild! '); END IF;END;/ SQL>

/

I am not a childPL/SQL procedure successfullycompleted.

The conditions of an IF statement can also be combined using logics such as AND,OR,NOT.

There are some details to pay attention to in the IF conditional judgment statement:

A. IF and END IF must appear in pairs

B. there is a space in the middle of the END IF

C. There is no E in the ELSIF

d. There is a semicolon after END IF ";"

II. The usage of CASE

CASE is actually a variant of the IF statement, and when the IF statement has a large number of similar ELSIF judgment conditions, you can use CASE to simplify. The use of CASE can be divided into two types, one is CASE expression, the other is CASE statement.

(1) CASE expression:

The basic format of an CASE expression is:

CASE selector

WHENexpression1 THEN result1

WHENexpression2 THEN result2

...

WHENexpressionN THEN resultN

[ELSEresultN+1]

END

Where the selector selection value is equal to the value of expression1, result1 is executed, and if equal to expression2, result2... is executed. And so on, if no value of expression can match, resultN+1 is executed.

CASE expressions can be divided into simple CASE and Searched CASE, where simple CASE means that CASE is followed by a selection value, and then the result of the conditional expression is compared with that selection value, as in this example:

SQL > edit

SET VERIFY OFFDECLARE v_grade CHAR (1): = UPPER ('& grade'); v_appraisal VARCHAR2 (20); BEGIN v_appraisal: = CASE v_grade WHEN'A 'THEN' Excellent' WHEN'B' THEN 'Very Good' WHEN' c 'THEN' Good' ELSE'No such grade' END The part from CASE to END is simple CASE, and its running result is assigned to the variable v_appraisal as a value. DBMS_OUTPUT.PUT_LINE ('Grade:' | | v_grade | | 'Appraisal' | | v_appraisal); END

SQL > /

Enter value for grade: aGrade: An Appraisal ExcellentPL/SQL procedure successfullycompleted.

SQL > /

Enter value for grade: sGrade: S Appraisal No suchgradePL/SQL procedure successfullycompleted.

Instead of selecting the value selector after CASE in Searched CASE, it directly determines the Boolean value of the expression after the keyword WHEN, as in the following example:

SQL > edit

DECLARE v_grade CHAR (1): = UPPER ('& grade'); v_appraisal VARCHAR2 (20) BEGIN v_appraisal: = CASE-- the CASE here has no selected value WHEN v_grade ='A 'THEN'Excellent'-- directly completes the return of the Boolean value in the conditional judgment section to WHEN v_grade IN (' bounded Magnum C') THEN'Good' ELSE'No such grade' END DBMS_OUTPUT.PUT_LINE ('Grade:' | | v_grade | | 'Appraisal' | | v_appraisal); END;/

SQL > /

Enter value for grade: AGrade: An Appraisal ExcellentPL/SQL procedure successfullycompleted.

SQL > /

Enter value for grade: oGrade: O Appraisal No such gradePL/SQL procedure successfullycompleted.

SQL > /

Enter value for grade: bGrade: B Appraisal GoodPL/SQL procedure successfullycompleted.

(2) CASE statement

CASE statements are separate statements that cannot assign the results of the run to other variables.

Such as the following example:

SQL > edit

DECLARE 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 count (*) INTO v_emps FROM employees WHERE department_id = v_deptid -- the CASE statement no longer assigns the run result to other variables as a whole. WHEN 200THEN SELECT department_id,department_name INTO v_deptid, v_deptname FROM departments WHERE manager_id = 200 SELECT count (*) INTO v_emps FROM employees WHERE department_id = vdeptid; ELSE SELECT department_id,department_name INTO v_deptid, v_deptname FROM departments WHERE manager_id = 100 SELECT count (*) INTO v_emps FROM employees WHERE department_id = vdependent; END CASE must be in the END CASE;-- CASE statement, but END CASE is not required if it is an CASE expression. DBMS_OUTPUT.PUT_LINE ('You are workingin the' | | v_deptname | | 'department. There are'| | v_emps | | 'employees inthis department'); END;/

SQL > /

You are working in theFinance department. There are 6 employees in thisdepartmentPL/SQL procedure successfullycompleted.

CASE statements are also divided into SimpleCASE statements and Searched CASE statements. The difference between them is the same as the CASE expression. The above example belongs to the use of SimpleCASE. If you want to use the Searched CASE statement, you can write it in the following form:

SQL > edit

DECLARE v_deptid NUMBER; v_deptname VARCHAR2 (20); V_emps NUMBER; v_mngid NUMBER:= 100 BEGIN CASE WHEN v_mngid = 108THEN-- v_mngid is not used as a selection value, but is judged directly after WHEN, SELECT department_id,department_name INTO v_deptid, v_deptname FROM departments WHERE manager_id = 108 SELECT count (*) INTO v_emps FROM employees WHERE department_id = v_deptid WHEN v_mngid = 200 THEN SELECT department_id,department_name INTO v_deptid, v_deptname FROM departments WHERE manager_id = 200 SELECT count (*) INTO v_emps FROM employees WHERE department_id = vdeptid; ELSE SELECT department_id,department_name INTO v_deptid, v_deptname FROM departments WHERE manager_id = 100 SELECT count (*) INTO v_emps FROM employees WHERE department_id = department; END CASE; DBMS_OUTPUT.PUT_LINE ('You are workingin the' | | v_deptname | | department. There are'| | v_emps | | 'employees inthis department'); END;/

SQL > /

You are working in theExecutive department. There are 3 employees in thisdepartmentPL/SQL procedure successfullycompleted.

Third, the treatment of null value

The NULL value has some special properties in PL/SQL:

a. If a NULL value is involved in a simple comparison expression, the final result will be a null value.

b. If NOT NULL appears in the logic operation, the result is still NULL. Only if it is written as IS NULL or IS NOT NULL, can you get the value of TRUE or FALSE.

C. The statement after IF is executed only if the result is TRUE. If the result is NULL or FASLE, it will not be executed.

D. The NULL value can also be a separate statement in PL/SQL to indicate doing nothing, for example:

IF conditions THEN

Dosome thing

ELSE

NULL

The main ones that use NULL statements are:

1. Enhance the readability of the program.

two。 Use NULL after the tag.

The use of tags will be explained in more detail later. Here is a small example:

DECLARE

...

BEGIN

IFconditions THEN

GOTO LastPoint

-- GOTO means that when you get to this step, jump directly to the location of the tag LastPoint.

ENDIF

...

There should be a statement after the tag LastPoint, but we often encounter situations where no action is done after the tag in a production environment.

Using the NULL; statement, you can satisfy the syntax rules without doing anything.

NULL

END

IV. Logical relations in conditional judgment

1. Logical operators AND,OR and NOT

The logical operators AND, OR, and NOT can be used in conditional judgment, and various logical relationships are formed when Boolean values and logical operators are combined:

two。 Short circuit phenomenon

Short-circuit operation refers to the operation mode in which only part of the conditions are judged in logical judgment, but there is no need to judge the whole condition. If the logical operator is AND, after the condition before AND is FALSE, it does not continue to judge the condition after AND; if the logical operator is OR, then after the condition before OR is determined to be true, it does not continue to judge the condition after OR.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.