Dr. Edgar F. Codd (1923–2003), an IBM research scientist, first conceived the relational database model in 1969. He was looking into new ways to handle large amounts of data in the late 1960s and began thinking of how to apply mathematical principles to solve the myriad problems he had been encountering.

After Dr. Codd presented the relational database model to the world in 1970, organizations such as universities and research laboratories began efforts to develop a language that could be used as the foundation of a database system that supported the relational model. Initial work led to the development of several different languages in the early to mid-1970s. One such effort occurred at IBM’s Santa Teresa Research Laboratory in San Jose, California.

The use of local variables within a PL/SQL program is important for everyone using the language to understand. It is a basic component of each program, and as such, it is invaluable for understanding what is available and how best to use it. You can now look at how you use and define variables and working storage within your PL/SQL programs.

As with all programming languages, there are characters that you use to write your programs. Each language has its own rules and restrictions when it comes to the valid characters. In the following sections of article, we will show you:

The structure you use in PL/SQL is the foundation for the language as a whole. Once you’ve mastered it, you will then be able to move forward; however, if you do not take the time to get this first step right, your journey will be difficult. Thankfully, it’s quite simple.

The structure is quite basic. You will have areas for your program parameters (these are used to pass values from outside a program to the program itself), your internal variables, the main program code and logic, and various ways to deal with problem situations. Let’s look at the basic form of a PL/SQL block:

Problem

You want to grant someone the ability to create and execute stored PL/SQL programs.

Solution

To grant the ability for a user to create a procedure, function, or package, you must log in to the Oracle database with a privileged account and grant the CREATE PROCEDURE privilege to the user. Here's an example:

Problem

You have a number of procedures and functions that formulate an entire application when used together. Rather than defining each subprogram individually, you prefer to combine all of them into a single, logically related entity.

Solution

Create a PL/SQL package that in turn declares and defines each of the procedures together as an organized entity. You declare each of the subprograms in the package specification (otherwise known as a header) and define them in the package body.

Problem

You want to create a PL/SQL procedure that accepts several parameters. However, some of those parameters should be made optional and contain default values.

Solution

You can allow the procedure caller to omit the parameters if default values are declared for the variables within the procedure. The following example shows a procedure declaration that contains default values:

Problem

You have a PL/SQL procedure in your Oracle database that accepts a large number of parameters. When calling the procedure, you would rather not worry that the positioning of the parameters is correct.

Solution

Rather than trying to pass all the parameters to the procedure in the correct order, you can pass them by name. The code in this solution calls a procedure that accepts six parameters, and it passes the parameters by name rather than in order.

Problem

You want to execute some code each time a particular PL/SQL package is instantiated in a session.

Solution

Create an initialization block for the package in question. By doing so, you will have the ability to execute code each time the package is initialized. The following example shows the same package that was constructed in this article. However, this time the package contains an initialization block.

A relational modelis not enough, we need a specialized programming language to interact with the database. There have been many attempts to create such a language, and SQL is the winner.

Oracle Database 12cis more than just a database management system - it’s also an engine for many programming languages. Not only does it serve as a Java engine with the built-in Java Virtual Machine (JVM), it’s a PL/SQL engine as well. This means that the code you write can be stored in a database and then run as required when it is needed.

The PL/SQL engine is bundled together with the database and is an integral part of the Oracle server, providing you with a powerful language to empower your logic and data. Let’s look at how PL/SQL fits into the Oracle server. Figure 1 shows you how PL/SQL works from both within and outside the Oracle database.