Already have an account? Get multiple benefits of using own account!
Login in your account..!
Remember me
Don't have an account? Create your account in less than a minutes,
Forgot password? how can I recover my password now!
Enter right registered email to receive password!
Pl/sql Conditional Control: IF statementsFrequently, 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-THENThe 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 THENsequence_of_statementsEND 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 THENcompute_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 inIF x > y THEN high := x; END IF;IF-THEN-ELSEThe IF statement that is the second form adds the keyword ELSE follow by an alternative series of statements is as shown below:IF condition THENsequence_of_statements1ELSEsequence_of_statements2END 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’ THENUPDATE accounts SET balance = balance + credit WHERE...ELSEUPDATE 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’ THENUPDATE accounts SET balance = balance + credit WHERE...ELSEIF new_balance >= minimum_balance THENUPDATE accounts SET balance = balance - debit WHERE ...ELSERAISE insufficient_funds;END IF;END IF;IF-THEN-ELSIFAt 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 THENsequence_of_statements1ELSIF condition2 THENsequence_of_statements2ELSEsequence_of_statements3END 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 THENbonus := 1500;ELSIF sales > 35000 THENbonus := 500;ELSEbonus := 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.
Expressions An expression is a randomly complex combination of the constants, variables, literals, operators, & function calls. The simplest expression is the single variabl
EXECUTE Privilege To call an invoker-rights routine straightforwardly, the users should have the EXECUTE privilege on that routine. By yielding the privilege, you permit a user
Dynamic Ranges The PL/SQL lets you determine the loop range dynamically at run time, as the example below shows: SELECT COUNT(empno) INTO emp_count FROM emp; FOR i IN 1..emp_cou
Declaring and Initializing Objects: An object type is once defined and installed in the schema; you can use it to declare the objects in any PL/SQL, subprogram, block or packa
Advantages of PL/SQL The PL/SQL is a high-performance transaction processing, completely portable language that offers the following advantages as shown: 1) Support for SQL
Accessing Attributes: You can refer to an attribute only by its name not by its position in the object type. To access or modify the value of an attribute, you can use the dot
Named Notation The second procedure call uses the named notation. An arrow (=>) serve as the relationship operator that associates the formal parameter to the left of the arro
Truth Tables: However in propositional logic - here we are restricted to expressing sentences and where the propositions are true or false - so we can check where a particular
INSERT Command in SQL Loosely speaking, INSERT takes the rows of a given source table and adds them to the specified target table, retaining all the existing rows in the targ
DBMS_OUTPUT: The Package DBMS_OUTPUT enables you to display output from the PL/SQL subprograms and blocks, that makes it easier to test and debug them. The procedure put_ line
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!
whatsapp: +91-977-207-8620
Phone: +91-977-207-8620
Email: [email protected]
All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd