Arrays in PLSQL

September 19, 2015 0 Comments A+ a-


PL/SQL programming language provides a data structure called the ARRAY, which can store a fixed-size sequential collection of elements of the same type. A array 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.
All arrays consist of contiguous memory locations.

Each element in a varray has an index associated with it. It also has a maximum size that can be changed dynamically.
Creating a Array Type :
A array type is created with the CREATE TYPE statement. You must specify the maximum size and the type of elements stored in the array.

CREATE OR REPLACE TYPE array_type_name IS VARRAY(n) of <element_type>

  • array_type_name is a valid attribute name.
  • n is the number of elements (maximum) in the array.
  • element_type is the data type of the elements of the array.
NOTE:
Maximum size of a varray can be changed using the ALTER TYPE statement.

Example –
SET VERIFY OFF
SET SERVEROUTPUT ON
CL SCR
DECLARE
type namesarray IS VARRAY(5) OF VARCHAR2(10);
type grades IS VARRAY(5) OF INTEGER;
names namesarray;
marks grades;
total integer;
BEGIN
names := namesarray('Nita', 'Harish', 'Sonali');
marks:= grades(88, 75, 3);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i in 1 .. total LOOP
dbms_output.put_line('Student: ' || names(i) || 'Marks: ' || marks(i));
END LOOP;
END;
/
o/p –
Total 3 Students
Student: Nita Marks: 88
Student: Harish Marks: 75
Student: Sonali Marks: 3
PL/SQL procedure successfully completed.

For Loop in PLSQL

September 19, 2015 0 Comments A+ a-


It is a type of looping where initialization, Incrementation and condition checking is automatically done by the system the formula given by user which leads to less amount of coding.
Syntax:
FOR IN [REVERSE] LOWERBOUND .. UPPERBOUND LOOP
STATEMENT ;
......
END LOOP;
/


While Loop in PLSQL

September 19, 2015 0 Comments A+ a-


It is a type of looping where condition is check first then it will execute the statement.
Syntax:
WHILE LOOP
STATEMENT1;
STATEMENT2;
..............
END LOOP;


Basic Loop in PLSQL

September 19, 2015 0 Comments A+ a-


Basic Loop :
In this loop statement is executed first then it will check the statement and looping statement executed at least once

SYNTAX:
LOOP
STATEMENT1;
STATEMENT2;
............
EXIT[WHEN<CONDITION>]
END LOOP;

Control Structure in PLSQL

September 19, 2015 0 Comments A+ a-


It is a block of executable statement which will perform a task once or number of times unless and until a condition is satisfied control structure are basically required to perform condition wise task.
PL/SQL provides following control structure to perform condition wise job.
  • If loop
  • Basic/While loop
  • For loop

Type of Programme in PL/SQL

September 19, 2015 0 Comments A+ a-


It supports two type of program to manipulate the oracle database.
  • Anonymous Block
  • Sub Program
The primary jobs of above program are same to manipulate database but they are categorised in two different types depending on their security and execution.

Advance Data Type in PLSQL

September 19, 2015 0 Comments A+ a-


PL/SQL Large Object (LOB) Data Types :
Large object (LOB) data types refer large to data items such as text, graphic images, video clips and sound waveforms. LOB data types allow efficient, random, piecewise access to this data.
Following are the predefined PL/SQL LOB data types:

User Defined Type in PLSQL

September 19, 2015 0 Comments A+ a-


PLSQL provide user define type, Type is collection of one or more number of own data type .
Syntax to Declare User Defined Type:
DECALRE
TYPE <T_NAME> IS RECORD(
V_NAME1 DATATYPE(SIZE),
V_NAME2 DATATYPE(SIZE),
.......................
.....................N)
;


V_NAME <T_NAME>;

Scalar Attribute in PLSQL

September 18, 2015 0 Comments A+ a-


If you want declare a variable and it’s data type is same as one column’s data type in one some table then you use scalar attribute.
The above data type in PL/SQL provides a scalar attribute %type.

Data Type in PL/SQL

September 18, 2015 0 Comments A+ a-


To design a programme using a language the minimum requirement is a variable and variable minimum requirement is data type.
PL/SQL provides two types of data types :
  • Scalar data type
  • Composite data type
  • Large Object (LOB)

Wildcard Operator

September 18, 2015 0 Comments A+ a-


Wildcard Operator/Like Operator:
The like operator is used to compare a value to similar values using wildcard operators.
It always use Like operator

Comments Line in PL/SQL

September 18, 2015 0 Comments A+ a-


The PL/SQL supports single line and multi-line comments. All characters available inside any comment are ignored by PL/SQL compiler. The PL/SQL single-line comments start with the delimiter – - (double hyphen) and multi-line comments are enclosed by /* and */.
Example -2
DECLARE
/* This programme written for show my website in PL/SQL programme My website is
www.tutorialslife.com*/


website varchar2(20):= 'www.tutorialslife.com';  --my website is assign to website variable
BEGIN
dbms_output.put_line(website);
END;
/

Output – www.tutorialslife.com
PL/SQL procedure successfully completed.

Identifiers in PL/SQL

September 18, 2015 0 Comments A+ a-


PL/SQL identifiers are constants, variables, exceptions, procedures, cursors, and reserved words.
The identifiers consist of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters.
By default, identifiers are not case-sensitive. So you can use integer or INTEGER to represent a numeric value. You cannot use a reserved keyword as an identifier.

Overview – PL/SQL

September 18, 2015 0 Comments A+ a-


PL/SQL is pronounced as a language but it is not a language typically rather than it is a mechanism or technology implemented on Oracle server to manipulate Oracle database programmatically using SQL command

What is PL/SQL?
  • PL/SQL stands for Procedural Language extension of SQL.
  • PL/SQL is a combination of SQL along with the procedural features of programming languages.
  • It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
  • It is a block structure language.