PL/SQL programming

Data Type:

Numeric type:
Number indicates a Variable Length Value column. The syntax is Number (p, s). p indicates the Number of digits of all valid numbers, and s indicates the Number of digits after the decimal point; the values of p and s are respectively p = 1 to 38, s =-84 to 127;
Valid digits: the first digit on the left is not 0, and the decimal point and negative number are not counted in the valid digits.

P> 0. There are three cases for s:
1. s> 0
Accurate to the second place to the right of the decimal point, and rounded. Then, check whether the valid digit is <= p; if s> p, at least s-p is filled with 0 at the right of the decimal point.
2. s <0
It is accurate to the second place on the left of the decimal point, and rounded up. Then, check whether the valid digit is <= p + | s |
3. s is 0 or not specified, rounded to the nearest integer.
Note: When p is less than s, it indicates that the number is a number whose absolute value is less than 1, and the first s-p digit from the right of the decimal point must be 0, and the second decimal point is reserved.

For example
Value Datatype Stored Value
123.2564 NUMBER 123.2564
1234.9876 NUMBER (6) 1235
1234.9876 NUMBER (1234.99)
12345.12345 NUMBER () Error
12345.345 NUMBER (5,-2) 12300
1234567 NUMBER (5,-2) 1234600
Error 12345678 NUMBER (5,-2)
12345.58 NUMBER (*, 1) 12345.6
0.1 NUMBER () Error
0.01234567 NUMBER (0.01235)
0.09999 NUMBER (0.09999)
0.099996 NUMBER () <>

The usage is as follows:
Create table t_n (id number (5, 2 ));
Insert into t_n values (123.455 );
Insert into t_n values (1.234 );
Insert into t_n values (. 001 );
Select * from t_n:

ID
123.46
1.23
0.00

The Number type stores real numbers. PLS_Integer and BINARY_Integer can only store integers.

Character Type:

Char indicates a fixed-length string. The syntax is Char (L) and L is optional. If no L value is specified, the default value is 1. the maximum length is 32767.

VarChar2 is used to store variable strings. The syntax VarChar2 (L), L is required. The maximum length is 32767.

Boolean Type:

Valid values of the Boolean type are True, False, and Null.

Type conversion:

To_Char: converts the Number and Date types to the Varchar2 type;

To_Date: Convert the Char type to the Date type;

To_Number: Convert the Char type to the Number type;

Variables and constants:

Variable name: 1. It must start with a letter; 2. It can be followed by one or more letters and numbers (0 ~ 9), special characters $, #, or _;

3. The variable length cannot exceed 30 characters; 4. The variable name cannot contain spaces.

1. Variable declaration:

DECLARE
V_StudentName VARCHAR2 (20 ),
V_CurrentDate DATE;
V_NumberCredits NUMBER (3 );

2. Custom Data Types:

DECLARE
TYPE t_StudentRecord is record (FirstName Varchar2 (10), LastName Varchar2 (10 ),

CurrentCredits NUMBER (3 ));
V_Student t_StudentRecord;

3. variable attributes:

The following describes the definitions of several common Composite data type variables.
(1) Use % type to define variables
To make the variable types in PL/SQL consistent with the data types of fields in the data table, Oracle 9i provides the % type definition method. In this way, when the field type of the data table is modified, the corresponding variable type in the PL/SQL program is also automatically modified. As follows:
Declare
Mytable emp. empno % type;
Begin
Select a. empno into mytable
From emp
Where a. ename = 'Scott ';
Dbms_output.put_line (mytable );
End;

(2) Use % rowtype to define variables
Use % type to get the data type of the field for the variable, and use % rowtype to get the data type of the entire record for the variable.

Compare the definitions of the two: variable name data table. Column name % type, variable name data table % rowtype.
Declare
Mytable emp % rowtype;
Begin
Select * into mytable
From emp
Where a. ename = 'Scott ';
Dbms_output.put_line (mytable. empno | ''| mytable. job );
End;

Process control:

 

To be continued...