A key concept of object-oriented programming (OOP) is inheritance, which allows grouping properties and behaviors into a hierarchy. This way, new objects can automatically acquire functionality (methods, variables, constants, etc.) from their ancestor.
Object-oriented programming is not exclusive to higher-level languages; a very similar principle can be achieved in PL/SQL using the special OBJECT type. A class, or rather an object, is defined similarly to a package, i.e., with a specification and a body.
Savepoints are a mechanism within a PL/SQL block that allows you to divide the transaction being executed into partial units, or milestones. You can then easily return to these milestones without losing the entire transaction.
The option to define a PL/SQL function or procedure inside an SQL query might seem strange at first glance, but it has one significant advantage – the database does not need to switch context, which in certain cases leads to a massive increase in performance.
To use an array in SQL queries, it was previously necessary to create a “stored” type on the SQL server side and assign the appropriate rights… Read more
As already indicated in the introduction, the part of the block introduced by the keyword EXCEPTION is used for catching program runtime errors.
In previous parts, most examples were demonstrated at the level of a single anonymous PL/SQL block. In practice, however, it is more appropriate to unify functionality into logical units, called packages.
Named blocks include procedures and functions. These can be called repeatedly. The declarative section within both types of methods is indicated by the keyword IS, which is mandatory. Further division into the executable part and the exception handling part is identical to anonymous blocks.
For cyclical repetition of a code block, PL/SQL provides the FOR and WHILE constructs.
The topic of structures capable of holding multiple values is somewhat extensive due to the division of the Oracle database into an SQL part and a procedural PL/SQL extension. Also, individual database versions bring slight differences in the use of given structures.