Parameter and keyword description - loop statements, PL-SQL Programming

Assignment Help:

Parameter and Keyword Description:

label_name:

This is an undeclared identifier which optionally labels a loop. When used, the label_name should be enclosed by double angle brackets and should appear at the starting of the loop. Optionally, the label_name (not enclosed in the angle brackets) can also appear at the ending of the loop.

You can use the label_name in an EXIT statement to exit the loop labelled by the label_name. You can exit not only the present loop, but any enclosing loop.

You cannot position the index of a FOR loop from the nested FOR loop if both indexes have similar name unless the outer loop is labeled by the label_name and you can use the dot notation, which is as shown below:

label_name.index_name

In the illustration below, you can compare the two loop indexes that have similar name, one used by an enclosing loop, and the other by a nested loop:

<>

FOR ctr IN 1..20 LOOP

...

<>

FOR ctr IN 1..10 LOOP

IF outer.ctr > ctr THEN ...

END LOOP inner;

END LOOP outer;

basic_loop_statement:

The simplest form of the LOOP statement is the fundamental (or unlimited) loop that encloses a series of statements between the keywords LOOP and END LOOP. With each of the iteration of loop, the series of statements is executed, then the control resume at the top of the loop. When further processing is impossible or undesirable, you can use the GOTO, EXIT, or RAISE statement to complete the loop. The raised exception will also complete the loop.

while_loop_statement:

The WHILE-LOOP statement relates a Boolean expression with a series of statements enclosed by the keywords LOOP & END LOOP. Before each of the iteration of the loop, the expression is computed. If the expression results TRUE, the series of the statements is executed, and then control the resumes at the top of the loop. If the expression results FALSE or NULL, the loop is then bypassed and the control passes to the next statement.

boolean_expression:

This is an expression which results the Boolean value TRUE, FALSE, & NULL. It is related with a series of statements that is executed only if the expression results TRUE.

for_loop_statement:

While the number of iterations through a WHILE loop is unfamiliar until the loop complete, the number of iterations during a FOR loop is acknowledged before the loop is entered. The Numeric FOR loops iterate over a specific range of integers. The range is section of an iteration scheme that is enclosed by the keywords FOR and LOOPS.

The range is computed when the FOR loop is primary entered and is never re-evaluated. The series of statements in the loop is executed once for each integer in the range defined by the ower_bound..upper_bound.  Subsequent to the each and every iteration, the loop index is incremented.

index_name:

This is an undeclared identifier which names the loop index. Its scope is the loop itself. And therefore, you cannot reference the index outside the loop.

The implicit declaration of the index_name overrides any other declaration outside the loop. Therefore, another variable with similar name cannot be referenced inside the loop unless a label is used, which is as shown below:

<

>

DECLARE

num NUMBER;

BEGIN

...

FOR num IN 1..10 LOOP

...

IF main.num > 5 THEN -- refers to the variable num,

... -- not to the loop index

END IF;

END LOOP;

END main;

lower_bound .. upper_bound:

These are the expressions which should results integer values. The expressions are computed only when the loop is primary entered. By default, the loop index is assigned the value of the lower_bound. If that value is not bigger than the value of the upper_bound, the series of statements in the loop is executed, and then the index is incremented. If the value of the index is still not bigger than the value of the upper_bound, the series of statements is executed again. This procedure repeats until the value of the index is bigger than the value of the upper_bound. By that point, the loop completes.

REVERSE:

By default, the iteration proceeds upward from the lower bound to the upper bound. Though, if you use the keyword REVERSE, the iteration proceeds downward from the upper bound to the lower bound. An illustration is a shown below:

FOR i IN REVERSE 1..10 LOOP -- i starts at 10, ends at 1

-- statements here execute 10 times

END LOOP;

The loop index is assigned the value of the upper_bound. If that value is not less than the value of the lower_bound, the series of statements in the loop is executed, and then the index is decremented. When the value of the index is still not less than the value of the lower_bound, the series of statements is executed again. This procedure repeats until the value of the index is less than the value of the lower_bound. By that point, the loop completes.

