Summary: PL/SQL (procedural language/structured Query Language) is a procedural language that belongs to a third-generation language that is as focused on processing details as C, C + +, and Java, and can be used to implement more complex business logic. It allows SQL's data manipulation language and query statements to be included in the Block structure (block_structured) and the code process language, making PL/C a powerful transactional language.
First, Background introduction
Structured Query Language (structured query Language, SQL) is used to access the relational database of a common language, belonging to the fourth generation language (4GL), its implementation is non-procedural, that is, do not specify the implementation of specific methods and ways, Instead, simply invoke the corresponding statement to get the result directly. Obviously, the language that does not care about any implementation details is very convenient for developers. However, some complex business processes require a corresponding program to describe, in which case 4GL is somewhat powerless. The appearance of PL/SQL is to solve this problem, PL/SQL is a process language, belongs to the third generation language, it is like C, C + +, Java and other languages focus on processing details, can be used to achieve more complex business logic. This paper mainly introduces the programming basis of PL/SQL, so that the novice has a general understanding and basic grasp of PL/SQL language.
second, the basic knowledge of programming
1. Program Structure
The PL/SQL program is based on blocks (block), and the entire PL/SQL block is divided into three parts: the Declaration section (beginning with declare), the execution part (beginning with begin), and the Exception handling section (starting with exception). The execution part is mandatory, and the other two parts are optional. Regardless of the amount of code in the PL/SQL program section, the basic structure is composed of these three parts. As shown below is a complete PL/SQL block:
/* Declaration section, beginning with declare */
declare v_id integer;
V_name varchar (20);
/* Execution section, beginning with BEGIN */
Begin open C_emp; Open cursor
Loop
Fetch c_emp into v_id,v_name; //Fetch data from the cursor
Exit when C_emp%notfound;
End Loop;
Close c_emp; //Close Cursors
/* Exception handling section, start with exception */
exception
When No_data_found Then
Dbms_output. Put_Line (' no data ');
2. Control structure
There are three program structures in the PL/SQL program segment: conditional structure, loop structure, and sequential structure.
1) Conditional structure
Similar to other languages, the syntax structure is as follows:
If condition Then
Statement1
Else
Statement2
2) Cycle structure
This structure is not the same as other languages, there are three looping structures in the PL/SQL program:
A. Loop ... end loop;
B. While condition loop ... end loop;
C. For variable in Low_bound. . Upper_bound loop ... end loop;
One of the "..." represents the loop body.
3) Sequential structure
Actually is the use of Goto, but from the point of view of program control, as little as possible to use Goto can make the program structure more clear.
3. Variable declaration and assignment
PL/SQL is primarily used for database programming, so all of its data types correspond to one by one of the field types in the Oracle database, and are broadly divided into digital, Boolean, character, and date-based. Here is a brief description of two common data types: number, varchar2.
Number
Used to store integers and floating-point numbers. The range is 1e130~10e125, and its use syntax is:
number[(precision, scale)]
Where (precision, scale) is optional, precision represents the number of all numbers, and scale represents the number of digits to the right of the decimal point.
Varchar2
Used to store a variable-length string with the following syntax:
varchar2[(size)]
Where size is optional, indicating the maximum length that the string can store.
Declaring a variable in PL/SQL is not the same as other languages, it is declared in a right-to-left way, such as declaring a variable of type number v_id, which should be in the form:
V_ID number;
If you assign a value to the v_id variable above, you cannot use "=", you should use ": =", that is, the form is:
V_ID: = 5;
4. SQL BASIC Commands
The database manipulation language used by PL/SQL is also based on the data, so familiarity with SQL is the basis for PL/C programming. The classification of the SQL language is roughly as follows:
1) Data Definition language (DDL): Create,drop,grant,revoke, ...
2) Data Manipulation language (DML): Update,insert,delete, ...
3) Data Control Language (DCL): Commit,rollback,savapoint, ...
4) Other: Alter system,connect,allocate, ...
The specific syntax structure can refer to other information about the SQL language, which is not mentioned here.
Iii. processes and Functions
The procedures and functions in PL/SQL are, like the procedures and functions of other languages, statements that are grouped together in order to perform certain tasks. The procedure 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 (argument list) return value as PL/SQL statement block
For ease of understanding, examples are as follows:
Problem: Suppose there is a table T1, there are F1 and F2 two fields, F1 for number type, F2 for VARCHAR2 type, to write two records in T1, content custom.
V_F11 number: = 1; /* Declare the variable and assign the initial value */
V_F12 number: = 2;
V_F21 varchar2: = ' first ';
V_f22 varchar2 (): = ' second ';
Insert into T1 values (V_F11, V_F21);
Insert into T1 values (V_F12, v_f22);
At this point, the test_procedure stored procedure is completed and can be invoked in other PL/SQL blocks or procedures after compilation. Functions are very similar to procedures and are no longer detailed here.
Iv. Cursors
A cursor is defined as a result set returned by using cursors to refer to a DML SQL operation. That is, when a query operation on a database returns a set of result sets, a cursor is used to label the set of results, and later to get the data information in the result set by manipulating the cursor. The concept of a cursor is particularly important here because of its importance in PL/SQL programming. The syntax structure for defining cursors is as follows:
CURSOR cursor_name is SQL statement;
In the first paragraph of this article, there is a sentence as follows:
Cursor C_emp is a SELECT * from employee where emp_id=3;
The implication is to define a cursor c_emp that represents the result set of all the emp_id fields in the Employee table as 3. When you need to manipulate the result set, you must complete three steps: Open the cursor, use the FETCH statement to remove the data from the cursor, and close the cursor. Refer to the comments in the first section of this article for a three-step procedure to understand cursor operations.
v. Other Concepts
The concept of a package in PL/SQL is important, mainly to encapsulate a set of functionally similar processes and functions, similar to the concept of namespaces in object-oriented.
A trigger is a special kind of stored procedure whose callers are special and are called when a particular event occurs, primarily for message notification between multiple tables.
Getting started with Oracle PL/SQL language
