Repost Oracle Pl/sql Language Basics

Oracle|sql language []oracle pl/sql Language Foundation

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.

Naming and anonymity of pl/sql blocks

The PL/SQL program block can be either a named program block or an anonymous program block. Anonymous program blocks can be used on the server side or on the client.

Named program blocks can appear in the Declarations section of other pl/sql blocks, which is more obvious than subroutines, which can be referenced in the execution part or in the Exception handling section.

The PL/SQL program block can be independently compiled and stored in the database, and any application connected to the database can access the 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 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 program block that accepts 0 or more parameters as input (input) or output (output), or both as input and output (INOUT), unlike functions where stored procedures do not return values and stored procedures cannot be directly used by SQL statements. The syntax for defining stored procedures is as follows, 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]


Package (Package)

A package is actually a collection of related objects that are grouped together, and when any function or stored procedure in a package is invoked, the package is loaded into memory, and the access speed of any function or stored procedure in the package is greatly accelerated.
The package consists of two parts: the specification and the package body, the specification describes variables, constants, cursors, and subroutines, and the package body fully defines subroutines and cursors.

Triggers (trigger)

Triggers are associated with a table or database event, and triggers that are defined on the table are triggered when a trigger event occurs.

Variables and constants

Variables are stored in memory to obtain values that can be referenced by pl/sql blocks. You can think of a variable as a container that can store things, and things in a container can be changed.

declaring variables

Variables are generally declared in the Declarations section of the Pl/sql block, and Pl/sql is a strong type language, which means that the variable must be declared first in the declaration section before it can be referenced in the execution or exception handling section.

The syntax for declaring variables is as follows:

variable_name [CONSTANT] databyte [Not null][:=| DEFAULT expression]


Note: You can impose a not-null constraint on a variable while declaring a variable, at which point the variable must be assigned when it is initialized.

Assigning values to variables

There are two ways to assign a value to a variable:

. Assigning values directly to variables

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

. Assigning values to variables through SQL SELECT into or fetch into

SELECT sum (SALARY), sum (salary*0.1)
Into Total_salary,tatal_commission
From EMPLOYEE
WHERE dept=10;

Constant

Constants are similar to variables, but the value of a constant cannot be changed within a program, and the value of a constant is given when defined, and he declares the same way as a variable, but must include the keyword constant. Both constants and variables can be defined as SQL and user-defined data types.

Zero_value CONSTANT number:=0;


This statement has a constant named Zero_value, the data type is number, and the value is 0.

Scalar (scalar) data type

Scalar (scalar) data types do not have internal components, and they can be roughly grouped into the following four categories:

. Number
. Character
. Date/time
. Boolean

Table 1 shows the numeric data types, table 2 shows the character data types, 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 a single byte integer.
Required storage length lower than number value.
Subtype used to limit scope (subtype):
NATURAL: for non-negative numbers
POSITIVE: only for positive numbers
NATURALN: For non-negative and non-null values only
Positiven: For positive numbers only, 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 choose the precision and scale method, syntax:
Number[([,])].
The default precision is 38,scale is 0.

Pls_integer
-2147483647-2147483647

Basically the same as Binary_integer, but Pls_integer provides better performance when machine operations are used.


Table 2 Character data types

DataType
Rang
Subtype
Description

CHAR
Maximum length 32767 bytes
CHARACTER
Store a fixed-length string, if the length is not determined, the default is 1

LONG
Maximum length 2147483647 bytes

Storing variable-length strings

RAW
Maximum length 32767 bytes

Used to store binary data and byte strings, and when passed between two databases, raw data is not converted between character sets.

Longraw
Maximum length 2147483647

Similar to a Long data type, it is also not possible to convert between character sets.

ROWID
18 bytes

As with the database rowid pseudo column type, the ability to store a row designator can be considered a unique key value for each row in the database.

VARCHAR2
Maximum length 32767 bytes
Stringvarchar
Similar to varchar data types, stores variable-length strings. Declaring methods the same as varchar


Table 3 Date and Boolean

DataType
Range
Description

BOOLEAN
True/false
Store logical value TRUE or FALSE, no parameters

DATE
01/01/4712 BC
Store fixed-length date and time values, date values contain time


