How do you get data into your programs? Simple—select it from the database. This is the easiest way to use SQL in your program. Thus, inserting a line like the following will provide you with the capability to access data in the database:
Let’s break down the statement and look at what it means to the program. As you look at the SELECT statement, you can see that it looks very similar to a standard SELECT statement. However, you should also have noticed the word “INTO” in the statement. You may be wondering what it’s for. This is how you put a value into a variable using a SELECT statement.
The following example illustrates how you include a SQL statement in your PL/SQL program:
In addition to selecting one value from the database, you have the ability to select more than one value and add conditions that you want to include. To do this, use the following cursor format:
You can use any SQL statement you want within your program. It can be a SELECT, INSERT, UPDATE, or DELETE statement. All of these will be supported. When you use a SELECT statement as you did in the preceding example, it is called an implicit cursor. An implicit cursor is a SQL statement that is contained within the executable section of the program and has an INTO statement (as in the case of a SELECT statement). With an implicit cursor, Oracle will handle everything for you, but there is a cost to doing this: The program will run slower. You need to understand what you’re doing because although it may not be the best way to perform a SELECT statement, you must use an implicit cursor when you want to run a INSERT, UPDATE, or DELETE statement. So, let’s move on to see a better way of doing the same thing. We will revisit your previous example so that you can compare the two.
The better way is to create an explicit cursor. An explicit cursor is a SELECT statement that is declared in the DECLARE section of a program. You do this so that Oracle will then prepare your SQL statement before running your program.
At times, you may not know the exact SQL statement you want to run. To help you with a more dynamic solution that allows you to define the SQL statement at runtime, PL/SQL supports both dynamic and static processing within a program. Dynamic SQL enables you to build SQL statements dynamically at runtime, while static SQL statements are known in advance. You can create more general-purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation time.
To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. Dynamic SQL is especially useful for executing SQL statements. The following example shows you how to use dynamic SQL to make your programs more flexible and responsive to change:
In Oracle 12c, the types that can be used as bind variables have been extended: BOOLEAN variables and nested tables can now be used as bind variables in Dynamic SQL. Ultimately this further extends the flexibility and functionality that can be supported by Dynamic SQL.
This makes for very efficient use of memory by the program. Let’s look at what the program would look like with an explicit cursor:
In the previous code, we converted the initial example into one that uses an explicit cursor. Notice that the SELECT statement is now contained in the declaration section. There is also no longer an INTO clause. This functionality is moved into the execution section, where it is used in the FETCH command.
Ask the Expert
Also note that we introduced three new PL/SQL commands: OPEN, FETCH, and CLOSE. With these three simple commands, you can get data from your database using SQL cursors. The OPEN command tells Oracle to reserve memory that will need to be used by the SELECT statement. Meanwhile, the FETCH command pulls the data from the first row of the result set, and the CLOSE command closes the cursor you just opened and returns the memory back to Oracle for other uses.
Leave a Reply