SQL Plus Procedures Conclusion
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:
- Describe the uses of parameters in SQL*Plus and PL/SQL
- Use parameters when starting a SQL*Plus script
- Describe the ways in which parameters are used in PL/SQL
- Place a parameter in a procedure
- Use parameters and the RETURN command appropriately
- Identify correct syntax for using parameters with cursors
Oracle DBMS Packages
In this module you were introduced to the following glossary term:
In the next module, you will learn to combine procedures and functions into units called packages.
Use Tools to Write Code Effectively
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.