What is PL/SQL (Procedural Language/Structured Query Language)?
In Oracle database management, PL/SQL is a procedural language extension to Structured Query Language (SQL). PL/SQL is used to combine database language and procedural programming language features and constructs so programmers can write procedural code that includes SQL easily, as though it were a single language.
During compilation, the Oracle Database server compiles PL/SQL program units and stores them inside the database. Since PL/SQL can include SQL statements in its syntax, PL/SQL and SQL run in the same server process during runtime to improve program execution efficiency.
An advantage of PL/SQL is how it accommodates writing procedural code that includes SQL so easily, as if it were a single language. PL/SQL is tightly integrated with SQL -- the most popular, widely used language for database manipulation. This integration lets programmers use all SQL statements, functions and operators. There's rarely any need to perform data type conversions with PL/SQL since the language supports all SQL data types. Also, programmers can use both static and dynamic SQL to build more flexible, versatile applications.
Specific application programming interface (API) knowledge is not needed to map data types, prepare statements or even to process result sets. Developers can use APIs in PL/SQL to abstract complex data structures and security implementations from client applications. For example, by adding code -- inserts, updates, deletes -- to an API, they can wrap the transactional processing in that API and avoid having to create triggers, say, to implement a specific business functionality in an application. Once the API implementation is set, it can be tuned as needed. The client application remains intact during the tuning, reducing the need for application redeployment.
With PL/SQL, business logic is built in the database through the use of blocks, with each block containing multiple SQL statements. As a result, the client code needs only a single database call per transaction. The application sends a single PL/SQL block to the database, and the database processes this entire block in one pass. This approach reduces network overhead.
PL/SQL allows application logic to be stored in the database itself. SQL statements and other PL/SQL constructs are grouped together and stored as a schema object (PL/SQL procedure or function). These statements and constructs run as a unit to solve problems or perform related tasks. The centralization of application logic in the database can increase program security and developer productivity.
Another PL/SQL benefit relates to business logic coding. In middle-tier applications, multiple interactions may occur between the application server and the database to process and execute a single business transaction. This increases overhead to the network traffic between the application and the database.
Finally, PL/SQL is a good choice when working with Oracle Database.
Oracle Database is a relational database management system (RDBMS) that controls data storage, organization and retrieval in a relational database. It implements object-oriented features, like user-defined types, inheritance and polymorphism, and ensures that physical data storage is independent from logical data structures. Since it extends the relational model of a DBMS to an object-relational model, it supports the storage and manipulation of complex business models in a relational database.
Gleaning application benefits from Oracle Database requires maintaining correct and complete data. An effective way to do this involves exposing the database using an interface that hides the implementation details, such as the tables and SQL statements. PL/SQL facilitates this. PL/SQL lets developers adopt the SmartDB paradigm, in which the code implements the surrounding business logic and the subprograms inside the database issue the SQL statements. Data can be changed and viewed only through a PL/SQL interface, ensuring correctness and completeness.
Blocks and procedures in PL/SQL
PL/SQL is a proprietary procedural language extension to SQL. It's also a full-fledged, portable, high-performance language ideal for transaction processing.
The basic unit of a PL/SQL source program is a block. Using blocks, programmers can directly write and run SQL statements in PL/SQL and add the required code to interface between the SQL statement and PL/SQL code.
Every PL/SQL block does the following:
- Groups related declarations and statements with the declarations being local to the block.
- Is an executable statement,
- Is defined by the keywords DECLARE, BEGIN, EXCEPTION and END.
- Can be nested and, therefore, appear in another block wherever an executable statement is allowed.
- Can have a label.
PL/SQL blocks can be submitted to an interactive tool and also embedded in an Oracle precompiler or Oracle Cloud Infrastructure program. Anonymous blocks are those not stored in the database and compiled every time they're loaded into memory. Block compilation always happens in three stages:
- Syntax checking.
- Semantic checking.
- Code generation.
A named PL/SQL block is known as a PL/SQL subprogram. Subprograms, which can be either procedures or functions, can be invoked repeatedly.
A procedure is a specific type of PL/SQL subprogram that can perform an action but not return a value. Procedures usually have parameters that pass information into subprograms when they're called and can be used in different contexts and circumstances to create more flexible subprograms.
A PL/SQL program that is stored in a database in compiled form and can be called by name is referred to as a stored procedure. Stored procedures can be used as building blocks for different applications connected to Oracle Database, and they can accept parameters at invocation.
A PL/SQL stored procedure that is stored in the database and implicitly started when an INSERT, UPDATE or DELETE statement is issued against an associated table is called a trigger. PL/SQL enables programmers to specify the following:
- The event that occurs in the database and that the trigger will run in response to.
- When the trigger will fire -- before or after the event.
- Whether the trigger should run for each event or for each row the event affected.
The other type of PL/SQL subprogram is known as a function. Unlike procedures, functions compute and return a value.
How PL/SQL works
PL/SQL blocks can be variables, constants, cursors or exceptions. Blocks are defined by the keywords DECLARE, BEGIN & END, and EXCEPTION, which divide the block into a declarative part, an executable part and an exception-building part, respectively.
The declaration section of the block is written first. It is used to define and initialize constants, variables and similar items; if a variable is not initialized, it defaults to a NULL value. It is written first but is optional. The defined types, variables and similar items are manipulated in the executable part, which is the only part required to execute a PL/SQL program. The exception-handling part deals with errors that occur during execution.
Blocks in PL/SQL, being executable statements, can be nested.
PL/SQL is designed to compute and return a single scalar value or a single collection, such as a nested table or VARRAY. Users can create their own functions to supplement those Oracle provides. While functions can be used in an SQL statement, procedures cannot.
The PL/SQL architecture consists of two key building blocks: the PL/SQL engine that executes procedural code and the SQL statement executor that executes SQL code. There's tight binding between SQL and PL/SQL. This is what makes PL/SQL look like a single language, enabling developers to seamlessly use all SQL statements, functions, operators and data types.
PL/SQL program units reside in a database. When an application calls a stored procedure, Oracle Database loads the compiled program unit into the shared pool in the system global area. The PL/SQL engine, a special component of Oracle Database -- although it can also be installed in an application development tool, like Oracle Forms -- accepts any valid PL/SQL unit as input. It works with the SQL statement executor to run and process the statements in the PL/SQL procedure.
Developers can construct several types of program units with PL/SQL: anonymous blocks, procedures, functions and packages. Anonymous blocks group related declarations and statements but are not named and are not stored in the database. Stored procedures or functions are stored in the database and can be called by name from an application. A package is a group of procedures, functions and variable definitions stored in the database and callable by other procedures, functions and variable definitions. Triggers are stored procedures associated with a database table, view or event.
To run PL/SQL code, developers can use the following:
- SQL Commands page.
- Script Editor page.
- SQL Command Line.
The SQL Commands page is the easiest option and supports reuse of SQL statements in the future since the statements can be saved as a script file in a database repository.
PL/SQL can run with either interpreted execution or native execution. Developers can choose the method in Oracle9i and later, although native execution -- where the source code of PL/SQL program units stored in the database is compiled directly to object code -- tends to offer the best performance on computationally intensive program units.
PL/SQL features
Some key features of PL/SQL include procedural elements, cursors, identifiers and embedded PL/SQL.
PL/SQL is similar to other procedural languages in that it allows developers to define numerous types of procedural elements, like constants, variables and subprograms. They can also control program flow and trap and isolate runtime errors.
Cursors act as pointers to the context area. This is a private in-memory SQL area that stores information about processing a specific parsed SQL statement. A PL/SQL block uses a cursor to query the database, retrieve a result set and access these records one row at a time.
Cursors can be implicit or explicit. Oracle Database manages implicit cursors; explicit code is not required to process these cursors. Users can also use cursors explicitly using certain interfaces. Explicit cursors can be programmatically managed. They work as named resources in a PL/SQL program and enable programmers to parse embedded SQL statements effectively and better control record access in a result set.
Implicit cursors are the default cursors in PL/SQL blocks. They're created in lieu of an explicit cursor when PL/SQL encounters a SELECT statement that returns just one row or when data manipulation language statements, like DELETE, INSERT or UPDATE, are encountered. Explicit cursors grant users better control over the context area and should be used with a SELECT statement query that returns more than one row. Both explicit and implicit cursors have the same output, even though their access is different.
PL/SQL allows the use of identifiers to name different program items and units, including constants, variables, exceptions and subprograms. Assigning identifiers to PL/SQL blocks facilitates constructing complex applications that can be understood and maintained easily.
PL/SQL can be embedded in high-level host languages, like C, C++, Common Business-Oriented Language (COBOL) and Fortran. Oracle provides the Pro* series of precompilers (Pro*C/C++) to embed SQL and PL/SQL in application programs from these other languages. With these precompilers, developers can create highly customized applications, monitor resource use and SQL statement execution, and also use object data types in C and C++ programs.
Programming tools, like Pro*COBOL, interpret PL/SQL blocks as single, embedded SQL statements. This lets users place PL/SQL blocks anywhere in a host program where they might normally place an SQL statement. When embedding PL/SQL blocks into host programs, be sure to declare the variables that will be shared with PL/SQL and bracket the PL/SQL block with the EXEC SQL EXECUTE and END-EXEC keywords, according to Oracle documentation.
PL/SQL includes extensive features for error handling, data abstraction and conditional compilation. When an error occurs, PL/SQL raises an exception, and normal execution automatically stops. Developers don't have to manually check every operation to confirm if it succeeded. They also don't have to get too involved with the details of data to work with its essential properties and design algorithms to manipulate the data. Finally, the conditional compilation feature means users can customize PL/SQL application functionality, say, by using new features with the latest database release or activating debugging or tracing statements in the integrated development environment (IDE) without removing source text.
After a Java stored procedure is loaded and published, users can call it. Users can connect to Oracle with the Pro*C program. Once the proper data is entered, the program automatically assigns all row values in the index-by tables to corresponding elements in the host arrays. The program repeatedly calls the procedure and displays each batch of data until no more data is found, according to Oracle's "PL/SQL User's Guide and Reference Release 2 (9.2)."
Advantages of PL/SQL
PL/SQL is built to allow programmers to use SQL in PL/SQL, meaning SQL statements can be mixed with procedural constructs. This makes it possible to use PL/SQL blocks and subprograms to group SQL statements before sending them to Oracle for execution. Users aren't required to convert between PL/SQL and SQL data types since PL/SQL fully supports SQL data types.
The combination of SQL's data manipulation power with the processing power of procedural languages makes it easy to break down complex problems into understandable procedural code and issue SQL statements directly inside PL/SQL programs. The stored procedures in PL/SQL are callable from many different Oracle Database clients, including Pro*C or Oracle Call Interface, and from Oracle Reports and Oracle Forms. The procedural code can also be reused across multiple applications, and PL/SQL variables can be interchanged with data inside a table.
Without PL/SQL, Oracle must process SQL statements one at a time. In a network environment, this can affect traffic flow and slow down response time. PL/SQL blocks can be compiled once, stored in executable form, run in the database server, and invoked repeatedly to improve response time and lower memory requirements and invocation overhead. Developers can also issue a SQL query and process the result set one row at a time.
PL/SQL enables users to send a block of statements to the database, reducing traffic significantly between the app and database. Performance is also improved because Oracle Database reuses the SQL statements every time the same code runs.
Since PL/SQL is a portable language, PL/SQL programs can run on any operating system or platform where Oracle Database runs.
When stored in subprograms, PL/SQL increases scalability by centralizing access processing on the database server. The shared server's memory facilities enable Oracle Database to support multiple concurrent users on a single node. Scalability can be improved further by multiplexing network connections using Oracle Connection Manager -- a router that provides features like session multiplexing, access control and protocol conversion useful for streamlining the sending of client connection requests, either to the next hop or directly to the database server.
Users need to maintain only one copy of a subprogram on the database server instead of multiple copies on each client system. This makes it easier to manage the programs and even alter subprograms without affecting the apps that use or invoke them.
PL/SQL also enables users to create applications that generate webpages directly from the database. This enables users to make their databases available on the web.
PL/SQL Server Pages enable users to develop webpages and are an alternative to coding a stored subprogram that writes the Hypertext Markup Language code one line at a time. During development, PL/SQL Server Pages are used as templates, and users can design layouts and write PL/SQL scripts to generate the content.
PL/SQL vs. SQL
Although they have similar-sounding names and PL/SQL is a procedural extension of SQL, SQL and PL/SQL are significantly different programming languages.
Unlike SQL, which is an industry-standard database language, PL/SQL is unique to Oracle and is meant to be used with Oracle Database. This means that the Oracle Database server compiles PL/SQL program units and the units themselves are stored inside the database. PL/SQL includes all the features of programming languages and integrates easily with SQL.
PL/SQL includes support for object-oriented programming. PL/SQL supports this programming paradigm using abstract data types. ADTs separate large systems into logical components to reduce programming complexity. Each ADT consists of a data structure, which itself is composed of variables, known as attributes, and subprograms that manipulate the data. In PL/SQL, ADTs are known as user-defined types and object types and are stored in the database.
SQL does not support variables or control structures, like FOR loops. PL/SQL supports both. Using control structures, users can manipulate and process Oracle data using conditional, iterative and sequential flow-of-control statements, like IF-THEN and FOR loops.
PL/SQL is primarily an application-oriented language suitable for applications like database administration automation, Extensible Markup Language document management and integration of databases with webpages. SQL is a data-oriented language whose chief purposes are to help users store, retrieve, manipulate and manage data in relational databases and to create and modify database structures.
How queries are processed in SQL vs. PL/SQL is another distinguishing difference. In SQL, the DBMS processes a query as single statement via a series of steps to retrieve data. In PL/SQL, blocks of statements are sent to the database with only a single database call from the client code per transaction, reducing network traffic.
SQL and PL/SQL use different syntax. Where SQL consists of only pure SQL statements, PL/SQL combines SQL statements with procedural logic, so programmers can write procedural code that includes SQL as if it were a single language.
Other differences between SQL and PL/SQL include the following:
- SQL does not provide error and exception handling, while PL/SQL does.
- SQL can be embedded into PL/SQL, while the reverse is not possible.
- SQL interacts directly with the database server, while PL/SQL does not.
- PL/SQL offers high processing speeds even for high-volume data; speeds are limited for high-volume data in SQL.
While SQL is considered a source of data for reports, webpages and screens, PL/SQL is viewed as an application language similar to Java and PHP. PL/SQL can be used to build, format and display reports, webpages and screens.
PL/SQL Developer IDE
Regardless of which programming language is used to write programs, programmers need a development environment and tools to help them execute and streamline many tasks. These tools may include a coder, compiler, interpreter and debugger. Such an environment is called an integrated development environment.
One popular IDE for PL/SQL is called PL/SQL Developer. Ideal for developing software -- stored program units -- for the Oracle Database environment, PL/SQL Developer is developed and built by Netherlands-based company Allround Automations. This IDE provides a powerful PL/SQL editor with useful features, like syntax highlighting, compiler hints, refactoring, code contents, bookmarks, multilevel undo and redo, and hyperlink navigation, all of which help to streamline and simplify PL/SQL coding, even for demanding tasks.
A debugger tool is integrated with PL/SQL Developer, offering features like view call stack, run until exception, and view and set variables. Programmers can debug program units without modifying the debugger. Easy to use, the debugger can help fix a wide range of bugs. It also enables programmers to find the cause of exceptions by directing them to the line that caused the exception and displaying the variable values at the time of the exception.
PL/SQL Developer includes AI Assistant, which provides numerous predefined AI Functions to help programmers create, modify, finish, fix and optimize PL/SQL code quickly. Developers can also modify the predefined AI Functions, ask general questions to AI Assistant about code and create new AI Functions for specific tasks.
Another unique feature of PL/SQL Developer is PL/SQL Beautifier. Developers can work with a user-defined set of rules to format their SQL and PL/SQL code. As soon as they compile, save or open a file, PL/SQL Developer automatically beautifies it, thus improving its readability and understandability. This is crucial to keep the software development lifecycle moving and to facilitate easy collaboration among members of large project teams.
Since it is a multithreaded IDE, PL/SQL Developer lets developers continue working even when other tasks run, such as SQL queries, PL/SQL programs or debug sessions. There's no need to install a database object to use PL/SQL Developer, and the only middleware needed is Oracle Net.
There are several different database options for organizations to choose from. Learn more about how DBMS and RDBMS compare. Also, NoSQL systems are becoming more prevalent in the cloud, with numerous cloud providers and vendors offering them. Learn about the various types of NoSQL databases and their pros and cons.
