2.pl/sql--pl/sql Overview and its basic structure

first, what is PL/SQL

PL/SQL is an extension of the language.

The SQL language is a non-procedural language in which the user simply tells the computer what to do without needing to understand how the computer is doing it. For example, users want to query a table of a column of data, only need to input:selectcolunm_name from table_name where CONDITION; you can, without needing to know how the computer is queried. This is both an advantage and a disadvantage, the advantage is that the language is very easy to learn, the disadvantage is the lack of process control, limiting its function, such as the if...else can not be judged by the conditions, and can not be done for the loop operation.

based on The limitations of the SQL language, hence the birth of PL/C. the PL/SQL language provides the functions of conditional judgment, looping, and other common programming languages. the SQL language is a standard language, such as Oracle,MySQL,sql_server, and so on, while PL- SQL is Oracle Company Proprietary, it will process control and The SQL language is seamlessly combined to enhance the functionality of the SQL language.

ii. PL/SQL the function

the features of PL/ SQL include:

1. PL/SQL provides a block structure, which enables a modular design. Any programming language provides basic structures such as functions, processes, and function processes that are modular in a large program, making it easier to develop and maintain,andPL/SQL is no exception.

2. PL/SQL provides a number of procedural constructs:

A. variables (Variables), Constants (Constants), and multiple data types;

B. provide conditional branch judgment and cyclic structure;

C. provides a compile-time, multiple-run unit.

iii. PL/SQL the implementation mechanism

PL/SQL statements are executed in two parts, andthe SQL language part is executedby the SQL engine in the Oracle database kernel ; the PL/SQL blocks are pl/ SQL engine to execute.

The SQL engine is always located inside the Oracle database,and the PL/SQL engine can reside inside the Oracle database or independently of the Oracle database. the PL/SQL engine that exists independently of the Oracle database is provided by some database development tools and executed on the client side rather than on the server.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/45/41/wKiom1PlNwmA_a-zAAEU6Rsr06Y709.jpg "title=" 1.png " alt= "Wkiom1plnwma_a-zaaeu6rsr06y709.jpg"/>

Iv. PL/SQL The advantages

the main advantages of PL/ SQL are:

1. The integration of some process Control in the SQL language;

2. Improved performance,

if the simple use of SQL language, each execution of an SQL statement requires that the result be returned to the client, which is branched by the client based on the return value, and then executes the next SQL statement, which generates a lot of I/O operations.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/45/41/wKiom1PlNxqiqEUsAABUHBrtvoY492.jpg "title=" 2.png " alt= "Wkiom1plnxqiqeusaabuhbrtvoy492.jpg"/>

in correspondence with, use The PL/SQL language, which can be packaged as a whole by the client's business code to be queried , is sent to the Oracle database at once, and even many business logic statements are integrated into the core of the Oracle database. The client only needs to be called. The database will perform all operations on the server and return the results to the client at once, thus reducing the transmission of network data.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/45/42/wKioL1PlOEHgrjRbAAB0Jide8rE410.jpg "title=" 3.png " alt= "Wkiol1ploehgrjrbaab0jide8re410.jpg"/>

3. support of modular programming;

4. Many of Oracle 's development tools support the development of PL/SQL language;

5. portability (primarily porting between Oracle databases, which can be difficult to migrate across databases, such as MySQL and Oracle)

6. support exception handling.

v. PL/SQL The block structure

the basic unit of PL/SQL language is the block, which is divided into the following four parts:

DECLARE (optional) --declaration Section

This section mainly defines the following: variables ( Variables), constant (Constant), pointer (cursor), user-defined exception (user-definedexceptions), andso on.

BEGIN ( Mandatory )--Business logic Section

This section mainly stores the business logic:

SQL Statements

PL/SQL statements

EXCEPTION (optional) --Exception handling Section

This section mainly deals with exceptions.

END; ( Mandatory )--End part

Note that theBEGIN and END These two parts are a must, and the DECLARE and EXCEPTION These two sections are optional and only in the END There's a semicolon at the end, no other place. .

Vi. Types of blocks

PL /SQL blocks have the following three main types:

A. anonymous block (Anonymous)

an anonymous block is a block that is not named, and it is not stored in in the Oracle database, it is invalidated once it has been executed and cannot be reused; it is mainly used for writing tests and temporary use of statements, which are structured as follows:

[DECLARE]

BEGIN

--statements

[EXCEPTION]

END;

B. process block (Procedure)

the process block requires a name, where the PROCEDURE part corresponds to the DECLARE part of the anonymous block . The names of the blocks (Procedure and Function) can be stored in the database repeatedly called, used in a more formal environment, its structure is as follows:

PROCEDURE name

Is

BEGIN

--statements

[EXCEPTION]

END;

C. function blocks (functions)

a function block also has a name, which differs from a procedure block in that it must return data with the following structure:

FUNCTION name

RETURN datatype

Is

BEGIN

--statements

RETURN value;

[EXCEPTION]

END;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/45/42/wKioL1PlOFuAw5AzAAEQSpSY9TY394.jpg "title=" 4.png " alt= "Wkiol1plofuaw5azaaeqspsy9ty394.jpg"/>

vii. PL/SQL The program build

PL/SQL programs can be built on the client or built on the server side, and it is more common to build on the server side, because if you build on the client, you also need to use the additional development tools provided by Oracle.

built on the client the main contents of PL/SQL programs are: Anonymous blocks, procedures, functions, packages (package is the integration of functions and processes), triggers and some other types of elements;

built on the service side The main contents of the PL/SQL program are: Anonymous blocks, stored procedures and functions, packages, triggers, and some other types of elements.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/45/41/wKiom1PlN16h46JtAAHM8LgwBJg479.jpg "title=" 5.png " alt= "Wkiom1pln16h46jtaahm8lgwbjg479.jpg"/>

This article is from the "Big sword without front of the great Qiao Not Work" blog, please make sure to keep this source http://wuyelan.blog.51cto.com/6118147/1537687