LOB data type

LOB (Large object, Large object) data type is used to store large data objects such as images, sounds, and LOB data objects can be binary or character data with a maximum length of no more than 4G. LOB data types support arbitrary access, and long only supports sequential access. The LOB is stored in a separate location, while a lob locator (LOB locator) is stored in the original table, which is a pointer to the actual data. Manipulating LOB Data Objects in Pl/sql using the package Dbms_lob provided by Oracle. LOB data types can be grouped into the following four categories:

. BFILE
. Blob
. Clob
. NCLOB

Operator

As with other programming languages, Pl/sql has a series of operators. The operators are grouped into the following categories:

. Arithmetic operator

. Relational operators

. comparison operator

. logical operators

The arithmetic operators are shown in table 4

operator
Operation

+ Plus
-Minus
/except

* Multiply
* * the exponentiation


Relational operators are used primarily for conditional judgment statements or for a where substring, the relational operator checks whether the condition and the result are true or false, and table 5 is the relational operator in Pl/sql

operator
Operation

<
Less-than operator

<=
Less than or equal to operator

>
Greater than operator

>=
Greater than or equal to operator

=
equals operator

!=
Not equal to operator

<>
Not equal to operator

:=
Assignment operator


Table 6 shows the comparison operator

operator
Operation

Is NULL
Returns true if the operand is null

Like
Comparing String values

BETWEEN
Verify that the value is within range

In
Verify that the operand is in a set number of values


Table 7.8 shows the logical operators

operator
Operation

and
All two conditions must be met

OR
As long as one of the two conditions is met

Not
Take the counter


Executive part

The execution section contains all the statements and expressions, the execution begins with the keyword begin, ends with the keyword exception, and ends with the keyword End If exception does not exist. Semicolons separate each statement, using the assignment operator: = or SELECT INTO or fetch into to assign values to each variable, and the execution part of the error will be resolved in the Exception handling section, and another PL/SQL program block can be used in the execution section, which is called a nested block

All SQL data manipulation statements can be used in the execution section, and the Pl/sql block can no longer display the output of the SELECT statement on the screen. The SELECT statement must include an into substring or a part of the cursor, and the variables and constants used by the execution must first be declared in the declaration section, and the execution must include at least one executable statement, NULL being a valid executable statement, Object control statements Commit and rollback can be used in the execution section, and the Data definition language (language) cannot be used in the execution section, and DDL statements are used with execute immediate or are DBMS_ SQL call.

Execute a pl/sql block

The execution of an anonymous pl/sql block in the Sql*plus is entered/executed after the Pl/sql block, as in the following example:

Declare
V_COMM_PERCENT constant number:=10;
Begin
Update emp
Set Comm=sal*v_comm_percent
where deptno=10;
End
Sql>/
Pl/sql procedure successfully completed.

Sql>


Unlike the execution of an anonymous program, a named program block must use the Execute keyword:

Create or replace procedure Update_commission
(V_dept in number,v_pervent at number default) is
Begin
Update emp
Set Comm=sal*v_percent
where deptno=v_dept;
End

sql>/

Procedure created

Sql>execute update_commission (10,15);

Pl/sql procedure successfully completed.

Sql>


If you execute this program in another named program block or in an anonymous block, you do not need execute to close the word.

Declare
V_dept number;
Begin
Select A.deptno
Into v_dept
From EMP A
where job= ' PRESIDENT '
Update_commission (v_dept);
End
sql>/
Pl/sql procedure successfully completed
Sql>


Control structure

Control structure Control pl/sql program flow code line, PL/SQL support conditional control and cyclic control structure.

Grammar and use

IF.. THEN

Grammar:

IF condition THEN
Statements 1;
Statements 2;
....
End IF


The IF statement determines whether the condition condition is true and, if so, executes the statement following then, if condition is false or NULL, skips the statement between then to end if, and executes the statement following the "if".

IF.. THEN ... ELSE

Grammar:

IF condition THEN
Statements 1;
Statements 2;
....
ELSE
Statements 1;
Statements 2;
....
End IF


If the condition condition to true, the statement between then to else is executed, otherwise the statement between else and end if is executed.

If can be nested, can be in the if or if. Else statement using the IF or if ... Else statement.

