Basic Oracle PL/SQL knowledge and related concepts

Structured Query Language (SQL) is a common Language used to access relational databases. It is a fourth-generation Language (4GL) and its execution features are non-procedural, that is, you do not need to specify the specific method and method of execution, but simply call the corresponding statement to directly obtain the result. Obviously, this language that does not focus on any implementation details is very convenient for developers. However, some complex business processes require corresponding programs to describe. In this case, 4GL is powerless. Oracle L/SQL is designed to solve this problem. Oracle PL/SQL is a procedural language and belongs to the third generation language, similar to C, C ++, Java, and other languages, it focuses on processing details and can be used to implement complicated business logic.

I. Basic programming knowledge

1. Program Structure
Oracle PL/SQL programs are based on block blocks. The entire Oracle PL/SQL block consists of three parts: the declaration part starts with declare) and the execution part starts with begin) and exception Handling ). The execution part is required, and the other two parts are optional. The basic structure of Oracle PL/SQL block is composed of three parts, regardless of the size of the Code.

2. Control Structure
The Oracle PL/SQL program segment has three program structures: Conditional structure, cyclic structure, and sequential structure.
1) condition Structure
Similar to other languages, the syntax structure is as follows:
If condition then
Statement1
Else
Statement2
End if;
2) Loop Structure
This structure is not the same as other languages. There are three types of loop structures in PL/SQL programs:
A. loop... End loop;
B. while condition loop... End loop;
C. for variable in low_bound... upper_bound loop... End loop;
"…" Indicates the loop body.
3) Ordered Structure
Actually, it is the use of goto. However, from the perspective of program control, using less goto can make the program structure clearer.

3. Variable declaration and assignment
Oracle PL/SQL is mainly used for database programming. Therefore, all its data types correspond to the Field Types in the Oracle database in a one-to-one manner, which can be divided into numeric, Boolean, numeric, and date types. Here we will briefly introduce two common data types: number and varchar2.
1) number is used to store integers and floating-point numbers. The value range is 1e130 ~ 10e125, with the Syntax: number [(precision, scale)]
(Precision, scale) is optional. precision indicates the number of all digits, and scale indicates the number of digits on the right of the decimal point.
2) varchar2 is used to store variable-length strings. Its syntax is: varchar2 [(size)]
The size parameter is optional, indicating the maximum length that the string can store.

Declared variables in Oracle PL/SQL are not the same as those in other languages. They are declared from right to left. For example, to declare a variable v_id of the number type, the form should be: v_id nunmer;
If you assign a value to the above v_id variable, you cannot use "=". You should use ": =" in the form of: v_id: = 5;

4. Basic SQL commands
PL/SQL uses the database operation language based on SQL. Therefore, familiarity with SQL is the basis for Oracle PL/SQL programming. The SQL language classification is roughly as follows:
1) Data Definition Language (DDL): Create, Drop, Grant, Revoke ,...
2) Data Control Language (DML): Update, Insert, Delete ,...
3) Data Control Language (DCL): Commit, Rollback, Savapoint ,...
4) Others: Alter System, Connect, Allocate ,...
For more information about the specific syntax structure, see SQL language.

Ii. Process and Function

The procedures and functions in Oracle PL/SQL are the same as those in other languages. They are combined to execute certain tasks. The process has no return value, and the function has a return value.

Its syntax structure is:
Procedure: Create or replace procedure procname (parameter list) as PL/SQL statement Block
Function: Create or replace function funcname (parameter list) return value as PL/SQL statement Block

Iii. cursor

A cursor is defined as a result set returned by a dml SQL operation. That is, when a query operation on a database returns a set of result sets, it uses a cursor to mark this set of result sets. Later, it obtains data information in the result set through the cursor operation. The cursor concept is put forward here because it is very important in PL/SQL programming. The syntax structure for defining the cursor is as follows: cursor cursor_name is SQL statement;

IV. Other Concepts

The concept in Oracle PL/SQL is very important. It is mainly to encapsulate a group of processes and functions with similar functions, similar to the concept of namespace in Object-Oriented.
A trigger is a special stored procedure. It is called only when a specific event occurs. It is mainly used for message notifications between multiple tables.

1. How to adjust Oracle SQL Performance
2. Oracle PL program process example
3. Oracle EM practices for managing MySQL
4. How can I use Oracle SQL to communicate?
5. Relationship between Oracle adjustment and SQL statement Optimization