PL/SQL Simple Getting Started tutorial

PL/SQL is an extension of Oracle's standard database language, Oracle has consolidated PL/SQL into Oracle servers and other tools, and more developers and DBAs have begun using PL/SQL in recent years, and this article will cover PL/SQL basic syntax, structures and components, And how to design and execute a PL/SQL program.

　　 Benefits of PL/SQL

Since version 6 PL/SQL has been reliably integrated into Oracle, it is hard to imagine that Oracle is missing PL/SQL when it has the benefits of PL/SQL and the convenience of its unique data management. PL/SQL is not a standalone product, it is a technology integrated into Oracle servers and Oracle tools that can treat PL/SQL as an engine within an Oracle server, and a single SQL statement processor to handle pl/ SQL program block. The SQL statement executor in the Oracle server processes the SQL statements in a PL-SQL program block when the PL/SQL program block is processed in the PL/SQL engine.

　　 the advantages of PL/SQL are as follows:

. PL/SQL is a high-performance transactional-based language that can run in any Oracle environment and support 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 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 by the command of an application that can be accessed by any client/server tool, with good reusability.

. You can use Oracle data tools to manage the security of PL/SQL programs stored on the server. The ability of other users of the database to access PL/SQL programs can be authorized or revoked.

. PL/SQL code can be written using any ASCII text editor, so it is convenient for any operating system that Oracle can run

. For sql,oracle to process each SQL statement at the same time, in a networked environment this means that each individual call must be handled by the Oracle server, which consumes a lot of server time and causes the network to be congested. PL/SQL is sent to the server with the entire statement block, which reduces network congestion.

　　 PL/SQL block structure

PL/SQL is a block-structured language, and the unit that makes up PL/SQL programs is a logical block, and a PL/SQL program contains one or more logical blocks, each of which can be divided into three parts. As with other languages, variables must be declared before they are used, and PL/SQL provides separate parts specifically for handling exceptions, which describe the different parts of the PL/SQL block:

　　 Declaration part (Declaration section)

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; It is important to note that the declaration of the cursor is also in this section.

　　 Execution part (executable section)

The execution section is the instruction portion of the PL/SQL block, starting with the keyword begin, where all executable statements are placed, and other PL/SQL blocks can be placed in this part.

　　 Exception Handling sections (Exception section)

This section is optional, dealing with exceptions or errors in this section, and we'll take a detailed discussion of exception handling.

　　 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, which can make multiple lines, but the semicolon represents the end of the statement. There can be more than one SQL statement in a row, separated by semicolons. Each PL/SQL block starts with a begin or declare and ends with end. Note by--mark.

　　The naming and anonymity of PL/SQL blocks

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

A named program block can appear in the Declarations section of other PL/SQL program blocks, which is obviously a subroutine that can be referenced in the execution section or in the Exception handling section.

PL/SQL blocks can be independently compiled and stored in a database, and any application connected to the database can access these 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 return 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 or outputs (output), or both as input and output (INOUT), unlike functions, where stored procedures do not have return values and stored procedures cannot be used directly by SQL statements. The syntax for defining stored procedures can be called 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]

　　Packages (Package)

A package is actually a collection of related objects that are grouped together, and when any function or stored procedure in the package is called, the package is loaded into memory, and the subroutine of any function or stored procedure in the package is accessed much faster.
The package consists of two parts: a specification and a package body (body), a specification describing variables, constants, cursors, and subroutines, and a package body that fully defines subroutines and cursors.

