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
, WHIL E-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 E XIT-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;
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 100OUTPUT:-
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;
CREATE
OR REPLACE TRIGGER onemployee/staff/ employee100that find username of employeeperforming Insert intotable.
No comments:
Post a Comment