5.PLSQL Introduction with Basic Syntax,Data Types,Variables,Constants and Operators with Examples.

#Basic Syntax:Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be nested within other PL/SQL blocks using BEGIN and END. Here is the basic structure of a PL/SQL block:

DECLARE
   
BEGIN
   
EXCEPTION
   
END;

The 'Hello World' Example:
DECLARE
   message  varchar2(20):= 'Hello, World!';
BEGIN
   dbms_output.put_line(message);
END;
/

The PL/SQL Delimiters:

+, -, *, / Addition, subtraction/negation, multiplication, division

% Attribute indicator

' Character string delimiter
. Component selector
(,) Expression or list delimiter
: Host variable indicator
, Item separator
" Quoted identifier delimiter
= Relational operator
@ Remote access indicator
; Statement terminator
:= Assignment operator
=> Association operator
|| Concatenation operator
** Exponentiation operator
<<, >> Label delimiter (begin and end)
/*, */ Multi-line comment delimiter (begin and end)
-- Single-line comment indicator
.. Range operator
<, >, <=, >= Relational operators
<>, '=, ~=, ^= Different versions of NOT EQUAL.

#Data Types:

PL/SQL Numeric Data Types and Subtypes:
PLS_INTEGER,BINARY_INTEGER,BINARY_FLOAT,BINARY_DOUBLE ,NUMBER
DEC(prec, scale),DECIMAL(prec, scale),NUMERIC(pre, secale)DOUBLE PRECISION,FLOAT
INT,INTEGER,SMALLINT,REAL.

Example:Following is a valid declaration:

DECLARE
   num1 INTEGER;
   num2 REAL;
   num3 DOUBLE PRECISION;
BEGIN
   null;
END;
/

PL/SQL Character Data Types and Subtypes:

CHAR Fixed-length character string with maximum size of 32,767 bytes
VARCHAR2 Variable-length character string with maximum size of 32,767 bytes
RAW Variable-length binary or byte string with maximum size of 32,767 bytes, not interpreted by PL/SQL
NCHAR Fixed-length national character string with maximum size of 32,767 bytes
NVARCHAR2 Variable-length national character string with maximum size of 32,767 bytes
LONG Variable-length character string with maximum size of 32,760 bytes
LONG RAW Variable-length binary or byte string with maximum size of 32,760 bytes, not interpreted by PL/SQL
ROWID Physical row identifier, the address of a row in an ordinary table
UROWID Universal row identifier (physical, logical, or foreign row identifier).

PL/SQL Boolean Data Types:

The BOOLEAN data type stores logical values that are used in logical operations. The logical values are the Boolean values TRUE and FALSE and the value NULL.

However, SQL has no data type equivalent to BOOLEAN. Hence Boolean values cannot be used in:

• SQL statements
• Built-in SQL functions (such as TO_CHAR)
• PL/SQL functions invoked from SQL statements

PL/SQL Datetime and Interval :

YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,TIMEZONE_HOUR,TIMEZONE_MINUTE,TIMEZONE_REGION,TIMEZONE_ABBR.

PL/SQL Large Object (LOB) Data Types:BFILE,BLOB,CLOB,NCLOB.

PL/SQL User-Defined Subtypes:
A subtype is a subset of another data type, which is called its base type. A subtype has the same valid operations as its base type, but only a subset of its valid values.

PL/SQL predefines several subtypes in package STANDARD. For example, PL/SQL predefines the subtypes CHARACTER and INTEGER as follows:

SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);

#PLSQL Variables:

Variable Declaration in PL/SQL

PL/SQL variables must be declared in the declaration section or in a package as a global variable. When you declare a variable, PL/SQL allocates memory for the variable's value and the storage location is identified by the variable name.

The syntax for declaring a variable is:

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

 %TYPE vs %ROWTYPE - What's the difference?

Description: Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automatically picks up the new definition from the database.

% TYPE is used to define a varibale as same data type of the column of associated table.
%ROWTYPE provides a record type that represents a row in database table.

Example:

-- %TYPE is used to declare a field with the same type as 
-- that of a specified table's column: 
 
DECLARE
   v_EmpName  emp.ename%TYPE;
BEGIN
   SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
   DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/
 
-- %ROWTYPE is used to declare a record with the same types as 
-- found in the specified database table, view or cursor: 
 
DECLARE
  v_emp emp%ROWTYPE;
BEGIN
  v_emp.empno := 10;
  v_emp.ename := 'XXXXXXX';
END;
/
 

Initializing Variables in PL/SQL

Whenever you declare a variable, PL/SQL assigns it a default value of NULL. If you want to initialize a variable with a value other than the NULL value, you can do so during the declaration, using either of the following:

• The DEFAULT keyword
• The assignment operator

For example:

counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';

You can also specify that a variable should not have a NULL value using the NOT NULL constraint. If you use the NOT NULL constraint, you must explicitly assign an initial value for that variable.

Variable Scope in PL/SQL:
PL/SQL allows the nesting of Blocks, i.e., each program block may contain another inner block. If a variable is declared within an inner block, it is not accessible to the outer block. However, if a variable is declared and accessible to an outer Block, it is also accessible to all nested inner Blocks. There are two types of variable

scope:

Local variables - variables declared in an inner block and not accessible to outer blocks.

Global variables - variables declared in the outermost block or a package.

Assigning SQL Query Results to PL/SQL Variables.

You can use the SELECT INTO statement of SQL to assign values to PL/SQL variables. For each item in the SELECT list, there must be a corresponding, type-compatible variable in the INTO list.

The following program assigns values from the above table to PL/SQL variables using the SELECT INTO clause of SQL:

DECLARE
   c_id customers.id%type := 1;
   c_name  customers.name%type;
   c_addr customers.address%type;
   c_sal  customers.salary%type;
BEGIN
   SELECT name, address, salary INTO c_name, c_addr, c_sal
   FROM customers
   WHERE id = c_id;

   dbms_output.put_line
   ('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal);
END;
/

#Constants:

Declaring a Constant:

A constant is declared using the CONSTANT keyword. It requires an initial value and does not allow that value to be changed. For example:

PI CONSTANT NUMBER := 3.141592654;

#Operators:

An operator is a symbol that tells the compiler to perform specific mathematical or logical manipulation. PL/SQL language is rich in built-in operators and provides the following types of operators:

Arithmetic operators: +,-,*,/,**

Relational operators: =,!=,<>,~=,>,<,>=,<=

Comparison operators: Like,Between,IN,IS NULL.

Logical operators : AND,OR,NOT

String operators: ||(concatination operator).