CONTROL STRUCTURE STATEMENT IN PL/SQL

INTRODUCTION:

According to the structure theorem, any computer program can be written using the basic control structures shown below. They can be combined in any way necessary to deal with a given problem.

Control Structures

The selection structure tests a condition, then executes one sequence of statements instead of another, depending on whether the condition is true or false. Acondition is any variable or expression that returns a Boolean value (TRUE or FALSE). The iteration structure executes a sequence of statements repeatedly as long as a condition holds true. The sequence structure simply executes a sequence of statements in the order in which they occur.

CONDITIONAL CONTROL: IF AND CASE STATEMENTS

Often, it is necessary to take alternative actions depending on circumstances. The IF statement lets you execute a sequence of statements conditionally. That is, whether the sequence is executed or not depends on the value of a condition. There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF. The CASE statement is a compact way to evaluate a single condition and choose between many alternative actions.

 

1) IF-THEN Statement

The simplest form of IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF (not ENDIF), as follows:

IF condition THEN

   sequence_of_statements

END IF;

The sequence of statements is executed only if the condition is true. If the condition is false or null, the IF statement does nothing. In either case, control passes to the next statement.

 

2). IF-THEN-ELSE Statement

The second form of IF statement adds the keyword ELSE followed by an alternative sequence of statements, as follows:

IF condition THEN

   sequence_of_statements1

ELSE

   sequence_of_statements2

END IF;

The sequence of statements in the ELSE clause is executed only if the condition is false or null. Thus, the ELSE clause ensures that a sequence of statements is executed. In the following example, the first UPDATE statement is executed when the condition is true, but the second UPDATE statement is executed when the condition is false or null.

 

3).IF-THEN-ELSIF Statement

Sometimes user want to select an action from several mutually exclusive alternatives. The third form of IF statement uses the keyword ELSIF (not ELSEIF) to introduce additional conditions, as follows:

IF condition1 THEN

   sequence_of_statements1

ELSIF condition2 THEN

   sequence_of_statements2

ELSE

   sequence_of_statements3

END IF;

4).CASE Statement

Like the IF statement, the CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions.  To compare theIF and CASE statements, consider the following code that outputs descriptions of school grades:

IF grade = 'A' THEN

   dbms_output.put_line('Excellent');

ELSIF grade = 'B' THEN

   dbms_output.put_line('Very Good');

ELSIF grade = 'C' THEN

   dbms_output.put_line('Good');

ELSIF grade = 'D' THEN

   dbms_output. put_line('Fair');

ELSIF grade = 'F' THEN

   dbms_output.put_line('Poor');

ELSE

   dbms_output.put_line('No such grade');

END IF;

ITERATIVE CONTROL: LOOP AND EXIT STATEMENTS

LOOP statements let you execute a sequence of statements multiple times. There are three forms of LOOP statements: LOOP, WHILE-LOOP, and FOR-LOOP.

LOOP

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:

LOOP

   sequence_of_statements

END LOOP;

With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop. If further processing is undesirable or impossible, you can use an EXIT statement to complete the loop. You can place one or more EXIT statements anywhere inside a loop, but nowhere outside a loop. There are two forms of EXIT statements: EXIT and EXIT-WHEN.

 

EXIT

The EXIT statement forces a loop to complete unconditionally. When an EXIT statement is encountered, the loop completes immediately and control passes to the next statement. An example follows:

LOOP

   ...

   IF credit_rating < 3 THEN

      ...

      EXIT;  -- exit loop immediately

   END IF;

END LOOP;

WHILE-LOOP

The WHILE-LOOP statement associates a condition with a sequence of statements enclosed by the keywords LOOP and END LOOP, as follows:

WHILE condition LOOP

   sequence_of_statements

END LOOP;

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. An example follows:

WHILE total <= 25000 LOOP

   ...

   SELECT sal INTO salary FROM emp WHERE ...

   total := total + salary;

END LOOP;

 

FOR-LOOP

Whereas the number of iterations through a WHILE loop is unknown until the loop completes, the number of iterations through a FOR loop is known before the loop is entered. FOR loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP. A double dot (..) serves as the range operator. The syntax follows:

FOR counter IN [REVERSE] lower_bound..higher_bound LOOP

   sequence_of_statements

END LOOP;

The range is evaluated when the FOR loop is first entered and is never re-evaluated.

As the next example shows, the sequence of statements is executed once for each integer in the range. After each iteration, the loop counter is incremented.

FOR i IN 1..3 LOOP  -- assign the values 1,2,3 to i

   sequence_of_statements  -- executes three times

END LOOP;

COURSORS IN PL/SQL

INTRODUCTION

A cursor is a special construct used in procedural SQL to hold the data rows returned by an SQL query. All of the SQL statements we have used inside a PL/SQL block have returned a single value. If the SQL statement returns more than one value, Oracle returns the predefined exception TOO_MANY_ROWS. If we want to use SQL statement that returns more than one value inside our PL/SQL code, we need to use a cursor. We can think of a cursor as a reserved area of memory in which the output of the query is stored, like an array holding columns & rows. Cursors are held in reserved memory are in the DBMS server, not in the client computer.

