Conditional statements acquiesce stored procedures to accomplish decisions. They are acclimated to ascertain assorted branches of beheading based on whether or not a action was met.
A frequently acclimated codicillary account is the IF statement, area a annex of beheading can be taken if a specific action is satisfied. IF statements can additionally ascertain a annex of beheading for back a action is not met.
Another codicillary account in SQL PL is the CASE statement, which is agnate to an IF statement, but the aberration accommodation can be based on the value of a distinct variable.
The best frequently acclimated access for codicillary beheading is the IF statement. There are about three altered types of IF statements.
The simplest anatomy of the IF account does article if a action is true, and annihilation otherwise.
But what happens if you appetite to do one affair if a action is accurate and article abroad if it is false? This is area the ELSE article comes in handy. Back acclimated in affiliation with an IF statement, you can do article IF a action is accurate and article ELSE if the action is false.
Thirdly, ELSEIF is acclimated to annex to assorted cipher paths based on mutually absolute altitude in the aforementioned address as an IF statement. You can accomplish use of an ELSEIF account to carbon a ladder of nested IF … ELSE statements for readability. Your action can specify an absolute cardinal of ELSEIF statements.
The syntax of an IF account is depicted in Figure 3.7.
The search-condition specifies the action for which an SQL account should be invoked. If the action is false, processing continues to the aing search-condition, until either a action is accurate or processing alcove the ELSE clause.
SQL-procedure-statement specifies the statements to be invoked if the above-mentioned search-condition is true. If no search-condition evaluates to true, afresh the SQL-procedure-statement afterward the ELSE keyword is invoked.
The atom of an SQL action apparent in Figure 3.8 demonstrates how the appraisement of an agent determines the accession in bacon and benefit that he or she will receive.
SQL PL does not crave the use of a admixture account to assassinate added than one account in a annex of a codicillary statement.
Indent statements aural the anatomy of IF, ELSEIF, and ELSE statements in order to advance readability. If there are several levels of nesting, bash cipher at anniversary akin to reflect their akin of nesting.
At (1), an agent with a 1 appraisement can apprehend a accession of 10% and a benefit of $1000. At (2), an agent with a 2 appraisement earns a 5% accession with a $500 bonus. At (3), all added advisers can apprehend a 3% pay backpack with no bonus.
You are not artlessly bound to algebraic operators such as equals (=) and greater than (>). You can additionally use the SQL keywords NOT, AND, and OR to build altitude in your IF statements.
An IF or ELSEIF action charge absorb an operator. It is not acceptable to alone specify a capricious (as can be done in some added programming languages), as SQL PL does not abutment the angle of a abrogating amount acceptation apocryphal and a absolute amount acceptation true.
Your stored action can additionally accomplish use of nested IF statements. There is no absolute imposed by DB2 on the cardinal of nested levels, admitting it is best not to get too agitated abroad as it takes abroad from the readability of your code. Now if that’s not enough, you can additionally backup IF statements central of loops and loops central of IF statements. Back nesting IFs, a accepted botheration is aback analogous an ELSE with the amiss IF. Beware.
3.3.2 The CASE Account
The CASE account provides the adeptness to appraise a account of options based on the amount of a distinct variable. You would best acceptable accept to use a CASE account if you accept a ample accommodation timberline and all branches depend on the value of the aforementioned variable. Contrarily you would be bigger off application a alternation of IF, ELSEIF, and ELSE statements. The syntax diagram for the CASE account is shown in Figure 3.9.
The CASE account has two accepted forms: one that uses a simple-case-statement-when-clause, and one that uses a searched-case-statement-when-clause.
In the simple-case-statement-when-clause, the announcement prior to the aboriginal WHEN keyword is activated for adequation with the amount of each announcement that follows the WHEN keyword. If the expressions aftereffect in the aforementioned value, the SQL procedure-statement afterward the THEN keyword is executed. Otherwise, comparisons are connected amid the first announcement and the announcement afterward the aing WHEN clause. If the aftereffect does not bout any of the chase conditions, and an ELSE article is present, the statements in the ELSE article are processed.
In a searched-case-statement-when-clause, the search-condition afterward anniversary WHEN keyword is evaluated. If search-condition evaluates to true, the statements in the associated THEN article are processed. If it evaluates to false, the aing search-condition is evaluated. If no search-condition evaluates to accurate and an ELSE article is present, the statements in the ELSE article are processed.
Both forms of the CASE account crave END CASE to denote the end of the statement. It is accessible to use a CASE account after an ELSE clause. However, if none of the altitude defined in the WHEN article are accurate at run-time, an absurdity will result(SQLSTATE 20000).
The archetype that you accept already apparent in Figure 3.8 could be rewritten as apparent in Figure 3.10 application the simple-case-statement-when-clause.
Once again, at (1), an agent with a appraisement of 1 can apprehend a accession of 10% and a benefit of $1000. At (2), an agent with a appraisement of 2 earns a 5% raise and a benefit of $500, while at (3), all added advisers can artlessly apprehend a raise of 3% and no bonus.
Perhaps there accept been some contempo changes to the appraisement system, and there is now a added ambit of ratings that advisers can receive. Now, two employees with hardly altered ratings can acquire the aforementioned accession and bonus. Obviously, our cipher needs to be updated.
Figure 3.11 reflects the changes to the appraisement arrangement and shows how to handle this application a searched-case-statement-when-clause. Note that the WHEN article now contains a condition.
As you can see, our cipher now handles a ambit of ratings for anniversary action of the CASE statement. For example, at (1), an agent with a appraisement that falls amid 1 and 3 across-the-board will accept a accession of 10% and a benefit of $1000.
Attending Ladder Diagram Tutorial Can Be A Disaster If You Forget These Five Rules | Ladder Diagram Tutorial – ladder diagram tutorial
| Allowed to my own website, on this time I’ll demonstrate about ladder diagram tutorial