Introduction to the PL/SQL programming of Oracle Database

Facilitates the operation of client/server environment applications

For a client/server environment, the real bottleneck is on the network. No matter how fast the network, as long as the client and the server to do a large amount of data exchange, the efficiency of the application run back to be affected. If you are programming with PL/SQL, placing such an application with a large amount of data processing in the server execution will naturally eliminate the time it takes to transfer the data online.

Suitable for the customer environment

PL/SQL is divided into database PL/SQL and tools PL/SQL. For clients, PL/SQL can be nested within the appropriate tool, and the client program can execute the locally included PL/SQL section, or it can run to the service to send a command or to activate the server-side PL/E program.

Modular

PL/SQL Program structure is a highly descriptive, well-defined block structure, nested block structure, divided into separate processes, functions, triggers, and can be combined into a package, improve the program's modularity.

Process of

PL/SQL is a procedural extension of Oracle on standard SQL, allowing for the embedding of SQL statements within a PL/s program and allowing the use of various types of conditional branching and looping statements to share their solution across multiple applications.

Provides a large number of built-in packages

Oracle provides a large number of built-in packages that enable some of the lower-level operations and advanced features of DBS that are important to DBAs and application developers alike.

The handling of running errors

Using exception Handling (EXCEPTION) provided by PL/SQL, developers can focus on a variety of Oracle and PL/SQL errors, or deal with system errors and custom errors to enhance the robustness of the application.

In summary, the advantages of PL/SQL are:

Reduce network traffic and improve application performance
Good compatibility, different development tools can use the same PL/SQL script
Provides modular program development capabilities to simplify development and maintenance
Provides program control architecture, enhanced business processing capabilities
Provides exception handling to make PL/SQL programs more robust
Of course, in addition to these advantages, PL/SQL has other advantages, such as better performance, portability and compatibility, maintainability, ease of use and speed, and the ability to define identifiers to increase program readability.

Iii. PL/SQL blocks

The PL/SQL program consists of three parts, that is, the declaration part, the execution part, the exception processing part.

The syntax structure of a PL/SQL block:

DECLARE
--declaration section: this declares the variables, types and cursors used in PL/SQL, as well as local stored procedures and functions
BEGIN
--Execution part: process and SQL statement, which is the main part of the program
EXCEPTION
--Execution Exception section: Error handling
END;

PL/SQL blocks can be divided into:

Anonymous blocks: Blocks with no name, dynamically constructed, are directly executed blocks. Can be executed only once, other programs can be called, but cannot be called by other programs.
Named Block: A block with a name, which is a label. Named blocks can be divided into:
Subroutines: Stored procedures, functions, etc. stored in a database. They can be called in other programs when they are established on the database.
Triggers: When a database operation occurs, some events are triggered, which automatically executes the corresponding program.
Package: A set of subroutines, variable definitions stored in a database. A subroutine in a package can be called by another package or subroutine. However, if you declare a local subroutine, you can call that local subroutine only in the block that defines the local subroutine.

Introduction to the PL/SQL programming of Oracle Database