[Repost] Oracle PL / SQL Language Foundation Oracle PL / SQL Language Foundation
PL / SQL is Oracle extension to standard database language, Oracle has integrated PL / SQL to Oracle Server and other tools, and more developers and DBAs in recent years begins using PL / SQL, this article will tell PL / SQL basic grammar, structure, and components, and how to design and execute a PL / SQL program.
Advantages of PL / SQL
From version 6, PL / SQL is reliable to be integrated into Oracle. Once you have the advantages of PL / SQL and its unique data management convenience, you can imagine that Oracle has lacks PL / SQL situations. PL / SQL is not a separate product, he is a technology integrated into the Oracle server and Oracle tool, you can treat PL / SQL as a engine in the Oracle server, SQL statement executor handles a single SQL statement, PL / SQL The engine processes the PL / SQL block. When the PL / SQL program block is processed in the PL / SQL engine, the SQL statement actuator in the Oracle server processes the SQL statement in the PL / SQL block.
The advantages of PL / SQL are as follows:
PL / SQL is a high-performance transaction-based language that runs in any Oracle environment that supports 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 SQL data types and all SQL functions, and supports all Oracle object types
PL / SQL block can be named and stored in 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, which has good reusability .
You can use the Oracle Data Tool to manage the security of the PL / SQL program stored in the server. The ability to authorize or revoke other users to access the PL / SQL program.
PL / SQL code can be written using any ASCII text editor, so it is very convenient to operate the operating system that can run in any Oracle.
For SQL, Oracle must handle each SQL statement at the same time, which means that every individual call must be processed by the Oracle server, which takes up a lot of server time while causing network crowding. The PL / SQL is sent to the server throughout the statement, which reduces the network crowding.
PL / SQL block structure
PL / SQL is a language of a block structure, which makes up the unit of the PL / SQL program is a logic block, and a PL / SQL program contains one or more logic blocks, each block can be divided into three parts. Similarly to other languages, variables must be declared before use, and PL / SQL provides independent portions that deal with exceptions, which describes different parts of the PL / SQL block:
Declaration Section
The declaration section contains the data type and initial value of variables and constants. This part begins by keyword declare if you don't need to declare a variable or constant, then you can ignore this part; it is necessary to explain that the cursor is also in this part.
Executable Section
The execution section is the instruction section 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 section (Exception Section)
This part is optional, handling exception or error in this section, detailed discussion of exception handling we will do.
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 make multiple rows, but the semicolon indicates the end of the statement. There are multiple SQL statements in a row, and they are separated between semicolons. Each PL / SQL block is started by Begin or Declare, with END. Comment original. Naming and anonymity of PL / SQL block
The PL / SQL block can be a named block or an anonymous block. Anonymous blocks can be used in the server side can also be used on the client.
The naming block can appear in the declaration part of the other PL / SQL block, which is more obvious in this regard, the subroutine can be referenced in the execution section, and can also be referenced in the exception processing section.
The PL / SQL block can be kept independently and stored in the database, and any application connected to the database can access these stored PL / SQL blocks. Oracle provides four types of storageable programs:
Function
Process
Package
Trigger
function
The function is named, the PL / SQL block of the database stored in the database. The function accepts zero or more input parameters, there is a return value, the data type of the return value is defined when the function is created. The syntax of the definition function is as follows:
Function name [{parameter, parameter, ...])] Return DataTypes IS
[Local Declarations]
Begin
Execute Statements
[EXception
Exception Handlers]
END [NAME]
process
The stored procedure is a PL / SQL block, accepts zero or more parameters as input (input) or outputs, or both input and output, unlike functions, stored procedures have not returned values, storage The process cannot be used directly by the SQL statement, and can only be called inside the execut command or the PL / SQL block block, defining the syntax of the stored procedure as follows:
Procedure Name [(Parameter [, parameter, ...])] IS
[Local Declarations]
Begin
Execute Statements
[EXception
Exception Handlers]
END [NAME]
Package
The package is actually a collection of related objects that is combined. Any function or stored procedure in the package is called, and the package is loaded into the memory, and any function or stored procedure in the package will be greatly accelerated.
The package consists of two parts: specification and package main body (BODY), specification describes variables, constants, cursors, and subroutines, envelopes fully define subroutines and cursors.
Trigger
The trigger is associated with a table or database event, and when a trigger event occurs, the trigger defined on the table is triggered.
Variables and constants
The variable is stored in memory to obtain a value, can be referenced by the PL / SQL block. You can imagine a variable into a container that can be stored, and something in the container can be changed.
Declaration variable
Variables are generally declared in the PL / SQL block declaration, PL / SQL is a strong type of language, which means that first declares first before reference variables, to use variables in execution or exception processing, then variables must be first The declaration is declared.
The grammar of the declared variable is as follows:
Variable_name [constant] DatabaseTe [not null] [: = | default expression]
Note: The NOT NULL constraints can be enforced to the variables while declaring variables, and the variable must be assigned when the variable is initialized.
Assign value for variables
There are two ways to assign a variable:
Directly assign a variable directly
X: = 200;
Y = Y (x * 20);
Assign value to variables via 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 constant cannot be changed inside, and the value of constants is assigned when defined, and his declaration is similar to the variable, but must include keyword Constant. Constants and variables can be defined as SQL and user-defined data types.
ZERO_VALUE Constant Number: = 0;
This statement sets a constant called Zero_Value, the data type is Number, the value of 0.
Scale (SCALAR) data type
Scarr data types There are no internal components, and they can be roughly divided into the following four categories:
Number
CHARACTER
Date / time
Boolean
Table 1 shows the digital data type; Table 2 shows the character data type; Table 3 shows the date and Boolean data type.
Table 1 Scalar Types: Numeric Types: Numeric
DataType
Range
Subtypes
Description
Binary_integer
-214748-2147483647
Natural
Natural
Npositive
Positiven
SIGNTYPE
Used to store unison intensity.
The storage length is required to be lower than the Number value.
Subtype (Subty):
Natural: Used for non-negative numbers
Positive: only for positive numbers
Naturaln: only for non-negative numbers and non-NULL values
Positiven: For positive numbers, can not be used 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
Store digital values, including integers and floating point numbers. It can be selected for accuracy and scale, syntax:
Number [([,])].
The default accuracy is 38, Scale is 0.
PLS_INTEGER
-2147483647-2147483647
The PLS_INTEGER provides better performance when it is basically the same as binary_integer, but PLS_INTEGER provides better performance.
Table 2 Character data type
DataType
rang
Subtype
Description
Charr
Maximum length 32767 bytes
Character
Store a set of sets, if the length is not determined, the default is 1
Long
Maximum length 2147483647 bytes
Storage variable length string
Raw
Maximum length 32767 bytes
Used to store binary data and byte strings, the RAW data is not converted between the character set when passed between the two databases.
Longraw
Maximum length 2147483647
Similar to the LONG data type, but also does not convert between character sets.
RowID
18 bytes
As in the database RowID pseudo column type, a row marker can be stored, and the row marker can be regarded as the unique key value of each row in the database.
VARCHAR2
Maximum length 32767 bytes
Stringvarchar
Similar to the VARCHAR data type, store variable length strings. Declaration method is the same as varchar
Table 3 Date and Boolean
DataType
Range
Description
Boolean
True / false
Storage logic value True or false, no parameters
Date
01/01/4712 BC
Store fixed length date and time value, the date value contains time
LOB data type
Lob (large object, large object) data type is used to store large data objects such as images, sound, and LOB data objects may be binary data or character data, and the maximum length is not more than 4G. The LOB data type supports any access method, and Long only supports sequential access. LOB is stored in a separate location while a "LOB Locator" is stored in the original table, which is a pointer to the actual data. Operating the LOB data object in PL / SQL Use Oracle's package DBMS_LOB.lob data types can be divided into the following four categories:. Bfile
BLOB
CLOB
NCLOB
Operator
As with other programming languages, PL / SQL has a series of operators. The operator is divided into the following categories:
Arithmetic operator
Relational operator
Compare operator
Logic operator
The arithmetic operator is shown in Table 4
Operator
Operation
Plus
- Less
/ Division
* Multiplied
** passenger
The relationship operator is mainly used for conditional judgment statements or in the WHERE substring, the relational operator check condition and the result is TRUE or FALSE, Table 5 is the relational operator in PL / SQL.
Operator
Operation
<
Less than operator
<=
Less than or equal to operator
>
Greater than operator
> =
Big than or equal to operator
=
Equal
! =
Not equal to operator
<>
Not equal to operator
: =
Assignment operator
Table 6 shows comparison operators
Operator
Operation
Is NULL
If the operand is NULL Returns True
Like
Comparison string value
Between
Whether the verification value is within the scope
In
Verify the operand in a set of values
Table 7.8 shows logical operators
Operator
Operation
AND
Two conditions must be met
Oral
As long as one of the two conditions
NOT
Negate
Executive part
The execution section contains all statements and expressions, and the execution section begins with the keyword begin, ending with the keyword Exception, if the exception does not exist, then the key is ended. Separates each statement, use assignment operators: = or SELECT INTO or FETCH INTO to assign each variable, and the error will be resolved in the exception processing section, and another PL / SQL block can be used in the execution section. This block is called a nest block
All SQL data operation statements can be used to execute a portion, and the PL / SQL block can no longer display the output of the SELECT statement. The SELECT statement must include an INTO substring or a portion of the cursor. The variables and constants used in the execution section must first declare in the declaration section, and the execution section must include at least one executable statement. NULL is a legal executable statement, the transaction statement Commit and rollback can be used in the execution section, the data definition language cannot be used in the execution section, and the DDL statement is used with Execute IMMEDITE or DBMS_SQL calls.
Perform a PL / SQL block
SQL * PLUS The execution of anonymous PL / SQL block is performed after the PL / SQL block is entered / executed, as shown 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>
Named programs and anonymous executions, perform named blocks must be used using Execute Keywords: create or replace procedure Update_Commission
(v_dept in number, v_pervent in number default 10) 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 this program is executed in another named block or anonymous block, then Execute is required.
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 Controls PL / SQL program flow, PL / SQL support condition control, and loop control structure.
Grammar and use
IF..Then
grammar:
IF condition the
STATEMENTS 1;
Statements 2;
....
END IF
If the IF statement is determined whether the condition is True, if yes, execute the state after then, if the cons corresponding to false or null, skip the statement between the ten to END IF, execute the statement behind the end if.
IF..Then ... Else
grammar:
IF condition the
STATEMENTS 1;
Statements 2;
....
Else
STATEMENTS 1;
Statements 2;
....
END IF
If the condition condition is true, the statement between the1n to ELSE is executed, otherwise the statement between the ELSE to End IF is executed.
If IF can nested, IF or IF EELSE statements can be used in IF or IF ..ELSE statements.
IF (a> b) and (a> c) THEN
g: = a;
Else
g: = b;
IF c> g tell
g: = C;
END IF
END IF
IF..Then..lsif
grammar:
IF condition1 Then
STATEMENT1;
Elsif condition2 Then
STATEMENT2;
Elsif condition3 Then
STATEMENT3;
Else
STATEMENT4;
END IF;
STATEMENT5;
If condition Condition1 executes statement1, then executes statement5, otherwise the condition2 is true, if you perform Statement2, then execute Statement5, the same is the same for Condition1, if condition1, condition2, condition3 is not established, then execute Statement4 , Then perform Statement5.
Cyclic control
The basic form of cyclic control is the statement between the LOOP statement, the LOOP, and End Loop, will be unlimited. The syntax of the loop statement is as follows:
Loop
STATEMENTS;
End loop
The statement between LOOP and End Loop is obviously not, then the exit statement must be used when using the loop statement, the enforcement cycle ends, for example: x: = 100;
Loop
X: = x 10;
IF x> 1000 Then
EXIT;
END IF
End loop;
Y: = x;
The value of Y Y is 1010.
The EXIT WHEN statement will end the loop, and if the condition is TRUE, the loop is ended.
X: = 100;
Loop
X: = x 10;
EXIT WHEN X> 1000;
X: = x 10;
End loop;
Y: = x;
While..loop
While..loop has a condition associated with cycling, if the condition is true, the statement in the cycle is executed, and if the result is FALSE, the loop is ended.
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 number of loops of LOOP and WHILE cycles is uncertain. The number of loops in the for loop is fixed. Counter is a hidden declared variable, his initial value is start_range, the second value is start_range 1 until end_range, If START_RANGE is equal to END _RANGE, the loop will execute once. If the REVERSE keyword is used, the scope 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 tags to make programs better readability. The block or loop can be marked. The form of label is <>.
Marking program block
<>
[Declare]
... ...
Begin
........
[EXCEPTION]
.......
End label_name
Mark cycle
<>
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 performing a goto statement, the control will immediately go to the statement marked by the label. Pl / SQL has some restrictions on the goto statement, for blocks, loops, if statements, jump from the outer layer to the inner layer is illegal.
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 legal executable statement.
Nested
The interior of the block can have another block, which is called nested. Nesting should be aware that variables are defined in the outermost block, and if the same variable name as the external block variable is defined in the sub-block, the sub-block will be used. The variables defined in the block. The variables defined in the sub-block cannot be referenced by the parent block. The same GOTO statement cannot be jumped by the parent block, and vice versa is legal. "Outer Block"
Declare
A_number integer;
B_number integer;
Begin
--A_number and b_number is available from 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 introduce the basic syntax of PL / SQL and how to use the PL / SQL language and run the PL / SQL block, and integrate the PL / SQL program into the Oracle server, although the PL / SQL program is functional block. Embed in the Oracle database, but the close combination of PL / SQL and Oracle databases makes more and more Oracle database administrators and developers start using PL / SQL
