Oracle Pl/sql Language Basics

Pl/sql is Oracle's extension of the standard database language, Oracle has integrated PL/SQL into Oracle servers and other tools, and more developers and DBAs have started using Pl/sql in recent years, and this article covers Pl/sql basic syntax, architecture and components, And how to design and execute a pl/sql program.

Advantages of Pl/sql

Starting with version 6, Pl/sql is reliably integrated into Oracle, and once you have the benefits of Pl/sql and its unique data management convenience, it's hard to imagine Oracle missing Pl/sql. Pl/sql is not a stand-alone product, he is a technology that integrates into Oracle Server and Oracle tools, can treat pl/sql as an engine inside Oracle server, SQL statement performer handles a single SQL statement, Pl/sql engine handles pl/ SQL program block. When the PL/SQL program block is processed by the Pl/sql engine, the SQL statement executor in the Oracle server processes the SQL statements in the PL/SQL program block.

The advantages of Pl/sql are as follows:

. Pl/sql is a high-performance, transaction-based language that can run in any Oracle environment and support all data processing commands. Process SQL data definition and data control elements by using the PL/SQL program unit.

. Pl/sql supports all SQL data types and all SQL functions, while supporting all Oracle object types

. Pl/sql blocks can be named and stored in an Oracle server, and can be invoked by other PL/SQL programs or SQL commands, and any client/server tool can access the PL/SQL program and is well reusable.

. You can use Oracle data tools to manage the security of PL/SQL programs that are stored in the server. Ability to authorize or revoke access to Pl/sql programs by other users of the database.

. Pl/sql code can be written using any ASCII text editor, so it is very convenient for any operating system that Oracle can run

. For sql,oracle, each SQL statement must be processed at the same time, which means that each individual invocation must be handled by an Oracle server in a networked environment, which takes up a lot of server time and leads to network congestion. The pl/sql is sent to the server with the entire statement block, which reduces network congestion.

PL/SQL block Structure

Pl/sql is a kind of block structure language, the unit that composes Pl/sql program is logical block, a pl/sql program contains one or more logical blocks, each block can be divided into three parts. As with other languages, a variable must be declared before it is used, and Pl/sql provides a separate section specifically for handling exceptions, which describes the different parts of the Pl/sql block:

Declarations section (Declaration sections)

The Declarations section contains the data types and initial values of variables and constants. This section starts with the keyword declare, and if you don't need to declare variables or constants, you can ignore this part; What you need to say is that the declaration of the cursor is also in this section.

Executive part (executable section)

The execution part is the instruction part in the Pl/sql block, starting with the keyword begin, all executable statements are placed in this part, and other pl/sql blocks can also be placed in this section.

Exception handling (Exception section)

This section is optional, dealing with exceptions or errors in this section, and the detailed discussion of exception handling is done later.

Pl/sql block syntax

[DECLARE]
---declaration statements
BEGIN
---executable statements
[EXCEPTION]
---exception statements
END

Each statement in a pl/sql block must end with a semicolon, and the SQL statement can make more than one line, but the semicolon represents the end of the statement. You can have more than one SQL statement in a row, separated by semicolons. Each pl/sql block begins with begin or declare and ends with end. Comment by--marked.