Oracle PL/SQL Tutorial/PL SQL Data Types/Introduction

All identifiers within the same scope must be unique.

   <source lang="sql">

SQL> SQL> declare

 2      v_amount NUMBER;
 3      v_amount BINARY_INTEGER; -- duplicate!!!
 4  Begin
 5     NULL;
 6  end;
 7  /

PL/SQL procedure successfully completed. SQL> SQL></source>

Block Structure

You typically use PL/SQL to add business logic to the database.

PL/SQL programs are divided up into structures known as blocks.

Each block containing PL/SQL and SQL statements.

A typical PL/SQL block has the following structure:

   <source lang="sql">

[DECLARE

 declaration_statements

] BEGIN

 executable_statements

[EXCEPTION

 exception_handling_statements

] END;</source>

Building Expressions with Operators

Expressions are constructed by using operands and operators.

An Example of a Simple PL/SQL Expression

   <source lang="sql">

SQL> SQL> declare

 2      v_i1 NUMBER;
 3      v_i2 NUMBER;
 4  begin
 5      v_i1:=10/3;
 6      v_i2:=-v_i1;
 7  end;
 8  /

PL/SQL procedure successfully completed. SQL></source>

Introducing the Main Data type Groups

There are four main groups:

1. Scalar datatypes represent single values that can"t be divided into parts.
2. Composite datatypes include internal components that can be manipulated independently.
3. %ROWTYPE is an example of the PL/SQL RECORD datatype.
4. References contain pointers to other program items.
5. Large objects store or point to large amounts of textual or binary information, such as images, movies, or books.

Scalar datatypes are divided into families:

1. Numeric datatypes.
2. Character datatypes.
3. Date/time datatypes.
4. Boolean datatypes.

21. 1. Introduction 21. 1. 1. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/BlockStructure.htm">Block Structure</a> 21. 1. 2. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/YourFirstPLSQLBlock.htm">Your First PL/SQL Block</a> 21. 1. 3. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/TheslashcharacterattheendoftheexampleexecutesthePLSQL.htm">The slash character (/) at the end of the example executes the PL/SQL.</a> 21. 1. 4. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/Youcandeclarethewholestringtobeenclosedinquotesbyusingtheconstructqtext.htm">You can declare the whole string to be enclosed in quotes by using the construct q"!text!"</a> 21. 1. 5. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/Allidentifierswithinthesamescopemustbeunique.htm">All identifiers within the same scope must be unique.</a> 21. 1. 6. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/BuildingExpressionswithOperators.htm">Building Expressions with Operators</a> 21. 1. 7. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/PLSQLdatatypes.htm">PL/SQL datatypes</a> 21. 1. 8. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/VariableNamingRules.htm">Variable Naming Rules</a> 21. 1. 9. Introducing the Main Data type Groups

PL/SQL datatypes

Oracle PL/SQL supports all SQL types plus the following additional Oracle PL/SQL specific types

