PL/SQL is an extension of Oracle's standard database language, Oracle has consolidated PL/SQL into Oracle servers and other tools, and more developers and DBAs have started using PL/SQL in recent years, and this article will cover the PL/SQL basic syntax. Structures and components, and how to design and execute a PL/SQL program.
Benefits of PL/SQL
since version 6 PL/SQL has been reliably integrated into Oracle, it is hard to imagine that Oracle is missing PL/SQL when it has the benefits of PL/SQL and the convenience of its unique data management. PL/SQL is not a standalone product, it is a technology integrated into Oracle servers and Oracle tools that can treat PL/SQL as an engine within an Oracle server, and a single SQL statement processor to handle pl/ SQL program block. The SQL statement executor in the Oracle server processes the SQL statements in a PL-SQL program block when the PL/SQL program block is processed in the PL/SQL engine.
the advantages of PL/SQL are as follows:
. PL/SQL is a high-performance transactional-based language that can run in any Oracle environment and support all data processing commands. The data definition and data control elements of SQL are processed by using the PL/SQL program unit.
. PL/SQL supports all 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 by the command of an application that can be accessed by any client/server tool, with good reusability.
. You can use Oracle data tools to manage the security of PL/SQL programs stored on the server. The ability of other users of the database to access PL/SQL programs can be authorized or revoked.
. PL/SQL code can be written using any ASCII text editor, so it is convenient for any operating system that Oracle can run
for sql,oracle to process each SQL statement at the same time, in a networked environment this means that each individual call must be handled by the Oracle server, which consumes a lot of server time and causes the network to be congested. PL/SQL is sent to the server with the entire statement block, which reduces network congestion.
PL/SQL block structure
PL/SQL is a block-structured language, and the unit that makes up PL/SQL programs is a logical block, and a PL/SQL program contains one or more logical blocks, each of which can be divided into three parts. As with other languages, variables must be declared before they are used, and PL/SQL provides separate parts specifically for handling exceptions, which describe the different parts of the PL/SQL block:
Declaration part (Declaration section)
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; It is important to note that the declaration of the cursor is also in this section.
Execution part (executable section)
The execution section is the instruction portion of the PL/SQL block, starting with the keyword begin, where all executable statements are placed, and other PL/SQL blocks can be placed in this part.
Exception Handling sections (Exception section)
This section is optional, dealing with exceptions or errors in this section, and we'll take a detailed discussion of exception handling.
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, which can make multiple lines, but the semicolon represents the end of the statement. There can be more than one SQL statement in a row, separated by semicolons. Each PL/SQL block starts with a begin or declare and ends with end. Note by--mark.
the naming and anonymity of PL/SQL blocks
the PL/SQL program block can be a named program block or an anonymous block. Anonymous blocks can be used on the server side as well as on the client.
A named program block can appear in the Declarations section of other PL/SQL program blocks, which is obviously a subroutine that can be referenced in the execution section or in the Exception handling section.
PL/SQL blocks can be independently compiled and stored in a database, and any application connected to the database can access these stored PL/SQL blocks. Oracle provides four types of stored programs:
. function
. Process
. Package
. Trigger
function
A function is a named PL/SQL program block that is stored in a database. The function accepts 0 or more input parameters, has a return value, and the data type of the return value is defined when the function is created. The syntax for defining a function is as follows:
FUNCTION name [{parameter[,parameter,...])] RETURN Datatypes is
[local declarations]
BEGIN
Execute Statements
[EXCEPTION
Exception handlers]
END [name]
Process
A stored procedure is a PL/SQL program block that accepts 0 or more parameters as input or outputs (output), or both as input and output (INOUT), unlike functions, where stored procedures do not have return values and stored procedures cannot be used directly by SQL statements. The syntax for defining stored procedures can be called only through the execut command or the PL/SQL program block:
PROCEDURE name [(Parameter[,parameter,...]) is
[local declarations]
BEGIN
Execute Statements
[EXCEPTION
Exception handlers]
END [name]
Packages (Package)
Java Oracle Stored Procedure issues
