In Oracle, multiple SQL statements are executed at a time without passing through the stored procedure. Oracle PL/SQL

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
<	Less than Operator
<=	Operator less than or equal
>	Greater than Operator
> =	Operator greater than or equal
=	Equal to operator
! =	Not equal to operator
<>	Not equal to operator
: =	Value assignment operator

Table 6 displays comparison operators.

Operator	Operation
Is null	Returns true if the operand is null.
Like	Compare string values
Between	Whether the verification value is within the range
In	Verify that the operands are in the set series of values.

Table 7.8 displays logical operators.

Operator	Operation
And	Both conditions must be met
Or	If either of the two conditions is met
Not	Invert

　　Execution part

The execution part contains all statements and expressions. The execution part starts with the keyword begin and ends with the keyword exception. If the exception does not exist, it ends with the keyword end. Use a semicolon to separate each statement and assign values to each variable using the = or select into or fetch into operator. Errors in the execution part will be solved in the Exception Handling Section, you can use another PL/SQL block in the execution part. This block is called a nested block.

All SQL data operation statements can be used for execution. PL/SQL blocks cannot display the SELECT statement output on the screen. The SELECT statement must contain an into substring or a part of the cursor. the variables and constants used for execution must be declared in the declaration part first. The execution part must contain at least one executable statement, null is a legal executable statement. The transaction control statements commit and rollback can be used in the execution part. The data definition language cannot be used in the execution part, DDL statements are used together with execute immediate or dbms_ SQL calls.

　　Execute a PL/SQL Block

In SQL * Plus, the execution of anonymous PL/SQL blocks is input/after the PL/SQL blocks. The following example shows:

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>

The name of the program is different from that of the anonymous program. The execute keyword must be used to execute the named program block:

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 you execute this program in another named block or anonymous block, you do not need to execute it.

Declare V_dept number; Begin Select a. deptno Into v_dept From EMP Where job = 'President' Update_commission (v_dept ); End SQL>/ PL/SQL procedure successfully completed SQL>

Control Structure

The control structure controls the code lines of PL/SQL program flows. PL/SQL supports Conditional Control and cyclic control structures.

Syntax and usage

If... then

Syntax:

If condition then Statements 1; Statements 2; .... End if

The IF statement determines whether the condition is true. If yes, the statement after then is executed. If the condition is false or null, the statement between then and end if is skipped, execute the statement after end if.

　　If... then... else

Syntax:

If condition then Statements 1; Statements 2; .... Else Statements 1; Statements 2; .... End if

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

If can be nested, you can use if or if. Else statements in if or if. Else statements.

If (A> B) and (A> C) then G: =; Else G: = B; If C> G then G: = C; End if End if

　　If... then... elsif

Syntax:

If condition1 then Statement1; Elsif condition2 then Statement2; Elsif condition3 then Statement3; Else Statement4; End if; Statement5;

If the condition condition1 is true, execute statement1, and then execute statement5; otherwise, judge whether condition2 is true; if it is true, execute statement2, and then execute statement5. The Condition 3 is the same. If condition1, if neither condition2 nor condition3 is true, statement4 is executed and statement5 is executed.

　　Loop Control

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

Loop
Statements;
End Loop

The statement between the loop and the end loop cannot be executed infinitely. Therefore, when using the loop statement, you must use the exit statement to force the loop to end. For example:

X: = 100; Loop X: = x + 10; If X> 1000 then Exit; End if End loop; Y: = X;

The value of Y is 1010.

The exit when statement ends the loop. If the condition is true, the loop ends.

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 the loop. If the condition is true, the statement in the loop body is executed. If the result is false, the loop ends.

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 in a loop and while loop is uncertain. The number of loops in a for loop is fixed. Counter is an implicitly declared variable whose 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 is executed once. If the reverse keyword is used, the range is a descending order.

X: = 100; For v_counter in 1 .. 10 Loop X: = x + 10; End Loop Y: = X;

To exit the for loop, use the exit statement.

　　Tag

You can use tags to make the program more readable. The block or loop can be marked. The label format is <>.

Tag Block

 

< > [Declare] ......... Begin ........ [Exception] ....... End label_name

Mark Loop

< > Loop ......... < > Loop .......... < > Loop .... Exit outer_loop when v_condition = 0; End loop innermost_loop; .......... End loop inner_loop; End loop outer_loop;

　　GOTO statement

Syntax:

Goto label;

When the GOTO statement is executed, the control will immediately switch to the statement marked by the tag. PL/SQL has some restrictions on the GOTO statement. For block, loop, and if statements, it is illegal to jump from the outer layer 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 legal executable statement.

　　Nesting

A program block can have another program block. In this case, it is nested. Note that variables are nested. variables defined in the most external block can be used in all child blocks. If the child block defines the same variable name as the external block variable, when executing a sub-block, the variables defined in the sub-block are used. The variables defined in the Child block cannot be referenced by the parent block. Similarly, the GOTO statement cannot jump from the parent block to the sub-block. Otherwise, it 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

This article introduces the basic PL/SQL syntax and how to use the PL/SQL language to design and run the PL/SQL block, and integrate the PL/SQL program into the Oracle server, although PL/SQL programs are embedded into Oracle databases as functional blocks, the close combination of PL/SQL and Oracle databases has enabled more and more Oracle Database administrators and developers to use PL/SQL.

My subject
The above content is reprinted. It mainly introduces the basic syntax of PL/SQL and how to use the PL/SQL language to design and run the PL/SQL program block. Next I will talk about how.. Net executes Multiple SQL statements at a time. Generally, only one SQL statement can be executed at a time unless a stored procedure is used. For example, the following services are available: when deleting a user (assuming userid = 5), you also need to delete all posts and replies from the user on the Forum (to ensure the integrity of the reference ), assume that the user table is users, the post table is articles (with The USERID field), and the reply table is replys (with The USERID field ), generally, there are three steps: (1) Delete all replies with userid = 5 in the replys table; (2) Delete All posts with userid = 5 in the articles table; (3) delete users whose userid is 5 in the users table.
In each step above, create an oracleconnection, create an oraclecommand, executenonquery () method, then oraclecommand call dispose () method, and finally oracleconnection call the close () method. This process is executed in the above process. We know that database connection operations are time-consuming. Is there any better way? The answer is yes.
PS/SQL is used.

Begin
Delete from replys where userid = 5; -- delete a reply
Delete from articles where userid = 5; -- delete a post
Delete from users where userid = 5; -- delete a user
End;

The entire process can be written as follows: Private void deleteuser (INT userid)
{
String deletesql = "begin Delete from replys where userid = {0}; delete from articles where userid = {1}; delete from users where userid = {2}; end ;";
Deletesql = string. Format (deletesql, userid );
Oracleconnection connection = new oracleconnection (connectionstring );
Connection. open ();
Oraclecommand cmd = new oraclecommand (deletesql, connection );
Cmd. Dispose ();
Connection. Close ();
}

In this way, all the operations can be performed in one connection. Of course, the transaction processing is not considered in this Code. In actual use, you should consider adding as appropriate.