Type Description CHAR[(length [BYTE | CHAR])] Fixed-length character data of length bytes or characters and padded with trailing spaces. Maximum length is 2,000 bytes. VARCHAR2(length [BYTE | CHAR]) Variable-length character data of up to length bytes or characters. Maximum length is 4,000 bytes. NCHAR[(length)] Fixed-length Unicode character data of length characters. Number of bytes stored is 2 * length for AL16UTF16 encoding and 3 * length for UTF8. Maximum length is 2,000 bytes. NVARCHAR2(length) Variable-length Unicode character data of length characters. Number of bytes stored is 2 * length for AL16UTF16 encoding and 3 * length for UTF8 encoding. Maximum length is 4,000 bytes. BINARY_FLOAT Stores a single precision 32-bit floating-point number. Operations involving BINARY_FLOAT are typically performed faster than on NUMBERs. BINARY_FLOAT requires 5 bytes of storage space. BINARY_DOUBLE Stores a double precision 64-bit floating-point number. Operations involving BINARY_DOUBLE are typically performed faster than on NUMBERs. BINARY_DOUBLE requires 9 bytes of storage space. NUMBER(precision, scale) and NUMERIC(precision, scale) Variable-length number; precision is the maximum number of digits (in front of and behind a decimal point, if used) that may be used for the number. The maximum precision supported is 38; scale is the maximum number of digits to the right of a decimal point (if used). If neither precision nor scale is specified, then a number with up to a precision and scale of 38 digits may be supplied (meaning you can supply a number with up to 38 digits, and any of those 38 digits may be in front of or behind the decimal point). DEC and DECIMAL Subtype of NUMBER. A fixed-point decimal number with up to 38 digits of decimal precision. DOUBLE PRECISION and FLOAT Subtype of NUMBER. A floating-point number with up to 38 digits of precision. REAL Subtype of NUMBER. A floating-point number with up to 18 digits of precision. INT, INTEGER, and SMALLINT Subtype of NUMBER. An integer with up to 38 digits of decimal precision. DATE Date and time with the century, all four digits of year, month, day, hour (in 24-hour format), minute, and second. May be used to store a date and time between January 1, 4712 B.C. and December 31, 4712 A.D. Default format is specified by the NLS_DATE_FORMAT parameter (for example: DD-MON-RR). INTERVAL YEAR[(years_precision)] TO MONTH Time interval measured in years and months; years_precision specifies the precision for the years, which may be an integer from 0 to 9 (default is 2). Can be used to represent a positive or negative time interval. INTERVAL DAY[(days_precision)] TO SECOND[(seconds_precision)] Time interval measured in days and seconds; days_precision specifies the precision for the days, which is an integer from 0 to 9 (default is 2); seconds_precision specifies the precision for the fractional part of the seconds, which is an integer from 0 to 9 (default is 6). Can be used to represent a positive or negative time interval. TIMESTAMP[(seconds_precision)] Date and time with the century, all four digits of year, month, day, hour (in 24-hour format), minute, and second; seconds_precision specifies the number of digits for the fractional part of the seconds, which can be an integer from 0 to 9 (default is 6). Default format is specified by the NLS_TIMESTAMP_FORMAT parameter. TIMESTAMP[(seconds_precision)] WITH TIME ZONE Extends TIMESTAMP to store a time zone. The time zone can be an offset from UTC, such as ??-5:0", or a region name, such as ??US/Pacific". Default format is specified by the NLS_TIMESTAMP_TZ_FORMAT parameter. TIMESTAMP[(seconds_precision)] WITH LOCAL TIME ZONE Extends TIMESTAMP to convert a supplied datetime to the local time zone set for the database. The process of conversion is known as normalizing the datetime. Default format is specified by the NLS_TIMESTAMP_FORMAT parameter. CLOB Variable length single-byte character data of up to 128 terabytes. NCLOB Variable length Unicode national character set data of up to 128 terabytes. BLOB Variable length binary data of up to 128 terabytes. BFILE Pointer to an external file. LONG Variable length character data of up to 2 gigabytes. Superceded by CLOB and NCLOB types, but supported for backwards compatibility. RAW(length) Variable length binary data of up to length bytes. Maximum length is 2,000 bytes. Superceded by BLOB type, but supported for backwards compatibility. LONG RAW Variable length binary data of up to 2 gigabytes. Superceded by BLOB type but supported for backwards compatibility. ROWID Hexadecimal string used to represent a row address. UROWID[(length)] Hexadecimal string representing the logical address of a row of an index-organized table; length specifies the number of bytes. Maximum length is 4,000 bytes (also default). REF object_type Reference to an object type. VARRAY Variable length array. This is a composite type and stores an ordered set of elements. NESTED TABLE Nested table. This is a composite type and stores an unordered set of elements. XMLType Stores XML data. User defined object type You can define your own object type and create objects of that type. PL/SQL only data type PL/SQL only data type BOOLEAN Boolean value (TRUE, FALSE, or NULL). BINARY_INTEGER Integer between C231 (C2,147,483,648) and 231 (2,147,483,648). NATURAL Subtype of BINARY_INTEGER. A non-negative integer. NATURALN Subtype of BINARY_INTEGER. A non-negative integer (and cannot be NULL). POSITIVE Subtype of BINARY_INTEGER. A positive integer. POSITIVEN Subtype of BINARY_INTEGER. A positive integer (and cannot be NULL). SIGNTYPE Subtype of BINARY_INTEGER. An integer of C1, 0, or 1. PLS_INTEGER Integer between C231 (C2,147,483,648) and 231 (2,147,483,648). Similar to BINARY_INTEGER, but computations involving PLS_INTEGER values are faster. STRING Same as VARCHAR2. RECORD Composite of a group of other types. Similar to a structure in C. REF CURSOR Pointer to a set of rows.

