Introduction to Oracle PL/SQL

The ORACLE tutorial is as follows: Oracle PL/SQL getting started.

I. Objective of PL/SQL

Structured Query Language (SQL) is a common Language used to access relational databases. It is a fourth-generation Language (4GL) and its execution features are non-procedural, that is, you do not need to specify the specific method and method of execution, but simply call the corresponding statement to directly obtain the result. Obviously, this language that does not focus on any implementation details is very convenient for developers. However, for some complex business processes that require the corresponding program to describe, then 4GL is powerless. To solve this problem, PL/SQL is a procedural language that belongs to the third generation. It is compatible with C, C ++, java and other languages focus on processing details, so they can be used to implement complicated business logic.

This tutorial is divided into two parts. The first part mainly discusses the basics of PL/SQL programming. The second part uses a case to explain PL/SQL programming. I hope that after reading this article, you will have a general understanding of PL/SQL programming and lay a foundation for the future development of PL/SQL programming.

Ii. PL/SQL programming Basics

To master a programming language, you must first understand its basic syntax structure, namely the program structure, data type, control structure, and corresponding embedded functions (or programming interfaces ).

1. PL/SQL program structure

PL/SQL programs are all based on blocks. The following shows a complete PL/SQL block:

From the preceding PL/SQL section, the entire PL/SQL block is divided into three parts: Declaration part (beginning with declare) and execution part (starting with begin) and exception Handling (beginning with exception ). The execution part is required, and the other two parts are optional. The basic structure of a PL/SQL segment is composed of three parts, regardless of the size of the Code.

2. Variable declaration and assignment

PL/SQL is mainly used for database programming. Therefore, all its data types correspond to the Field Types in the oracle database in a one-to-one manner, which can be divided into numeric, Boolean, numeric, and date types. To facilitate understanding of the subsequent routines, we will briefly introduce two common data types: number and varchar2.

Number

Used to store integers and floating-point numbers. The value range is 1E-130 ~ 10E125, which uses the following syntax:

(Precision, scale) is optional. precision indicates the number of all digits, and scale indicates the number of digits on the right of the decimal point.

Varchar2

Used to store variable-length strings. The syntax is as follows:

The size parameter is optional, indicating the maximum length that the string can store.

Declaration of variables in PL/SQL is not the same as that in other languages. It is declared from right to left. For example, to declare a variable v_id of the number type, the form should be:

If you assign values to the above v_id variable, you cannot use "=" to use ": =", that is, the form is:

[NextPage]

I. Objective of PL/SQL

Structured Query Language (SQL) is a common Language used to access relational databases. It is a fourth-generation Language (4GL) and its execution features are non-procedural, that is, you do not need to specify the specific method and method of execution, but simply call the corresponding statement to directly obtain the result. Obviously, this language that does not focus on any implementation details is very convenient for developers. However, for some complex business processes that require the corresponding program to describe, then 4GL is powerless. To solve this problem, PL/SQL is a procedural language that belongs to the third generation. It is compatible with C, C ++, java and other languages focus on processing details, so they can be used to implement complicated business logic.

This tutorial is divided into two parts. The first part mainly discusses the basics of PL/SQL programming. The second part uses a case to explain PL/SQL programming. I hope that after reading this article, you will have a general understanding of PL/SQL programming and lay a foundation for the future development of PL/SQL programming.

Ii. PL/SQL programming Basics

To master a programming language, you must first understand its basic syntax structure, namely the program structure, data type, control structure, and corresponding embedded functions (or programming interfaces ).

1. PL/SQL program structure

PL/SQL programs are all based on blocks. The following shows a complete PL/SQL block:

From the preceding PL/SQL section, the entire PL/SQL block is divided into three parts: Declaration part (beginning with declare) and execution part (starting with begin) and exception Handling (beginning with exception ). The execution part is required, and the other two parts are optional. The basic structure of a PL/SQL segment is composed of three parts, regardless of the size of the Code.

2. Variable declaration and assignment

PL/SQL is mainly used for database programming. Therefore, all its data types correspond to the Field Types in the oracle database in a one-to-one manner, which can be divided into numeric, Boolean, numeric, and date types. To facilitate understanding of the subsequent routines, we will briefly introduce two common data types: number and varchar2.

Number

Used to store integers and floating-point numbers. The value range is 1E-130 ~ 10E125, which uses the following syntax:

(Precision, scale) is optional. precision indicates the number of all digits, and scale indicates the number of digits on the right of the decimal point.

Varchar2

Used to store variable-length strings. The syntax is as follows:

The size parameter is optional, indicating the maximum length that the string can store.

Declaration of variables in PL/SQL is not the same as that in other languages. It is declared from right to left. For example, to declare a variable v_id of the number type, the form should be:

If you assign values to the above v_id variable, you cannot use "=" to use ": =", that is, the form is:

[NextPage]

Iii. Process and Function

The procedures and functions in PL/SQL are the same as those in other languages. They are all combined statements to execute certain tasks. The process has no return value, and the function has a return value. Its syntax structure is:
Procedure: Create or replace procedure procname (parameter list) as PL/SQL statement Block

Function: Create or replace function funcname (parameter list) return value as PL/SQL statement Block

The following is an example to describe how to use the process:

Question: Suppose there is a table t1 with two fields f1 and f2, f1 is of the number type, f2 is of the varchar2 type, and then two records are written to t1, with the content customized.

So far, the test_procedure stored procedure has been completed and can be called in other PL/SQL blocks or procedures after compilation. Since functions and processes have great similarity, we will not repeat them here.

Iv. cursor

The cursor concept is put forward here because it is very important in PL/SQL programming. It is defined as: A cursor is used to represent the result set returned by a dml SQL operation. That is, when a query operation on a database returns a set of result sets, it uses a cursor to mark this set of result sets. Later, it obtains data information in the result set through the cursor operation. The syntax structure for defining a cursor is as follows:

The first code in this article contains the following sentence:

It defines a cursor c_emp, which represents the result set of all the emp_id fields in the employee table being 3. When you need to operate the result set, you must complete three steps: Open the cursor, use the fetch statement to retrieve the data in the cursor, and close the cursor. Refer to the comments of the first code in this article to understand the three steps of the cursor operation.

V. Other Concepts

The concept in PL/SQL is very important. It is mainly to encapsulate a group of processes and functions with similar functions, similar to the concept of namespace in Object-Oriented.

A trigger is a special stored procedure. It is called only when a specific event occurs. It is mainly used for message notifications between multiple tables.

Vi. debugging environment

Currently, PL/SQL has many debugging environments. In addition to Oracle's self-contained debugging environment SQL * plus, I recommend the TOAD tool. This tool has a user-friendly interface and can improve program compilation efficiency.

This article mainly explains the basic part of PL/SQL. After familiarizing yourself with this part, you can write and apply the stored procedure, which is helpful for improving the execution efficiency of the database server.

Previous Page