Lesson 8

This module explored several ways to use parameters within SQL*Plus procedures, functions, and cursors. You discovered what a parameter is and how it is used. You experimented with specifying parameters in SQL*Plus. You explored the three modes of parameters: IN, OUT, and IN OUT. You learned how to call a procedure that uses all three types of parameters. You saw how the function substitutes a RETURN command for an OUT parameter and you learned how to convert a procedure into a function.
You reviewed an interesting variation on the cursor in which you add parameters to the cursor's definition and use the parameters when opening the cursor.
In this module, you learned how to:

1. Describe the uses of parameters in SQL*Plus and PL/SQL
2. Use parameters when starting a SQL*Plus script
3. Describe the ways in which parameters are used in PL/SQL
4. Place a parameter in a procedure
5. Use parameters and the RETURN command appropriately
6. Identify correct syntax for using parameters with cursors

From Oracle 11g to Oracle 18c, several advancements and changes have been incorporated into how parameters are used in SQL*Plus and PL/SQL. However, the fundamental process of declaring and using parameters, variables, and constants in these environments has remained largely the same. Following is a detailed analysis:

1. SQL*Plus: In SQLPlus, variables can be declared and used as bind variables. You use the VARIABLE command to declare a bind variable, and then you can reference this variable in later PL/SQL blocks. These variables can be used as parameters in SQL statements. However, the enhancements related to variables and parameters usage are mostly seen at the SQL and PL/SQL language level rather than the SQLPlus level.
2. PL/SQL: The PL/SQL engine treats PL/SQL anonymous blocks, procedures, and functions as separate entities, each having its own namespace for variables, parameters, and subprograms. Between Oracle 11g and 18c, there have been changes and additions in the handling of parameters and variables, but the core principles remain consistent.

1. Parameter Modes: In PL/SQL, parameters can be categorized into three types: IN, OUT, and IN OUT. There have been no changes in these parameter modes from Oracle 11g to Oracle 18c.
2. Default Values: As of Oracle 11g, only IN parameters could have default values. However, in Oracle 12c and maintained in 18c, PL/SQL subprograms can now assign default values to IN OUT parameters.
3. Variable Argument Functions and Procedures: Oracle 18c introduced the concept of PL/SQL functions and procedures with variable numbers of arguments, using VARIADIC parameters. This feature enables the passing of variable numbers of parameters to PL/SQL stored procedures or functions.
4. Qualified Expressions: In Oracle 18c, qualified expressions were introduced. They enable the easier construction of record and associative array values. They also allow specifying the datatype only at the end of an array literal.
5. Initialization of Types with a Default Constructor: Oracle 18c enables the initialization of types without the need to declare a constructor explicitly. If no constructor is defined for a type, a default constructor is provided.
6. Enhancements in Triggers: Oracle 18c brought improvements in trigger functionality, enabling the declaration of PL/SQL variables, constants, and types in a trigger body, and initialising variables and constants with constant values. This provides a more robust context for handling trigger-based operations.
7. Static Expressions for SQL and PL/SQL: In Oracle 18c, PL/SQL now allows static expressions for SQL and PL/SQL. This enhancement means that developers can use more PL/SQL functions in more contexts within SQL.

As we can see, while the fundamental nature of variables and parameters in SQL*Plus and PL/SQL has not dramatically changed from Oracle 11g to Oracle 18c, the way these constructs can be manipulated and used within Oracle's database environment has been improved. These advancements facilitate more flexible, dynamic, and efficient code.

So far, we have assumed that you have had two basic tools with which to work: 1) SQL*Plus and a 2) text editor. SQL*Plus is an excellent example of an engine for compiling and executing both SQL and PL/SQL. As a command-line environment, however, it leaves much to be desired. If you are already familiar with the commands and intimately familiar with the Oracle data dictionary views, you can perform seeming wizardry. For the rest of humanity, however, SQL*Plus can be a serious obstacle to high-productivity PL/SQL development.
Fortunately, during the past five years, many third-party vendors have developed software products (usually lumped under the category of interactive development environments, or IDEs) that allow you to build, test, debug, and format your code in much more effective ways. Complementing these "all-in-one" utilities are a host of more specialized programs, such as code formatters and generators. This section introduces you to a variety of these tools and capabilities. We do not endorse or recommend any single tool; the price, features, and interface vary too wildly to make such a recommendation useful. Rather, we suggest that you try out several products to see how they match up to your needs and preferences. An alphabetical list of the products known to the authors at the time of publication. Virtually all of these products have some version you can download via the Internet; several of them, identified as "freeware" or "open source," are truly free. Unless otherwise indicated, these products run on some flavor(s) of Microsoft Windows.
In the next module, you will learn to combine procedures and functions into units called packages.