Control structure, PL-SQL Programming

Assignment Help:

Control Structures

The Control structures are the most important PL/SQL extension to the SQL. Not only does PL/SQL let you manipulate Oracle data, it lets you process the data using iterative, conditional, and sequential flow-of-control statements like IF-THEN-ELSE, WHILE-LOOP, FOR-LOOP, EXIT-WHEN, and GOTO. Together, these statements can handle any situation.

Conditional Control

Frequently, it is necessary to take alternative actions depending on the circumstances. The IF THEN-ELSE    statement executes a sequence of statements conditionally. The IF  clause checks the condition; the THEN clause defines what to do if the condition is true; the ELSE clause defines what to do when the condition is false or null.

Consider the program below, that process a bank transaction. Before permitting you to withdraw $500 from account 3, it makes sure that the account has sufficient funds to cover the withdrawal. If the fund is available, the program debit the account. If not, the program inserts a record into an audit table.

-- available online in file 'examp2'

DECLARE

acct_balance NUMBER(11,2);

acct CONSTANT NUMBER(4) := 3;

debit_amt CONSTANT NUMBER(5,2) := 500.00;

BEGIN

SELECT bal INTO acct_balance FROM accounts

WHERE account_id = acct

FOR UPDATE OF bal;

IF acct_balance >= debit_amt THEN

UPDATE accounts SET bal = bal - debit_amt

WHERE account_id = acct;

ELSE

INSERT INTO temp VALUES

(acct, acct_balance, 'Insufficient funds');

-- insert account, current balance, and message

END IF;

COMMIT;

END;

A sequence of statements that uses query results to select an alternative action is common in database applications. Another common sequence inserts/deletes a row only if an associated entry is found in other table. You can pack these common sequences into a PL/SQL block using conditional logic. This can improve the performance and simplify the integrity checks built into Oracle Forms applications.

Iterative Control

The LOOP statements execute a sequence of statements multiple times. You put the keyword LOOP  before the first statement in the sequence and the keywords END LOOP  after the last statement in the sequence. The example below shows the simplest kind of loop, that repeats a sequence of statements repeatedly:

LOOP

-- sequence of statements

END LOOP;

The FOR-LOOP statement specifies a range of integers, after that execute a sequence of statements once for every integer in the range. For e.g., assume that you are a producer of custom-made cars and that each car has a serial number. To keep the track of which customer buys each car, you might use the FOR loop as shown:

FOR i IN 1..order_qty LOOP

UPDATE sales SET custno = customer_id

WHERE serial_num = serial_num_seq.NEXTVAL;

END LOOP;

The WHILE-LOOP statement associates a condition with a series of statements. Before every iteration of the loop, the condition is calculated. When the condition is true, the chain of statements is executed, afterward control resumes at the top of the loop. And if the condition is false or null, the loop is bypassed and control passes to the next statement.

In the example below, you find the first employee who has a salary over $4000 and is higher in the chain of the command than employee 7902:

-- available online in file 'examp3'

DECLARE

salary emp.sal%TYPE;

mgr_num emp.mgr%TYPE;

last_name emp.ename%TYPE;

starting_empno CONSTANT NUMBER(4) := 7902;

BEGIN

SELECT sal, mgr INTO salary, mgr_num FROM emp

WHERE empno = starting_empno;

WHILE salary < 4000 LOOP

SELECT sal, mgr, ename INTO salary, mgr_num, last_name

FROM emp WHERE empno = mgr_num;

END LOOP;

INSERT INTO temp VALUES (NULL, salary, last_name);

COMMIT;

END;

The EXIT-WHEN statement completes a loop if further processing is not possible or undesirable. When the EXIT statement is encountered, the condition in the WHEN clause is checked. If the condition is true, the loop completes and control passes to the next statement. In the example below, the loop completes when the value of total exceeds 25,000:

LOOP

...

total := total + salary;

EXIT WHEN total > 25000; -- exit loop if condition is true

END LOOP;

-- control resumes here

Sequential Control

The GOTO statement branch to an unconditionally label. The label, an undeclared identifier enclosed by double angle brackets, should precede an executable statement or a PL/SQL block. If executed, the GOTO statement transfers the control to the labeled statement or block, as shown:

IF rating > 90 THEN

GOTO calc_raise; -- branch to label

END IF;

...

<>

IF job_title = 'SALESMAN' THEN -- control resumes here

amount := commission * 0.25;

ELSE

amount := salary * 0.10;

END IF;


Related Discussions:- Control structure

I need sql to infopath data connection, I need SQL to infopath data connect...

I need SQL to infopath data connection Project Description: Want data retrieval connection from SQL to SharePoint infopath Skills required are Sharepoint, SQL

Pl/sql engine, Architecture The PL/SQL run-time system and compilation ...

Architecture The PL/SQL run-time system and compilation is a technology, not an independent product. Consider this technology as an engine that compiles and executes the PL/SQL

Example of cast operator - sql, Example of Cast Operator So long as C...

Example of Cast Operator So long as CAST is used as shown, we could obtain the total marks for each exam in similar fashion, using SUM (Mark) AS TotalMarks. However, this giv

%isopen - implicit cursor attributes, %ISOPEN The Oracle closes the SQ...

%ISOPEN The Oracle closes the SQL cursor automatically after executing its related SQL statement. As a result, the %ISOPEN forever yields FALSE.

Left and right joins, Left and Right Joins LEFT OUTER JOIN can be used...

Left and Right Joins LEFT OUTER JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3...

Keyword and parameter description - cursors, Keyword and Parameter Descript...

Keyword and Parameter Description select_statement: This is a query which returns a result set of the rows. Its syntax is such that of select_ into_statement without the IN

Natural join - sql, Natural Join - SQL In the absence of NATURAL JOIN...

Natural Join - SQL In the absence of NATURAL JOIN Example has to be replaced by something rather more longwinded, as shown in Example. Example: Joining IS_CALLED and IS_EN

Sql database, SQL Database: So, an SQL database is one whose symbols a...

SQL Database: So, an SQL database is one whose symbols are organized into a collection of tables. Now, shows an SQL table as the current value of an SQL variable, ENROLMENT, b

Parameter and keyword description - sql cursor, Parameter and Keyword Descr...

Parameter and Keyword Description: SQL: This SQL is the name of the implicit SQL cursor. %FOUND: This attribute results TRUE if an INSERT, DELETE, or UPDATE state

Creating a sql file, Creating a SQL file 1. Open a new file in Notepad...

Creating a SQL file 1. Open a new file in Notepad++ and save it to the location c:\mysql\bin, with the name lab8script.sql (the file extension should be .sql ). Add a MySQL co

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