PL/SQL

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

Advantages of PL/SQL

These are the advantages of PL/SQL.

•Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.

• Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).

• Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.

•Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

PL/SQL Placeholders

Placeholders are temporary storage area. Placeholders can be any of Variables, Constants and Records.

PL/SQL Variables

The General Syntax to declare a variable is:

variable_name datatype [NOT NULL := value ];

•variable_name is the name of the variable.

•datatype is a valid PL/SQL datatype.

•NOT NULL is an optional specification on the variable.

•value or DEFAULT valueis also an optional specification, where you can initialize a variable.

•Each variable declaration is a separate statement and must be terminated by a semicolon.

For example: The below example declares two variables, one of which is a not null.

DECLAREsalary number(4);dept varchar2(10) NOT NULL := “HR Dept”;

PL/SQL Constants

As the name implies a constant is a value used in a PL/SQL Block that remains unchanged throughout the program. A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.

The General Syntax to declare a constant is:

 constant_name CONSTANT datatype := VALUE;

•constant_name is the name of the constant i.e. similar to a variable name.

•The word CONSTANT is a reserved word and ensures that the value does not change.

•VALUE – It is a value which must be assigned to a constant when it is declared. You cannot assign a value later.

For example, to declare salary_increase, you can write code as follows:

DECLARE salary_increase CONSTANT number (3) := 10;

 You must assign a value to a constant at the time you declare it. If you do not assign a value to a constant while declaring it and try to assign a value in the execution section, you will get a error. If you execute the below Pl/SQL block you will get error.

PL/SQL Records

What are records?

Records are another type of datatypes which oracle allows to be defined as a placeholder. Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc. Each scalar data types in the record holds a value. A record can be visualized as a row of data. It can contain all the contents of a row.

Declaring a record:

To declare a record, you must first define a composite datatype; then declare a record for that type.

The General Syntax to define a composite datatype is:

TYPE record_type_name IS RECORD (first_col_name column_datatype, second_col_name column_datatype, …);

•record_type_name – it is the name of the composite type you want to define.

•first_col_name, second_col_name, etc.,- it is the names the fields/columns within the record.

•column_datatype defines the scalar datatype of the fields.

There are different ways you can declare the datatype of the fields.

1) You can declare the field in the same way as you declare the fieds while creating the table.
2) If a field is based on a column from database table, you can define the field_type as follows:

col_name table_name.column_name%type;

By declaring the field datatype in the above method, the datatype of the column is dynamically applied to the field.  This method is useful when you are altering the column specification of the table, because you do not need to change the code again.

NOTE: You can use also %type to declare variables and constants.

The General Syntax to declare a record of a uer-defined datatype is:

record_name record_type_name;