Oracle PL/SQL Programming _ data types and defining variables and constants

-----------------------------------Basic Data Type-----------------------------------

1. Numeric type

Number (P,s)

The parameter P represents precision, and the parameter S represents the scale range.

Precision refers to the number of all valid digits in a numeric value, and the scale range refers to the number of decimal places to the right of the point.

Subtype is the type alias that is equivalent to number.

Subtypes: DEC, DECIMAL, DOUBLE, INTEGER, INT, NUMERIC, SMALLINT, Binary_integer, Pls_integer

2. Character types

Character types: VARCHAR2, CHAR, LONG, NCHAR, NVARCHAR2

These types of variables are used to store string or character data.

VARCHAR2 (maxlength)

The parameter maxlength indicates that the maximum length of the string can be stored, which must be given when the variable is defined (because the VARCHAR2 type has no default maximum length).

The maximum value of the parameter maxlength can be 32767 bytes.

CHAR (maxlength)

The maximum value of the parameter maxlength can be 32767 bytes.

The default maximum length for the CHAR type is 1 bytes. If the value assigned to the CHAR type variable is less than maxlength, then a space complement is used on the subsequent side.

3. Date type

DATE

4. Boolean type

BOOLEAN

The value of the variable can be one of TRUE, FALSE, or NULL.

-----------------------------------Special Data Types-----------------------------------

1.%TYPE type

Use the%TYPE keyword to declare a data type that is the same as the specified column name, which is usually immediately behind the specified column.

Cases:

Declares a variable that is exactly the same as the data type of the job column in the EMP table Var_job

Declare

Var_job Emp.job%type;

2. Record type, also known as "recording type"

Type Record_type is record

(

Var_member1 data_type [Not Null][:=default_value],

...

Var_membern data_type [not Null][:=default_value])

Record_type: Represents the name of the record type to be defined.

Var_member1: The name of the member variable that represents the record type.

Data_type: Represents the data type of the member variable.

Cases:

Declares a record type Emp_type, and then uses that type of variable to store a record information in the EMP table and output the record.

Declare

Type Emp_type is record--Declares the record type Emp_type

(

Var_ename varchar2,---Define field/member variables

Var_job varchar2 (20),

Var_sal number

);

Empinfo Emp_type; --Define variables

Begin

Select Ename,job,sal

Into Empinfo

From EMP

where empno=7369;

/* Output Employee information */

Dbms_output.put_line (' Employees ' | | empinfo.var_ename| | ' The position is ' | | empinfo.var_job| | ' wages are ' | | Empinfo.var_sal);

End

/

Output Result:

Employee Smith's job is clerk, salary is 2712.5

3.%rowtype type

Variables of type%rowtype combine the advantages of the%TYPE type and the RECORD type variable, which can be defined according to the structure of rows in a data table

A special data type used to store a row of data retrieved from a data table.

Rowvar_name Table_name%rowtype;

Rowvar_name: Represents a variable name that can store a row of data.

TABLE_NAME: The specified table name.

Cases:

Declares a variable rowvar_emp of type%rowtype, and then uses that variable to store a row of data in the EMP table and output it.

Declare

Rowvar_emp Emp%rowtype; --Define variables that can store an EMP table row of data

Begin

SELECT *

Into Rowvar_emp

From EMP

where empno=7369;

/* Output Employee information */

Dbms_output.put_line (' Employees ' | | rowvar_emp.var_ename| | ' The position is ' | | rowvar_emp.var_job| | ' wages are ' | | Rowvar_emp.var_sal);

End

/

Output Result:

Employee Smith's job is clerk, salary is 2712.5

-----------------------------------define variables and constants-----------------------------------

1. Defining variables

< variable name > < data type > [(length):=< initial value >];

Cases:

Var_countryname VARCHAR2 (50): = ' China ';

2. Defining constants

< constant name > constant < data type >:=< constant value >;

Cases:

CON_DAY constant integer:=365;

Oracle PL/SQL Programming _ data types and defining variables and constants