These datatypes can be used for creating simple scalar variables.

They can be combined into structures such as records or PL/SQL tables.

A scalar variable is a variable that is not made up of some combination of other variables.

Scalar variables don"t have internal components that you can manipulate individually.

They are often used to build up more complex datatypes such as records and arrays.

Some of the datatype names match those used by Oracle for defining database columns.

In most cases the definitions are the same for both the database and PL/SQL, but there are a few differences.

PL/SQL also provides subtypes of some datatypes.

A subtype represents a special case of a datatype.

A subtype represents a narrower range of values than the parent type.

For example, POSITIVE is a subtype of BINARY_INTEGER that holds only positive values.

The slash character (/) at the end of the example executes the PL/SQL.

   <source lang="sql">

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    age   NATURAL;
 3
 4  BEGIN
 5    age := 10;
 6    DBMS_OUTPUT.PUT_LINE("age:");
 7    DBMS_OUTPUT.PUT_LINE(age);
 8  END;
 9  /

age: 10 PL/SQL procedure successfully completed. SQL></source>

Variable Naming Rules

Variable names can be composed of letters, dollar signs, underscores, and number signs.

No other characters can be used.

A variable name must start with a letter, after which any combination of the allowed characters can be used.

The maximum length for a variable name is 30 characters.

Variable names, like those of keywords and other identifiers, are not case sensitive.

21. 1. Introduction 21. 1. 1. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/BlockStructure.htm">Block Structure</a> 21. 1. 2. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/YourFirstPLSQLBlock.htm">Your First PL/SQL Block</a> 21. 1. 3. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/TheslashcharacterattheendoftheexampleexecutesthePLSQL.htm">The slash character (/) at the end of the example executes the PL/SQL.</a> 21. 1. 4. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/Youcandeclarethewholestringtobeenclosedinquotesbyusingtheconstructqtext.htm">You can declare the whole string to be enclosed in quotes by using the construct q"!text!"</a> 21. 1. 5. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/Allidentifierswithinthesamescopemustbeunique.htm">All identifiers within the same scope must be unique.</a> 21. 1. 6. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/BuildingExpressionswithOperators.htm">Building Expressions with Operators</a> 21. 1. 7. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/PLSQLdatatypes.htm">PL/SQL datatypes</a> 21. 1. 8. Variable Naming Rules 21. 1. 9. <A href="/Tutorial/Oracle/0420__PL-SQL-Data-Types/IntroducingtheMainDatatypeGroups.htm">Introducing the Main Data type Groups</a>

You can declare the whole string to be enclosed in quotes by using the construct q"!text!"

   <source lang="sql">

SQL> SQL> declare

 2      
 3      
 4  begin
 5      NULL;
 6  End;
 7  /

PL/SQL procedure successfully completed.

Text literals in Oracle are case sensitive.</source>

Your First PL/SQL Block

   <source lang="sql">

SQL> SQL> DECLARE

 2    x     NUMBER;
 3  BEGIN
 4    x := 72600;
 5  END;
 6  /

PL/SQL procedure successfully completed.</source>