Oracle PL/SQL Basics

The ORACLE tutorial is the basics of Oracle PL/SQL language.

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

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:

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:

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.

[NextPage]

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:

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.

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

Table 2-character data type

Table 3 DATE and BOOLEAN

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

[1] [2] Next page

The ORACLE tutorial is the basics of Oracle PL/SQL language. . 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.

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.

Table 6 displays comparison operators.

Table 7.8 displays logical operators.

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:

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:

If you EXECUTE this program in another named block or anonymous block, you do not need to EXECUTE it.

[NextPage]

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:

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 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... THEN... ELSIF

Syntax:

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:

The value of Y is 1010.

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

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.

FOR... LOOP

Syntax:

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.

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

Mark 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.

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.

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.

Previous Page

Previous Page [1] [2]