Introduction to Oracle PL and SQL
- 2020-06-03 08:39:06
- OfStack
1. The purpose of PL/SQL
Structured query language (Structured Query Language, referred to as SQL) is used to access a relational database 1 common language, it belongs to the fourth generation of language (4GL), its execution characteristics is non-procedural, that is, do not specify the specific method and way of execution, but simply call the corresponding statement to directly obtain the results. Obviously, a language that doesn't care about any implementation details is of great convenience to developers. However, for some complex business processes that require corresponding programs to describe them, 4GL is somewhat powerless. PL/SQL is a procedural language belonging to the third generation. Like C,C++,Java and other languages, PL/SQL focuses on processing details, so it can be used to implement more complex business logic.
This tutorial is divided into two parts. Part 1 discusses the programming basics of PL/SQL, and part 2 introduces PL/SQL programming with a case study. It is hoped that readers can have an overall understanding of PL/SQL programming after reading this article, which will lay a foundation for in-depth PL/SQL programming in the future.
2. PL/SQL Programming basics
The first step in mastering a programming language is to understand its basic syntactic structure, that is, program structure, data type, control structure, and corresponding embedded functions (or programming interfaces).
1. PL/SQL program structure
PL/SQL programs are based on blocks (block). 1 complete PL/SQL block is shown below:
As you can see from the PL/SQL program segment 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 part (beginning with exception). The execution part is required and the other two parts are optional. The basic structure of the PL/SQL segment, no matter how much code it contains, consists of these three parts.
2. Variable declaration and assignment
PL/SQL is mainly used for database programming, so all its data types correspond to the field types in oracle database, which are roughly divided into number type, Boolean type, character type and date type. To help you understand the following routines, here are two common data types: number and varchar2.
number
Used to store integers and floats. The range is 1ES75en-130 ~ 10E125, and its usage syntax is:
Where (precision, scale) is optional, precision is the number of all Numbers, and scale is the number of Numbers to the right of the decimal point.
varchar2
Used to store variable-length strings, the syntax is:
Where size is optional and represents the maximum length that the string can store.
In PL/SQL, variables are declared from right to left. For example, a variable of type v_id is declared in the following form:
If the above v_id variable is assigned, instead of "=", it should be ":=", i.e.
[NextPage]
1. The purpose of PL/SQL
Structured query language (Structured Query Language, SQL for short) is used to access a relational database 1 common language, it belongs to the fourth generation of language (4GL), its execution characteristics is non-procedural, that is, do not specify the specific method and way of execution, but simply call the corresponding statement to directly obtain the results. Obviously, a language that doesn't care about any implementation details is of great convenience to developers. However, for some complex business processes that require corresponding procedures to describe, 4GL is somewhat powerless. PL/SQL is a process language and belongs to the third generation. Like C,C++ and Java, it focuses on processing details and can be used to implement complex business logic.
This tutorial is divided into two parts. Part 1 discusses the programming basics of PL/SQL, and part 2 introduces PL/SQL programming with a case study. It is hoped that readers can have an overall understanding of PL/SQL programming after reading this article, and lay a foundation for further PL/SQL programming.
2. PL/SQL Programming basics
The first step in mastering a programming language is to understand its basic syntactic structure, that is, program structure, data type, control structure, and corresponding embedded functions (or programming interfaces).
1. PL/SQL program structure
PL/SQL programs are based on blocks (block). The PL/SQL block of 1 section is shown below:
As you can see from the PL/SQL section above, the entire PL/SQL block is divided into three parts: the declaration part (starting with declare), the execution part (starting with begin), and the exception handling part (starting with exception). The execution part is required and the other two parts are optional. The basic structure of an PL/SQL segment, no matter how large the amount of code, consists of these three parts.
2. Variable declaration and assignment
PL/SQL is mainly used for database programming, so all its data types correspond to the field types in the oracle database, which are roughly divided into number type, Boolean type, character type and date type. To help you understand the routines that follow, here are two common data types: number and varchar2.
number
Used to store integers and floats. The range is 1E-130 ~ 10E125, and its usage syntax is:
Where (precision, scale) is optional, precision is the number of all Numbers, scale is the number of Numbers to the right of the decimal point.
varchar2
Used to store variable-length strings, the syntax is:
Where size is optional and represents the maximum length that the string can store.
In PL/SQL, variables are declared from right to left. For example, a variable of number type v_id is declared in the following form:
If the above variable v_id is assigned, instead of "=", it should be ":=", i.e.
[NextPage]
3. Procedures and functions
Procedures and functions in PL/SQL, like the concept 1 of procedures and functions in other languages, are statements that are combined from 1 to 1 in order to perform a given task. Procedures have no return values; functions have return values. Its grammatical structure is:
Procedure: Create or replace procedure procname(parameter list) as PL/SQL statement block
Create or replace function funcname(argument list) return return value as PL/SQL statement block
To illustrate the application of the process in more aspects, an example is given below:
Question: Suppose you have a table t1 with two fields f1 and f2. f1 is of TYPE number and f2 is of type varchar2, and then write two records to t1 with the content determined.
At this point, the test_procedure stored procedure is complete and can then be compiled and invoked in other PL/SQL blocks or procedures. Since the function is very similar to the process, it is not repeated here.
4. The cursor
The cursor concept is particularly raised here because of its importance in PL/SQL programming. It is defined as a cursor that refers to the result set returned by an DMLSQL operation. That is, when a query operation to a database returns a set of result sets, a cursor is used to mark the set of result sets, and then the data information of the result sets is obtained through cursor operations. The syntax structure for defining a cursor is as follows:
In the first paragraph of this article there is a sentence that reads as follows:
This means to define a cursor c_emp, which represents the result set of 3 for all employee tables in the employee table. When you need to manipulate the result set, you must complete three steps: open the cursor, use the fetch statement to get the data out of the cursor, and close the cursor. Refer to the comments in section 1 of this article to understand the three steps of cursor operation.
5. Other concepts
The concept of packages in PL/SQL is important in that it encapsulates a set of closely functional processes and functions, similar to the concept of namespaces in object orientation.
Triggers are special stored procedures that are called when a particular event occurs and are used for notification of messages between tables.
6. Debugging environment
PL/SQL debugging environment is more, in addition to Oracle has its own debugging environment Sql*plus, I recommend TOAD this tool, the user interface is friendly, can improve the programming efficiency.
This paper mainly explains the basic part of PL/SQL. After you are familiar with this part, you can write and apply stored procedures, which is very helpful for improving the execution efficiency of database server.
On 1 page