if (a>b) and (a>c) then
G:=a;
Else
G:=b;
If C>g Then
G:=c;
End If
End If


IF.. THEN.. elsif

Grammar:

IF Condition1 THEN
Statement1;
elsif Condition2 THEN
Statement2;
elsif Condition3 THEN
Statement3;
ELSE
STATEMENT4;
End IF;
STATEMENT5;


If the condition condition1 to True, the statement1 is executed, then the STATEMENT5 is judged, or the condition2 is true, and if true, the statement2 is executed, and then the STATEMENT5 is executed. The same is true for Condition3, and if Condition1,condition2,condition3 does not, then the STATEMENT4 is executed and STATEMENT5 is executed.

Loop control

The basic form of loop control is the loop statement, and the statements between the loop and end loop are executed infinitely. The syntax for the Loop statement is as follows:

LOOP
statements;
End LOOP

The execution of the statement between the loop and end loop is clearly not possible, so you must use the Exit statement to force the loop to end when you use the loop statement, for example:

x:=100;
LOOP
x:=x+10;
IF x>1000 THEN
EXIT;
End IF
End LOOP;
Y:=x;


The value of Y now is 1010.

The EXIT when statement ends the loop and ends the loop if the condition is true.

x:=100;
LOOP
x:=x+10;
EXIT when x>1000;
x:=x+10;
End LOOP;
Y:=x;


While.. LOOP

While.. The loop has a condition that is associated with a loop and, if the condition is true, executes the statement in the loop body and ends the loop if the result is false.

x:=100;
While x<=1000 loop
x:=x+10;
End LOOP;
Y=x;


For ..... LOOP

Grammar:

For counter in [REVERSE] Start_range....end_range LOOP
statements;
End LOOP;


The loops and while loops are indeterminate, and the loop number of loops is fixed, counter is an implicitly declared variable, his initial value is Start_range, and the second value is start_range+1 until End_range, If Start_range equals end _range, then the loop executes once. If you use the Reverse keyword, the range will be a descending order.

x:=100;
For V_counter in 1..10 loop
x:=x+10;

End Loop
Y:=x;


If you want to exit the for loop, you can use the Exit statement.

Label

Users can use labels to make programs more readable. The program block or loop can be marked. The form of the label is <>.

Marking a program block

<>
[DECLARE]
... ... ...
BEGIN
........
[EXCEPTION]
.......
End Label_name


Tag Loops

<>
LOOP
.........
<>
Loop
..........
<>
Loop
....

EXIT Outer_loop when v_condition=0;
End Loop Innermost_loop;
..........
End LOOP Inner_loop;
End LOOP Outer_loop;


Goto statement

Grammar:

GOTO LABEL;

When a goto statement is executed, control immediately turns to the statement labeled by the label. There are some restrictions on goto statements in Pl/sql, and it is illegal for blocks, loops, and if statements to jump from the outer layers to the inner layer.

X: = 100;
For V_counter in 1..10 loop
IF V_counter =4 THEN
GOTO End_of_loop
End IF
x:=x+10;
<>
Null
End LOOP

Y:=x;


Note: null is a valid executable statement.

Nesting

The inside of a program block can have another program block, which is called nesting. Nesting to be aware of is a variable, defined in the outermost block of the variables can be used in all the child blocks, if you define in the child block variable with the same variable name, in the execution of the child block will use the variables defined in the child block. A variable defined in a child block cannot be referenced by a parent block. The same goto statement cannot be diverted from the parent block to the child block, whereas the converse is legal.

"OUTER Block"
DECLARE
A_number INTEGER;
B_number INTEGER;
BEGIN
--a_number and B_number are available here
<>
DECLARE
C_number INTEGER
B_number Number (20)
BEGIN
C_number:=a_number;
C_number=outer_block. B_number;
End Sub_block;
End Out_block;


Summary

In this article we introduced the basic syntax of pl/sql and how to design and run the PL/SQL program block using the Pl/sql language, and integrate the PL/SQL program into the Oracle server, although the Pl/sql program is embedded in the Oracle database as a feature block, but pl/ The tight integration of SQL and Oracle databases has enabled more and more Oracle database administrators and developers to start using Pl/sql