Introduction to PL/SQL (Lexical Units & Operator)

Lexical Units: Lexical units include letters, numerals, special characters, tabs, spaces, returns and symbol that building a PL/SQL block. Lexical unit can classified as following…

  • Identifier
  • Delimiters
  • Literals
  • Comments

Identifier: Identifier are the names given to PL/SQL objects.
Quoted Identifier: Quoted identifier makes identifier case sensitive, include character such as space and use reserved words. example-

“begin date” DATE;
“end adte” DATE;

Delimiters: Delimiters are symbols that have special meaning. I know you have already learned about that the semicolon (;) is used to terminate a SQL & PL/SQL statement. Therefore, (;) is the best example of a delimiters. A list of delimiters are following-

Symbol Meaning
+ Addition Operator
Subtraction/Negation Operator
* Multipluication Operator
/ Division Operator
= Equalty Operator
@ Remote access indicator
; Statement Terminator
<> Inequality Operator
!= Inequality Operator
|| Concatention Operator
Single line Comment ondicator
/* Beginning Comments Indicator
*/ Ending Comment Indicator
:= Assignment Operator

Literals: Any value that is assigned to a variable is a literal. Any character, numeral, boolean or date value that is not an identifier is a literal. Literal can classified as …
1. Character Literal : Such as – Asgor, 124c, 11-jan-2014
2. Numeric Literal : Such as – 51478, 45.457
3. Boolean Literal : TRUE, FALSE and NULL is boolean literals.

Comments: It is good programming practice to explain what a piece of a code is trying to achieve. When you include the explanation in a PL/SQL block, the compiler cannot interpret these instruction. Comments are given by the …

– Two hyphen (–) are used to single line comment.
– The beginning and ending comments delimiters (/* and */) are used to comments multiple lines.

continue….

Introduction to PL/SQL (Composite Data Type)

Composite data type: Composite data type stores values that have internal components. You can pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually. Internal components can be either scalar or composite. You can use scalar components wherever you can use scalar variables or you can use composite components wherever you can use composite variables of the same type. There are two types of composite data type…

  • 1. PL/SQL records.
  • 2. PL/SQL collection.
    • INDEX by Table
    • Nested Table
    • VARRAY

1. PL/SQL records: Use PL/SQL records when you want to store values of different data types but only one occurrence at a time. PL/SQL record must contain one or more components (called fields) of any scalar, RECORD or Index by table data type.

Creating PL/SQL records:

Syntax:
TYPE type_name is RECORD
    (field_declaration [, field_declaration ]...);
identifier type name;

field_declaration:
field_name {field_type | variable%type
           | table.column%type | table%rowtype}
           [[NOT NULL] {:= | DEFAULT} expr]

Example:

...
TYPE emp_record_type IS RECORD
     (lname VARCHAR2(20),
      job_id VARCHAR2(30)  DEFAULT 'Developer',
      salary NUMBER emp.salary%TYPE);
emp_type emp_record_type;
...

Using %ROWTYPE in composite data type: The %ROWTYPE attribute is useful when the number and data types of the underlying database columns is unknown and  retrieving a row with the SELECT * from statement.

Example:

DECLARE
  emp_rec employees%ROWTYPE;
BEGIN
  SELECT * INTO emp_rec FROM employees
    WHERE employee_id = 124;
  emp_rec.hire_date := SYSDATE;
 UPDATE EMP SET ROW = emp_rec;
END;

 

Introduction to PL/SQL (Variable & Data type)

Variable: Variable are mainly used for temporary storage of data and manipulation of stored data. Variable can stored any PL/SQL objects, such as variables, types, cursors and subprograms. Variable can be used repeatedly in an application by referring to them in the statements.

Identifier & Variable: Identifier are name of the variables. Variable are storage location of data. Data stored in memory. Variable point to this memory location where data can be read and modified. Identifier are used to name PL/SQL objects and Variable are used to store PL/SQL objects.

You must declare all PL/SQL identifier in the declaration section before referencing them in the PL/SQL block.

Syntax:

identifier [CONSTANT] datatype [not null] [:= | DEFAULT expr];

Example:

DECLARE
  hire_date DATE;
  dept_no NUMBER(2) NOT NULL := 10;
  location VARCHAR2(10) := 'Dhaka';
  c_comm CONSTANT NUMBER := 1400;
  country VARCHAR2(10) DEFAULT 'Bangladesh';
