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. PL / SQL Advantage From Version 6 Start PL / SQL is reliable to integrate into Oracle, once you master the advantages of PL / SQL and their unique data management convenience, you can imagine Oracle missing PL / SQL situation. 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 can run 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 while supporting all Oracle object types. PL / SQL blocks can be named and stored in the Oracle server, and can also be called by other PL / SQL programs or SQL commands, Any customer / server tool can access the PL / SQL program with 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 the operating system that can run for any Oracle is very convenient. For SQL, Oracle must handle each SQL statement at the same time, which means in the network environment Every independent call must be processed by the Oracle server, which takes up a lot of server time while causing network congestion. The PL / SQL is sent to the server throughout the statement, which reduces the network crowding. The PL / SQL block structure PL / SQL is a block structure. The unit constituting the PL / SQL program is a logic block, and a PL / SQL program contains one or more logical blocks, each block can be divided into three. section. As with 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: Declarative section declaration section contains variables and The data type and initial value of 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. The executable section is the instruction section in the PL / SQL block. Start with the keyword Begin, all executable statements are placed in this part, and other PL / SQL blocks can also be placed in this part. This part of the exception section is optional, and the exception or error is processed in this section, and the detailed discussion of exception processing We will do it later. PL / SQL block syntax
[Declare] --- Decutable Statements [- Exception] --- Exception StatementSend
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. The name of the PL / SQL block and anonymous 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:. Functions. Procedure. Pack. The trigger function function is named, stored in the PL / SQL block of 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 function is as follows: function name [{parameter [, parameter, ...])] Return DataTypes is [Local Declarations] Beginexecute Statements [ExceptionException Handlers] End [Name]
The process stored procedure is a PL / SQL block, accepts zero or more parameters as input (input) or outputs, or both input and output, unlike the function, the stored procedure has not returned value, The stored procedure cannot be used directly by the SQL statement, and can only be called inside the execut command or the PL / SQL block block, the syntax of the stored procedure is as follows:
Procedure Name [(Parameter [, Parameter, ...])] is [local declarations] beginexecute statements [exceptionException handlers] End [name]
The package (package) package is actually a collection of related objects that is combined. Any function or stored procedure in the package is called, 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. The Trigger 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 constant variables are stored in memory to obtain values, can be referenced by PL / SQL blocks. You can imagine a variable into a container that can be stored, and something in the container can be changed. Declaring variable variables are generally declared in the PL / SQL block declaration, PL / SQL is a strong type of language, which means that the variable must be declared before the reference variable, the variable must be used in the execution or exception handling part. First, declaration is made in the declaration section. 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. There are two ways to assign variable assignments to variables:. Direct assignment value x: = 200; y = y (x * 20);. Variable assignment SUM (SALARY) by SQL SELECT INTO or FETCH INTO. * 0.1) INTO TOTAL_SALARY, TATAL_COMMISSIONFROM EMPLOYEWHERE DEPT = 10; constant constants Similar to variables, but the value of constant cannot change in the program, and the value of constants is assigned when defined, and his declaration is similar to the variable, but must include keywords 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. Scarar Data Type Scalar (SCALAR) Data Type There is no internal component, which 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
DataTyperanGesubtypescriptionbinary_integer-214748-2147483647naturalNaturalNPositivePositivePositiveSignSignType is used to store single-by-10 intensity. The storage length is required to be lower than the Number value. Subtype for restriction: Natural: Used for non-negative number POSTIVE: only for positive Naturaln: only for non-negative numbers and non-null values Positiven: For positive numbers, cannot be used for NULL values SIGNTYPE: Only values: -1,0 or 1.number1.0e-130-9.99e125Decdecimaldouble precisionFloat IntegerIntNumericRealsmallint stores digital values, including integers and floating point numbers. It can choose accuracy and scale, syntax: Number [([,])]. The default accuracy is 38, scale is 0.pls_integer-2147483647-2147483647 is basically the same as binary_integer, but PLS_INTEGER provides better performance when using machine operation.
Table 2 Character data type
DataTyperangSubtypedScriptionChar maximum length 32767 byte Character stores a set string, if the length is not determined, the default is 1 long maximum length 2147483647 byte Storage variable length string Raw maximum length 32767 bytes for storing binary data and byte strings, When transmitting between the two databases, the RAW data is not converted between the character set. LongRaw maximum length 2147483647 is similar to the long data type, and he cannot convert between the character set. The RowID18 byte is the same as the database RowID pseudo column type, and can store a row marker to see the row marker as the unique key value of each row in the database. VARCHAR2 maximum length 32767 byte Stringvarchar is similar to the VARCHAR data type, stores a variable length string. Declaration method is the same as varchar
Table 3 Date and Boolean
DataTypeRange DescriptionBooleanTrue / False stores logic value true or false, no parameter DATE01 / 01/4712 BC storage fixed length date and time value, the date value contains time LOB data type LOB (large object, large object) data type for storage Similar Image, sound such a large data object, the LOB data object can be binary data or a character data, and its maximum length does not exceed 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.lob.lob. The following four categories:. Bfile. Blob. Clob. Nclob operator is the same as other programming languages, PL / SQL has a series of operators . The operator is divided into the following categories:. Arithmetic operator. Relational operator. Compare operator. Logical operator arithmetic operator as shown in Table 4
OperatorOperation plus - minus / divided * multiplied ** passengers
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.
OperatorOperation
Table 6 shows comparison operators
Operator Operationis NULL If the operand is NULL Returns the Truelike comparison string value BetWeen verification value is in the range in the value of the operand in a set of values
Table 7.8 shows logical operators
OperatorOperationand two conditions must be met or as long as you meet a NOT in both conditions
The execution section contains all statements and expressions, and the execution section begins with the keyword begin, ending with the keyword Exception, if Exception does not exist, then the keyword end 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. Such blocks are called all SQL data operation statements of the nesting block can be used to execute a portion, and the PL / SQL block cannot 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. Performing a PL / SQL block SQL * PLUS execution is performed by the PL / SQL block after input / execute, 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; EndSql> / PL / SQL ProCedure SuccessFully Completed.sql> The program is different from the anonymous program, and the named block must be Use an Execute keyword:
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; endSQL> / Procedure createdSQL> 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); Endsql> / PL / SQL Procedure SuccessFully CompletedSql>
Control Structure Control Structure Controls PL / SQL program flow, PL / SQL support condition control, and loop control structure. Grammatical and use IF..Then syntax:
IF Condition Then 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 Syntax:
IF Condition Then 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 THEN G: = C; End IFEND IF
IF..Then..lsif syntax:
Elsif Condition2 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. The basic form of cyclic control loop control is the statement between the LOOP statement, the LOOP, and End Loop will execute unlimited. The syntax of the loop statement is as follows: loop statements; End loop Loop and end LOOP's statement unlimited execution is obviously not, then the EXIT statement must be used when using the loop statement, for example: x: = 100; Loop x: = x 10; IF x> 1000 THEN EXIT; Endness loop; Y: = x;
At this point Y is 1010. The EXIT WHEN statement will end the loop. If the condition is true, the loop is ended.
X: = 100; loopx: = x 10; Exit WHEN X> 1000; x: = x 10; end loop; y: = x;
While..Loop while..loop has a condition associated with looping, if the condition is true, execute the statement in the loop, and the loop is ended if the result is false.
X: = 100; while x <= 1000 loop x: = x 10; end loop; y = x;
For ... loop syntax:
For counter in [reverse] start_range .... End_range loopstatements; 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 loopx: = x 10; end favor: = x;
If you want to exit the FOR loop, you can use the exit statement. Label users can use tags to make the program 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 ou_loop when v_condition = 0; end loop innermost_loop; ....... ... End loop inner_loop; end loop @ ly_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; <> Nullend LOOPY: = 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 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 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.
