I. PL / SQL's destination structured query language (SQL) is used to access a common language in the relational database, which belongs to the fourth generation language (4GL), and its execution characteristics are non-process, namely It is not necessary to indicate the specific method and path of the execution, but simply call the corresponding statement to achieve the result. Obviously, this language that does not pay attention to any detail has great convenience for developers.
However, for some complex business processes, the corresponding procedures are required to describe, then 4GL is a bit of powerful. The emergence of PL / SQL is to solve this problem, and PL / SQL is a procedural language, which is the third generation language. It focuses on processing details as languages such as C, C , Java, so it can be used to implement comparison Complex business logic.
This tutorial is divided into two parts. The first part mainly discusses the PL / SQL programming basis, and the second part combines a case to explain PL / SQL programming. I hope the reader can have a general understanding of the PL / SQL programming after reading this article, and lay a foundation for the future depth PL / SQL programming.
Second, PL / SQL programming foundation
Mastering a programming language is primarily to understand its basic syntax structure, namely the program structure, data type, control structure, and the corresponding embedded function (or programming interface).
1, PL / SQL program structure
The PL / SQL program is based on the block (block). As shown below as a complete PL / SQL block:
/ * Declaration part, starting with DECLAR * / declare v_id integer; v_name varchar (20); cursor c_emp is select * from employee where emp_id = 3; / * Execution section, starting with Begin * / begin open c_emp; - Open Cursor LOOP FETCH C_EMP INTO V_ID, V_NAME; - From the cursor data exit when c_emp% notfound; end loop; close c_emp; - Close the cursor dbms_output.put_line (v_name); / * Abnormal processing section, start * / Exception When NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('no data');
As seen from the above PL / SQL block, the entire PL / SQL block divides three parts: declaration part (starting with DECLARE), the execution section (starting with the Begin) and the exception processing section (beginning with an Exception). The execution section is necessary, and the other two are optional. Regardless of the amount of code of the PL / SQL block, its basic structure is composed of these three parts.
2, variable declaration and assignment
PL / SQL is mainly used for database programming, so all data types in the Oracle database are one or one, which is generally divided into digital, Boolean, character types, and date types. In order to facilitate understanding of the routines behind, two common data types are briefly introduced: Number, Varchar2.
Number
Used to store integers and floating point. The range is 1e-130 ~ 10E125, which uses the syntax:
Number [(Precision, Scale)]
Among them, Precision, Scale is optional, precision represents the number of all numbers, and Scale represents the number of numbers on the right side of the decimal point.
VARCHAR2
Used to store a growing string, whose syntax is: varchar2 [(size)]
Where SIZE is optional, indicating the maximum length that the string can be stored.
Declaring variables in PL / SQL is not only the same as other languages, which is declared from right to left, such as declaring a Number type variable V_ID, which should be:
v_id number;
If you assign the above V_ID variable, you can't use "=" should be ": =", that is, the form is:
v_id: = 5;
3, control structure
There are three program structures in the PL / SQL program: conditional structure, loop structure, and sequential structure.
Conditional structure
Similar to other languages, the syntax structure is as follows:
IF Condition1State1LseStatement2nd IF;
Circulating structure
This structure is not much in other languages, there are three cyclic 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;
The "..." represents the cyclic body.
Sequential structure
Actually, Goto is used, but from the perspective of program control, try to use GOTO to make the program structure more clear.
4, SQL basic command
PL / SQL uses database operation language or SQL based, so familiar with SQL is the basis for performing PL / SQL programming. Table 1-1 is classified for 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, ... Other ALTER SYSTEM, CONNECT, Allocate, ...
You can refer to other information about the SQL language to understand the specific syntax structure, here is not described here.
Third, the process and function in the process and functions in the function PL / SQL are the same as the concept of other languages, and are all statements that are combined to perform certain tasks. The process has no return value, and the function has a return value. Its grammar structure is:
Process: CREATE OR Replace Procedure Procname (parameter list) AS PL / SQL statement block
Function: CREATE OR REPLACE FUNCNAME (Parameter List) Return Return Value AS PL / SQL Statement Block
Here is the application of more ways to use, one will be given below:
Question: Suppose there is a table T1, there are two fields of F1 and F2, F1 is Number type, f2 is VARCHAR2 type, and then write two records in T1, and the content is customized.
Create or replace process_procedure as v_f11 number: = 1; / * Declare variable and assign initial value * / v_f12 number: = 2; v_f21 varchar2 (20): = 'first'; v_f22 varchar2 (20): = 'second'; Begininsert INTO T1 VALUES (V_f11, V_F21); INSERT INTO T1 VALUES (V_F12, V_F22); End Test_Procedure; / * Test_Procedure can omit * /
At this point, the Test_Procedure stored procedure has been completed, and then it can be called in other PL / SQL blocks or processes after compiling. Since the function is very similar to the process, it will not be repeated here. Fourth, cursor
It is especially proposing the concept of a cursor because it is important in the programming of PL / SQL. It is defined as: uses a cursor to refer to the result set returned by a DML SQL operation. That is, when a query operation for the database returns a set of result sets, with a cursor, the set of result sets are marked, and then the data information in the result set is obtained by the operation of the cursor. The grammatical structure defined the cursor is as follows:
CURSOR CURSOR_NAME IS SQL statement;
There is a sentence in the first paragraph of this article as follows:
Cursor C_Emp is SELECT * from Employee WHERE EMP_ID = 3;
Its meaning is to define a cursor C_EMP that represents the result set of all EMP_ID fields in the EMPLOYEE table. When you need to operate the result set, you must complete three steps: open the cursor, remove the data in the cursor, close the cursor. Please refer to the annotation of the annotation of the first paragraph of this article to understand the three steps of the cursor operation.
Five, other concepts
The concept of PL / SQL in the package is important, mainly to encapsulate a set of functions and functions of a set of functions, similar to the concept of object-oriented name space.
The trigger is a special stored procedure, which is special, and is a message notification between multiple tables when a specific event occurs.
Six, debugging environment
PL / SQL debugging environment is currently more, in addition to Oracle's self-contained environment SQL * Plus, I recommend Toad tool, the tool user interface is friendly, and can improve the programming efficiency.
This article mainly explains the basics of PL / SQL, familiar with this part of the content, can be prepared and applied, which is helpful for improving the performance efficiency of the database server.
