【PL/SQL】类型和变量 | Data Type & Variables

Block type:
Anonymous:declare??
Subprograms are complementary to anonymous blocks.
Procedure:procedure name is
Function: function name return datatype is

variable：
Temporary storage of data
Manipulation of stored values
Reusability
Must start with a letter
Can include letters or numbers
Can include special characters (such as $, _, and # )
Must contain no more than 30 characters
Must not include reserved words
Declared and initialized in the declarative section
Used and assigned new values in the executable section
Passed as parameters to PL/SQL subprograms
Used to hold the output of a PL/SQL subprogram
follow naming conventions
Use meaningful and appropriate identifier
NOT NULL must sign with a value

Initialize:
assignment operator (:=)
DEFAULT reserved word
If you do not assign an initial value, the new variable contains NULL by default until you assign a value
Two objects can have the same name only if they are defined in different blocks.
Avoid using column name as identifiers(consider be referenced)
NOT NULL must be init with a clause: pincode VARCHAR2(15)NOT NULL := ‘Oxford’;

%TYPEAttribute:
Is used to declare a variable according to:
–  A database column definition
–  Another declared variable
Is prefixed with:
–  The database table and column names
–  The name of the declared variable
A NOT NULL database column constraint does not apply to variables that are declared using %TYPE.

Advantages of the %TYPE Attribute

• avoid errors caused by data type mismatch or wrong precision.
• avoid hard coding the data type of a variable.
• You need not change the variable declaration if the column definition changes.

Scalar Data Types:
Hold a single value
Have no internal components
four categories: number, character, date, and Boolean.
Character and number data types have subtypes that associate a base type to a constraint. For example, INTEGER and POSITIVE are subtypes of the NUMBER base type.

Boolean:
Ture/ false/ null

Bind variables:
-Created in the environment (host variables)
-Not in the declarative section of a PL/SQL block
-can be used/ manipulated by multiple subprograms
-Created with the VARIABLE keyword
-Used in SQL statements and PL/SQL blocks
-Accessed even after the PL/SQL block is executed
-Referenced with a preceding colon
-PRINT to display

Large objects(LOB)
store a large amount of data
A database column can be of the LOB category
Charcter CLOB
Binary BLOB
Binary BFILE
Stores a pointer inside the database
National language character LOB

Composite Data Type
A scalar type has no internal components.
A composite type has internal components that can be manipulated individually.
Composite data types (also known as collections) are: TABLE, RECORD, NESTED TABLE, and VARRAY types.
Use the TABLE data type to reference and manipulate collections of data as a whole object.
Use the RECORD data type to treat related but dissimilar data as a logical unit.