Oracle PL/SQL language basics (1)

PL/SQL is an extension of Oracle's standard database language. Oracle has integrated PL/SQL into ORACLE servers and other tools, in recent years, more developers and DBAs have started to use PL/SQL. This article describes basic PL/SQL syntax, structures and components, and how to design and execute a PL/SQL program.

Advantages of PL/SQL

PL/SQL has been reliably integrated into ORACLE since version 6. Once you master the advantages of PL/SQL and its unique data management convenience, it is hard to imagine that Oracle lacks PL/SQL. PL/SQL is not an independent product. It is a technology integrated into ORACLE servers and oracle tools. It can regard PL/SQL as an engine in Oracle servers, the executor of the SQL statement processes a single SQL statement, and the PL/SQL engine processes the PL/SQL block. When the PL/SQL block is processed by the PL/SQL engine, the SQL statement executor on the Oracle server processes the SQL statements in the PL/SQL block.

PL/SQL has the following advantages:

. PL/SQL is a high-performance transaction-based language that runs in any Oracle environment and supports all data processing commands. Use PL/SQL program units to process SQL data definitions and data control elements.

. PL/SQL supports all SQL data types and all SQL functions, and all Oracle Object Types

. PL/SQL blocks can be named and stored on the Oracle server, and can also be called by other PL/SQL programs or SQL commands. Any client/server tool can access the PL/SQL program, it has good reusability.

. You can use ORACLE data tools to manage the security of PL/SQL programs stored on servers. Other database users can be authorized or revoked to access PL/SQL programs.

. PL/SQL code can be written in any ASCII text editor, so it is very convenient for any operating system that Oracle can run.

. For SQL, Oracle must process each SQL statement at the same time. In the network environment, this means that each independent call must be processed by the Oracle server, this takes a lot of server time and causes network congestion. PL/SQL sends the entire statement block to the server, which reduces network congestion.

PL/SQL Block Structure

PL/SQL is a block-structured language. The unit of a PL/SQL program is a logical block. a pl/SQL program contains one or more logical blocks, each block can be divided into three parts. Like other languages, variables must be declared before they are used. PL/SQL provides an independent part dedicated to exception handling. The following describes the different parts of PL/SQL blocks:

Declaration Section)

The declaration part contains the data types and initial values of variables and constants. This part starts with the keyword declare. If you do not need to declare variables or constants, you can ignore this part. It must be noted that the declaration of the cursor is also in this 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 part.

Exception Section)

This part is optional. In this part, we will discuss Exception Handling in detail.

PL/SQL block syntax

[Declare]
--- Declaration statements
Begin
--- Executable statements
[Exception]
--- Exception statements
End


Each statement in the PL/SQL block must end with a semicolon. The SQL statement can contain multiple rows, but the semicolon indicates the end of the statement. A row can contain multiple SQL statements separated by semicolons. Each PL/SQL block starts from begin or declare and ends with end. Annotation is marked.

Naming and anonymity of PL/SQL Blocks

The PL/SQL block can be a named block or an anonymous block. Anonymous blocks can be used on servers or clients.

The named program block can appear in the declaration part of other PL/SQL program blocks. It is obvious that the subroutine can be referenced in the execution part or in the exception handling part.

The PL/SQL block can be compiled independently and stored in the database. Any database-connected application can access the PL/SQL block. Oracle provides four types of stored programs:

. Function

. Process

. Package

. Trigger

Function

The function is a named PL/SQL block stored in the database. The function accepts zero or multiple input parameters and has a return value. The data type of the returned 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 block that accepts zero or multiple parameters as input or output, or as both input and output (inout ), unlike functions, stored procedures do not return values. stored procedures cannot be directly used by SQL statements. They can only be called using the execut command or within the PL/SQL block. The syntax for defining stored procedures is as follows:

Procedure name [(parameter [, parameter,...])] is
[Local declarations]
Begin
Execute statements
[Exception
Exception handlers]
End [name]


Package)

A package is actually a collection of related objects that are combined. When any function or stored procedure in the package is called, the package is added to the memory, sub-program access to any function or stored procedure in the package will be greatly accelerated.
A package consists of two parts: the specification and the package body. The specification describes variables, constants, cursors, and subprograms. The package body fully defines subprograms and cursors.

Trigger)

A trigger is associated with a table or database event. When a trigger event occurs, the trigger defined in the table is triggered.

Variables and constants

