Introduction to Oracle PL/SQL

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 processesProgramSo 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

MasterProgramming LanguageFirst, you need to understand the basic syntax structure, that is, the program structure, data type, control structure and the 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:

/* Declaration part, starting with declare */ Declare V_id integer; V_name varchar (20 ); Cursor c_emp is select * from employee where emp_id = 3; /* Execution part, starting with begin */ Begin Open c_emp; -- open the cursor Loop Fetch c_emp into v_id, v_name; -- get data from the cursor Exit when c_emp % notfound; End loop; Close c_emp; -- close the cursor Dbms_output.put_line (v_name ); /* Exception Handling Section, starting with exception */ Exception When no_data_found then Dbms_output.put_line ('no data '); End;

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. Regardless ofCodeThe basic structure of a volume is composed of these three parts.

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:

Number [(precision, scale)]

(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:

Varchar2 [(size)]

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:

V_id number;

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

V_id: = 5;

3. Control Structure

The PL/SQL program segment has three program structures: Conditional structure, cyclic structure, and sequential structure.

Condition Structure

Similar to other languages, the syntax structure is as follows:

If condition then Statement1 Else Statement2 End if;

Loop Structure

This structure is not the same as other languages. There are three types of loop structures in PL/SQL programs:

A. Loop... End loop; B. While condition loop... End loop; C. For variable in low_bound... upper_bound loop... End loop;

"…" Indicates the loop body.

Sequential Structure

Actually, it is the use of Goto. However, from the perspective of program control, using less goto can make the program structure clearer.

4. Basic SQL commands

The database operating language used by PL/SQL is based on SQL. Therefore, familiarity with SQL is the basis for PL/SQL programming. Table 1-1 is a type of SQL language.

Table 1-1 SQL language classification

Category	SQL statement
Data Definition Language (DDL)	Create, drop, Grant, revoke ,...
Data manipulation language (DML)	Update, insert, delete ,...
Data Control Language (DCL)	Commit, rollback, savapoint ,...
Others	Alter system, connect, allocate ,...

You can refer to other information about the SQL language to understand the specific syntax structure. I will not go into detail here.
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.

Create or replace procedure test_procedure V_f11 number: = 1;/* declare the variable and assign the initial value */ V_f12 number: = 2; V_f21 varchar2 (20): = 'first '; V_f22 varchar2 (20): = 'second '; Begin Insert into T1 values (v_f11, v_f21 ); Insert into T1 values (v_f12, v_f22 ); End test_procedure;/* test_procedure can be omitted */

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:

Cursor cursor_name is SQL statement;

The first code in this article contains the following sentence:

Cursor c_emp is select * from employee where emp_id = 3;

The description is embedded into the pseudo-dataset _ EMP, which represents the result set where all emp_id fields in the employee table are 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.

Original: http://www.yesky.com/SoftChannel/72342371928965120/20040913/1853193.shtml
■ Author: Feng Jie ■ Source: Skynet