　　Trigger (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 variables as a container to store things that can be changed in a container.

　　declaring variables

Variables are generally declared in the declaration section of the PL/SQL block, and PL/SQL is a strong type language, which means that before referencing a variable you must first declare that you want to use the variable in the execution or exception handling section, and the variable must first be declared in the Declarations 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 when declaring a variable, at which time the variable must be assigned a value at initialization time.

　　Assigning values to variables

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

. Assigning a value directly to a variable

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

. Assigning a value to a variable 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 a constant cannot be changed inside the program, and the value of the constant is given when defined, and he is declared in a manner similar to a variable, but 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 set a constant named Zero_value, whose data type is number and has a value of 0.

Scalar (scalar) data types

Scalar (scalar) data types do not have internal components, they can be broadly divided 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
-214748-2147483647	requires storage length below the number value. Subtype for restriction range (subtype): NATURAL: For non-negative POSITIVE: Positive only Naturaln: only for non-negative and non-null values Positiven: Only 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  integeric INT NUMERIC REAL SMALLINT	number[([,])]. The default precision is 38,scale is 0.
pls_integer	-2147483647-2147483647		is basically the same as Binary_integer, but with machine operations, The Pls_integer provides better performance.

Table 2 Character data types

DataType	Rang	Subtype	Description
CHAR	Maximum length 32767 bytes	CHARACTER	Stores the 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, raw data is not converted between character sets when passing between two databases.
Longraw	Maximum length 2147483647		Similar to the Long data type, it is also not possible to convert between character sets.
ROWID	18 bytes		The same as the database ROWID pseudo-column type, can store a row identifier, you can treat the row identifier as a unique key value for each row in the database.
VARCHAR2	Maximum length 32767 bytes	Stringvarchar	Similar to the varchar data type, stores variable-length strings. The Declaration method is 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 that contain time in date values

LOB data types

LOB (Large object, Large object) data types are 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 4G. LOB data types support arbitrary access methods, and long supports sequential access only. The LOB is stored in a separate location, and 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 uses the package DBMS_LOB provided by Oracle. LOB data types can be categorized into the following four categories:

. BFILE
. Blob
. Clob
. NCLOB

Operator

As with other programming languages, PL/SQL has a series of operators. The operators fall into the following categories:

. Arithmetic operators

. Relational operators

. Comparison operators

. logical operators

The arithmetic operators are shown in table 4

operator	Operation
+	Add
-	Reducing
/	Except
*	By
**	Powers

Relational operators are primarily used for conditional judgment statements or for where substrings, the relational operator checks whether the condition and 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 operators

operator	Operation
Is NULL	Returns true if the operand is null
Like	Comparing String values
Between	Verify that the value is within range
Inch	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 counter

　　Executive Section

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 the exception does not exist. Semicolons separate each statement, use the assignment operator: = or SELECT INTO or fetch into to assign a value to each variable, the execution part of the error is resolved in the Exception handling section, and in the execution section you can use another PL/SQL program block, which is called a nested block

All SQL data manipulation statements can be used in the execution section, and the PL-SQL block no longer displays the output of the SELECT statement on the screen. The SELECT statement must include an into substring or part of a cursor, and the variables and constants used in the execution must first be declared in the declaration section, and the execution part must include at least one executable statement, and NULL is a valid executable statement. Object control statements Commit and rollback can be used in the execution section, data definition language cannot be used in the execution section, DDL statements are used with execute immediate, or Dbms_ SQL calls.

　　Executes a PL/SQL block

The execution of an anonymous PL/SQL block in Sql*plus is entered/executed after the PL/SQL block, 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>

A named program differs from the execution of an anonymous program, and executing a named block must use the Execute keyword:

Create or replace procedure Update_commission (V_dept in number,v_pervent with 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 the execute close 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

The control structure controls the line of code for the PL/SQL program flow, and PL/SQL supports conditional control and loop control structures.

Syntax and use

IF.. Then

Grammar:

IF condition Then Statements 1; Statements 2; .... END IF

The IF statement determines whether the condition condition is true, if yes, then executes the statement after then, if condition is false or NULL then skips the statement between then and end if, executing the statement after the end If.

　　IF.. Then ... ELSE

Grammar:

IF condition Then Statements 1; Statements 2; .... ELSE Statements 1; Statements 2; .... END IF

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

If can be nested, can be in 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 is true then Statement1 is executed, then STATEMENT5 is executed, otherwise the condition2 is true, if True statement2 is executed and STATEMENT5 is executed, For Condition3 is also the same, if the Condition1,condition2,condition3 is not established, then the STATEMENT4 will be executed, and then execute statement5.

　　Loop control

The basic form of loop control is the loop statement, where the statement between loop and end Loop executes indefinitely. The syntax of the loop statement is as follows:

LOOP
statements;
END LOOP

The execution of an infinite number of statements between loop and end loops is obviously not possible, so you must use the Exit statement when using the Loop 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 at this point is 1010.

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

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

　　While.. LOOP

While.. Loop has a condition that is associated with the 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;

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

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

You can use the exit statement if you want to exit the For loop.

　　Label

The user can use the label to make the program more readable. Blocks or loops can be marked. The label is in the form of <>.

Marking program blocks

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

Marking 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 goes to the statement labeled by the label. There are some restrictions on the goto statement in PL/SQL, which is illegal for blocks, loops, if statements, to jump from the outer 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

There can be another program block inside the block. This is known as nesting. Nesting to be aware of variables, variables defined in the most external blocks can be used in all child blocks, and variables defined in the child block will be used when the child block is executed if the same variable name is defined in the child block as the external program block variable. A variable defined in a child block cannot be referenced by a parent block. The same goto statement cannot be skipped by the parent block, or vice versa.

"OUTER BLOCK" DECLARE A_number INTEGER; B_number INTEGER; BEGIN --a_number and B_number is 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 describe the basic syntax for PL/SQL and how to design and run PL/SQL blocks using the PL/SQL language and consolidate the PL/SQL program into an Oracle server, although the PL/SQL program is embedded as a function block in the Oracle database, but pl/ The close integration of SQL with Oracle database has enabled more and more Oracle database administrators and developers to start using PL/SQL.

PL/SQL Simple Getting Started tutorial