Oracle PL / SQL Language Basic PL / SQL is Oracle's extension of standard database language, Oracle has integrated PL / SQL into Oracle servers and other tools, more developers and DBAs in recent years start 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 [Excertable Statements "--- Each statement in the Exception Statements End PL / SQL block must end with a semicolon, SQL statement can make multiple lines , 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 dattypes is [local declarations] begin execute statements [exception exception handlers] END [NAME] process stored procedure is a PL / SQL block Accepting zero or more parameters as input (input) or output, or both input and output, unlike functions, the stored procedure does not return value, the stored procedure cannot be used directly by the SQL statement, only The syntax of the stored procedure can be called inside the execut command or the PL / SQL program block, the syntax of the stored procedure is as follows: Procedure Name [(Parameter [, Parameter, ...])] is [local declarations] begin execute statements [exception exception handlers] End [ The package package is actually a collection of related objects that are combined, and any function or stored procedure in the package is called, the package is loaded into the memory, any function or stored procedure in the package is accessed The speed 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] You can give the variable mandatory to add NOT NULL constraints at the same time as the declaration variable, at which time 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_COMMISSION FROM EMPLOYEEE DEPT = 10; constant constants Similar to variables, but the value of constants cannot change in the program, and the value of constants is assigned, and his declaration is similar to the variable, but must include Key words 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 Datatype Range Subtypes Description Binary_integer -214748-2147483647 Natural NPOSTIVE POSITIVEN SIGNTYPE is used to store universal integers. 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. Number 1.0e-130-9.99e125 Dec Decimal Double Precision Float Integer Int Numeric Real Smallint 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 Datatype Rang Subtype Description Char maximum length 32767 Byte Character Store set, if the length is not determined, the default is 1 Long maximum length 2147483647 byte storage variable length string RAW maximum length 32767 bytes In the storage binary data and byte string, the RAW data is not converted between the character set when passed between the two databases.
LongRaw maximum length 2147483647 is similar to the long data type, and he cannot convert between the character set. RowID 18 bytes are the same as the database RowID pseudo column type, and can store a row marker to see a row marker as a 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 Methods Table 3 Date and Boolean DataType Range Description Boolean True / False Storage Logic Value True or False, None Parameter Date 01/01/4712 BC Storage Fixed Date and Time Value, 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. The DBMS_Lob.lob data type can be divided into 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. Comparison Operator. Logical operator arithmetic operator is shown in Table 4 Operator Operation Plus - Reduce / Except * Passenger relationship operator main For conditions for condition judgment statements or for WHERE substrings, relational operators check condition and result is TRUE or FALSE, Table 5 is the relational operator in PL / SQL Operator Operation
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 executed 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> different naming program execution anonymous program executes the named block must 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; end SQL> / Procedure created SQL> execute update_commission (10,15);. PL / SQL procedure successfully completed SQL> If Execute this program 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 of a control structure of the control PL / SQL program flow line , PL / SQL support condition control and loop control structure. Syntax and use IF..Then Syntax: if Condition Then Statements 1; Statements 2; .... End If IF statement judgment condition Condition is true if it is true, if yes, then execute the state of the state, if condition is false or null Skip the statement between the THEN to END IF, execute the statement behind the endiff.
IF..THEN ... ELSE grammar: if Condition Then Statements 1; Statements 2; .... Else Statements 1; Statements 2; .... Endiff If condition condition is true, execute then to else between THEN to ELSE The statement, otherwise execute the statement between ELSE to END IF. 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 if end if if..then..lsif syntax: if condition1 Then statement1; ELSIF condition2 tHEN statement2; ELSIF condition3 tHEN statement3; eLSE statement4; END IF; statement5; if the condition condition1 is TRUE executed statement1, then perform statement5, otherwise determines condition2 whether to TRUE, If TRUE is executed statement2, then execute statement5 Also the same for Condition3, if condition1, condition2, condition3 are not true, then statement4, then execute 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; END if End loop; y: = x; At this time, the value of Y is 1010. The EXIT WHEN statement will end the loop. 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 and loop contact 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 syntax: 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.
