Pl/sql conditional control: if statements, PL-SQL Programming

Assignment Help:

Pl/sql Conditional Control: IF statements

Frequently, it is necessary to take the alternative actions depending on the circumstances. The IF statement execute a series of statements conditionally. That is, whether the series is executed or not depends on the value of the condition. There are 3 forms of IF statements: IF-THEN, IF-THEN-ELSE, & IF-THEN-ELSIF.


IF-THEN

The simplest form of the IF statement acquaintances a condition with a series of statements enclosed by the keywords THEN and END IF (not ENDIF), as shown below:


IF condition THEN
sequence_of_statements
END IF;

The series of statements is executed only if the condition is true. When the condition is false or null, then the IF statement can do nothing. In either of the case, the control passes to the next statement. An illustration is shown below:


IF sales > quota THEN
compute_bonus(empid);
UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
END IF;

You may want to place brief IF statements on a single line, as in

IF x > y THEN high := x; END IF;



IF-THEN-ELSE

The IF statement that is the second form adds the keyword ELSE follow by an alternative series of statements is as shown below:


IF condition THEN
sequence_of_statements1
ELSE
sequence_of_statements2
END IF;


The series of statements in the ELSE clause is executed only if the condition is false or null. Therefore, the ELSE clause ensure that a sequence of statements is executed. In the example below, the first UPDATE statement is executed if the condition is true, while  the second UPDATE statement is executed if the condition is false or null:


IF trans_type = ’CR’ THEN
UPDATE accounts SET balance = balance + credit WHERE...
ELSE
UPDATE accounts SET balance = balance - debit WHERE...
END IF;


The THEN and ELSE clauses can involve the IF statements. That is, the IF statements can be nested, as the example below shows:

IF trans_type = ’CR’ THEN
UPDATE accounts SET balance = balance + credit WHERE...
ELSE
IF new_balance >= minimum_balance THEN
UPDATE accounts SET balance = balance - debit WHERE ...
ELSE
RAISE insufficient_funds;
END IF;
END IF;


IF-THEN-ELSIF

At many times you want to select an action from some mutually exclusive alternatives. The third form of the IF statement uses the keyword ELSIF to introduce the additional conditions which is as shown below:

IF condition1 THEN
sequence_of_statements1
ELSIF condition2 THEN
sequence_of_statements2
ELSE
sequence_of_statements3
END IF;


When the first condition is false or null, then the ELSIF clause tests another condition. An IF statement can have a few number of ELSIF clauses; the final ELSE clause is elective. The Conditions are evaluated one by one from top to bottom. When any condition is true, its related sequence of statements is executed and the control passes to the next statement. If all the conditions are false or null, then the sequence in the ELSE clause is executed. Consider the following illustration as shown below:


BEGIN
...
IF sales > 50000 THEN
bonus := 1500;
ELSIF sales > 35000 THEN
bonus := 500;
ELSE
bonus := 100;
END IF;
INSERT INTO payroll VALUES (emp_id, bonus, ...);
END;


When the value of sales is bigger than 50000, the first and second conditions are true.
However, bonus is assigned the proper value of 1500 as the second condition is never tested. When the first condition is true, its related statement is executed and the control passes to the INSERT statement.


Related Discussions:- Pl/sql conditional control: if statements

Collections in pl sql, Collections:   The collection is an ordered gr...

Collections:   The collection is an ordered group of elements, all of similar type (for illustration, the grades for a class of students). Each element has an exclusive subsc

Order of evaluation-pl/sql expressions , Order of Evaluation When you do...

Order of Evaluation When you do not use the parentheses to specify the order of evaluation, the operator precedence determine the order. Now compare the expressions below: NOT

Parameter modes - pl sql, Parameter Modes: You do not require to speci...

Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

Procedure, 1. Create a procedure called TAX_COST_SP to accomplish the tax c...

1. Create a procedure called TAX_COST_SP to accomplish the tax calculation task. Keep in mind that the state and subtotal values are inputs into the procedure and the procedure is

Effects of null for multiple assignments - sql, Effects of NULL for Multipl...

Effects of NULL for Multiple Assignments - SQL If the row expression given as the source for a multiple assignment evaluates to NULL, then NULL is assigned to each target. If

Benefit of the dynamic sql pl sql, Benefit of the dynamic SQL: This pa...

Benefit of the dynamic SQL: This part shows you how to take full benefit of the dynamic SQL and how to keep away from some of the common pitfalls. Passing the Names of Sc

Subprograms, What Are Subprograms? The Subprograms are named PL/SQL blo...

What Are Subprograms? The Subprograms are named PL/SQL blocks which can take parameters and be invoked. The PL/SQL has 2 types of subprograms known as the procedure s and func

Parameter and keyword description - object types, Parameter and Keyword Des...

Parameter and Keyword Description: type_name: This identifies a user-defined type specifier that is used in the subsequent declarations of the objects. AUTHID Clause:

Parameter and keyword description - exit statement, Parameter and Keyword D...

Parameter and Keyword Description: EXIT: An unconditional EXIT statement (i.e., one without a WHEN clause) exits the present loop instantly. The Execution resumes with th

Package dbms pipe in pl/sql, DBMS_PIPE: The Package DBMS_PIPE allows va...

DBMS_PIPE: The Package DBMS_PIPE allows various sessions to communicate over the named pipes. (A pipe is a region of memory used by one of the process to pass information to

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd