I. background
Structured Query Language (SQL) is a common Language used to access relational databases. It belongs to the fourth-generation Language, 4GL). The execution features of Structured Query Language are non-procedural, that is, you do not need to specify the specific execution method.Method and method, 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, some complex business processes require corresponding programs to describe. In this case, 4GL is powerless. To solve this problem, PL/SQL is a procedural language and belongs to the third generation language, similar to C, C ++, Java, and other languages, it focuses on processing details and can be used to implement complicated business logic. This article mainly introduces the programming basics of PL/SQL, so that the compiler has a general understanding and basic understanding of PL/SQL language.
Ii. Basic programming knowledge
1. Program Structure
PL/SQL programs are based on block blocks. The entire PL/SQL block consists of three parts: the declaration part starts with declare) and the execution part starts with begin) and exception Handling ). 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. 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, start with begin */begin open c_emp; // open the cursor loopfetch c_emp into v_id, v_name; // retrieve 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 */exceptionwhen no_data_found thendbms_output.PUT_LINE ('no data'); end;
|
2. Control Structure
The PL/SQL program segment has three program structures: Conditional structure, cyclic structure, and sequential structure.
1) condition Structure
Similar to other languages, the syntax structure is as follows:
if condition thenstatement1elsestatement2end if ;
|
2) 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.
3) Ordered Structure
Actually, it is the use of goto. However, from the perspective of program control, using less goto can make the program structure clearer.
3. 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 are generally divided into numeric, Boolean, numeric, and date types. Here we will briefly introduce two common data types: number and varchar2.
Number
Used to store integers and floating-point numbers. The value range is 1e130 ~ 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 "=" cannot be used to assign values to the above v_id variable, ": =" should be used, that is, the form is:
V_id: = 5;
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. The SQL language classification is roughly as follows:
1) Data Definition Language (DDL): Create, Drop, Grant, Revoke ,...
2) Data Control Language (DML): Update, Insert, Delete ,...
3) Data Control Language (DCL): Commit, Rollback, Savapoint ,...
4) Others: Alter System, Connect, Allocate ,...
For more information about the specific syntax structure, see SQL language.
Iii. Process and Function
The procedures and functions in PL/SQL are the same as those in other languages. They are combined 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
For ease of understanding, the following is an example:
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 two records must be written to t1, with the content customized.
Create or replace procedure test_procedure as 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. After compilation, it can be called in other PL/SQL blocks or procedures. Functions have great similarity with processes, so we will not detail them here.
Iv. cursor
A cursor is defined as a 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 cursor concept is put forward here because it is very important in PL/SQL programming. 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;
It defines a cursor c_emp, which represents the result set in which all the 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.