The variable is stored in the memory to obtain the value. It can be referenced by PL/SQL blocks. You can think of variables as a container that can store things. Things in the container can be changed.

Declare Variables

Variables are generally declared in the declaration part of PL/SQL blocks. PL/SQL is a strong type language, which means that it must be declared before referencing variables, to use variables in execution or exception handling, the variables must be declared in Declaration first.

The syntax for declaring variables is as follows:

Variable_name [constant] databyte [not null] [: = | default expression]


Note: When declaring a variable, you can add a not null constraint to the variable. At this time, the variable must be assigned a value during initialization.

Assign values to variables

There are two ways to assign values to variables:

Directly assign values to variables

X: = 200;
Y = Y + (x * 20 );

. Assign values to variables through SQL select into or fetch

Select sum (salary), sum (salary * 0.1)
Into total_salary, tatal_commission
From employee
Where dept = 10;

Constant

A constant is similar to a variable, but the value of a constant cannot be changed within the program. The value of a constant is assigned when it is defined. Its declaration method is similar to that of a variable, but it must include the keyword constant. Constants and variables can be defined as SQL and user-defined data types.

Zero_value constant number: = 0;


This statement defines a constant named zero_value, whose data type is number, and whose value is 0.

Scalar Data Type

Scalar data types have no internal components. They can be divided into the following four categories:

. Number
. Character
. Date/time
. Boolean

Table 1 shows the numeric data type, table 2 shows the character data type, and table 3 shows the date and boolean data types.

Table 1 scalar types: Numeric

Datatype
Range
Subtypes
Description

Binary_integer
-214748-2147483647
Natural
Natural
Npositive
Positiven
Signtype
Used to store single-byte integers.
The storage length must be less than the number value.
Subtype used to limit the range ):
Natural: used for non-negative numbers
Positive: only used for positive numbers
Natura1n: used only for non-negative and non-null values
Positiven: used only for positive numbers, not for null values
Signtype: only values:-1, 0, or 1.

Number
1.0e-130-9.99e125
Dec
Decimal
Double
Precision
Float
Integeric
Int
Numeric
Real
Smallint
Stores numeric values, including integers and floating-point numbers. You can select the precision and scale methods. Syntax:
Number [([,])].
The default precision is 38, And the scale is 0.

Pls_integer
-2147483647-2147483647

It is basically the same as binary_integer, but when using machine operations, pls_integer provides better performance.


Table 2-character data type

Datatype
Rang
Subtype
Description

Char
Maximum length: 32767 bytes
Character
Storage of fixed-length strings. If the length is not determined, the default value is 1.

Long
Maximum length: 2147483647 bytes

Store variable-length strings

Raw
Maximum length: 32767 bytes

It is used to store binary data and byte strings. When transmitted between two databases, raw data is not converted between character sets.

Longraw
Maximum length 2147483647

Similar to the long data type, it cannot be converted between character sets.

Rowid
18 bytes

The row identifier is the same as the row ID pseudo-column type in the database. A Row identifier can be stored as a unique key value for each row in the database.

Varchar2
Maximum length: 32767 bytes
Stringvarchar
Similar to the varchar data type, it stores variable-length strings. The declaration method is the same as that of varchar.


Table 3 date and Boolean

Datatype
Range
Description

Boolean
True/false
The storage logic value is true or false, with no parameter.

Date
01/01/4712 BC
Store fixed date and time values. The date value contains the time.
Lob Data Type

Lob (large object, large object) data types are used to store large data objects such as images and sounds. lob data objects can be binary data or character data, the maximum length cannot exceed 4 GB. The lob data type supports any access method, and long only supports sequential access. Lob is stored in a separate location, and a "Lob locator" (lob Locator) is stored in the original table. This locator is a pointer to the actual data. To operate lob Data Objects in PL/SQL, use the dbms_lob.lob package provided by Oracle. The data types can be divided into the following four types:

. Bfile
. Blob
. Clob
. Nclob

Operator

Like other programming languages, PL/SQL has a series of operators. Operators are divided into the following types:

. Arithmetic Operator

. Relational operators

. Comparison operator

. Logical operators

Arithmetic Operators are shown in table 4.

Operator
Operation

+ Add
-Subtraction
/Division

* Multiplication
** Multiplication party


Relational operators are mainly used for condition judgment statements or for where substrings. Relational operators check whether the conditions and results are true or false. Table 5 is the Relational operators in PL/SQL.

Operator
Operation

<