Executable Commands Section

Executable Commands Section

The Executable commands section starts with the keyword begin.It consists of SQL and PL/SQl executable statements.It may also contain flow control and looping statements,cursoe execution commands etc.
Eg;
Declare
pi constant number(7,2):=3.14;
rad number(4)
area number(7,2)
Begin
For i in 1 to 4
Loop
area:=pi*power(rad,2)
insert into areas values(rad,area);
End Loop;
end;

Inserting Comments in a PL/SQL Block

PL/SQL supports two comment styles: single-line and multi-line.
Single line commenting:Begin the comment with -- (two hyphens). End the comment with a line break.
Multi line commenting:Begin and end the comment with a slash and an asterisk (/*).
Eg:
Declare
--declaring variables
pi constant number(7,2):=3.14;
rad number(4);
area number(7,2);
Begin
/* The following line computes the area of a circle using pi, which
is the ratio between the circumference and diameter. */
area := pi *power(radius,2);
End;

Substitution Variable

A substitution variable is a user variable name preceded by one or two ampersands (&). The substitution variable is used for accepting a value from keyboard.You can assign values to a variable using substitution variable.
Eg: rad:=&radius;

Dispalying Messages

DBMS_OUTPUT:The DBMS_OUTPUT package enables you to display messages on the screen.
There are two procedures under this package that is put_line and put:
PUT_LINE:You can place an entire line of information into the buffer by calling PUT_LINE.When you call PUT_LINE, the item that you specify is automatically followed by an end-of-line marker.
To display messages to the user the SERVEROUTPUT should be set to ON.SERVEROUTPUT is a SQLPlus parameter.
Syntax:
SQL>Set Serverouput on;

Flow Control Statements
flow-of-control statements such as IF-THEN-ELSE, FOR-LOOP, WHILE-LOOP, EXIT-WHEN, and GOTO are supported by PL/SQL. Collectively, these statements can handle any situation.

Conditional Control Statement

The IF-THEN-ELSE statement lets you execute a sequence of statements conditionally. The IF clause checks a condition; the THEN clause defines what to do if the condition is true; the ELSE clause defines what to do if the condition is false or null.

The Syntax of IF clause is given below.

IF
THEN
ELSE

END IF;

Nested IF Statements:
You can nest if conditions within each other.
Syntax:
IF
then
IF
then
end if;
ELSE
end if;

Use of ELSEIF clause:

ELSEIF clause can be used instead of nested if clause.
That way, your code will be easier to read and understand. Compare the following IF statements:

IF condition1 THEN IF condition1 THEN
statement1; statement1;
ELSE ELSIF condition2 THEN
IF condition2 THEN statement2;
statement2; ELSIF condition3 THEN
ELSE statement3;
IF condition3 THEN END IF;
statement3;
END IF;
END IF;
END IF;

Sample program
Consider the program below, which processes a bank transaction. Before allowing you to withdraw 500 from account 3, it makes sure the account has sufficient funds to cover the withdrawal. If the funds are available, the program debits the account. Otherwise, the program inserts a record into an audit table.

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;

NULL Statement

The NULL statement explicitly specifies inaction; it does nothing other than pass control to the next statement. It can, however, improve readability. In a construct allowing alternative actions, the NULL statement serves as a placeholder. It tells readers that the associated alternative has not been overlooked, but that indeed no action is necessary. In the following example, the NULL statement shows that no action is taken for unnamed exceptions:
EXCEPTION
WHEN ZERO_DIVIDE THEN
ROLLBACK;
WHEN VALUE_ERROR THEN
INSERT INTO errors VALUES ...
COMMIT;
WHEN OTHERS THEN
NULL;
END;

Each clause in an IF statement must contain at least one executable statement. The NULL statement is executable, so you can use it in clauses that correspond to circumstances in which no action is taken.

Iterative Control

LOOP statements let you execute a sequence of statements multiple times. You place the keyword LOOP before the first statement in the sequence and the keywords END LOOP after the last statement in the sequence. The following example shows the simplest kind of loop, which repeats a sequence of statements continually:

Simple Loop statement

The simplest form of LOOP statement is the basic (or infinite) loop, which encloses a sequence of statements between the keywords LOOP and END LOOP, as follows
Syntax:
LOOP
sequence of statements
END LOOP;

The EXIT-WHEN statement lets you complete a loop if further processing is impossible or undesirable. When the EXIT statement is encountered, the condition in the WHEN clause is evaluated. If the condition is true, the loop completes and control passes to the next statement. In the following example, the loop completes when the value of rad exceeds 10:
LOOP
...
area := pi*power(rad,2)

EXIT WHEN rad > 10; -- exit loop if condition is true
END LOOP;

FOR_LOOP Statement

The FOR-LOOP statement lets you specify a range of integers, then execute a sequence of statements once for each integer in the range.
Syntax:
FOR counter IN [REVERSE] lower_bound..higher_bound
LOOP
sequence_of_statements
END LOOP;


Let us take the example for calculating area for a circle for different values of radius in the range of 1 to 10.(lower_bound =1 and higher_bound=10)

Eg;
DECLARE
pi CONSTANT NUMBER(7,2):=3.14;
rad NUMBER(4)
area NUMBER(7,2)
BEGIN
FOR i IN 1 .. 10
LOOP
area:=pi*power(rad,2)
INSERT INTO AREA VALUES(rad,area);
END LOOP;
END;

By default, iteration proceeds upward from the lower bound to the higher bound. However, as the example below shows, if you use the keyword REVERSE, iteration proceeds downward from the higher bound to the lower bound. After each iteration, the loop counter is decremented.
FOR i IN REVERSE 1..3
LOOP -- assign the values 3,2,1 to i
sequence_of_statements -- executes three times
END LOOP;

Note:Inside a FOR loop, the loop counter can be referenced like a constant but cannot be assigned values to it.

WHILE_LOOP Statement

The WHILE-LOOP statement associates a condition with a sequence of statements. Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement.
Syntax:
WHILE
LOOP
Statements
END LOOP;

Let us take the example for calculating area for a circle for different values of radius in the range of 1 to 10.
Eg;
DECLARE
pi CONSTANT NUMBER(7,2):=3.14;
rad NUMBER(4):=1
area NUMBER(7,2)
BEGIN
WHILE RAD <11
LOOP
area:=pi*power(rad,2)
INSERT INTO AREA VALUES(rad,area);
END LOOP;