Introduction to PL/SQL
Procedural Language/Structured Query Language (PL/SQL) is Oracle’s extension of
Difference between SQL and PL/SQL
SQL and PL/SQL are both used for interacting with relational databases. However, there are some key differences between both languages. Let’s compare both languages for better understanding.
SQL | PL/SQL | |
Non-procedural language | Procedural language | |
Executes a single query at once | Executes blocks of codes | |
No variable declaration | Variables of different data types can be used | |
The database engine directly executes statements | The PL/SQL engine executes code | |
Limited error-handling capabilities | Advance error handling capabilities using exceptions |
Features
Here are some prominent features of PL/SQL:
Procedural language: PL/SQL is a procedural language. It means that it has a sequenced structure. The code can contain loops, conditional statements, procedures, etc.
Block structure: PL/SQL allows the creation of blocks. Users can send blocks of instructions to the database instead of sending each instruction separately. It helps reduce traffic between applications and databases, resulting in faster data processing.
Exception handling: PL/SQL has exception-handling abilities. We can create a separate section to define how the program should react to specific errors. If an error occurs during the execution of code, the control will be passed to the
EXCEPTIONblock of the code.
EXCEPTIONWHEN --- Write condition here ---THEN --- What should code do when condition occurs ---END;/
The END; / code is not particularly part of EXCEPTION block; however, it is common practice to write EXCEPTION block at the end of the code so it is followed by END; / i.e., placed at the end of PL/SQL code.
Triggers: We can create triggers in PL/SQL. Triggers are special pieces of code that execute automatically when specific database events occur. For instance, we can create a trigger to automatically update the audit table whenever a new record is inserted into the products table.
Structure
As discussed above, PL/SQL consists of blocks that serve as fundamental building blocks of code. All the code is written in these logical blocks. These blocks are dedicated for different purposes, for instance, variable declarations, exception handling, etc. Here is the basic structure of PL/SQL:
Architecture
PL/SQL is a combination of different components. Here is a breakdown of its architecture:
PL/SQL engine: At the core of the PL/SQL architecture is the PL/SQL engine. This component parses the code and separates SQL and PL/SQL statements. The SQL statements are executed by the database server directly, and the PL/SQL engine handles PL/SQL statements.
PL/SQL block: All the code is stored in the block. Three basic sections are declaration, execution, and exception.
Database server: The database server is the most important PL/SQL architecture component. The server is used to execute the SQL statements of the code. All the SQL statements can be sent to the server in a single go. This increases the performance of the application. We can also use a database server to store PL/SQL code.
DELIMITER //CREATE TRIGGER after_products_insertAFTER INSERT ON audit_tableFOR EACH ROWBEGININSERT INTO audit_table (action, table_name)VALUES ('Insert', 'products');END;//DELIMITER ;SELECT * FROM audit_table;
Code explanation
Line 1:
DELIMITERis used to change the delimiter temporarily so that MySQL interprets the entire trigger creation statement correctly.
Note: By default, the delimiter is
;, but changing it to//allows you to use;within the trigger definition without terminating the statement prematurely.
Lines 2–4: The
CREATE TRIGGERstatement tries to define a trigger namedafter_products_insert.The trigger is set to executeAFTER INSERT ON audit_table, which means that it is supposed to be triggered after an insertion occurs in theaudit_table.
Note: Creating a trigger that triggers upon insertion into the same table (
audit_table) that it’s acting upon can lead to recursive triggers and potential issues like infinite loops or errors due to modification of the same table that triggers the action.
Lines 5–8: Inside the trigger definition, there’s a block of code encapsulated between
BEGINandEND. This block is where the trigger’s actions are defined. In this case, it’s anINSERT INTOstatement that tries to insert a new row into theaudit_tablewhenever an insertion happens in theaudit_table.Line 11: After defining the trigger, there’s a
SELECTstatement attempting to display the contents of theaudit_table.
Free Resources
