PL SQL Tutorial

• Pl Sql Introduction
• Pl Sql Blocks
• Pl Sql Operators
• Pl Sql Variables
• Pl Sql Conditional Statements
• Pl Sql Iterative Statements
• Pl Sql Implicit Cursors
• Pl Sql Explicit Cursors

Pl Sql Tutorial

Pl Sql Variables

Declaration and Initialization

- Declaring variables in declarative area of the block;
- The initialization can be made to the Declaration or in the execution area;
- The variables will be visible in the rest of the block, and in blocks included in it, less in blocks in which their name is redefined;
- All PL/SQL variables are a type of time restrictions and a string valid values;
- Constants must be binding, and subsequently set up will not be able to change the value;
- Variables NOT NULL must by law be initialized, and will not be able to receive the value NULL;

Types of variables

- scaling
- composite
- reference
- object
- LOB (Large Objects): CLOB, BLOB, NCLOB, BFILE
- Variables non-PL/SQL: environment variables (BIND VARIABLES)

Scalar types contain simple values and correspond mainly types that you can have columns of tables.
- char - fixed length max 32,767 bytes
- varchar2 - variable length max 32,767 bytes
- long [string length variable 2 GB]
- number (precision, scale)
- boolean (true, false, null)
- data
- binary_integer and pls_integer (integers between-than 2147483647 and than 2147483647)
- binary_float and binary_double (for real numbers in the version of Oracle 10 g)
- timestamp (for fractions of a second)

Attribute% TYPE

Assigns a variable type to another variable or specific data type of a column in the table.

variable table.column_name%TYPE;
or
variable1 type_data;
variable2 variable1%TYPE;

Example:

DECLARE
  v_name employees.name%TYPE ;
  v_surname employees.surname%TYPE;
BEGIN
  SELECT name, surname
     INTO v_name, v_surname
    FROM employees
  WHERE id_employee = 100;
DBMS_OUTPUT.PUT_LINE ("EMPLOYEE NAME is: ' | | v_name | | ' '||v_surname);
END;