cursor_for_loop_statement:

The cursor FOR loop implicitly declares its loop index as the %ROWTYPE record, opens a cursor, repetitively fetches rows of values from the result set into fields in the record, and closes the cursor when all the rows have been processed. And hence, the series of the statements in the loop is executed once for each row which satisfies the query related with cursor_name.

cursor_name:

This identifies an explicit cursor formerly declared within the present scope. When the cursor FOR loop is entered, the cursor_name cannot refer to a cursor already opened by an OPEN statement or an enclosing cursor FOR loop. 

record_name:

This identifies an implicitly declared record. The record has similar structure as a row retrieved by the cursor_name and is equivalent to the record declared as shown below:

record_name cursor_name%ROWTYPE;

The record is defined only within the loop. You can't refer to its fields outside the loop. The implicit declaration of the record_name overrides any other declaration outside the loop. Therefore, the other record with similar name cannot be referenced inside the loop unless a label is used.

The Fields in the record store column values from the implicitly fetched row. The fields have similar names and datatypes as their equivalent columns. To access the field values, you use the dot notation, which is as shown below:

record_name.field_name:

The Select-items fetched from the FOR loop cursor should have easy names, or if they are expressions, they must have aliases. In the illustration below, wages is an alias for the select item

sal+NVL(comm,0):

CURSOR c1 IS SELECT empno, sal+NVL(comm,0) wages, job ...

cursor_parameter_name:

This identifies the cursor parameter; that is, the variable declared as the formal parameter of the cursor. The cursor parameter can appear in a query where a constant can appear. The formal parameters of the cursor should be IN parameters.

 select_statement:

This is a query related with an internal cursor unavailable to you. Its syntax is such that of the select_into_statement without the INTO clause. The PL/SQL automatically opens, declares, fetches from, and closes the internal cursor. As the select_statement is not an free statement, the implicit SQL cursor does not apply to it. 


Related Discussions:- Parameter and keyword description - loop statements

Assignment of DBMS in SQL server, i have an assignment of DBMS subject, thi...

i have an assignment of DBMS subject, this assignment is based on SQL server and power BI

Object types - syntax, Object Types An object type is a user-defined co...

Object Types An object type is a user-defined complex datatype which encapsulates the data structure along with the functions and procedures required to manipulate the data. Th

Using %type-declarations in sql, Using %TYPE The %TYPE attribute gives th...

Using %TYPE The %TYPE attribute gives the datatype of a variable or the database column. In the example below, the %TYPE gives the datatype of a variable: credit REAL(7,2); debi

Sql script to create and populate the tables, Create the four tables and po...

Create the four tables and populate them with the given data. Answer the following queries in SQL. 1. Get all part-color/part-city combinations. Note: Here and subsequently, the

Authorisations - privileges, Authorisations - Privileges As relational...

Authorisations - Privileges As relational theory is silent on the issue of authorisation, it offers nothing with which SQL's vast edifice in support of what it calls privilege

Theory of eternity of life - origin of life, THEO R Y OF ETERNITY OF LIFE...

THEO R Y OF ETERNITY OF LIFE (PRAYER - 1880) - The theory of eternity of life, also called the steady-state theory , states that life has ever been in existence as at presen

Semijoin and composition - sql, Semijoin and Composition - SQL For sem...

Semijoin and Composition - SQL For semijoin, the dyadic relational operator MATCHING, defined thus: r1 MATCHING r2, where r1 and r2 are relations such that r1 JOIN r2 is de

Assignments in pl/sql, Assignments in pl/sql The Variables and constants...

Assignments in pl/sql The Variables and constants are initialized every time a block or subprogram is entered. By default, the variables are initialized to NULL. Therefore, unle

Program, heap sort program in pl/sql

heap sort program in pl/sql

Varrays versus nested tables, Varrays versus Nested Tables The Nested ta...

Varrays versus Nested Tables The Nested tables are differing from varrays in the following ways: 1)  Varrays have a maximum size, while nested tables do not. 2)  Varrays are

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