TYPES OF CURSORS

Cursors are classified depending on the circumstances under which they are opened. Two of the most common types used in oracle are-

Ø  Implicit Cursors

Ø  Explicit Cursors

Ø  Implicit cursor: An implicit cursor is automatically created in procedural SQL  when the SQL  statement return only one value.

Cursor Attributes

NAME	DESCRIPTION
%found %notfound %rowcount %isopen	Returns TRUE if record was fetched successfully, FALSE otherwise. Returns TRUE if record was not fetched successfully, FALSE otherwise. Returns number of records fetched from cursor at that point in time. Returns TRUE if cursor is open, FALSE otherwise.

·         SQL%FOUND: It is used to determine if any rows were retrieved. This attribute will return TRUE if an INSERT, UPDATE, DELETE statement affects one or more rows or a SELECT INTO statement  returns one or more rows, otherwise it returns FALSE.

SQL%NOTFOUND- This attribute is opposite to the SQL%FOUND. It returns TRUE if no rows are found otherwise it return FALSE

·         SQL%ROWCOUNT- This attribute is used to determine the number of rows affected by INSERT, UPDATE or DELETE or SELECT INTO statement.

·         SQL%ISOPEN- This attribute always returns FALSE because implicit cursors are opened and closed implicitly before you can reference SQL%ISOPEN to check their status.

EXPLICIT CURSOR: An Explicit cursor is created to hold the output of an SQL statement that may return two or more rows.

PROGRAMS ON CURSORS

PROGRAM: Write  a PL/SQL to Retrieve the number of seats in the room identified by ID 99999 and store the result in V_Number seats.

OBJECTIVE: the objective of this program is to retrieve the number of seats which is identified by ID 9999

CONCLUSION: The conclusion is numer of seats retrieve i.e 1000 is retrieve.

PROGRAM:- Write a Pl/SQl code to upadate the salary of mangaer to twice in emp table

OBJECTIVE:- The objective this program is to update the salary of manager twice using implicit cursors.

CONCLUSION: This program concludes that the salary of an manager is updated twice.

PROGRAM:-  A manager decides to raise the sal of the employees by 0.15%. Write a PL/SQL code block to accept empno from user and update the salary of employee. Also if employee doesnot exist, then display the message.(Use %FOUND and %NOTFOUND)

CONCLUSION:- This program conclude that the salary of an  employees are raised by .15%.

PROGRAM:- A manager decides to raise the salary of employees in dept 20 by 0.15. Write a PL/SQL code to update salary. Also if employee doesn’t exist, display the message else print the number of rows affected. (Use SQL%ROWCOUNT

CONCLUSION:-  This program concludes that slary of an employees which ar of dept 20 raised their salary by .15%.

PROGRAM:-  Write a PL/SQL Code in which manager has decided to raise the salaries of all the employees in dept 20 by 0.05. Whenever such a raise is given to the employee, a record for the same is maintained in table emp_raise (empno,raise_sal,raise_date).

CONCLUSION:- This program concludes that salaries of all employees are raised by 0.05% which are of dept number 20.

PROGRAM:- Write a PL/SQL code to Create a Cursor without parameters to find  employee salary.

OBJECTIVE:- Objective of this program is to create a cursor without parameters to retrieve employee salary.  

OUTPUT:

CONCLUSION:  This program concludes that retrieve the employee salary by using explicit cursor.

PROGRAM:- Write a PL/SQL  code to Close a cursor and open it  in rooms table where building is 7 and number of seats is greater than 100

OUTPUT:-

CONCLUSION:-  This program concludes that the number of seats and building are retrieve which number of eats of aroom are large than 100.

PROGRAM:-  Write a PL/ SQl code that  Fetches the records from a cursor variable that returns employee information

OBJECTIVE:- The objective of this program is to fetch the employee information

OUTPUT:-

                 

CONCLUSION:  This program concludes is to fetch employee information.

Program:

Title: Write a PL/SQl code that create trigger t_trigger AFTER updating  on Table t for  each row

Objective: Create trigger and upadating a table for each row.

Conclusion:- This program concludes that trigger t  has been created and update a table in each row.

Program:-

Title:- Write a PL/SQL code to CREATE OR REPLACE TRIGGER on employee/staff/employee100 that    find username of employee performing UPDATE into table.

Objective: Create trigger that show emp username.

Conclution:- This program concludes that a trigger ‘EMPLOYEE’ has been created.

Program:

Title: Write a PL/SQL code to that Find username of person performing the DELETE on the employee table.

Objective: create a trigger with delete on emp table.

Conclution:- This program of trigger concludes that it Find username of person performing the DELETE on the employee table.

Program:-

Title: Write a PL/SQL code to CREATE OR REPLACE TRIGGER on employee/staff/employee100 that    find username of employee performing Insert into table.

Objective:- Trigger with update on emp;

Conclusion:- After executing this program,it concludes thatCREATE OR REPLACE TRIGGER onemployee/staff/employee100that find username of employeeperforming Insert intotable.