IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi folks,
In this post, I’m going to discuss 8 features that you must know to work with PL/SQL. These are structures that I have been using most in my years of experience with Oracle. I know that there is a tremendous amount of important peculiarities when discussing PL/SQL. However, I decided to focus on those I think are most important.
Before starting, I’m not focusing on SQL queries/statements. I know that we can’t split PL/SQL from SQL, nevertheless, I suppose you have some SQL background and you want to learn a little bit of PL/SQL.
1 – Block Structure
Blocks are the most used and practical structure in PL/SQL. Actually, PL/SQL is a block-structured language whose code is organized into blocks. A PL/SQL block consists of three sections, declaration (non-required), executable (required), and exception-handling (non-required) sections.
The following picture depicts the basic structure of a block in PL/SQL. This syntax will vary when according to what you are doing. For instance, the required syntax to create a procedure is slightly different than a function. However, the main idea is the same.
For more detail and examples about this topic, please check out the following link.
2 – Implicit Cursors
Well, when talking about iterations, the most common and used structure is the implicit cursor. You can create a FOR LOOP with quite a few lines and you don’t have to worry about cursor treatment such as open, fetch and close. However, this feature is only available when creating a FOR LOOP.
For more detail and examples about this topic, please check out the following link.
PL/SQL For Practitioners – #2 Implicit Cursor FOR LOOP Statement
3 – Explicit Cursors
Explicit cursors are defined by the programmers to gain more control over the context area. These cursors should be defined in the declaration section of the PL/SQL block. It is created on a SELECT Statement which returns one or more than one rows.
For more detail and examples about this topic, please check out the following link.
PL/SQL For Practitioners – #3 Explicit Cursor FOR LOOP Statement
4 – Exception Handling
In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined (by the run-time system) or user-defined. Examples of internally defined exceptions include division by zero and out of memory. On the other hand, a user-defined exception can be anything that the developer decides to. When an error occurs, an exception is raised. That is normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. To handle raised exceptions, you write separate routines called exception handlers.
For more detail and examples about this topic, please check out the following link.
5 – Procedures
PL/SQL is a modern, block-structured programming language. It provides several features that make developing powerful database applications very convenient. Procedures and functions that are created outside of a package are called stored or standalone subprograms. On the other hand, procedures and functions defined within a package are known as packaged subprograms. Procedures and functions nested inside other subprograms or within a PL/SQL block are known as local subprograms, which cannot be referenced by other applications and exist only inside of the enclosing block.
For more detail and examples about this topic, please check out the following link.
6 – Functions
Procedures and functions that are created outside of a package are called stored or standalone subprograms. On the other hand, procedures and functions defined within a package are known as packaged subprograms. Procedures and functions nested inside other subprograms or within a PL/SQL block are known as local subprograms, which cannot be referenced by other applications and exist only inside of the enclosing block.
For more detail and examples about this topic, please check out the following link.
7 – Packages
A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents. Packages usually have two parts, a specification (spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms. If a subprogram or variable is declared only on the body, nobody outside of this package can see it.
For more detail and examples about this topic, please check out the following link.
8 – Types
In PL/SQL, object-oriented programming is based on object types. They provide abstract templates for real-world objects, and so are an ideal modelling tool. To plug an object type into your programs, you need to know only what it does, not how it works. All the information a client program needs to use the methods is in the spec. Think of the spec as an operational interface and of the body as a black box. You can debug, enhance, or replace the body without changing the spec and without affecting client programs.
In an object type spec, all attributes must be declared before any methods. Only subprograms have an underlying implementation. So, if an object type spec declares only attributes, the object type body is unnecessary. You cannot declare attributes in the body. All declarations in the object type spec are public (visible outside the object type).
For more detail and examples about this topic, please check out the following link.
Please write down in the comments any further questions you may have. I’ll be happy to help you.
