First, what is Pl-sql
Pl-sql is an extended language that combines the Oracle process language with the Structured Query Language (SQL). Specifically, Pl-sql is based on ordinary SQL statements to add the characteristics of the programming language, the data operations and query statements organized in the PL-SQL Code of the process unit, through logical judgment, loop, and other operations to achieve complex functions or computational programming language.
Using Pl-sql has the following advantages:
1, the ability to put a set of SQL statements in a module. Make it more modular.
2, can increase the logical structure judgment, the circulation and so on the Pl-sql the program structure.
3, has the exception processing function. So that the program does not break.
4, reduce network interaction, improve performance.
5, have better transplant sex.
Second, PL-SQL program block
The PL-SQL program block consists of five parts, a declaration section, a program section beginning, a code body part, an exception section, and a program end.
DECLARE -- Declarations Section Declaration of variables, constants, cursors, user-defined exceptions ... . BEGIN -- Program start Section SQL statements and PL-SQL statements constitute the execution statement-the code body part ... When an exception occurs in the EXCEPTION program, the exception is caught and the exception is handled. --Abnormal part ... END; -- Program End Section
Attention:
1, where the declaration part and the exception part is optional.
2, Delcare,begin and exception have no semicolons, and end and all other PL-SQL statements must be terminated with semicolons!
Iii. Declaration and assignment of variables
1. Define the syntax of the variable:
DECLARE variable_name Type[not Null][:=value]
DECLARE: Defines the keyword used by the variable.
Variable_name: The name of the variable.
Type: Types of variables.
[NOT NULL]: variable is not allowed to be empty.
[: =value]: Assigns the initial value to the variable.
Example:
DECLARE varchar2(); --Declaring a variable of a string type number:=&age; --Declaring a variable of a numeric type &age represents the input data, and a temporary pop-up input box lets us enter the data.
Variable naming rules in Pl-sql:
1. The first letter of the variable name must be an English letter, followed by letters, numbers, underscores, #,$.
2. The variable name is not more than 30 characters in length.
3. The variable name cannot have spaces.
4. You cannot use keywords to name them.
Attention:
The declaration of a variable is valid only in the current program block.
Pl-sql Programming Basics