Oracle Pl/sql Language Basics

Oracle|sql Language Oracle Pl/sql Language Foundation
2002-8-23
Oracle Pl/sql Language Basics


Pl/sql is Oracle's extension of the standard database language, Oracle has integrated PL/SQL into Oracle servers and other tools, and more developers and DBAs have started using Pl/sql in recent years, and this article covers Pl/sql basic syntax, architecture and components, And how to design and execute a pl/sql program.

Advantages of Pl/sql

Starting with version 6, Pl/sql is reliably integrated into Oracle, and once you have the benefits of Pl/sql and its unique data management convenience, it's hard to imagine Oracle missing Pl/sql. Pl/sql is not a stand-alone product, he is a technology that integrates into Oracle Server and Oracle tools, can treat pl/sql as an engine inside Oracle server, SQL statement performer handles a single SQL statement, Pl/sql engine handles pl/ SQL program block. When the PL/SQL program block is processed by the Pl/sql engine, the SQL statement executor in the Oracle server processes the SQL statements in the PL/SQL program 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 and support all data processing commands. Process SQL data definition and data control elements 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 an Oracle server, and can be invoked by other PL/SQL programs or SQL commands, and any client/server tool can access the PL/SQL program and is well reusable.

. You can use Oracle data tools to manage the security of PL/SQL programs that are stored in the server. Ability to authorize or revoke access to Pl/sql programs by other users of the database.

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

. For sql,oracle, each SQL statement must be processed at the same time, which means that each individual invocation must be handled by an Oracle server in a networked environment, which takes up a lot of server time and leads to network congestion. The pl/sql is sent to the server with the entire statement block, which reduces network congestion.

PL/SQL block Structure

Pl/sql is a kind of block structure language, the unit that composes Pl/sql program is logical block, a pl/sql program contains one or more logical blocks, each block can be divided into three parts. As with other languages, a variable must be declared before it is used, and Pl/sql provides a separate section specifically for handling exceptions, which describes the different parts of the Pl/sql block:

Declarations section (Declaration sections)

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; What you need to say is that the declaration of the cursor is also in this section.

Executive 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 section.

Exception handling (Exception section)

This section is optional, dealing with exceptions or errors in this section, and the detailed discussion of exception handling is done later.

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, and the SQL statement can make more than one line, but the semicolon represents the end of the statement. You can have more than one SQL statement in a row, separated by semicolons. Each pl/sql block begins with begin or declare and ends with end. Comment by--marked.

Naming and anonymity of pl/sql blocks

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

Named program blocks can appear in the Declarations section of other pl/sql blocks, which is more obvious than subroutines, which can be referenced in the execution part or in the Exception handling section.

The PL/SQL program block can be independently compiled and stored in the database, and any application connected to the database can access the 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 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 program block that accepts 0 or more parameters as input (input) or output (output), or both as input and output (INOUT), unlike functions where stored procedures do not return values and stored procedures cannot be directly used by SQL statements. The syntax for defining stored procedures is as follows, 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]

Package (Package)

A package is actually a collection of related objects that are grouped together, and when any function or stored procedure in a package is invoked, the package is loaded into memory, and the access speed of any function or stored procedure in the package is greatly accelerated.

The package consists of two parts: the specification and the package body, the specification describes variables, constants, cursors, and subroutines, and the package body fully defines subroutines and cursors.

