The Oracle tutorial you are looking at is an introductory overview of Oracle Pl/sql.
First, the purpose of pl/sql appearance
Structured Query Language (structured query Language, referred to as SQL) is used to access relational database a common language, which belongs to the fourth generation language (4GL), its performance is not a process, that is, do not specify the implementation of specific methods and ways, But simply call the corresponding statement to get the result directly. Obviously, this kind of language that does not pay attention to any implementation details is very convenient for developers. However, for some complex business processes and require the appropriate procedures to describe, then 4GL there is nothing to do. Pl/sql's appearance is to solve this problem, Pl/sql is a procedural language, belongs to the third generation language, it and C,c++,java and other languages as attention to processing details, so can be used to implement more complex business logic.
This tutorial is divided into two parts, the first part focuses on the Pl/sql programming basis for discussion, the second part of the combination of a case to explain pl/sql programming. I hope the reader can have a general understanding of Pl/sql programming after reading this article, and lay a foundation for further pl/sql programming in the future.
Second, Pl/sql Programming Foundation
Mastering a programming language is primarily about understanding its basic grammatical structure, namely, program structure, data type, control structure, and the corresponding inline function (or programming interface).
1. PL/SQL Program Structure
Pl/sql programs are based on blocks (block) as the basic unit. As shown below a complete pl/sql block:
As seen from the Pl/sql program section above, the entire pl/sql block is divided into three parts: the declaration part (beginning with declare), the execution part (beginning with begin), and the Exception handling section (beginning with exception). Where the execution part is required, the other two parts are optional. Regardless of the amount of code in the Pl/sql program segment, the basic structure is made up of these three parts.
2. Variable declaration and assignment
Pl/sql is primarily used for database programming, so all of its data types correspond to the one by one field types in Oracle databases, which are generally classified as numeric, Boolean, character, and date. To facilitate understanding of the following routines, here are two common data types: number, varchar2.
Number
Used to store integers and floating-point numbers. The range is 1E-130 ~10e125, and its use syntax is:
Where (precision, scale) is optional, precision represents the number of all numbers, scale represents the number of digits to the right of the decimal point.
Varchar2
Used to store a variable length string, using the syntax:
The size is optional, representing the maximum length that the string can store.
Declaring a variable in pl/sql is not the same as other languages, it is declared from right to left, such as declaring a variable v_id of type number, which should be in the form of:
If you assign a value to the v_id variable above, you cannot use "=" to use ": =", which is the form:
[NextPage]
First, the purpose of pl/sql appearance
Structured Query Language (structured query Language, referred to as SQL) is used to access relational database a common language, which belongs to the fourth generation language (4GL), its performance is not a process, that is, do not specify the implementation of specific methods and ways, But simply call the corresponding statement to get the result directly. Obviously, this kind of language that does not pay attention to any implementation details is very convenient for developers. However, for some complex business processes and require the appropriate procedures to describe, then 4GL there is nothing to do. Pl/sql's appearance is to solve this problem, Pl/sql is a procedural language, belongs to the third generation language, it and C,c++,java and other languages as attention to processing details, so can be used to implement more complex business logic.
This tutorial is divided into two parts, the first part focuses on the Pl/sql programming basis for discussion, the second part of the combination of a case to explain pl/sql programming. I hope the reader can have a general understanding of Pl/sql programming after reading this article, and lay a foundation for further pl/sql programming in the future.
Second, Pl/sql Programming Foundation
Mastering a programming language is primarily about understanding its basic grammatical structure, namely, program structure, data type, control structure, and the corresponding inline function (or programming interface).
1. PL/SQL Program Structure
Pl/sql programs are based on blocks (block) as the basic unit. As shown below a complete pl/sql block:
As seen from the Pl/sql program section above, the entire pl/sql block is divided into three parts: the declaration part (beginning with declare), the execution part (beginning with begin), and the Exception handling section (beginning with exception). Where the execution part is required, the other two parts are optional. Regardless of the amount of code in the Pl/sql program segment, the basic structure is made up of these three parts.
2. Variable declaration and assignment
Pl/sql is primarily used for database programming, so all of its data types correspond to the one by one field types in Oracle databases, which are generally classified as numeric, Boolean, character, and date. To facilitate understanding of the following routines, here are two common data types: number, varchar2.
Number
Used to store integers and floating-point numbers. The range is 1E-130 ~10e125, and its use syntax is:
Where (precision, scale) is optional, precision represents the number of all numbers, scale represents the number of digits to the right of the decimal point.
Varchar2
Used to store a variable length string, using the syntax:
The size is optional, representing the maximum length that the string can store.
Declaring a variable in pl/sql is not the same as other languages, it is declared from right to left, such as declaring a variable v_id of type number, which should be in the form of:
If you assign a value to the v_id variable above, you cannot use "=" to use ": =", which is the form:
[NextPage]
Iii. Processes and functions
The procedures and functions in pl/sql, like the concepts of processes and functions in other languages, are statements that are grouped together to perform certain tasks. The procedure has no return value and the function has a return value. Its grammatical structure is:
Procedure: Create or replace procedure procname (argument list) as PL/SQL statement block
Function: Create or Replace function funcname (argument list) return returns value as PL/SQL statement block
Here is an example of the use of a more descriptive process:
Question: Suppose there is a table T1, there are F1 and F2 two fields, F1 is number type, F2 is varchar2 type, then write two records to T1, the content is customized.
At this point, the test_procedure stored procedure has been completed and then compiled to be invoked in other pl/sql blocks or procedures. Because functions and processes have a lot of similarity, this is not repeated here.
Four, cursors
The concept of a cursor is specifically presented here because it is very important in pl/sql programming. It is defined as: using Grand to refer to the result set returned by a DML SQL operation. That is, when a query operation on a database returns a set of result sets, the set of results is annotated with a cursor, and the data information in the result set is later obtained by manipulating the cursor. The syntax structure for defining cursors is as follows:
One sentence in the first paragraph of this article is as follows:
The implication is to define a cursor c_emp that represents the result set for all emp_id fields in the Employee table 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. Use the comments in the first paragraph of this article to understand the three steps of cursor manipulation.
V. Other concepts
The concept of package in Pl/sql is important to encapsulate a set of functionally similar processes and functions, similar to the concept of namespace in object-oriented.
A trigger is a special kind of stored procedure whose callers are special and are invoked only when a particular event occurs, mainly for message notifications between multiple tables.
Six, debugging environment
Pl/sql debugging environment At present more, in addition to Oracle with debugging environment Sql*plus outside, I recommend toad this tool, the tool user-friendly interface, you can improve the efficiency of programming.
This article mainly explains the basic part of Pl/sql, familiar with this part of the content can be stored in the process of writing and application, to improve the implementation of the database server side of the efficiency is very helpful.
Previous page
