6.PLSQL Conditions,Loops,Strings,Arrays with examples.

#PL/SQL Conditions:

IF-THEN Statement

IF-THEN-ELSE Statement

IF-THEN-ELSEIF Statement

CASE Statement

Searched CASE statement

Nested IF-THEN-ELSE

#Loops:

Basic Loop- In this loop structure, sequence of statements is enclosed between the LOOP and END LOOP statements.

Ex:The syntax of a basic loop in PL/SQL programming language is:

LOOP
  Sequence of statements;
END LOOP;
Here, sequence of statement(s) may be a single statement or a block of statements. An EXIT statement or an EXIT WHEN statement is required to break the loop.

Example:
DECLARE
   x number := 10;
BEGIN
   LOOP
      dbms_output.put_line(x);
      x := x + 10;
      exit WHEN x > 50;
   END LOOP;
   -- after exit, control resumes here
   dbms_output.put_line('After Exit x is: ' || x);
END;
/

WHILE Loop :Repeats a statement or group of statements while a given condition is true.

Syntax:
WHILE condition LOOP
   sequence_of_statements
END LOOP;
Example:
DECLARE
   a number(2) := 10;
BEGIN
   WHILE a < 20 LOOP
      dbms_output.put_line('value of a: ' || a);
      a := a + 1;
   END LOOP;
END;
/

FOR Loop : Execute a sequence of statements multiple times and abbreviates the code that manages the loop variable.

Syntax:
FOR counter IN initial_value .. final_value LOOP
   sequence_of_statements;
END LOOP;

Example:
DECLARE
   a number(2);
BEGIN
   FOR a in 10 .. 20 LOOP
       dbms_output.put_line('value of a: ' || a);
  END LOOP;
END;
/

Nested Loops in PL/SQl : You can use one or more loop inside any another basic loop, while or for loop.

LOOP
   Sequence of statements1
   LOOP
      Sequence of statements2
   END LOOP;
END LOOP;

#Strings:The string in PL/SQL is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all. PL/SQL offers three kinds of strings:

Fixed-length strings: In such strings, programmers specify the length while declaring the string. The string is right-padded with spaces to the length so specified.

Variable-length strings: In such strings, a maximum length up to 32,767, for the string is specified and no padding takes place.

Character large objects (CLOBs): These are variable-length strings that can be up to 128 terabytes.

Declaring String Variables:
Oracle database provides numerous string datatypes , like, CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB. The datatypes prefixed with an 'N' are 'national character set' datatypes, that store Unicode character data.

#Arrays:PL/SQL programming language provides a data structure called the VARRAY, which can store a fixed-size sequential collection of elements of the same type.

A varray is used to store an ordered collection of data, but it is often more useful to think of an array as a collection of variables of the same type.

Creating a Varray Type
A varray type is created with the CREATE TYPE statement. You must specify the maximum size and the type of elements stored in the varray.

Syntax for creating a VRRAY type at the schema level is:

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of
Where,

• Varray_type_name is a valid attribute name,
• n is the number of elements (maximum) in the varray,
• element_type is the data type of the elements of the array.

Maximum size of a varray can be changed using the ALTER TYPE statement.