Triggers (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 a variable as a container that can store things, and things in a container can be changed.

declaring variables

Variables are generally declared in the Declarations section of the Pl/sql block, and Pl/sql is a strong type language, which means that the variable must be declared first in the declaration section before it can be referenced in the execution or exception handling 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 while declaring a variable, at which point the variable must be assigned when it is initialized.

Assigning values to variables

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

. Assigning values directly to variables

x:=200;

y=y+ (X*20);

. Assigning values to variables through 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 within a program, and the value of a constant is given when defined, and he declares the same way as a variable, but must include the keyword constant. Both constants and variables can be defined as SQL and user-defined data types.

Zero_value CONSTANT number:=0;

This statement has a constant named Zero_value, the data type is number, and the value is 0.

Scalar (scalar) data type

Scalar (scalar) data types do not have internal components, and they can be roughly grouped 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




Table 2 Character data types



Table 3 Date and Boolean



LOB data type

LOB (Large object, Large object) data type is 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 no more than 4G. LOB data types support arbitrary access, and long only supports sequential access. The LOB is stored in a separate location, while 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 using the package Dbms_lob provided by Oracle. LOB data types can be grouped into the following four categories:

. BFILE

. Blob

. Clob

. NCLOB

Operator

As with other programming languages, Pl/sql has a series of operators. The operators are grouped into the following categories:

. Arithmetic operator

. Relational operators

. comparison operator

. logical operators

The arithmetic operators are shown in table 4



Relational operators are used primarily for conditional judgment statements or for a where substring, the relational operator checks whether the condition and the result are true or false, and table 5 is the relational operator in Pl/sql



Table 6 shows the comparison operator



Table 7.8 shows the logical operators

[[The No.8 picture.]]

Executive part

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

All SQL data manipulation statements can be used in the execution section, and the Pl/sql block can no longer display the output of the SELECT statement on the screen. The SELECT statement must include an into substring or a part of the cursor, and the variables and constants used by the execution must first be declared in the declaration section, and the execution must include at least one executable statement, NULL being a valid executable statement, Object control statements Commit and rollback can be used in the execution section, and the Data definition language (language) cannot be used in the execution section, and DDL statements are used with execute immediate or are DBMS_ SQL call.

Execute a pl/sql block

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

Unlike the execution of an anonymous program, a named program block must use the Execute keyword:

Create or replace procedure Update_commission

(V_dept in number,v_pervent at 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 execute to close the 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

Control structure Control pl/sql program flow code line, PL/SQL support conditional control and cyclic control structure.

Grammar and use

IF.. THEN

Grammar:

IF condition THEN

Statements 1;

Statements 2;

....

End IF

The IF statement determines whether the condition condition is true and, if so, executes the statement following then, if condition is false or NULL, skips the statement between then to end if, and executes the statement following the "if".

IF.. THEN ... ELSE

Grammar:

IF condition THEN

Statements 1;

Statements 2;

....

ELSE

Statements 1;

Statements 2;

....

End IF

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

If can be nested, can be in the 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 to True, the statement1 is executed, then the STATEMENT5 is judged, or the condition2 is true, and if true, the statement2 is executed, and then the STATEMENT5 is executed. The same is true for Condition3, and if Condition1,condition2,condition3 does not, then the STATEMENT4 is executed and STATEMENT5 is executed.

Loop control

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

LOOP

statements;

End LOOP

The execution of the statement between the loop and end loop is clearly not possible, so you must use the Exit statement to force the loop to end when you use the loop statement, for example:

x:=100;

LOOP

x:=x+10;

IF x>1000 THEN

EXIT;

End IF

End LOOP;

Y:=x;

The value of Y now is 1010.

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

x:=100;

LOOP

x:=x+10;

EXIT when x>1000;

x:=x+10;

End LOOP;

Y:=x;

While.. LOOP

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

The loops and while loops are indeterminate, and the loop number of loops is fixed, counter is an implicitly declared variable, his initial value is Start_range, and the second value is start_range+1 until End_range, If Start_range equals end _range, then the loop executes once. If you use the Reverse keyword, the range 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 labels to make programs more readable. The program block or loop can be marked. The form of the label is <>.

Marking a program block

<>

[DECLARE]

... ... ...

BEGIN

........

[EXCEPTION]

.......

End Label_name

Tag 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 turns to the statement labeled by the label. There are some restrictions on goto statements in Pl/sql, and it is illegal for blocks, loops, and if statements to jump from the outer layers 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

The inside of a program block can have another program block, which is called nesting. Nesting to be aware of is a variable, defined in the outermost block of the variables can be used in all the child blocks, if you define in the child block variable with the same variable name, in the execution of the child block will use the variables defined in the child block. A variable defined in a child block cannot be referenced by a parent block. The same goto statement cannot be diverted from the parent block to the child block, whereas the converse 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 introduced the basic syntax of pl/sql and how to design and run the PL/SQL program block using the Pl/sql language, and integrate the PL/SQL program into the Oracle server, although the Pl/sql program is embedded in the Oracle database as a feature block, but pl/ The tight combination of SQL and Oracle databases has enabled more and more Oracle database administrators and developers to start using Pl/sql.

Original Author: Kiump IT certification integrated station