Oracle PL/SQL

Document directory

• 1. Define Constants
• 2. Basic Data Types
• 3. define basic data types
• 4. Composite data types
• 5. Define one-dimensional table type variables
• 9 Functions
• 10. Process Control
• 11. Cycle Control
• 12. Transaction Processing
• 13 cursor
• 14 Process
• 15 series

1. Define Constants

Constant name constant type identifier [not null]: = Value

2. Basic Data Types

Number Type
Int Integer type
Pls_integer Integer type. An error occurs when overflow occurs.
Binary_integer Integer type, indicating a signed integer
The maximum length of a char character is 255.
Varchar2 variable-length memory type, up to 2000
Long variable characters, up to 2 GB
DATA date type
Boolean (true, false, null)

3. define basic data types

Variable name type identifier [not null]: = Value

4. Composite data types

1. Use % type to define variables
Mydata tempuser. Test. curr % Type
Indicates that a field of the same type as that of the test and curr fields in the data table is defined.
2. Record type data type
Type myrecord is record (A int, B data );
Srecord myrecord;
3. Use % rowtype to get the structure of a table.
Mytable text % rowtype
Obtain the same table structure as the text record.

5. Define one-dimensional table type variables

Type table type is table of type index by binary_integer; // defines the type.
Table variable name table type; // defines the value
6. | concatenate string Operators
7. You can use count. Delete. First. Last. Next. exists. Prior to define table variables.
8 expression
+-*/** Multiplication ......

9 Functions

To_char converts other types to character type
To_date converts other types to date types
To_number converts other types to numerical types

10. Process Control

1) if condition then statement segment end if;
2) If condition then statement segment 1 else statement Segment 2 end if;

11. Cycle Control

1) loop statement segment
If exit condition then
Exit;
End if;
End Loop
2) loop statement
Exit When exit condition
End loop;
3) while condition Loop
Execution statement segment
End Loop
4) for loop variable in loop lower bound... loop upper bound Loop
Loop processing segment
End loop;

12. Transaction Processing

Commit transaction command
Use set auto on to enable auto submit
Disable Automatic submission with set Auto Off
Rollback transaction rollback command
Serverpoint save point command
Create a save point: savepoint: Save the name;
Rollback save point: rollback to save the name;

13 cursor

1. Define a cursor
Cursor cursor name is SELECT statement;
2. Open the cursor
Open cursor name
3. Extract cursor data
Fetch cursor name into variable 1, variable 2 ....;
Or fetch cursor name into record type variable
4. Close the cursor

14 Process

1. creation process
Create or replace procedure process name
Declaration statement segment;
Begin
Execution statement segment;
Exception
Exception Handling statement segment
End;
2. Process with Parameters
In parameter: Read parameter. The main program passes the parameter to the process.
Out parameter: Read the parameter. The process passes the parameter to the main program.
In out parameter: bidirectional Parameter

15 series

Nextval