BEGIN
...
END;

Types of Variable:
PL/SQL Variable –

  • Scalar: Scalar data type can hold single value.
  • Comosite: Composite data type contain internal elements that are either scalar or scalar. Record and Table are example of examples of composite data types.
  • Reference: Reference data types hold values, called pointers, that point to a storage location.
  • Large Objects (LOB): LOB data type hold values, called locators, that specify the location of large objects that are stored out of line.

Non-PL/SQL Variables – Bind variable.

Initialize the variable to an expression with the assignment operator (:=) or with the DEFAULT reserved word.

Base Scalar Data Types:

Data Type Description
CHAR (max_length) Base type for fixed-length character data up to 32767 bytes.
VARCHAR2 (max_length) Base type for variable-length character data up to 32767 bytes.
Number (p,s) Number having precision p and scale s. The precision p can be range from 1 to 38. The scale s can range -84 to 127.
BINARY_INTEGER Base type for integers between -2,147,483,647 and 2,147,483,647.
PLS_INTEGER Base type singed integers between -2,147,483,647 and 2,147,483,647. The arithmetic operation on PLS_INTEGER and BINARY_INTEGER values are faster than on number values.
BOOLEAN Base type that stores one of the three possible values used for logical calculations: TRUE, FALSE and NULL.
BINARY_FLOAT Represents floating point number in IEEE 754 format. Requeires 5 bytes to store the value.
BINARY_DOUBLE Represents floating point number in IEEE 754 format. Requeires 9 bytes to store the value.
DATE Base type for date and time.
TIMESTAMP The TIMESTAMP data type, which extends the DATE type, stores the year, month, day, hour, minute, second and fraction of second.
TIMESTAMP WITH TIME ZONE The TIMESTAMP WITH TIME ZONE data type, which extends the TIMESTAMP data type, includes a time zone displacement.
TIMESTAMP WITH LOCAL TIME ZONE The TIMESTAMP WITH LOCAL TIME ZONE data type, which extends the TIMESTAMP data type, includes a time zone displacement. The time zone displacement is the difference between local and UTC time.

Declare Variable with %TYPE Attribute:  %type attribute is used to declare a variable according to a database column definition or another declared variable. Such as…

....
  lname employees.last_name%TYPE,
  dept_no NUMBER;
  manager dept_no%TYPE;
....

Bind Variable: Bind variable are crated in the environment and not in the declarative section of a PL/SQL block. Variable declared in a PL/SQL block are available only when you execute the block. After the block is executed, the memory used by the variable is freed. Sometimes they are called host variable and create with VARIABLE command.
Example:

VARIABLE name VARCHAR2
SET AUTOPRINT ON
BEGIN
  SELECT last_name INTO :name 
  FROM employees WHERE employee_id = 100;
END;
/

 continue….

Introduction to PL/SQL (Basic)

PL/SQL (Procedural Language/Structured Query Language): PL/SQL is Oracle Corporation’s procedural language extension for SQL and Oracle corporation’s standard data access language for relational database. PL/SQL is available in Oracle Database since version 7. PL/SQL includes procedural language elements such as conditions and loops. You can declare constants and variables, procedures and functions, types and variables of those types, and triggers. You can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections.

PL-SQL

PL/SQL Block Structure: A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. PP/SQL block structure like this…

DECLARE (optional)
  -- Variable, cursors, user defined exceptions.
BEGIN (mandatory)
  -- SQL Statements
  -- PL/SQL Statements
Exception (optional)
  -- Actions to perform when error occur
END (mandatory)

Block type: There are three type of block make up a PL/SQL progarm. they are…

  • Anonymous block: Anonymous block are unnamed blocks. These block are not stored in the database.
  • Procedures: Procedure are a named PL/SQL block that are stored in the database and perform an action.
  • Functions: Function similar to procedure, except that a function must return a value.

Test your output: Enable output in SQL*Plus with the this command –  SET SERVEROUTPUT ON and use a predefined Oracle package and its procedure – DBMS_OUTPUT.PUT_LINE 

Example:

SET SERVEROUTPUT ON
...
BEGIN
  DBMS_OUTPUT.PUT_LINE ('Hello World!');
END;
/

 Continue….