Procedural DML
In procedural DML, the user has to tell the system two things:
In procedural DML, the user specifies what data is needed and how to retrieve it. The user has to provide the steps, conditions, and complete logic for accessing the required data. Therefore, the user needs some knowledge about how the data is stored and accessed internally. It gives the system a clear procedure or sequence of operations to follow.
Example: Relational Algebra is a theoretical example of a procedural language.
Non-Procedural DML (Declarative DML)
In non-procedural DML, the user only tells the system one thing:
The user does not need to explain how to get it. The user just writes a simple, high-level query. The DBMS itself (using a part called the Query Optimizer) decides the best way to fetch the data like which path to follow and which index to use. The user does not need detailed knowledge about the database’s internal storage or access methods. It is generally easier to write, understand, and maintain than procedural DML.
Example: SQL is the most common example of a non-procedural DML.
Table
| Feature | Procedural DML | Non-Procedural DML |
| User Requirement | Specifies What data is needed and How to fetch it. | Specifies only What data is needed. |
| Complexity | More complex requires knowledge of algorithms and storage logic. | Simple and user friendly focuses on results. |
| Execution Strategy | Written manually by the programmer. | Determined automatically by the DBMS Query Optimizer. |
| Code Length | Typically longer because it includes procedural logic. | Concise usually single line or short queries. |
| Main Example | Relational Algebra, procedural extension languages. | SQL (Structured Query Language). |