Pl-sql Programming Basics

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