PL/SQL (Procedural Language/SQL) is a procedural language and belongs to the third-generation language. It pays attention to details like C, C++, Java and other languages, and can be used to implement more complex business logic. It allows SQL data manipulation language and query statements to be included in block structure (block_structured) and code procedure language, making PL/SQL a powerful transaction processing language. In terms of Oracle database management, PL/SQL is a procedural language extension to Structured Query Language (SQL). The purpose of PL/SQL is to combine database language and procedural language.
The advantages of
PL/SQL are as follows: PL/SQL is a high-performance transaction-based language that can run in any ORACLE environment and supports all data processing commands. Process SQL data definition and data control elements by using PL/SQL program units.
PL/SQL supports all SQL data types and all SQL functions, and supports all ORACLE object types.
PL/SQL blocks can be named and stored in the ORACLE server, and can also be called by other PL/SQL programs or SQL commands, any customer/Server tools can access PL/SQL programs, with good reusability.
The ORACLE data tool can be used to manage the security of PL/SQL programs stored in the server. Can authorize or revoke the ability of other users of the database to access PL/SQL programs.
PL/SQL code can be written using any ASCII text editor, so it is very convenient for any operating system that ORACLE can run
For SQL, ORACLE must process every SQL statement at the same time, which means doing in a network environment. An independent call must be processed by the oracle server, which takes up a lot of server time and causes network congestion. And PL/SQL is sent to the server in the entire sentence block, which reduces network congestion.
The biggest disadvantage of PL/SQL is that the logic is concentrated in the database for processing, which occupies valuable database computing resources, resulting in low system concurrency and limiting system expansion.
I started using SQL in around 1999, and gradually started to use PL/SQL and other stored procedures. When doing distributed systems with C/S architecture, because PL/SQL and other storage processes have the advantages of high efficiency, convenient debugging, easy update (no need to update the client), etc., almost all complex logic uses PL/SQL To achieve. Because the database pressure is relatively small in the face of business distributed systems, there is no problem in doing so. But when I switched to the B/S architecture, I ran into big troubles. The centralized B/S platform and database resources are the most precious. If you continue to use PL/SQL extensively, the concurrency of the system will not be mentioned at all, so All the logic is transferred from the database (PL/SQL) to the middle layer, and then further splits such as servicing. I still continue to use PL/SQL, but it is mainly used in situations where the logic of end-of-day processing and ETL is more complicated and does not require large concurrency.
Let's talk together: when and under what circumstances did you start to contact PL/SQL?
What do you mainly use PL/SQL for now?
Is there anything you like or need to improve in PL/SQL?
Are you optimistic about the future development of PL/SQL and even Oracle database?